Skip to content
System Design Lab
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.

Intermediate 4 min read· Updated 2026-11-18

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.

StrategyRows go byGood for
Rangecreated_at in [2026-11-01, 2026-12-01)Time series, logs, events, orders — anything queried and expired by date.
Listregion IN ('EU', 'UK')A few known groups queried separately (tenants, countries).
Hashhash(customer_id) % 8Spreading 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.

Loading simulation…

Range partitions in PostgreSQL

Daily partitions, pruning and instant retention
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

BenefitsCosts
QueriesPruning skips irrelevant data; per-partition indexes stay small.Queries without the partition key search every partition — sometimes slower than one table.
MaintenanceDrop, archive or vacuum one partition at a time.Partitions must be created ahead of time; too many partitions slow planning.
ConstraintsTransparent 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 EXPLAIN to confirm pruning actually happens.
  • Converting a live table needs a migration plan (new partitioned table + backfill + switch).

Further reading

Related topics