Enforcing Retention Windows with PostgreSQL Partitions

Deleting expired rows one DELETE at a time is how retention policies quietly stop being enforced, and this page replaces that with partition drops that finish in milliseconds. It is a focused part of Tenant Offboarding & Data Retention Workflows: making the retention window a physical property of the table rather than a cron job's good intentions.

Problem Framing

A DELETE FROM usage_event WHERE occurred_at < now() - interval '90 days' on a billion-row table takes hours, generates enormous write-ahead log volume, leaves the space to autovacuum, and blocks behind long-running transactions. Teams respond by batching it, then by running it less often, then by letting it fail silently — at which point the published retention policy and the actual data diverge, and nobody notices until an auditor asks how old the oldest row is.

Declarative range partitioning by time changes the operation completely. Expiring a period becomes DROP TABLE, which is a catalogue operation: constant time, no dead tuples, no vacuum debt, and the disk space is returned immediately. The retention window stops being a query and becomes the set of partitions that exist.

The multi-tenant wrinkle is that retention is rarely uniform. An enterprise contract may require twenty-four months of audit history where the default is twelve, and a regulated tenant may require less rather than more. That means partitions cannot be dropped purely by age — they can only be dropped when no tenant with a longer window still has rows in them, which is why the design pairs time partitions with a per-tenant override table and a longest-window rule.

Retention becomes the set of partitions that exist. The frontier moves only as far as the longest tenant window allows.

Step-by-Step Guide

1. Partition by the time column the policy is written against

The partition key must be the column the retention policy names — usually the event time, not the insert time — or the drop will remove the wrong rows.

CREATE TABLE usage_event (
  id           bigint GENERATED ALWAYS AS IDENTITY,
  tenant_id    uuid        NOT NULL,
  occurred_at  timestamptz NOT NULL,
  meter_key    text        NOT NULL,
  quantity     numeric     NOT NULL,
  PRIMARY KEY (occurred_at, id)          -- partition key must be in the primary key
) PARTITION BY RANGE (occurred_at);

CREATE INDEX ON usage_event (tenant_id, occurred_at);

The primary key requirement is the first constraint that surprises people: PostgreSQL cannot enforce uniqueness across partitions unless the partition key is part of the key.

2. Create partitions ahead of the write head, automatically

A missing future partition is an insert failure, so the job that creates them must run well ahead and be monitored as a production dependency.

CREATE OR REPLACE FUNCTION ensure_future_partitions(months_ahead int DEFAULT 3) RETURNS void AS $$
DECLARE
  m date;
  part text;
BEGIN
  FOR i IN 0..months_ahead LOOP
    m    := date_trunc('month', now())::date + (i || ' months')::interval;
    part := format('usage_event_%s', to_char(m, 'YYYY_MM'));
    IF to_regclass(part) IS NULL THEN
      EXECUTE format(
        'CREATE TABLE %I PARTITION OF usage_event FOR VALUES FROM (%L) TO (%L)',
        part, m, (m + interval '1 month')::date);
      EXECUTE format('CREATE INDEX ON %I (tenant_id, occurred_at)', part);
    END IF;
  END LOOP;
END; $$ LANGUAGE plpgsql;

3. Record per-tenant retention overrides

CREATE TABLE tenant_retention (
  tenant_id  uuid PRIMARY KEY,
  dataset    text NOT NULL DEFAULT 'usage_event',
  months     integer NOT NULL,
  reason     text NOT NULL,            -- contract clause or regulation
  CHECK (months BETWEEN 1 AND 120)
);

-- The frontier is set by the longest window in force, not by the default.
CREATE OR REPLACE VIEW retention_frontier AS
SELECT date_trunc('month', now())
     - (greatest(
         (SELECT max(months) FROM tenant_retention WHERE dataset = 'usage_event'),
         12                                        -- platform default
       ) || ' months')::interval AS drop_before;

4. Detach before dropping, so a mistake is recoverable

DETACH removes the partition from the parent without destroying it, giving a window in which the data can be re-attached if the frontier was computed wrongly.

DO $$
DECLARE
  frontier timestamptz := (SELECT drop_before FROM retention_frontier);
  p record;
BEGIN
  FOR p IN
    SELECT c.relname,
           (regexp_match(pg_get_expr(c.relpartbound, c.oid), 'FROM \(''([^'']+)'''))[1]::timestamptz AS lower
    FROM   pg_class c
    JOIN   pg_inherits i ON i.inhrelid = c.oid
    WHERE  i.inhparent = 'usage_event'::regclass
  LOOP
    IF p.lower < frontier THEN
      EXECUTE format('ALTER TABLE usage_event DETACH PARTITION %I CONCURRENTLY', p.relname);
      INSERT INTO retention_detachment (relname, detached_at, drop_after)
      VALUES (p.relname, now(), now() + interval '7 days');
    END IF;
  END LOOP;
END $$;

DETACH ... CONCURRENTLY avoids the ACCESS EXCLUSIVE lock that would otherwise stall every query against the parent — worth the extra step on a table that is hot.

5. Drop on a delay, and record that you did

-- Runs daily; the seven-day gap is the recovery window.
DO $$
DECLARE d record;
BEGIN
  FOR d IN SELECT * FROM retention_detachment WHERE drop_after < now() AND dropped_at IS NULL LOOP
    EXECUTE format('DROP TABLE IF EXISTS %I', d.relname);
    UPDATE retention_detachment SET dropped_at = now() WHERE relname = d.relname;
  END LOOP;
END $$;

The retention_detachment table doubles as evidence: it shows, per period, exactly when data left the system, which is what a retention control needs to demonstrate.

Five stages, one of which is deliberately a week of doing nothing. That week is what makes a wrong frontier survivable.

Verification

-- 1. Nothing older than the frontier is still attached.
SELECT min(occurred_at) AS oldest_row, (SELECT drop_before FROM retention_frontier) AS frontier
FROM   usage_event;
-- expected: oldest_row >= frontier

-- 2. Future partitions exist for at least the next two months.
SELECT count(*) AS future_partitions
FROM   pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE  i.inhparent = 'usage_event'::regclass
  AND  pg_get_expr(c.relpartbound, c.oid) LIKE '%' || to_char(now() + interval '2 months', 'YYYY-MM') || '%';
-- expected: >= 1

-- 3. Query plans still prune — partitioning is worthless if they do not.
EXPLAIN (COSTS OFF)
SELECT sum(quantity) FROM usage_event
WHERE  tenant_id = $1 AND occurred_at >= now() - interval '7 days';
-- expected: only the last one or two partitions appear in the plan

Pruning is the check people skip and then regret. A query that filters on a computed expression rather than a plain comparison against the partition key will scan every partition, turning a well-designed retention scheme into a performance regression.

The difference is not incremental. It is the reason a partitioned retention policy stays enforced and a delete-based one does not.

Failure Modes & Gotchas

Symptom: inserts start failing with "no partition of relation found for row". Cause: the partition-creation job stopped and the write head moved past the last partition. Fix: create several months ahead, alert when the newest partition is under thirty days away, and treat that alert as page-worthy.
Symptom: a tenant with a twenty-four-month contract loses data at twelve months. Cause: the frontier uses the platform default instead of the longest override in force. Fix: compute the frontier as the maximum of the default and every active override, and re-check it whenever a contract changes.
Symptom: the daily job blocks all queries against the table. Cause: plain DETACH PARTITION takes an ACCESS EXCLUSIVE lock on the parent. Fix: use DETACH ... CONCURRENTLY, and run the job outside peak hours anyway.
Symptom: queries got slower after partitioning. Cause: predicates do not prune — a function wraps the partition key, or the filter uses a different column. Fix: filter directly on the partition key with plain comparisons, and assert pruning in a plan test.

FAQ

How wide should partitions be? Match them to the retention granularity and keep the total count modest. Monthly partitions with a twelve-to-twenty-four-month window give twelve to twenty-four live partitions, which planners handle comfortably. Daily partitions over two years produce more than seven hundred, at which point planning time and catalogue bloat become real. If a dataset genuinely needs daily expiry, partition daily but keep the window short.

Can this handle a single tenant's deletion request? No — partitions are time-shaped, not tenant-shaped, so a tenant's rows are spread across every live partition. Per-tenant deletion remains a DELETE ... WHERE tenant_id = $1 per partition, or crypto-shredding for encrypted payloads. Sub-partitioning by tenant hash helps only for the largest handful of tenants and multiplies partition count for everyone else; it is rarely worth it.

What about the audit log — can it be partitioned the same way? Yes, and it should be, since audit retention is usually the longest and largest dataset you hold. The one extra consideration is that hash-chained audit records must not be dropped mid-chain without recording that the chain now starts later, or verification will report a break at the boundary. Store the last checkpoint of each dropped period so a verifier can start from a known-good anchor.

What happens to a partitioned table's indexes and constraints? Each partition carries its own physical indexes, created either directly or by declaring them on the parent so new partitions inherit them. That is generally an advantage: indexes stay small, and an index rebuild affects one period rather than the whole table. The constraint worth knowing about is uniqueness — a unique index can only be enforced within a partition unless the partition key is part of it, which is why the partition key belongs in the primary key and why a globally unique business identifier needs its own thought.