Postgres Time-Series Partition Maintenance, Done Right

Partitioning a time-series table in Postgres is the easy part — CREATE TABLE ... PARTITION BY RANGE takes ten minutes. Keeping it fed with new partitions, pruned of old ones, and monitored so a failure pages you instead of your users is the part that actually determines whether this pays off. This is the maintenance layer: pg_partman, run_maintenance_proc() on pg_cron, retention policies, and the queries that tell you it's still working.

📖 Read the full guide: Postgres Partition Maintenance: The Runbook That Works

▶ Watch the video walkthrough: Time-series in vanilla Postgres: partition maintenance that scales
https://www.youtube.com/watch?v=2pe61MS2bdk

The Failure Mode Nobody Plans For: Running Out Of Partitions

The metrics table was partitioned by day. Somebody had built the partitions six months ahead in a one-time script, and then that person left. The runway ended on a Saturday in December. Nobody noticed until Monday morning when the ingest service had been retrying the same batch for 38 hours and the on-call channel was full of "no partition of relation "metrics" found for row".

That is the good version of this failure. It is loud. Your writers break, your alerting fires on error rate, and the fix is one CREATE TABLE ... PARTITION OF away.

The bad version is the one where somebody added a DEFAULT partition as a safety net. Then the writes succeed. For three weeks. And when you finally go to create the correct daily partitions, Postgres has to scan the DEFAULT partition to prove no conflicting rows live there, and it fails outright when it finds them, while holding a strong lock. Now your "quick fix" is a data migration under lock pressure, at whatever hour you discovered it.

Declarative partitioning has been in Postgres since 10 (hash arrived in 11), and creating a partitioned table is genuinely easy. The calendar is the hard part. Everything below is about the calendar.

What Postgres Partition Pruning Actually Buys You

Three real wins.

Pruning. With enable_partition_pruning on (the default), the planner drops partitions that can't match your WHERE clause, and the executor can drop more at runtime when the value only shows up at execution time. You'll see that second kind in EXPLAIN ANALYZE as Subplans Removed. A query for last hour's data on a two-year table reads one child.

Retention that costs nothing. DROP TABLE metrics_p2024_01_14 unlinks files and updates the catalog. Deleting the same 40 million rows produces 40 million dead tuples, a pile of WAL, index bloat, and an autovacuum job that will fight you for the rest of the day. This is the single biggest reason to partition time-series data, and it's why I push back when someone proposes a nightly DELETE ... WHERE created_at < now() - interval '90 days' on a 2 TB table.

Per-partition operations. You can VACUUM one child, REINDEX one child, move cold months to a slower tablespace, and set different storage parameters on hot and cold data.

Now the honest costs.

Planning cost scales with partition count. Postgres has to consider each one before pruning, and at a few thousand partitions you can measure it on short OLTP queries. A unique constraint or primary key on a partitioned table must include every partition key column, so there is no global unique index on id alone. Foreign keys referencing a partitioned table only work from PG 12 onward, so if you're stuck on 11 that constraint isn't available to you at all. An UPDATE that changes the partition key moves the row across children (fine since 11, but it's a delete plus insert internally). And any query that doesn't filter on the time column touches every child, which is how a "fast" dashboard query becomes a 400-partition Append node.

Choosing The Partition Interval: The Arithmetic

Do the division before you pick an interval. Live partition count is retention window divided by interval, plus premake, plus however long you keep detached tables around.

Retention	Interval	Live partitions	Verdict
30 days	1 day	~37 (with premake 7)	Ideal
90 days	1 day	~97	Ideal
1 year	1 day	~372	Fine
2 years	1 day	~730	Workable, watch plan time on short queries
5 years	1 day	~1,825	Don't. Go weekly or monthly
5 years	1 month	~64	Comfortable
7 days	1 hour	~175	Fine, but check partition size

Rules I use: aim for individual partitions in the 10–100 GB range on high-ingest tables, keep total partition count for a single parent under a few thousand, and remember every partition multiplies your index count, which multiplies autovacuum work and pg_class bloat.

One more planner detail. With hundreds of partitions and prepared statements, generic plans can hurt because pruning that would have happened at plan time now happens at execution. If you see plan-time regressions on parameterized queries, plan_cache_mode = force_custom_plan on the session or role is a reasonable lever.

Declarative Partitioning By Time: The Vanilla Build

Copy-paste, works as-is on PG 14+.

CREATE TABLE public.metrics (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    device_id   bigint      NOT NULL,
    metric      text        NOT NULL,
    value       double precision NOT NULL,
    created_at  timestamptz NOT NULL,
    PRIMARY KEY (created_at, id)          -- partition key MUST be in the PK
) PARTITION BY RANGE (created_at);

-- A day partition
CREATE TABLE public.metrics_p2026_08_08 PARTITION OF public.metrics
    FOR VALUES FROM ('2026-08-08 00:00:00+00') TO ('2026-08-09 00:00:00+00');

-- A month partition, on a different tablespace (cold data pattern)
CREATE TABLE public.metrics_p2026_07 PARTITION OF public.metrics
    FOR VALUES FROM ('2026-07-01 00:00:00+00') TO ('2026-08-01 00:00:00+00')
    TABLESPACE slow_disk;

-- Tripwire, not a destination
CREATE TABLE public.metrics_default PARTITION OF public.metrics DEFAULT;

The primary key ordering matters. (created_at, id) gives you a usable index for time-range scans; (id, created_at) gives you an index that mostly sits there taking up space.

Indexes on the parent cascade to all existing and future children since PG 11:

CREATE INDEX ON public.metrics (device_id, created_at DESC);

That takes an ACCESS EXCLUSIVE lock on everything while it builds. On a live table with existing data, do it the two-step way instead:

CREATE INDEX metrics_device_created_idx ON ONLY public.metrics (device_id, created_at DESC);

-- per child, one at a time
CREATE INDEX CONCURRENTLY metrics_p2026_08_08_device_created_idx
    ON public.metrics_p2026_08_08 (device_id, created_at DESC);

ALTER INDEX metrics_device_created_idx
    ATTACH PARTITION metrics_p2026_08_08_device_created_idx;

CREATE INDEX CONCURRENTLY is not supported directly on the parent. Once every child index is attached, the parent index flips from invalid to valid on its own.

About the DEFAULT partition: keep it, monitor it, and expect it to be empty forever. Its job is to convert a hard outage into an alert. Note that it also blocks DETACH PARTITION CONCURRENTLY, which is a real tradeoff I'll come back to.

Why The Cron Script You're About To Write Will Bite You

You are going to write a shell script that runs psql -c "CREATE TABLE ... PARTITION OF ..." for next month. For a small, low-churn table with monthly partitions and no retention requirement, that script is genuinely fine and I will not argue with you about it. Ship it.

For anything with daily partitions and a retention policy, here is what the naive version misses.

No premake buffer. If it creates exactly one partition and the run fails, you have zero runway. One failed cron run equals an outage on a boundary you didn't watch.

Lock queueing. ATTACH PARTITION and non-concurrent DETACH PARTITION take ACCESS EXCLUSIVE on the parent. That lock queues behind your long-running analytics query, and every new query queues behind the lock. A 4-second DDL becomes a 6-minute full-table stall. Always:

SET lock_timeout = '5s';

and retry rather than wait.

No index templating. Someone adds an index by hand to last month's child at 2am during an incident. Every child created after that lacks it. Nobody finds out until a query plan changes six weeks later.

Timezone drift. Your cron host runs UTC, the database session default is America/Chicago, and your boundary is off by five or six hours depending on the season. This produces a partition gap exactly once a year.

No idempotency. The retry after a transient failure errors on "relation already exists", the exit code is nonzero, and now your monitoring is trained to ignore this job.

No last-success alerting. This is the killer. Most teams alert on job errors and nobody alerts on job silence. The pattern I keep running into is a maintenance job that stopped running in March and got discovered in May, by which point you're doing surgery on a DEFAULT partition instead of a routine fix.

pg_partman Tutorial: create_parent And What Each Argument Does

CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

pg_partman is BSD-licensed and maintained by Crunchy Data. It manages the lifecycle: pre-creating future children, applying retention, and keeping child properties consistent.

SELECT partman.create_parent(
    p_parent_table := 'public.metrics',
    p_control      := 'created_at',
    p_interval     := '1 day',
    p_type         := 'range',
    p_premake      := 10
);

Version warning, and this one has burned people. pg_partman 5.0 dropped the old trigger-based partman type entirely, supports only native declarative partitioning, renamed function arguments with a p_ prefix, and requires PostgreSQL 14 or newer. Snippets you find on Stack Overflow written for 4.x will pass p_type := 'native' or use unprefixed argument names, and they will fail on 5.x with signature errors. Check SELECT extversion FROM pg_extension WHERE extname='pg_partman' before you copy anything, including this article.

premake defaults to 4. For daily partitions that's four days of runway, which is less than a long holiday weekend plus a slow incident response. I use 10 for daily and 4 for monthly. The rule: premake should be at least twice your worst realistic window of unattended breakage.

This is a real dependency. It's another extension to build or install on every replica and every environment, another thing to verify before a major-version upgrade, and another API that can change under you. I still install it, because the alternative is 300 lines of bash that nobody owns.

The Template Table: How Indexes And Constraints Reach New Children

pg_partman creates a template table, partman.template_public_metrics, for properties that Postgres does not automatically propagate to newly created children. Historically that means certain unique indexes and some constraints. Regular indexes created on the parent propagate natively, so you generally do not need the template for those.

The mistake I see: someone puts an index only on the template and assumes existing children got it (they didn't), or puts it only on the parent and assumes a unique index got enforced per-child (Postgres won't let you create one that omits the partition key on the parent at all).

Belt and braces for a per-child unique index:

-- propagates to future children
CREATE UNIQUE INDEX ON partman.template_public_metrics (device_id, metric, created_at);

Then verify parity across children before you trust it:

SELECT child.relname AS partition,
       count(x.indexrelid) AS index_count,
       array_agg(i.relname ORDER BY i.relname) AS indexes
FROM pg_class parent
JOIN pg_inherits inh ON inh.inhparent = parent.oid
JOIN pg_class child  ON child.oid = inh.inhrelid
LEFT JOIN pg_index x ON x.indrelid = child.oid
LEFT JOIN pg_class i ON i.oid = x.indexrelid
WHERE parent.oid = 'public.metrics'::regclass
GROUP BY child.relname
ORDER BY index_count, child.relname;

Sort by count and the odd children float to the top.

Postgres Partition Retention Policy: DROP vs DETACH vs Archive

Retention lives in partman.part_config and you change it with ordinary SQL:

UPDATE partman.part_config
SET retention              = '90 days',
    retention_keep_table   = true,   -- detach only, don't drop
    retention_keep_index   = true,
    premake                = 10,
    infinite_time_partitions = true
WHERE parent_table = 'public.metrics';

Dropping Old Partitions In Postgres: What Each Setting Does

retention_keep_table = true: expired children get detached from the set and left on disk. Space is not returned. This is the correct setting for your first cycle.
retention_keep_table = false: DROP TABLE. Space returns immediately. This is what you want steady-state.
retention_keep_index: whether indexes survive on the detached table.

Shortening retention takes effect on the next maintenance run. If retention_keep_table is false, that is a destructive change with roughly the latency of your cron schedule. Treat an UPDATE to that column with the same care as a DROP TABLE, because that's what it is.

For archival, in rough order of how often I use them:

-- PG 14+, avoids holding ACCESS EXCLUSIVE on the parent for the duration
ALTER TABLE public.metrics DETACH PARTITION public.metrics_p2026_05_01 CONCURRENTLY;

Restrictions worth knowing: it cannot run inside a transaction block, and it is not supported when the partition set has a DEFAULT partition. That second one is the actual tradeoff of keeping your tripwire. On tables with heavy read concurrency I've dropped the DEFAULT partition specifically to get concurrent detach, and accepted loud insert failures as the alerting mechanism instead.

After detaching: ALTER TABLE ... SET TABLESPACE cold to move it to cheap disk, pg_dump -t the single child to object storage, or attach it to a foreign server. Whichever you pick, write it down before the first drop, not after.

Scheduling Maintenance: run_maintenance_proc On pg_cron

pg_cron needs to be in shared_preload_libraries and runs jobs against the database named in cron.database_name — not necessarily whatever database you happened to be connected to when you scheduled the job. Get bitten by that mismatch once and you'll double-check it forever after. Set both, restart, then:

SELECT cron.schedule(
    'partman-maintenance',
    '7 1 * * *',
    $$CALL partman.run_maintenance_proc()$$
);

Use the procedure, not the run_maintenance() function. run_maintenance_proc() commits between operations, so a large partition set doesn't run as one long transaction holding locks and blocking vacuum. On a set with 700 children that difference is the whole ballgame.

Pick an odd minute like :07. Every job on the planet runs at :00 and you don't want your DDL queueing behind a backup snapshot.

The alternative is pg_partman's background worker, pg_partman_bgw, which also requires a shared_preload_libraries entry and a restart. It's clean, but most managed platforms won't let you load it, and pg_cron is more commonly available. If neither is possible, an external scheduler calling the same CALL over psql is fine, as long as you also monitor last-success from inside the database.

Set p_analyze deliberately. Auto-analyzing every new empty child on every run is wasted work on large sets. If you have pg_jobmon installed, pg_partman logs to it, which gives you a durable audit trail beyond cron.job_run_details.

Monitoring: The Four Queries I Actually Run

1. Runway. How many future partitions exist per parent, and how far the calendar is covered.

SELECT pc.parent_table,
       pc.premake,
       count(*) FILTER (WHERE b.range_start > now()) AS future_partitions,
       max(b.range_start) AS covered_through
FROM partman.part_config pc
JOIN pg_class p     ON p.oid = pc.parent_table::regclass
JOIN pg_inherits i  ON i.inhparent = p.oid
JOIN pg_class c     ON c.oid = i.inhrelid
CROSS JOIN LATERAL (
    SELECT (regexp_match(pg_get_expr(c.relpartbound, c.oid),
                         $$FROM \('([^']+)'\)$$))[1]::timestamptz AS range_start
) b
GROUP BY 1, 2
ORDER BY 1;

Alert when future_partitions < premake / 2.

2. Last successful maintenance run.

SELECT j.jobname, d.status, d.start_time, d.end_time,
       age(now(), d.end_time) AS since_last_run,
       d.return_message
FROM cron.job j
LEFT JOIN LATERAL (
    SELECT * FROM cron.job_run_details r
    WHERE r.jobid = j.jobid
    ORDER BY r.start_time DESC LIMIT 1
) d ON true
WHERE j.jobname = 'partman-maintenance';

Alert on since_last_run > 26 hours, not just on status <> 'succeeded'. Silence is the failure mode.

3. DEFAULT partition rows. This should always be zero.

SELECT count(*) FROM public.metrics_default;

If you don't know the DEFAULT child's name offhand, find it first:

SELECT c.relname
FROM pg_class c
JOIN pg_inherits i ON i.inhrelid = c.oid
WHERE i.inhparent = 'public.metrics'::regclass
  AND pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT';

Any nonzero result on the count is a page, not a ticket.

4. Per-partition size and row estimate, so you can forecast retention pressure.

SELECT c.relname,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       pg_size_pretty(pg_relation_size(c.oid))       AS heap_size,
       c.reltuples::bigint                            AS est_rows
FROM pg_class c
JOIN pg_inherits i ON i.inhrelid = c.oid
WHERE i.inhparent = 'public.metrics'::regclass
ORDER BY c.relname DESC
LIMIT 40;

Watch the trend, not the number. When daily size doubles, your 90-day retention window is now a 180-day storage bill.

If you'd rather not wire four checks into your monitoring stack by hand, MyDBA's free health check covers this ground automatically — flagging stalled partition jobs, DEFAULT partition leakage, and growth trends against your retention window.

Migrating An Existing Monolith Table Without Downtime

The path I use most often for a large existing table:

Rename the original: ALTER TABLE metrics RENAME TO metrics_old;

Create the new partitioned parent with the same column definitions.
Register with create_parent(), premake generously.
Cut writes over to the new parent.

Backfill in batches with partman.partition_data_proc(), which moves rows into the correct children and commits per batch:

CALL partman.partition_data_proc(
    p_parent_table := 'public.metrics',
    p_interval     := '1 day',
    p_wait         := 1
);

partition_data_time() is the function equivalent when you want to drive batching yourself.

pg_partman also supports an offline approach where the original table becomes the DEFAULT partition of the new parent, and you then move data out of it incrementally. It's less disruptive to application config, but you're living with a populated DEFAULT partition until backfill completes, which blocks concurrent detach and makes new overlapping partitions expensive to create.

Whichever you pick: set lock_timeout on the backfill session, size batches so each commit is seconds not minutes, and run it during your actual traffic trough rather than the one on the dashboard from last year. If your application can tolerate it, dual-write to both tables during migration and swap reads at the end. It's more code, and it's the only version where a failed backfill costs you nothing.

Postgres vs TimescaleDB: When Timescale Earns Its Keep

Timescale is a good product and I'm not going to pretend otherwise. It also isn't the answer to "partition maintenance is annoying", because pg_partman plus pg_cron already answered that.

Reach for TimescaleDB when you need:

Continuous aggregates. Materialized views over hypertables refreshed incrementally rather than recomputed. If you're about to hand-roll a cron'd INSERT ... ON CONFLICT DO UPDATE rollup job, this is the feature you're paying for.
Columnar compression on old chunks. Substantial storage reduction on cold time-series data. Vanilla has no equivalent.
Time-series SQL sugar. time_bucket, gapfill, last()/first(). You can write around all of it, and the queries are uglier.

Chunk management with no external extension. Timescale handles creation and retention internally, so pg_partman does not enter the picture at all.

Stay vanilla when:

You're on Amazon RDS for PostgreSQL or Aurora PostgreSQL, where Timescale isn't offered as an installable extension. This decides it for a lot of teams before any technical discussion.

You want no license ambiguity around the advanced features.
You do major-version upgrades on your own schedule and don't want a third-party extension's release calendar gating them.

The decision rule I give people: if your workload is ingest plus range queries plus retention, use native partitioning with pg_partman. If you are building rollups and dashboards over years of data and storage cost is a line item somebody complains about, evaluate Timescale on those two features specifically.

A Partition Maintenance Checklist You Can Run Today

premake is at least twice your worst unattended-outage window. Daily partitions: 10 minimum.
A DEFAULT partition exists, or you've deliberately dropped it to enable concurrent detach and accepted loud insert failures instead.
If the DEFAULT partition exists, its row count is monitored and alerts at nonzero.

Retention is configured in part_config with retention_keep_table = true for at least the first full cycle. Eyeball what it detaches before you let it drop.
The maintenance job alerts on last-success age, not only on errors.
Maintenance uses run_maintenance_proc(), not run_maintenance().

Every DDL script sets lock_timeout and retries rather than blocking the parent.
Index parity across children is checked, and the template table carries anything Postgres won't inherit.
The primary key includes the partition key, and its column order favors time-range scans.

Partition count per parent is reviewed quarterly against plan time on your shortest query.
An archive path (tablespace move, dump to object storage, foreign table) is documented and tested before the first DROP.
Your pg_partman version is pinned and recorded, and every snippet in your runbook says which major version it targets.

pg_cron's cron.database_name points at the right database, and someone other than you knows that.

Partitioning takes an afternoon. The maintenance around it is what determines whether you hear about this table again.