Ring-buffer collectors re-derive their watermark with an unbounded MAX that decompresses every retained chunk each cycle; persist it at write time instead #4197

Activity

1. erikdarlingdata

   added

   enhancementNew feature or requestNew feature or request

   client-siteOwned by the client-site agents (other laptop). Local sessions never pick these up.Owned by the client-site agents (other laptop). Local sessions never pick these up.

   in-progressActively being worked by a local session or its agents (PR open or in flight)Actively being worked by a local session or its agents (PR open or in flight)

   on Sep 25, 2026
2. erikdarlingdata commented on Sep 25, 2026

   

   erikdarlingdata

   on Sep 25, 2026

   OwnerAuthor

   More actions

   Lane S3b, diagnosis-only follow-up on QueryStoreBackfill.GetCandidateDatabasesAsync (the "same class, different read" this issue also names).

   The shipped predicate is not always inert - it depends on whether continuous aggregates are active.

   GetCandidateDatabasesAsync's SQL already carries collection_time > $2 with CandidateWindow = 7 days. Its inline comment says this is inert because CandidateWindow is "wider than raw retention." That claim is precise, not vague: RetentionTierRouter.RawMaxAge (raw query_store_stats retention once continuous aggregates take over history, TimescaleSupport.RawRetentionSpan = 4 days minus a routing margin) is narrower than the 7-day window. On a store with continuous aggregates active, query_store_stats never holds data older than ~4 days, so every row already satisfies the 7-day predicate and TimescaleDB has nothing to exclude - matching the issue's own production EXPLAIN ("shared hit=176417 across all chunks").

   I confirmed this on a rig (fresh TimescaleDB, PgMigrations + TimescaleSupport.CreateHypertableSql/EnableCompressionSql, 33 one-day chunks, compressed past 1 day): with NO continuous-aggregate drop-to-4-days applied, the same predicate DOES prune (8 of 33 chunks touched, not 33; execution buffers 901-1030 vs 1633 unbounded; planning buffers 74 vs 29015 for a literal-constant bound). That setup doesn't match the shape PlainStoreHorizon vs RollupStoreHorizon implies for a store with rollups, so it doesn't contradict the comment - it just isn't the configuration the comment is about. I did not have a way to reproduce the WITH-continuous-aggregates, 4-day-raw-retention shape without a multi-day backfill/rollup cycle actually running, which is beyond a single-session rig diagnosis.

   Net: the comment is accurate for the rollup-store shape, and the existing predicate genuinely cannot help there - narrowing CandidateWindow further only makes an already-inert bound more inert. A real fix needs the issue's own suggested shape (read the candidate list from a maintained per-server set or the latest database_states snapshot instead of DISTINCT over raw), which is a design-level change: what maintains that set, when it's refreshed, and how a database that stops shipping rows ages out of it. Leaving this open under #4197, as PR #4209 already does (title: "Part of #4197").
3. erikdarlingdata

   added a commit that references this issue on Sep 25, 2026

   Bound ring-buffer watermark reads to a recent window, fall back to un…

   ...

   4fd3ceb
4. erikdarlingdata commented on Sep 25, 2026

   

   erikdarlingdata

   on Sep 25, 2026

   OwnerAuthor

   More actions

   Same three reads after the store host's RAM upgrade (2026-09-25 03:20Z). The OS file cache now serves the store's reads at ~7 µs per 8 KB block, and EBS reads fell ~92% against the same hours the day before. This is a pg_stat_statements delta over 83 min (04:52–06:15Z) on the Query Store–ingesting store, 43 servers:

   read	calls	mean	buffers touched
   QueryStoreBackfill SELECT DISTINCT database_name FROM query_store_stats …	683	200 ms	1,707 GB
   SELECT MAX(event_time) FROM default_trace_events WHERE server_id = $1	684	99 ms	40 GB
   SELECT MAX(run_datetime) FROM job_history WHERE server_id = $1	684	82 ms	25 GB

   Warm, the means drop (the DISTINCT went from 915 ms to 200 ms), but the work doesn't: every cycle still walks every retained chunk. The DISTINCT alone touches ~2.5 GB of buffers per call, about 1.2 TB an hour. Memory hides this read's I/O but not its CPU or its growth with retention, so the persist-at-write-time fix still stands.
5. erikdarlingdata commented on Sep 25, 2026

   

   erikdarlingdata

   on Sep 25, 2026

   OwnerAuthor

   More actions

   Lane D3: design for the rest of #4197. Design only: no product code and no PR. Everything below was measured on a local rig.

   In short

   • The candidate DISTINCT (part a): bound it at the backfill's own floor, floorLimit, which is 23 hours back on a store with rollups. Then add the databases named by hole keys, which the method already loads. No migration. On the rig's heavy server, this took the read from 206 ms and 21,411 buffers to 1.7 ms and 792 buffers. The read now stops at the two uncompressed chunks. A persisted per-server list is cheaper still (0.26 ms, 4 buffers). But the gap is under 2 ms a call, and closing it needs writer changes. A loose index scan is ruled out: across the compressed chunks it took 38.8 s.
   • Persisting the ring-buffer watermarks (part b): not yet. After Bound the ring-buffer watermark read to a recent window, fall back to unbounded only on a miss (#4197) #4209, a hit costs 0.3-0.5 ms against 0.08 ms for a persisted lookup. Only the miss path still pays the old cost, and a miss happens only when a server wrote no row in the last 6 hours. The field miss rate is unknown, because the field numbers predate Bound the ring-buffer watermark read to a recent window, fall back to unbounded only on a miss (#4197) #4209. Measure it first. If it matters, persist the watermark in collector_state, which needs no migration.
   • Lite: it has the same reads (the same 7-day DISTINCT, and unbounded per-server MAX watermarks), but on DuckDB they cost 1.5-13 ms. Twin the candidate change for parity. Lite gains nothing from persisting.

   Rig

   • PostgreSQL 18.6 with TimescaleDB 2.30.1, in UTC.
   • query_store_stats, default_trace_events, job_history and collector_state come from a store the Darling migrations built (schema version 143). They have the same columns and indexes, 1-day chunks on collection_time, and compression segmented by server_id. TimescaleDB chose the order collection_time DESC, query_hash itself.
   • query_store_stats: 43 servers, each with 10 databases and 12 rows per 15-minute cycle, for 4 days (1.99M rows). That makes 5 chunks. The 3 older chunks are compressed, as the 1-day policy leaves them. One heavy server was added: 100 databases and 50 rows per cycle (1.92M rows).
   • default_trace_events and job_history: 30 days each, in 31 chunks, 29 of them compressed. One quiet server has written no row for 8 hours, to exercise the Bound the ring-buffer watermark read to a recent window, fall back to unbounded only on a miss (#4197) #4209 miss path.
   • Every read was run as PREPARE with the types Npgsql binds (int4, timestamp without time zone), then EXPLAIN (ANALYZE, BUFFERS) EXECUTE. That gives a custom plan, which is what an unnamed Npgsql statement gets. The cache was warm, and each time is the median of 3 runs. Buffers are shared hit plus read.

   1. The candidate DISTINCT in QueryStoreBackfill.GetCandidateDatabasesAsync

   What the backfill needs from the list

   RunServerSliceAsync walks the list in name order. For each database it does one of these:

   1. A hole key exists: it services the hole, or deletes it once holeTo <= floorLimit.
   2. A done key exists: it skips the database.
   3. Otherwise it reads MIN(last_execution_time). If there are no rows, it skips. If the minimum is at or below floorLimit, it writes the done key. If not, it digs the tail.

   In steady state every database is done and there are no holes, so the list is only walked and skipped. Only two kinds of database can ever have work:

   • (a) a database with a hole key. The state that names it is already in state, which the method loads just before the DISTINCT.
   • (b) a database with no done key, whose first contact came after floorLimit. Its oldest row then has collection_time >= last_execution_time > floorLimit, so any read bounded at floorLimit finds it.

   floorLimit is now - HorizonFor(hasContinuousAggregates):

   • On a store with continuous aggregates, that is RollupStoreHorizon, which is min(RawMaxAge = 3 days, RefreshReachableDepth = 23 hours), so 23 hours.
   • Without them, it is PlainStoreHorizon, which is 3 days.

   With 1-day chunks and compress_after of 1 day, the two newest chunks are always uncompressed. A 23-hour bound therefore never touches a compressed chunk. If the compression job lags, more chunks stay uncompressed, which does no harm.

   Measured

   Chunks are shown as uncompressed + compressed. Buffers are shown as execution / planning.

   Shape	Server	Chunks	Planning	Execution	Buffers
   Shipped DISTINCT, 7-day bound	standard (10 DBs)	2 + 3	0.88 ms	11.4 ms	512 / 45
   Same, 3-day bound (PlainStoreHorizon)	standard	2 + 2	0.79 ms	9.0 ms	489 / 37
   Same, 23-hour bound (RollupStoreHorizon)	standard	2 + 0	0.31 ms	0.23 ms	88 / 21
   Loose index scan (recursive CTE), 7-day bound	standard	2 + 3	1.69 ms	104 ms	1,349 / 90
   Loose index scan, 23-hour bound	standard	2 + 0	0.62 ms	0.34 ms	84 / 42
   Persisted list: read (new table, primary key)	standard	none	0.08 ms	0.03 ms	3 / 2
   Persisted list: writer upsert of 10 databases	standard	none	0.08 ms	0.10 ms	50 / 4
   Shipped DISTINCT, 7-day bound	heavy (100 DBs)	2 + 3	0.90 ms	206 ms	21,411 / 45
   Same, 3-day bound	heavy	2 + 2	0.75 ms	162 ms	20,114 / 37
   Same, 23-hour bound	heavy	2 + 0	0.28 ms	1.71 ms	792 / 21
   Same, 1-hour bound (reference only)	heavy	1 + 0	0.24 ms	0.96 ms	391 / 15
   Loose index scan, 7-day bound	heavy	2 + 3	1.44 ms	38,811 ms	516,191 / 90
   Loose index scan, 23-hour bound	heavy	2 + 0	0.60 ms	2.06 ms	788 / 42
   Persisted list: read	heavy	none	0.09 ms	0.17 ms	4 / 2

   The heavy server's shipped read (206 ms) matches the field mean (200 ms). The field read touches far more bytes per call: about 2.5 GB, against 167 MB here. So the ratios carry over, and the absolute numbers do not.

   Why the loose index scan fails

   Inside a compressed chunk there is no btree on database_name. The compressed chunk's only index is (server_id, _ts_meta_v2_first_collection_time), and its sparse index is a bloom filter on collection_time and query_hash. So each recursive step unpacks the server's whole segment again, once per database. On the uncompressed chunks it runs level with the plain bounded DISTINCT, so it adds nothing there either.

   Correctness of each option

   Case	A: bound at floorLimit, plus hole keys	B: persisted per-server list	C: loose index scan
   A database stops collecting	Drops out once its last row is older than floorLimit. That loses nothing. Its first contact is older than its last row, so it has no tail left to dig.	Filter on last_seen > floorLimit. The result matches today's.	The same as today.
   A newly added database	Its first-contact rows sit in the newest chunk, so the read finds it.	The writer upserts it in the COPY's transaction, so it cannot be listed before its rows commit.	Found.
   A restart that reseeds	No in-memory state, so nothing changes.	The table is persistent, so nothing changes.	Nothing changes.
   A removed server	The worker only runs for configured servers, so there is no read.	Its rows stay in the list. They are never read and take a few bytes. They need a purge when the server is removed, or they can stay inert.	No read.
   Continuous-aggregate routing	The bound is floorLimit, so it follows HorizonFor(flag): 23 hours with rollups, 3 days without.	Reads floorLimit, so it is exact under both.	Exact under both.
   A hole on a database that went quiet	Kept by the union with hole keys. Today such a hole is serviced only while the database still has raw rows. With the union it is serviced or expired, never left behind.	Needs the same union.	Needs the same union.
   Migration	None	A new table, or collector_state keys with no migration. Also a one-off seed, and a writer upsert inside the Query Store COPY transaction, which #3953 already opens.	None

   Stores without continuous aggregates

   There are two ways to handle that case:

   • Bound at HorizonFor(flag), which is exact. On the heavy server it still reads 2 compressed chunks, at 162 ms.
   • Bound at 23 hours always. That is cheap, but it differs from today in one case. A database whose tail is still being dug can go silent for more than 23 hours. It then stops being dug, and the rest of its tail expires. Today's code keeps digging it.

   I recommend the exact form. It costs nothing on the field store, which runs rollups, and it keeps the behaviour identical on every store.

   Recommendation for part (a)

   Use option A with HorizonFor(flag), and no migration. Compute floorLimit once, before the read. Bind it as the read's lower bound, then merge in the databases named by HoleKeyPrefix keys. Also replace the comment that says the predicate is "inert".

   A sibling read with the same problem

   GetStoredFloorAsync (MIN(last_execution_time), unbounded) cost 68 ms and 19,417 buffers per call on the heavy server. It runs once a tick for each database that is not done yet. In steady state that is never, but a new database pays it every tick while its tail is dug. A cheap exact form exists:

   • If any row has collection_time <= floorLimit, the database is done. That is an EXISTS with a limit of 1, which stops at the first batch.
   • Otherwise, the MIN over collection_time > floorLimit is the true minimum, because every row is newer than that.

   This is optional for the same lane.

   2. Are persisted ring-buffer watermarks still worth it after #4209?

   Measured on the standard server. dte is default_trace_events, and jh is job_history.

   Read	Chunks	Planning	Execution	Buffers
   Before #4209: unbounded MAX(event_time), dte	2 + 29	4.26 ms	2.40 ms	482 / 498
   Before #4209: unbounded MAX(run_datetime), jh	2 + 29	4.58 ms	3.16 ms	720 / 498
   #4209 hit (6-hour probe), dte	1 + 0	0.38 ms	0.09 ms	61 / 36
   #4209 hit, jh	1 + 0	0.27 ms	0.08 ms	99 / 36
   #4209 miss on the quiet server, dte (probe, then unbounded)	1, then 2 + 29	0.34 + 5.04 ms	0.05 + 2.74 ms	544 / 534
   #4209 miss, jh	1, then 2 + 29	0.32 + 3.58 ms	0.07 + 2.62 ms	818 / 534
   Possible second probe at 23 hours on the quiet server, dte	2 + 0	0.73 ms	0.33 ms	233 / 54
   The same, jh	2 + 0	0.88 ms	0.53 ms	373 / 54
   Persisted lookup (collector_state primary key)	none	0.07 ms	0.01 ms	3 / 2

   On the heavy server, the hit cost 0.4 ms and 9-15 buffers, and the unbounded read cost 8 ms.

   What this says

   • A hit is already within about 0.3 ms and about 100 buffers of a persisted lookup.
   • A miss costs the full pre-Bound the ring-buffer watermark read to a recent window, fall back to unbounded only on a miss (#4197) #4209 read plus the probe. In the field that is about 99 ms and 58 MB for dte, and 82 ms and 37 MB for jh.
   • Planning 31 chunks is more than half of that read's time on the rig.

   So persisting pays only on misses, and in proportion to how often they happen. A miss means the server wrote no row to that table in 6 hours. That is normal for a quiet default trace, and for a server whose jobs run nightly or weekly. So the miss rate can be large. The field numbers cannot tell us, because they come from a build before #4209. After #4209 deploys, pg_stat_statements shows the rate directly: the bounded and unbounded statements have different texts. Divide the unbounded statement's calls by the bounded one's.

   The cheaper step, if misses matter

   Add a second probe at 23 hours before the unbounded fallback. It stays on the uncompressed chunks (1-1.4 ms), needs no writer change, and returns the same value (the #4209 argument, extended one step). It covers servers quiet for 6-23 hours, such as nightly jobs. It does not cover servers quiet for longer than that.

   Persisting at write time, if it is still needed after that

   Store the value as a collector_state key (server_id, collector_name, state_key), which needs no migration. The writer's side:

   • Transaction scope. Upsert the key in the same transaction as the COPY, with no savepoint, as GREATEST(existing, batch max over the rows actually copied). Then the watermark cannot pass rows that did not commit. Today these collectors COPY in autocommit mode. Only the diverting collectors and Query Store open a transaction. So this adds a transaction and one statement to every batch they write.
   • Why no savepoint. In the PLAN_REGRESSION's read deduplicates the whole raw Query Store slice every pass; keep the latest snapshot per interval as it is written #3953 pattern, a failed upsert rolls back alone while raw still commits. That leaves the watermark behind the stored rows, and the next cycle collects those rows again as duplicates. A failed upsert must fail the whole batch, and the batch is then retried like any COPY failure.
   • Concurrent writers and failed batches. GREATEST under the row lock does not depend on commit order. An older batch, such as a replay after an outage, cannot move the watermark back. A failed batch rolls back both the rows and the key.
   • A writer that bypasses the batch writer. Any path that inserts into these tables some other way leaves the key too low, which again means duplicates. A replay, a drain or an import could each do it. The build lane needs a census of every writer of each table. The read must fall back to the Bound the ring-buffer watermark read to a recent window, fall back to unbounded only on a miss (#4197) #4209 derivation when the key is missing, and seed the key from it.
   • Retention purges. The key survives drop_chunks, and the derived MAX does not. On a server with no rows left in retention, today's read returns null and falls into the first-run window. The persisted key keeps the real watermark. That is a behaviour change, and the right one: no events are skipped.
   • A server removed and added back. Removing a server does not delete its collector_state rows. The runtime deletes there only prune single keys, or the keys of dropped databases. So the watermark key must sit outside those per-database prefixes. With the same server_id, the old key and the old rows agree, as today. A new server_id starts from nothing, as today.
   • A restart. The key is persistent, so no reseed is needed. The upgrade case (no key yet) uses the fallback seed above.

   3. Lite parity

   Lite has both reads:

   • RemoteCollectorService.QueryStoreBackfill runs the same SELECT DISTINCT database_name ... collection_time > now - 7 days, and the same unbounded MIN.
   • GetLastCollectedTimeAsync and GetLastCollectedInstanceIdAsync run an unbounded SELECT MAX(col) FROM table WHERE server_id = $1.

   Both read the hot DuckDB tables, which hold 7 days (ArchiveService, hotDataDays: 7).

   I timed them on a file-backed DuckDB 1.4.4 database: 20 servers and 7 days, at the rig's standard per-server rates (1.6M query_store_stats rows). Each time is the median of 25 warm runs.

   Read (Lite)	Median
   MAX(event_time), dte, unbounded (today)	1.54 ms
   The same with a 6-hour bound	1.43 ms
   MAX(instance_id), jh, unbounded (today)	1.50 ms
   DISTINCT database_name, 7-day bound (today)	12.7 ms
   The same with a bound of about one day	5.7 ms
   MIN(last_execution_time) for one database	3.3 ms

   DuckDB has no chunks to decompress, and its row groups mix servers, so a server_id filter prunes nothing. Every one of these reads is a scan of a few columns, measured in milliseconds. Recommendation: twin the candidate change (the bound plus the hole-key merge) so the two backfills keep the same rules. Do not persist Lite's watermarks: there is nothing to win.

   Proposed lanes

   1. Lane A runs on the default tier. It covers Darling and its Lite twin, with no migration:

      • bound the candidate read at floorLimit, merge in the hole-key databases, and fix the "inert" comment.
      • optionally, the exact EXISTS-then-bounded-MIN form of GetStoredFloorAsync.
      • tests:
        • a SQL-shape pin that fails on the unbounded form.
        • a live test on a seeded hypertable with compressed chunks. It checks that a hole on a quiet database is still serviced, that a done database with no recent rows drops out, and that a new database is found.
        • a plan assertion that only uncompressed chunks are read at 23 hours.
        • the Lite twin tests.

      About 60-90 calls.

   2. Measurement (coordinator, no code): after Bound the ring-buffer watermark read to a recent window, fall back to unbounded only on a miss (#4197) #4209 is on the production store, take a pg_stat_statements delta over at least 60 minutes. Split the bounded and unbounded MAX texts for dte and jh, and add the candidate read after Lane A ships.

   3. Lane B runs if step 2 shows the misses matter. It builds the 23-hour second probe first, which is small and needs no writer change. If that is not enough, it then persists the watermark in collector_state inside the COPY transaction, with the writer census above. Darling only.

   Decisions for Erik

   1. Part (a): approve option A over the persisted list and the loose index scan. Option A is a bound at floorLimit plus the hole-key merge, with no migration.
   2. Stores without continuous aggregates: choose the exact bound HorizonFor(flag) or 23 hours always. I recommend the exact bound, which still costs 162 ms on a heavy store of that kind. The 23-hour bound is cheap, but it can drop the rest of a tail when a database goes quiet mid-dig.
   3. Lite: twin Lane A's change in the same PR (recommended), or leave Lite as it is.
   4. GetStoredFloorAsync: fold its exact bounded form into Lane A, or file it separately.
   5. Part (b): defer persisting until the post-Bound the ring-buffer watermark read to a recent window, fall back to unbounded only on a miss (#4197) #4209 miss-rate measurement (recommended), or build it now. If it is deferred, name the threshold that starts Lane B. An example: the unbounded MAX texts still above some number of GB an hour.
   6. If persisting is built: approve storing it in collector_state, which needs no migration. Also approve a transaction around the COPY for the collectors that persist. They commit without one today.

   🤖 Generated with Claude Code

   https://claude.ai/code/session_01TszxYhJJbTEh4LrZ56NYo3
6. erikdarlingdata

   removed

   in-progressActively being worked by a local session or its agents (PR open or in flight)Actively being worked by a local session or its agents (PR open or in flight)

   on Sep 25, 2026
7. erikdarlingdata commented on Sep 25, 2026

   

   erikdarlingdata

   on Sep 25, 2026

   OwnerAuthor

   More actions

   Ruling on the design's six decisions. These are technical choices inside the issue's scope, so the coordinator makes them:

   1. Part (a) uses option A: bound the candidate read at floorLimit, then merge in the databases named by hole keys. No migration. Replace the "inert" comment.
   2. A store without continuous aggregates uses the exact bound, HorizonFor(flag). The 23-hour bound is cheaper, but it can drop the rest of a tail when a database goes quiet mid-dig.
   3. Lite gets the same candidate change in the same PR, so the two backfills keep the same rules. Lite does not persist anything.
   4. The exact form of GetStoredFloorAsync goes in the same lane: an EXISTS at floorLimit first, then the bounded MIN.
   5. Part (b), persisting the watermarks, waits for a field measurement after Bound the ring-buffer watermark read to a recent window, fall back to unbounded only on a miss (#4197) #4209 is on the production stores. The measurement is a 60-minute pg_stat_statements delta that splits the bounded and unbounded MAX statements for default_trace_events and job_history. If the unbounded statements read more than 1 GB of buffers an hour on either store, Lane B starts. It builds the 23-hour second probe first. If the probe is not enough, it then persists the watermarks.
   6. Decision 6 waits for Lane B.

   Lane A's tests are the design's list:

   • a SQL-shape pin that fails on the unbounded form;
   • a live test on a seeded hypertable with compressed chunks. A hole on a quiet database is still serviced, a finished database with no recent rows drops out, and a new database is found;
   • a plan check that a 23-hour read touches only uncompressed chunks;
   • the Lite twin tests.

   🤖 Generated with Claude Code

   https://claude.ai/code/session_01TszxYhJJbTEh4LrZ56NYo3
8. erikdarlingdata

   added

   in-progressActively being worked by a local session or its agents (PR open or in flight)Actively being worked by a local session or its agents (PR open or in flight)

   on Sep 25, 2026
9. erikdarlingdata

   added 5 commits that reference this issue on Sep 25, 2026

   Add Darling live tests for the #4197 backfill candidate bound

   ...

   d9b3bd9

   Add Lite DuckDB fixture tests for the #4197 backfill candidate/floor …

   ...

   b0f1922

   Fix two census pins the #4197 source change left stale

   ...

   2efd2bf

   Merge #4306's stacked ExpectedSiteCount summaries into one

   ...

   3bc7f44

   Bound the Query Store backfill candidate read at its horizon (#4197) (#…

   ...

   bf865ef
10. erikdarlingdata commented on Sep 25, 2026

    

    erikdarlingdata

    on Sep 25, 2026

    OwnerAuthor

    More actions

    Part a (the candidate read bounded at the backfill's horizon, the hole-database merge and the exact floor form, in Lite and Darling) merged to dev in #4306 (bf865ef). Part b, persisting the ring-buffer watermarks, waits for a field measurement after #4209, so this issue stays open.
11. erikdarlingdata commented on Sep 26, 2026

    

    erikdarlingdata

    on Sep 26, 2026

    OwnerAuthor

    More actions

    Claude posting for Erik Darling

    Part (b) field reading: the unbounded fallback no longer runs, but the bounded watermark read still moves about 122 GB of buffers an hour. So part (b) goes ahead, as a watermark cached in memory per server.

    Production SQL Server store (43 servers), nightly build 470, which includes #4209. It's a 60-minute pg_stat_statements delta, 2026-09-26 04:31–05:31Z:

    statement	calls/h	mean ms	buffers (hit+read) GB/h
    SELECT MAX(run_datetime) FROM job_history WHERE server_id = $1 AND collection_time > $2	506	~63	~56
    SELECT MAX(instance_id) FROM job_history WHERE server_id = $1 AND collection_time > $2	506	~63	~56
    SELECT MAX(event_time) FROM default_trace_events WHERE server_id = $1 AND collection_time > $2	503	35	10.1
    unbounded MAX (the quiet-server fallback)	0	n/a	n/a

    • The unbounded fallback is gone. It ran about 500 times an hour on build 467.
      • That fallback is the miss path, so the second probe the 2026-09-25 ruling put first has nothing left to fix.
    • The bounded read is the cost now.
      • Each job_history call touches about 14.6k blocks (about 117 MB).
      • The three statements together move about 122 GB of buffers an hour. This issue's bar was 1 GB an hour.

    The part (b) fix (coordinator ruling, 2026-09-26):

    • Cache the watermark in memory, per server and collector.
    • Seed it from one bounded MAX at first use or after a restart.
    • Advance it from each successful run's own newest collected value.
    • Drop it on a collector fault, or when a server is re-added.
    • In the steady state, no MAX runs. Nothing is persisted, so the writer's transaction doesn't change and no migration is needed.
    • Darling only: Lite's equivalent reads cost milliseconds on DuckDB.
12. erikdarlingdata

    added 9 commits that reference this issue on Sep 26, 2026

    Wire the in-memory server watermark cache into DarlingCollectorRunner (…

    ...

    6eba33f

    Pin ServerWatermarkCache and the cache-eligibility defaults (#4197 pa…

    ...

    48a4b90

    Extract the watermark-cache resolve/advance seam for live testing (#4197

    ...

    8dd79ac

    Add runner-level live pins for the watermark cache (#4197 part b)

    fabd7ce

    Fix #4197 watermark cache microsecond truncation and run live pins (#…

    ...

    53fa63c

    Move the #2797 dispatch gate into ResolveServerWatermarkAsync (#4197 …

    ...

    5f1034a

    Count the watermark reads without pg_stat_statements (#4197)

    d1d032f

    Fix the counting logger's accessibility in the watermark live tests (#…

    ...

    e4bbe25

    Cache the server-scoped watermark read across a runner's lifetime (#4399

    ...

    66364bd
13. erikdarlingdata commented on Sep 26, 2026

    

    erikdarlingdata

    on Sep 26, 2026

    OwnerAuthor

    More actions

    Claude posting for Erik Darling.

    #4399 is merged (66364bd). The collector runner caches the server-scoped watermark per (server, collector) for its lifetime, so the repeated MAX() reads drop to one seed per pair. Any fault in a run invalidates the entry.
14. erikdarlingdata commented on Sep 26, 2026

    

    erikdarlingdata

    on Sep 26, 2026

    OwnerAuthor

    More actions

    Claude posting for Erik Darling

    Closing: both parts have shipped.

    • Part (a): the candidate read is bounded at the backfill's horizon, with the hole-database merge and the exact floor form, in Lite and Darling. That's Bound the Query Store backfill candidate read at its horizon (#4197) #4306.
    • Part (b): the server-scoped watermark is cached in memory per server and collector, so a steady-state collector run issues no MAX. That's Cache the server-scoped watermark read across a runner's lifetime (#4197) #4399.
      • On a simulated field hour with 43 servers, job_history's MAX calls fell from 1,032 to 86 (the fill after start only), and its buffers from about 43 GB to 3.6 GB.
      • The live equivalence pin caught and fixed a sub-microsecond drift that would have put the cache ahead of the store.
    • Decision 6 (persist in collector_state) no longer applies, because nothing is persisted.
    • Deliberately not cached, as the PR body explains:
      • pg_cpu_utilization, because a second writer also fills it;
      • cpu_utilization, because of its UTC-twin frame.
      • Neither was a measured cost. Open an issue if a field reading ever shows one.
15. erikdarlingdata

    closed this as completedon Sep 26, 2026
16. erikdarlingdata

    removed

    in-progressActively being worked by a local session or its agents (PR open or in flight)Actively being worked by a local session or its agents (PR open or in flight)

    on Oct 12, 2026