Hello,
we are facing troubles with insert and update of values in our Timescale table. In our db we have databases for each machine (~20max) and each machine has a table which contains minutely data for around 200 string/double values. And we want to keep these data for approximately 5 years.
ATM the update of just one day takes us up to 2 minutes.
I tried to analyze this with just updating a single value for a single timestamp (UPDATE … SET … WHERE time = ‘myMinutelyTimestamp’) and with Analyze Explain I see that this statement iterates through all the hypertables, and it does not matter if the date is an actual date or 4 years ago. Because of this this simple statement already has an execution time of 1 - 3 seconds.
Why does the query need to check all hypertables when we just filter by a single timestamp?
Our bucket size is 7days.
TimeScale version: 2.9.3
PostgresSQL: 13.9