Implementing a Partition Retention Policy with Scheduled Jobs

A retention window that lives in a runbook is a window nobody enforces. This guide builds the job that enforces it: policy as data, an idempotent procedure that creates ahead and retires behind, a scheduler, and the metrics that prove it is still running. It implements the operational half of Partition Lifecycle & Retention Management under Partitioning Implementation Patterns & Routing.

Prerequisites

Step 1 — Store the policy as data

One row per table, with an audit trail built in:

Each policy column drives exactly one action The interval column decides the bound width of new partitions. premake decides how many are created ahead of now. retention decides which partitions are detached. archive_first decides whether the retire queue waits for a verified dump before dropping. enabled decides whether the table is processed at all, which is the switch used during migrations. Policy columnAction in the procedureChanging it means interval_unitpremakeretentionarchive_firstenabled width of each new partition's bounds how many future partitions exist which partitions are detached whether a dump must verify before DROP whether this table is processed at all mixed bounds going forward more headroom, no data moved data leaves — needs review recoverability changes a pause during a migration Only the retention row destroys data, which is why it is the one that should require a second reviewer on the pull request that changes it. 
CREATE TABLE partition_policy (
    table_name     text PRIMARY KEY,
    interval_unit  text        NOT NULL CHECK (interval_unit IN ('day','week','month')),
    premake        int         NOT NULL DEFAULT 3 CHECK (premake > 0),
    retention      interval    NOT NULL,
    archive_first  boolean     NOT NULL DEFAULT true,
    enabled        boolean     NOT NULL DEFAULT true,
    updated_at     timestamptz NOT NULL DEFAULT now(),
    updated_by     text        NOT NULL DEFAULT current_user
);

INSERT INTO partition_policy (table_name, interval_unit, premake, retention)
VALUES ('events', 'month', 3, '25 months'),
       ('metrics', 'day', 14, '13 months');

Operational note: The enabled flag matters more than it looks. During a migration or an incident you need a way to stop retention for one table without disabling the job for all of them, and without editing code.

DBA tip: Add a trigger that writes changes to a history table. “Who shortened retention on audit_log?” is a question that gets asked exactly once, and always after the data is gone.

Step 2 — Write one idempotent procedure

The procedure does both ends of the lifecycle so the two can never drift apart:

CREATE OR REPLACE PROCEDURE maintain_partitions()
LANGUAGE plpgsql AS $$
DECLARE
    pol      record;
    period   date;
    child    text;
    bound_lo date;
    bound_hi date;
BEGIN
    IF NOT pg_try_advisory_xact_lock(42_001) THEN
        RAISE NOTICE 'another maintenance run holds the lock; exiting';
        RETURN;
    END IF;

    FOR pol IN SELECT * FROM partition_policy WHERE enabled LOOP
        -- create forward to the premake horizon
        FOR i IN 0..pol.premake LOOP
            period   := date_trunc(pol.interval_unit, now())::date
                        + (i || ' ' || pol.interval_unit)::interval;
            bound_lo := period;
            bound_hi := period + ('1 ' || pol.interval_unit)::interval;
            child    := format('%s_%s', pol.table_name, to_char(period, 'YYYY_MM_DD'));

            EXECUTE format(
                'CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
                child, pol.table_name, bound_lo, bound_hi);
        END LOOP;

        -- retire behind the retention cutoff
        FOR child IN
            SELECT c.relname
            FROM   pg_class c
            JOIN   pg_inherits i ON i.inhrelid = c.oid
            WHERE  i.inhparent = pol.table_name::regclass
              AND  split_part(pg_get_expr(c.relpartbound, c.oid), '''', 4)::date
                   < (now() - pol.retention)::date
        LOOP
            EXECUTE format('ALTER TABLE %I DETACH PARTITION %I', pol.table_name, child);
            INSERT INTO partition_retire_queue (table_name, child_name, detached_at)
            VALUES (pol.table_name, child, now());
        END LOOP;
    END LOOP;
END;
$$;

Note what the procedure does not do: it never drops anything. Detached tables go on a queue, and a separate worker archives and drops them once the archive verifies. Splitting those responsibilities is what makes an accidental policy change recoverable.

Operational note: CREATE TABLE IF NOT EXISTS ... PARTITION OF makes the creation half safe to run at any frequency. Running the procedure hourly is not wasteful — it is what gives a failed run a chance to self-heal before anyone notices.

DBA tip: pg_try_advisory_xact_lock returns false instead of blocking, so a second scheduler exits immediately. Use the blocking variant only if you actually want runs to queue, which for maintenance you rarely do.

One procedure, both ends of the timeline A timeline shows the retention cutoff on the left and the premake horizon on the right. The same procedure creates partitions forward to the horizon and detaches partitions behind the cutoff onto a retire queue. Because both ends move on every run, the number of live partitions stays constant regardless of how long the system runs. detachedon the retire queue live partitions — constant countqueried, vacuumed, backed up pre-createdempty, premake horizon retention cutoff now newest bound detach behind the cutoff create ahead of the horizon Both edges advance on every run, so the live set never grows. A system that only creates has an unbounded partition count and an unbounded bill; a system that only retires eventually has nowhere to put new rows. Keeping both in one procedure means they cannot be deployed independently and drift out of sync.

Step 3 — Schedule it

With pg_cron, scheduling is a row in a table:

SELECT cron.schedule(
  'partition-maintenance',
  '17 * * * *',                       -- hourly, off the hour to avoid pile-ups
  $$CALL maintain_partitions()$$
);

-- confirm it is registered and check recent runs
SELECT jobid, schedule, command, active FROM cron.job;
SELECT runid, job_pid, status, return_message, start_time
FROM   cron.job_run_details
ORDER  BY start_time DESC LIMIT 5;

Operational note: Schedule at a minute that is not zero. Every other cron on the machine fires on the hour, and a DDL job competing with backups and metric scrapes is more likely to hit a lock timeout.

SRE tip: cron.job_run_details grows forever unless you prune it. Add a second cron entry that deletes rows older than thirty days, or the table becomes its own capacity problem.

Step 4 — Publish state as metrics

The job must report the state of the data, not its own exit code:

CREATE OR REPLACE VIEW v_partition_health AS
SELECT p.table_name,
       (SELECT max(split_part(pg_get_expr(c.relpartbound, c.oid), '''', 4)::date)
        FROM pg_class c JOIN pg_inherits i ON i.inhrelid = c.oid
        WHERE i.inhparent = p.table_name::regclass)          AS newest_bound,
       (SELECT min(split_part(pg_get_expr(c.relpartbound, c.oid), '''', 2)::date)
        FROM pg_class c JOIN pg_inherits i ON i.inhrelid = c.oid
        WHERE i.inhparent = p.table_name::regclass)          AS oldest_bound,
       (SELECT count(*) FROM partition_retire_queue q
        WHERE q.table_name = p.table_name AND q.dropped_at IS NULL) AS pending_retire
FROM   partition_policy p;
# queries.yaml for postgres_exporter
partition_health:
  query: |
    SELECT table_name,
           (newest_bound - current_date) AS days_ahead,
           (current_date - oldest_bound) AS days_of_history,
           pending_retire
    FROM v_partition_health
  metrics:
    - table_name:      {usage: "LABEL"}
    - days_ahead:      {usage: "GAUGE", description: "Days until the newest partition bound"}
    - days_of_history: {usage: "GAUGE", description: "Age of the oldest live partition in days"}
    - pending_retire:  {usage: "GAUGE", description: "Detached partitions not yet archived and dropped"}
# alerts that catch every silent failure of this pipeline
pg_partition_health_days_ahead < 21
pg_partition_health_days_of_history > 800
pg_partition_health_pending_retire > 0
The days-ahead metric is what catches a stopped scheduler The days-ahead gauge sits flat at about ninety days while maintenance runs. When the job stops, the value declines by one per day. It crosses the twenty-one day alert threshold sixty-nine days later, giving three weeks of warning before the horizon is reached and inserts have no partition to land in. 90 d60 d30 d0 alert: days_ahead < 21 scheduler stops here — no error anywhere alert fires with 21 days of headroom left inserts fail A monitor watching the job's exit status sees nothing during this whole decline, because a disabled job never runs and never fails. Only a gauge derived from the data itself notices that maintenance has quietly stopped happening.

Operational note: Alert on pending_retire > 0 only after a grace period — a partition detached minutes ago is normal. for: 24h is the right shape.

SRE tip: Include the same three gauges on the team’s main database dashboard, not on a partitioning-specific one. The whole point is that someone sees them when nothing is wrong.

Verification

Run the procedure by hand and confirm both ends moved:

CALL maintain_partitions();

SELECT * FROM v_partition_health;
 table_name | newest_bound | oldest_bound | pending_retire
------------+--------------+--------------+----------------
 events     | 2026-11-01   | 2024-08-01   |              1
 metrics    | 2026-08-17   | 2025-07-01   |              0

newest_bound should be premake intervals ahead of today, oldest_bound should be within one interval of the retention window, and pending_retire should return to zero once the archive worker has run.

Failure mode table

Failure mode Root cause SRE mitigation
Partitions stop being created, silently pg_cron job set inactive, or the extension disabled after a failover to a replica that never had it scheduled alert on days_ahead, not on job status; add cron.job presence to the post-failover checklist
Retention detaches a partition still under legal hold policy row was edited without review require review on partition_policy changes; keep the history table and reconcile it against the data-retention register quarterly
Two runs collide during a long detach a second scheduler (deploy hook plus cron) started while the first was mid-run pg_try_advisory_xact_lock makes the second run exit immediately — verify it is present and returns rather than waits

FAQ

Why put the retention window in a table instead of the job's code?

Because the window is a business decision that changes without a deploy, and because auditors ask who set it and when. A policy row carries the value, the author and the timestamp, and it lets one generic job serve every table. Code changes then only happen when the mechanism changes, not when the number does.

What stops two schedulers from running the job at once?

A transaction-level advisory lock taken at the start of the procedure. pg_try_advisory_xact_lock returns false rather than waiting, so a second invocation exits immediately instead of queueing behind the first and then repeating its work. The lock is released automatically when the transaction ends, including on error.

How should the job report that it ran?

By writing state, not by exiting zero. Publish the newest and oldest partition bounds and the count of detached-but-not-dropped tables as gauges, and alert on those values. A cron that was disabled produces no failed runs at all, so a monitor watching exit codes sees a healthy system right up until inserts start failing.

Back to Partition Lifecycle Retention Management