Repository navigation
Store grows ~2 GB/hr on a 42-server fleet — ~5 days of disk runway, and query_store_stats compresses 2.7x against siblings' 10-30x #2295
Description
Activity
Sized the store on the use2 box. The growth has a named largest contributor, and a measurable share of it is about to go away on its own.
Where the bytes are
Top hypertables by total size, with the index and TOAST split out:
query_store_stats 22 GB idx 2190 MB toast 13 GB query_stats 3917 MB idx 978 MB toast 519 MB perfmon_stats 3135 MB idx 90 MB toast 2329 MB spinlock_stats 2706 MB idx 45 MB toast 2295 MB index_object_stats 2073 MB idx 208 MB toast 1175 MB wait_stats 1846 MB idx 37 MB toast 1542 MB procedure_stats 1268 MB idx 423 MB toast 157 MBquery_store_statsis 22 GB and 13 GB of it is TOAST, which is the interesting part rather than the headline: the fact row itself is narrow, so the out-of-line bytes are the text.Measured directly, over the last 2 hours of collection:
rows = 1,252,243 inline_text_rows = 1,252,243 text_bytes = 755 MB avg = 632 bytesEvery row carries text, and there are ~548k rows/hour arriving (3,287,104 in 6 hours). So ~377 MB/hour is statement text, against the ~2 GB/hour total growth this issue is about — call it ~19% of growth, pre-compression, and it is text that was already stored on the previous snapshot of the same interval. That is precisely what #2150's separate fetch removes: text becomes one row per
query_idinstead of one copy per re-collected snapshot, and Query Store has already de-duplicated it to one statement per id, so the side table should be a rounding error by comparison. #2297 flips it.That does not solve this issue — 80% of the growth is elsewhere and the runway is still short — but it means the largest single object shrinks without a retention or compression decision, so it is worth landing before tuning anything else, and re-measuring afterwards rather than now.
Correction to something I would otherwise have reported
My first pass joined
timescaledb_information.jobsto the continuous aggregates onmaterialization_hypertable_nameand got "no-job" for all 20 of them, which would have been a serious finding: 25.5 GB of materialized aggregates that never refresh. It was my join that was wrong. For a refresh policy,jobs.hypertable_nameholds the continuous aggregate's view name (query_store_stats_hourly), not the materialization hypertable (_materialized_hypertable_N). Read directly, every aggregate has a livepolicy_refresh_continuous_aggregatewith a sanenext_start— hourly tiers on01:00:00, daily tiers on1 day. Nothing is paused. Recording it because the wrong version of that claim is the kind that gets acted on.The aggregate tiers, for the sizing question
They are not free, though:
query_store_stats_interval_hourly 7104 MB query_store_stats_interval_daily 4093 MB query_store_stats_hourly 3070 MB query_store_stats_corrected_hourly 3058 MB query_stats_hourly 2916 MB procedure_stats_hourly 866 MB query_store_stats_corrected_daily 279 MB query_store_stats_daily 276 MB query_stats_daily 257 MB (+ 11 smaller: baselines at 116 MB each, db tiers, daygrain)~25.5 GB of materialized aggregate, of which 17.6 GB is four Query-Store-derived hourly/daily tiers. Worth asking separately whether
_interval_hourlyat 7 GB and_interval_dailyat 4 GB both need their current retention, since together they are a third of the aggregate footprint and larger than every non-Query-Store fact table combined. Not proposing a change here — that is a retention decision, and I would rather re-measure after #2297 lands than tune against a number that is about to move.Compression
query_store_statssits in 5 chunks, 3 compressed (so two live chunks carry the recent bulk), and the compression policy is armed and running hourly with a near-futurenext_start. Same shape onquery_statsandprocedure_stats. The wide-chunk tables (perfmon_stats,spinlock_stats,wait_stats,file_io_statsat 31 chunks, 29 compressed) are behaving as configured. So this is not a stalled-compression story.Found the mechanism, and it corrects the emphasis of my last comment. I said "nothing is paused" — that was true of refresh policies and I should not have left it that broad, because the retention policies on the biggest tiers are deliberately held paused, and that is why the history accumulates.
Three lines from the service log, one per tier:
Retention policy for query_store_stats_interval_hourly HELD PAUSED - query_store_stats_corrected_daily does not yet cover everything it holds, so arming could drop history that rollup has never materialized Retention policy for query_store_stats_hourly HELD PAUSED - query_store_stats_daily ... Retention policy for query_store_stats_interval_daily HELD PAUSED - query_store_stats_daygrain_daily ...So the product is refusing to arm retention on exactly the four tiers I measured as the largest, and refusing for a good reason: the daily rollup does not yet cover the window the hourly tier holds, so arming retention would drop history that nothing has materialized. That is the correct call — dropping unmaterialized history is unrecoverable, holding it costs disk — but it is also a hold with no exit condition being worked, which is what makes it a growth story rather than a policy setting:
query_store_stats_interval_hourly 7104 MB retention HELD PAUSED query_store_stats_interval_daily 4093 MB retention HELD PAUSED query_store_stats_hourly 3070 MB retention HELD PAUSED query_store_stats_corrected_hourly 3058 MB17.3 GB in the three held tiers alone, and it grows for as long as the hold stands. The log line names the way out — "Backfill past the N days horizon" — so the actionable question for this issue is whether the daily rollups can be backfilled to cover what the hourly tiers hold, after which retention arms itself and those tiers stop being unbounded. That is a different fix from compression or from trimming a retention window, and it is the one that changes the trajectory.
I have not attempted the backfill — it writes to the store on a prod monitoring box, so it is Erik's call, and I would want to know the intended coverage horizon before running it rather than inferring one from the current data.
For the record on the other half: the #2150 flip is now live on this box and the inline-text share of growth is gone. Four minutes after the restart the side table held 437,785 rows / 215 MB; ten minutes later 675,056 rows / 350 MB, which is the one-time backfill of historical
query_ids working through the per-database watermark rather than ongoing growth — it should plateau, and I will say so explicitly once it has rather than assuming it. Newly collected fact rows carry no inline text at all (146,479 rows since the process start, 0 with text), so the ~377 MB/hour is no longer being spent.Erik authorized scope-and-run on the backfill; scoping is underway. Fresh clock first, because it changes the urgency: store 190.6 GB, box free 257.5 GB (was 269.5 on 8-16). Whole-store daily deltas: 8-14 −2.73 GB, 8-15 −1.21, 8-16 +15.57 (the burst this issue measured), 8-17 +1.73 in 15 hours (~2.8 GB/day pace). The #2150 text flip deployed yesterday removed the inline-text share, and today's rate puts runway at ~90 days, not 5 — so this is now a structural fix on a calm clock, not an emergency. The plan (backfill the daily rollups past the 90/90/7/10-day horizons the service log names, restart to arm retention) will follow here before execution, including expected duration and the transient disk cost of materializing dailies before the hourlies drop.
Scoping done — the product ships the exact tool for this:
--backfill-rollups, with source-relative convergence targeting precisely what the arming gate measures, its own preflight disk arithmetic, and refresh-collision retries so it runs with the service up. Dry-run on the box just now:query_store_stats_daily: 2026-08-04 -> 2026-08-06 (2 buckets), est 63.1 MB query_store_stats_corrected_daily: 2026-08-04 -> 2026-08-05 (1 bucket), est 27.9 MB query_store_stats_interval_daily: 2026-08-04 -> 2026-08-05 (1 bucket), est 409.3 MB query_store_stats_daygrain_daily: 2026-08-05 -> 2026-08-17 (12 buckets), est 11.4 GB (UNCALIBRATED upper bound) Total: 11.9 GB est; ~13 min; free 257.5 GB, required 24.9 GBEvery hourly tier already covers; the whole job is four daily-tier stretches, and the scary-looking 11.4 GB is the deliberate uncalibrated bound on a composer-grain daily whose measured ratio is ~1% of source — actual cost will be far smaller. Executing now; restarting the service immediately after (the verb's own closing warning: an armed hourly policy trims freshly-built coverage ~daily, so the restart must not wait), then confirming the startup line reads
0 held paused pending backfill.Done.
--backfill-rollupsran in ~3 minutes (well under the 13-minute budget — the 11.4 GB daygrain estimate was indeed the deliberate upper bound on a ~1%-of-source object), every rollup reported COVERED against exactly what the arming gate measures, and the restart confirmed it:13:09 17/17 retention policies in place, 13 armed, 4 held paused pending backfill 15:24 17/17 retention policies in place, 17 armed, 0 held paused pending backfillZero HELD PAUSED lines since. The unbounded-growth mechanism this issue identified is closed: the four query_store tiers now purge on their 90/90/7/10-day horizons, and the first purge reclaims the raw history in one pass — I'll post the store-size delta once it has fired. Combined with the #2150 flip removing the inline-text share (~2.8 GB/day current growth vs the +15.6 GB burst day this was filed on), the growth story here is resolved; what remains of store size is the query_plan_dim conversation (121 GB, 63% of the store), which is its own issue.
Closing — the growth mechanism this issue is about is fixed and verified, and the one promised follow-up turns out not to be a thing that can fire soon, for a good reason.
Verified on-box just now (
timescaledb_information.job_stats): all 17 retention policies reportSuccess. The four query_store tiers ran their first armed pass at 15:24 with the restart; the short-horizon tiers (7/10-day) purged then. The 90-day tiers — the ones holding the bulk of raw history — cannot reclaim anything until the store's history actually reaches 90 days of age, which lands early October. So there is no near-term "first-purge store-size delta" to report: the horizons are armed, the purges run green, and the reclaim arrives on the calendar, not on a job I can watch this week.Where the numbers ended up:
- Growth: ~2.8 GB/day at last measure (vs the +15.6 GB burst day this was filed on), after the [BUG] 3.4.0 query_store collector runs 37–100 min on Azure SQL DB (3.3.0 median: 4.8 s) — starves all other collectors #2150 text flip removed the inline-text share. Runway ~90 days and now bounded by the armed horizons.
- Store: 196 GB today; the rollup backfill materialized its dailies well under the 11.9 GB estimate, and 17/17 armed with zero HELD PAUSED since.
- query_store collector costs 40-110s per run on multi-tenant primaries — the read has no interval watermark, so every cycle pays for the whole window #2312's fix (merged today) also cuts the inflow side: ~two thirds of the open-interval re-snapshots stop being written.
What remains is composition, not growth, and it's split into #2316:
query_plan_dimat 121 GB (63% of the store, a plain table — no compression, no retention) andquery_store_statscompressing at 2.7x against siblings' 10-30x.
Found during the 2026-08-16 dogfood watch on
prod-sql-use2-monitor-01(42 SQL Servers, managed PG store, schema v74).The growth is steady, not a burst
Today's running daily delta, sampled hourly from
get_store_metrics:~2 GB/hour, sustained across three hours. On-disk
pg-datais lumpier (+4.25 GB one hour, +0.53 the next — WAL/checkpoint cycles), but disk free tracks the same underlying rate: 278.9 → 269.5 GB over ~4.3 h, ≈ −2.2 GB/hr.At that rate ~269 GB free is roughly 5 days of runway. Not urgent today; needs a decision this week.
What makes it a change rather than a level: the store was shrinking on 8-14 (−2.73 GB) and 8-15 (−1.21 GB) as the use1/use2 migration double-population aged out. That shed finished today and revealed the underlying rate.
Where it is going
Hour-over-hour deltas at 16:55, largest first:
The compression asymmetry, which is the interesting part
Two separate things here, and they should not be conflated:
query_plan_dimat 116 GB is 65% of the store and showschunks=None, ratio 0.0. That is expected, not a defect — it is a plain table carrying application-level gzip (query_plan_gz, V54), so TimescaleDB compression neither applies nor is needed. Worth stating explicitly because the numbers look alarming next to the others.query_store_statscompressing at 2.7x is the anomaly. Its numeric siblings reach 10–30x. The difference is that this hypertable carries inlinequery_sql_text—nvarchar(max)statement text repeated on every runtime-stats interval row of the same query — and text compresses far worse than the numeric columns that make upperfmon_statsorwait_stats. It also shows only 5 compressed chunks against 31 for the fully-compressed tables.Why this matters for #2150
This is the same inline-text cost as #2150, measured on the storage side rather than the collector side. #2150 removes
query_sql_textfrom the runtime stream and stores it once perquery_idincollect.query_store_textinstead of once per interval row. So finishing that work should:query_store_stats(the repetition is the bulk of it), andThat makes #2150 a storage fix as well as a latency fix, which was not the argument for it before. The V74 storage and the fetch are already merged and inert; the remaining step is the reader conversion and flipping
FetchQueryTextSeparately.Not established
Whether the 4 retention policies HELD PAUSED pending backfill (
query_store_stats_hourly,_interval_hourly,_corrected_hourly,_interval_daily) are contributing. They are a plausible second cause — a paused policy prunes nothing — butget_store_metricsreports those as job rows without sizes, so the CAGG tiers' own sizes are not visible in it and the hypothesis is untested. Sizing those four tiers directly is the next diagnostic.Suggested order
query_store_statssize and ratio. That is already-built work with an independent justification.