Design to Scale
Database Partitioning
Split one huge table into smaller pieces inside the same database, so queries skip the pieces they do not need and old data is dropped in an instant instead of deleted row by row.
What you will learn
- Distinguish range, list and hash partitioning and when each fits.
- Explain partition pruning and why queries must filter on the partition key.
- Replace expensive DELETE-based retention with dropping partitions.
- Tell partitioning (one database) apart from sharding (many databases).
Before you start
Overview
An accountant who keeps every receipt in one giant box spends ages finding last week's. Put each month in its own folder and you only open one folder — and throwing away last year means binning twelve folders, not sorting through the box.
Partitioning splits one logical table into several physical tables (partitions) inside the same database, by a partition key. The application still queries one table. The planner looks at the WHERE clause and skips partitions that cannot match — partition pruning — and maintenance works per partition.
| Strategy | Rows go by | Good for |
|---|---|---|
| Range | created_at in [2026-11-01, 2026-12-01) | Time series, logs, events, orders — anything queried and expired by date. |
| List | region IN ('EU', 'UK') | A few known groups queried separately (tenants, countries). |
| Hash | hash(customer_id) % 8 | Spreading writes and index size evenly when there is no natural range. |
Run it
The events table holds 30 days of data and dashboards need today's. In A every query scans the whole table, and the nightly retention DELETE at t = 12 s overloads the database. B uses daily partitions. C is partitioned too, but half the queries filter by customer instead of date. Press Run retention job now to repeat it.
Range partitions in PostgreSQL
CREATE TABLE events (
id bigint NOT NULL,
customer_id bigint NOT NULL,
created_at timestamptz NOT NULL,
payload jsonb,
PRIMARY KEY (id, created_at) -- the partition key must be part of unique keys
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_11_18 PARTITION OF events
FOR VALUES FROM ('2026-11-18') TO ('2026-11-19');
-- Pruned: only events_2026_11_18 is scanned.
SELECT status, count(*) FROM events WHERE created_at >= now() - interval '1 day' GROUP BY status;
-- Not pruned: no partition key in WHERE → every partition is searched.
SELECT * FROM events WHERE customer_id = 42;
-- Retention: a metadata change instead of deleting millions of rows.
ALTER TABLE events DETACH PARTITION events_2026_10_18;
DROP TABLE events_2026_10_18;Partitioning vs sharding
Partitioning keeps everything on one database server: it makes big tables manageable and queries cheaper, but it does not add CPU, memory or write throughput. Sharding spreads partitions across many servers — more capacity, plus routing, cross-shard queries and resharding to deal with.
Trade-offs
| Benefits | Costs | |
|---|---|---|
| Queries | Pruning skips irrelevant data; per-partition indexes stay small. | Queries without the partition key search every partition — sometimes slower than one table. |
| Maintenance | Drop, archive or vacuum one partition at a time. | Partitions must be created ahead of time; too many partitions slow planning. |
| Constraints | Transparent to most application code. | Unique keys must include the partition key; moving rows between partitions is costly. |
Production considerations
- Pick the key your most important queries filter on and your retention policy uses — usually time.
- Aim for partitions of a size you can comfortably vacuum and index; hundreds, not tens of thousands.
- Check plans with
EXPLAINto confirm pruning actually happens. - Converting a live table needs a migration plan (new partitioned table + backfill + switch).