Living With Partitions — Attach, Detach, Retention

A partitioned table is a process as much as a schema. Next month's partition has to exist before the first row for it arrives, and last year's has to leave without an outage. Neither happens because the DDL was written correctly; both happen because something runs on a schedule and the schedule itself is monitored.

The payoff is the trade this chapter opened with. Retention on delivery_events stops being an eleven-hour DELETE that leaves 200 GB of debris and becomes two statements, detach the oldest month and drop it, with a few milliseconds of lock between them and no vacuum work at all. The rest of this topic is the machinery that makes those two statements safe, and the one maintenance rule about partitioned tables that catches nearly everyone.

Creating Ahead of Time

Postgres will not invent a partition for a row that fits nowhere. The insert fails with an error naming the partition key value, and because delivery_events is written by the delivery pipeline on every scan and every status change, the failure arrives as a flood at 00:00 on the first of the month. Cartwheel's job runs daily and keeps two months of future partitions in existence, and it is monitored on the state rather than on the run. An alert on "the partition covering tomorrow does not exist" catches a job that stopped being scheduled three weeks ago; an alert on "the job failed" needs the job to have run in order to fire.

SELECT to_char(d, 'YYYY_MM') AS month,
       to_regclass('delivery_events_' || to_char(d, 'YYYY_MM')) AS partition
  FROM generate_series(date_trunc('month', now()),
                       date_trunc('month', now()) + interval '2 months',
                       interval '1 month') d;

  month  |        partition
---------+--------------------------
 2026_08 | delivery_events_2026_08
 2026_09 | delivery_events_2026_09
 2026_10 |                            -- NULL: nothing exists yet

A NULL in that second column is the whole alert. The temptation at this point is to add a DEFAULT partition so a missing month cannot cause an error. That trades an immediate, obvious failure for three quieter ones: rows piling up in a relation no query targets, a later ATTACH that has to scan them under a lock to prove they belonged elsewhere, and a table on which DETACH … CONCURRENTLY is not permitted at all.

ATTACH and Its Validation Scan

Attaching an existing table as a partition takes a SHARE UPDATE EXCLUSIVE lock on the parent, the friendly maintenance lock from Chapter 3 that blocks neither readers nor writers, plus an ACCESS EXCLUSIVE lock on the table being attached. Then it scans that table in full, to prove that every row satisfies the bounds it is being given. On a 58 GB month of historic events that scan is the whole cost, and it holds the exclusive lock on the incoming table for its entire duration.

The scan is avoidable, and the mechanism is the same one Chapter 3 used to add constraints to orders without a window. Put a CHECK constraint matching the intended bounds on the table first, as NOT VALID, then validate it separately under the weak lock. When ATTACH PARTITION then runs, Postgres uses that validated constraint as proof and skips the scan entirely, turning a long exclusive lock into a brief one. The documentation recommends dropping the now-redundant constraint afterwards, since the partition bounds enforce the same thing.

ALTER TABLE delivery_events_2024_09
  ADD CONSTRAINT ck_bounds
  CHECK (occurred_at >= '2024-09-01' AND occurred_at < '2024-10-01') NOT VALID;

ALTER TABLE delivery_events_2024_09 VALIDATE CONSTRAINT ck_bounds;  -- weak lock

ALTER TABLE delivery_events ATTACH PARTITION delivery_events_2024_09
    FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');          -- no scan

ALTER TABLE delivery_events_2024_09 DROP CONSTRAINT ck_bounds;      -- redundant now

DETACH, and DETACH CONCURRENTLY

Plain DETACH PARTITION takes an ACCESS EXCLUSIVE lock on the parent, which means it blocks every reader and writer of the whole partitioned table, and per Chapter 3's queue rule so does everything that arrives behind it. On a table the API writes to continuously, that is an outage of whatever length the longest running query happens to be. Since 14 there is a better form. DETACH PARTITION … CONCURRENTLY needs only SHARE UPDATE EXCLUSIVE on the parent, taking the strong lock on the partition alone at the very end.

It works in two internal transactions, which is why it cannot run inside a transaction block: the first marks the partition as detaching and then waits for every transaction currently using the partitioned table to finish, and the second completes the removal. Two consequences follow. It can take a while on a system with long-running analytics queries, and if it is cancelled halfway the partition is left pending, where ALTER TABLE … DETACH PARTITION … FINALIZE completes it and only one partition can be pending at a time. It is also refused outright on a table that has a DEFAULT partition, which is the concrete price of that shortcut. On the way out Postgres adds a CHECK constraint duplicating the partition bounds to the detached table, so what you are left with is an ordinary table that still carries a proof of what it contains.

-- 1. leaves the parent, blocks nobody; not inside BEGIN/COMMIT
ALTER TABLE delivery_events
  DETACH PARTITION delivery_events_2025_08 CONCURRENTLY;

-- 2. it is now an ordinary table; archive at whatever pace suits
INSERT INTO delivery_events_archive
SELECT * FROM delivery_events_2025_08;

-- 3. give the 58 GB back to the filesystem
DROP TABLE delivery_events_2025_08;

Retention as Data Lifecycle

Once a partition is detached, what happens to it is a business decision and no longer a database problem. It can be copied into delivery_events_archive and dropped, as Cartwheel does. It can be dumped to object storage and dropped, which is cheaper still. It can stay attached but be moved to a slower tablespace, so that twelve-month-old events remain queryable at a fraction of the storage cost. Or it can simply be dropped, when the data has no value and no compliance claim on it. All four are the same two-statement mechanism with a different middle step, and that uniformity is what pays for the structural complexity partitioning adds. Note what the middle step is protecting against. Once DROP TABLE runs, the data is gone from the database and is present only in backups taken before this moment, which Chapter 12 has opinions about.

Per-Partition Maintenance

Storage parameters are properties of a relation, and the partitions are the relations. A partitioned table accepts no storage parameters at all — setting autovacuum_vacuum_scale_factor on the parent is rejected outright rather than stored and ignored, so the way to give the hot current month aggressive autovacuum settings is to set them on that month's partition when it is created, and to leave the closed months alone, since a partition that takes no writes needs nothing beyond the occasional freeze pass Chapter 7 describes. The same applies to statistics targets, fillfactor, and index builds: a REINDEX of one 58 GB month is a job you can schedule, where a reindex of a 700 GB table is a negotiation.

The rule that catches people is about the parent. Autovacuum does not process partitioned tables at all, only their children, and the parent nonetheless carries its own set of statistics, the ones the planner uses when it estimates a query spanning the whole hierarchy. Those statistics drift with every insert and stay drifted until ANALYZE is run by hand. Since 18 the ONLY keyword makes that cheap: it collects the parent's statistics without re-analyzing every child, so the monthly maintenance job can refresh the hierarchy's estimates in seconds rather than re-reading 700 GB.

-- per relation: the hot month vacuums at 1% dead, not the default 20%
ALTER TABLE delivery_events_2026_08
  SET (autovacuum_vacuum_scale_factor = 0.01);

-- 18+: refresh the parent's own statistics, skip all twelve children
ANALYZE ONLY delivery_events;

Automation Worth Adopting

Rolling your own creation and retention job is perfectly reasonable for one table. It is a liability across ten, because the failure mode produces no error until the first insert of a new month and the code lives in whichever repository the last engineer happened to pick. pg_partman is the standard answer: it creates partitions ahead of the calendar, applies a retention policy that detaches or drops old ones, and since version 5 it supports only declarative partitioning, the trigger-based approach having been dropped. It ships a background worker, so it does not require pg_cron or an external scheduler to run its own maintenance, which removes one of the two components that can stop running without saying so.

TimescaleDB is the other direction: it manages time partitioning on the same underlying machinery and adds columnar compression and continuous aggregates on top, which matters when the historic months are queried analytically rather than just retained. Both are extensions and both are covered as an ecosystem question in Chapter 14. The decision here is narrower. If Cartwheel had one partitioned table, the fifteen-line job and the alert on the missing partition would be the right amount of machinery. It has one now and will have three within the year, and three hand-rolled jobs is where the arithmetic changes.