We have latest postgres and timescale versions. Currently ingesting 2500 sensors every 5 seconds, each record contains a timestamp,sensor_id,value,quality_id.
All the data resides in a single data table, as per client spec.
Currently we have hyper tables that are chunked at 1 week, then compressed after 30 days, with added segment by sensor_id. Data is kept in this table for 2 years.
The compression % we are getting is amazing at 94%.
The query response time is however degrading and becoming an issue; hence my thoughts on a possible method that would allow a chunk per sensor_id, that was indexed by the timestamp? Our thoughts are that if each sensor_id had it’s own chunk that was further “chunked” or segment by timestamp, then query speeds would be that much faster or not?
I have experimented with postgres partitions but that are a cumbersome and inflexible, yet it would be nice to be able to hyper-table the postgres partition.
Can anyone suggest a solution that would speed up the query speed? The queries are random on sensor_id’s and timestamp selections, less so for yr2 data.
The query types are not suited to materialised views as they are of a nature where an exception/anomaly is flagged, this is associated with a set of sensors whose data will be selected along with a selected date range for a UI to view a trend etc.
Additionally there are maybe 10 other users from python/Jupyter notebooks, who would select sets of random data for model creation etc.
Config changes and auto-tuning aside for now, I would like to have the most efficient hyper table or alternate solution, setup that suits the data profile.
The 2500 data points could also double dependent upon performance.
Albeit newish to Timescale we are now introducing this into our solutions, so excuse some ignorance.
Thank you for reading, any advice will be welcome.