We need to create an index to facilitate queries. However, the new index slows down the data write. What to tune on the TimescaleDB side or PostgreSQL to address this?
TimescaleDB: 2.15.2
PostgreSQL: 16.3
The table looks like
\dS+ table_metrics
Table "table_metrics"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------------±-------------------------±----------±---------±--------±--------±------------±-------------±------------
ts | timestamp with time zone | | not null | | plain | | |
metric_id | bigint | | not null | | plain | | |
device_id | bigint | | | | plain | | |
value | double precision | | | | plain | | |
Indexes:
“table_metrics_pkey” PRIMARY KEY, btree (ts, metric_id)
“table_metrics_ts_idx” btree (ts DESC)
Triggers:
ts_cagg_invalidation_trigger AFTER INSERT OR DELETE OR UPDATE ON table_metrics FOR EACH ROW EXECUTE FUNCTION _timescaledb_functions.continuous_agg_invalidation_trigger(‘20’)
ts_insert_blocker BEFORE INSERT ON table_60 FOR EACH ROW EXECUTE FUNCTION _timescaledb_functions.insert_blocker()
Child tables: _hyper_20_32_chunk,
_hyper_20_39_chunk,
_hyper_20_45_chunk
Access method: heap
We need to add an index on the metric_id column to accommodate the queries like select * from table_metric WHERE metric_id = 6000 AND ts > NOW() - INTERVAL ‘3 hours’;
When you say Slow down", can you share exactly what’s the performance comparison you have as a baseline?
More specifically, the elapsed time of inserts would increase from 5 seconds in average to 200 seconds after some time. Not sure what had caused this.
the same situation happened if I change the primary key column order from (ts, metric_id) to metrics_id, ts). This combination will satisfy queries but will have the same effect as the new index on the metric_id column
I think it has something to do with the index. I checked the wait events are DataFileRead, IO.
Any suggestions?