Claude posting for Erik Darling
Two service statements that take MAX(collection_time) over a whole hypertable are cut off at their 10-second client deadline on a large store. When the startup read fails, every collector on that server runs at once instead of resuming its cadence. When the orphan-watermark prune fails, the orphaned keys stay.
Seen
A production SQL Server store (43 servers, the busiest store), PostgreSQL server log from 2026-09-24 22:00Z to 2026-09-27 ~15:45Z. PostgreSQL logs a client-side command timeout as canceling statement due to user request. 59 such cancels were logged, and most do not coincide with a service stop:
| statement |
cancels |
the collector_state orphan-key prune (DarlingCollectorRunner.PruneOrphanedDatabaseStateKeysSql): DELETE … USING (SELECT MAX(collection_time) … FROM database_states WHERE server_id = $1) |
18 |
the startup watermark read (DarlingWorker.ReadCollectorWatermarksAsync): SELECT collector_name, MAX(collection_time) FROM collection_log WHERE server_id = $1 GROUP BY collector_name |
12 |
| others (analysis-pass reads) |
29 |
They cluster in four places: 5–30 minutes after each service start (32 of them), around 00:37Z daily, one 33-minute burst with no restart near it, and a scattered few. A second, smaller store logged none in the same window.
Why
Both statements run with CommandTimeout = ServiceCommandDeadlines.CollectionSweepSeconds (10 s), and neither bounds collection_time. So each one reads the newest row per server (or per server and collector) across every chunk of database_states or collection_log, compressed chunks included. On a store with a month of history, that can pass 10 s, most often right after a start, when every server runs its connect path at once.
- The watermark read: its failure is isolated by design ("a store hiccup returns an EMPTY map so the caller seeds every collector as never-run"). But then every collector for that server is scheduled for a prompt, jittered run instead of resuming its cadence, on a store that is already busiest right after a start.
- The prune: it is best-effort hygiene, so a failure only leaves the orphaned keys for the next cycle, which fails the same way.
Fix shape
Bound both reads to the recent past, the way #4255 did for the availability-group reads: the newest row within a window the collector cadence guarantees (for example, the last one or two chunk widths), or an ordered ORDER BY collection_time DESC LIMIT 1 descent per server, which TimescaleDB answers from the newest chunk. Keep the existing semantics where the bound finds nothing:
- the watermark read then treats the collector as never run, as today;
- the prune's snapshot guard then prunes nothing, as today.
Pin it with an EXPLAIN against a store with many compressed chunks (the chunks touched, and a plan that doesn't scale with retention), plus the unchanged empty-result behavior.
Related: #4255 (the same shape, fixed for the availability-group reads), #4450 (how the cancels were found).
Claude posting for Erik Darling
Two service statements that take
MAX(collection_time)over a whole hypertable are cut off at their 10-second client deadline on a large store. When the startup read fails, every collector on that server runs at once instead of resuming its cadence. When the orphan-watermark prune fails, the orphaned keys stay.Seen
A production SQL Server store (43 servers, the busiest store), PostgreSQL server log from 2026-09-24 22:00Z to 2026-09-27 ~15:45Z. PostgreSQL logs a client-side command timeout as
canceling statement due to user request. 59 such cancels were logged, and most do not coincide with a service stop:collector_stateorphan-key prune (DarlingCollectorRunner.PruneOrphanedDatabaseStateKeysSql):DELETE … USING (SELECT MAX(collection_time) … FROM database_states WHERE server_id = $1)DarlingWorker.ReadCollectorWatermarksAsync):SELECT collector_name, MAX(collection_time) FROM collection_log WHERE server_id = $1 GROUP BY collector_nameThey cluster in four places: 5–30 minutes after each service start (32 of them), around 00:37Z daily, one 33-minute burst with no restart near it, and a scattered few. A second, smaller store logged none in the same window.
Why
Both statements run with
CommandTimeout = ServiceCommandDeadlines.CollectionSweepSeconds(10 s), and neither boundscollection_time. So each one reads the newest row per server (or per server and collector) across every chunk ofdatabase_statesorcollection_log, compressed chunks included. On a store with a month of history, that can pass 10 s, most often right after a start, when every server runs its connect path at once.Fix shape
Bound both reads to the recent past, the way #4255 did for the availability-group reads: the newest row within a window the collector cadence guarantees (for example, the last one or two chunk widths), or an ordered
ORDER BY collection_time DESC LIMIT 1descent per server, which TimescaleDB answers from the newest chunk. Keep the existing semantics where the bound finds nothing:Pin it with an EXPLAIN against a store with many compressed chunks (the chunks touched, and a plan that doesn't scale with retention), plus the unchanged empty-result behavior.
Related: #4255 (the same shape, fixed for the availability-group reads), #4450 (how the cancels were found).