query_plan_dim holds 15.58 days against 4-day fact retention: 59.1% of rows outlive their referencing facts, and nothing says whether that is margin or slack #3210

Activity

1. erikdarlingdata commented on Sep 9, 2026

   

   erikdarlingdata

   on Sep 9, 2026

   OwnerAuthor

   More actions

   Claude posting for Erik Darling

   Mostly margin, and the resolver says why — but roughly seven days of it is explained by neither retention nor the clamp.

   What sets the cutoff

   DarlingRetention does not use the fact retention directly. It computes the widest retention across collectors whose target table feeds a payload dimension, then clamps that to a measured floor: the oldest surviving digest-carrying fact row, taken as the oldest surviving chunk's range_start rather than a per-row probe. The comment is explicit that this is one-directional — "the chunk floor is CONSERVATIVE in the safe direction … prunes less, never dangles."

   So the horizon is legitimately wider than the 4-day retention, and the first version of this question was wrong to measure against 4 days.

   The clamp, measured

   hypertable	chunks	oldest range_start	age	chunk interval
   query_store_stats	5	09-05 00:00	4.58 d	1 day
   query_stats	6	09-04 00:00	5.58 d	1 day
   query_snapshots	9	09-01 00:00	8.58 d	1 day

   query_stats and query_store_stats are the two carrying a retention policy among the dim-feeding candidates, so the floor across them is 5.58 days. Even taking the most generous reading — including query_snapshots, whose dimension-feeding status I did not confirm — the floor is 8.58 days.

   query_plan_dim holds 15.60 days.

   The gap

   Against the most conservative floor I can reconstruct, about 7 days of dimension content sits beyond the safety boundary, and against the floor from the two confirmed tables it is about 10.

   Sizing the conservative case from the age histogram: rows aged 9 days and older total 1,466,449 — 27.4% of the table — at a sampled mean payload of 10,044 bytes, so on the order of 15 GB of the 53 GB TOAST heap. That is smaller than the 32 GB this issue opened with, because the clamp legitimately explains the 4-to-8.58-day band. It is not zero.

   What I did not do, and it is the thing that would close this

   I reconstructed the cutoff from the resolver's source; I did not run it. retentionDaysFor is an injected override and I did not evaluate it, PayloadDimensions.ForTable decides which collectors count and I inferred the set from which tables carry retention policies, and the sweep's own logs would state the cutoff it actually computed. Any of those could account for the remaining band.

   So the honest disposition is not "there is 15 GB of slack." It is: the documented mechanism explains the horizon out to about 8.6 days and does not obviously explain 15.6, and the sweep can be asked directly. Whoever owns DarlingRetention can settle it faster than more measurement from outside can.

   If it turns out to be margin, the margin should say what it is for — a horizon nearly twice its own safety floor is the kind of number that reads as deliberate to whoever wrote it and as unexplained to everyone after.

   One thing this rules out

   The GC is not failing to keep up. Every policy_retention job ran in the last 24 hours, all succeeded, maximum 0.4 s, no backlog and no held policy. Whatever the cutoff is, it is being enforced promptly — so the 15.6 days is where the computation lands, not where the sweep gave up.
2. erikdarlingdata commented on Sep 9, 2026

   

   erikdarlingdata

   on Sep 9, 2026

   OwnerAuthor

   More actions

   Claude posting for Erik Darling

   Answered as far as measurement can take it: the GC works, it computes a 15-day cutoff, and that cutoff matches none of the three retention values in play. This is a source question now, not a measurement one.

   The sweep logs its own cutoff

   From the service log on the affected store:

   2026-09-08 18:12:59 [INFO] Retention purge drained 200888 row(s) from query_plan_dim
                              in 5 batch(es) (cap 50000), cutoff 2026-08-24 18:10Z
   2026-09-08 23:30:44 [INFO] Retention purge drained  68641 row(s) from query_plan_dim
                              in 2 batch(es) (cap 50000), cutoff 2026-08-24 23:29Z
   

   Cutoff is exactly now − 15.0 days, which is why the table holds 15.6 and not something else. The GC is not behind and is not deferring — it drains six figures of rows per run inside its batch cap, and neither the dimension GC deferred nor the bounded by surviving facts line has appeared in three days of logs.

   Three retention values, and the cutoff matches none

   source	value
   TimescaleDB policy on query_stats / procedure_stats (live)	4 days
   CollectorScheduleDefaults fallback for both	30 days
   measured safety floor (oldest surviving chunk range_start, both tables)	5.59 days
   cutoff the sweep actually computed	15.0 days

   The resolver reads retentionDaysFor?.Invoke(name) ?? CollectorScheduleDefaults[name].RetentionDays, so it should land on 4 (if the override resolves) or 30 (if it does not), then clamp conservatively. Fifteen is neither, and the clamp cannot produce it from either — the floor is 5.59.

   Correcting my own earlier comment: I named query_store_stats as a dim-feeding table. It is not. PayloadDimensions lists exactly query_stats → (query_text_dim, query_plan_dim) and procedure_stats → query_plan_dim. Both measure identically — 4-day policy, 6 chunks, oldest chunk start 5.59 days, oldest actual row 5.59 days — so the floor is 5.59 and the unexplained band is 10 days, not the 7 I gave before. That is ~2.28M rows, 42.5% of the table, on the order of 23 GB.

   The second observation, which may matter more

   Both purges on 09-08 ran immediately after a service start (18:08 and 23:28 both follow a TimescaleDB detected line), and there have been zero purge runs on 09-09 — no restart today, no purge today, ~14.5 hours and counting.

   I have not read the scheduling path, so I am not claiming the sweep is startup-only. But two runs, both at startup, and none on a day without a restart, is the shape of it, and it would be worth someone confirming from source rather than from three days of logs.

   What is not wrong

   Worth stating plainly so this is not read as an incident. The GC drains what it is told to drain and keeps up. It errs in the safe direction — it retains more than the safety boundary requires, never less, so nothing is at risk of dangling. query_plan_dim is 0.7% dead tuples and its live rows account for its size, so this is retained data rather than vacuum debt. The cost is disk.

   What would close it

   The cutoff derivation needs reading by whoever owns DarlingRetention — specifically what retentionDaysFor resolves to on this store and how 15 falls out of it. That is one source read and it beats any further measurement from outside.
3. erikdarlingdata commented on Sep 9, 2026

   

   erikdarlingdata

   on Sep 9, 2026

   OwnerAuthor

   More actions

   Claude posting for Erik Darling

   Answered, and this issue's premise — mine — was wrong. There is no unexplained band. The horizon is a knob, it is set to 14, and it has a name and a timestamp.

   config.config_service.plan_content_retention_days = **14**, updated_at 2026-09-05 12:11:46, updated_by claude-revert-8to64-runaway-cap-governs.

   Fourteen days plus the documented one-day chunk margin is the 15-day cutoff the purge logs (cutoff 2026-08-24 18:10Z on a run at 2026-09-08 18:12). Exact match, no residual.

   Why I did not find it for three comments

   DarlingWorker calls the sweep with two retention inputs:

   await DarlingRetention.PurgeAsync(
       postgres, _timescaleAvailable, _logger, stoppingToken,
       name => StoreConfigProvider.ResolveFleetRetentionDays(name, overrides),
       config.PlanContentRetentionDays);

   I modelled the first and ignored the second. Plan content has its own retention, deliberately separate from the fact retention of the tables that reference it — so computing the horizon from fact retention and the chunk floor and finding a ten-day gap was measuring against a mechanism that does not govern this table. The gap was my model's, not the store's.

   Everything else I asserted on this issue, corrected

   "Two sources of truth for retention." No. config.config_collector_schedules is empty — zero rows, fleet-wide or otherwise — so ResolveFleetRetentionDays returns null for every collector and the resolver falls through to defaults. There is no config-versus-TimescaleDB disagreement.

   "The purge may be startup-only." No. _nextPurgeUtc initialises to DateTime.MinValue and advances by AddHours(24), so it fires on the first tick after any start and daily thereafter. Two restarts on 09-08 (18:08, and 23:28 for the fleet install) produced the two runs I saw; none on 09-09 because there was no restart and the next is due ~23:30Z. Expected behaviour, and I reported the shape of two data points as a possible defect.

   "~10 days / ~23 GB unexplained." There is no unexplained band. 15.6 days of dimension content is 14 days of configured plan-content retention plus margin, working correctly.

   And query_store_stats is not a dim-feeding table — PayloadDimensions lists query_stats and procedure_stats only. That error is in my first comment here.

   What actually remains, and it is a product question rather than a defect

   Plan content is retained 14 days while the fact rows that reference it are retained 4. That is a deliberate setting by a named actor four days ago, not drift — so the question is whether 14 is the value wanted now, and it belongs to whoever set it. The cost of the answer being "yes" is roughly 2.3M rows and on the order of 23 GB of gzipped plan XML that no live fact references.

   Worth stating for whoever picks that up: nothing is at risk either way. The GC drains six figures of rows per run inside its batch cap, errs conservative by construction, and query_plan_dim is 0.7% dead tuples with live rows accounting for its size. This is a disk-versus-plan-history trade, made explicitly, and the only thing missing was anything connecting the knob to the horizon it produces.

   Suggested disposition: close as answered, or keep open re-scoped to "should plan_content_retention_days be 14 against 4-day fact retention" — which is a different question from the one this issue asks.
4. erikdarlingdata commented on Sep 9, 2026

   

   erikdarlingdata

   on Sep 9, 2026

   OwnerAuthor

   More actions

   Claude posting for Erik Darling

   Closing: the premise of this issue is wrong, and it was mine. The 15.58-day span is not an unexplained margin over fact retention — it is a configured value, set deliberately four days ago, and the arithmetic reproduces exactly.

   config.config_service.plan_content_retention_days = 14, updated_at 2026-09-05 12:11:46. DarlingRetention.ComputeDimensionCutoff (:813) computes utcNow.AddDays(-(planContentRetentionDays + 1)) — fourteen days plus the documented one-day chunk margin — which is the 15-day cutoff the purge logs, with no residual.

   The comparison this issue is built on is against a mechanism that does not govern that table. PurgeAsync takes two retention inputs: the fleet resolver (retentionDaysFor) and planContentRetentionDays. ComputeDimensionCutoff's fact-retention-derived arm at :808 applies only when the dedicated knob is unset (<= 0). It is set. Plan content has its own retention, deliberately separate from the fact retention of the tables that reference it — so deriving the horizon from widest-fact-retention plus a chunk floor and calling the difference unexplained was measuring the wrong thing.

   I flagged in the original text that the resolver had not been run and that the answer might be "correct margin". That caveat was correct and insufficient: ComputeDimensionCutoff is forty lines and it answers the entire question. Reading it was cheaper than filing this.

   Three further claims made while investigating this are retracted in the comments above and are not defects: there is no two-sources-of-truth problem (config_collector_schedules is empty, so the resolver falls through to defaults and nothing disagrees); the purge is not startup-only (_nextPurgeUtc starts at MinValue and advances 24 hours, so two restarts on 09-08 produced two runs — the shape of two data points was reported as a possible defect); and query_store_stats is not a dim-feeding table.

   What actually remains, and it is not this issue

   A product question with no defect in it: plan content is retained 14 days against facts retained 4, which is deliberate, named, and four days old. Leaving it costs roughly 2.3M rows and on the order of 23 GB of gzipped plan XML that no live fact references. Nothing is at risk either way — the GC drains six figures per run inside its cap, errs conservative, and query_plan_dim carries 0.7% dead tuples.

   That is a knob, not a bug, and it is one line to change if the answer is that 14 was not intended. It is being raised directly rather than re-filed here, because an issue asking "is this configured value the one you want" is a question for the person who set it, not a tracked defect.

   The generalisable part, which cost four reads

   A memory note from #2316 — the same class — said to check a knob's updated_at before calling its effect a bug. It was not consulted, because a model had already been built that explained most of the number. A model that explains most of a number is the thing that stops you looking for the rest of it. The residual here was ten days out of fifteen and still read as noise against a story that fit.
5. erikdarlingdata

   closed this as completedon Sep 9, 2026