1.0: the maintenance daemon should run expire() for tables that declare a TTL

Part of the 1.0 time-series set the owner chose on 2026-10-12. All seven are listed on #1283.

What exists

pgcolumnar.expire(table) drops row groups whose rows have all passed the retention declared with set_options(ttl_column, ttl_interval).
The maintenance daemon pgcolumnar.autovacuum runs compact_rewrite and recluster on tables that maintenance_due reports as due.

What is missing

expire never runs on its own. docs/features.md says so: "you call it by name". So a user who declares a TTL must also schedule expire themselves, with cron or pg_cron.

Goal

When the daemon is on, it also runs expire for every table that declares a TTL, under the same rules it already follows: online operations only, never an exclusive lock, and it yields to a stronger lock a session requests.

Questions to settle in the design

Should maintenance_due report "expire due", or should the daemon call expire on every pass? expire is cheap when nothing has aged out, which needs measuring.
Does it need its own setting to turn expiry off while the daemon stays on? Users may want retention to be manual.

Done when

An arm shows the daemon removes an aged-out row group without being asked, and a table without a TTL is untouched.
An arm shows it yields to a session's ACCESS EXCLUSIVE request, as the existing daemon arms do.
Both twins, ledger rows from all five majors, docs updated.

🤖 Generated with Claude Code

@OffgridwithJD

Plan for #1290: the daemon runs `expire`

@OffgridwithJD, please attack this before I write the red arms.

Owner decision (today): automatic expiry gets its own setting, pgcolumnar.autovacuum_expire, off by default. Turning on the daemon alone never deletes rows, so anyone upgrading keeps today's behaviour. To get automatic retention, a user turns on both the daemon and this setting.

Goal

When pgcolumnar.autovacuum and pgcolumnar.autovacuum_expire are both on, every pass runs pgcolumnar.expire(t) for each table that declares a TTL. It follows the daemon's existing rules: SHARE UPDATE EXCLUSIVE only, and cancellation by core when a session queues a conflicting lock.

Not in scope

No per-table switch. Declaring a TTL is the per-table intent.
No change to what expire retires, or to its cutoff rules.
No orphan or space reclamation beyond what expire already does.

Phases, in dependency order

0. Measure before deciding "every pass" versus a due check.
expire has no early exit: it reads the row-group list and one zone map per group even when nothing has aged out. I'll measure a no-op expire (instruction count and catalog scans) at 10, 100 and 1000 row groups.

If it is cheap, the daemon calls it on every pass, and maintenance_due keeps its signature.
If not, the daemon first reads the oldest group's max against the cutoff.
Verified by: the numbers, posted here.

1. Red arms first. test/autovacuum_expire.sh, plus an independent test_autovacuum_expire.py driving the server with ALTER SYSTEM and a reload, as test_hilbert_cluster.py already does. The fixture is one TTL table with aged and fresh groups, and one table without a TTL.

(a) Daemon on, expire off: nothing is retired. A premise first shows that a manual expire would retire, counted on a copy.
(b) Both on: aged groups are retired, no row inside the retention is lost, and the no-TTL table is untouched, with no "skipped" log line for it.
(c) Yield: the daemon holds SHARE UPDATE EXCLUSIVE inside expire (through maintenance_hold_ms). A session's ACCESS EXCLUSIVE is granted quickly, and the log shows the cancel. This mirrors autovacuum_yield.sh.
(d) Isolation: an expire that errors (the TTL column was dropped) does not roll back a compact_rewrite due on the same table in the same pass.
Verified by: each arm red against today's daemon, for the stated reason.

2. Code.

The new GUC, PGC_SIGHUP, defined before MarkGUCPrefixReserved.
The worker's table list gains a has_ttl column from pgcolumnar.options (ttl_column and ttl_interval both non-null, as PgColumnarReadTtl reads it).
expire runs first, in its own transaction and PG_TRY, so compaction doesn't rewrite groups that are about to be retired, and an expire failure is logged and skipped on its own.
The log line reads pgcolumnar autovacuum: expire <t> retired <n> row groups.
expire calls pgcolumnar_maintenance_hold() after it takes its lock, as compact_rewrite does, so (c) has a window.
Verified by: arms green, five majors.

3. Mutations, for ledger rows from 15–19, one per arm: the GUC check bypassed; the has_ttl filter removed; the order or transaction split collapsed; the hold removed from expire.

4. Docs and comments. These say expire never runs on its own: features.md, sql-reference.md, how-to.md (and fix its stale "timestamp or timestamptz", since date is allowed), configuration.md, administration.md, ARCHITECTURE.md, the pgcolumnar.autovacuum GUC description, expire's doc comment, and the options catalog comment. alpha5 is untagged, so the alpha5 SQL and the alpha4→alpha5 script can carry the comment change; no new upgrade script.

Risks and open questions

TimeZone. For date and timestamp TTL columns, the cutoff depends on the session TimeZone, and the daemon's session uses the server's TimeZone. A user whose sessions run in another zone gets a cutoff shifted by the offset. This erring is in both directions, and expire deliberately errs toward keeping. Proposal: document it, and add an arm showing that the daemon's cutoff follows the server's TimeZone. Do you see a case where this should be refused instead?
Ownership. The daemon is the bootstrap superuser, so expire's owner check always passes. That matches how compact and recluster run today.
Assumed: expire stays SHARE UPDATE EXCLUSIVE. Not yet checked: whether PgColumnarRetireGroup takes anything stronger. Phase 1(c) checks that from pg_locks.

Phase 0: a no-op `expire` costs one zone_map scan per row group

Measured on PG17, on a throwaway cluster. Each table has 1000-row groups and a 3650-day TTL, so nothing ages out. Time is the mean of 50 calls; scans are the calling transaction's own pg_stat_get_xact_numscans on zone_map plus zone_map_pkey.

row groups	zone_map scans per call	ms per call
10	10	0.049
100	100	0.280
1000	1000	2.746

The cost is linear: about 2.7 µs and one catalog scan per group. Wall clock on this hybrid CPU is indicative only; the scan counts are exact.

Decision: call it on every pass, and keep maintenance_due unchanged. A 1000-group table costs about 3 ms per pass against the default 60 s naptime. Extrapolated to a billion-row table (1M groups), that is roughly 3 s and a million catalog scans per pass. Background work under SHARE UPDATE EXCLUSIVE stays tolerable, but it's worth removing.

Follow-up, after #1304 merges: #1304 adds PgColumnarReadWholeChunkZoneMapsForColumn, which reads a column's zone maps in one scan keyed on storage_id. expire can use it to go from N scans to 1. I'll file that as its own issue rather than couple #1290 to #1304. A due check based on the oldest group doesn't work: groups aren't ordered by the TTL column, so finding the oldest max is itself the N-scan read.