I have been running Postgres with TimescaleDB for several years now. Not with Ignition (yet), but to collect application telemetry from several applications servers. I am a big fan.
Here are two use cases.
Log file searching
Table size 137M records. 11 columns. Mostly text. Max text message around 2048 bytes. Table growth is about 800K records per day. I concatenated the text fields into a tsvector via a trigger and placed a GIN index on it.
Searching small windows is insanely fast. Tens of MS for a 10 days. Three seconds for 40 days. Searching through 3.5 months of information takes about 28 seconds.
Application Metrics
Table size 137M records. 10 columns. Mostly int32 and int64. Table growth is 800K/day too. I only have an index on the datetime column. Query performance suffers compared to above because I do not have indexes on the other fields.
During our review, MS SQL Server Standard did not even compare. It stayed in sleep mode. PG with TS was 30% - 50% faster than standard PG.
It's been a while, any update on your experience of using timescaleDB with Ignition?
I'm really curious to know if is it still stable and if you can query your data faster than Ignition SQL Historian.
We suffer a lot for reading data when it comes to a month range.
Yes I use it it help but in general it is not good as TSDB solution and also this method use a lot of space.
I realise IA integrated at least one free TSDB engine into the product
Yes, we have two applications running TSDB with Ignition. One project is over a year old and the other was started Oct 2022 and to date - no issues. Performance seems fine, but unfortunately, neither of these projects have users that query extended lengths of data.
I wanted to ask about storage, does using TSDB require less disk storage? Or it doesn't include much.
On the other hand, if I already have several sqlth_1_data type tables since I have the Ignition partition configured, in this case do I have to convert each of the tables into a hyper table?
IIUC, to get the most benefit from Timescale, you turn off Ignition's partitioning and put all the raw data into a single hypertable. I'm not sure what trouble you will have moving existing data into that architecture.