Scaling the Database

The database is the part of a system you cannot simply run more of. Application servers are interchangeable; a database holds state, and state is what makes scaling hard. It is why almost every system design conversation eventually arrives here.

There is an order to do things in, and most designs skip several steps. The order below is the answer to “how would you scale this?” — and knowing where to stop is more valuable than knowing the last step.

   1  index properly            free, and usually enough
   2  fix the query patterns    N+1 is the usual culprit
   3  connection pooling        cheap, and a common silent ceiling
   4  bigger machine            boring, effective, no code changes
   5  read replicas             reads scale; writes do not
   6  partition                 one table split within one database
   7  shard                     data split across databases — expensive
   8  a different datastore     when the shape was wrong all along

1. Indexes

An index is a sorted structure that turns “scan every row” into “walk down a tree”. On a million rows that is the difference between a million comparisons and about twenty.

The part worth understanding is column order in a composite index, because it is where most index problems actually live. StayHub’s outbox has one index, and its shape is derived directly from the query it has to serve:

-- the query:  WHERE status = 'PENDING' AND available_at <= now()
--             ORDER BY available_at
CREATE INDEX ix_outbox_pending ON outbox (status, available_at);

Equality column first, then the range-and-sort column. Postgres seeks straight to the PENDING rows and then walks them already in available_at order, so there is no sort step and the cost does not grow as millions of DONE rows pile up behind them.

Reverse the two and the index can no longer satisfy the ORDER BY without sorting. Index only status and every poll re-sorts every pending row. The rule generalises: equality columns first, then the range or sort column.

Indexes are not free

Each one is a second copy of its columns that must be updated on every insert, update and delete. Six indexes on a table means a write does seven writes.

The most common waste is a redundant index, and it arrives by accident. StayHub’s first outbox migration created two:

CREATE INDEX ix_outbox_pending ON outbox (status, available_at);
CREATE INDEX ix_outbox_status  ON outbox (status);              -- redundant

A B-tree on (a, b) already answers every query a lone index on (a) would, because it is sorted by a first. The second index costs write throughput and disk for nothing. It appeared because the model declared index=True on the column and a composite index, and the schema autogenerator faithfully created both — which is exactly how redundant indexes get into real systems.

Two more worth knowing. A partial index covers only some rows, which is ideal for a queue where the pending set is minuscule:

CREATE INDEX ix_outbox_pending ON outbox (status, available_at)
    WHERE status = 'PENDING';

And a covering index includes every column a query needs, so the answer comes from the index without touching the table at all.

2. Query patterns

The most common database performance problem is not a missing index. It is issuing hundreds of correct, fast, indexed queries where one would do.

   N+1:   SELECT * FROM properties LIMIT 20;          1 query
          for each: SELECT * FROM images WHERE ...   20 queries
                    SELECT * FROM amenities ...      20 queries
                    SELECT * FROM users WHERE id=... 20 queries
                                                     ── 61 queries
   fixed: eager-load the relationships                ── 4 queries

Each of those 61 is a network round trip — and from the latency table, a datacenter round trip costs more than reading a megabyte from memory. The number of calls matters more than the size of each.

N+1 is insidious because it is invisible in development. StayHub’s seed database holds 12 listings; 61 fast queries against 12 rows is imperceptible. The same code against a page of 100 results in production is 301 queries.

The fix is eager loading — telling the ORM to fetch the relationships up front. The detection is easier than the fix: count queries per request in development and fail a test when the count is unexpected.

Reading the query plan

Everything above is guesswork until you ask the database what it is actually doing. EXPLAIN ANALYZE is the tool, and you need to recognise about four things in its output:

   Seq Scan on bookings  (cost=0.00..18334.00 rows=1000000)
   ^^^^^^^^ reading every row — fine on 100 rows, fatal on 10 million

   Index Scan using ix_bookings_property  (rows=12)
   ^^^^^^^^^^ found them via the index, then fetched the rows

   Index Only Scan  (rows=12)
   ^^^^^^^^^^^^^^^ answered entirely from the index — never touched the table

   Sort  (actual rows=1000000)
   ^^^^ sorting a million rows in memory or on disk;
        usually means the index order does not match the ORDER BY

Two habits make this useful rather than decorative. Run EXPLAIN ANALYZE, not plain EXPLAIN — the first executes the query and reports what really happened, while the second only reports the plan and its estimates. And compare the estimated row counts with the actual ones: when they differ by orders of magnitude the planner is working from stale statistics, and the fix is ANALYZE, not a new index.

A sequential scan is not automatically wrong, either. For a small table, or a query returning most of the rows, reading everything sequentially genuinely beats bouncing through an index. The planner knows this; the finding is a sequential scan on a large table returning few rows.

3. Connection pooling

A database connection is expensive to create and, in Postgres, is backed by an actual operating system process using several megabytes. Postgres’s default limit is 100.

   20 app servers x a pool of 20 connections each  = 400 connections
   Postgres max_connections                        = 100

   -> 300 connections refused. The application reports
      "too many clients already" and looks like a database outage.

This is a genuine ceiling that people hit while believing they have a scaling problem. The answers, in order: size each application pool deliberately rather than by default; put a connection pooler such as PgBouncer in front, which multiplexes thousands of client connections onto a few dozen real ones; and only then raise max_connections, which costs memory per connection.

Counter-intuitively, more connections is often slower. Beyond roughly the number of cores, they contend rather than parallelise.

4. A bigger machine

Deeply unfashionable and frequently correct. Vertical scaling requires no code changes, no distributed anything, and modern hardware goes remarkably far — a database server with 128 cores and a terabyte of RAM handles more than most companies ever will.

Its limits are real: there is a ceiling, resizing means a brief restart, and it is still one machine to lose. But the comparison to make is not “bigger machine versus sharding” in the abstract. It is “a week of work and a larger bill” versus “a quarter of engineering, a permanent increase in complexity, and losing cross-shard transactions forever.”

Take the bigger machine. Do it twice. Then talk about sharding.

5. Read replicas

The first real distribution step, and the one with the best ratio of benefit to cost — because most systems read far more than they write.

                 writes
   [ app ] ─────────────> [ PRIMARY ]
      │                        │  streaming replication (async)
      │                        ├──> [ replica 1 ] ─┐
      └──── reads ─────────────┴──> [ replica 2 ] ─┴─> reads

The primary takes every write and streams changes to replicas that serve reads. Adding replicas scales reads nearly linearly. It does nothing at all for writes, which is the thing to say out loud, because it is the limitation that eventually forces sharding.

Replication lag is a correctness problem

Replication is asynchronous, so a replica is always slightly behind — milliseconds normally, seconds under load, minutes if something is wrong.

   t=0     user updates their profile      -> PRIMARY
   t=0.01  page reloads, reads a replica    -> replica is 200ms behind
                                            -> the OLD name comes back

   The user sees their change vanish. They try again.

This is not a performance issue; it is a bug users report and engineers cannot reproduce, because it depends on which replica answered and how loaded it was. It has a name — read-your-writes — and it gets covered properly in the consistency post.

The practical answers: route a user’s reads to the primary for a few seconds after they write; or send reads that must be current to the primary by design. StayHub does the latter implicitly — every booking decision reads the primary, because availability must never be answered from a stale copy.

What replicas also buy you

Worth mentioning because it is often the stronger justification: a replica is a warm standby. If the primary dies, one can be promoted, which turns “restore from backup over several hours” into “fail over in under a minute”.

They are also where you point analytics, so an accidental full-table scan by a reporting query cannot slow down the site.

6. Partitioning

One table split into several physical pieces within the same database. Transactions, joins and foreign keys all still work, which makes it dramatically cheaper than sharding.

CREATE TABLE bookings (
    id          BIGSERIAL,
    property_id BIGINT NOT NULL,
    check_in    DATE   NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE bookings_2026_q1 PARTITION OF bookings
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE bookings_2026_q2 PARTITION OF bookings
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

Two payoffs. Queries filtered by the partition key touch only the relevant partitions (“partition pruning”), so a query for last month reads one partition instead of five years of history. And deleting old data becomes DROP TABLE bookings_2019_q1 — instant, rather than a DELETE that rewrites millions of rows and leaves the table bloated.

That second point is the underrated one. Time-series data that must be retained for a fixed window is the clearest possible case for partitioning, and the reason is data removal, not query speed.

7. Sharding

Splitting data across separate databases, each holding a subset. This is the step that has no ceiling and a very high cost.

   shard_for(user_id) = hash(user_id) % 4

   users 1, 5, 9  ──> [ DB 0 ]
   users 2, 6, 10 ──> [ DB 1 ]
   users 3, 7, 11 ──> [ DB 2 ]
   users 4, 8, 12 ──> [ DB 3 ]

   4x the write capacity. And no query can span two of them.

What you give up

These are not inconveniences; they are the reason to postpone this step as long as possible.

• Cross-shard joins are gone. Data that lives on different shards has to be assembled in application code.
• Cross-shard transactions are gone. “Move money from A to B” stops being atomic and becomes a saga with compensating actions.
• Globally unique ids need solving. Auto-increment collides across shards — see the unique id post.
• Every query needs the shard key, or it fans out to all shards and you have built a slow, expensive version of a single database.
• Operations multiply. Backups, migrations, upgrades and monitoring now happen N times.

Choosing the key

The decision you will live with, because changing it later means moving all the data.

The test is simple: can your most common query be answered from one shard? If a booking system shards by property_id, then “this property’s calendar” is one shard and “my bookings” is all of them. Shard by user_id and it is the reverse. You have to pick which query gets to be fast.

Hot shards, and resharding

Even distribution of keys is not even distribution of load. A social network sharded by user id puts a celebrity with fifty million followers on one shard, and that shard is now the whole problem.

And when four shards become eight, hash(key) % 4 becomes hash(key) % 8 — which relocates roughly every row. Consistent hashing exists to fix exactly this: keys and shards are placed on a ring, and adding a shard moves only the keys in one arc, which is roughly 1/N of the data rather than all of it.

The other common answer is to over-provision logical shards from the start — create 1,024 of them and map many onto each physical machine. Growing then means moving logical shards between machines, with no rehashing at all.

Moving to a shard without downtime

Worth having an answer for, because “we shard” invites “how do you get there?” — and the honest answer is that it is a migration, not a deployment.

   1  add the shard key to the schema, backfill it       (no behaviour change)
   2  route reads AND writes through a routing layer     (still one database)
   3  stand up the new shards, replicate into them
   4  dual-write: every write goes to old AND new
   5  verify: compare the two continuously
   6  shift reads to the new shards, a percentage at a time
   7  stop writing to the old one
   8  decommission

   Every step is reversible until step 7. That is the point of the order.

The two steps people skip are 2 and 5. Introducing the routing layer while there is still only one database means the risky change (routing) and the risky change (splitting the data) do not happen simultaneously. And continuous verification is the only way to discover that some write path nobody remembered is still writing to the old place.

8. A different datastore

Sometimes the answer is that the data was never relational-shaped. Adding a specialised store alongside Postgres — not instead of it — is often better than scaling Postgres into a job it is bad at.

StayHub does exactly this for search: Postgres remains the source of truth, and Elasticsearch holds a derived copy for the one query pattern SQL handles badly.

    def _sync(self, prop: Property) -> None:
        indexed = indexer.index_property(prop)

The property that makes it safe is that the index is derived and disposable. It can be rebuilt from Postgres at any time, so a divergence is a repair job rather than data loss. The cost is the dual-write problem, which is what the next post is about.

Two things that are not scaling but look like it

Both come up constantly and neither is on the ladder, because they are about the data rather than the machines.

Denormalisation

Storing the same value in more than one place to avoid a join. A listing row carrying rating_average and rating_count rather than computing them from the reviews table on every read:

    rating_average: Mapped[Decimal] = mapped_column(Numeric(3, 2), default=Decimal("0"), nullable=False)
    rating_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)

The read becomes free and the write becomes a problem: every new review has to update the listing too, and the two can now disagree. That is the trade, in one sentence — denormalisation moves work from read time to write time and buys it with the risk of inconsistency.

It is the right call when reads massively outnumber writes and the derived value is reconstructible, which is the case here: if the average ever drifts, it can be recomputed from the reviews. Denormalised data you cannot rebuild is a much worse bargain.

Soft deletes

Flagging rows rather than removing them. StayHub does this because a booking references a property and a user forever, and deleting either would leave the booking pointing at nothing.

The scaling consequence is the one people miss: deleted rows still cost you. They occupy pages, they sit in indexes, and every single query must remember to exclude them. Miss one WHERE deleted = false and you have leaked deleted data into a response — which is a correctness bug, not a performance one.

At volume the answer is to move genuinely dead rows to an archive table on a schedule, so the hot table stays small, and to use partial indexes that cover only the live rows.

The counters problem

A specific denormalisation worth naming, because it is where write contention usually shows up first. A view counter, a like count, an inventory level — a single row that many requests update at once.

   1,000 requests/sec all doing:
       UPDATE posts SET views = views + 1 WHERE id = 42;

   Every one takes a row lock on the SAME row. They serialise.
   Throughput is now "how fast can one row be updated", not 1,000/sec.

Row-level locking is doing exactly its job here, and the job is the bottleneck. There is no index to add.

Three standard escapes. Batch in memory and flush periodically, accepting that the count is slightly behind and that a crash loses a few. Shard the counter into N rows and sum them on read, so writes spread across N locks. Or move it out of the relational database entirely — Redis increments are atomic and cost nothing, and a view count is precisely the kind of value that can be approximate and volatile.

The general principle is the useful part: contention is about how many writers touch one row, not how many rows exist. A table with a billion rows and no contention scales fine; a table with one row and a thousand writers does not.

Where most systems actually land

Being honest about this is worth more in an interview than reciting the full ladder.

   indexes + query fixes            solves ~70% of "the database is slow"
   + connection pooling             ~80%
   + a bigger machine               ~90%
   + read replicas                  ~97%
   + partitioning                   ~99%
   + sharding                       the remaining 1%

The arithmetic from the estimation post is what places you on that list. A booking system at 0.3 writes per second is in row one, and a candidate who shards it has been given the answer and not read it.

Migrations at scale

One last thing that catches teams the moment a table gets large: a schema change that is instantaneous on a development database can lock a production table for minutes.

-- Locks the whole table while it rewrites every row.
ALTER TABLE bookings ADD COLUMN notes TEXT NOT NULL DEFAULT '';

-- Three cheap steps instead. Each is safe on its own.
ALTER TABLE bookings ADD COLUMN notes TEXT;              -- metadata only
UPDATE bookings SET notes = '' WHERE notes IS NULL;      -- in batches
ALTER TABLE bookings ALTER COLUMN notes SET NOT NULL;    -- brief validation

The general rule is that a migration must be safe to run while the old code is still deployed, because during a rolling deploy both versions are running at once. That forces the expand-and-contract shape: add the new column, write to both, backfill, switch reads, then drop the old one — several deploys rather than one.

The related discipline: never edit a migration that has already been applied. Your database has run it; a colleague’s has not; the two now disagree about what the schema is and nothing detects it. Write a new migration.

Adding an index has the same trap and the same escape — CREATE INDEX CONCURRENTLY builds without blocking writes, at the cost of taking longer and needing to be retried if it fails.

The summary

• Index for the query you actually run — equality columns first, then the range or sort column — and delete the redundant ones.
• Fix N+1 before scaling anything. It is invisible on small data and catastrophic on large.
• Check the connection pool. It is a ceiling that looks like an outage.
• Vertical scaling is underrated. Twice, before considering anything distributed.
• Replicas scale reads only, and introduce lag that is a correctness problem rather than a performance one.
• Partition before sharding. Same table, one database, transactions intact.
• Shard last. Name what you lose — joins, transactions, simple ids — and pick the key by which query has to be fast.

The one-line version: almost nothing needs sharding, and everything needs indexes. The ladder exists so you can say where a given system sits on it and why — and the most impressive answer to “how would you scale this?” is frequently “I would not yet, and here is the arithmetic.”

Next: consistency, availability and CAP — the theory behind replica lag, and what it costs to have more than one copy of anything.