Database Interview
Every question shows beginner, strong and expert answers, green and red flags, follow-ups and a scenario. The goal is judgment: does the candidate reach for evidence and name the tradeoff?
SQLBeginner
What does WHERE x <> 'a' return for rows where x is NULL?
Explain NULL in SQL and give an example where it silently produces the wrong result.
SQLIntermediate
Why is this SUM double the real number?
A revenue query joins orders to order_items and sums orders.total, and the number is far too high. Explain.
SQLIntermediate
Why did this LEFT JOIN behave like an INNER JOIN?
Someone put a condition on the right table in WHERE and the outer join stopped returning unmatched rows. Explain.
SQLIntermediate
EXISTS vs IN vs NOT IN
When would you use each, and which is dangerous?
SQLIntermediate
ROW_NUMBER vs RANK vs DENSE_RANK
Explain the difference and how each affects a "top 3" query with ties.
ModelingIntermediate
How do you start a schema design?
You are given requirements for a new feature. Walk me through getting to tables and indexes.
ModelingIntermediate
Natural key or surrogate key?
Would you use email as a primary key? UUID or bigint?
ModelingIntermediate
Explain normalization to 3NF with an example
What problem does normalization solve, and what is 3NF?
ModelingAdvanced
When is denormalization the right call?
Give an example of duplicating data on purpose and how you keep it correct.
ModelingIntermediate
Embed or reference in a document database?
How do you decide whether to embed a relationship or reference it by id?
IndexesBeginner
When should you add an index?
Walk me through deciding whether to add an index.
IndexesIntermediate
The index exists but the query does not use it. Why?
Give the common reasons a planner ignores an index.
IndexesIntermediate
How do you order columns in a composite index?
Given a query, how do you decide the column order in a multi-column index?
IndexesAdvanced
When is a partial index the right tool?
Give a case where a partial index beats a full one.
OptimizationIntermediate
A PostgreSQL query is slow. What do you do?
Walk me through diagnosing a slow query.
OptimizationBeginner
What is the N+1 query problem?
Explain N+1 and how you would fix it.
OptimizationIntermediate
What happens when you run SELECT * FROM users WHERE email = ?
Trace the query from the client to the result.
OptimizationAdvanced
Nested loop, hash join, merge join — when does each win?
Explain the three join algorithms and what makes the planner pick each.
TransactionsBeginner
Explain ACID
What do the four letters mean, and which mechanism provides each?
TransactionsAdvanced
Which isolation level would you choose?
How do you pick an isolation level, and what does Serializable cost?
ConcurrencyIntermediate
What is a lost update and how do you prevent it?
Two requests increment a counter and one increment vanishes. Explain and fix.
ConcurrencyAdvanced
What is write skew?
Explain write skew and why snapshot isolation does not prevent it.
ConcurrencyAdvanced
How does MVCC work, and why does VACUUM exist?
Explain multi-version concurrency control and its maintenance cost.
ConcurrencyIntermediate
What causes a deadlock and how do you prevent it?
Explain deadlocks and your prevention strategy.
ConcurrencyIntermediate
Optimistic vs pessimistic locking
When do you use each?
NoSQLIntermediate
SQL or NoSQL — how do you decide?
A greenfield project asks whether to use PostgreSQL or a NoSQL store. How do you decide?
NoSQLAdvanced
When is more than one database justified?
What does polyglot persistence cost, and when is it worth it?
NoSQLAdvanced
When is a graph database the right tool?
When would you reach for Neo4j over Postgres?
NoSQLIntermediate
What is Redis good for beyond caching?
Name uses of Redis that are not caching, with the data structure each relies on.
NoSQLIntermediate
When should you NOT use Redis?
What are Redis’s limits, and what should never live only in it?
NoSQLIntermediate
JSONB column or real columns?
When do you store data as JSONB versus typed columns in Postgres?
NoSQLBeginner
Which types do you reach for, and which do you avoid?
Money, timestamps, ids, enums — what types and why?
ScalingIntermediate
How do you scale a database?
Traffic is growing. In what order do you scale, and why that order?
ScalingBeginner
Why does a user see their own change revert?
After adding read replicas, users report seeing old data right after saving. Explain.
ScalingAdvanced
How do you choose a shard key?
What makes a good shard key, and what is a hot shard?
ScalingIntermediate
Partitioning vs sharding — what is the difference?
Explain both and when each is appropriate.
ProductionIntermediate
Why "too many connections", and how do you fix it?
The app fails with too-many-clients but the database CPU is idle. Explain.
DistributedAdvanced
Explain the CAP theorem
What does CAP actually say, and what is the real everyday tradeoff?
DistributedAdvanced
What does W + R > N give you?
Explain quorum reads and writes.
ProductionIntermediate
Which caching pattern would you use?
Cache-aside, read-through, write-through, write-behind — how do you choose?
ProductionAdvanced
What is a cache stampede and how do you prevent it?
A deploy briefly takes the database down. Explain and fix.
ProductionAdvanced
When the database changes, how does the cache know?
Compare cache invalidation strategies.
NoSQLAdvanced
Dedicated vector database or Postgres + pgvector?
How do you decide where embeddings live?
NoSQLAdvanced
How does approximate nearest neighbour trade off recall and speed?
Explain HNSW and the knobs that trade recall for latency.