Database engineer at Adobe

As asked

A Postgres query on a 200 million row orders table takes 40 seconds. How do you read the query plan and decide what index to add?

Sample answer outline

Use EXPLAIN ANALYZE to compare estimated rows with actual rows, identify sequential scans, expensive sorts, nested loops over large inputs, and whether time is spent reading buffers or computing. Design the index around the predicate, join, and ordering pattern, not around every column in the table. For common queries, a composite or partial index may beat separate single-column indexes, but it must match selectivity and column order. Check write overhead, bloat, and whether the index supports index-only scans through included columns. Candidates often add indexes blindly without validating the plan before and after.

Expect these follow-ups

• When would a sequential scan be the right plan?
• How do stale statistics affect the optimiser?
• What is the tradeoff between a composite index and two single-column indexes?

As asked

Walk me through how a B-tree index is physically laid out on disk, how a point lookup traverses it, and what happens when a page split occurs during an insert.

Sample answer outline

A strong answer covers the balanced tree of fixed-size pages, the fan-out at each internal node holding separator keys and child pointers, and leaf pages holding key-to-rowid mappings in sorted order. The candidate should explain that a page split creates a new sibling page and propagates a separator key upward, potentially cascading to the root and increasing tree height. Bonus: mention fill factor and how it pre-allocates space to reduce split frequency.

Expect these follow-ups

• How does a clustered index differ from a secondary index in terms of page layout?
• At what point does adding an index hurt write throughput more than it helps read throughput?

As asked

Explain the conditions under which PostgreSQL's query planner prefers a hash join over a nested loop join, and describe a scenario where a nested loop is actually faster.

Sample answer outline

The planner chooses a hash join when one input is large and lacks a useful index, because building a hash table and probing it is O(N+M) versus O(N*M) for a naive nested loop. A nested loop wins when the outer side is small and the inner side has a selective index, making each inner lookup O(log N) and total cost low. The candidate should mention work_mem as the limit: if the hash table spills to disk, the hash join degrades and a merge join or nested loop might be cheaper.

Expect these follow-ups

• What does enable_hashjoin=off do and when would you set it?

As asked

You run EXPLAIN (ANALYZE, BUFFERS) on a slow query and see that the planner estimated 50 rows but the actual count was 2 million for a particular node. What does this tell you and how do you fix it?

Sample answer outline

A large discrepancy between estimated and actual rows signals stale statistics. The planner chose a plan optimized for 50 rows (possibly a nested loop with an expensive inner index scan) that performs terribly at 2 million rows. The fix starts with ANALYZE on the table to refresh statistics. If the column has a high-correlation pattern or heavy skew, increase the statistics target with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS. For complex joins, extended statistics (CREATE STATISTICS) capturing column correlations may help.

Expect these follow-ups

• What does Buffers: shared hit vs shared read tell you in the BUFFERS output?

As asked

List the four SQL standard isolation levels, identify which read anomalies each prevents, and give a concrete example of a non-repeatable read that would slip through READ COMMITTED.

Sample answer outline

READ UNCOMMITTED allows dirty reads, non-repeatable reads, and phantom reads. READ COMMITTED prevents dirty reads but allows non-repeatable reads and phantoms. REPEATABLE READ prevents dirty and non-repeatable reads but allows phantoms in the SQL standard (PostgreSQL actually prevents phantoms too due to MVCC). SERIALIZABLE prevents all anomalies. A non-repeatable read example: transaction A reads a row showing balance=100, transaction B commits an update to 50, transaction A reads again and sees 50 within the same transaction under READ COMMITTED.

Expect these follow-ups

• What is a write skew anomaly and which isolation level prevents it?

As asked

You find application code that runs SELECT * FROM orders WHERE user_id = $1 in a loop for each user. The loop runs 5,000 times per request. Rewrite this as a single query and explain the join strategy the database should choose.

Sample answer outline

Replace the loop with SELECT o.* FROM orders o WHERE o.user_id = ANY($1) where $1 is an array of all user IDs, or join directly to a users query. The planner should choose a hash join on user_id if the user list is large, using the user_id array as the build side. The candidate should mention adding an index on orders.user_id and ensuring statistics are fresh so the planner estimates join cardinality correctly.

Reference implementation (sql)

-- Before: N+1 pattern
FOR user_id IN (SELECT id FROM users WHERE active = true) LOOP
  SELECT * FROM orders WHERE user_id = user_id;
END LOOP;

-- After: single query
SELECT o.*
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.active = true;

Expect these follow-ups

• When would you use a lateral join instead of a plain join here?