Partitioning

An index speeds up finding rows inside a table. Partitioning changes what "a table" even means physically: instead of one heap file, a partitioned table is a thin logical wrapper over several real child tables, each holding a distinct slice of the rows. Postgres decides which slice a row belongs in at insert time and which slices a query even needs to look at, at plan time.

INSERT INTO events (event_time = 2026-02-10)

INSERT INTO eventsparent — holds no rows itself

Router checks event_timeagainst each partition's bounds

events_2026_02the only partition touched

Declaring a partitioned table

Postgres supports three partitioning strategies, chosen with PARTITION BY:

-- RANGE: contiguous ranges of a value — the default choice for time-series data
CREATE TABLE events (
  id         bigserial,
  event_time timestamptz NOT NULL,
  payload    jsonb
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- LIST: an explicit set of discrete values per partition
CREATE TABLE orders (
  id      bigserial,
  region  text NOT NULL,
  total   numeric
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('us', 'ca');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('de', 'fr', 'es');

-- HASH: no natural range or list, just spread rows evenly across N buckets
CREATE TABLE sessions (
  id      bigserial,
  user_id int NOT NULL
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_0 PARTITION OF sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_1 PARTITION OF sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- ... REMAINDER 2, REMAINDER 3

The parent table (events, orders, sessions) holds no rows of its own — every row physically lives in exactly one child partition, and Postgres routes INSERTs there automatically based on the partition key.

Partition pruning

The whole payoff shows up in EXPLAIN: when a query's WHERE clause can be matched against the partition key, the planner eliminates non-matching partitions before scanning anything in them.

EXPLAIN SELECT * FROM events
WHERE event_time >= '2026-02-01' AND event_time < '2026-02-15';

--                          QUERY PLAN
-- ------------------------------------------------------------
--  Append
--    ->  Seq Scan on events_2026_02
--          Filter: (event_time >= ... AND event_time < ...)
-- (only events_2026_02 appears — events_2026_01 was pruned)

events_2026_01 never appears in the plan at all — not "scanned and found empty," genuinely never opened. For a query that only ever touches recent data, this turns a scan over years of history into a scan over one month, without an index in sight.

Bulk operations become metadata operations

Because each partition is a real, independent table, whole ranges of data can be dropped or moved as a single fast catalog change instead of a row-by-row DELETE:

-- instant: unlinks the child table, no row-by-row work
DROP TABLE events_2026_01;

-- detach first if you want to keep the data around, just outside the partition set
ALTER TABLE events DETACH PARTITION events_2026_01;

This is the other half of why time-series workloads reach for partitioning: "keep 13 months, drop anything older" becomes a monthly DROP TABLE against a single small partition instead of a DELETE that has to find and remove millions of scattered rows — and unlike DELETE, it doesn't leave dead tuples for VACUUM to clean up afterward.

Common mistake

Partitioning is not free, and more partitions is not automatically better. Every query against a partitioned table has to consider every partition during planning, even ones that get pruned — with hundreds or thousands of tiny partitions, planning time itself becomes the bottleneck, sometimes exceeding the actual execution time. A good partition key produces a modest number of meaningfully large partitions (monthly, not hourly; by region, not by user ID) — not the finest granularity you can think of. If a table is only tens of thousands of rows, it almost certainly doesn't need partitioning at all.

Production note

A global unique constraint (a PRIMARY KEY that's unique across all partitions, not just within one) requires the partition key to be part of that key. Postgres can enforce uniqueness per-partition trivially, but enforcing it cluster-wide would mean checking every other partition on every insert — so it simply requires the constraint to include the partition column instead.

Next: the mechanism that makes every one of these writes durable in the first place, in Write-Ahead Log.

Declaring a partitioned table

Partition pruning

Bulk operations become metadata operations

On this page