Payload-dim upsert still writes on every sighting: ON CONFLICT DO UPDATE locks each presented row before its one-hour guard skips the UPDATE #4249

Related: #1801 (the lock-order requirement this keeps), #4130 (plan-dim purge)

Problem

PayloadDimensions.UpsertSql is designed so that "the second and every later sighting of the same content writes nothing". Its conflict arm refreshes last_seen only when the stored stamp is more than an hour old:

ON CONFLICT (digest) DO UPDATE SET last_seen = EXCLUDED.last_seen
WHERE <dim>.last_seen < EXCLUDED.last_seen - INTERVAL '1 hour'

The guard does stop the UPDATE. It doesn't stop the write. PostgreSQL locks every conflicting row before it evaluates that WHERE. The INSERT reference says the rows the condition rejects are not updated, but "all rows will be locked when the ON CONFLICT DO UPDATE action is taken". So each re-sighted digest still produces:

• a Heap/LOCK WAL record, and a dirtied heap page (the tuple's xmax changes);
• with data checksums on (the managed store runs initdb --data-checksums), an 8 KB full-page image the first time that page is touched in each checkpoint cycle;
• a hint-bit change when the next reader finds the lock-only xmax, which is a second page image (FPI_FOR_HINT) if that reader gets to the page first in a new cycle.

Every collection cycle presents the batch's plans and texts again, so the hot pages of query_plan_dim and query_text_dim are re-imaged roughly every checkpoint (5 min) and written back to disk just as often.

Measured

Production SQL Server stores, build 3.8.0-nightly.20260924.467, 2026-09-25. Store A: 43 servers, ingests Query Store. Store B: 42 servers.

• Store B, pg_stat_statements delta over a steady 55.6 min (04:45–05:41Z): the query_plan_dim upsert ran 2,520 times. It wrote 671,162 WAL records, 109,861 of them page images, 0.85 GB of WAL: 23.2% of all WAL the store generated in the window (3.65 GB). It dirtied 125,471 buffers (1.03 GB).
  • Over the same window, pg_stat_all_tables shows 17,324 inserts and 10,605 updates on query_plan_dim. Nearly all of the 671k records are tuple locks on rows the statement left unchanged.
  • query_text_dim added another 0.08 GB.
  • An earlier window that crossed a service restart (03:43–04:38Z) gave 0.80 GB, 15.0% of 5.32 GB.
• Store B, pg_waldump of the live WAL, per-record block references aggregated by relation (398 MB sample at 04:38Z): the query_plan_dim heap was the largest single relation.
  • Heap/LOCK: 39,085 records, 7,257 of them carrying page images, 45.6 MB = 12.0% of WAL;
  • FPI_FOR_HINT on the same heap: 20.5 MB = 5.4%;
  • FPI_FOR_HINT on query_text_dim: another 2.2%.
  • An earlier 60 MB sample had Heap/LOCK on query_plan_dim at 19.9%.
• Store A, same steady window: 2,808 calls, 754,616 records, 161,773 page images, 1.17 GB = 12.4% of WAL (9.45 GB). It dirtied 433,175 buffers (3.55 GB), against 9,540 inserts and 44,638 updates.
  • pg_waldump (1.1 GB sample at 05:41Z): the query_plan_dim heap was again the largest relation, at 15.5% of WAL (FPI_FOR_HINT 12.6%, Heap/LOCK 1.6%, Heap/UPDATE 1.2%), plus its pkey at 3.4%.
  • New rows can't explain the hint images: 19,841 of them in a 7-minute sample, against 9,540 inserts in the whole hour. The likely source (inferred, not traced) is readers setting xmax-invalid hints on tuples a previous cycle locked.
  • The steady-window sample on store B had the heap at 17.6%, with the pkey another 3.6%.
• The pattern recurs every checkpoint. In a paired-cycle read (page images in one checkpoint cycle whose block was already modified in the previous cycle), Heap/LOCK was the fourth-largest source of repeat images on store B: 3,335 of the cycle's 37,809.
• Per day, extrapolated from the steady hour:
  • store B: ~22 GB of WAL (of ~95–135 GB/day), plus ~27 GB/day of dirtied dim pages written back;
  • store A: ~30 GB of WAL, plus ~90 GB/day of dirtied pages.
  • Not all of that dirtying is avoidable: the inserts and the hourly refresh UPDATEs stay. The locks are the part a fix removes.

Where

• Darling/PerformanceMonitor.Darling.Storage/PayloadDimensions.cs:336-355: UpsertSql, both arms (the gzip plan dim and the text dims). The doc comment at :301-326 describes the guard as writing nothing.
• Darling/PerformanceMonitor.Darling.Storage/PayloadDimensionWriter.cs:89: the call site, inside the batch's fact-COPY transaction.

Fix shape

• Skip fresh rows with a plain MVCC read, which takes no lock. For example:

  INSERT INTO <dim> (digest, <payload>, last_seen)
  SELECT u.digest, u.payload, $3
  FROM unnest($1, $2) AS u(digest, payload)
  WHERE NOT EXISTS (SELECT 1 FROM <dim> d
                    WHERE d.digest = u.digest
                    AND   d.last_seen >= $3 - INTERVAL '1 hour')
  ON CONFLICT (digest) DO UPDATE SET last_seen = EXCLUDED.last_seen
  WHERE <dim>.last_seen < EXCLUDED.last_seen - INTERVAL '1 hour'

  • Only new digests and hour-stale ones reach the conflict arm, so the Payload-dim batch upserts deadlock under concurrent collection cycles: unnest arrays carry no cross-session lock order #1801 lock order (client-side sorted arrays) still covers every lock the statement takes.
  • A row that turns fresh between the probe and the insert is harmless: it takes one extra lock.
  • A row fresh within the hour can't be purge-eligible, since the horizon is days.
  • An alternative is INSERT … ON CONFLICT DO NOTHING followed by an ordered UPDATE … WHERE last_seen < $3 - '1 hour', which is two statements but the same effect.
• Optional, and not for disk: probe digests first and ship payloads only for the missing ones. Today every sighting sends the gzip plan bytes to the store.
• Pins (live store test):
  • upsert a batch, then upsert the same batch again within the hour: the second call must leave every row's xmax at 0, and its pg_current_wal_lsn() delta must be under a small bound;
  • a row stamped two hours ago is refreshed;
  • a new digest is inserted;
  • the existing Payload-dim batch upserts deadlock under concurrent collection cycles: unnest arrays carry no cross-session lock order #1801 concurrent-reversed-batch test still passes.
• Measure after: pg_stat_statements.wal_bytes for the statement, and Heap/LOCK on the dim heaps in pg_waldump --stats.