Scaling a relational database

Climb the ladder one rung at a time

"The database can't keep up" has many possible fixes, and they differ enormously in cost. Sharding (05.01 Sharding: range, hash, directory) gets the attention, but it's the most expensive by far: every query, migration and feature has to know about it forever. Most databases that "need sharding" need one of the cheaper rungs first.

1. Tune what you have

The cheapest capacity is the capacity you're wasting. Look at the slowest and most frequent queries (04.03 Reading a query plan): a missing index, an N+1 loop issuing 500 queries per page, or a report running every minute instead of every hour can be a large share of the load. A few query fixes often buy more headroom than any new hardware.

2. Pool connections

In Postgres, each client connection is a separate server process with its own memory. Thousands of app processes, each holding a few connections, can exhaust the database before any query is slow. A connection pooler such as PgBouncer sits between them: thousands of app connections share a few dozen real database connections, each handed out only for the duration of a transaction.

3. Scale up

A bigger machine: more cores, more memory to cache the working set, faster disks (02.02 Split the database; scale up or out). No code changes. Cloud databases make this a few minutes of downtime or a failover.

4. Offload reads

A cache for hot keys and read replicas for the rest (02.04 Read replicas and a cache, 04.07 Replication: leaders, followers and lag). This handles read-heavy systems like Snip, as long as the reads can tolerate a little lag.

5. Partition and archive

Some tables grow without end. Snip's clicks_daily gains a row per link per day, forever. Table partitioning splits one logical table into pieces by a key, usually time:

CREATE TABLE clicks_daily (code text, day date, clicks bigint, PRIMARY KEY (code, day))
  PARTITION BY RANGE (day);
CREATE TABLE clicks_daily_2026_11 PARTITION OF clicks_daily FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

Queries for "the last 30 days" touch only the recent partitions and their small indexes. Deleting a year of old data is dropping a partition, not a slow, bloating DELETE of a billion rows. Older partitions can be archived to cheaper storage. It's still one database, so nothing else in the app changes.

6. Split by feature

When one database serves everything, separate the parts that don't need to be together: Snip's users and billing in one database, links in another, stats in a third. This is vertical partitioning: each database gets fewer tables and less load, and can be sized and tuned for its job. The cost: no more joins or transactions across those groups, so split along boundaries where the app rarely needs them.

7. Shard

When a single table is too big or too busy for the biggest machine, split its rows across many databases by a key. That's 05.01 Sharding: range, hash, directory, and it's where Part 2 continues.

Recap

Interview questions