Partition lifecycle: retention without bloat

Sharding and Partitioning covers what partitioning is and when to reach for it. This is the operational half nobody writes down: how partitions get created before they are needed, how old ones get dropped, and why the whole point is that retention becomes a metadata operation instead of a delete.

The problem it solves: a high-churn table — events, executions, logs, audit rows — where you keep N days and discard the rest. Deleting is the obvious approach and it is the one that fails.

Why DELETE loses to DROP

DELETE FROM executions WHERE finished_at < now() - interval '7 days';

Three costs, and each one compounds under load:

• Dead tuples, not free space. MVCC marks rows dead; autovacuum has to reclaim them, and on a table taking heavy writes it falls behind. Disk does not drop. See VACUUM, autovacuum, table bloat, and transaction wraparound.
• WAL amplification. Every deleted row is a WAL record, replicated to every standby and written to archive. A ten-million-row delete is a traffic event.
• Index bloat. The index entries die too, and index bloat only comes back with a REINDEX.

Dropping a partition is a catalog update. No dead tuples, no WAL per row, no vacuum debt, and the space returns the moment the file is unlinked.

-- The same retention, as a metadata operation.
ALTER TABLE executions DETACH PARTITION executions_2026_08_14 CONCURRENTLY;
DROP TABLE executions_2026_08_14;

Detach before dropping, and use CONCURRENTLY where your version supports it. A plain DETACH takes an ACCESS EXCLUSIVE lock on the parent, which blocks every query against the whole table for its duration — a retention job that briefly locks the hot table is an outage with a cron schedule.

The two jobs you must automate

Partitioning fails in production for one boring reason: nobody created next month’s partition. Writes that match no partition error out, and if you have a default partition they all land there instead, which is worse — the default grows unbounded and cannot be detached while it holds rows that belong elsewhere.

CREATE TABLE executions (
    id          bigserial,
    workflow_id bigint      NOT NULL,
    finished_at timestamptz NOT NULL,
    data        jsonb,
    PRIMARY KEY (id, finished_at)      -- key must include the partition column
) PARTITION BY RANGE (finished_at);

CREATE TABLE executions_2026_08_14 PARTITION OF executions
    FOR VALUES FROM ('2026-08-14') TO ('2026-08-15');

Note the primary key: a unique constraint on a partitioned table must include the partition key. That constraint surprises people migrating an existing table, and it is the usual reason a migration stalls.

The two scheduled jobs

Two scheduled jobs, both idempotent, both alerted on failure:

-- Run daily. Create a week ahead so a failed run is not an incident.
DO $$
DECLARE d date;
BEGIN
  FOR d IN SELECT generate_series(
      current_date, current_date + 7, '1 day')::date
  LOOP
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS %I PARTITION OF executions '
      'FOR VALUES FROM (%L) TO (%L)',
      'executions_' || to_char(d, 'YYYY_MM_DD'), d, d + 1);
  END LOOP;
END $$;

Create ahead, not just-in-time. A week of lead time means a failed job is a ticket rather than a page. Just-in-time creation means the first write after midnight fails.

Tooling

pg_partman manages the whole lifecycle: it pre-creates partitions from a configured premake count, applies a retention policy that detaches or drops past a cutoff, and handles the template table so indexes and defaults propagate. Call run_maintenance_proc() on a schedule and it does both jobs.

The honest trade: extensions need superuser to install, and many managed Postgres services do not offer them. If pg_partman is unavailable — common on smaller managed plans — the hand-rolled version above is thirty lines and works everywhere. Say that in an interview; reaching for an extension you cannot install is a real design error.

pg_cron runs the schedule inside the database, which removes an external moving part. Where it is unavailable, an application-side scheduler or a container cron is fine — the important property is that it is monitored, not where it lives.

Migrating a live table

You cannot convert a table in place. The shape that avoids downtime:

1. Create the partitioned table under a new name, with partitions covering the existing range plus lead time.
2. Backfill in batches, oldest first, with a sleep between batches so replicas and vacuum keep up.
3. Dual-write, or switch writes with a short lock once the backfill has caught up to a small tail.
4. Swap names inside one transaction, keeping the old table until you are sure.

Related mechanics in Zero-downtime migrations. For very large tables ATTACH PARTITION on the existing table as the first partition is the shortcut, but it requires a matching CHECK constraint already validated, or the attach scans the whole table under a lock.

What to alert on

• Partition lead time. “The furthest future partition is less than 3 days away” is the alert that prevents the outage. Alert on the gap, not on the job.
• Default partition row count, if you have one. Non-zero means routing is wrong.
• Partition count. Planning time grows with it; a daily partition kept for three years is 1,095 children and the planner will notice.
• Oldest partition age, so a silently failing retention job is visible before the disk is.

Where it breaks in production

• Retention job runs, disk does not drop. It used DELETE, not DROP.
• DETACH without CONCURRENTLY locks the parent and stalls the app.
• Too many partitions. Hourly partitions kept for a year is 8,760 children; query planning slows measurably. Match granularity to retention.
• Queries without the partition key hit every partition. Pruning only works when the key is in the WHERE clause — check with EXPLAIN.
• Indexes not on the template. Create indexes on the parent so they propagate; adding them per-partition by hand drifts.
• Extension unavailable in production but used in dev. Discovered at deploy.

Related

• Sharding and Partitioning - when to partition, and partitioning vs sharding.
• VACUUM, autovacuum, table bloat, and transaction wraparound - the bloat that DELETE leaves behind.
• Zero-downtime migrations - migrating the table without an outage.
• WAL, Checkpoints, and fsync - why a bulk delete is a WAL event.
• n8n at scale: topology, connections and retention
  • the execution-table case this pattern exists for.