30 / 32 · 5 min

Splitting one table across many

Replicas solve reads. They do not solve writes, because every write still goes to one primary, and they do not solve a table too large for one machine. Splitting the data does.

Two different things with similar names

Partitioning splits a table within one database. The database knows about every partition and routes queries itself.

Sharding splits the data across separate databases that do not know about each other. Something above them has to decide where each row lives.

Partitioning is a tuning decision. Sharding is an architecture.

Partitioning

sql

CREATE TABLE orders (
    id BIGSERIAL, order_date DATE NOT NULL, amount NUMERIC
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2026_q1 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

A query with WHERE order_date >= '2026-02-01' touches one partition and ignores the rest, which is called partition pruning. And dropping last year's data becomes DROP TABLE on one partition, instantly, rather than a DELETE of a hundred million rows.

A query without the partition key touches every partition and is slower than it was before.

Choosing the key

This is the decision the whole thing rests on, and it is expensive to change.

Key	Fast	Slow
Date range	recent data, time queries	anything across all time
Customer	one customer's data	reports across customers
Hash of id	even spread, single row lookups	any range query

Hot partitions are the trap: partitioning by date puts every new write into the newest partition, so writes are not spread at all. Partitioning by customer when one customer is a thousand times larger puts that load on one machine.

What sharding costs

Once data is in separate databases:

a join across shards is not available, and must be done in application code
a transaction across shards needs distributed coordination, or is given up
ORDER BY with LIMIT must query every shard and merge
a unique constraint across shards cannot be enforced by any single database

Which is why the honest advice is to exhaust the alternatives first: indexes, a bigger machine, read replicas, caching, archiving old rows. Teams that shard early usually discover their real problem was a missing index, and now they have both problems.