Repository navigation
expire reads a column's zone maps in one catalog scan, not one per row group #1310
Description
Follow-up to #1290 (PR #1307), whose phase 0 measured a no-op pgcolumnar.expire at one zone_map catalog scan per row group: 10 scans and 0.05 ms at 10 groups, 1000 scans and 2.7 ms at 1000 groups (PG17). With the daemon running expire every pass, a billion-row table (about 1M groups) would cost about 3 s and a million catalog scans per pass.
#1304 added PgColumnarReadWholeChunkZoneMapsForColumn(storageId, columnIndex, snapshot), one scan keyed on storage_id. expire can read the retention column's maxima through it instead of calling PgColumnarReadZoneMapForColumn per group.
Note: that scan reads every zone-map row of the table and filters in C. OffgridwithJD measured 1006 planning buffers against 120 at 100M rows for the planner's use (#1292). For expire, which already reads every group, that trade is still N scans to 1. Measure both before choosing, and consider option 5's per-column extent (#1292) if it lands first.
Done when: an arm counts expire's zone_map scans (pg_stat_get_xact_numscans on zone_map plus zone_map_pkey) at 50 groups and asserts 1, red before the change. ttl_expire and autovacuum_expire stay green.
🤖 Generated with Claude Code
Activity
jdatcmd commentedon Oct 12, 2026
Measured: neither variant is worth shipping. Waiting on option 5 instead.
The setup: PG17, a no-op
expire(a 3650-day TTL), and a 10-column table. A reads the column's zone maps in one scan throughPgColumnarReadWholeChunkZoneMapsForColumn, then bsearches by group. B keeps the per-group probes, with one sharedPgColumnarZoneMapSession. Time is the mean of 50 calls; buffers are fromEXPLAIN (ANALYZE, BUFFERS)of one call.A wins only with unusually small row groups. On default geometry it's 4× slower. The scan keys on
storage_idalone, so it reads every column's whole-chunk and per-vector rows and discards all but one column's. That's the same cost OffgridwithJD measured for the planner on #1304. B changes nothing: the per-probe cost is the index descent, not opening the catalog.What would actually help: most daemon passes find nothing to retire, so the gain is an early exit for the no-op pass. If the retention column's minimum over the whole table is already past the cutoff, nothing can expire, and
expirecan return 0 without a single per-group read. That needs a per-column extent kept at flush time, which is exactly #1292's option 5 (OffgridwithJD is building it). The bound also has to stay sound after retire and rewrite, as I asked there; for this use, a stale-but-wide extent only means no early exit, which is safe.Plan: this waits on option 5. Then:
expirereads zero zone-map rows when the extent's minimum is past the cutoff.expirestill retires correctly when the extent says it might.test: a no-op expire must read the extent, not every zone map, red (c…
feat: a no-op expire answers from the column's extent, without a zone…
test: the pytest twin of commandprompt#1310's arms, and a column left…
test: ttl_expire enters the ledger, 67 checks, 16 dated by four mutat…
test: a no-op expire must read the extent, not every zone map, red (c…
feat: a no-op expire answers from the column's extent, without a zone…
test: the pytest twin of commandprompt#1310's arms, and a column left…
test: ttl_expire enters the ledger, 67 checks, 16 dated by four mutat…
Merge pull request #1326 from OffgridwithJD/feat/1310-expire-extent
docs: alpha5 notes, three corrections from review (#1333)