PostgreSQL in Practice #7 Partitioning: Splitting Giant Tables by Time

Infrastructure PostgreSQL Database
4 min read

Tables that grow forever with time — events, logs, orders — eventually reach hundreds of millions of rows. Even if reads hold up thanks to indexes (including practice #4’s BRIN), two things keep getting heavier: deleting old data, and VACUUM. The standard prescription for both is partitioning — physically splitting one logical table into pieces.

What partitioning solves #

  • Deleting old data becomes a single DROP. This is more than half the practical value. Doing “delete events older than a year” with DELETE creates tens of millions of dead tuples and leaves VACUUM to mop up; with monthly partitions, DROP TABLE events_2025_08; finishes instantly, with zero dead tuples.
  • Partition pruning: when the query condition includes the partition key, only the relevant partitions are scanned. In a table holding 12 months, a this-month query reads 1/12.
  • Smaller management units: VACUUM, ANALYZE, and REINDEX run per partition, so each pass is light and parallelizable.

Be equally clear about what it doesn’t solve. Partitioning is not a general performance cure. A query without a partition-key condition scans every partition — extra overhead, if anything — and single-row lookups are already fast through an index; splitting doesn’t speed them up. The purpose is “lifecycle management of giant tables,” not “tuning slow queries.”

Declarative partitioning: monthly RANGE #

Monthly RANGE partitioning 
CREATE TABLE events (
    id         bigint GENERATED ALWAYS AS IDENTITY,
    user_id    bigint NOT NULL,
    payload    jsonb NOT NULL DEFAULT '{}',
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id, created_at)          -- the partition key must be part of the PK
) PARTITION BY RANGE (created_at);

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

The application just inserts into and queries events; PostgreSQL does the routing. The syntactic snag is the PK: PK and UNIQUE constraints must include the partition key (each partition is an independent table, so global uniqueness can’t be checked). Hence the composite (id, created_at) PK. The IDENTITY sequence effectively guarantees global uniqueness of id itself, but at the constraint level it loosens — the representative trade-off of partitioning. LIST (per region or tenant) and HASH (even spreading) exist too, but time-based RANGE is most of real-world usage.

The conditions for pruning #

Pruning is not free — it works only when the query condition directly narrows the partition key.

Pruning conditions compared 
-- pruned: a direct created_at condition
SELECT * FROM events WHERE created_at >= '2026-08-01' AND user_id = 42;

-- not pruned: no key condition → scans every partition
SELECT * FROM events WHERE user_id = 42;

In the same family as basics chapter 4’s “transform the column and you lose the index,” a transformed condition like date_trunc('month', created_at) = ... also defeats pruning. The check is, as always, EXPLAIN — count the partitions appearing in the plan. This property means adopting partitioning comes bundled with a query-pattern review: a table whose main queries carry no time condition is not a candidate for time partitioning.

Operations: partitions must keep being created #

Monthly partitioning’s chore is “who creates next month’s partition.” Fail to create it, and next month’s first INSERT fails — there’s no partition to receive it. (A DEFAULT partition avoids the error, but moving the data that piled up there later costs more.) Manual operations get forgotten, without exception, so the standard is the pg_partman extension: declare “pre-create this many future partitions, detach ones older than this many months,” and it runs on schedule. Managed services mostly support it too.

The adoption criteria, condensed: a table that grows without bound over time, has a retention period, and whose main queries carry a time condition is partitioning’s home. The practical timing is not doing it preemptively at tens of millions of rows, but adopting it before a table showing those three traits reaches hundreds of millions.

Summary #

  • Partitioning’s biggest value is deletion. The DELETE-plus-VACUUM ordeal becomes a single DROP TABLE.
  • Pruning works only with conditions that directly narrow the partition key. Key-less queries scan every partition instead.
  • The PK must include the partition key. The loosened global-uniqueness constraint is the representative trade-off.
  • Partition creation must be automated. Declare “pre-create and expire” with pg_partman.
  • The adoption test is the trio: unbounded growth + retention period + time-conditioned queries. Next chapter: replication and high availability.
X