Partition Lifecycle & Retention Management

A partition has a life: it is created before it is needed, absorbs writes while it is current, is queried less as it ages, and eventually costs more to keep than it earns. Managing that arc is what turns partitioning from a query optimisation into a storage strategy. This topic sits under Partitioning Implementation Patterns & Routing and picks up where automated partition creation workflows ends — creation is one edge of the lifecycle, and everything after it is here.

Problem Framing

A metrics table has been partitioned daily for three years. Nobody has ever deleted anything, because the retention policy says “keep at least 13 months” and no one was asked to decide the maximum. The table now carries 1,095 partitions, the nightly backup takes eleven hours, planning time on the hot query has tripled, and the storage bill is four times what the data younger than a year would cost.

None of this was a decision. Each individual month looked affordable, the partitions were created automatically, and no job existed to remove them. That is the default outcome of a partitioning project that automates creation and stops there: growth is automatic, removal is manual, and manual work that has no deadline never happens.

The Four Stages

Every partition passes through four stages, and each has a different cost profile, access pattern and correct storage medium.

The stages are useful because they turn vague questions (“how long should we keep data?”) into concrete, separately answerable ones: how far ahead do we create, when does a partition stop being hot, when is it compressed, and when is it removed. Each has a different owner — the last one is usually legal rather than engineering.

Retention Policy as Configuration

Retention rules belong in a table, not in a script. Encoding them as data makes them auditable, lets different tables have different windows, and allows the maintenance job to be generic:

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,
    hot_window        interval    NOT NULL,
    warm_window       interval    NOT NULL,
    retention         interval    NOT NULL,
    archive_target    text,
    updated_at        timestamptz NOT NULL DEFAULT now(),
    updated_by        text        NOT NULL DEFAULT current_user
);

INSERT INTO partition_policy
  (table_name, interval_unit, premake, hot_window, warm_window, retention, archive_target)
VALUES
  ('events',   'month', 3, '60 days',  '365 days', '25 months', 's3://archive/events/'),
  ('metrics',  'day',  14, '7 days',   '90 days',  '13 months', 's3://archive/metrics/'),
  ('audit_log','month', 6, '90 days',  '730 days', '7 years',   's3://archive/audit/');

The updated_by and updated_at columns are not decoration. When an auditor asks why a table holds thirteen months of data, the answer needs a date and a name attached to it, and a row in this table is a far better answer than a commit message in a repository that has since been reorganised.

The Retention Job

One job, driven by the policy table, handles every table. It runs the same four steps in the same order every time, and every step is safe to repeat:

-- 1. ensure future partitions exist up to the premake horizon
-- 2. detach partitions past the retention cutoff
-- 3. archive detached tables, verify, then drop
-- 4. publish metrics: newest bound, oldest bound, detached count

# retention.py — invoked hourly; every step is idempotent
def run(conn):
    for policy in fetch_policies(conn):
        ensure_future_partitions(conn, policy)
        for part in partitions_past_cutoff(conn, policy):
            detach(conn, part)                  # catalog operation, seconds
        for part in detached_tables(conn, policy):
            if not archived(part):
                archive(part, policy.archive_target)
            if verify_archive(part, policy.archive_target):
                drop_table(conn, part)          # space returned here
        publish_metrics(conn, policy)

The ordering matters more than the implementation. Archive before drop, verify before drop, and never combine the detach and the drop into one step — the gap between them is the window in which a mistake is recoverable.

Storage Tiering by Age

Because a range partition’s age is encoded in its bounds, tiering is mechanical rather than heuristic. PostgreSQL exposes it through tablespaces:

-- warm partitions move to slower, cheaper storage
ALTER TABLE events_2026_02 SET TABLESPACE slow_storage;

-- new partitions are created directly on fast storage
CREATE TABLE events_2026_09 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01')
  TABLESPACE fast_storage;

ALTER TABLE ... SET TABLESPACE rewrites the table and holds an ACCESS EXCLUSIVE lock for the duration, which is acceptable on a warm partition nobody is writing and unacceptable on a hot one. That constraint is exactly why tiering is a lifecycle operation: it is cheap at the hot-to-warm transition and expensive at any other moment.

Monitoring the Lifecycle

Four metrics, published by the maintenance job itself, cover every failure mode of the pipeline:

-- newest bound: how much headroom before inserts have nowhere to go
SELECT max(to_date(right(relname, 7), 'YYYY_MM')) AS newest_bound
FROM   pg_class c JOIN pg_inherits i ON i.inhrelid = c.oid
WHERE  i.inhparent = 'events'::regclass;

-- oldest bound: is retention actually running?
-- default partition rows: is an unexpected value arriving?
SELECT count(*) FROM events_default;

-- detached but not dropped: is archival leaking disk?
SELECT c.relname, pg_size_pretty(pg_total_relation_size(c.oid))
FROM   pg_class c
WHERE  c.relkind = 'r'
  AND  c.relname ~ '^events_\d{4}_\d{2}$'
  AND  NOT EXISTS (SELECT 1 FROM pg_inherits WHERE inhrelid = c.oid);

Who Owns Each Decision

The lifecycle spans three groups whose incentives differ, and most retention failures are ownership failures rather than technical ones. Naming the owner of each number, in writing, removes the ambiguity that otherwise resolves itself as “keep everything forever”.

Engineering owns the mechanism and the horizon. How partitions are created, how far ahead, how they are archived, and what the alerts are. These are implementation choices with no external constraints, and they should be changeable without consulting anyone.

The data owner owns the retention window. Usually a product manager or a compliance function, occasionally legal. The number is a business commitment — to customers, to regulators, or to an internal analytics roadmap — and engineering’s role is to implement it and to make its cost visible, not to choose it.

Finance owns the tiering thresholds, indirectly. The decision to move partitions to cheaper storage after ninety days is a cost decision informed by an access-pattern measurement. Engineering supplies the measurement; the threshold follows from what the organisation is willing to spend.

The reason to write this down is that the failure mode is silence. Nobody objects to keeping more data, the cost is diffuse, and the person who would have to approve deletion is rarely asked. A policy table with an updated_by column and a quarterly review turns a decision nobody is making into a decision someone declined to change — which is a materially different position when an auditor or a bill arrives.

The conversation that actually reduces cost

The highest-leverage question is not “can we compress this?” but “what is the maximum we are allowed to keep?”. Compliance documents almost always specify a minimum retention and are silent on the maximum, and systems default to infinity in the absence of an instruction. Turning “at least twelve months” into “exactly thirteen months” typically removes more storage than any amount of compression work, costs one meeting, and requires no maintenance window at all.

Bring three numbers to that meeting: the current storage cost of data older than the minimum, the query volume touching it (usually under one percent), and the restore time from archive if it is ever needed. With those three, the conversation takes ten minutes. Without them it takes a quarter and ends in a decision to keep everything.

Late-arriving data is the exception that shapes the schedule

Every retention design assumes a partition stops changing once the calendar moves past it, and every real system has some traffic that violates that assumption: a mobile client that was offline for a week, a batch import replaying a failed day, a correction posted against last quarter. The volume is small and the consequences are not, because a partition that is detached while writes are still arriving for it produces insert failures rather than a graceful fallback.

Two settings absorb this. The first is a lag between the partition’s close and its eligibility for archival — typically one full interval, so a monthly partition is only archived once the month after it has also closed. The second is a DEFAULT partition that catches anything arriving for a range that no longer exists, paired with an alert on its row count so those rows are noticed rather than silently accumulating in an unprunable table.

Measure the real tail before choosing the lag. A single query against the current partition — the maximum difference between a row’s business timestamp and its insertion timestamp — gives the answer directly, and it is almost always longer than the team’s intuition. Ninety-ninth-percentile lateness of a few hours with a maximum of eleven days is a common shape, and it means an archival job running one day after the month closes will hit rows that were still arriving.

Failure Modes

Common Mistakes

• Automating creation without automating removal. Growth becomes autonomous, cleanup stays manual, and manual work with no deadline never happens.
• Deleting rows instead of dropping partitions. It converts a metadata operation into hours of WAL, bloat and vacuum work — see the comparison in detaching and archiving old partitions.
• Dropping before verifying the archive. The archive is only a backup once something has proven it can be read.
• Treating retention as an engineering decision. The maximum is a legal and product decision; engineering owns the mechanism, not the number.
• Alerting on job exit codes. A cron that was disabled reports no failures. Alert on the state of the data — bounds and ages — not on the health of the process.

FAQ

Related

• Detaching and Archiving Old Partitions in PostgreSQL — the mechanics of detach, dump, verify and drop
• Implementing a Partition Retention Policy with Scheduled Jobs — turning the policy table into a job that runs itself
• Compressing Cold Partitions for Cheaper Storage — column compression, tablespaces and what compresses well
• Restoring an Archived Partition into a Live Table — the reverse path, and why it must be rehearsed
• Automated Partition Creation Workflows — the other end of the lifecycle, where partitions come from