SQL and Database Interview Questions

Model answer

An index is a separate, sorted structure (usually a B-tree) that maps column values to the rows that hold them. Instead of scanning every row, the database walks the tree in a few steps, like using the index at the back of a book.

The costs: every INSERT, UPDATE of an indexed column and DELETE must update each index too, so writes get slower; indexes take disk and memory; and the planner has more options to weigh. An index that no query uses is pure cost.

Model answer

Add a composite index on (user_id, status, created_at). The single-column index finds the user's rows, but then the database filters them by status and sorts them in memory before taking 20.

Column order matters because a B-tree is sorted by the first column, then the second within it, and so on. Put equality columns first (user_id, status) and the sort or range column last. Then all matching rows sit next to each other already in created_at order, and the database reads the first 20 and stops.

The reversed order (created_at, user_id, status) would not help this query, because the matching rows are scattered through the date order. This is the leftmost-prefix rule.

Model answer

• A function on the column: WHERE LOWER(email) = ? cannot use an index on email. Index the expression, or store the value normalised.
• Type mismatch: comparing a text column to a number forces a cast on every row.
• A leading wildcard: LIKE '%gmail.com' cannot walk a B-tree; LIKE 'abc%' can.
• Low selectivity: if the filter matches a large share of the table, scanning really is cheaper than thousands of random index lookups, and the planner is right.
• Stale statistics: the planner thinks the table is small. Run ANALYZE.
• OR across columns, or a column that is not the leftmost in the composite index.

Always check with EXPLAIN ANALYZE on production-sized data; a test table with 100 rows will be scanned whatever you do.

Model answer

Measure first. Postgres records how often each index is read in pg_stat_user_indexes; an index with idx_scan near zero after weeks of traffic is a candidate to drop. Look for duplicates too: an index on (a) is redundant next to one on (a, b).

Make the ones you keep earn their place: one well-ordered composite index can serve several queries; a partial index (WHERE status = 'pending') indexes only the rows queries ask for; a covering index (INCLUDE) answers a hot query without touching the table.

Then change the shape of the problem: move heavy analytical queries to a read replica or a warehouse with its own indexes, batch inserts, and for append-only logs consider time partitioning so each partition's indexes stay small.

Model answer

• Atomicity: all of a transaction happens or none of it. A transfer debits one account and credits another; a crash in between undoes the debit.
• Consistency: a transaction moves the database from one valid state to another, respecting constraints such as "balance must not be negative" or foreign keys.
• Isolation: transactions running at the same time do not see each other's half-done work, as if they ran one after another (to a degree set by the isolation level).
• Durability: once committed, it survives a crash, because it was written to the write-ahead log on disk before the commit returned.

Model answer

This is a lost update: both read 1000, both write 200, and one withdrawal vanishes, leaving the account effectively at -600. It happens under the default isolation level of Postgres (read committed) when the code reads, decides in the application, then writes.

Fixes, simplest first:

• Do it in one atomic statement: UPDATE accounts SET balance = balance - 800 WHERE id = ? AND balance >= 800, and check that one row changed.
• Lock the row while deciding: SELECT balance FROM accounts WHERE id = ? FOR UPDATE, so the second transaction waits for the first.
• Optimistic locking: keep a version column, UPDATE ... WHERE id = ? AND version = ?, and retry if no row changed.

Model answer

Read committed: you never see uncommitted data (no dirty reads), but reading the same row twice in one transaction can give different answers (non-repeatable read), and new matching rows can appear (phantoms). Default in Postgres and Oracle.

Repeatable read: rows you have read will not change under you. In the SQL standard phantoms are still allowed; Postgres implements it as snapshot isolation, which prevents phantoms but allows write skew: two doctors each check "someone else is on call", both go off call, and nobody is. Default in MySQL InnoDB.

Serializable: the result is as if transactions ran one at a time; write skew is caught too. Postgres does it with SSI and aborts one transaction with a serialization error, so the application must retry.

Higher levels mean more waiting or more aborted transactions, which is why most systems stay at read committed and fix the specific races with locks or atomic updates.

Model answer

Transfer A to B locks A then B. At the same time, a transfer B to A locks B then A. Each holds one lock and waits for the other, forever. The database detects the cycle and kills one transaction with a deadlock error.

Fixes:

• Lock in a fixed order: always lock the lower account id first, whatever the direction, so a cycle cannot form.
• Keep transactions short: no network calls or user waits while holding locks.
• Retry on the deadlock error, with a small backoff: deadlocks can be made rare, rarely impossible.

Model answer

Loading a list takes one query, then the code loops over the results and runs one more query per item: 1 query for 50 posts, then 50 queries for their authors. 51 round trips where 1 or 2 would do.

It hides well because each query is fast and ORMs make it invisible: post.author looks like a property but runs a query when touched. It shows up as a page that gets slower with every row added.

Model answer

A JOIN fetches both in one query: SELECT p.*, u.name FROM posts p JOIN users u ON u.id = p.author_id ORDER BY p.created_at DESC LIMIT 50.

A batched second query: fetch the posts, collect the distinct author ids, then SELECT * FROM users WHERE id IN (...) once and match them up in memory. Two queries in total, whatever the page size.

ORMs offer both as eager loading: include in Prisma or Sequelize, JOIN FETCH or @EntityGraph in JPA, select_related and prefetch_related in Django. Joins suit one-to-one; the batched query suits one-to-many, where a join repeats the parent row for every child.

Model answer

Use a DataLoader (the pattern, or the library of that name). Each author resolver calls userLoader.load(id) instead of the database. The loader collects every load made in the same tick of the event loop, then calls one batch function with all the ids: one WHERE id IN (...) query.

It also caches within the request, so an author who wrote 10 of the posts is fetched once.

Create a new loader per request. A shared loader would serve one user's cached data to another and never notice updates.

Model answer

Same idea, one level up: the user service needs a batch endpoint (GET /users?ids=1,2,3 or a POST with a list), and the caller groups ids before calling it, with a DataLoader or explicit batching.

For slowness: set a timeout well inside your own, and decide what a partial page looks like. Showing posts with "Unknown author" usually beats failing the whole page. A circuit breaker stops hammering the service when it is down.

If author names are needed on every read, consider keeping a copy: the post stores the author name, kept up to date by an event when a user renames. That trades a little consistency for removing the call entirely.