Postgres Partition Maintenance: The Runbook That Works

Postgres Partition Maintenance: The Runbook Nobody Reads Until 03:00

The page came in at 03:12: pgdata at 94% on the primary. The cause was an events table growing about 40 GB a month, with a nightly retention job that ran DELETE FROM events WHERE created_at < now() - interval '90 days'. That job had been "working" for two years. It deleted rows, autovacuum eventually cleaned them, and the relation never gave a single byte back to the filesystem. Worse, the delete generated so much WAL and so many dead tuples that autovacuum on that table was permanently behind, which meant the visibility map was stale, which meant index-only scans stopped being index-only, which meant more I/O. The disk filled from both ends.

There's a 5-minute video version of this if you want the shape of the argument quickly. This is the runbook: exact SQL, lock behaviour, the migration path for a table you can't take offline, and the failure modes that only show up six months in.

The thesis, stated plainly: declarative partitioning by time is the right default for postgres time-series partitioning in vanilla Postgres, and the partitioning itself is the easy part. Almost all the value comes from the maintenance loop around it — pre-create partitions ahead of the write head, detach without blocking, drop for retention, and monitor that the job actually ran. pg_partman automates that loop on anything that gives you pg_cron. TimescaleDB earns its extra dependency only when you specifically need continuous aggregates or columnar compression.

Why Range-by-Time Beats DELETE for Retention

Three concrete wins, in order of how much they matter to your on-call rotation.

Space comes back immediately. DROP TABLE on a partition is a catalog change plus a file unlink at commit. No dead tuples, no bloat, no index churn, no autovacuum work queued behind it. The DELETE of the same rows leaves every one of them as a dead tuple that autovacuum must visit, and it does not shrink the relation on disk. If your capacity model assumes a postgres partition retention policy returns space via DELETE, that model quietly breaks — and you find out when the disk alarm fires, often after the DELETE itself gets killed mid-transaction by a lock wait pileup, leaving a partial delete, a bloated table, and an autovacuum worker chewing on the same relation for hours without finishing.

Pruning. enable_partition_pruning is on by default. Planning-time pruning handles constant predicates; since PG 11, execution-time pruning handles parameterised queries and stable expressions like now(), and EXPLAIN reports it as Subplans Removed: N. A dashboard query for "last 24 hours" touches one or two partitions instead of the whole relation.

Per-partition vacuum and analyze granularity. Yesterday's hot partition gets vacuumed in seconds because it's 1.5 GB, not stuck in a queue behind a 400 GB relation scan. Autovacuum thresholds are evaluated per partition, so a busy day triggers its own vacuum without dragging the archive along.

The Parent Table, Done Properly

Some of these decisions are painful to reverse. Get them right at CREATE TABLE time.

CREATE TABLE app.events (
    id            bigint       GENERATED ALWAYS AS IDENTITY,
    created_at    timestamptz  NOT NULL,
    tenant_id     bigint       NOT NULL,
    event_type    text         NOT NULL,
    session_id    uuid,
    payload       jsonb        NOT NULL DEFAULT '{}'::jsonb,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Propagates to every existing and future partition (PG 11+)
CREATE INDEX ON app.events (tenant_id, created_at DESC);
CREATE INDEX ON app.events USING brin (created_at) WITH (pages_per_range = 64);

Notes on each decision:

Doing It by Hand First

Before you hand this to pg_partman, run it manually once so you know what the extension is doing on your behalf.

SET TimeZone = 'UTC';

CREATE TABLE app.events_p2026_08_09 PARTITION OF app.events
    FOR VALUES FROM ('2026-08-09 00:00:00+00') TO ('2026-08-10 00:00:00+00');

Adopting an existing table as a partition is where people get burned. ATTACH PARTITION scans the table to validate every row against the bound, unless an equivalent CHECK constraint already exists. Add the constraint first (validate it under a weaker lock), then attach:

ALTER TABLE app.events_old
    ADD CONSTRAINT events_old_range CHECK (
        created_at >= '2026-01-01 00:00:00+00'
    AND created_at <  '2026-02-01 00:00:00+00'
    ) NOT VALID;

ALTER TABLE app.events_old VALIDATE CONSTRAINT events_old_range;

ALTER TABLE app.events ATTACH PARTITION app.events_old
    FOR VALUES FROM ('2026-01-01 00:00:00+00') TO ('2026-02-01 00:00:00+00');

The Lock Reality

DROP TABLE on a partition, and non-concurrent ATTACH/DETACH PARTITION, all take ACCESS EXCLUSIVE on the partitioned parent. That lock request queues behind any running query on the table, and every query that arrives after it queues behind the lock request. One analyst running a 40-minute SELECT plus one unguarded maintenance script equals a fully stalled table. This is the single most common way partition maintenance causes the incident it was supposed to prevent.

Guard every maintenance statement with a lock_timeout and an actual retry loop, not just a comment promising one:

SET lock_timeout = '3s';

DO $
BEGIN
  FOR i IN 1..10 LOOP
    BEGIN
      EXECUTE 'ALTER TABLE app.events DETACH PARTITION app.events_p2026_05_01';
      EXIT;
    EXCEPTION WHEN lock_not_available THEN
      PERFORM pg_sleep(5);
    END;
  END LOOP;
END $;

On PG 14+ you have a better option: detach partition concurrently.

-- Cannot run inside a transaction block
ALTER TABLE app.events DETACH PARTITION app.events_p2026_05_01 CONCURRENTLY;

It avoids holding a long ACCESS EXCLUSIVE lock. The restrictions matter: it can't run inside a transaction block, and if it's interrupted the partition is left in a "detach pending" state. Recovery is ALTER TABLE app.events DETACH PARTITION app.events_p2026_05_01 FINALIZE. Put that command in your runbook now, because you will need it at an inconvenient moment.

pg_partman Tutorial: The Whole Lifecycle in One Config Row

Declarative partitioning by time arrived in PostgreSQL 10; before that it was inheritance plus triggers, and nobody misses it. pg_partman (open source, maintained by Keith Fiske at Crunchy Data) is the piece Postgres still doesn't ship: the scheduler that pre-creates partitions and applies retention.

Version discipline: pg_partman 5.0 dropped the old trigger-based method entirely, supports only native declarative partitioning, and requires PostgreSQL 14 or newer. If you're on 4.x, plan that upgrade separately from your partitioning project.

CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

SET TimeZone = 'UTC';

SELECT partman.create_parent(
    p_parent_table     := 'app.events',
    p_control          := 'created_at',
    p_type             := 'range',
    p_interval         := '1 day',
    p_premake          := 8,
    p_template_table   := 'app.events_template',
    p_start_partition  := '2026-08-01 00:00:00+00'
);

UPDATE partman.part_config
SET retention             = '90 days',
    retention_keep_table  = false,
    retention_keep_index  = false,
    infinite_time_partitions = true
WHERE parent_table = 'app.events';

Read that retention_keep_table = false line again in your own config, because this is the assumption that bites everyone: part_config.retention_keep_table defaults to true. Setting a retention interval does not drop your old data by default — it only controls when a partition gets detached from the parent. It stays on disk, unattached and no longer queried, until you explicitly set this to false. Teams set retention = '90 days', watch nothing get smaller, and conclude retention is broken. It isn't; it's doing exactly what it says. Decide deliberately: false to drop, or true plus a retention_schema if you want detached partitions parked somewhere for an export job to pick up.

| part_config column | What it controls | Our default | |---|---|---| | premake | Partitions created ahead of the current one (default 4) | 8 for daily | | retention | Interval past which partitions are eligible | Match your compliance answer, not your hope | | retention_keep_table | Defaults true. Detach only, don't drop | false | | retention_keep_index | Keep indexes on detached partitions | false | | retention_schema | Move detached partitions here instead of dropping | Set if you archive | | infinite_time_partitions | Keep making partitions even with no incoming data | true for gapped feeds | | automatic_maintenance | Whether the bgw picks this table up | on |

premake should be at least twice your maintenance interval's worth of partitions. With daily partitions and hourly maintenance that's trivially satisfied at 8, and it means you can lose maintenance for a full week before writes start failing.

Scheduling Maintenance: pg_cron vs the Background Worker

Two options. run_maintenance_proc() is a procedure with transaction control, which means it commits per table and one slow partition doesn't hold a snapshot open across the whole run. Prefer it over the older run_maintenance() function.

CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule_in_database(
    'partman-maintenance',
    '7 * * * *',
    $ SET lock_timeout = '5s'; CALL partman.run_maintenance_proc(); $,
    'appdb'
);

pg_cron itself needs to be in shared_preload_libraries, stores schedules in cron.job and per-run results in cron.job_run_details — that second table is your audit trail, and you should be alerting on it, not just trusting the cron entry exists. cron.schedule_in_database() is how you target the application database from the cron database.

The alternative is the pg_partman background worker:

postgresql.conf

shared_preload_libraries = 'pg_partman_bgw'   # requires a restart
pg_partman_bgw.interval  = 3600               # seconds
pg_partman_bgw.role      = 'partman_maint'
pg_partman_bgw.dbname    = 'appdb'

That requires editing shared_preload_libraries and restarting, which most managed platforms won't let you do. On RDS and Aurora, pg_partman is a supported extension and pg_cron is the practical scheduler. Fine either way.

Run maintenance hourly for daily partitions. It's idempotent, it's cheap when there's nothing to do, and running it 24 times a day means a single failed run is a non-event rather than an incident — the 3am run catches what the 2am run missed, long before you're anywhere near out of headroom.

Migrating an Existing 500 GB Table Without a Maintenance Window

The path we use:

1. Create the new partitioned parent under a different name, with the partition key and constraints you decided on above. Register it with pg_partman and let it pre-create partitions covering the full historical range plus premake ahead. 2. Backfill in batches. partman.partition_data_proc() handles this with transaction control, or write your own chunked INSERT ... SELECT loop keyed on created_at with an explicit commit per chunk. Chunk size: whatever keeps each transaction under about 30 seconds. Watch replication lag between chunks and back off if a replica falls behind; this generates a lot of WAL. 3. Build the per-partition indexes after the data lands in each partition, not before. Loading into an indexed partition is dramatically slower. 4. Dual-write or shadow-write from the application during the backfill, or put a view plus trigger in front of the old table so reads and writes can be redirected atomically. 5. Cut over by swapping names inside one short transaction with lock_timeout set. That transaction takes ACCESS EXCLUSIVE, so it must be fast and it must be guarded.

If the existing table already covers a clean time range and you're partitioning by month, you can skip the copy for that slice entirely: add the matching CHECK constraint, validate it, and ATTACH the old table as the first partition. Zero data movement.

The Failure Modes Nobody Writes About

Quick reference, then the detail:

| # | Failure | Detection | |---|---|---| | 1 | Maintenance silently stopped | Headroom query below; cron.job_run_details failures | | 2 | Partition count creep exhausts max_locks_per_transaction | SELECT count(*) FROM pg_inherits WHERE inhparent = 'app.events'::regclass; | | 3 | Autovacuum never ANALYZEs the partitioned parent | Manual ANALYZE app.events; on a schedule | | 4 | Cold partitions ambushed by anti-wraparound autovacuum | VACUUM (FREEZE) on partitions past two intervals old | | 5 | Timezone-shifted bounds create gaps or overlaps | Compare partition bounds against expected UTC midnights | | 6 | Queries not filtering on the partition key scan everything | EXPLAIN — look for Subplans Removed: N |

1. Maintenance silently stopped. pg_cron logged a failure at 04:07 on a Sunday and nobody looked. Six days later premake is exhausted and inserts either fail outright or, if you kept a DEFAULT partition, silently pile into it. Detection is the headroom query below.

2. Partition count creep. Every partition a statement touches takes its own lock. With hundreds of partitions and a query that fails to prune, you can exhaust the shared lock table sized by max_locks_per_transaction (default 64) and get out of shared memory. Planning time and planner memory also grow with partition count. If you have more than a few hundred partitions per table, either your interval is too small or your retention is too long.

3. Stale parent statistics. Autovacuum does not automatically ANALYZE partitioned parent tables (still true as of PG 17). The parent's stats drive cross-partition estimates, and without them the planner guesses badly on multi-partition queries — wrong join order, bad row estimates on cross-partition aggregates. Schedule ANALYZE app.events; monthly, or weekly if the data distribution shifts.

4. Anti-wraparound ambush on cold partitions. A partition that stopped receiving writes in March still holds unfrozen tuples. Eventually anti-wraparound autovacuum will read the entire relation, at a time of its choosing. Freeze them on your terms once they go read-only:

VACUUM (FREEZE, ANALYZE) app.events_p2026_05_01;

Wire that into the same maintenance job, applied to any partition older than two intervals.

5. Timezone-shifted bounds. Someone runs create_parent from a session with TimeZone = 'Europe/Berlin'. The bounds land on 22:00 UTC instead of midnight, and every downstream "one partition per day" assumption is quietly off by two hours. Worse, if you then create later partitions from a UTC session, you get a gap or an overlap and inserts start failing. Always SET TimeZone = 'UTC' before any partition DDL, and put it in the cron command string too.

6. Queries that don't filter on the partition key. Check with EXPLAIN. You want to see a small number of partitions listed, or Subplans Removed: N for execution-time pruning. If you see every partition scanned, the query needs a created_at predicate, full stop.

What to Monitor

This is the monitoring contract. Four queries, wired into alerting, not into a dashboard nobody opens.

-- 1. Headroom: days between now() and the newest upper bound.
--    Alert below 3 days.
SELECT c.relname,
       (regexp_match(pg_get_expr(c.relpartbound, c.oid),
                     'TO \(''([^'']+)''\)'))[1]::timestamptz - now()
         AS headroom
FROM pg_class c
JOIN pg_inherits i ON i.inhrelid = c.oid
WHERE i.inhparent = 'app.events'::regclass
ORDER BY 2 DESC LIMIT 1;

-- 2. Rows in the DEFAULT partition (should be zero, always).
SELECT count(*) FROM ONLY app.events_default;

-- 3. Oldest partition vs retention target.
SELECT min(c.relname)
FROM pg_class c JOIN pg_inherits i ON i.inhrelid = c.oid
WHERE i.inhparent = 'app.events'::regclass;

-- 4. pg_cron failures in the last day.
SELECT jobid, start_time, status, return_message
FROM cron.job_run_details
WHERE status <> 'succeeded' AND start_time > now() - interval '1 day';

Add a fifth: per-partition size over time, from pg_total_relation_size(), sampled daily. That series is what lets you forecast the disk instead of reacting to it.

If you'd rather not build all of that yourself, MyDBA runs a free health check that covers it: it flags partition maintenance that hasn't run within its expected window, tracks per-table growth for capacity forecasting, and includes the autovacuum-liveness and cron-drift checks we added after a real WAL-and-disk outage where the scheduler had been dead for eleven days. It won't fix your schema. It will tell you the job stopped before the disk does.

pg_partman vs TimescaleDB: When the Extra Dependency Earns Its Keep

| | Vanilla + pg_partman | TimescaleDB | |---|---|---| | Chunk/partition creation | Scheduled job (pg_cron or bgw) | Automatic, no scheduler needed | | Retention | part_config.retention + drop | add_retention_policy() | | Rollups | Materialized views, recomputed | Continuous aggregates, incrementally refreshed | | Compression | None beyond TOAST | Columnar compression, vendor cites 90%+ ratios (measure it yourself) | | Licensing | PostgreSQL license, all of it | Core Apache-2; continuous aggregates and compression under the source-available Timescale License | | RDS / Aurora | pg_partman supported | TSL features not available | | Major-version upgrade | Nothing out of tree | Out-of-tree extension in the upgrade path |

Three verdicts:

The Checklist

Paste this into the ticket:

Partitioning costs you planning time, lock pressure and a scheduled job that can fail. It's still worth it, because the alternative is a retention DELETE that never returns space and a pager at 03:12.