# Scaling a Database (Replication & Sharding)

> When one database box isn't enough: how to scale up and optimize first, scale reads with replication (and live with replication lag), and only then scale writes with sharding - and why sharding is the expensive last resort.


---

# Scaling a Database (Replication & Sharding)

The product is working. Traffic is climbing - good news - and the database, the one box everything depends on, starts breathing hard. Queries that used to return instantly now hang. Someone in a meeting says "scale," and suddenly people are talking about replication and sharding like the obvious next step.

Here's what nobody tells you in that meeting: **most databases that "need to scale" don't.** They need a better index, a fixed query, or a cache - fixes that cost an afternoon, not a re-architecture. Reaching for replication or sharding before you've exhausted the cheap wins is one of the most expensive mistakes a team can make, because both are *permanent complexity* you can't easily take back.

This guide is about doing it in the right order. First, make the one box you have do less work. Then, when you genuinely need more capacity, scale **reads** with replication - the safe, common, well-understood move. Only when writes themselves are the wall do you reach for **sharding**, the powerful, costly, hard-to-undo option that splits your data across machines. We'll be plain about every trade-off, because the people who get burned are the ones who were sold the shiny version.

This guide assumes you're comfortable with what a database is and how queries work. If a query is slow and you're not yet sure *why*, start with [Why Is My Query Slow?](/guides/why-is-my-query-slow) first - most of Phase 1 builds directly on it.

## How to read this
- **Under pressure to "scale the database" right now?** Read [Phase 1: The Bottleneck](01-the-bottleneck.md) before you let anyone provision a single new machine. It will probably save you the whole project.
- **Want it to finally make sense?** Read in order - the bottleneck mindset tells you *whether* to scale, replication tells you how to scale reads, and sharding tells you the real cost of scaling writes.

## The phases
1. **[The Bottleneck](01-the-bottleneck.md)** - scale *up* and optimize before you scale *out*: indexes, queries, caching, connection pooling. The crucial distinction between a read-heavy problem and a write-heavy one, because they have completely different cures.
2. **[Replication](02-replication.md)** - keeping live copies of the database: a leader takes writes, followers serve reads. How this scales reads and gives you failover - and what replication lag means for your app the day a user reads stale data.
3. **[Sharding](03-sharding.md)** - splitting the *data* itself across machines by a shard key to scale writes. The hard parts told plainly: choosing the key, cross-shard queries and joins, rebalancing, and the transactions you lose. Why it's the last resort.

> Deliberately deferred to follow-up guides: the deep mechanics of [transactions and ACID](/guides/transactions-and-acid) (which sharding quietly breaks), and the [SQL vs NoSQL](/guides/sql-vs-nosql) decision (many NoSQL systems shard for you by default). This guide is about understanding the moves and their costs - so that whether you do it by hand or pick a managed system that does it for you, you know what's actually happening underneath.


---

# The Bottleneck

A database under load *feels* like it needs more hardware. It almost never does, at first - and machines you add for scaling are hard to remove later. So before you provision anything: find the actual bottleneck, fix it cheaply, and only then ask whether you've outgrown one box.

Two ideas drive this phase. **Scale the query before you scale the hardware.** And **a read-heavy problem and a write-heavy problem are different illnesses** - almost everything in the next two phases hinges on which one you have.

## Scale up before you scale out

📝 **Terminology.** *Scaling up* (*vertical scaling*) means giving your one database machine more CPU, RAM, or disk. *Scaling out* (*horizontal scaling*) means adding more machines and spreading the work. Replication and sharding are both scaling out.

These aren't two points on the same line - they're different worlds. A bigger single box is still one database: same queries, same transactions, same mental model, just more room. The moment you go to multiple machines, you inherit a permanent tax of coordination and operational complexity that never goes away.

"Scaling out" sounds like the grown-up answer, but a single modern server is enormous - hundreds of gigabytes of RAM, dozens of cores. Most applications never outgrow one well-tuned box. Everything is easier to reason about on one machine: bugs, backups, transactions, "where is this row" - trivial on a single box, genuinely hard across many. So: optimize what you have, scale up if you must, and treat scaling out as the move you make when a single machine truly can't keep up - not the default.

⚠️ **Gotcha - scale the query before you scale the hardware.** The slow-database feeling is far more often a *missing index* or a *bad query* - a full table scan, an N+1 loop firing a thousand small queries per page, a `SELECT *` dragging back columns nobody reads. Hardware buys a little headroom and hides the real problem until it comes back bigger. Fix the expensive queries first - that's the entire subject of [Why Is My Query Slow?](/guides/why-is-my-query-slow). A well-placed index can turn a query from seconds to milliseconds, which beats any amount of new hardware, and it's free.

## The cheap wins, in order

Before "we need to scale" becomes a project, walk this list. Each step is cheaper and less permanent than the one after it.

```mermaid
flowchart TD
  A["1. Fix the queries - indexes, kill N+1, stop SELECT *"] --> B["2. Add a cache - stop asking the DB the same thing"]
  B --> C["3. Pool the connections - reuse them, don't drown the DB"]
  C --> D["4. Scale UP - a bigger single box"]
  D --> E["5. Scale OUT: replication - more machines, more reads (Phase 2)"]
  E --> F["6. Scale OUT: sharding - split the data, more writes (Phase 3)"]
  A -.- top([cheapest / least permanent])
  F -.- bottom([most expensive / hardest to undo])
```

### Add a cache

A cache is a fast, temporary store (Redis, Memcached, often just in-memory) that holds answers to expensive or frequently-repeated questions, so your app skips the database entirely. The classic pattern is *cache-aside*: check the cache first; on a miss, query the database and store the result for next time.

If your homepage runs the same "top 10 articles" query for every visitor, you're asking the database the identical question thousands of times a minute for an answer that changes maybe once an hour. A cache turns thousands of database hits into one, plus thousands of fast cache reads. For read-heavy workloads, a good cache is often the single highest-leverage change you can make - and it's reversible, unlike adding machines.

⚠️ **Gotcha.** Caching introduces *staleness*: the cached answer can be out of date until it expires or you invalidate it. You're trading perfect freshness for speed, on purpose. (Hold onto that - replication in Phase 2 makes the same trade in different clothes.) The hard part of caching isn't the cache; it's deciding when to throw entries away. Phil Karlton's line - "there are only two hard things in computer science: cache invalidation and naming things" - stops being funny the first time stale data ships to a user.

### Pool your connections

A connection pool is a fixed set of already-open database connections that your application borrows and returns, instead of opening a brand-new connection per request. Every new connection costs real work - authentication, a new backend process or thread - and databases have a hard ceiling on how many they can hold open. A flood of connections can bring a database to its knees while CPU and disk sit nearly idle.

With a pool, a hundred concurrent web requests share, say, twenty long-lived connections, taking turns. Most web frameworks and ORMs have pooling built in or one config flag away; in front of databases like PostgreSQL, a dedicated pooler (PgBouncer is the common one) manages this at scale.

A team once "scaled" their database to a bigger instance because it kept hitting connection limits under load. The bigger box hit the same wall a week later - the limit was self-inflicted: every request opened its own connection. A connection pool fixed in an afternoon what a hardware upgrade couldn't fix at all.

## Read-heavy or write-heavy? This decides everything

**Is your bottleneck reads or writes?** This is the most important question in the guide.

📝 **Terminology.** A *read* is any query that fetches data without changing it (`SELECT`). A *write* is anything that modifies data (`INSERT`, `UPDATE`, `DELETE`). A *read-heavy* workload does far more reads than writes; a *write-heavy* workload is dominated by writes.

The two scaling tools ahead solve different problems:

```mermaid
flowchart LR
  R["too many READS"] --> RT["REPLICATION (Phase 2)<br/>copies of the DB serving reads"]
  W["too many WRITES"] --> WT["SHARDING (Phase 3)<br/>the data split across machines"]
```

Most applications are overwhelmingly read-heavy - a social feed, a news site, a store catalog: read constantly, written rarely. That's good news, because **reads are the easy thing to scale.** Make as many copies of the data as you like and spread reads across them - that's replication, the well-trodden path.

Writes are the hard thing. Every copy of the database has to agree on the new value, so more copies can't absorb more writes - a write has to land everywhere. When *writes* are your wall, copies don't help; you have to split the data itself so different machines own different writes. That's sharding, and it's hard precisely because writes are hard.

If you misdiagnose this, you'll reach for the wrong tool and the pain won't go away. Teams add read replicas (Phase 2) to a database that's actually drowning in writes and are baffled when nothing improves - replicas don't take writes off the leader; they add to its load. Measure your read/write ratio before you pick a strategy (PostgreSQL's `pg_stat_statements` breaks down where time goes). Diagnose first. The cure depends entirely on the disease.

## Recap

1. **Scale up before you scale out.** One bigger box keeps the simple mental model; multiple machines impose a permanent coordination tax. Most apps never truly outgrow one well-tuned server.
2. **Scale the query before you scale the hardware.** Missing indexes, N+1 queries, and `SELECT *` masquerade as capacity problems. Fix them first - it's free and it's the biggest win. (See [Why Is My Query Slow?](/guides/why-is-my-query-slow).)
3. **Add a cache** for repeated reads, and **pool your connections** so you don't drown the database - both cheap, both reversible.
4. **Diagnose read-heavy vs. write-heavy.** Reads are scaled with replication (Phase 2); writes, much harder, with sharding (Phase 3). Measure before you choose.

Next: the most common real scaling move there is - making copies of your database so they can share the read load.


---

# Replication

You've done the cheap work from [Phase 1](01-the-bottleneck.md) - queries tuned, a cache in place, connections pooled - and the database is *still* pinned, drowning in reads. Good: you've earned the right to scale out. For a read-heavy workload, replication is the move: the most common database-scaling technique there is, and it does two things at once - multiplies your read capacity, and gives you a spare copy ready to take over if the main one dies.

## The mental model: one leader, many followers

Replication means running **multiple live copies of the same database** on separate machines, kept in sync. One machine is the **leader** - the only one that accepts writes. Every change it makes, it streams to one or more **followers**, which apply those same changes to their own copies. Followers are read-only mirrors that trail just behind the leader.

📝 **Terminology.** The leader is also called the *primary*, *master*, or *source*; followers are called *replicas*, *secondaries*, *standbys*, or *read replicas*. Names vary by database, but the roles are identical: one place writes go, several places reads can come from. We'll say *leader* and *follower*.

```mermaid
flowchart TD
  Writes[WRITES go here only] --> Leader["LEADER<br/>(the one source of truth for writes)"]
  Leader -->|streams the write log| F1[FOLLOWER]
  Leader -->|streams the write log| F2[FOLLOWER]
  Leader -->|streams the write log| F3[FOLLOWER]
  F1 --> Reads[READS spread across followers]
  F2 --> Reads
  F3 --> Reads
```

The leader already keeps an ordered log of every change it makes, for crash recovery (PostgreSQL calls it the *write-ahead log* / WAL; MySQL calls it the *binlog*). Replication is, at heart, the leader shipping that change-log to each follower, which replays it to stay current - not re-running your `UPDATE` from scratch, just applying the leader's recorded result. That's the whole engine.

## How replication scales reads

Your app sends every write (`INSERT`, `UPDATE`, `DELETE`) to the leader, and spreads its reads (`SELECT`) across the followers. If one box could handle all your reads before tipping over, three followers give you roughly three boxes' worth of read capacity - add another follower, get more headroom, because a read can be answered by *any* copy.

Something has to route "this query goes to the leader, that one goes to a follower": read/write-aware application code (many ORMs support a primary/replica split), a *proxy* in front of the cluster that routes by inspecting the query, or a managed cloud database's single "reader endpoint" that load-balances across followers for you.

The day a marketing campaign triples your read traffic, you don't rewrite anything - you add a follower or two and the load spreads. Read capacity becomes a dial you can turn, a very different life from watching one box redline with no options.

## The other gift: failover and redundancy

Scaling reads is the headline, but replication quietly hands you a **hot spare**: a follower already holds a complete, current copy of your data, so it can be *promoted* to become the new leader if the original dies. This is **failover** - the difference between "a disk failed, we're down until we restore last night's backup" and "a disk failed, we promoted a follower, we were down for thirty seconds."

📝 **Terminology.** *High availability* (HA) means the system keeps serving even when a component fails. *Failover* is switching to a standby when the active one dies. *Promotion* is turning a follower into the leader.

The real catch: failover sounds automatic and clean, but it's genuinely tricky, because of **split-brain** - if the old leader isn't truly dead (just unreachable for a moment) and a follower gets promoted, you can briefly end up with *two* machines that both think they're the leader, both accepting writes, and now your data has diverged in two directions. Production systems use careful coordination (consensus, fencing, a witness node) to prevent this, and managed databases handle most of it for you - a strong argument for a managed offering if you can.

## The gotcha that defines replication: lag

⚠️ **Gotcha - replication lag, and the stale read.** A follower is always *slightly behind* the leader. The leader commits a write, then streams it, then the follower applies it - and during that gap, however small, the follower serves data from a moment ago. This delay is **replication lag**. Usually milliseconds; under load, a slow network, or a big batch write, it can stretch to seconds or worse. The consequence has a name: the **stale read** - a read replica handing back data that's already out of date.

This is the cache's trade-off from Phase 1, wearing different clothes: to make a copy that can serve reads, you accept it isn't instantaneously identical to the original. The technical name is *eventual consistency* - given no new writes, followers will *eventually* catch up, but at any given instant they might not match. You're trading strict freshness for read scalability, deliberately.

The classic bug: a user updates their profile, the app writes to the leader, then re-renders the page with a read routed to a follower that hasn't received the change yet. The user sees their *old* profile, concludes the save failed, and saves again. This is the most common replication footgun, called **"read your own writes."**

```mermaid
sequenceDiagram
  participant U as User
  participant L as Leader
  participant F as Follower
  U->>L: t0 clicks Save → WRITE lands on Leader ✔
  U->>F: t1 page reloads → READ goes to Follower
  Note over F: follower hasn't gotten the change yet
  F-->>U: t2 user sees OLD data - stale read! "Did my save not work?"
```

You don't eliminate lag - you decide where you can tolerate it:

- **Route reads-after-writes to the leader.** For the brief window after a user writes, send *that user's* reads to the leader so they always see their own change. Costs a little leader load for correctness where it matters most.
- **Accept staleness where it's harmless.** A view count, a "trending" list, an analytics dashboard - nobody is harmed if it's a few seconds behind. Send these to followers freely; this is most of your traffic.
- **Read from the leader when freshness is non-negotiable.** Account balances, inventory at checkout - read from the leader, accept the cost.

Teams that get burned by replicas flip reads to followers globally and assume the data is always current. The ones who sail through ask, query by query, "what happens if this read is two seconds stale?" and route accordingly. Make that a habit and replication becomes a tool you trust, not a source of mystery bugs.

## What replication does NOT solve

Replication scales reads. It does **not** scale writes. Re-read the leader diagram: *every write still goes through the single leader.* Adding followers gives you more places to read from, but not one extra ounce of write capacity - each follower even adds a little work, since the leader must stream its log to all of them. If your bottleneck is writes, more replicas won't help.

## Recap

1. **Replication = one leader (takes all writes) + followers (serve reads),** kept in sync by streaming the leader's change-log.
2. It **scales reads** (any copy can answer a read) and provides **failover/redundancy** (a follower can be promoted if the leader dies - though promotion is genuinely tricky; beware split-brain).
3. **Replication lag is unavoidable:** followers trail the leader, so reads from a follower can be **stale**. This is eventual consistency - the same freshness-for-speed trade as caching.
4. **Design around the stale read** - especially "read your own writes." Route by tolerance: leader for must-be-fresh, followers for harmless-if-slightly-old.
5. **Replication does not scale writes.** Every write still funnels through the one leader. When writes are the wall → Phase 3.

Next: the hard one. Splitting the data itself so different machines own different writes - and the real price you pay for it.

Watch it animated: [database replication](/explainers/Replication.dc.html)


---

# Sharding

You've optimized the queries, cached the hot reads, pooled the connections, and spread reads across followers. The database is *still* the wall - but now it's the **writes**. The leader alone can't keep up with the volume of `INSERT`s and `UPDATE`s, and replication (Phase 2) is no help, because every write still funnels through that one leader. Copies don't cut it anymore; you have to split the data itself.

This is sharding: the most powerful tool in this guide, and the most expensive - reach for it *reluctantly*, and with eyes open to the parts vendors gloss over.

## The mental model: split the data, not copy it

Sharding splits your data into pieces called **shards** and puts each on a *different* machine. Every row lives on exactly one shard. Unlike replication - where every machine holds *all* the data - each machine here holds only *part* of it. A **shard key** (a column, or a few - also called a *partition key*) decides which shard a row goes to.

📝 **Terminology.** *Sharding* is also called *horizontal partitioning* - "horizontal" because you're splitting the table by *rows* (this user's rows here, that user's rows there), not columns.

```mermaid
flowchart TB
  subgraph REP["REPLICATION (Phase 2) - each machine = a FULL copy · scales READS"]
    R1[ALL data]
    R2[ALL data]
  end
  subgraph SH["SHARDING (Phase 3) - each machine = a SLICE · scales WRITES"]
    S1[users A–H]
    S2[users I–P]
    S3[users Q–Z]
  end
```

With three shards, writes for users A–H land on shard 1, I–P on shard 2, Q–Z on shard 3 - three machines absorbing writes *in parallel*, each responsible for a third of the load. That's the parallelism replication could never give you, because no single machine has to see every write anymore. Add shards, add write capacity.

Two common schemes for mapping a key to a shard:

- **Range-based:** split by ranges of the key (A–H, I–P, Q–Z; or orders by date). Great for range queries - but prone to *hot shards* if one range gets disproportionate traffic (everyone whose name starts with S, this month's orders).
- **Hash-based:** hash the key to pick the shard. Spreads load evenly and avoids hotspots - but destroys natural ordering, so "all orders from last week" means asking every shard.

Even distribution vs. cheap range queries - your first taste of how every sharding decision costs you something elsewhere.

Now the plain part: sharding works, and it's also where a database stops being one clean thing and becomes a distributed system. Distributed systems are *hard*. Here are the costs, plainly.

## Hard part #1: choosing the shard key

This is the most consequential decision in the project. The shard key decides how evenly load spreads, which queries stay fast, and how painful future changes will be. A good key spreads writes evenly *and* matches how you actually query the data, so most queries touch a single shard. A bad key creates a **hot shard** (one machine swamped while others idle) or forces nearly every query to fan out across all shards.

⚠️ **Gotcha - the shard key is nearly impossible to change later.** Changing your mind means re-deciding where every row lives and physically moving most of your data across machines while the system is live. Teams put this off for months because it's so disruptive. Choose as if you can't change it, because in practice you almost can't.

**A concrete example.** Shard a multi-tenant app by `tenant_id`, and any one customer's data lives together on one shard - "show me everything for tenant 42" hits a single machine, fast. Shard by `created_at` instead, and that tenant's rows scatter across every shard by date, so every per-customer query has to ask all of them. Same data, same machines - one key makes your common query cheap, the other makes it expensive forever.

## Hard part #2: cross-shard queries and joins

This is where sharding's cost shows up on queries that were trivial yesterday. A query needing data from *more than one shard* can't be answered by one machine - the system has to ask several, then combine the results. This is a **cross-shard query** (or *scatter-gather*: scatter the question to every shard, gather the answers back).

`SELECT COUNT(*) FROM orders`, one query on a single database, becomes: ask every shard for its count, wait for the slowest one, sum the results - now as slow as your slowest shard, and it loads all of them at once. `ORDER BY ... LIMIT 10` is worse: each shard returns its own top 10, and a coordinator must merge and re-sort to find the true top 10.

⚠️ **Gotcha - cross-shard joins.** The one that surprises people most: a `JOIN` between two tables on different shards is, in general, something a sharded database cannot do efficiently - or at all. If `users` is sharded one way and `orders` another, joining them means pulling data across machines and stitching it together yourself. The usual answer is to *co-locate* related data by sharding both on the same key (shard `users` and `orders` both by `tenant_id`, so the join stays local) - but that only works if one key makes sense for everything, and it rarely does for *every* query. Some joins you give up, denormalize away, or compute in the application.

Design your schema and shard key around your most important queries, and expect that *queries spanning the shard key are the expensive ones* - a few reports will get slow or need to move to a separate analytics system.

## Hard part #3: rebalancing

Over time, shards drift out of balance - one fills up or gets hammered while others idle - or you need to add machines. **Rebalancing** is moving data between shards to even things out, and doing that live, without losing writes or breaking queries mid-move, is genuinely hard.

The naive scheme - `shard = hash(key) % number_of_shards` - has a brutal flaw: change the number of shards and the modulo changes for *almost every key*, so adding one machine means relocating nearly all your data. Systems that rebalance gracefully use cleverer schemes (*consistent hashing*, or many small logical shards mapped onto fewer physical machines) so adding capacity moves only a small fraction of the data - exactly the kind of machinery managed and distributed databases build for you, and a strong reason not to hand-roll sharding if you can avoid it.

## Hard part #4: the transactions you lose

This is the cost that's easiest to miss and most dangerous to discover late. On a single database, a transaction lets you change several rows *atomically* - all commit, or none do - even across different tables. That guarantee is the bedrock a lot of correct code quietly stands on. (If "atomic" is fuzzy, the foundations are in [Transactions and ACID](/guides/transactions-and-acid).)

⚠️ **Gotcha - you largely lose cross-shard transactions.** The moment two rows in one logical operation live on *different shards*, a normal transaction can't span them. Classic example: moving money - debit account A, credit account B. On one database that's one atomic transaction; on different shards, there's no simple way to make both-or-neither hold. There are answers - *distributed transactions* via two-phase commit (slow, complex, prone to locking trouble), or application-level *sagas* that do each step and compensate on failure - but they're far harder to get right than the single-line transaction you're used to. **A lot of sharding pain is really the pain of losing easy transactions.**

Plenty of teams shard, then *much* later realize an operation that must be atomic now spans shards, with no clean fix short of re-sharding or rewriting it. Before you commit to a shard key, walk through your must-be-atomic operations and check they stay inside one shard. If a critical one doesn't, rethink the key - or don't shard yet.

## When to actually reach for this

Sharding is the right tool when:

1. You have a genuine **write** bottleneck (Phase 1's diagnosis), not a read one.
2. You've exhausted the cheaper options: optimized queries, caching, a bigger box, and read replicas for the read side.
3. You can identify a shard key that keeps your **most important queries on a single shard** and your write-load **evenly spread**.

If you can't tick all three, you're probably not ready - good news, since you get to keep the simple life a while longer.

💡 **Key point - let someone else carry the weight if you can.** Most teams should not hand-build sharding. Managed and distributed databases (Vitess for MySQL, Citus for PostgreSQL, "distributed SQL" systems like CockroachDB and Spanner, plus many NoSQL stores) handle routing, rebalancing, and some cross-shard querying *for you*. They don't make the costs vanish - cross-shard joins are still expensive, the shard-key choice still matters, distributed transactions are still slower - but the hard parts above stop being *your* code to maintain at 2am. (This overlaps with the [SQL vs NoSQL](/guides/sql-vs-nosql) decision, since many NoSQL systems shard by default.) The cost here is permanent in a way the earlier moves aren't - the goal was never to shard, it was to keep the product up, and sharding is the heaviest, last tool you take off the shelf to do that.

## Recap

1. **Sharding splits the data across machines by a shard key** (each row on one shard) - unlike replication, which copies all data everywhere. This is what **scales writes**, because shards absorb writes in parallel.
2. **Choosing the shard key is the make-or-break decision** - it sets load balance and which queries stay fast, and it's nearly impossible to change later.
3. **Cross-shard queries and joins are slow or impossible.** Scatter-gather is as slow as your slowest shard; cross-shard joins usually require co-locating data on the same key or giving up the join.
4. **Rebalancing data live is hard;** naive `hash % N` relocates almost everything when you add a machine - good systems move only a fraction.
5. **You largely lose cross-shard transactions** - the atomic, all-or-nothing operation you relied on no longer spans shards cheaply. Much of sharding's pain is this.
6. **It's the last resort.** Exhaust optimization, caching, and replication first; prefer a managed/distributed database to hand-rolling it.

Watch it animated: [database sharding](/explainers/Sharding.dc.html)