Replication, sharding and consistency

Sharding splits data across independent databases so that writes scale, which is the one thing replicas cannot do. The shard key decides which shard holds a row, and it is the hardest decision in the system to reverse, because it is embedded in every query, every foreign key that no longer works, and every piece of application code that assumed a join was possible.

A poor key creates hotspots, and the two classic mistakes are geography and time. Sharding by country puts most of the traffic on whichever country you are largest in. Sharding by timestamp puts every current write on the newest shard while the others sit idle holding history, which is the worst possible arrangement: all the storage cost of many machines and the write capacity of one.

Hashing an identifier distributes evenly regardless of skew, and gives up range queries in exchange. Range partitioning keeps ordered scans cheap and invites hotspots. Directory-based sharding, a lookup table saying which shard holds which entity, keeps the flexibility to move one noisy tenant on its own and costs a lookup on every query plus a component that must never be unavailable. Each is a real answer for a different read pattern.

The key has to match how the data is read, not how it is naturally grouped. A query that can be answered from one shard is a normal query; one that spans shards becomes a scatter-gather, where the slowest shard sets the latency and the application does the joining and sorting that the database used to do. Notion sharded Postgres by workspace precisely because almost every query in that product is scoped to one workspace, which turns nearly all reads into single-shard reads.

Then there is everything you lose that nobody mentions in the design review. Cross-shard transactions are gone unless you build two-phase commit or sagas. Unique constraints across shards are gone, so identifiers have to be globally unique by construction. Auto-increment ids stop working, which is why Snowflake-style ids exist. Aggregate queries over the whole dataset need a separate analytics path. Each of these is solvable and each is work that did not exist the week before. Resharding later means moving live data while still serving traffic from it: double writes, backfill, verification, cutover, and a rollback plan for each. It is measured in months for a real system, which is why the honest order is a bigger machine, better indexes, caching and read replicas first. All four are reversible in an afternoon. Sharding is a change to the data model that you will live inside for years.

When a network partition splits your system, you either refuse requests in order to stay consistent, or answer them and risk divergence. That is the whole choice, and everything else written about CAP is commentary on those two sentences.

Partitions are not optional. Cables are cut, switches fail, a routing change isolates a rack, and a garbage collection pause long enough to miss every heartbeat looks exactly like a partition from outside. So sacrificing partition tolerance is not on the menu, and a system described as CA is a system whose behaviour during a partition has not been decided.

The choice is rarely uniform within one company, and this is where the theory becomes useful rather than academic. A core ledger chooses consistency and refuses the write. The cash machine in the lobby chooses availability, dispenses the money anyway, and reconciles afterwards with an overdraft fee, which is Brewer's own illustration and a reminder that the resolution rule can be a commercial decision rather than a technical one.

The extension worth knowing is PACELC: if there is a partition, choose availability or consistency, else, in normal operation, choose latency or consistency. That second half describes the trade you make every day, unlike the first half which describes a bad afternoon once a year. Every synchronous replication decision, every quorum size, every strongly consistent read is the else branch being answered.

Consistency in CAP is also narrower than the word suggests: it means linearizability, one register behaving as though there is a single copy. It is not the C in ACID, which is about invariants holding within a transaction. Conflating the two is the most common way a CAP discussion goes wrong, and noticing the difference is a reliable signal that someone has read past the triangle diagram.

So the answer that carries weight is never a letter. It is: during a partition this system refuses writes to these entities and accepts them for those, conflicts on the second group resolve by this rule, and here is what a user sees while it happens. Anything shorter has left the interesting part out.

Eventual consistency means replicas converge given no new writes. Read the definition carefully: it promises convergence and says nothing whatsoever about when, and in a system that is always being written to, the condition it depends on never actually occurs. The guarantee is real and it is weaker than the phrase makes it sound. There is a ladder of stronger session guarantees, and each is far cheaper than linearizability. Read-your-own-writes: you see your own changes. Monotonic reads: you never see time run backwards, which is what happens when consecutive reads hit replicas at different lag. Consistent prefix: you see writes in the order they were made, so a reply never appears before the message it answers. Most complaints filed as database inconsistency are one of these three, and each can be bought with routing rather than with consensus.

The engineering is mostly in the interface. Show the pending state, use an optimistic update so the change appears immediately, and do not pretend an action is complete when it is merely accepted. A spinner that says processing is honest; a green tick for work that is queued is a lie that generates a support ticket when the work later fails.

Convergence needs a merge rule, and choosing it is a product decision wearing technical clothes. Last write wins is the default in many stores and is the lossiest: it discards a write silently and depends on clocks you do not control. Amazon's shopping basket takes the union of conflicting versions, which can resurrect a removed item but can never lose an added one, because losing a purchase costs more than an unexpected item at checkout.

CRDTs are the version where the merge is a property of the data type rather than a decision at read time. A grow-only counter, an add-wins set, a sequence for collaborative text: each is defined so that merging in any order gives the same answer, which removes conflict resolution from the application entirely. They are not free, since the metadata to make that work can outgrow the data, and they are the right tool for exactly the cases they fit.

The practical test for a design is to ask what a user sees during the gap and what happens if two people act in it. If the answer to the first is nothing at all and to the second is one of them silently loses, the consistency model has not been designed, it has been inherited from a default.