What is Postgres table partitioning and when does it actually help?

Declarative partitioning lets you define one logical table whose rows are physically stored in child tables, called partitions. The parent holds no data. Postgres routes each inserted row to the right partition based on a partition key, such as a created_at timestamp. Queries against the parent work as usual, and the planner can skip partitions that cannot contain matching rows. This is called partition pruning.

The benefits come from making each unit of storage smaller and disposable. Retention becomes a metadata operation: dropping or detaching a month of data replaces a huge DELETE that generates WAL, leaves dead tuples and triggers long vacuums. Vacuum, REINDEX and index builds run per partition, so they finish within a maintenance window. Older partitions that no longer change need little vacuuming.

Partitioning changes how data is laid out. It does not change how rows are locked or how much work is needed per row.

Signal Does partitioning help? Why
Most queries filter on a time range Yes Pruning limits the scan to the relevant partitions
Old data is deleted in large batches Yes Dropping a partition is cheap and leaves no bloat
Vacuum or index maintenance never finishes Usually The work is split into smaller per-partition jobs
Queries rarely filter on the key No Every partition is scanned, and planning costs more
Many writers update the same rows No Row-level lock contention is unchanged
Insert throughput on recent data is the bottleneck No All new rows still go to a single partition

Partitioning does not replace proper indexes or well-written queries. A slow query on an unpartitioned table usually stays slow on a partitioned one.

Who should use time-based partitioning?

Time-based partitioning suits teams that own a large table with these traits:

  • Append-mostly, time-ordered data. Examples include events, logs, audit trails, metrics, notifications and order or payment history. Rows are written once, rarely updated, and have a natural creation timestamp.
  • A retention policy. Data older than some age is deleted, archived or moved to cheaper storage on a schedule. Partitioning gives you the biggest gain here.
  • Queries that filter on time. Dashboards, reports and APIs mostly ask for "the last N days" or a specific date range.
  • Maintenance pain. Autovacuum falls behind on the table, index builds or rebuilds don't fit in a maintenance window, or batched purge jobs need supervision.

Teams in a different position should usually wait:

  • The table is small enough that vacuum and index maintenance already finish quickly, and hot data fits in memory.
  • Most access is by primary key with no time component. Every lookup would have to probe every partition.
  • The schema can't absorb the constraint changes. The primary key and any foreign keys pointing at the table must include the partition key, and that affects application code and referencing tables.
  • Nobody will own the ongoing work: creating future partitions, running retention and monitoring the default partition.

The people who benefit most directly are the backend engineers who own the table's schema and queries, and the DBAs or platform engineers who carry the vacuum, bloat and retention work for it.

How does it work?

At a high level, a partitioned table has three moving parts:

  1. Catalog metadata. The parent table records the partitioning method (RANGE, LIST or HASH) and the key column. Each partition records its bounds. The parent has no storage of its own.
  2. Row routing on write. When you INSERT into the parent, the executor reads the row's key, finds the partition whose bounds contain it, and writes the row there. An UPDATE that changes the key moves the row to a different partition, which is executed as a delete plus an insert.
  3. Pruning on read. The planner compares the query's WHERE conditions on the key against each partition's bounds and leaves out partitions that cannot match. If the comparison value is only known at run time, the executor prunes instead.

Each partition is an ordinary table. It has its own heap, indexes, visibility map, statistics and autovacuum schedule, so maintenance on one partition doesn't touch the others. Retention works because removing a partition with DETACH PARTITION or DROP TABLE is mostly a catalog change. Postgres discards whole files instead of deleting rows one at a time. One caveat: autovacuum analyzes the partitions but not the partitioned parent itself. Run ANALYZE on the parent periodically if queries rely on statistics for the table as a whole.

The rest of this article covers range partitioning by time, which is the most common case.

How does range partitioning work in Postgres?

In declarative partitioning, the parent table is a logical shell. You create it with PARTITION BY RANGE (created_at) and it stores no rows itself. Each child partition is created as a partition of the parent with FOR VALUES FROM ('2025-01-01') TO ('2025-02-01'). The lower bound is inclusive and the upper bound is exclusive. A row stamped exactly at midnight on 1 February therefore belongs to the February partition. Adjacent ranges can share a boundary value without overlapping or leaving gaps. Postgres rejects any new partition whose range overlaps an existing one.

                 events  (parent, PARTITION BY RANGE created_at)
                    │
   ┌────────────────┼────────────────┬──────────────────┐
   │                │                │                  │
events_2025_01   events_2025_02   events_2025_03    events_default
[Jan 1, Feb 1)   [Feb 1, Mar 1)   [Mar 1, Apr 1)    (no matching range)

A DEFAULT partition catches any row that matches no range, such as a late-arriving event or a timestamp from a misconfigured client. A row whose key is NULL also goes there. Without a default partition, those inserts fail with an error. Treat the default partition as an alarm and keep it empty. When you attach a new partition, Postgres must scan the default partition to confirm it holds no rows that belong in the new range, and a large default partition makes that step slow.

On INSERT, the executor compares each row's key against the partition bounds and routes the row to the matching child. Application code still writes to the parent. For reads, the planner compares WHERE conditions on created_at with each partition's bounds and skips any child that cannot contain matching rows. This is called partition pruning. When the value is only known at runtime, for example a bound parameter in a prepared statement, pruning can happen during execution instead.

-- Parent table: a logical shell that stores no rows itself.
CREATE TABLE events (
    id         bigint      NOT NULL,
    created_at timestamptz NOT NULL,
    payload    jsonb,
    -- A primary key on a partitioned table must include the partition key.
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Monthly children: FROM is inclusive, TO is exclusive,
-- so adjacent ranges share a boundary without overlapping.
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');

-- Catch-all for rows that match no range. Monitor it and keep it empty.
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- The application writes to the parent; Postgres routes each row.
INSERT INTO events (id, created_at, payload) VALUES
    (1, '2025-01-31 23:59:59+00', '{}'),  -- lands in events_2025_01
    (2, '2025-02-01 00:00:00+00', '{}'),  -- exact boundary: events_2025_02
    (3, '2026-07-04 12:00:00+00', '{}');  -- no matching range: events_default

-- Confirm where each row went.
SELECT tableoid::regclass AS partition, id, created_at
FROM events
ORDER BY id;

-- Pruning: the plan should scan only events_2025_02.
EXPLAIN
SELECT count(*)
FROM events
WHERE created_at >= '2025-02-01' AND created_at < '2025-03-01';

Creates a range-partitioned events table with three monthly partitions and a default partition, shows how rows at and beyond the boundaries are routed, and uses EXPLAIN to check that a date-bounded query is pruned to a single partition.

How do you choose the partition key and interval?

Pick the key from the filter that appears in almost every hot query. For event, log and audit tables, that is usually the creation timestamp. If most queries say WHERE created_at >= now() - interval '7 days', partitioning on created_at lets the planner skip everything older. A key that queries rarely filter on gives you partitions the planner must scan anyway. Also use a column that never changes after insert. Updating the key moves the row between partitions, which costs a delete plus an insert.

Match the interval to how you expire data. If retention is 90 days and you drop data daily, daily partitions let you remove expired rows with a single DROP TABLE or DETACH PARTITION, with no bulk DELETE and no vacuum backlog. If you only purge once a month, monthly partitions are usually enough.

Partition count is the main trade-off. Each partition adds catalog entries, adds planning work for queries the planner cannot prune at plan time, and adds locks for statements that touch many children. Too few partitions cause the opposite problems: each one is so large that pruning skips little and retention drops are coarse.

Illustrative estimate. The table assumes 50 million rows/day at roughly 200 bytes/row on disk, about 10 GB/day, and one year of retention.

Interval Rows per partition Size per partition Partitions for 1 year
Daily 50 M ~10 GB 365
Weekly 350 M ~70 GB 52
Monthly ~1.5 B ~300 GB 12

With these numbers, weekly is a reasonable middle ground. The partition count stays modest, and each partition is still small enough to prune and drop cheaply.

Primary keys, unique constraints and indexes on partitioned tables

Postgres can only enforce a unique constraint on a partitioned table if the constraint includes every partition key column. Each partition enforces uniqueness through its own local index, and no single structure spans all partitions. A primary key on id alone is therefore rejected, and it typically becomes (id, created_at).

That change has three consequences:

  • Foreign keys. Any table that references this one must reference the full composite key. Each child row needs to store created_at as well, which means extra columns and backfills on those tables.
  • Global uniqueness. Uniqueness of id across the whole table is no longer enforced. An identity column or sequence still produces distinct values in practice. However, a manual insert or a bad data import can create duplicate id values in different partitions without any error.
  • Lookups by id alone. ORM calls like find(id) include no partition key, so the planner cannot prune. It probes the index of every partition. Pass created_at through the application, or encode time into the ID so callers can derive it.

Indexing existing large partitions

A plain CREATE INDEX on the parent blocks writes to every partition until the build finishes. CONCURRENTLY cannot be used on the parent. Use this sequence instead:

  1. Run CREATE INDEX ... ON ONLY against the parent. This creates an invalid index on the parent alone and does not build anything on the partitions.
  2. Run CREATE INDEX CONCURRENTLY on each partition, one at a time.
  3. Attach each partition's index with ALTER INDEX parent_idx ATTACH PARTITION child_idx. Once every partition's index is attached, the parent index becomes valid automatically.

Be careful with other DDL on the parent as well. Many ALTER TABLE operations take an ACCESS EXCLUSIVE lock that cascades to every partition, including adding a constraint or changing a column type. Set lock_timeout before running them.

-- Primary key must include the partition key column
CREATE TABLE events (
    id         bigserial,
    created_at timestamptz NOT NULL,
    user_id    bigint NOT NULL,
    payload    jsonb,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_10 PARTITION OF events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE TABLE events_2026_11 PARTITION OF events
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

-- Referencing tables must carry the full composite key
CREATE TABLE event_notes (
    note_id          bigserial PRIMARY KEY,
    event_id         bigint NOT NULL,
    event_created_at timestamptz NOT NULL,
    FOREIGN KEY (event_id, event_created_at)
        REFERENCES events (id, created_at)
);

-- Low-lock indexing. Run each statement separately, outside a transaction block.
-- 1. Create the parent index only. It starts out invalid.
CREATE INDEX events_user_id_idx ON ONLY events (user_id);

-- 2. Build each partition's index without blocking writes
CREATE INDEX CONCURRENTLY events_2026_10_user_id_idx ON events_2026_10 (user_id);
CREATE INDEX CONCURRENTLY events_2026_11_user_id_idx ON events_2026_11 (user_id);

-- 3. Attach each partition's index to the parent index
ALTER INDEX events_user_id_idx ATTACH PARTITION events_2026_10_user_id_idx;
ALTER INDEX events_user_id_idx ATTACH PARTITION events_2026_11_user_id_idx;

-- The parent index becomes valid once every partition's index is attached
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indexrelid = 'events_user_id_idx'::regclass;

A time-partitioned table with a composite (id, created_at) primary key, a foreign key that references both columns, and the ON ONLY / CONCURRENTLY / ATTACH PARTITION sequence for indexing existing partitions without blocking writes.

How do you migrate a live monolith table to a partitioned table?

There are two practical ways to migrate a live table, and they differ in how much data has to move.

Option A: build, backfill, swap. Create the partitioned table alongside the old one. Mirror new writes into it with a trigger or application dual-writes, then backfill history in small primary-key-ordered batches. Finish with a short transaction that renames both tables. This path has three main risks:

  • Long backfills. Copying a multi-terabyte table can take days.
  • Dual-write bugs. A missed UPDATE or DELETE path leaves the two tables silently inconsistent, so compare row counts and checksums per time range before cutover.
  • Replication lag. Heavy batch copies generate a lot of WAL and can push replicas behind. Throttle the batches and watch lag between them.

The rename itself needs an ACCESS EXCLUSIVE lock. Set a short lock_timeout and retry, so the rename doesn't queue behind a long query and block all traffic. To roll back, keep the old table and its trigger in place and rename the tables back.

Option B: attach the old table as history. Add a CHECK constraint on the old table that matches the partition bounds, created NOT VALID, then run VALIDATE CONSTRAINT. Validation scans the table under a lock that still allows reads and writes. Once the constraint is valid, ATTACH PARTITION can trust it and skip its own full scan. No data is copied. The trade-off is that the old table stays one oversized partition until its rows age out. To roll back, DETACH PARTITION and rename. Rows already written to newer partitions must be moved back by hand.

Option A: backfill and swap Option B: attach as partition
Data copied All of it None
Engineering effort High (sync, verification) Low to moderate
Downtime Brief rename lock Brief attach lock
Main risks Lag, consistency drift One oversized legacy partition
Rollback Rename back Detach, then reconcile new rows
-- Option B: adopt the existing table as one historical partition.
-- Assumes created_at is NOT NULL and every row is before 2026-12-01.

-- 1. Add the constraint without scanning (brief lock only).
ALTER TABLE events ADD CONSTRAINT events_history_bound
  CHECK (created_at IS NOT NULL AND created_at < '2026-12-01') NOT VALID;

-- 2. Scan and validate. SHARE UPDATE EXCLUSIVE lets reads and writes continue.
ALTER TABLE events VALIDATE CONSTRAINT events_history_bound;

-- 3. Build a unique index matching the parent's key, so ATTACH reuses it.
CREATE UNIQUE INDEX CONCURRENTLY events_id_created_at_idx
  ON events (id, created_at);

-- 4. Prepare the partitioned parent and the next partition ahead of time.
CREATE TABLE events_new (LIKE events INCLUDING DEFAULTS)
  PARTITION BY RANGE (created_at);
ALTER TABLE events_new ADD PRIMARY KEY (id, created_at);
CREATE TABLE events_2026_12 PARTITION OF events_new
  FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

-- 5. Cutover. Fail fast instead of queueing behind long queries; retry on timeout.
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE events RENAME TO events_history;
ALTER TABLE events_new RENAME TO events;
-- The valid CHECK proves the bound, so no full-table scan happens here.
ALTER TABLE events ATTACH PARTITION events_history
  FOR VALUES FROM (MINVALUE) TO ('2026-12-01');
COMMIT;

-- Rollback (rows written to events_2026_12 must be moved back by hand):
-- ALTER TABLE events DETACH PARTITION events_history;
-- ALTER TABLE events RENAME TO events_new;
-- ALTER TABLE events_history RENAME TO events;

Option B in SQL: validate a NOT VALID CHECK constraint online so ATTACH PARTITION can skip its full scan, then rename and attach in one short transaction under a lock_timeout.

How can you verify partition pruning with EXPLAIN?

Run plain EXPLAIN on a query whose WHERE clause compares the partition key to constants, such as created_at >= '2024-03-01' AND created_at < '2024-04-01' on a timestamptz column. Only the matching partitions should appear under the Append node. Drop the filter and every partition shows up. Each one adds planning work, because the planner opens it and considers its indexes and statistics. With hundreds of partitions, Planning Time in EXPLAIN ANALYZE output can exceed execution time for short queries.

Pruning only works when the planner can match the clause directly against partition bounds. It cannot do that when you wrap the key in an expression, such as date_trunc('day', created_at) = ... or created_at::date = .... Those queries scan everything. Rewrite them as half-open ranges on the bare column.

Types and volatility also matter. A literal of the column's own type can be pruned at plan time. A value that needs a stable conversion, such as a timestamp compared against a timestamptz key, can't be pruned until execution because the result depends on the session time zone. The same applies to stable functions like now(). Prepared statements that use a generic plan also defer pruning, because their parameter values aren't known until run time.

Run-time pruning still avoids the scans, but the plan looks different. All partitions are listed in plain EXPLAIN. EXPLAIN ANALYZE reports Subplans Removed: N, or marks skipped partitions as (never executed). If neither appears and every partition was actually scanned, check the predicate before blaming the scheme. Also confirm that enable_partition_pruning is on.

-- Monthly partitions on a timestamptz key
CREATE TABLE events (id bigint, created_at timestamptz NOT NULL, payload text)
  PARTITION BY RANGE (created_at);
CREATE TABLE events_2024_02 PARTITION OF events
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE events_2024_03 PARTITION OF events
  FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE events_2024_04 PARTITION OF events
  FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

SHOW enable_partition_pruning;  -- should be 'on'

-- 1. Plan-time pruning: constants of the key's type. Only events_2024_03 appears.
EXPLAIN SELECT * FROM events
WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';

-- 2. Expression on the key: no pruning, every partition is listed and scanned.
EXPLAIN SELECT * FROM events WHERE created_at::date = '2024-03-15';

-- 2b. Same intent as a half-open range on the bare column: pruned again.
EXPLAIN SELECT * FROM events
WHERE created_at >= '2024-03-15' AND created_at < '2024-03-16';

-- 3. timestamp vs timestamptz needs a stable cast, so pruning moves to execution.
--    Plain EXPLAIN lists all partitions; ANALYZE shows 'Subplans Removed: N'.
EXPLAIN ANALYZE SELECT * FROM events
WHERE created_at >= '2024-03-01'::timestamp AND created_at < '2024-04-01'::timestamp;

-- 3b. Stable function: also pruned at run time.
EXPLAIN ANALYZE SELECT * FROM events WHERE created_at >= now() - interval '7 days';

-- 4. Generic plan for a prepared statement: parameter values unknown at plan time.
SET plan_cache_mode = force_generic_plan;
PREPARE recent(timestamptz) AS SELECT * FROM events WHERE created_at >= $1;
EXPLAIN ANALYZE EXECUTE recent('2024-04-01');  -- look for 'Subplans Removed'
RESET plan_cache_mode;

EXPLAIN queries contrasting plan-time pruning with constants, no pruning when the key is wrapped in an expression, and run-time pruning from stable casts, now(), and generic prepared-statement plans.

How do you automate partition creation and retention with pg_partman?

Once pruning is confirmed, the remaining risk is operational. Someone has to create next month's partition before the first row arrives, and someone has to remove last year's. pg_partman handles both.

You register a parent table with the partition key, an interval and a premake value. pg_partman then creates that many future partitions ahead of the current one. Premaking matters because a row with no matching partition fails the insert, or lands in the default partition, which later blocks creation of the partition that should have held it. Partitions only get created when the maintenance routine runs, so schedule it. You can use the extension's background worker, a pg_cron job, or an ordinary cron entry that calls the maintenance function through psql. Run it more often than the interval so a missed run does no harm.

Retention settings tell maintenance how much history to keep. Older partitions are then either detached and kept as standalone tables, or dropped. The function names, parameters and config columns have changed between releases, so read the documentation for the version you have installed rather than copying an example.

Removing a partition is a metadata operation. A DELETE of the same rows writes WAL for every tuple, leaves dead tuples that bloat the table and its indexes, and creates vacuum work that competes with production load. Where your server supports it, use DETACH PARTITION CONCURRENTLY to avoid holding a strong lock on the parent. Archive the detached table with pg_dump or by exporting it to object storage, verify the copy, and then drop it.

With this in place, the monthly ticket for "create partitions and purge old data" becomes a scheduled job you can monitor and alert on.

How can partitioning save engineering time?

Most of the time savings come from removing recurring, hands-on work rather than from faster queries.

  • Retention stops being a project. Without partitions, purging old rows means a batched DELETE job that someone has to tune, throttle against replication lag, and watch until it finishes. Afterwards, autovacuum has to clean up the dead tuples, and the table and indexes may stay bloated. With partitions, retention is a scheduled detach or drop that finishes quickly and leaves no bloat behind.
  • Maintenance fits in normal hours. Index builds, REINDEX and manual VACUUM run one partition at a time. A failed or cancelled run only has to be redone for one partition, not for the whole table, and old partitions that no longer change need little attention.
  • Fewer incidents. Vacuum falling behind, transaction ID wraparound pressure on one giant table, and long purge jobs competing with production traffic are common sources of pages. Smaller partitions make each of these less likely and easier to recover from.
  • Faster archiving and restores. A detached partition is a self-contained table. You can dump, verify and drop it as a unit, or load one month back for an investigation without restoring the whole table.
  • Predictable growth. Partition sizes and counts are easy to track and alert on, so capacity planning relies less on guesswork.

Illustrative estimate. Assume a team spends about 4 hours a month supervising a batched purge job and about 8 hours a quarter on bloat cleanup and index rebuilds for one large table. That is roughly 80 hours a year. Also assume that after partitioning with automated maintenance, the recurring work drops to about 1 hour a month of checking alerts and job health, or 12 hours a year. The assumed saving is around 68 hours a year for that table. Weigh it against the one-time cost of the migration and the schema changes to primary and foreign keys, which can take weeks of engineering time. Substitute your own figures: the trade only pays off if the recurring cost you remove is larger than the migration and ownership cost you take on.

Partitioning doesn't save time when queries rarely filter on the key or when there is no retention policy. In those cases you take on the operational overhead without removing any recurring work.

What are the alternatives, and how do you keep a partitioning scheme healthy?

Automation turns partition management into something you can mostly stop thinking about. Before committing to that machinery, though, check whether a cheaper option solves the problem you actually have.

Option Fits when Cost
BRIN index on the timestamp Rows arrive in time order and queries scan time ranges Very small index, but loses selectivity if old rows are updated or data arrives out of order
Batched DELETE plus autovacuum tuning Retention is the main pain and delete volume is modest Dead tuples, heavy WAL, and bloat that plain VACUUM does not return to the OS
Archive to a separate table or storage Old data is rarely read but must be kept A second query path for historical data
Do nothing Hot data fits in memory and queries are well indexed Nothing today, but revisit as the table grows

If you do partition, monitor the scheme so it doesn't quietly decay:

  • Future partitions and job health. Alert when fewer partitions exist ahead of the current date than you premake. Also alert when the maintenance job's last successful run is older than expected. A stalled job shows no symptoms until inserts cross the last boundary.
  • Rows in the DEFAULT partition. This count should be zero. Rows there mean a partition was missing when they arrived. To attach or create a partition for that range, Postgres must scan the default partition under lock, and the operation fails if matching rows are present. Without a default partition, those inserts fail outright.
  • Partition count and per-partition size over time. Steadily growing partitions suggest a shorter interval. A count that keeps climbing points to broken retention and slower planning.

Finally, budget for ongoing ownership. The extension must be upgraded alongside major Postgres upgrades, its configuration lives in the database, and the scheduler must keep running on the primary after a failover.

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 postgres table 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.