There are some server side db configurations available to tune PostgresSQL.
Please refer URL for details about the same https://www.packtpub.com/books/content/server-configuration-tuning-postgresql
For optimizing query on DB you can refer the different Run-time statistics configuration available on PostgreSQL docs.
If you can provide more details about your current DB usage like I/O, read, write.
Hi Meenakshi, the way I see it, performance issues will be required to be addressed on several levels in your scenario, i can try to mention few:
1. ThingWorx Federation is one way to begin with, not sure what errors your received - for more on Federation system setup you could give this article a try
2. Consider having High Availability on your Data Tier which is PostgreSQL in your case
3. Optimizing purely from PostgreSQL side, may be consider having special views, materialized views to reduce the computation time on DB so data can simply be fetched with all its related attributes instead of first computing them on DB side and then returning result to your query.
Thanks Sushant Pandey!
As I have mentioned we are working with postgre as twx internal persistence provider that is hosted by ptc so it's monitoring and configurations are out of my hand.
I assume you got my scenario, so with your expertization what would you recommend?
Can I work with federation to keep 500gb into publisher server and after 500gb I will move historical data to subscriber server with a schedular?
I have followed the article as well as video tutorial to configure federation server, but still subscriber status is false.
the error in publisher server's logs is - "No Valid User Name Assigned To Federation Subscriber [subscriber] - subscriber will not be started"
just FYI, I am trying federation with 2 trial foundation server provided by thingworx for 30 days. Is the federation could be performed with trial instances?
I haven't tried it with sandbox and production instances.
Meenakshi, i recommend you to open a case with CS on this because you will need their help in setting up the federated ThingWorx. You/CS will need to likely configure the host correctly to allow reaching them from different ThingWorx instances, configuring just from the Composer might not be sufficient.
Meenakshi Agrawal , PostGreSQL performs well up to an estimated 500 Gb of data storage (this is a rough estimate dependant on use case). For very high volume data DSE is the choice, it performs well for large data sets.
I would suggest reading this article from Adam Ressler , very helpful in selecting database for your Thingworx.