5 exercises — choose the best-structured answer to common Database Engineer interview questions covering indexes, ORM optimisation, sharding, transactions, and zero-downtime schema migrations.
Structure for Database Engineer answers
Tip 1: Explain indexes by physical storage model — clustered = data order, non-clustered = pointer
Tip 2: For N+1: name the pattern, give an ORM example, then list solutions (eager loading, DataLoader)
Tip 3: For migrations: always mention the expand-contract pattern and NOT NULL DEFAULT rule
Tip 4: Connect ACID/BASE to CAP theorem when discussing NoSQL trade-offs
0 / 5 completed
1 / 5
The interviewer asks: "Explain the difference between a clustered and a non-clustered index." Which answer is most precise?
Option B is strongest because it explains the fundamental architectural difference: clustered = physical data order = leaf nodes ARE data; non-clustered = separate structure = leaf nodes contain pointers. Key concepts: physical data order, one clustered per table, B-tree leaf nodes, row locators, key lookup, covering indexes. Option C is partially true (clustered often on PK by default) but misses the physical storage explanation. Options A and D are incorrect.
2 / 5
The interviewer asks: "What is the N+1 query problem and how do you solve it?" Which answer best demonstrates ORM and query optimisation knowledge?
Option B is strongest because it defines the problem precisely with a concrete example and provides multiple solution strategies for different contexts. Key structure: definition (1 + N queries) → ORM lazy loading root cause → eager loading solution → DataLoader pattern → raw SQL JOIN → denormalisation → detection (query logging). Option A misunderstands "N". Option C (Redis cache) is a workaround, not a structural fix. Option D is incorrect.
3 / 5
The interviewer asks: "What is database sharding and when should you use it?" Which answer best demonstrates distributed database knowledge?
Option B is strongest because it defines sharding precisely, names the three sharding strategies, explains when it is appropriate, and lists trade-offs and alternatives. Key structure: horizontal partitioning across instances → shard key → range/hash/directory strategies → when: write throughput ceiling after vertical scale → trade-offs: cross-shard queries, 2PC, rebalancing → alternatives first. Option A confuses sharding with replication. Option C describes table partitioning within one instance (different concept). Option D ("always shard") is premature optimisation.
4 / 5
The interviewer asks: "Explain ACID properties and when you might sacrifice one." Which answer best demonstrates transactional database expertise?
Option B is strongest because it precisely defines each property, names the isolation levels, explains when relaxation is justified, and connects it to CAP theorem and BASE. Key structure: Atomicity (all-or-nothing) → Consistency (valid state transitions) → Isolation (levels: READ COMMITTED vs SERIALIZABLE) → Durability (WAL) → sacrifice Isolation for throughput → BASE/CAP for NoSQL → domain-driven choice. Option A is vague. Option C is incorrect (most applications benefit from ACID). Option D misdefines the acronym.
5 / 5
The interviewer asks: "How do you approach database migrations in a zero-downtime deployment?" Which answer best demonstrates production database operations knowledge?
Option B is strongest because it describes the expand-contract pattern, batched backfill, NOT NULL without DEFAULT pitfall, online schema change tools, and blue-green routing. Key structure: expand (add new) → backfill (batched) → contract (drop old) → NOT NULL DEFAULT rule → gh-ost/pg_repack → blue-green → production-sized test. Option A requires downtime (offline app). Option C (startup migrations) blocks deployment and risks timeouts. Option D (maintenance page) means downtime.