Table Partitioning in PostgreSQL

Partitioning splits one large logical table into smaller physical pieces ("partitions"), while every query still targets it as if it were a single table. It's how huge tables (hundreds of millions of rows) stay manageable and fast.

Range Partitioning — By a Value Range

The most common case: partitioning a huge log/events table by date.

CREATE TABLE events (
  id BIGSERIAL,
  event_type TEXT,
  created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

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

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

Inserts automatically route to the correct partition based on created_at — your application just inserts into events and never has to know which partition a row landed in.

INSERT INTO events (event_type, created_at) VALUES ('login', '2024-01-15');
-- automatically stored in events_2024_01

List Partitioning — By an Explicit Set of Values

CREATE TABLE orders (
  id BIGSERIAL,
  region TEXT,
  total NUMERIC
) PARTITION BY LIST (region);

CREATE TABLE orders_asia PARTITION OF orders FOR VALUES IN ('India', 'China', 'Japan');
CREATE TABLE orders_europe PARTITION OF orders FOR VALUES IN ('Germany', 'France');

Hash Partitioning — Even Distribution With No Natural Grouping

When there's no meaningful range or list to split on, distribute rows evenly by hashing a column.

CREATE TABLE users (
  id BIGSERIAL,
  email TEXT
) PARTITION BY HASH (id);

CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_p2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_p3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Partition Pruning — Why This Speeds Things Up

When a query's WHERE clause constrains the partition key, PostgreSQL skips reading partitions that couldn't possibly contain a match:

EXPLAIN SELECT * FROM events WHERE created_at >= '2024-02-01' AND created_at < '2024-02-15';
-- only scans events_2024_02 — every other monthly partition is skipped entirely

Dropping Old Data Instantly

The biggest operational win: deleting a whole month of old data becomes an instant metadata operation instead of a slow row-by-row DELETE.

DROP TABLE events_2023_01;
-- instantly removes an entire month's data, versus:
-- DELETE FROM events WHERE created_at >= '2023-01-01' AND created_at < '2023-02-01';  (slow on huge tables)

Common Errors & Troubleshooting

• ERROR: no partition of relation "events" found for row — you tried to insert a row whose partition key value doesn't fall into any defined partition range; add a partition covering that range (or a DEFAULT partition to catch anything unmatched).
• Query not pruning partitions despite a WHERE on the partition key — check the WHERE clause uses the partition key directly, without wrapping it in a function (like DATE(created_at)) — that can prevent the planner from pruning, the same trap covered in the Query Optimization lesson.
• Unique constraints failing unexpectedly on a partitioned table — a PRIMARY KEY/UNIQUE constraint on a partitioned table must include the partition key as part of the key — PostgreSQL can't otherwise guarantee uniqueness across separate partitions.

Key Takeaways

• Partitioning is invisible to most queries — you insert/select against the parent table, and PostgreSQL routes rows automatically.
• Partition pruning means a well-targeted query only scans the relevant partitions, not the entire table.
• Dropping an entire partition is near-instant — the standard way to purge old time-series data at scale.