Easy system-design scaling

The database CPU is pinned at 5,000 requests/s. What's the first fix, and does switching from Node.js to Go help?

The 30 second answer

The first fix is an index on the short code. Without it, every redirect scans the links table, and that's what pinned the database CPU. A unique index took the lookup to a fraction of the time and lifted throughput about ten times, to roughly 50,000 per second. Switching to Go only gave about 60,000 against 53,000, because the app was I/O-bound, not CPU-bound.

The index. The redirect query looks up the urls row by its code. EXPLAIN ANALYZE showed about 2 ms per lookup without an index. After CREATE UNIQUE INDEX on the code column, the same plan ran in a fraction of that time. Under load the database went from 0% idle CPU to about 20% idle, and throughput rose from about 5,000 to about 50,000 requests per second. That is ten times, without buying a bigger database.

Why not just change the stack. At 50,000 the app server was still about 70% idle, so the language wasn't the limit. The Node.js app ran as 48 processes, one per core. The Go version, a single process using all cores and much less RAM, reached about 60,000 against about 53,000 for Node.js in the same test. The speaker's conclusion: the work is I/O-bound, so a faster language doesn't move the bottleneck.

What a senior candidate adds. A unique index also enforces that a code maps to one link, which a URL shortener needs anyway. And the lesson is about order: find what is saturated before rewriting anything.

Alternate interview scenarios

"Your database CPU is at 100% on a simple key lookup. What do you check first?"

The plan for that query. A sequential scan on a hot lookup column means a missing index; a unique B-tree index on the key turns each lookup into an index scan.

Sources (3)
  1. Same video, Taking the Hardware for a Test Drive (37:49)youtube.com
  2. PostgreSQL docs: CREATE INDEX (UNIQUE)postgresql.org
  3. PostgreSQL docs: Using EXPLAINpostgresql.org