Database engineer databases interview questions

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

Users update their profile and immediately see old data because reads go to replicas. How would you fix this?

Sample answer outline

First confirm the symptom is replication lag by measuring primary commit time, replica replay lag, and the request path. Common fixes include read-your-writes routing to the primary for a short window after a write, session stickiness, or using a version token that waits until a replica has caught up. Do not route all reads permanently to the primary unless the capacity tradeoff is acceptable. Add lag-aware load balancing and alerts so unhealthy replicas stop serving freshness-sensitive reads. Strong answers define which reads require consistency and which can tolerate stale data.

Expect these follow-ups

• How would you expose read consistency requirements in an application API?
• What happens if replica lag is caused by one expensive analytical query?
• How does this differ in eventually consistent NoSQL stores?