PostgreSQL Range Partitioning for Time-Series Tables with pg_partman

What range partitioning is and when it is worth it

Declarative range partitioning splits one logical table into child tables. Each child holds a contiguous slice of a key, typically a timestamptz column such as created_at. You declare the parent with PARTITION BY RANGE, and each partition covers a bound like one day or one month. PostgreSQL routes each inserted row to the matching child. Queries still target the parent.

For time series postgres workloads, this layout pays off in three ways:

• Retention becomes a metadata operation. Expiring a month of data means detaching or dropping one partition. A large DELETE leaves dead tuples for autovacuum to reclaim and bloats indexes along the way. Dropping a partition does neither.
• Indexes stay smaller. Each partition has its own indexes, sized to its slice. Recent, hot partitions are more likely to fit in memory, and rebuilding one partition's index is a bounded job.
• Partition pruning skips irrelevant data. A query filtered to the last 24 hours touches only the partitions whose bounds overlap that window.

Partitioning does not make a single-partition lookup faster than a well-indexed unpartitioned table of the same size. A B-tree lookup is already cheap. The gains come from pruning, retention and maintenance. Each query also pays some planning overhead per partition.

How it works: parent tables, child partitions and bounds

You declare the parent with PARTITION BY RANGE (created_at). The parent stores no rows itself. It holds the column definitions and the routing rule. Each child is created with CREATE TABLE events_2025_01 PARTITION OF events FOR VALUES FROM ('2025-01-01') TO ('2025-02-01'). On insert, PostgreSQL compares created_at against each child's bounds and writes the row to the one matching partition.

The lower bound is inclusive and the upper bound is exclusive. A row stamped exactly 2025-02-01 00:00:00 goes to February, not January. Because of this, consecutive partitions can share a boundary value (January ends at '2025-02-01', and February starts there) without any gap or overlap. You never need to write 23:59:59.999999. PostgreSQL rejects overlapping bounds when you create a partition.

Copy                 INSERT INTO events
                         │
                 ┌───────┴────────┐
                 │ events (parent)│  routes on created_at
                 └───────┬────────┘
      ┌──────────────┬───┴──────────┬──────────────────┐
      ▼              ▼              ▼                  ▼
 events_2025_01  events_2025_02  events_2025_03   events_default
 [01-01, 02-01)  [02-01, 03-01)  [03-01, 04-01)   anything else

Without a matching partition, an insert fails with an error. A DEFAULT partition (CREATE TABLE events_default PARTITION OF events DEFAULT) catches those rows instead of losing writes. Treat it as an alarm, not as storage. When you later create a partition for a range, PostgreSQL must scan the default to confirm that none of its rows belong in the new range. If any do, the command fails. On a large default partition, that scan is slow and holds locks the whole time.

Monitor it with SELECT count(*) FROM events_default; and alert on any non-zero result.

Copy-- Parent: holds column definitions and the routing rule, but no rows.
-- A primary key on a partitioned table must include the partition key.
CREATE TABLE events (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    created_at  timestamptz NOT NULL,
    payload     jsonb,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Monthly children. FROM is inclusive and TO is exclusive, so a shared
-- boundary leaves no gap and no overlap. Literals are read in the
-- session TimeZone, so pin it (or write explicit offsets).
SET TimeZone = 'UTC';
CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE events_2025_03 PARTITION OF events
    FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

-- Safety net for rows that match no child. It should stay empty.
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- The boundary row lands in February, not January.
INSERT INTO events (created_at) VALUES ('2025-02-01 00:00:00');
SELECT tableoid::regclass AS partition, created_at FROM events;

-- Monitoring: alert if this returns any rows. The time range shows which
-- partitions are missing. Rows here must be moved out before a
-- partition covering their range can be created.
SELECT count(*)        AS stray_rows,
       min(created_at) AS oldest,
       max(created_at) AS newest
FROM events_default;

Creates a range-partitioned events table with monthly children and a DEFAULT partition, shows that a row on the shared boundary is routed to the later month, and queries the default partition for stray rows to alert on.

Choosing partition granularity

Pick the interval from two numbers you already know: the retention unit and the typical query window. If you drop data by the day, or most queries ask for the last 24 hours, daily partitions let a single DROP TABLE enforce retention and let the planner touch one or two children per query. If dashboards span a quarter and retention is counted in months, monthly partitions are usually enough.

Both directions fail in different ways. Too many small partitions add planning work, because the planner considers every child before pruning removes it. Each partition also adds catalog rows for the table, its indexes and its constraints, which bloats the system catalogs and slows DDL. Too few large partitions weaken pruning, because a one-hour query still scans an index covering a whole month. They also make retention coarse: you keep up to a month of expired data until the whole partition ages out.

Illustrative estimate. This is not a measurement. Assumed inputs: 500 rows/s (43.2M rows/day), 150 bytes per row on disk, 365-day retention. Sizes cover the heap only and exclude indexes.

Get this right up front. PostgreSQL cannot re-split existing partitions in place. Changing the interval later means building new partitions and copying the data into them, usually while the table is still receiving writes.

Keys, constraints and indexes on partitioned tables

PostgreSQL enforces uniqueness on a partitioned table by building a unique index inside each partition. Each partition only knows about its own rows, so a constraint can only be guaranteed across the whole table if every partition key column is part of it. A typical PRIMARY KEY (id) therefore becomes PRIMARY KEY (id, created_at).

This change has consequences:

• No global uniqueness on id alone. Two rows with the same id and different created_at values are now legal. A sequence or identity column will not produce duplicates on its own, but nothing in the database stops a manual insert or a buggy backfill from creating one.
• Foreign keys need the full key. A table that references this one must store and reference both id and created_at. This widens the child table and its indexes.
• Application assumptions need review. Look for lookups by id alone, upserts using ON CONFLICT (id), and caches keyed on id. A lookup without created_at also cannot be pruned, so it scans every partition.

Adding indexes without long locks

An index created on the parent propagates to every existing partition and to every partition created later. A plain CREATE INDEX on the parent locks the table against writes for the whole build, and CONCURRENTLY is not supported on the parent.

To avoid that lock, build the index in pieces:

1. Run CREATE INDEX ... ON ONLY against the parent. This creates an invalid index on the parent and builds nothing on the partitions.
2. Run CREATE INDEX CONCURRENTLY on each partition, one at a time.
3. Run ALTER INDEX parent_idx ATTACH PARTITION partition_idx for each partition index.

Once every partition's index is attached, PostgreSQL marks the parent index valid.

Copy-- The primary key must include the partition key column
CREATE TABLE events (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    created_at  timestamptz NOT NULL,
    account_id  bigint NOT NULL,
    payload     jsonb,
    PRIMARY KEY (id, created_at)  -- PRIMARY KEY (id) alone is rejected
) PARTITION BY RANGE (created_at);

-- Referencing tables must carry the full composite key
-- FOREIGN KEY (event_id, event_created_at) REFERENCES events (id, created_at)

-- Step 1: create the index on the parent only (invalid, nothing built yet)
CREATE INDEX events_account_id_idx ON ONLY events (account_id);

-- Step 2: build each partition's index without blocking writes.
-- CONCURRENTLY cannot run inside a transaction block.
CREATE INDEX CONCURRENTLY events_p20260901_account_id_idx
    ON events_p20260901 (account_id);
CREATE INDEX CONCURRENTLY events_p20261001_account_id_idx
    ON events_p20261001 (account_id);

-- Step 3: attach each partition index to the parent index
ALTER INDEX events_account_id_idx
    ATTACH PARTITION events_p20260901_account_id_idx;
ALTER INDEX events_account_id_idx
    ATTACH PARTITION events_p20261001_account_id_idx;

-- After the last attach, the parent index becomes valid
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index
WHERE indexrelid = 'events_account_id_idx'::regclass;

Defines a partitioned table whose primary key includes created_at, then adds an index without a long write lock by creating it ON ONLY the parent, building it CONCURRENTLY on each partition, and attaching each partition index until the parent index is valid.

Verifying partition pruning with EXPLAIN

Run EXPLAIN on a query whose WHERE clause bounds created_at with literal timestamps, such as created_at >= '2024-03-01' AND created_at < '2024-03-08'. With daily partitions, the plan should list only the seven matching child tables under the Append node. If you see every partition listed, the planner could not compare your predicate against the partition bounds.

Pruning only works when the predicate compares the bare partition key to a value of a compatible type. These patterns defeat it:

• Wrapping the key in a function. date_trunc('day', created_at) = '2024-03-01' hides the column from the planner. It cannot map an arbitrary expression back onto partition bounds, so it scans every partition.
• Mismatched types. If a cast ends up applied to created_at, for example created_at::date = '2024-03-01' or a join against a column of another type, the result is the same as wrapping it in a function. Put the cast on the constant instead.
• Filtering only on id. The partition key is absent, so every partition is scanned.

Pruning happens at two different points:

• Plan-time pruning happens when the bounds are constants. You can recognise it because fewer partitions appear in the plan.
• Execution-time pruning happens when the values are unknown at plan time. This covers parameters in a prepared statement's generic plan, stable functions such as now(), and values from a subquery or join. The plan keeps all partitions, and EXPLAIN ANALYZE reports Subplans Removed: N under the Append.

Planning cost scales with partition count even when pruning works, so keep the count bounded.

Copy-- Assumes events is range-partitioned by created_at (timestamptz), one partition per day.

-- 1. Plan-time pruning: literal bounds. Only the 7 March 1-7 partitions appear under Append.
EXPLAIN SELECT count(*) FROM events
WHERE created_at >= '2024-03-01' AND created_at < '2024-03-08';

-- 2. Defeats pruning: the key is wrapped in a function, so every partition is listed.
EXPLAIN SELECT count(*) FROM events
WHERE date_trunc('day', created_at) = '2024-03-01';

-- 3. Defeats pruning: the cast is applied to the key.
EXPLAIN SELECT count(*) FROM events WHERE created_at::date = '2024-03-01';

-- Fix: keep the key bare and express the day as a half-open range.
EXPLAIN SELECT count(*) FROM events
WHERE created_at >= '2024-03-01'::timestamptz
  AND created_at <  '2024-03-01'::timestamptz + interval '1 day';

-- 4. No partition key in the filter: every partition is scanned.
EXPLAIN SELECT * FROM events WHERE id = 42;

-- 5. Execution-time pruning: now() is stable, so it is not folded at plan time.
--    Look for "Subplans Removed: N" under Append.
EXPLAIN (ANALYZE) SELECT count(*) FROM events
WHERE created_at >= now() - interval '1 day';

-- 6. Execution-time pruning with a generic plan for a prepared statement.
PREPARE recent(timestamptz, timestamptz) AS
  SELECT count(*) FROM events WHERE created_at >= $1 AND created_at < $2;
SET plan_cache_mode = force_generic_plan;  -- force the generic plan for this demo
EXPLAIN (ANALYZE) EXECUTE recent('2024-03-01', '2024-03-08');
-- Expect Append to report Subplans Removed instead of listing fewer partitions.
RESET plan_cache_mode;
DEALLOCATE recent;

Shows EXPLAIN queries that prune partitions at plan time, queries that defeat pruning with a function, a cast on the key or an id-only filter, and execution-time pruning reported as Subplans Removed for now() and generic prepared plans.

Automating the lifecycle with pg_partman

Partition sets also need ongoing upkeep. Future partitions must exist before rows arrive, and old ones must be dropped on schedule. Hand-written cron scripts tend to handle the first part and forget the second. pg_partman turns both into configuration.

The workflow has four steps:

1. Install the extension in its own schema.
2. Register the parent table with its control column, a time interval such as one day, and a premake count. The premake count is the number of future partitions to keep ready.
3. Schedule maintenance. You can use the bundled background worker, or call the maintenance function from cron or another external scheduler. Each run creates partitions to restore the premake buffer and applies retention.
4. Set retention in pg_partman's configuration table. Partitions older than the retention window are either dropped or detached and kept for archiving.

The setup functions and their parameters have changed across major releases, so follow the documentation for the version you have installed rather than copying a signature from elsewhere.

Operational risks:

• Availability. Managed services may not offer pg_partman, or may not offer the version you want.
• Upgrades. The extension has its own upgrade path, separate from PostgreSQL upgrades.
• Stalled maintenance. If maintenance stops, inserts beyond the last premade partition either land in the default partition or fail outright. Rows in the default partition also block creating a regular partition for that range until you move them out. Alert on both maintenance job health and the default partition's row count.

Copy-- 1. Install the extension in its own schema.
CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

-- Parent table, range-partitioned on the time column.
CREATE TABLE public.events (
    id         bigint      NOT NULL,
    created_at timestamptz NOT NULL,
    payload    jsonb
) PARTITION BY RANGE (created_at);

-- 2. Register the parent: daily partitions, keep 7 future ones ready.
-- This is the pg_partman 5.x form. Older releases use different
-- parameters, so check the docs for your installed version.
SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control      := 'created_at',
    p_interval     := '1 day',
    p_premake      := 7
);

-- 4. Retention: drop partitions older than 30 days.
-- Set retention_keep_table = true to detach and keep them for archiving.
UPDATE partman.part_config
SET retention = '30 days',
    retention_keep_table = false
WHERE parent_table = 'public.events';

-- 3. Maintenance: call this from cron or another scheduler, or use the
-- background worker (pg_partman_bgw in shared_preload_libraries).
CALL partman.run_maintenance_proc();

-- Alert if this is non-zero: rows here mean maintenance fell behind.
-- Adjust the name to the default partition your version created.
SELECT count(*) AS default_rows FROM public.events_default;

Registering a daily-partitioned table with pg_partman, setting 30-day retention, running maintenance, and checking the default partition's row count. The create_parent call uses 5.x parameters, so verify them against the docs for your installed version.

Retention and migrating an existing table

Dropping a whole partition replaces the mass DELETE and the vacuum pass that has to reclaim the dead tuples it leaves behind. Run ALTER TABLE ... DETACH PARTITION, then either DROP TABLE or first dump the detached table to cheaper storage. Removing a file costs almost nothing and leaves no bloat in the remaining data. A plain detach takes an ACCESS EXCLUSIVE lock on the parent, which blocks every query against it. DETACH PARTITION ... CONCURRENTLY takes a weaker lock and runs as two transactions, so it cannot run inside a transaction block. It is also rejected when the parent has a default partition. If it is interrupted, finish the work with FINALIZE.

Migrating an existing table

Option one: build and swap. Create the partitioned table, then backfill it in key-ordered batches so each transaction stays short. Rows that arrive during the backfill need handling. You can dual-write from the application or a trigger, or you can copy the final delta during the window. Then rename both tables in one short transaction.

Option two: attach the old table as history. Add a CHECK constraint matching the intended bounds as NOT VALID, then run VALIDATE CONSTRAINT. Validation scans the table without blocking writes. Once the constraint is valid, ATTACH PARTITION can skip its own full scan.

Parent-level DDL locks every partition. Adding a column takes ACCESS EXCLUSIVE on all of them, and CREATE INDEX on the parent cannot run concurrently. To avoid that, build the index on each partition concurrently and attach each one to an index created with ON ONLY.

Primary keys must include the partition column. The application therefore has to supply the timestamp in lookups, foreign keys and ON CONFLICT targets.

Impact on cost and engineering time

Most of the cost is paid up front. The live migration is usually the riskiest and most expensive step. It has to move or re-attach large volumes of data while writes continue, and it needs a rehearsed rollback path. Composite primary keys spread outward: every foreign key that referenced id must now carry the timestamp as well. Code that looks up rows by id alone either changes or accepts slower queries. Without the partition key the planner cannot prune, so it probes an index on every partition. pg_partman also adds a standing duty. Its maintenance run is another scheduled job, and it needs alerting when it fails or falls behind.

The savings recur for the life of the table. Retention becomes a detach and drop of whole partitions, which is a metadata operation. You no longer run a long DELETE and then wait for vacuum to reclaim the dead tuples. Reindexing and vacuum are scoped to individual partitions, so each run touches a smaller table, and older partitions that no longer receive writes rarely need attention. Creating partitions ahead of time also removes a familiar on-call incident: inserts failing, or piling into a default partition, because nobody created the partition for the new period.

The trade pays off when retention and maintenance work recur often enough to outweigh a single, carefully planned migration.

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 range partitioning, 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.