The store-metrics latest read sorts all 400 days of collect.store_metrics on every call: 7.8 s at full retention #3934

Summary

DarlingStoreMetricsReader.StoreMetricsLatestSql takes each store object's newest collect.store_metrics row with a DISTINCT ON (object_kind, object_name) ... ORDER BY object_kind, object_name, metric_time DESC over the whole table. The only index is idx_store_metrics_time (metric_time), so every call sorts every retained row. The table keeps 400 days of hourly sweeps at about 250 objects a sweep, so the sort grows linearly for over a year after a store is created.

Found while fixing #3903 (payload size). The reads were out of that fix's scope, and changing them needs an index, which needs a migration.

Measured

A CI-sized rig (PostgreSQL 18.4, TimescaleDB 2.28.1, work_mem 4 MB) with store_metrics seeded to full retention: 400 days × 24 sweeps × 251 objects = 2,409,600 rows, 236 MB. Each read ran under EXPLAIN (ANALYZE, BUFFERS):

For comparison, DARLING01 today (83,355 rows, 17 days) runs the latest read in 225 ms and the 30-day daily read in 504 ms. So the cost is a function of store AGE, not fleet size, and it arrives gradually.

Candidate fix, measured on the same seed

An index on (object_kind, object_name, metric_time DESC) plus a skip-scan form of the latest read: a recursive CTE walks the distinct (kind, name) pairs and takes one LIMIT 1 row per pair. The latest read goes from 7,772 ms to 15 ms with identical rows (251). It needs a migration rung for the index, so the rung number has to be assigned. The daily read could use the same index per object, or stay on the time index with a larger work_mem for that statement.

The alternative with no migration is a semantic change: bound the latest read to the recent sweeps (metric_time >= max(metric_time) - N days). That drops long-gone objects from the inventory's stale-row count, which is arguably more correct but is a behavior change, so it needs a ruling.

Not urgent

Every figure above is under the 20 s MCP read deadline, and a store reaches full retention only after 400 days. But the latest read runs on every get_store_metrics call and on the hourly self-alert, and it only grows.