Replication and Sharding
Replicas for reads and survival, shards for writes and size - what each solves and what each breaks.
Before this: Relational Data Modeling
Objective
Understand and rehearse data-scaling's two levers: replication spreading reads and surviving machine loss, sharding spreading writes and datasets - including replica-lag reality, shard-key consequences, and the honesty to know when neither is needed yet.
Vocabulary
The words to use when directing your agent.
- replication
- Keeping copies of the same data on multiple nodes.
- replica lag
- The delay before copies reflect the primary's latest writes.
- sharding
- Splitting DIFFERENT rows across nodes so no single machine holds everything.
- shard key
- The column deciding which shard owns each row - effectively choosing your future queries.
- hot spot
- A shard receiving disproportionate load because its key range got popular.
Understand it
Lag is the tax on copies
Primary accepts writes; replicas catch up continuously but never instantly. Read-from-replica architectures buy throughput by selling freshness: dashboards may trail seconds behind. The classic self-inflicted wound: write then immediately read from a lagging replica and swear the write vanished. Cure: route author-following reads to the primary briefly - read-your-writes - and label everything else's freshness honestly.
Sharding is surgery; avoid until proven necessary
Shards complicate transactions, joins, backups, rebalancing and every query that crosses boundaries. Honest sequence: indexes first (Level 3), caching second, read replicas third, THEN shards when write volume or dataset size demonstrably demands it. Teams that shard early ship slower and debug longer; teams that shard when justified can - because they learned the rest first.
Core concepts
Replicas spread reads; shards spread rows
Replication copies the WHOLE dataset onto additional machines: read throughput scales, and losing one machine loses nothing permanent. Sharding splits the dataset so DIFFERENT machines own DIFFERENT rows: write throughput and dataset size scale beyond any single box. Most applications need the first far earlier than the second.
The shard key chooses your queries' fate
Shard by tenant_id and tenant-scoped queries fly while cross-tenant analytics scatter-gather painfully. Shard by date and recent data burns hot while archives idle. Resharding later is major surgery - so the key decision is less about today's speed than about which tomorrows remain possible. Choose like it is permanent, because practically it is.
Examples
Reports move to the replica
Month-end aggregations hammered the primary until heavy reads moved to a replica. Dashboards now trail by a few labeled seconds; transactional paths breathe easy. Total cost: one routing config change and one freshness label.
Tenant-bound sharding
A multi-team ledger shards by workspace id: every team's rows live together, team queries hit exactly one shard, and cross-team reports are acknowledged as batch jobs - designed around, not apologized for. The key choice made every OTHER decision simple.
Common mistakes
Common mistake
Reading your own writes from a lagging replica and filing 'data loss' bugs.
Fix
Read-your-writes routing for immediate follow-ups; explicit freshness labeling elsewhere. Teach the UX to expect the gap.
Common mistake
Choosing a shard key from today's query patterns without modeling tomorrow's.
Fix
Document the key analysis: which queries stay single-shard, which scatter, what volume shifts break it. Permanent decisions deserve memos.
Working with your AI agent
Demand lag visibility before routing ANY reads elsewhere: measure replication delay, expose it, alert on growth. When evaluating shard keys, make your agent simulate query distributions against candidate keys - show me the single-shard versus scatter ratio for each candidate before I choose.
Build it
The module’s real project. Your agent builds; you direct and inspect.
Project: Scale Study
Run the Ledger through data-scale school: stand up a read replica with visible lag, route heavy reads to it with read-your-writes protection, and produce the sharding decision memo - key candidates modeled, one chosen, resharding escape-hatch documented.
Requirements
- Second database instance acting as read replica with a measurable, exposed lag indicator
- Heavy-read routes (reports/analytics) served from replica; author-following reads pinned to primary
- Freshness labels shown where replica reads serve data
- Lag measurement logged/metric-ed - visible in your observability outputs
- Sharding decision memo: three candidate shard keys modeled against real query patterns, one selected with reasoning, resharding escape-hatch sketched
- Failover rehearsal: primary killed, replica promoted (or equivalent documented procedure executed)
Context for your agent
Guide my Scale Study. Stand up a local read-replica setup with honest, measured lag exposure. Move my heaviest READ paths to the replica with read-your-writes pinning for author-sensitive follow-ups; label freshness in UI. Instrument lag into my metrics. Then co-author the sharding memo: model three candidate keys against my actual queries, recommend one, sketch the resharding escape hatch. Finish coaching my failover drill: kill the primary, promote, restore, record timings honestly.
Verify it - twice
Check every item yourself first. Then hand your agent the prompt and compare verdicts.
Verification prompt - paste into your AI agent
Interrogate my Scale Study as a capacity-planning skeptic. Attempt each: (1) write then instantly read along the replica path - did read-your-writes protect me, and if not, reproduce the failure, (2) pause replica syncing to inflate lag - do my metrics and labels tell the truth about staleness?, (3) stress my chosen shard key with the cross-cutting query patterns from my own memo - does the scatter cost match what I claimed?, (4) run my failover runbook BLIND - following only the document, report where it lies or stalls. Grade DoD items, then judge: is my sharding deferral honest engineering or postponed fear?
Run this against “Replication and Sharding” after your own checklist pass. Where the agent and you disagree, investigate before moving on.
Review questions
Answer out loud. If you cannot, revisit before moving on.
What does replication buy, what does sharding buy - one sentence each?
Why is the shard key called a permanent decision, and what escape hatch did you document?
Describe your worst replica-lag moment during the study.
You can now
- Deploy read replicas with lag-aware, labeled routing
- Model shard keys against real workloads before committing
- Rehearse failover until the runbook works blind
Up next
Load Balancing and Horizontal Scaling