Partitioning: Range, List, Hash and Time

In one line

Partitioning splits one large table into smaller physical tables on one server by a partition key. Queries that filter on the key touch only relevant partitions (pruning), old data is dropped by detaching a partition instead of deleting rows, and maintenance runs per partition. It helps manageability far more than raw speed, and a bad key makes everything slower.

0/5 · 0%

Think of it like this

Filing receipts by month in separate folders. Finding March's receipts means opening one folder; throwing away receipts from 2019 means binning whole folders; but "all receipts from shop X" still means opening every folder.

Key ideas

01
Range: by value ranges, usually time (created_at monthly). List: by discrete values (region IN ('in','us')). Hash: by hash of a key modulo N, for evenly spreading data without a natural range. Sub-partitioning combines them.
02
Partition pruning happens at planning time (constants) and execution time (parameters). It only works when queries filter on the partition key, so pick the key from the dominant access pattern.
03
Retention: ALTER TABLE events DETACH PARTITION events_2024_01 CONCURRENTLY; DROP TABLE events_2024_01; is instant and generates no dead tuples, unlike DELETE of billions of rows. Archive the detached table to cheap storage first if needed.
04
Constraints: primary keys and unique indexes must include the partition key. Too many partitions (thousands) slow planning and use memory; aim for tens to low hundreds, with partitions in the ~10s–100s of GB.
05
Automate creation of future partitions (pg_partman or a scheduled job) and a default partition to catch unexpected values; alert if rows land in the default partition.

Code & diagrams

partitioning.sqlsql

CREATE TABLE events (
  id         bigint NOT NULL,
  tenant_id  bigint NOT NULL,
  created_at timestamptz NOT NULL,
  payload    jsonb,
  PRIMARY KEY (id, created_at)                       -- must include the partition key
) 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');
CREATE TABLE events_default PARTITION OF events DEFAULT;

CREATE INDEX ON events (tenant_id, created_at);      -- created on every partition

EXPLAIN SELECT count(*) FROM events
WHERE created_at >= '2026-09-10' AND created_at < '2026-09-11';
-- Aggregate -> Index Only Scan on events_2026_09 ...   (only one partition scanned)

-- retention: instant, no bloat
ALTER TABLE events DETACH PARTITION events_2025_09 CONCURRENTLY;
DROP TABLE events_2025_09;

Interview problem

The problem

Partition an events table of 8 billion rows

An events table has 8B rows (6 TB), 400M new rows/day, 90-day retention enforced by a nightly DELETE that runs for hours and causes bloat. Queries filter by tenant and time range. Design partitioning and the migration.

When it breaks

Queries that don't include the partition key

What you see

WHERE user_id = ? on a time-partitioned table scans every partition's index; with 365 partitions that's 365 index lookups per query, slower than before partitioning.

Fix & prevent

Choose the key from the dominant access pattern; for secondary patterns, add a lookup table or a separate read model.

Explain it without notes

Why does partitioning help data retention so much?

Practice

Create a hash-partitioned sessions table with 8 partitions on user_id and check that a query by user_id prunes to one.

Trade-offs

↔
Partitioning simplifies retention and maintenance, but constrains unique keys and punishes queries without the key.

Done when you can

I can choose a partition strategy and key, automate retention, and migrate an existing table.

Command Palette