Native partition lifecycle for time-partitioned columnar tables: create ahead, drop past retention

Owner's decision, 2026-10-12: we build this whatever pg_partman supports. It started as #1291's fallback. It is now a definite item of its own. #1291 still settles whether pg_partman works with columnar partitions, and its answer decides only what we document alongside this, not whether this gets built.

Why our own, even if pg_partman works

One install, no second extension. Time-series users get partition lifecycle with pgcolumnar alone.
It can know about columnar. It can set the access method on each partition on PostgreSQL 15 and 16, where the parent cannot carry it. It can carry the parent's set_options to each new partition, and clean pgcolumnar's catalog rows when a partition is dropped.
It runs from our own daemon (1.0: the maintenance daemon should run expire() for tables that declare a TTL #1290), so no external cron is needed.

Proposed scope

Three SQL functions in the pgcolumnar schema, for a RANGE-partitioned parent on a time column:

function	does
`pgcolumnar.partition_create_ahead(parent, interval, count)`	creates the next `count` partitions of width `interval`, columnar, carrying the parent's `set_options`
`pgcolumnar.partition_drop_before(parent, cutoff)`	detaches and drops partitions whose whole range is older than `cutoff`, and clears their pgcolumnar catalog rows
`pgcolumnar.partition_maintain(parent)`	both, from settings stored once per parent: width, how far ahead, retention

The #1290 daemon then calls partition_maintain on a schedule, the same way it calls expire().

Questions to settle on this issue before code

Lock strength: the parent, not the partition. Dropping a partition takes ACCESS EXCLUSIVE on that partition. Nothing avoids that; core does the same for its own tables. It is acceptable, because once a partition is detached no query reaches it through the parent. The lock to avoid is the one on the parent, which blocks every query on the table. DETACH PARTITION CONCURRENTLY (PostgreSQL 14+) detaches without it. So drop is two steps: detach concurrently, then drop the detached table.
The daemon's lock rule needs an explicit exception. The maintenance daemon today takes no exclusive lock. Dropping a detached partition is the one exclusive lock this work needs. It should run with a short lock_timeout, so that a partition someone is still holding (for example, a long query that began before the detach) is skipped until the next pass rather than queued behind, and never blocks other sessions.
Rows that arrive before their partition exists. Create far enough ahead, refuse, or route them to a default partition and move them later.
Where the per-parent settings live. A new pgcolumnar catalog needs an upgrade script and a format decision, so this is the first design point.
Scope for the first version. RANGE on a time column only. LIST and HASH only if someone needs them.
Coexistence. If a parent is also managed by pg_partman, refuse, so that two managers never race on one table.

Done when

The design is agreed here.
Arms on every major: partitions are created ahead and are columnar with the parent's options; partitions past retention are dropped and their catalog rows are gone; the daemon does both without being asked; no step takes an exclusive lock on the parent, and the drop of a detached partition that another session still holds gives up at lock_timeout and is retried on the next pass.
Both twins, ledger rows from all five majors, and a "time-partitioned table" task in docs/how-to.md.

Related: #1291 (pg_partman compatibility), #1290 (the daemon running expire()), #1295 (tiering cold partitions, which would build on this).

🤖 Generated with Claude Code

Correction from the owner, 2026-10-12: there is no way to avoid ACCESS EXCLUSIVE when dropping a table, and core PostgreSQL takes the same lock for its own tables. The body now separates the two locks:

The partition being dropped: ACCESS EXCLUSIVE is unavoidable and acceptable, because after a detach nothing reaches it through the parent.
The parent: this is the lock to avoid, because it blocks every query on the table. Hence DETACH PARTITION CONCURRENTLY, then drop.

The body also said the daemon must never take an exclusive lock. That is wrong for this job, so the drop is now an explicit exception: it runs with a short lock_timeout and skips a partition another session still holds until the next pass.

🤖 Generated with Claude Code

@jdatcmd

Design note 1: measured first, and two of the proposed functions cannot work as written

Measured on PG 15, 16, 17, 18 and 19 (assert builds with #1299's fix). The results are identical on every major unless noted. The parent is RANGE on timestamptz with two columnar partitions. "Parent lock" is the mode this backend holds on the parent right after the statement, read from pg_locks inside the transaction.

  statement                                         parent lock
  CREATE TABLE p_3 PARTITION OF m ... USING ...     AccessExclusiveLock
  CREATE TABLE p_3 (...) USING ...; ATTACH ...      ShareUpdateExclusiveLock
  ALTER TABLE m DETACH PARTITION p_1                AccessExclusiveLock
  DROP TABLE p_1   (the partition, still attached)  AccessExclusiveLock
  ALTER TABLE m DETACH PARTITION p_1 CONCURRENTLY   (works at top level)

  DETACH ... CONCURRENTLY inside a DO block         ERROR: cannot be executed from a function
  DETACH ... CONCURRENTLY inside a plpgsql function ERROR: cannot be executed from a function
                                                    (PG 19: "from a function or procedure")
  DETACH ... CONCURRENTLY with a DEFAULT partition  ERROR: cannot detach partitions concurrently
                                                          when a default partition exists
  INSERT outside every range, no DEFAULT            ERROR: no partition of relation "m" found for row
  pgcolumnar.set_options(<partitioned parent>, ...) ERROR: relation "m" is not a columnar table
                                                    (PG 17+ too, where the parent carries USING)

What this decides

1. Create ahead = CREATE standalone, then ATTACH. Never PARTITION OF. PARTITION OF takes ACCESS EXCLUSIVE on the parent and blocks every query on the table, however briefly. ATTACH takes SHARE UPDATE EXCLUSIVE, which readers and writers pass. ATTACH scans the new table to validate its range, and that costs nothing on a table created empty a moment earlier.

2. partition_drop_before() cannot be a SQL function that drops without the parent lock. DETACH CONCURRENTLY is refused from any function, DO block or procedure, on every major. Plain DETACH and DROP both take ACCESS EXCLUSIVE on the parent. That leaves three shapes:

shape	parent lock	who runs it
a. the daemon issues `DETACH CONCURRENTLY` as a top-level statement in its own transactions, then `DROP`s the detached table	none exclusive	#1290's worker, which needs a top-level utility path (it cannot go through SPI, which is a function context)
b. a function that returns the statements (`SELECT * FROM pgcolumnar.partition_drop_plan(parent)` then `\gexec`)	none exclusive	the user or their scheduler
c. a function that does plain `DETACH` + `DROP`	ACCESS EXCLUSIVE on the parent, briefly	anyone; documented as such

I'd build a for the daemon and b for people who schedule it themselves, and not ship c at all. A retention job that blocks every query on the table is the thing the issue says to avoid. @jdatcmd, a puts a requirement on #1290: the daemon has to execute one utility statement as top level and own its transaction boundaries, the way autovacuum runs VACUUM. Does your design note for maintenance_due already do that, or should it?

3. A DEFAULT partition and concurrent drop are mutually exclusive. I'd refuse to manage a parent that has a DEFAULT partition, with a message saying why, rather than fall back to the exclusive detach without saying so. That also settles "rows that arrive before their partition exists": they error (no partition ... found for row), so the defence is creating far enough ahead, and the daemon's pass reports how many partitions of headroom remain.

4. Per-parent settings need their own home. set_options refuses a partitioned parent even on 17+, so there is nothing to inherit from. Options:

a new catalog pgcolumnar.partition_policy (parent regclass PRIMARY KEY, width interval, ahead int, retain interval, options jsonb). That means an alpha6 upgrade script, plus the drop hook clearing it the way set_options refuses a columnar matview while reset_options accepts a plain view: the pair disagrees in opposite directions #1265 clears options.
allowing set_options on a partitioned parent, with new partitions copying it.

I'd do the catalog. It holds settings set_options has no columns for (width, ahead, retain), and it keeps set_options' meaning ("this table's storage") unchanged.

Measured but not a decision

Catalog rows are cleared when a detached columnar partition is dropped: storage and row_group go from 1/1 to 0/0. The existing drop hook already covers the drop step.
PG 15/16 vs 17+: before 17 the parent cannot carry USING. Since step 1 always creates the partition standalone with an explicit USING pgcolumnar, the same code path is right on every major.

Unless either of you objects, I'll write the catalog and partition_create_ahead (shape 1, no daemon dependency) first, as a PR with arms on all five majors. The arms will be: partitions columnar with the policy's options, the parent lock never exclusive (read from pg_locks), and refusal with a DEFAULT partition. The drop half waits on the daemon question above.

🤖 Generated with Claude Code

fix: the storage-row repair opened partitions an ALTER never locked

test: partition_create_ahead, written first (commandprompt#1297)

feat: pgcolumnar.partition_create_ahead keeps a time-partitioned pare…

test: partition_lifecycle registries and ledger (commandprompt#1297)

Merge pull request #1308 from OffgridwithJD/feat/1297-create-ahead

@jdatcmd

@jdatcmd #1308 shipped create-ahead. Here is the drop half as I'd now build it. Shape b from my design note: no daemon change and no catalog. The daemon question from that note is still open, and shape a can come later on top of this.

pgcolumnar.partition_drop_plan(parent regclass, cutoff timestamptz) RETURNS SETOF text, read-only:

What it returns. For each partition whose whole range is older than cutoff (upper bound ≤ cutoff), in range order, two statements: ALTER TABLE <parent> DETACH PARTITION <p> CONCURRENTLY and DROP TABLE <p>. Each runs as its own top-level statement: SELECT * FROM pgcolumnar.partition_drop_plan(...) \gexec, or a scheduler doing the same.
An interrupted concurrent detach. A partition left pending (pg_inherits.inhdetachpending) gets DETACH PARTITION <p> FINALIZE instead.
Refusals, with create-ahead's checks and codes:
- a parent that isn't RANGE on one timestamp/timestamptz column;
- a DEFAULT partition (55000), because a concurrent detach is impossible with one. I'd rather refuse than fall back to the exclusive detach without saying so.
Never lists a partition that straddles the cutoff.

Arms, red first, in both twins:

the plan lists exactly the old partitions, in order;
running it drops them and leaves the rest, with their rows intact;
a partition straddling the cutoff is kept;
nothing old gives an empty plan;
the DEFAULT refusal;
a pending detach is finalised. I'd make one by timing out a DETACH ... CONCURRENTLY while another transaction holds a snapshot;
the dropped partitions' pgcolumnar catalog rows are gone. That one is a premise on the existing drop hook.

If you'd rather the drop half wait for the daemon decision, or have the function require ownership of the parent, say so. I'm starting the red arms now and won't open the PR before you've had a look.

@OffgridwithJD

@OffgridwithJD, shape b looks right to me. Go ahead without waiting for the daemon decision, and don't require ownership: the function only reads catalogs the caller can already see, and DETACH enforces ownership when the statements run. Six points for the arms:

A timestamp (no zone) parent and a timestamptz cutoff. Comparing a bound with the cutoff converts it in the session's zone, so the plan for the same table and cutoff can differ between sessions. Either compare in UTC, or require the cutoff's type to match the column's; say which in the docs. Arm: the same call from sessions west and east of UTC returns the same plan, as partition_create_ahead's twin does.
Tiered partitions (1.0: tier cold partitions to Parquet on object storage #1295). A partition tier_partition moved to Parquet is a foreign table. Does the plan list it? I'd say yes, since retention is retention, but DROP TABLE removes only the attachment and leaves the file on object storage. The docs should say so. One arm either way.
Bounds. A first partition FROM (MINVALUE) TO (x) with x ≤ cutoff should be listed; one bounded by MAXVALUE never. One arm for MINVALUE.
Quoting. Every name goes through %I / regclass output. Use a schema and a partition name that need double quotes in arm 1 or 2; that is what caught feat: pgcolumnar.tier_partition moves a cold partition to Parquet, exactly once (#1295) #1317.
A partition that is itself partitioned. DETACH ... CONCURRENTLY and DROP TABLE both work on one, and the drop takes its children. Either list it or refuse it, and add an arm, so the behaviour is chosen rather than accidental.
Arm 7 is an arm, not a premise. Removing the drop hook would redden it, and test: 87 arms have survived every mutation aimed at their neighbours: vacuous, or named for something they do not measure #1236 says a check a repository change can redden is not a premise. Give it a mutation, such as the drop hook skipped for detached partitions, if one reaches it.

Arm 6's way of leaving a pending detach, timing it out while another snapshot holds, is the right shape. Run it on all five majors: FINALIZE's behaviour has shifted across versions before.

test: partition_drop_plan, the drop half of the partition lifecycle, …

feat: pgcolumnar.partition_drop_plan returns the statements that reti…

test, docs: partition_drop_plan's pytest twin, the how-to and the ref…

test: 'a second plan for the same cutoff is empty' is a premise (comm…

test: partition_drop_plan arms from jdatcmd's review of the design, r…

feat: partition_drop_plan takes a cutoff of the key's own type, drops…

test, docs: the twin's arms for jdatcmd's review points; the cutoff t…

test, docs: the quoting arm requires every planned statement to run; …

test: partition_drop_plan's 23 checks in the ledger, 20 dated by seve…

Merge pull request #1331 from OffgridwithJD/feat/1297-drop-plan

Closing, as agreed on #1283. The native partition lifecycle is built:

partition_create_ahead: test, docs: pg_partman manages columnar partitions, with one setting (#1291) #1302-era work;
partition_drop_plan: feat: pgcolumnar.partition_drop_plan returns the statements that retire old time partitions (#1297) #1331;
refusals and the month/year widths: test: refusals and the month/year path in the partition lifecycle and tiering, in both twins (#1341) #1350;
the boundary and race arms: test: the two #1339 findings in my features: tier_partition's race arm and partition_lifecycle's boundary rows #1348.

The remaining test_partition_lifecycle findings are #1339 items (#1361 is in review), not #1297 scope.

Navigation Menu

Repository navigation

Native partition lifecycle for time-partitioned columnar tables: create ahead, drop past retention #1297

Why our own, even if pg_partman works

Proposed scope

Questions to settle on this issue before code

Done when

Design note 1: measured first, and two of the proposed functions cannot work as written

What this decides

Measured but not a decision

Next

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Native partition lifecycle for time-partitioned columnar tables: create ahead, drop past retention #1297

Description

Why our own, even if pg_partman works

Proposed scope

Questions to settle on this issue before code

Done when

Activity

jdatcmd commented on Oct 12, 2026

OffgridwithJD commented on Oct 12, 2026

Design note 1: measured first, and two of the proposed functions cannot work as written

What this decides

Measured but not a decision

Next

OffgridwithJD commented on Oct 13, 2026

jdatcmd commented on Oct 13, 2026

jdatcmd commented on Oct 14, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions