Hi,
I'm fairly new to Ignition and am trying to use postgresql with TimescaleDB on top as a tag history provider. We have quite a few tags with a sampling rate of 1 second, and we're seeing high load times in Perspective when trending tags over longer periods of time. So I have a couple of questions regarding our setup and if there's any tweaking that can be done.
On the Ignition side, we've configured a database connection that stores the tags in postgres using the default JDBC driver. Partitioning is turned off and is handled by TimescaleDB, other than that we've not altered the default configuration from Ignition. Partitioning is configured according to the best practise from TimescaleDB documentation regarding size vs. server resources etc.
What I suspect is the reason why we're seeing such high load times is that all the tags and values are stored in the same tables (sqlth_data and the others), so the queries from Perspective will have to run against a large table (1000 tags, 1s sampling rate). Is there any way of configuring Ignition to store tags in separate tables (e.g. sqlth_data_1 for the first 100 tags, sqlth_data_2 for the next 100 and so on)?
We also haven't optimized the postgress/timescale by indexing beyond the default in timescale, perhaps that would also make a difference. Is there any specific indexing that works well with Ignition Perspective?
Best regards, Jørn
Edit: 100 tags --> 1000 tags
Are you using fixed sampling? If so, then the tag historian is not for you. Use transaction groups with wide tables, or the scripted equivalent inserts. Ignition's tag historian provides a wide table pseudo-provider to retrieve from such tables to Perspective charts.
If Timescale permits, consider using BRIN indices on your t_stamp columns.
'High load times' is underspecified. Is the slow part the visible component displaying the data, or the gateway actually retrieving the history?
Thanks for pointing me towards transaction groups! I've configured some groups now and will see how that compares to what we've used so far.
The slow part is the query time on the hypertable, we have the same issue if we disregard Ignition/Perspective and query the database directly. Part of the problem is very likely due to my limited experience with SQL-database tuning and TimescaleDB. I've tried some indexing since the first post and while it does improve the query time to some degree, it is nowhere near what I hoped for yet. There's more to be tried here though.
We're also running a noSQL historian in parallel on the same dataset and where we see 17s query time from post/timescale for a 24hours of data on a single tag, the query time for the historian is significantly faster (a few seconds just based on how fast the Perspective updates the trend). The databases are running on separate backend servers with equal amount of resources.
Note: I see I forgot a zero in the first post, we have about 1000 tags with 1s sampling rate. The amount of tags in the finished system could be much higher.