Automating Partition Rollover and Retention

← Back to Table Partitioning for Large Spatial Datasets

This page covers the scheduled job that keeps a partitioned spatial table alive: creating next month’s partition before anything needs it, expiring the oldest without locking the hierarchy, and alerting when the job stops.

# Context & When to Use

A partitioned table has a moving edge. On the first day of a month with no partition covering it, every insert fails with no partition of relation found for row — a total write outage caused by a job that stopped running weeks earlier. On the other end, partitions that are never expired accumulate until the retention policy is a fiction and the disk fills.

Both problems are solved by one scheduled function and one alert. The function is idempotent, creates several months ahead, and detaches anything past the retention horizon. The alert watches the runway — how many days of future partitions exist — because that is the only signal that degrades gradually before the outage. Row counts, disk usage and query latency all look perfectly healthy right up to the moment the first insert fails.

This applies to any table using the design from Table Partitioning for Large Spatial Datasets, and doubly so to an audit trail, where retention is a policy commitment rather than a disk-space convenience.

# Runnable Implementation

CREATE OR REPLACE FUNCTION maintain_positions_partitions(
    months_ahead   int DEFAULT 3,
    retain_months  int DEFAULT 24
) RETURNS TABLE (action text, partition_name text)
LANGUAGE plpgsql AS $$
DECLARE
    m      date;
    i      int;
    horizon date := date_trunc('month', now())::date
                    - (retain_months || ' month')::interval;
    part   record;
BEGIN
    -- 1. Create the runway. IF NOT EXISTS makes the whole function idempotent.
    FOR i IN 0..months_ahead LOOP
        m := (date_trunc('month', now()) + (i || ' month')::interval)::date;
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %I PARTITION OF positions
                 FOR VALUES FROM (%L) TO (%L)',
            'positions_' || to_char(m, 'YYYY_MM'), m, m + interval '1 month');
        action := 'ensured'; partition_name := 'positions_' || to_char(m, 'YYYY_MM');
        RETURN NEXT;
    END LOOP;

    -- 2. Expire anything entirely older than the retention horizon
    FOR part IN
        SELECT c.relname,
               (regexp_replace(c.relname, '^positions_', '') || '_01')::date AS starts
        FROM   pg_class c
        JOIN   pg_inherits inh ON inh.inhrelid = c.oid
        WHERE  inh.inhparent = 'positions'::regclass
          AND  c.relname ~ '^positions_\d{4}_\d{2}$'
    LOOP
        CONTINUE WHEN part.starts >= horizon;
        -- CONCURRENTLY: no ACCESS EXCLUSIVE lock on the parent hierarchy
        EXECUTE format('ALTER TABLE positions DETACH PARTITION %I CONCURRENTLY',
                       part.relname);
        EXECUTE format('DROP TABLE %I', part.relname);
        action := 'expired'; partition_name := part.relname;
        RETURN NEXT;
    END LOOP;
END $$;

-- Run it daily; the function is safe to run any number of times
SELECT cron.schedule('positions-partitions', '17 3 * * *',
                     $$SELECT maintain_positions_partitions()$$);

Note the regexp_replace reconstruction of the start date from the partition name. Reading the bound from pg_get_expr(c.relpartbound, c.oid) is more correct but far harder to parse reliably; naming partitions after their range and deriving the date from the name is the pragmatic choice, provided the naming convention is enforced by this same function.

# Key Parameters & Options

# Archiving before the drop

Retention rarely means “delete and forget” — it usually means the data leaves the hot database and lives somewhere cheaper. The detached partition is a plain table, so archiving is an ordinary export with no coordination required.

# The partition is standalone after DETACH: dump it without touching the parent
pg_dump --table=positions_2024_08 --format=custom --compress=9 \
        --file=/archive/positions_2024_08.dump "$DATABASE_URL"

# Or export geometry to a portable format for a data lake
ogr2ogr -f Parquet /archive/positions_2024_08.parquet \
        PG:"$DATABASE_URL" positions_2024_08

Exporting to GeoParquet keeps the geometry queryable outside PostGIS and compresses far better than a custom dump — the format trade-offs are covered in GeoJSON vs GeoParquet Serialization. Whichever route, verify the archive before the drop, and record the archive location in the same job so the audit trail of what was expired is not folklore.

# What the runway alert catches that nothing else does

The value of alerting on the runway is that it degrades linearly and predictably, while every other signal stays flat until the moment of failure. A stalled scheduler produces no errors, no latency change and no disk anomaly — inserts keep landing in the partition that already exists, right up to the last day it covers.

# Gotchas & Failure Modes

• ERROR: no partition of relation "positions" found for row. The runway ran out. Create the missing partition immediately, then fix the scheduler and add the runway alert — the outage is a symptom, not the bug.
• DETACH … CONCURRENTLY cannot run inside a transaction block. It errors with cannot run inside a transaction block, which surprises anyone wrapping maintenance in BEGIN. Run it as its own statement, and note that a failed detach can leave the partition in a pending state that needs ALTER TABLE … DETACH PARTITION … FINALIZE.
• A non-empty default partition. Rows there are invisible to pruning, and creating a partition whose range overlaps them requires a full scan of the default. Alert on count(*) > 0 for the default rather than treating it as a harmless net.
• Retention measured from insert time, not observation time. If the partition key is observed_at but the policy is about when data was received, late-arriving data can be expired the day it lands. Make the key and the policy agree.
• pg_cron running on a replica after failover. The job silently stops when the primary changes if cron.database_name is not configured for the new primary. Alerting on the runway covers this too, which is the point of alerting on the outcome rather than the job.
• Archive verified after the drop. Verify first. A corrupt dump discovered after DROP TABLE is unrecoverable.

# Verification Snippet

-- Runway in days: the single number to alert on
SELECT max(upper(pg_get_expr(c.relpartbound, c.oid)::text::daterange))::date
         - current_date AS runway_days
FROM   pg_class c
JOIN   pg_inherits i ON i.inhrelid = c.oid
WHERE  i.inhparent = 'positions'::regclass
  AND  c.relname ~ '^positions_\d{4}_\d{2}$';
--  runway_days
-- -------------
--          122      → healthy; alert below 30

-- Confirm the oldest partition matches the retention policy
SELECT c.relname
FROM   pg_class c JOIN pg_inherits i ON i.inhrelid = c.oid
WHERE  i.inhparent = 'positions'::regclass
ORDER  BY c.relname LIMIT 1;

# Dry run in staging: the function must be safe to run twice in a row
psql -c "SELECT * FROM maintain_positions_partitions()"
psql -c "SELECT * FROM maintain_positions_partitions()"   # identical output, no errors

# Related

• Table Partitioning for Large Spatial Datasets — the design this job maintains
• Audit Logging for Location Data Access — a table where retention is a policy commitment
• Observability for Spatial Endpoints — where the runway metric belongs

← Back to Table Partitioning for Large Spatial Datasets