Tuning PostgreSQL for Ignition

Ignition
1

Hello. I have inherited a Scada with 27000 tags, that saves them to historian each second. It creates tables of about 20Gb for each day. It uses PostgreSql in a Windows Server 2016 with 32 GB of RAM.

As I said in other post, I had problems with queryTagHistory ( a lot of time outs ). I have checked that removing timestamp index of table and leaving only the tagid+timestamp primary key, the number of timeouts decreases.

But I see that continues sometimes with time outs. I see in the Active Queries of the gateway that the same query of historian charts, sometimes takes few seconds and sometimes 5 or more minutes! Queries of ignition, not custom queries. PostgreSql is dedicated to Ignition in the same server.

Are there any recommended tunings for PostgreSql installation for Ignition? I read that the default installation maybe is poor for Ignition. I would love to, but now I don’t have time to be an expert with PostgreSql. I would appreciate some tips to check, to improve the performance with Ignition, that not have complex queries nor joins, only selects by timestamp and tagid, and inserts.

I promise I will do a report with the solution for future similar questions if I can resolve it :slight_smile:

Thank you!

2

First thought is do you really need to log 27000 tags at 1 sec interval?

There is a product called Timescale DB that works with Postgresql that we played around with that may help with the large amount of time series data you are using. You would have to test and see if you get satisfactory results in your configuration.

3

Like most databases, have it use lots of RAM. The settings in postgresql.conf are well-documented. The defaults assume a small, wimpy system.

For such a large, steady recording load, consider using transaction groups (wide tables) instead of the historian.