When Your Table Outgrows Itself: PostgreSQL Declarative Partitioning in Practice

Your events table hit 400 million rows, and the nightly job that deletes data older than 90 days now takes an hour and generates enough WAL to make your replica lag. Queries that filter on created_at still scan far more than they should. This is the classic case for PostgreSQL's declarative partitioning — and the useful takeaway is this: partition by the column you already filter and delete on, and both queries and maintenance get dramatically cheaper. But the win depends entirely on picking the right key, so it's worth understanding how the mechanism actually works before you migrate anything.

What declarative partitioning actually does

Since PostgreSQL 10, you can declare a table as partitioned by a key — by RANGE, LIST, or HASH — and attach child tables that each hold one slice of the data. The parent table stores nothing itself; it's a routing layer. When you insert a row, PostgreSQL sends it to the matching child. When you query, the planner looks at your WHERE clause and skips partitions that can't contain matching rows — a behavior called partition pruning.

Pruning is where the performance comes from. A query like WHERE created_at >= '2026-10-01' against a monthly-partitioned table lets the planner ignore every partition except October's. Instead of scanning a 400-million-row index, it scans one month. The catch: pruning happens at plan time, so the filter needs to be a literal constant or a stable expression the planner can evaluate. Wrapping the key in a function — WHERE date_trunc('month', created_at) = ... — can defeat pruning entirely, because the planner can't reason about the result.

A worked example: monthly event partitions

Here's a minimal setup you can run in any scratch database (PostgreSQL 12+ assumed; no special extensions needed):

CREATE TABLE events (
    id         bigint GENERATED ALWAYS AS IDENTITY,
    created_at timestamptz NOT NULL,
    payload    jsonb NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_09 PARTITION OF events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

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

Note the primary key: on a partitioned table, every unique constraint must include the partition key. That's a real design constraint — you can no longer enforce uniqueness on id alone across the whole table.

Insert a handful of rows into each month, then verify pruning works:

EXPLAIN (COSTS OFF)
SELECT count(*) FROM events
WHERE created_at >= '2026-10-01' AND created_at < '2026-10-15';

In the plan, you should see only events_2026_10 scanned — the September partition should not appear at all. If both partitions show up, pruning isn't happening and you should check whether the filter expression is opaque to the planner. Run ANALYZE events; first so the planner has statistics, and confirm enable_partition_pruning is on (it is by default).

Why maintenance is the real killer feature

Query speedups are nice, but the operational wins are often bigger. The nightly delete job from the opening scenario disappears entirely — instead of DELETE FROM events WHERE created_at < now() - interval '90 days', you drop the old partition:

DROP TABLE events_2026_07;

This is a metadata operation plus a file removal — no row-by-row deletion, no table bloat, no avalanche of WAL. It takes milliseconds regardless of how many rows the partition held. Similarly, VACUUM and ANALYZE can target a single partition (VACUUM events_2026_10;), so maintenance on recent hot data doesn't touch the rest of the table. Each partition also gets its own indexes — a GIN index on payload, for example, exists per child and is used independently. There is no single index spanning all partitions, which is worth remembering when you size things.

The trade-offs you sign up for

Partitioning is not free, and the failure modes are worth stating plainly:

• The partition key is nearly permanent. Changing it later means creating a new partitioned table and migrating data. Choose the column your queries and retention policy already revolve around — usually a timestamp or tenant ID.
• Skew hurts. A list-partitioned table keyed on a status column where 95% of rows are 'active' gives you one giant partition and several tiny ones — you've added complexity without the benefit.
• Too many partitions slows planning. Hundreds are fine; thousands of partitions can measurably increase query planning time, especially on older versions. PostgreSQL 13+ improved this substantially, but keep partition counts in the low hundreds where possible.
• Constraint and replication quirks. Unique constraints must include the partition key (as shown above), and logical replication publishes each child table separately, which complicates subscriber setup.

Before you commit

Don't partition because a table is big; partition because you have a query or maintenance pattern that maps to a key. The fastest way to validate the idea is the exercise above: build a small partitioned copy of your problem table, load a representative slice of data, and run EXPLAIN on your three most important queries. Confirm that pruning eliminates the partitions you expect, and test your retention workflow with DROP TABLE instead of DELETE. If the plans don't prune, no amount of partitioning machinery will help — fix the query shape first. And if you go ahead in production, automate partition creation (a small scheduled job or a tool like pg_partman) so nobody discovers a missing partition when the month rolls over.