Data distribution — what breaks after you spread it out
App servers scale. Databases don't scale the same way.
Copies let you distribute reads, but copies fall behind. Splitting distributes capacity and writes, but split-spanning operations get harder. Distributing data breaks things that were obvious on one machine. Knowing what breaks is what this chapter is about.
What you'll learn in this chapter
- Replication in sync vs. async, and what each loses
- Reading a replica means you can't read what you just wrote
- Sharding and picking a partition key, how skew happens
- When eventual consistency shows to users
- Reading the CAP theorem as "what to give up during a partition"
Builds on ACID from Transactions and isolation levels from Locks and isolation levels. Also uses standby patterns from Availability and redundancy.
What this chapter does not cover
Consistency across multiple services isn't covered. Methods like the Outbox pattern (matching database writes with message sends without loss) and Saga (splitting long processes and running compensating actions on failure) belong to inter-service integration guides. This chapter focuses on spreading one datastore across multiple machines.
Index design and query writing are in Indexes; cloud database product settings are in Practical AWS Guide — RDS.
Two ways to split
Two methods for placing data on multiple machines, by purpose.
| Method | What it does | Solves | Doesn't solve |
|---|---|---|---|
| Replication | Hold copies of the same data on multiple machines | Distributing reads and continuing when one machine dies | Capacity. Write volume |
| Sharding | Split data and place pieces on different machines | Capacity and write distribution | Continuing when one machine dies (needs separate replication) |
As "Distinctions from similar terms" in Indexes noted, sharding isn't a technique to speed up lookup. It solves problems of scale that don't fit on one machine.
These two are used together. Configurations that replicate each shard piece are common.
Replication — hold copies
Send changes from the write-accepting machine (primary) to others (standby, replica). Two send styles.
| Style | Response returns when | Loses |
|---|---|---|
| Sync | After replica has received and persisted | Writes get slower. Slow replica makes all writes wait |
| Async | As soon as primary writes | Primary crash loses what wasn't sent |
PostgreSQL's comparison table organizes this trade directly. Async is "doesn't wait on multiple servers" at the cost of not satisfying "no data loss on primary failure"; sync is the reverse (PostgreSQL — Comparison of Different Solutions).
What to choose is decided by what you can lose. Orders and payments can't lose one — sync. Browse history can lose some — async.
What "sync" waits for is settings-graded. In PostgreSQL, you must specify synchronous_standby_names, or sync replication isn't enabled at all. The default is empty, and commits then don't wait for replication (PostgreSQL — synchronous_standby_names). Enabled with default synchronous_commit = on, you wait for the replica to receive and persist, but not until replica queries can see it. To wait for visibility, specify remote_apply (PostgreSQL — synchronous_commit). Sync doesn't guarantee "latest is readable on the replica".
Can't read what you just wrote
Async replication with reads to replicas creates moments when you can't read what you just wrote.
To the user, "I saved it but it's not reflected". Looks like a bug, but is designed behavior.
Handling depends on use:
| Handling | Content | Fits |
|---|---|---|
| Just after writing, read primary | For some time after update, route that user's reads to primary | General purpose. Implementation touches connection routing |
| Use the just-returned value on screen | Just display the saved content; don't re-read | Just after post display |
| Communicate delayed reflection | Show "Reflection takes a few seconds" | Aggregations, rankings |
Delay magnitude is measurable. In PostgreSQL, monitor the position difference between primary and replica. Constant multi-second delay is a config or load issue.
A separate layer from isolation levels
The isolation levels covered by Locks and isolation levels are about how simultaneously running transactions look inside a single database.
Replica-spanning delay is a different delay layered on top. Even at the strongest isolation, an async replica behind returns old values. The two live in different layers, and strengthening one doesn't solve the other.
Sharding — split and place
Split data; place pieces on separate machines. The value that decides the split is the partition key.
What you gain is capacity and write distribution. In exchange, three things get harder.
- Cross-shard search — you have to query every shard and merge
- Cross-shard transactions — transaction atomicity no longer fits on one machine
- Split changes — adding shards requires moving existing data
So picking the partition key is central to the design. Choose a key so the frequent search fits within one shard. For user-scoped services, user ID is a candidate.
Keys that produce skew
Partition keys should be many-valued with distributed access. Keys with the opposite property concentrate load on a specific shard.
| Key example | Distribution | Reason |
|---|---|---|
| User ID (many users) | Good | Many values; access distributes |
| Creation date rounded to a day | Bad | Day's writes concentrate in one place |
| Status code (a handful) | Bad | Few values; split is meaningless |
| Device ID (one device dominates) | Bad | Many values, but access to one skews |
The classification matches DynamoDB's partition-key design guidance (Amazon DynamoDB — Designing partition keys to distribute your workload). Confirm variety and access distribution separately. Even many values can concentrate.
For values that get outsized reads (hot products, celebrity accounts), you can append a suffix to distribute across shards. The cost is that reading that value requires collecting from multiple shards.
Eventual consistency
Crossing replicas or splits removes the state where every machine holds the same value at all times. What holds instead is "if updates stop, everything eventually settles on the same value". Called eventual consistency.
The issue is what shows during "eventual". It shows to users.
- Just posted; open the list and your post isn't there
- Like count changes on refresh
- Payment completed but purchase history doesn't show it yet
Design decides two things. Which operations tolerate delay, and what to show during the delay. Displays involving money often can't allow it — read primary there.
CAP theorem — what to give up during a partition
For distributed data, an unavoidable constraint: the CAP theorem.
Three properties (Gilbert and Lynch, "Perspectives on the CAP Theorem", 2012):
| Property | Meaning |
|---|---|
| Consistency | Each operation appears to happen at one instant between request and response. Indistinguishable from one server processing in order |
| Availability | Every request received by a non-failing node results in a response — speed isn't the point |
| Partition tolerance | Continues to work when node communication is delayed or lost |
The theorem says: on networks where communication can fail, you cannot both respond to all requests and preserve consistency (§2 of the paper).
The reading matters. "Pick 2 out of 3" is a widespread explanation, but practitioners don't pick that way. Partitions aren't chosen; they happen. Networks fail.
So the real choice is:
During a partition, do you give up consistency or availability?
| Give up | Behavior during partition | Fits |
|---|---|---|
| Availability | Non-communicating side refuses. Errors, but no old values | Balance, inventory, reservations |
| Consistency | Both sides respond. Old values may return; reconcile after recovery | View counts, notifications, history |
You can choose per feature in the same system. Refusing balance queries while recommendations return old values isn't contradictory.
Outside partitions, you satisfy both. CAP only applies during partitions, not the everyday design.
Decision procedure
- Decide what's insufficient — reads → replicate; capacity and writes → shard
- Decide what you can lose — none → sync. Delay OK → async
- Decide where reads go — for replicas, also decide how to handle reads just after write
- When sharding, pick a key from the search shape — do frequent searches fit in one piece?
- Confirm key skew — separately check value variety and access distribution
- Enumerate delay-visible spots — what does the user see? Money displays read primary
- Decide per-feature behavior during partition — refuse, or return old values?
Common misconceptions
"More replicas speed up writes." — Only reads speed up. Writes still go to one place, and more replicas means more targets to send changes to. If write volume is the problem, consider sharding.
"Sync replication has no delay." — Two things differ. First, what's waited for is set by config. In PostgreSQL you must name the target, and even then default only waits for persistence, not until replica queries can see it. Reading a replica just after write can still return old values under sync. Second, waiting makes writes slower. On days a replica is slow, all writes slow; replica failure can become write failure.
"CAP is pick 2 of 3." — Partitions can't be chosen. You pick behavior during a partition. Everyday satisfaction of both C and A doesn't contradict the theorem.
"Eventual consistency means no consistency." — There's a guarantee: "eventually converges". What's missing is a promise on "when". The issue is what users see until convergence, not perpetual divergence.
"Raising isolation levels solves replica delay." — Different layers. Isolation is about simultaneous execution within one machine. Replica delay is outside that.
Exercises
Q1. "Just posted, open the list, and my post isn't shown" is reported. Setup is async replication with reads on replicas. Explain cause and remedies.
Answer and explanation
Cause: the read ran before the write reached the replica. Not a bug — this is designed behavior of async replication.
Remedy candidates:
| Remedy | Effect | Cost |
|---|---|---|
| Route that user's reads to primary for a period after post | Definitively solves | Primary read load increases |
| Front-end prepends the posted content to the list | Solves display-wise | Discrepancy with other elements possible |
| Reduce replica delay | Only lowers frequency | Never zero |
The first is the main path. "Your own writes are visible to you" is enough; other people's post delay is usually tolerable.
Q2. For a per-user-closed service, you're planning "creation date" as the sharding partition key. What's the problem?
Answer and explanation
Two problems.
- Writes concentrate on one shard. All today's data goes to the same piece. Sharded but write not distributed
- Frequent searches span shards. "All data for this user" spans shards for all periods the user was registered
Choose user ID naturally. Many values, access distributes, frequent searches fit in one shard.
But even user ID skews if one user is disproportionately large. Check variety and access distribution separately.
Q3. "By CAP, we take availability and give up consistency" is written in a design memo. What do you fix?
Answer and explanation
Add the scope and conditions.
Two deficits:
- When isn't stated. CAP applies during a partition. Outside, both are satisfied. Reads as "always give up consistency"
- Which features isn't stated. The choice doesn't have to be system-wide. Balance refuses, view counts return old — per-feature is fine
Rewritten:
While node communication is severed, view counts and notifications return old values and keep responding. Balance and inventory reads return errors and don't return old values.
This makes what to implement clear. Design is not which to pick, but deciding per-feature which to pick.
Summary
- Two ways. Replication solves reads and continuity; sharding solves capacity and writes
- Sync is slow; async loses unsent parts on primary failure
- Reading a replica means you can't read what you just wrote. Decide handling per use
- Replica delay is a separate layer from isolation levels. Strengthening one doesn't solve the other
- Partition keys: many-valued with distributed access. Rounded dates concentrate writes
- Eventual consistency's issue is "what shows until convergence". Money displays read primary
- CAP isn't "2 of 3". Per feature, pick which to give up during a partition — consistency or availability
- Transactions — Premise that atomicity fits within one machine
- Locks and isolation levels — Concurrent visibility within one machine
- PostgreSQL — Comparison of Different Solutions — Sync vs. async trade-off
- Gilbert and Lynch — Perspectives on the CAP Theorem — Definitions of the three properties and the theorem's claim
- Amazon DynamoDB — Designing partition keys — Partition key quality classification
What to read next
- Architecture diagrams and trade-offs — Explaining the choices so far in diagrams and words
- Transactions — How atomicity is preserved on one machine