Databases & SQL interview questions and answers

Query-writing rounds and the storage-engine reasoning behind why a query is slow, for backend, data, and DBA roles alike.

28 published across 8 topics.

Databases & SQLA 22-minute read on how the topic fits together, before the questions.Database fundamentals58 short answers on one page, for revising rather than studying.

SQL Query Writing

4 questions

Joins, window functions, aggregation, and the analytical queries asked in a shared editor.

mediumCodingDesign

You need a work queue with priorities and you are told to build it in Postgres, not Kafka. What does the dequeue statement have to do, and what happens when ten workers run it at once?

The dequeue has to pick the highest-priority ready row, lock it, and mark it claimed in one statement, using FOR UPDATE SKIP LOCKED so ten workers each take a different row instead of queueing behind the same one. The lock only lasts as long as the transaction, so the claim must be written or the transaction held.

6 minmid, senior

mediumCodingConcept

Adding a second LEFT JOIN doubled the revenue figure on a report. Explain what happened and write the correct query.

SQL join duplicates cause double counting when two one-to-many joins multiply rows before aggregation. Aggregate each child table to one row per parent first, then join those results back to the parent. Use this sql queries answer to show the decision, trade-off, and evidence rather than a memorised definition.

4 minentry, mid, senior

mediumCodingConcept

Find me every customer with no order in the last thirty days, then tell me which ways of writing that get NULLs wrong.

Use NOT EXISTS for customers with no recent order because it expresses an anti-join and avoids NULL traps. NOT IN can return no rows if the subquery contains NULL, and LEFT JOIN becomes wrong if filters are placed in WHERE. Use this SQL answer to show the decision, trade-off, and evidence rather than a memorised definition. It also connects null semantics to the point an interviewer is testing.

4 minentry, mid, senior

mediumCodingConcept

Write a query returning the second-highest salary in each department, then tell me what it does when two people tie for the top.

To get the second highest salary per department in SQL, rank employees inside each department with DENSE_RANK and filter rank two. DENSE_RANK handles ties where ROW_NUMBER or a global MAX gives the wrong result. Use this window functions answer to show the decision, trade-off, and evidence rather than a memorised definition. It also connects sql queries to the point an interviewer is testing.

3 minentry, mid, senior

All 4 in SQL Query Writing

Indexing

4 questions

B-tree mechanics, composite-column order, covering indexes, and why an index goes unused.

hardConceptScenarioDesign

A table has forty million rows, thirty-five million of them soft-deleted, and every query for active rows has got slower. Would a partial index help, and what would you have to be careful about?

A partial index only contains rows matching its predicate, so indexing WHERE deleted_at IS NULL stores five million entries instead of forty million - smaller, shallower, and far likelier to stay cached. The catch is the planner only uses it when it can prove the query predicate implies the index predicate, and it does nothing about dead rows still in the heap.

5 minmid, senior, staff

hardConceptDesign

Three queries hit the same table with different filters. What composite indexes do you build, and how do you order the columns?

Order by how the predicate uses the column, not by cardinality: equality columns first, then one range column, then columns needed only for sorting. A range predicate stops the index seeking on everything after it, and the leftmost prefix decides which other queries the index can serve.

6 minmid, senior, staff

hardScenarioConcept

This table has fourteen indexes and writes have got slower. How do you work out which ones to drop?

Decide from evidence, not intuition: per-index scan counts read against the date the statistics were reset, redundancy judged by leading columns, and the constraints and foreign keys that need an index whether or not anything ever scans them. Then make the drop reversible.

6 minmid, senior, staff

hardScenarioConcept

A query has an index on the filtered column but the plan shows a sequential scan. Walk me through how you would diagnose it.

Work outward from the plan: confirm the index is actually usable for the predicate as written, then check whether the optimiser is choosing to ignore it because of selectivity, stale statistics, or a type or collation mismatch that makes the predicate non-sargable.

3 minmid, senior, staff

All 4 in Indexing

Transactions & Isolation

4 questions

ACID guarantees, isolation levels, the anomalies each permits, and locking versus MVCC.

hardConceptScenario

Two doctors can go off call only if at least one remains. Both check the rule at the same moment, both see two doctors on call, and both go off call. What isolation problem is this, and what actually fixes it?

This is write skew: two transactions read the same set, each writes a different row, and the pair commits a state neither would have produced alone. No row-level constraint can catch it because the invariant is a property of the set, so the fix is either serializable isolation, a lock on something both transactions must touch, or remodelling the invariant onto a single row.

6 minmid, senior, staff

hardScenarioConcept

You are getting a handful of deadlock errors an hour under load. How do you find the cause, and how do you stop them?

A deadlock is a cycle in the wait-for graph, so read the engine's own report to get both statements and the locks each held. Nearly all of them come from two code paths taking the same rows in different orders; impose one order, shorten transactions, and retry, because a deadlock is always safe to retry.

7 minmid, senior, staff

hardConceptScenario

Walk me through the transaction isolation levels and which anomalies each one permits.

Each level is defined by the anomalies it allows: READ COMMITTED permits non-repeatable reads and phantoms, PostgreSQL's REPEATABLE READ is snapshot isolation so it prevents both yet still permits write skew, and only SERIALIZABLE rules out all four.

6 minmid, senior, staff

hardScenarioDesign

A user submits the form twice and you get two identical orders. Why did the check-then-insert not prevent it, and how do you fix it in the database?

Two transactions can each run the SELECT before either commits, so neither sees the other and both inserts succeed. The fix is a serialisation point the database owns - a unique index on a natural or client-supplied key - plus a defined answer for whoever loses the race. It also connects idempotency to the point an interviewer is testing.

6 minmid, senior, staff

All 4 in Transactions & Isolation

Query Optimisation

3 questions

Reading execution plans, statistics and cardinality estimation, and diagnosing real regressions.

mediumConcept

Before you tune anything, how do you decide which queries are worth your time?

Rank by the total time a statement shape consumes across a measured window rather than by the slowest single execution, then check what that time was spent waiting on before touching the SQL. The cheap statement running ten thousand times a minute usually outranks the ten-second report.

4 minmid, senior, staff

mediumConceptScenario

Walk me through how you read a PostgreSQL execution plan.

Read the plan tree innermost-node-first, remember that EXPLAIN only estimates while EXPLAIN ANALYZE executes, multiply each node's actual rows by its loops, and treat a large gap between estimated and actual rows as the primary finding.

5 minmid, senior, staff

hardScenarioConcept

The same statement takes 20ms in psql and four seconds from the application. Where do you look?

When the same query is fast in psql but slow from the application, first separate database time from connection wait, network transfer and ORM hydration. Then compare the real application plan, bind parameters, session settings, fetch size and result volume.

6 minmid, senior, staff

All 3 in Query Optimisation

Data Modelling

3 questions

Normalisation and deliberate denormalisation, keys, constraints, and schema evolution under load.

hardDesignConcept

We sell to trade customers who each negotiate their own prices, and those prices change. Design the schema.

Give every entity a surrogate primary key but keep a unique constraint on the business identifier, and model a negotiated price as a row with a validity period rather than a mutable column - then copy the agreed price onto the order line so history cannot be rewritten.

5 minmid, senior, staff

hardDesignConcept

Half the catalogue has attributes the other half does not, and merchants add new ones weekly. How would you store them?

Decide by which attributes are filtered or constrained, not by how many exist. Anything a query filters, sorts or validates on becomes a real typed column; the open-ended remainder goes in a JSONB document with a GIN index, and entity-attribute-value is the fallback that costs you every guarantee.

7 minmid, senior, staff

mediumDesignScenario

Every table in this schema soft-deletes with a deleted_at column. A year in, what has that cost you?

Soft deletion is a modelling decision rather than a convenience: it silently breaks uniqueness, leaves foreign keys pointing at rows that are logically gone, and adds a predicate every future query has to remember. Model the lifecycle explicitly instead.

5 minmid, senior, staff

All 3 in Data Modelling

NoSQL Stores

4 questions

Document, key-value, wide-column, and graph stores, with honest selection criteria.

mediumDesignConcept

You are building a user-profile service. Key-value store or document database: what decides it?

It is decided by everything besides fetching a profile by user id. If every read is by id and every write replaces the whole profile, a key-value store is enough. Login by email, unique emails and field-level updates push towards a document database, or towards index items you maintain yourself.

4 minmid, senior

mediumDesign

You have to pick a database in the first five minutes of a design round. Which three questions decide it?

Database selection in a design round rests on three questions: what the hot path reads and writes, by which key; which facts must never be wrong; and how much data and traffic arrive at peak, derived as numbers. Then commit to one store in a sentence, with the reason and the fact that would change it.

4 minmid, senior, staff

hardDesignConcept

How do you choose between a document store, a key-value store, a wide-column store and a graph database for a new service?

Compare key-value, document, wide-column and graph databases by access pattern: known-key lookups, aggregate documents, partition-local range reads or unbounded relationship traversal. The right NoSQL choice follows the query shape, not a generic scalability claim. It also connects cassandra to the point an interviewer is testing.

5 minmid, senior, staff, lead

hardScenarioConcept

Your service writes a record, reads it straight back, and sometimes gets the old value. What is happening and what do you change?

An acknowledged write is only guaranteed on the replicas that acknowledged it, so a read served elsewhere can legitimately return the previous version. Locate the staleness - replica, secondary index or cache - then buy read-your-writes for that operation only, and price what it costs during a partition.

5 minmid, senior, staff

All 4 in NoSQL Stores

PostgreSQL Internals

2 questions

MVCC and vacuum, WAL, connection handling, and the operational depth senior roles expect.

hardConcept

Monitoring says the age of the oldest transaction ID on this PostgreSQL database keeps climbing. What is it measuring, and what happens if it is ignored?

PostgreSQL compares 32-bit transaction IDs, so old rows must be frozen before the counter laps them. A rising age means vacuum is not freezing fast enough, and if it keeps rising the server eventually refuses to assign new transaction IDs and stops accepting writes.

5 minmid, senior, staff

hardConceptScenario

In PostgreSQL, what does an UPDATE do to a row under MVCC, and why does a long-running transaction make that expensive?

An UPDATE writes a new row version and leaves the old one visible to older snapshots; vacuum reclaims it only once no snapshot can see it. A long transaction pins that horizon, so dead tuples accumulate as bloat and freezing stalls, which is what leads to transaction-id wraparound.

6 minsenior, staff, lead

DBA & Operations

4 questions

Backup and recovery, replication and failover, upgrades, capacity planning, and incident response.

hardScenarioDesign

Your team says the database is backed up nightly. How would you establish whether you could really restore it?

A backup job that exits zero is a belief, not a control. Establishing recoverability means agreeing an RPO and RTO, then rehearsing a restore onto clean infrastructure, measuring how long it took and verifying the result — including everything the dump does not contain.

5 minmid, senior, staff

hardScenarioDesign

You are moving a sharded datastore to Cassandra with no maintenance window. How do you cut over, and how do you know both copies agree before you do?

Replicate from the source's change log rather than dual-writing from application code, backfill with each row's original write timestamp so history can never overwrite live data, then run continuous range checksums and shadow reads until divergence is understood rather than merely small. The read flip and the write flip are separate steps, each reversible.

6 minsenior, staff, lead

hardScenarioDesign

The table is 800GB and the shard key turned out wrong. How do you reshard while writes continue?

Changing a shard key moves rows between shards, so it is a copy-and-delete across nodes rather than an UPDATE. Put a bucket-to-shard directory in front of the data, move one bucket at a time behind a freeze measured in milliseconds, verify each bucket before flipping its ownership, and keep every bucket independently reversible.

5 minsenior, staff, lead

hardScenarioDesign

You need to migrate the schema of a 400-million-row table on a live system. Talk me through the plan, the backup you would want first, and how the connection pool affects it.

Every step is chosen to hold weak locks briefly - add the constraint invalid and validate separately, index concurrently, backfill in committed batches - behind a restore you have rehearsed and timed, and a pool small enough that one blocked DDL statement does not exhaust it.

7 minsenior, staff, lead

All 4 in DBA & Operations