Databases Study Portal

Interview talking points

• "How would you speed up a slow query?" — First EXPLAIN ANALYZE it; don't guess. Look for a Seq Scan on a big table, a bad row estimate (stale stats → ANALYZE), or a non-SARGable predicate. Then add/adjust the right index, make the predicate SARGable, select only needed columns, and re-measure.
• "What is an index and what does it cost?" — A sorted side structure for O(log n) lookups by a column. It speeds reads but slows every write and uses storage, so index for your real query patterns, not "just in case."
• "Composite index (a, b) — which queries use it?" — Leftmost-prefix: WHERE a=… and WHERE a=… AND b=… yes; WHERE b=… alone no. Put equality / most-selective columns first.
• "What's a covering index?" — One that contains all columns the query reads, so it's answered index-only without touching the table.
• "Why is WHERE YEAR(created_at)=2026 slow?" — Wrapping the column in a function makes it non-SARGable, so the index can't be used. Rewrite as a half-open range >= '2026-01-01' AND < '2027-01-01'.
• "Explain the isolation levels." — Read Uncommitted/Committed/Repeatable Read/Serializable, each preventing one more of dirty / non-repeatable / phantom reads. Name each phenomenon and the trade-off (correctness vs concurrency/aborts).
• "What causes a deadlock and how do you avoid it?" — Two txns hold locks each other needs. Acquire locks in a consistent order, keep transactions short, and retry the victim on the deadlock error.
• "Offset vs keyset pagination?" — OFFSET rescans and discards skipped rows (slower the deeper you go); keyset seeks via the index using the last key (constant time), at the cost of no random page jumps.
• "When do you denormalize?" — As a deliberate, measured optimization for a read-heavy path where a join is the bottleneck — accepting the duplicate-sync cost. Normalize by default.
• "How do you fix N+1?" — Replace the per-row queries with one join / projection / eager-load. Spot it in the query log as a repeated parameterized query.
• "Surrogate vs natural key?" — A surrogate id is stable and decoupled from business meaning (which can change), keeps FKs small/fast, and avoids leaking PII; a natural key avoids an extra column but can change and is often wider.