Database Concurrency & Scaling interview question · Most asked
Database CPU is at 85%. How do you decide what to optimise before buying a bigger server?
Summary
Rank statements by total time (calls × mean) using pg_stat_statements or performance_schema digests, fix the top entries (indexes, N+1, pagination), and verify with EXPLAIN ANALYZE — usually far cheaper than scaling hardware.
Longer Answer
The slowest single query is rarely the biggest load; a 30 ms query called millions of times usually is. Look at calls per request too (N+1), and compare estimated vs actual rows in plans for statistics problems. Then consider caching and read replicas for what remains.
Key Points
- Optimise by total time
- pg_stat_statements / performance_schema
- Verify with EXPLAIN ANALYZE
- Hardware last
Deep dive: Finding the Queries That Matter
More most-asked Database Concurrency & Scaling questions
- How do you prevent overselling in a flash sale with 50,000 buyers in the first minute?
- After a deploy, logs show hundreds of 'deadlock detected' errors per hour on checkout. Walk through how you'd investigate.
- Two customers buy the last item at the same moment and both orders are confirmed. What happened, and how do you prevent it?
- Your service throws 'connection pool exhausted' errors but database CPU is only 20%. What's going on?
- Why shouldn't you call a payment API inside a database transaction?
- What's the difference between Read Committed and Repeatable Read in practice?
- A user saves their profile and the next page shows the old data. Reads go to replicas. How do you fix it?
- How do you choose a shard key for a multi-tenant SaaS?