Partitioning & sharding: splitting the data

Replication copies the whole database onto more machines. That buys you read capacity and failure survival, but it leaves two ceilings standing: every write still goes through the single primary, and every machine still has to store the entire dataset. When the data grows past what one machine can hold, or the write rate past what one primary can absorb, copying doesn't help — you have to split the data so different machines own different pieces.

That's this lesson, and the first job is to un-blur a distinction that's constantly muddled, because the two words are not synonyms.

Partitioning vs sharding: where the boundary is

• Partitioning splits one large table into smaller physical pieces inside a single database server. It's one machine, one Postgres, presenting what looks like one table but is physically several. The goal is manageability and per-query efficiency, not escaping the machine.
• Sharding splits the data across multiple independent database servers, each holding a subset of the rows. It's the same "divide the rows" idea, but the pieces now live on different machines, which is what lets you exceed one machine's storage and write throughput.

The mechanics of how you divide (by range, by hash, by a lookup) are shared between them. The difference that matters is the boundary the split crosses: partitioning stays on one node; sharding crosses machines — and crossing machines is where almost all the difficulty is born. Keep the two straight and the rest of the lesson stays clear.

Partitioning: one table, many physical pieces

Postgres has built-in declarative partitioning. You define one logical parent table and tell Postgres how to divide its rows into physical child partitions:

• Range partitioning — by value ranges, most often time: one partition per month of created_at. Natural for time-series and append-heavy data.
• List partitioning — by an explicit set of values: one partition per region, per tenant tier, per status.
• Hash partitioning — by a hash of a key, to spread rows evenly when there's no natural range or list.

You still query the parent table normally. The payoffs are concrete:

• Partition pruning. When a query filters on the partition key (WHERE created_at >= '2026-07-01'), the planner knows which partitions can't contain matching rows and skips them entirely — it reads one month's partition instead of scanning years. This is the selectivity idea from the planner lesson applied at the table-file level.
• Cheap bulk deletion. Dropping old data becomes DROP TABLE july_2024_partition — an instant metadata operation — instead of DELETE FROM ... WHERE created_at < ..., which would churn through millions of rows, generate mountains of dead tuples, and demand a heavy vacuum. For rolling-window data (logs, events, metrics) this alone justifies partitioning.
• Smaller indexes and localized maintenance. Each partition has its own smaller B-tree and gets vacuumed independently, so index operations and autovacuum work on manageable pieces rather than one monster table.

Crucially, partitioning is still one server. It makes a big table pleasant to live with; it does nothing for the machine's total capacity. It's also frequently all you actually need — a great many "we need to shard!" situations are solved by partitioning a single well-provisioned server, and never crossing the machine boundary at all.

Sharding: crossing the machine boundary

When one server genuinely isn't enough — storage or write throughput — you shard: run N independent databases, each holding a slice of the rows, with a shard key deciding which server any given row lives on. Now writes and storage scale horizontally, because a write to shard 3 doesn't touch shards 1, 2, or 4. The strategy for mapping a key to a shard is the same family of choices as partitioning, but the consequences are sharper because getting to the wrong machine now means a network hop or a query that can't be answered at all:

• Range sharding — assign key ranges to shards (users A–M on shard 1, N–Z on shard 2; or date ranges). Range scans are efficient (a range often lives on one shard), but it's hotspot-prone: sharding by timestamp sends all new writes to whichever shard owns "now," so one shard is on fire while the rest idle. The exact anti-pattern.
• Hash sharding — shard = hash(key) mod N. Spreads rows evenly, killing hotspots — but destroys range locality (a range query must hit every shard), and plain mod N is a resharding disaster: change N and almost every key remaps to a different shard, forcing a near-total data reshuffle. This is precisely the problem consistent hashing from the load-balancing lesson was invented to solve — adding a shard should remap ~1/N of the keys, not all of them.
• Directory (lookup) sharding — keep an explicit map from key (or key-range) to shard. Maximally flexible: rebalance by editing the map, no formula constrains you. The cost is that the directory is another component to run, consult on every request, and keep from becoming a bottleneck or single point of failure.

Insert some rows and see the difference. With Range sharding, sequential keys pile onto one shard — a write hotspot — while the others idle; switch to Hash and the same inserts spread evenly. Then compare a query with the shard key (one shard) against one without (scatter-gather across all).

The price of sharding: what stops being free

Here's the part that makes sharding a last resort rather than a default. On a single node, three things are free that become hard-to-impossible across shards:

• Cross-shard queries. A query that filters by the shard key goes straight to the one shard that owns the data — fast. But a query that doesn't (or needs data from many keys) has to scatter-gather: ask every shard, then merge the results in the application. That's slow, it scales with shard count, and it turns a simple query into a distributed one. Worse, JOINs across shards are effectively unsupported — the data the join needs lives on different machines, so you either denormalize, or fetch-and-join in application code, or design so joined data shares a shard.
• Cross-shard transactions. A single-shard transaction is a normal local ACID transaction. A transaction spanning shards needs a distributed commit protocol (two-phase commit) to stay atomic across machines — slow, operationally heavy, and a new failure mode of its own. Most sharded systems refuse to pay this and instead give up cross-shard atomicity, reaching for eventual-consistency patterns (sagas, the outbox — a distributed-patterns topic) instead. Losing easy multi-row atomicity is the single biggest thing you sacrifice by sharding.
• Global constraints and IDs. A UNIQUE constraint or a foreign key is trivially enforced within one database and painful across many — each shard only knows its own rows. And a global auto-increment sequence needs cross-shard coordination, which is exactly why sharded systems abandon integer sequences for client-generatable globally-unique IDs (UUIDs, ULIDs) that need no central allocator.