Roadmaps / Databases / Interview topics
Reference
Interview topics
Use these for backend, data and database roles. Each follow-up is the question a good interviewer asks next. The Staff line is what lifts the answer.
Phase 1
| Question | Follow-up | A Staff answer adds |
|---|---|---|
| How does an index speed up a query? | When does it not? | The B-tree shape, selectivity and a plan that shows the planner's choice |
| How do you read a query plan? | What if the estimates are wrong? | Estimated against actual rows, statistics and a fix with a measurement |
| How do you store money? | What about currencies? | Integers in minor units, the currency column and rounding rules |
| How do you design for an access pattern? | When do you denormalise? | The queries first, the cost on writes and a rule for keeping copies correct |
System design prompt (20 minutes): the schema for a payments ledger. Outline: requirements and rules (3 min), tables and keys (5), indexes for the main queries (5), growth (4), failure cases (3).
Phase 2
| Question | Follow-up | A Staff answer adds |
|---|---|---|
| What are the isolation levels? | Which anomaly does each allow? | A concrete reproduction and the cost of the stronger level |
| How do you prevent a double spend? | What if the client retries? | A conditional update, an idempotency key and a stress test |
| What is a deadlock and how do you handle it? | How do you avoid it? | A lock order, short transactions and the log line that shows one |
| What is MVCC? | What are its costs? | Snapshots, old versions, vacuum and long transactions as a cause of bloat |
System design prompt (20 minutes): seat booking with no double sales. Outline: rules (3 min), the update statement (5), concurrency and retries (5), failure and timeouts (4), testing (3).
Phase 3
| Question | Follow-up | A Staff answer adds |
|---|---|---|
| How do you find the slowest query? | Total time or one slow call? | pg_stat_statements, percentiles and the query that costs the most overall |
| When do you partition? | What does it cost? | The pruning benefit, the maintenance work and a measured example |
| How do you use a cache safely? | What breaks? | An invalidation rule, a stale-read test and a plan for a cold cache |
| When do you choose another database? | How do you decide? | The workload, a measured comparison and the cost of running two systems |
System design prompt (20 minutes): a read-heavy product catalogue with 50 million rows. Outline: numbers (3 min), primary and replicas (5), cache (5), search and analytics side paths (4), consistency (3).
Phase 4
| Question | Follow-up | A Staff answer adds |
|---|---|---|
| How do you back up and restore a database? | How do you know it works? | Point-in-time recovery, a timed restore drill and a monitored result |
| What happens when the primary fails? | How do you avoid split brain? | Promotion steps, fencing, measured loss and a rehearsal schedule |
| How do you change a large table safely? | What if it must stay online? | Expand and contract steps, batches, a lock budget and a copy rehearsal |
| How do you secure a database? | What about an application breach? | Least privilege, row-level security where it fits and audit logs |
System design prompt (20 minutes): disaster recovery for a payments database. Outline: goals (3 min), backups and archiving (5), failover (5), drills and checks (4), cost (3).