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)
- Same video, Taking the Hardware for a Test Drive (37:49)youtube.com
- PostgreSQL docs: CREATE INDEX (UNIQUE)postgresql.org
- PostgreSQL docs: Using EXPLAINpostgresql.org