pg_partition_magician

→ Explainer & install page

Online RANGE partitioning for PostgreSQL, in pure SQL. No compiled extension, no superuser: install it by running one file. The only runtime dependency is pg_cron, and only to run the background job.

It partitions on any monotonic key (time, integer/bigint ids including Snowflake, or UUIDv7 / ULID) and manages the whole lifecycle:

• transmute: convert a live, unpartitioned table to partitioned with no row movement. The original is renamed aside and attached intact as one bounded monolith child; a fresh DEFAULT is the safety net. The cutover is one read-only scan plus a metadata flip: no rebuild, no row rewrite. The table is locked for the duration of that scan, so size a maintenance window from its row count (see the guide). Reversible with untransmute until the history outgrows the monolith.
• obtain: keep N partitions ahead of the write frontier.
• regrain: split the monolith into fine partitions on demand, by copying (no dead tuples, no vacuum). Optional, a coarse monolith is a correct permanent state.
• retain: drop partitions past a policy. Set config.archive_fn to a resumable archive strategy -- e.g. archive to long-term storage, see the optional pgpm_archive add-on for ready-made ones -- and a partition only drops once it's fully archived, never before.
• maintain: the one procedure pg_cron calls (obtain, retain, optional auto-regrain).

The schema is pgpm. Think "a slice of pg_partman, installable as plain SQL."

Two caveats, both covered in the guide. There is no DEFAULT partition: obtain keeps a grid of real partitions ahead of the write frontier, and a write beyond that grid is refused rather than parked somewhere. config.obtain x partition_step is therefore both your slack if maintenance stalls and a ceiling on how far ahead you may write. And incoming foreign keys are preserved, not ignored (transmute never rewrites your key; p_incoming_fks => 'preserve' re-adds each one once the table is quiescent).

Why it exists

pg_partman is excellent, but it is a compiled C extension: it needs CREATE EXTENSION, the binary, and privileges some managed or locked-down environments do not grant. pg_partition_magician is just tables, views, and PL/pgSQL, so it installs anywhere you can run SQL and schedule a job.

Modules

pgpm_core is the only required piece. Everything else is an independent, optional add-on that loads on top of it (never before it); none of the add-ons depend on each other, and installing any subset in any order is fine.

Directory	What it's for	When you need it
pgpm_core	The product itself: transmute/obtain/retain/regrain/maintain.	Always.
pgpm_hypertable	A one-time migration tool (from_hypertable) that converts a TimescaleDB hypertable to a pgpm-managed table, then hands off to transmute. Not something you keep using afterward.	Only if migrating off TimescaleDB (Apache edition).
pgpm_archive	Ready-made S3 archive strategies for config.archive_fn (see retain above).	Only if you want retain to archive a partition's data before dropping it; without it, archive_fn stays null and partitions just drop.

Install

Copypsql "$DATABASE_URL" -f pgpm_core/install.sql

Re-running that file over an existing install is the supported upgrade path. select pgpm.version() reports what is installed, and pgpm.installed records one row per install.sql run.

The install page has dashboard copy-paste bundles and the registry command; the guide covers all three channels and uninstall. pg_cron must be enabled for scheduled maintenance.

Quickstart

Copy-- 1. Convert and register. Registers PAUSED: nothing moves until you resume.
call pgpm.transmute(
  p_parent   => 'public.events',
  p_control  => 'created_at',         -- the key to range-partition on (must be in the PK)
  p_interval => interval '1 month',
  p_obtain   => 7,                    -- keep 7 partitions ahead
  p_retain   => '90 days'             -- drop partitions older than this (null = keep)
);

-- 2. Schedule maintenance (one job covers every managed table):
select pgpm.schedule();

-- 3. Inspect, then go live:
select * from pgpm.status();
select pgpm.resume('public.events');

-- 4. (optional) Split the coarse history into fine partitions, paced across ticks:
select pgpm.set_regrain('public.events', '1 month');

The two-step (transmute paused, then resume) lets you inspect before anything moves. transmute reuses a primary key or unique constraint that includes the control column, or partitions keyless if neither exists; the one hard requirement is a NOT NULL control column. See the walkthrough.

Migrating from TimescaleDB

On a TimescaleDB hypertable (Apache edition)? from_hypertable migrates it to a pgpm-managed partition set: an online copy into one plain table, done chunk by chunk (the source keeps serving traffic), then a brief cutover that hands off to transmute. It preserves keys, indexes, identity, generated columns, CHECK/defaults/NOT NULL, and translates a drop_chunks policy into pgpm retain. Keyed and keyless hypertables both migrate.

Copycall pgpm.from_hypertable('public.metrics', 'ts', interval '1 day');

For workloads that update or delete during the copy, pass p_track_changes => true (it reconciles by key, so it needs one; keyless tables migrate append-only). Either way the catch-up backlog is drained online before the cutover, so the lock applies only a tiny residual.

One keyless caveat: a translated drop_chunks retention stays dormant until you add a key and regrain the history (retain drops fine partitions, not the monolith).

It is an optional add-on, loaded only where the timescaledb extension exists:

Copypsql "$DATABASE_URL" -f pgpm_hypertable/install.sql

See the reference for the phases and knobs.

Observability

pgpm logs every operation to pgpm.log but keeps no system-wide history. With pg_flight_recorder (PGFR) installed, pgpm.impact_report reports what the workload experienced during a conversion (checkpoints, WAL, waits, latency).

Copyselect pgpm.impact_report('public.events');

Both ship with pgpm_core, read-only, and PGFR is never a dependency: they raise a clear error until it's installed.

Archiving (optional)

retain drops partitions past a policy, but data doesn't have to just disappear: the optional pgpm_archive add-on supplies ready-made archive strategies (NDJSON and Parquet, to S3 or any S3-compatible store, optionally GZIP-compressed) for config.archive_fn. Set it once (after the one-time connection setup covered in pgpm_archive/README.md), and a partition only drops once it's been fully archived -- automatically, in bounded chunks, ahead of every drop:

Copyselect pgpm.set_archive_fn('public.events', 'pgpm.archive_to_s3_parquet(regclass,name,text,text)'::regprocedure);

Load it on top of the core:

Copypsql "$DATABASE_URL" -f pgpm_archive/install.sql

See pgpm_archive/README.md for the full picture: connection setup, choosing NDJSON vs. Parquet, and the synchronous alternative (archive.to_s3/archive.to_s3_parquet) for manual, one-off archiving instead of the automatic archive_fn path.

Documentation

• User guide: concepts, install, transmute, scheduling, regrain, retain, foreign keys, troubleshooting.
• Reference: every function and catalog object.
• Runbook: symptom-driven operational procedures.
• Explainer: the visual overview.
• Pilot template: how an early production install is run, and what it does not promise.
• Releasing: what a version number covers, and how a release is cut.
• Security policy: how to report a vulnerability, and what is in scope.

Tests

Copy./test.sh        # full matrix: PG 15-18 x all install channels
./test.sh 15     # one version, all channels
./test.sh ci     # every track CI runs, including the ones the matrix skips

pgTAP on Docker. ./test.sh covers the four PostgreSQL versions but skips the timescale, observe, archive, perf and discriminate tracks, which need their own image or service, so it does not by itself predict a green CI. Use ./test.sh ci before pushing anything that touches pgpm_core/install.sql. See ONBOARDING.md for the dev loop.

License

Apache License 2.0. See NOTICE for attribution.