The 30 second answer
Because every redirect runs its own insert transaction, and each commit waits for durable storage over the network, about a millisecond. The disk had plenty of IOPS and bandwidth left, so a faster SSD wouldn't help; the limit was round trips and commit latency per row. The fix is batching, but not inside the web processes, because a crash or deploy would lose the batch.
What the metrics showed at about 50,000 requests/s.
- Database disk: about 900 writes per second against 8,000 provisioned IOPS, and about 37 MB/s against 1 GB/s of throughput.
- Disk reads: zero. The links table had only about 50,000 rows, so Postgres kept it in memory.
- Database CPU: about 20% idle.
So neither IOPS nor bandwidth was the limit. The speaker's diagnosis: each redirect is a separate database transaction that has to reach the disk, the disk is network-attached storage, and his estimate of that write latency is about one millisecond. Paying for a faster volume wouldn't raise throughput.
The fix: batch writes. Four redirects mean four round trips and four commits. Holding the rows and writing them in one transaction means one round trip and one commit for the whole batch.
Why not batch inside each web process. The 48 app processes crash, run out of memory and restart on every deploy. A batch held in their RAM disappears with them, and the requirement says view rows must not be lost. That leads to a durable buffer between the web tier and Postgres (next question).
Alternatives worth naming. PostgreSQL's asynchronous commit lets a transaction return before its WAL is flushed, at the documented cost that the most recent transactions may be lost if the database crashes. That trades away the durability this product needs, so batching is the safer lever here.
Alternate interview scenarios
"We bought a faster disk and throughput didn't change. Why?"
Because throughput was bound by per-commit latency, not by IOPS or MB/s. Look at writes per second against the provisioned limit; if you're far below it, fewer and larger transactions help, faster storage doesn't.
Sources (4)
- Same video, Taking the Hardware for a Test Drive (37:49)youtube.com
- Same video, Moving to Our Production App (1:07:47)youtube.com
- PostgreSQL docs: Asynchronous commitpostgresql.org
- Amazon EBS volume types (IOPS and throughput limits)docs.aws.amazon.com