1.0: partition lifecycle for time-partitioned columnar tables, via pg_partman #1291

Activity

1. jdatcmd commented on Oct 12, 2026

   

   jdatcmd

   on Oct 12, 2026

   CollaboratorAuthor

   More actions

   Owner's decision, 2026-10-12: if pg_partman cannot do it, we build a utility

   The measurement in this issue stays first: whether pg_partman manages columnar partitions on every major. If it can't, a documented limitation is not an acceptable outcome. We provide our own partition lifecycle instead. The body's "done when" now says so.

   Proposed minimum scope, if it comes to that (for discussion here, before any code)

   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)

   Then #1290's daemon calls partition_maintain on a schedule, the same way it will call expire(), so lifecycle needs no external cron.

   Questions to settle first

   • 15 and 16: the parent cannot carry USING pgcolumnar there, so creation must set the access method on each partition. That is easy for our own utility, and is exactly what pg_partman may not do.
   • Lock strength: DETACH PARTITION CONCURRENTLY (PG14+) avoids an ACCESS EXCLUSIVE on the parent. The repository's rule is to justify any exclusive lock against a weaker correct one.
   • The default partition: rows that arrive before their partition exists. Create ahead far enough, refuse, or route to a default partition and move them later.
   • Scope: RANGE on time only, for 1.0. LIST and HASH are out unless someone needs them.

   @OffgridwithJD, this stays yours with the rest of #1291. The pg_partman measurement decides whether this comment becomes the work.

   🤖 Generated with Claude Code
2. jdatcmd commented on Oct 12, 2026

   

   jdatcmd

   on Oct 12, 2026

   CollaboratorAuthor

   More actions

   Update from the owner, 2026-10-12: the utility proposed above is now a definite item of its own, #1297. We build it whatever pg_partman supports. This issue goes back to its original question: does pg_partman work with columnar partitions on every major? If it does, we document and test that. If it does not, #1297 is the answer. Either way, #1297 gets built.

   🤖 Generated with Claude Code
3. OffgridwithJD commented on Oct 12, 2026

   

   OffgridwithJD

   on Oct 12, 2026

   Collaborator

   More actions

   Measured: pg_partman 5.4.1 works with columnar partitions, but only with one setting, and it does not avoid the parent lock

   pg_partman v5.4.1 (5ca3fa2, the version packaged for PG 18), built from source against each prefix. PG 15, 16, 17, 18 and 19, with #1299's fix. The parent is RANGE on timestamptz, daily partitions, create_parent(..., p_premake := 3).

   1. Out of the box, every partition pg_partman makes is heap, on every major. That includes 17+, where the parent itself carries USING pgcolumnar:

     parent USING pgcolumnar (17+), or no AM (15/16)   ->  289 children, heap=289, rows heap=700
   

   pg_partman does not take the access method from the parent.

   2. Switching its template table to columnar does nothing. ALTER TABLE partman.template_public_m SET ACCESS METHOD pgcolumnar succeeds, and the next run_maintenance still makes heap children (PG 15, 17, 19).

   3. What works: default_table_access_method, set where pg_partman's maintenance runs (here, ALTER DATABASE ... SET). Then on every major:

     children        pgcolumnar (the DEFAULT partition pg_partman creates included)
     1000 rows       pgcolumnar=1000
     retention       run_maintenance with retention '30 days', keep_table false:
                     pgcolumnar.storage 21 -> 0, row_group 21 -> 0, orphaned rows 0, no TRAP
   

   So the honest recipe is one setting. Its cost: every table created in that database, or by that role, becomes columnar unless it says otherwise. A role used only for pg_partman's maintenance narrows that.

   4. It does not meet #1297's lock rule. run_maintenance held AccessExclusiveLock on the parent on every major (read from pg_locks inside its transaction). It also creates a DEFAULT partition, which rules out DETACH CONCURRENTLY (#1297 note 1). That's fine for many users, and it's why #1297 exists.

   5. It does not reach #1299's defect, so it isn't an independent reproducer. On unfixed main (d5eb40b), retention dropped 21 columnar partitions with no TRAP. Its drop path does not issue an ALTER TABLE <parent> that leaves sibling partitions unlocked.

   What #1291 delivers, as I read it

   A docs/how-to.md section, "partition lifecycle with pg_partman", carrying: the default_table_access_method recipe and its cost; the warning that without it you get heap partitions silently; the parent lock and the DEFAULT partition; and a pointer to #1297's native lifecycle for those who need no exclusive parent lock. Plus an arm that would catch the silent-heap trap, if we want one that depends on an external extension. I'd make it an opt-in suite (PGC_RUN_PARTMAN=1, like pg_upgrade.sh). It would record a named SKIP when pg_partman isn't installed, never a pass, per jd's rule.

   @jdatcmd, objections to the opt-in suite? Otherwise docs and suite come as one PR after alpha5.

   🤖 Generated with Claude Code
4. OffgridwithJD

   added a commit that references this issue on Oct 12, 2026

   test, docs: pg_partman manages columnar partitions, with one setting (c…

   ...

   719d788
5. jdatcmd

   closed this as completedin #1302on Oct 12, 2026
6. jdatcmd

   added a commit that references this issue on Oct 12, 2026

   Merge pull request #1302 from OffgridwithJD/test/1291-pg-partman

   ...

   cc0d8f8