Scaling a database
The app is growing and the database is the first thing to struggle. CPU sits at 90% at dinner time, queries that took 5 milliseconds take 400, and someone in a meeting says "we need to shard". Sharding is the most expensive answer on the list, and it is usually the last one you need. There is a ladder, and most teams can climb it for years before reaching the top rung.
This chapter is that ladder, in the order you should climb it. The next chapters go deep on the two biggest rungs: replication and partitioning and sharding.
Your Postgres primary is at 90% CPU at peak. Before adding any new servers, name two things you would check or change first, and why.
The ladder#
Drag the load up. Each step is what a sensible team does when the step before it runs out.
Connection pooling, briefly#
This rung surprises people, so it is worth a number. Postgres starts one operating system process per connection, and each one reserves memory whether it is busy or not.
Every autoscaled app instance brings its own pool, so connections grow with the app fleet while the database stays the same size. A pooler in between holds a small number of real connections and hands them out per transaction. It is often the cheapest big win on the whole ladder.
Splitting reads from writes#
Read replicas change your application, not just your infrastructure. The code has to decide, per query, whether it can tolerate slightly old data. A product listing can. A balance shown right after a payment cannot. The replication chapter covers the lag, and the patterns for hiding it from users.
| Choice | What you gain | What you pay | Pick it when |
|---|---|---|---|
| Scale up | No code changes, no new failure modes. | Gets expensive at the top end, and there is a top end. | First, and for longer than most people expect. |
| Read replicas | Read capacity grows by adding copies. | Replication lag, and code that must choose where each read goes. | Read heavy workloads that tolerate slightly old data on most pages. |
| Sharding | Write capacity and storage grow by adding machines. | Cross shard joins and transactions, rebalancing, and much harder operations forever. | Only when writes or data size outgrow one machine. |
Plenty of large products ran on a single primary Postgres or MySQL with replicas for a very long time. Shopify, GitHub and Notion have all written publicly about how far they pushed one database, and about the work sharding took when they finally did it. That work is the reason to postpone it.
Checkpoint#
1. What is usually the cheapest first step when a database is overloaded?
2. Why do read replicas not help with a write bottleneck?
3. Autoscaling the app from 10 to 80 instances made the database slower. What is the likely cause?
- The ladder: fix queries, pool connections, scale up, read replicas, cache, then partition and shard.
- Replicas scale reads only. Writes need the data split.
- One Postgres connection is one process. Pool them.
- Postpone sharding as long as the lower rungs work.
When a database struggles, I climb a ladder rather than jump to sharding. First I find the expensive queries with pg_stat_statements or the slow query log and fix them, often with an index. Then I put a connection pooler in front, since each Postgres connection is a process and an autoscaled app opens far too many. Then I scale the machine up. For read heavy load I add read replicas, accepting replication lag and routing reads deliberately, and I cache the hottest reads. Only when writes or data size outgrow one machine do I partition and then shard, because sharding makes joins, transactions and operations harder for good.