pg_partition_magician transmutes an existing Postgres table into a native RANGE-partitioned one, online. Your live table is kept intact as one bounded MONOLITH partition, no rows move; pgpm keeps fresh partitions ahead of your writes, and regrains the history into fine partitions on demand.
pgpmA good magician will, just once, turn the box around and show you the hinges. No data teleports; there's no secret copy. There are seven moves, and every one is plain Postgres, performed in the right order, on the right lock.
Postgres has no ALTER TABLE … PARTITION BY. The only built-in path is to build a fresh partitioned table and copy every row, a long lock, or a long migration with a risky cutover. pg_partition_magician copies nothing. Watch the hands.
transmute
transmute() renames your live table aside and builds a partitioned parent under the same name, attaching the old table intact as one bounded monolith child that holds the whole history, then laying a grid of real, empty partitions ahead of it. No rows move: same name, same data, a catalog-only swap the app never sees. The one hard requirement is that the partition key be NOT NULL. A key is optional: an existing primary key or unique constraint that contains the partition key is reused in place (move 3), and a table with neither is partitioned keyless. What transmute will not do is rewrite a key that excludes the partition key: it refuses and tells you how, rather than mangling it. And it reverses cleanly, while the monolith still holds the whole table, untransmute puts it back exactly as it was.
Postgres requires a partitioned table's key to contain the partition key, and the modern time-ordered key (Snowflake bigint, UUIDv7, ULID) already is that key. So nothing widens and no index rebuilds: transmute reuses it in place, pure metadata, online at any size. Containing is enough, not leading, so a composite (tenant_id, id) qualifies too. The one shape pgpm refuses is a key that excludes the partition key, because widening that means rebuilding it under ACCESS EXCLUSIVE, O(rows): about 28 minutes at 300M rows. Predictable beats clever.
Everything keys off the frontier: the line between settled past and live present. Behind it sits the closed history, in one coarse monolith, safe to regrain into fine partitions whenever you like; the one straddling it is open, still taking writes, and pgpm's lone rule is to leave it be. Ahead of it, partitions are obtained in advance, so new rows always land in a real partition. There is no DEFAULT to catch a stray, and that is the deliberate trade: a write past the grid is refused outright rather than parked somewhere to be filed away later by a background process. Nothing is reorganized until it has closed.
A normal ATTACH scans the whole table being attached under ACCESS EXCLUSIVE to prove every row honors the bound. pgpm proves it first: CHECK … NOT VALID is instant, then VALIDATE scans under a gentle SHARE UPDATE EXCLUSIVE lock that lets reads and writes continue, so the ATTACH skips its scan. The same move attaches the monolith at cutover and every regrained partition after. A 4M-row attach: 101 ms → 0.4 ms.
regrain
The history starts as one coarse monolith. regrain splits it by copying: it reads a range into a fresh, born-validated partition, swaps it in, and drops the source, online and a chunk at a time. Because it copies instead of deleting, there are no dead tuples and no vacuum. It is optional, a coarse monolith is a correct, permanent state, and regrain is what restores partition pruning and fine-grained retention. Because the monolith stays whole and attached until that one atomic swap, a read of the parent is never short: every row stays visible the entire time. The one honest cost is transient disk, about 2x the span while the copies and the source briefly coexist.
maintain_all
pg_cron tick repeats every move, and shows its hands.One scheduled call ties it together: obtain stocks the future, retain drops partitions past your policy (archiving each one first, if you set a strategy), and, once you enable it, regrain splits the history a chunk per tick, looping on pg_cron with every step written to pgpm.log, the magician keeping his hands open. It starts paused: resume() to go live, pause() to stop.
If the partition key only moves forward (a clock, a sequence, a time-ordered id), pg_partition_magician can manage it.
A timestamptz, timestamp, or date column. Monthly, daily, hourly: any interval.
call pgpm.transmute(
'public.events',
'created_at', interval '1 month');A bigint or numeric sequence, including Snowflake-style ids, where time lives in the high bits.
call pgpm.transmute(
'public.events',
'id', 10000000);Time-ordered uuids, and ULIDs stored as uuid. The grid is time; the bounds are encoded back into uuids.
call pgpm.transmute(
'public.events',
'id', interval '1 day');The hard parts are documented, not hidden. The illusion is discipline, not deception.
The key must only move forward. Floats are rejected: NaN and infinity would poison the frontier math.
An outgoing key is carried onto the new parent for you, so it keeps enforcing across every partition and not just the monolith. An incoming key survives because pgpm never rewrites the primary key it references: with p_incoming_fks => 'preserve' it is dropped for the cutover and re-added against the new parent. No composite key, no denormalization.
There is no DEFAULT partition. A write no partition covers is refused, loudly, instead of being parked somewhere for a background process to file away later. That removes the machinery, and the referential-integrity window that came with it. The price is a ceiling: obtain × step is how far ahead you may write.
Preserving an incoming key means dropping it for the cutover, so referential integrity is off on the referencing table until it is re-added. You can close that yourself the moment transmute returns; either way status() reports it rather than hiding it. Nothing else moves rows, so nothing else opens a gap.
621 assertions on PostgreSQL 15-18, all three install channels, every partition kind. And five guards, four on locks and one on a data-coupled scan, that CI re-runs against a deliberately reintroduced defect: a guard that still passes then is not testing anything, so it fails the build.
Pure SQL, no compiled extension, no superuser. Point psql at the single source file and you're done:
psql "$DATABASE_URL" -f pgpm_core/install.sql
No CLI? On Supabase or any SQL editor, the install page has a paste-and-run bundle. A Trusted Language Extension package is there too, for completeness.