Skip to content
build-3000
databases

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.

Self-verified0/9
Functional
Architecture
Security
Testing
Production

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.

  1. What does replication buy, what does sharding buy - one sentence each?

  2. Why is the shard key called a permanent decision, and what escape hatch did you document?

  3. 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