knaxuslearn · build · ship

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

QuestionFollow-upA 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

QuestionFollow-upA 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

QuestionFollow-upA 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

QuestionFollow-upA 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).