System design, step by step · Lesson 3 of 7 · 14 min

How to Scale a Database: Indexes, Replicas and Sharding

How to scale a database step by step: model data around queries, add the right index, use read replicas despite lag, then shard by key with IDs that scale.

By Matthews WongReviewed on 13 October 2026

Key takeaway

Scaling a database means applying the cheapest fix that removes the measured bottleneck: model the data around its queries, add the right index, add read replicas and route around their lag, and only when one server cannot take the writes, shard the rows across servers by a shard key, with IDs that no single counter has to issue.

You will be able to

Order the fixes from cheapest to most expensive, and say which bottleneck each one removes.
Route reads so users always see their own writes despite replica lag.
Tell partitioning from sharding, choose a shard key, and pick IDs that need no shared counter.

Watch it work

Step through the diagram

Press Play, or step with the arrows. Every step is written out below the diagram.

From one primary to shards: the cheapest fix first

An index makes the primary's query cheap, a read replica takes reads but lags, sending a user's reads to the primary after a write hides that lag, a shard router splits writes by tenant, and UUIDv7 keys need no shared counter.

One Postgres primary takes every read and write. Fixes come cheapest first: an index inside it, then the dashed boxes, a read replica, a router and a second shard.

Keys: ← → step · Home / End · K play or pause

One Postgres primary takes every read and write. Fixes come cheapest first: an index inside it, then the dashed boxes, a read replica, a router and a second shard.
A list screen filters orders by tenant, newest first. Without an index Postgres reads every page; a B-tree on (tenant_id, created_at) walks straight to the matching rows.
CREATE INDEX ON orders (tenant_id, created_at);
Reads keep growing. A read replica replays the primary's write-ahead log and answers read-only queries; every write still goes to the one primary.
Predict the next step Ana saves an order, then reloads from the replica at once. She sees? (Her new order · The old list, without it · An error from the replica)
The write commits on the primary first. If replay on the replica lags 500 ms and her reload comes after 100 ms, the replica still shows the old list.
For a few seconds after Ana writes, send her reads to the primary; everyone else keeps reading the replica. Read-your-writes, at the cost of a little primary load.
SET pin:ana 1 EX 5
When writes outgrow one primary, only more servers help. A router hashes the shard key, tenant_id, and sends each tenant to one shard. Partitioning would stay on one server.
shard = hash(tenant_id) % 2Predict the next step Each shard keeps its own id sequence. What happens to order ids? (They stay unique everywhere · The same id exists on both)
Two sequences would issue the same numbers. Use UUIDv7 instead: the app creates it with no shared counter, and its time prefix keeps new keys together in the index.
id = uuidv7()

How do you choose the data model before scaling anything?

Start from the questions the system must answer, not from a SQL versus NoSQL label. A relational database lets you query flexibly and answer questions you have not thought of yet, at the cost of joins. A NoSQL store such as DynamoDB is designed around the most common queries, and the AWS guide warns that queries outside those planned ways can be expensive and slow.

Why is an index the first fix, and what does it cost?

An index is a second, sorted copy of some columns with pointers back to the rows. Without one, finding a tenant's newest orders means reading every page of the table; with a B-tree on the right columns, Postgres walks a short path and reads the matching rows already in order. A missing index looks exactly like a table that is too big, which is why it comes before any new server.

What do read replicas buy, and why do they return stale data?

A replica receives the primary's write-ahead log and replays it. In Postgres, a hot standby runs read-only queries and its transactions can never write, so replicas add read capacity, never write capacity. The documentation is plain that there is a measurable delay between primary and standby: the standby is eventually consistent with the primary.

When do you partition, when do you shard, and how do IDs change?

Partitioning splits one large logical table into smaller physical pieces inside one database server. The engine routes each row, queries that filter on the partition key skip the pieces that cannot match, and dropping an old partition is far faster than a bulk DELETE. Postgres's rule of thumb is that it pays off once the table is larger than the server's physical memory. It adds no hardware: it is still one machine.

The real config behind this lesson

Excerpts from the repository that builds and deploys this website, quoted as they are.

scripts/generate-blog-sources.mjs:10-12

 * This derives them rather than asking an author to restate them, for one
 * reason: structured data must mirror what the reader sees. A hand-maintained
 * citation list is free to drift from the visible block; a derived one cannot.

This site has no database, but it keeps a derived copy: lib/blog-sources.json is generated from the sources block that ends every post, so the citations in the structured data cannot drift from what readers see. That is the contract of an index or a replica too: a copy stays trustworthy only while it is derived from one source of truth and never edited by hand.

package.json:12

"prebuild": "node scripts/generate-blog-sources.mjs --check && node scripts/game/check-placements.mjs && node scripts/game/check-imports.mjs && node scripts/check-message-keys.mjs",

And the build will not ship a stale copy. prebuild runs the generator with --check first, which exits non-zero when the committed JSON no longer matches the posts. A replica that falls behind serves stale reads; a derived file that falls behind fails the build here, the stricter of the two choices.

lib/blog-meta.ts:9685-9690

  // The date a reader sees comes from monthKey + year; the date Google sees
  // comes from datePublished here (JSON-LD, sitemap lastmod, both feeds).
  // Checking key sets alone let those drift: 168 of 386 posts had disagreed,
  // several by ten months, so a card read "May 2025" while its own structured
  // data said 2024-07-15. Nothing surfaced it because both values were
  // individually valid.

What storing one fact twice costs. Each post's date lived in two registries, and 168 of 386 posts had drifted apart while each copy looked valid on its own. assertBlogParity() now throws on a mismatch, which fails the build. In a database the same lesson shapes the model: keep a fact in one place, or pay for whatever keeps the copies in step.

What breaks

Mistakes that cost real time

Sharding before indexing
A missing index looks exactly like a table that is too big. Shard it and every shard runs the same full scan, now with cross-shard queries and per-shard operations on top. Read the EXPLAIN output first.
Reading your own write from a replica
The commit is on the primary, the next read hits a replica that has not replayed it, and the record seems lost. Send that user's reads to the primary for a few seconds after a write. Making every commit wait for a synchronous standby to apply it, synchronous_commit = remote_apply, also works, at the price of slower writes.
One tenant bigger than a shard
Hashing spreads many keys evenly but never splits one. A tenant with ten times the rows of the rest makes its shard hot while the others idle. Design keys that spread traffic and avoid hot spots, and give an outsized tenant a shard of its own.
Random UUIDs as the primary key
UUIDv4 values land at random places in the B-tree, so a write-heavy table touches pages all over the index instead of one end. RFC 9562 calls this poor index locality; UUIDv7 keeps the uniqueness without the scatter.
A unique key on a partitioned table
Postgres requires a unique or primary key on a partitioned table to include every partition key column, because each partition's index can only enforce uniqueness within itself. A key on id alone is rejected, so you end up with (id, created_at), and id is no longer unique across partitions by constraint.

Partitioning or sharding: what each one changes
	Partitioning	Sharding
Where the pieces live	Inside one database server	On separate servers
Who routes a row	The database engine	Your shard router
Adds write capacity?	No: still one machine	Yes: each shard has its own
Joins and transactions	Normal, on one node	Hard across shards
Main reason to use it	Scan size and cheap retention	Writes or data beyond one server

Try it yourself

Do it on your own machine

You need Docker and Node. Run Postgres 18 locally, watch an index change a query plan, see UUIDv7 values sort by time, and count what a modulo shard router moves.

Start Postgres 18 and open psql.

docker run --rm -d --name pg -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
docker exec -it pg psql -U postgres

Create a million orders across 500 tenants, then read the plan for one tenant's newest orders. Expect a Seq Scan and a Sort that touch thousands of buffers.

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id int NOT NULL,
  created_at timestamptz NOT NULL,
  items int NOT NULL
);
INSERT INTO orders (tenant_id, created_at, items)
SELECT g % 500, now() - g * interval '1 minute', g % 7 + 1
FROM generate_series(1, 1000000) AS g;
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE tenant_id = 42
ORDER BY created_at DESC LIMIT 20;

3
Add the composite index and run the same EXPLAIN again. On my run the buffers fell from about 7,400 to 23, and the plan became an Index Scan Backward with no Sort.
```
CREATE INDEX ON orders (tenant_id, created_at);
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE tenant_id = 42
ORDER BY created_at DESC LIMIT 20;
```
4
Ask Postgres 18 for three UUIDv7 values. The first 12 hex digits are the creation time in milliseconds, so values made together start the same and sort in creation order.
```
SELECT uuidv7() FROM generate_series(1, 3);
```

Save router.mjs and run node router.mjs. Tenant 7 lands on shard 1 of 2, and going from 2 to 3 shards moves about two thirds of all tenants, which is why real routers use a lookup table or consistent hashing.

// router.mjs: which shard owns a tenant, and what moves when you add a shard
import { createHash } from "node:crypto";
const shardOf = (tenantId, shards) =>
  createHash("sha256").update(String(tenantId)).digest().readUInt32BE(0) % shards;
const tenants = 100_000;
let moved = 0;
for (let t = 1; t <= tenants; t += 1) {
  if (shardOf(t, 2) !== shardOf(t, 3)) moved += 1;
}
console.log(`tenant 7 lives on shard ${shardOf(7, 2)} of 2`);
console.log(`going from 2 to 3 shards moves ${((moved / tenants) * 100).toFixed(1)}% of tenants`);

Quick quiz

Three questions before you move on

Pick an answer to see why it is right or wrong. Nothing is saved or graded, so guess freely.

1.A tenant's order list got slow at 2 million rows. CPU is fine and one query dominates. What do you try first?
2.Users save a record, and the next page says it does not exist; a refresh shows it. Reads go to a replica. What is the best fix?
3.Your multi-tenant app shards by tenant_id. One tenant now has ten times the orders of any other. What happens?

Think it through

Answer in your head, then check

1.What is the difference between partitioning and sharding?Show the answer
Answer:Partitioning splits a table inside one server and the engine routes the rows. Sharding spreads the data across servers and your router does; only sharding adds write capacity.
2.Why do read replicas not help a write-heavy database?Show the answer
Answer:A hot standby runs only read-only queries. Every write still goes to the one primary, which also has to ship it to each replica.
3.A query filters tenant_id = 7 and sorts by created_at. Why is an index on (tenant_id, created_at) a good fit?Show the answer
Answer:The equality column leads, so the scan is limited to one tenant's entries, and those entries are already sorted by created_at, so Postgres reads them in order without a Sort.

Read the module

Every post in this module, in order

The lesson above is the map. Each post goes deep on one part of it, with the full worked examples.

Sources

Primary sources checked on the research date. Follow them for the current version.

Previous lessonHow to Scale a Web Application: Stateless Servers to CDN Next lessonConsistency in Distributed Systems Explained: ACID, CAP, Sagas