The database bottleneck

Two months after the big promotion, Friday dinners are busier than ever. Tiffin's app servers are stateless and autoscale nicely. At 8pm there are 8 of them, each at a relaxed 30% CPU. And yet p99 latency is creeping up, from 300 ms to 900 ms to, last Friday, 2.5 seconds.

Someone does the obvious thing and changes the dinner schedule to start 20 servers instead of 8. (Autoscaling on CPU would never have added them: CPU is fine.) The next Friday is worse.

That's the signature of a different bottleneck. The servers aren't working hard. They're waiting. And every one of them is waiting on the same thing: the one database they all share. This lesson is about recognising that, finding out exactly what the database is struggling with, and knowing what to try first.

The signs

You met the clue in Module 4: low CPU with high latency means the server is waiting on something. When the something is the database, you'll see a pattern like this:

Request tracing (Module 11 goes deep on this) is the quickest way to confirm it. A trace for Tiffin's menu endpoint might show:

GET /restaurants/42/menu            total 1,840 ms
  auth check (Redis)                       1 ms
  waiting for a database connection    1,120 ms
  SELECT menu_items ...                  710 ms
  build JSON                               9 ms

The app's own work is 10 ms. The other 1,830 ms is the database, or waiting for a turn to talk to it.

Why more servers made it worse

Adding app servers adds demand on the database without adding any capacity. Each new server opens more connections and sends more queries at once. Past a certain point, a database doing more things at the same time gets less done in total: queries fight over CPU, memory, disk and locks, and the database spends more time switching between them than running them.

It's like adding more cars to a jammed road. The road doesn't get wider.

Look inside the database

Once you know it's the database, the next question is what in the database. Guessing is a waste of time, because the database will tell you.

In Postgres, the pg_stat_statements extension records every distinct query with how often it runs and how long it takes. Sort by total time:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Here's what Tiffin's database showed for one Friday dinner (simplified):

Read it carefully, because the biggest problem is not the slowest query.

• The menu query takes under 3 milliseconds, yet its total time is the largest of all: 8 million calls a dinner, about 750 a second. It's also the one caching (Module 3) should be absorbing. That many calls means the cache isn't doing its job, maybe because the TTL is too short.
• The order history query at 14 ms smells like the (customer_id, created_at, id) index from Module 2 is missing or not being used. Module 2 showed how much that can matter.
• The order status query takes under a millisecond. About 10,000 customers have their order screen open at the dinner peak, and each one's app polls it every 10 seconds: about 1,000 calls a second, almost 11 million a dinner. Each call is cheap, but nearly every one asks a question whose answer hasn't changed.
• The daily report is run by the finance team's dashboard. Only 30 calls, but each takes a minute and reads millions of rows, right in the middle of dinner.

The query that hurts most is the one with the highest total time, not the highest time per call. A fast query run millions of times can cost more than a slow one run rarely.

A quick check on the scale: the first row alone is 23,500 seconds of database time over a 3-hour dinner. That's 23,500 / 10,800 ≈ 2.2 seconds of work every second, so more than two CPU cores do nothing all evening but build menus the cache should have served. The top four rows together keep about five of the database's 32 cores busy. The rest of its load comes from the long tail of queries below the top five, and from juggling hundreds of connections at once, which the next lesson deals with.

Four ways a database runs out

Like an app server, a database can run out of different things. Each looks different, and each has different fixes.

Memory and disk

Postgres keeps recently used data pages in memory. A read from memory takes microseconds. A read from SSD takes about 100 microseconds or more, and every query that misses waits for it. When the working set fits in memory, the cache hit ratio is above 99%. When the data outgrows memory, it drops, and latency follows.

For Tiffin: orders from the last week are read constantly, older ones rarely. While a week of orders fits in memory, everything is fast. Module 9 covers what to do when even the recent data gets too big.

Locks

Say the order transaction also runs UPDATE city_stats SET orders_today = orders_today + 1 WHERE city_id = 1. Now every order in the city needs the lock on that one row, and holds it until its transaction commits. If the order transaction takes 50 ms (it also writes the items and payment row, and waits for the commit to reach disk), only about 20 orders a second can get through, however many cores the database has. One hot row turns parallel work into a single-file queue. Database CPU looks fine, but queries pile up waiting. Module 12 returns to counters like this.

Reads and writes

One more number shapes everything in this module: how much of the database's work is reading, and how much is writing.

For Tiffin, counting queries at dinner:

reads:   ~6,200 queries/s  (menus, order status, history, restaurant lists)
writes:  ~1,500 queries/s  (orders, status changes, rider locations)
share of reads: 6,200 / 7,700 ≈ 80%

That's typical. Most apps read far more than they write. And it matters, because reads and writes scale very differently. Reads can be answered from a copy of the data, so you can add copies. Writes must all go to one place in a normal setup, so copies don't help them. The read replicas lesson builds on exactly this.

The order of fixes

There's a well-worn order to work through, cheapest and safest first:

1. Fix the queries. Add missing indexes, stop scanning rows you don't need, kill N+1 query loops. Free, and often the biggest win. (Module 2)
2. Cache what's read often. Menus and restaurant lists barely change. (Module 3)
3. Pool connections. Stop hundreds of app processes each holding their own connections. (Next lesson)
4. Move heavy work off the busy hours, or off the main database. That finance report doesn't need to run on the primary at 8pm.
5. Scale up the database. More cores and memory. Simple, and it goes a long way. (Module 4 made the case)
6. Add read replicas for the reads that are left. (This module)
7. Archive, partition, and eventually shard. For data that has outgrown one machine. (Module 9)

Each step down the list costs more effort and adds more moving parts. Plenty of companies never get past step 6.

For Tiffin, the plan from that pg_stat_statements output is: index the order history query, fix the menu cache TTL, move the finance report to run at 4am, and look at whether order status needs to be polled from the database at all (Module 6 has a better answer). Then pool the connections, which is the next lesson.