"Database interview questions: indexing, transactions, scaling"

The database round starts with SQL, but the part that sorts candidates comes after the query compiles: why it is slow, what happens when two run at once, and what breaks when the table outgrows one box. If you are still on query-language traps (NOT IN with NULLs, the join-filter trap, GROUP BY rules), start with the SQL interview questions post and the SQL fundamentals notes. This one routes each later question into its lesson.

---

Indexing and EXPLAIN

Which of these queries can use the index?

Given CREATE INDEX idx ON events (tenant_id, created_at), the leftmost-prefix rule decides. It serves tenant_id alone and tenant_id + created_at, but a filter on created_at alone cannot use it: there is no leading tenant_id to seek on. Column order is not cosmetic.

Trap equality columns must come before range columns. A range predicate stops the index from narrowing on anything to its right.

-- WRONG: range column first, status equality can't seek
CREATE INDEX bad ON orders (created_at, status);
-- RIGHT: equality first, range scans a contiguous slice
CREATE INDEX good ON orders (status, created_at);
SELECT * FROM orders WHERE status = 'paid' AND created_at > '2026-01-01';

The index exists but the planner ignores it

Follow-up "The EXPLAIN shows a Seq Scan even though the index is there. Is that a bug?" Usually not. On a low-selectivity predicate (a status flag matching half the table), jumping index to heap per row loses to a sequential scan. A large estimated-vs-actual row gap means stale statistics: the fix is ANALYZE, not forcing the index.

Study Database indexing.

Transactions and isolation anomalies

Naming the four levels is the floor. The signal is predicting an interleaved schedule and naming the anomaly it produces.

Non-repeatable read vs phantom read

A precision probe. A non-repeatable read is one row's value changing between two reads in one transaction. A phantom is the set of rows matching a predicate changing (a new row appears in a repeated WHERE). Read Committed allows both; Repeatable Read forbids the first.

Trap the textbook "Repeatable Read allows phantoms" is engine-dependent. Postgres Repeatable Read is snapshot isolation and forbids phantoms too. Do not recite the ANSI matrix without naming the database.

Write skew

The anomaly snapshot isolation cannot stop, and the one interviewers love because it survives when every read was consistent. Two transactions read an overlapping set, each writes a disjoint row, both commit, and a cross-row invariant breaks.

-- snapshot isolation, invariant: at least one on-call
T1: SELECT count(*) FROM shifts WHERE on_call   -- sees 2
T2: SELECT count(*) FROM shifts WHERE on_call   -- sees 2
T1: UPDATE shifts SET on_call=false WHERE id=1  -- disjoint row
T2: UPDATE shifts SET on_call=false WHERE id=2  -- disjoint row
-- both COMMIT. Zero on-call. No dirty read occurred.

The fixes: SERIALIZABLE (SSI aborts a victim with a 40001 you retry), a SELECT ... FOR UPDATE on a summary row to materialize the conflict, or a constraint the engine can serialize on.

Study Transactions & isolation.

Schema design and modeling

Normalize this, then denormalize it

Interviewers ask for 3NF, then push on when you would walk it back. Know the three anomalies normalization removes: update (a fact changed in one row, missed in its copy), insert (no customer row until they place an order), delete (removing the last order erases the customer). A many-to-many needs a junction table whose composite of two foreign keys is the natural primary key.

Follow-up "When do you denormalize?" For a measured read pattern, not by default. Every duplicated column becomes a consistency obligation you now maintain by hand.

Red flags comma-separated values stuffed in one column, dropping foreign keys "for performance," denormalizing with no read pattern to justify it.

Study Schema design & modeling.

Choosing NoSQL (and defending it)

Would a document store fit here?

Name the four families and one fit each: key-value for cache and sessions, document for app objects, wide-column for high write volume, graph for connected data. The middle-band question is embedding vs referencing: bounded data you read together embeds, unbounded or shared data is referenced by id (MongoDB caps a document at 16 MB, so unbounded arrays are a design bug).

Trap modeling a document store like a relational schema. NoSQL is access-pattern-first: list every query before you design the keys, because no ad-hoc join saves you later. The honest answer names what you give up (joins, ad-hoc queries, multi-entity transactions) for what you gain (horizontal scale, single-digit-ms reads).

Study NoSQL data models.

Replication and sharding

Replicas are stale. Why, and how do you fix read-your-writes?

Read replicas scale reads, not writes, and async replication lets a replica serve data older than the write you just made. The user edits their profile, the refresh shows the old value. Fixes: route reads to the primary for a window after a write, or wait for the replica to catch up to that write's position.

Trap "synchronous replication means no stale reads." MySQL semi-sync acks on receipt into the relay log, not on apply, so it can still serve stale data. Postgres needs synchronous_commit = remote_apply for the standby to apply before the primary acks.

Cross-shard pagination

Once the table is sharded, OFFSET breaks: to reach global offset K, each shard must return OFFSET 0 LIMIT K+n and let the coordinator merge and skip. Pushing OFFSET K down to each shard returns the wrong rows.

-- WRONG: deep offset returns wrong global rows, O(offset) per shard
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;
-- RIGHT: keyset, seek past the last id seen, O(page), shard-safe
SELECT * FROM orders WHERE id > $last_id ORDER BY id LIMIT 20;

Study Database scaling.

Performance under load

The endpoint is slow. Walk me through it.

Measure before you change code: percentiles plus the slow-query log, not the mean, because latency is right-skewed and the mean hides the tail. Report p95 and p99. The classic culprit is the N+1 query (one query for the list, one per row in a loop), fixed by eager loading, a batched WHERE IN, or a DataLoader.

Trap maxing the connection pool. Each Postgres connection is a backend process; throughput peaks at a small pool near (cores * 2), and past that knee TPS falls while p99 climbs. Keyset pagination (above) and a cache in front of the hot read are the other levers. For the cache, know cache-aside vs write-through and how you invalidate, because a bad invalidation serves stale data indefinitely.

Study Backend performance and Backend caching.

Red flags across the whole round: "add an index and everything gets faster" (it slows every write and only helps matching predicates), "lower the isolation level to fix the concurrency bug" (that adds anomalies), "just add a replica" for a write bottleneck, and sharding before exhausting indexes, caching, and vertical scale. Any of these caps the score no matter how fluent the rest was.