PostgreSQL Partitioning with pg_partman: Setup and Retention

What partitioning a time-series table buys you, and who should use it

Declarative partitioning lets you define one logical table, the parent, with PARTITION BY RANGE on a timestamp column. Rows are stored in child tables that each cover a fixed, non-overlapping time range, such as one day or one month. Applications insert into and query the parent, and PostgreSQL routes each row to the child whose range contains its timestamp.

For time-series data, this gives you three concrete benefits:

• Cheap retention. Without partitioning, expiring old data means a large DELETE. That statement writes WAL for every removed row and leaves dead tuples that vacuum must later clean up. It often leaves bloat in both the table and its indexes. With partitioning, you drop or detach a whole partition instead. That is mostly a catalog change, and it leaves no dead tuples for vacuum to handle.
• Partition pruning. When a query filters on the partition key, the planner skips partitions whose bounds cannot match. Pruning can also happen during execution for parameterized queries. A query for the last six hours touches one or two partitions instead of the full history.
• Smaller, local indexes. Each partition has its own indexes. The indexes on recent, frequently used partitions stay small enough to remain in memory. Vacuum and analyze also run per partition, so each run is shorter.

The best fit is an append-heavy table where most queries are bounded by time and where a retention policy says data older than N days must go.

Partitioning does not make queries faster in general. A query that does not filter on the partition key still scans every partition. Planning time also grows with the number of partitions, so such queries can end up slower than on a single unpartitioned table.

How native declarative partitioning works

The parent table is declared with PARTITION BY RANGE (event_time) and holds no rows. Each child is created with PARTITION OF ... FOR VALUES FROM (...) TO (...). The lower bound is inclusive and the upper bound is exclusive, so adjacent months share a boundary timestamp without overlapping. You can also build a table separately, load it, and then ATTACH PARTITION it. This is how bulk loads and migrations avoid locking the parent for long. A DEFAULT partition catches any row that matches no range.

Copy                   events (parent, no rows)
                   PARTITION BY RANGE (event_time)
   ┌──────────────┬──────────────┬──────────────┬──────────────┐
   │ events_p01   │ events_p02   │ events_p03   │ events_p04   │  events_default
   │ [Jan, Feb)   │ [Feb, Mar)   │ [Mar, Apr)   │ [Apr, May)   │  (unmatched rows)
   └──────▲───────┴──────────────┴──────────────┴──────▲───────┘
          │                                            │
   drop / detach  ◄──── pg_partman maintenance ────►  premake future
   past retention        (scheduled run)              partitions

Pruning. When the WHERE clause compares event_time to a constant, the planner excludes non-matching partitions at plan time. When the value is only known at run time, such as a prepared-statement parameter or now() - interval '1 day', pruning happens during execution instead. EXPLAIN shows this as "Subplans Removed". If every partition still appears in the plan, the predicate is not prunable. A common cause is wrapping the column in a function.

Constraints. Primary keys and unique constraints must include the partition key, because each partition enforces uniqueness only locally. That means (id, event_time) is allowed, but id alone is not.

Choosing the column. Partition on event time if queries and retention reason about when things happened. Partition on insert time if late-arriving data is common and you want writes to land predictably. Late or out-of-range rows go to the default partition. Keep it close to empty: creating a new partition requires scanning the default for conflicting rows, and that operation fails if any such rows are found.

Copy-- Parent holds no rows; the PK must include the partition key.
CREATE TABLE events (
    id         bigint      NOT NULL,
    event_time timestamptz NOT NULL,  -- event time, not insert time
    payload    jsonb,
    PRIMARY KEY (id, event_time)       -- PRIMARY KEY (id) alone would fail
) PARTITION BY RANGE (event_time);

-- FROM is inclusive, TO is exclusive, so adjacent months never overlap.
CREATE TABLE events_p01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_p02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Catches rows that match no range, such as late or far-future events.
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- Build and load a table separately, then attach it.
CREATE TABLE events_p03 (LIKE events INCLUDING DEFAULTS);
-- ... bulk load events_p03 here ...
-- A CHECK constraint matching the bounds lets ATTACH skip its validation scan.
-- ATTACH still checks events_default for rows that belong in the new range.
ALTER TABLE events_p03 ADD CONSTRAINT events_p03_bounds
    CHECK (event_time >= '2026-03-01' AND event_time < '2026-04-01');
ALTER TABLE events ATTACH PARTITION events_p03
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
ALTER TABLE events_p03 DROP CONSTRAINT events_p03_bounds;  -- now redundant

-- Constant predicate: pruned at plan time, so only events_p02 is listed.
EXPLAIN SELECT * FROM events
 WHERE event_time >= '2026-02-10' AND event_time < '2026-02-11';

-- now() is evaluated at run time, so pruning happens during execution.
-- Use ANALYZE to see 'Subplans Removed' in the output.
EXPLAIN (ANALYZE) SELECT * FROM events
 WHERE event_time >= now() - interval '1 day';

-- Not prunable: the column is wrapped in a function, so every partition is scanned.
EXPLAIN SELECT * FROM events WHERE date_trunc('day', event_time) = '2026-02-10';

Declares a range-partitioned events table with a composite primary key, monthly child partitions, a default partition, and a separately loaded partition attached with ATTACH PARTITION. It then uses EXPLAIN to show plan-time pruning, execution-time pruning, and a predicate that cannot be pruned.

Choosing the partition interval

The interval sets two numbers that pull against each other: how many partitions exist at once, and how large each one is. Many small partitions cost planning time, and any query that cannot be pruned locks every partition it touches. Each partition and each of its indexes also adds catalog entries. Few large partitions make pruning less selective, so a one-hour query may scan a month of data. They also make retention coarse, because you can only drop whole partitions.

Illustrative estimate. Assume 50 million rows per day at about 200 bytes per row on disk, which is roughly 10 GB per day before indexes, with a 90-day retention requirement.

Retention granularity always matches the interval. A monthly partition can only be dropped once its newest row is older than 90 days. Until then you store up to a month of data you no longer need, and your storage estimate has to include it.

For this workload, daily is a reasonable default. It keeps the partition count in the tens and partitions small enough to vacuum and reindex in a short window. If your queries mostly span weeks, switch to weekly. If you need several years of retention, check that a daily interval does not push the partition count into the thousands.

Setting up pg_partman and scheduling maintenance

Install pg_partman into its own schema, conventionally partman, with CREATE SCHEMA partman followed by CREATE EXTENSION pg_partman SCHEMA partman. A dedicated schema keeps its configuration tables and functions apart from application objects and makes grants easier to manage.

Create the parent table yourself with native declarative partitioning: PARTITION BY RANGE on the timestamp column. pg_partman manages partitions but does not replace the core feature. Then register the table with partman.create_parent(), passing the parent table name, the control column, the interval (for example 1 day), and premake, which is how many future partitions to keep ready. The function's signature changed across major pg_partman releases, with parameters renamed, removed, or given new defaults. Check the documentation for the version you actually installed rather than copying an example from a blog post.

pg_partman only creates future partitions when its maintenance routine runs, through run_maintenance() or run_maintenance_proc(). You can schedule it in two ways:

• Built-in background worker. Load the optional background worker through shared_preload_libraries and configure its interval and target database.
• External scheduler. Call the procedure from cron or pg_cron. With pg_cron, cron.job_run_details records each run and whether it failed.

Either way, add an independent check that alerts when the newest partition covers less time ahead than you expect.

This matters because maintenance failures surface late. premake buys you a buffer, so nothing breaks until that buffer runs out, often hours or days after the scheduler died. At that point rows either pile up in the default partition, which is slow to clean up later, or fail outright if no default exists.

Copy-- Install into a dedicated schema
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

-- Parent table uses core declarative partitioning
CREATE TABLE public.events (
    id         bigint      NOT NULL,
    created_at timestamptz NOT NULL,
    payload    jsonb
) PARTITION BY RANGE (created_at);

-- Register with pg_partman. Named arguments are used here, but check
-- create_parent() in the docs for your installed version first.
SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control      => 'created_at',
    p_interval     => '1 day',
    p_premake      => 4   -- keep 4 future daily partitions ready
);

-- Run maintenance every hour with pg_cron.
-- Future partitions only exist if this keeps running.
SELECT cron.schedule('partman-maintenance', '0 * * * *',
                     'CALL partman.run_maintenance_proc()');

-- Did recent runs succeed?
SELECT d.status, d.start_time, d.return_message
FROM cron.job_run_details d
JOIN cron.job j USING (jobid)
WHERE j.jobname = 'partman-maintenance'
ORDER BY d.start_time DESC
LIMIT 5;

-- Separate check: how far ahead does the newest partition reach?
-- Alert if this falls below an agreed threshold, such as 2 days.
SELECT i.child_end_time - now() AS runway
FROM partman.show_partitions('public.events', p_order => 'DESC') p
CROSS JOIN LATERAL partman.show_partition_info(
    p.partition_schemaname || '.' || p.partition_tablename) i
LIMIT 1;

Installs pg_partman in its own schema, registers a daily-partitioned table, schedules maintenance with pg_cron, and adds queries to confirm that maintenance runs succeed and that enough future partitions exist.

Configuring retention: drop versus detach

Retention is enforced by the same maintenance run, so the scheduler matters here too. It is configured per parent in partman.part_config. Set retention to an interval such as 90 days. On each maintenance run, pg_partman removes child partitions whose entire range is older than that interval. retention_keep_table controls what "removes" means. When it is false, the partition is dropped. When it is true, the partition is only detached and becomes an ordinary standalone table. Setting retention_schema moves detached tables into a separate schema, which keeps them out of the application's search path.

For archival, detach first and drop later. A separate job exports each detached table to cold storage, verifies the copy, and only then runs DROP TABLE. Keeping the drop out of maintenance means a failed export cannot destroy data.

Caveats

Retention works one whole partition at a time. With daily partitions and a 90-day rule, rows can survive almost a full extra day past the cutoff. If compliance requires an exact cutoff, run a targeted DELETE on the oldest remaining partition, or use smaller intervals.

Both drop and detach take locks on the parent, so they can block or queue behind application queries. PostgreSQL offers DETACH PARTITION ... CONCURRENTLY, which needs weaker locks but cannot run inside a transaction block. Schedule maintenance outside peak load, and check the locking behavior documented for your PostgreSQL version before relying on either option.

Copy-- 1. Configure retention for one parent table (run once).
--    Expired partitions are detached and moved to an archive schema, not dropped.
CREATE SCHEMA IF NOT EXISTS archive;

UPDATE partman.part_config
SET retention            = '90 days',
    retention_keep_table = true,       -- detach only; false would DROP
    retention_schema     = 'archive'   -- keep detached tables off the app's search_path
WHERE parent_table = 'public.events';

-- 2. Maintenance (normally run by your scheduler) applies the retention rule.
SELECT partman.run_maintenance('public.events');

-- 3. Separate archive job, run from psql. It never runs inside maintenance.
--    The partition name is a placeholder. Naming depends on your pg_partman version and interval.
\copy archive.events_p20260101 TO '/var/archive/events_p20260101.csv' WITH (FORMAT csv, HEADER)

--    Verify the export first, for example by comparing row counts with the file.
SELECT count(*) FROM archive.events_p20260101;

--    Drop only after the copy is confirmed. This frees the disk space.
DROP TABLE archive.events_p20260101;

-- Optional: detach one partition by hand with weaker locking (PostgreSQL 14+).
-- This cannot run inside a transaction block. Check your version's docs for
-- restrictions, such as when a default partition exists.
ALTER TABLE public.events DETACH PARTITION public.events_p20260102 CONCURRENTLY;

This sets pg_partman to detach expired partitions into an archive schema, then shows a separate export-verify-drop job and an optional manual concurrent detach.

Migrating an existing large table

This is the riskiest step in the whole process. A rushed cutover can silently lose rows written mid-copy, or duplicate rows that were copied twice. Plan it as a migration with a rollback path, not as maintenance.

Build and backfill

Create the new partitioned table alongside the old one, with matching columns, constraints and indexes. Let pg_partman create partitions covering the full historical range. Then backfill in batches bounded by time, such as one day or one partition at a time, with each batch in its own transaction. Small batches keep transactions short, which limits lock duration, WAL bursts and replication lag. They also let you pause and resume. Reading from the old table takes only ordinary row-level reads, so live traffic continues.

Validate before switching

For each time range, compare count(*) and a cheap aggregate, such as the sum of an ID or the min/max timestamp, between the old and new tables. A mismatch in a single bucket tells you exactly which batch to redo.

Cutover

New writes must not land in the old table after its final batch is copied. You have two options:

• Short write freeze. Stop writers, copy the final slice, revalidate it, then rename both tables in one transaction.
• Dual writes. Have the application or a trigger write to both tables during the backfill, and make inserts idempotent so overlapping batches don't duplicate rows.

A rename, or swapping a view that points to the table, needs an ACCESS EXCLUSIVE lock. Set a lock_timeout so the swap fails fast instead of queuing behind long-running queries.

Attach instead of copying

If the old data fits a single range, attach the old table as one partition. First add a CHECK constraint that matches the partition bounds, and validate it separately. With that constraint in place, the attach can skip its full validation scan.

Copy-- 1) Validate per day: list only buckets where the old and new tables disagree.
--    An empty result means every day matches. Run this before the cutover.
WITH src AS (
  SELECT date_trunc('day', created_at) AS bucket, count(*) AS rows_n, sum(id) AS id_sum
  FROM events GROUP BY 1
), dst AS (
  SELECT date_trunc('day', created_at) AS bucket, count(*) AS rows_n, sum(id) AS id_sum
  FROM events_new GROUP BY 1
)
SELECT coalesce(s.bucket, d.bucket) AS bucket,
       s.rows_n AS old_rows, d.rows_n AS new_rows,
       s.id_sum AS old_id_sum, d.id_sum AS new_id_sum
FROM src s FULL OUTER JOIN dst d ON s.bucket = d.bucket
WHERE s.rows_n IS DISTINCT FROM d.rows_n
   OR s.id_sum IS DISTINCT FROM d.id_sum
ORDER BY 1;

-- 2) Cutover inside a short write freeze, all in one transaction.
BEGIN;
-- Fail fast instead of queuing behind long queries (and blocking everyone behind us).
SET LOCAL lock_timeout = '3s';
-- EXCLUSIVE mode blocks writes to the old table but still allows reads.
LOCK TABLE events IN EXCLUSIVE MODE;
-- Copy the final slice written since the last batch. ON CONFLICT relies on a
-- unique key that includes the partition column, so re-copied rows are skipped.
-- SELECT * assumes both tables have identical column order.
INSERT INTO events_new
SELECT * FROM events WHERE created_at >= '2026-10-29 00:00:00+00'
ON CONFLICT DO NOTHING;
-- Recheck the final slice here: compare count(*) for that range in both tables.
-- Each rename needs ACCESS EXCLUSIVE. The old table stays available for rollback.
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_new RENAME TO events;
COMMIT;

Compares per-day row counts and ID sums between the old and new tables, then performs the cutover in a single transaction: it blocks writes with a short lock timeout, copies the final slice idempotently, and swaps the names with renames.

Operational checks and monitoring

Treat partition coverage as something to monitor continuously. Most failures are silent: inserts keep succeeding, and only later does someone discover that a month of data landed in the wrong place.

Confirm upcoming partitions exist

Start from partman.part_config, which lists each managed parent with its partition_interval and premake settings. For each parent, join pg_inherits (inhparent to inhrelid) to pg_class. Then read each child's bounds with pg_get_expr(relpartbound, oid). The highest upper bound should sit roughly premake intervals ahead of now. If it does not, maintenance has stopped creating partitions.

Watch the default partition

The default partition should hold no rows. Any count above zero means data arrived outside every defined range. Common causes are clock skew, backfills, or maintenance falling behind.

This also blocks future work. When PostgreSQL creates or attaches a partition, it checks that no row in the default partition falls within the new partition's bounds. If such rows exist, the operation fails, and pg_partman's maintenance fails with it. Creating partitions then stays blocked until you move those rows out, usually by moving them into a temporary table, creating the partition, and reinserting the rows through the parent.

Alerts worth having

• Runway: page when the newest partition's upper bound is less than about two intervals away. Treat this as a hard deadline, because once it passes, new rows land in the default partition.
• Maintenance freshness: pg_partman does not report its own failures to you. Have the scheduled job record a success timestamp, or read your scheduler's run history. Alert when the last successful run is older than a few maintenance cycles.

When to avoid it, alternatives, and impact on cost and engineering time

When to avoid it

Skip partitioning when your common queries don't filter on the partition key. The planner can't prune partitions, so each query touches every partition and also pays extra planning cost. Unique constraints and primary keys must include the partition key, so a table that needs global uniqueness on id alone is a poor fit. Small tables gain little: if a full purge finishes quickly and vacuum keeps up, partitions only add moving parts. Also check that your platform allows pg_partman, because some managed services restrict which extensions you can install.

Alternatives

Cost and time

The upfront work is real. You change keys to composite keys and update application code that assumed single-column uniqueness. You plan and rehearse the migration. You also confirm pg_partman compatibility before every PostgreSQL major upgrade.

The payoff is removing recurring toil: hand-written DDL, purge jobs, and bloat firefighting.

Illustrative estimate (assumed numbers, not a benchmark). Annual hours saved = 12 × (monthly DDL hours + monthly purge hours) + incidents per year × hours per incident.

With 1 h of DDL per month, 2 h of purge work per month, and 3 bloat incidents at 6 h each:

12 × (1 + 2) + 3 × 6 = 54 h per year.

Compare that figure with your one-time migration estimate.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql partitioning pg_partman, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.