Cover the failure modes: bloat, wraparound, thrashing, over-indexing
0 / 15 completed
1 / 15
The interviewer asks: "Compare LSM-trees and B-trees for a write-heavy workload — explain write amplification, read amplification, and space amplification for each." Which answer best covers the trade-off analysis?
Option B gives the full RUM-conjecture analysis: B-tree write amplification (WAF 2–10×, random I/O pattern), LSM write amplification mechanics (memtable → L0 → levelled compaction, WAF 10–30× with real numbers), LSM read amplification (multiple SSTable checks, Bloom filter mitigation), space amplification during compaction, and practical tuning knobs (RocksDB parameters). It also gives the key insight about which workloads favour each. Options C and D each identify one aspect but don't give the full amplification picture across all three dimensions (read/write/space).
2 / 15
The interviewer asks: "Explain how MVCC (Multi-Version Concurrency Control) works in PostgreSQL — how are versions stored, how are they cleaned up, and what can go wrong?" Which answer demonstrates the deepest understanding?
Option B covers every layer: tuple format (xmin/xmax), snapshot visibility rule with exact logic, MVCC storage bloat mechanics, the difference between VACUUM and VACUUM FULL, XID wraparound (32-bit limit, 2.1B transactions, VACUUM FREEZE as the solution, and the emergency shutdown consequence), and the long-transaction + bloat interaction. Option C states the xmin/xmax fact but doesn't explain the snapshot rule, wraparound risk, or tuning implications. Options A and D are correct but surface-level.
3 / 15
The interviewer asks: "Explain the WAL (Write-Ahead Log) recovery process in a crash scenario — what are the ARIES phases and how does PostgreSQL implement them?" Which answer best covers crash recovery?
Option B is the complete answer: ARIES phases with the nuance that redo replays ALL transactions (not just committed ones — a common misconception), WAL record structure with LSN as a 64-bit pointer, checkpoint mechanics with the specific configuration knobs, PITR with WAL archiving, WAL level modes, and full-page writes (explaining why they're needed — torn page prevention). Options C and D each name 2-3 correct concepts but miss the ARIES redo-all-transactions nuance, full-page writes, and PITR mechanics.
4 / 15
The interviewer asks: "How does a database buffer pool work, and what are the key eviction policy trade-offs between LRU and clock-sweep?" Which answer best covers buffer pool internals?
Option B covers the full picture: buffer pool structure with the page table hash map and descriptor fields (pin count, dirty flag, usage count, LSN), LRU mechanics and its sequential scan thrashing failure mode, clock-sweep with exact PostgreSQL usage counter values (max 5), the sequential scan ring buffer optimisation (a key PostgreSQL-specific detail rarely mentioned), ARC as an alternative with its use in ZFS/Oracle, and pinning semantics and their impact on effective pool size. Options C and D mention the right algorithm names but don't explain the sequential scan ring buffer, the eviction counter mechanics, or ARC.
5 / 15
The interviewer asks: "Design an index advisor for a relational database — what signals would you collect, how would you recommend indexes, and how do you avoid over-indexing?" Which answer demonstrates the most complete design?
Option B covers all five design dimensions: signal collection with specific PostgreSQL views and the metrics they provide, candidate index generation with the column ordering rule (equality first, range last), cost estimation using `hypopg` for hypothetical evaluation, over-indexing prevention with specific thresholds (zero scan detection, 30-day window, 5-index limit, partial indexes), and output format with write overhead reporting and human approval gates for large tables. Options C and D each identify 1-2 of the five dimensions but none cover column ordering rules, write overhead estimation, or the over-indexing prevention mechanisms.
6 / 15
Sarah (Senior Database Engineer) just posted a code review comment on your query: 'This query is performing full table scans – seriously consider adding an index on the customer_id column. It's impacting overall performance.' What's the MOST accurate response you should provide to Sarah, explaining why her suggestion is valuable?
Sarah is correctly identifying a performance issue – full table scans are often inefficient. Indexes dramatically speed up data retrieval by allowing the database to quickly locate relevant rows based on indexed columns. Option A misrepresents the benefits of indexes and their impact on write operations; options C and D shift responsibility or avoid addressing the core problem.
7 / 15
David (Backend Developer) sends you this Slack message: 'Our API endpoint for retrieving user orders is timing out frequently. I've checked the server logs and it seems like there are a lot of slow queries running.' Which of the following actions BEST addresses David's immediate concern, focusing on database performance?
David's message points to a database-related issue – slow queries. Profiling these queries is crucial for understanding the root cause and implementing targeted solutions like adding indexes or optimizing query plans. Scaling hardware without addressing the underlying problem will likely be ineffective; options A and C are misdirected and option B is a reactive, not proactive, approach.
8 / 15
You're writing the PR description for adding a new column to a large customer database table. The description should accurately reflect your understanding of the potential impact on query performance. Which statement is MOST appropriate?
While adding a column *can* improve queries that utilize it, it's crucial to acknowledge potential impacts. Option A is overly optimistic and misleading; option D lacks technical awareness. Option B accurately describes the need for optimization (indexes), while option C offers false reassurance.
9 / 15
During a standup meeting, your team lead asks: 'Can you briefly explain how the database handles concurrent updates to the same record?' Your response should focus on the core principles. Which of the following best summarizes your explanation?
Option A describes a simplistic and often problematic locking strategy. Option B correctly introduces the concept of MVCC – allowing concurrent access without explicit locks. Options C and D represent specific implementations of concurrency control that are subsets of MVCC or other advanced techniques.
10 / 15
You're tasked with designing a system to monitor index usage in a large database. Which metric(s) would be MOST valuable for identifying potential over-indexing or under-utilized indexes?
Tracking query usage directly reveals whether an index is actually being utilized. Combining this with index cardinality (the number of rows indexed by a column) and execution time provides valuable insight into index effectiveness. Option A only measures size; options C and D provide irrelevant data.
11 / 15
Sarah (Senior Database Engineer) just posted a code review comment on your query: 'This query is performing full table scans – seriously consider adding an index on the customer_id column. It's impacting overall performance.' What's the MOST accurate response you should provide to Sarah, explaining why her suggestion is valuable?
Sarah is correctly identifying a performance issue – full table scans are often inefficient. Indexes dramatically speed up data retrieval by allowing the database to quickly locate relevant rows based on indexed columns. Option A misrepresents the benefits of indexes and their impact on write operations; options C and D shift responsibility or avoid addressing the core problem.
12 / 15
David (Backend Developer) sends you this Slack message: 'Our API endpoint for retrieving user orders is timing out frequently. I've checked the server logs and it seems like there are a lot of slow queries running.' Which of the following actions BEST addresses David's immediate concern, focusing on database performance?
David's message points to a database-related issue – slow queries. Profiling these queries is crucial for understanding the root cause and implementing targeted solutions like adding indexes or optimizing query plans. Scaling hardware without addressing the underlying problem will likely be ineffective; options A and C are misdirected and option B is a reactive, not proactive, approach.
13 / 15
You're writing the PR description for adding a new column to a large customer database table. The description should accurately reflect your understanding of the potential impact on query performance. Which statement is MOST appropriate?
While adding a column *can* improve queries that utilize it, it's crucial to acknowledge potential impacts. Option A is overly optimistic and misleading; option D lacks technical awareness. Option B accurately describes the need for optimization (indexes), while option C offers false reassurance.
14 / 15
During a standup meeting, your team lead asks: 'Can you briefly explain how the database handles concurrent updates to the same record?' Your response should focus on the core principles. Which of the following best summarizes your explanation?
Option A describes a simplistic and often problematic locking strategy. Option B correctly introduces the concept of MVCC – allowing concurrent access without explicit locks. Options C and D represent specific implementations of concurrency control that are subsets of MVCC or other advanced techniques.
15 / 15
You're tasked with designing a system to monitor index usage in a large database. Which metric(s) would be MOST valuable for identifying potential over-indexing or under-utilized indexes?
Tracking query usage directly reveals whether an index is actually being utilized. Combining this with index cardinality (the number of rows indexed by a column) and execution time provides valuable insight into index effectiveness. Option A only measures size; options C and D provide irrelevant data.
What does "Database Internals Engineer — Interview Questions — Best-Answer Practice" cover?
Practice answering Database Internals Engineer interview questions in professional English. 5 exercises on LSM-tree vs B-tree trade-offs, MVCC, WAL recovery, buffer pool management, and index advisor design.
How many questions are in this interview set?
This set has 15 exercises, each with a full explanation.
Is this exercise free to use?
Yes. Every exercise on CoderSlingo, including this one, is free to use with no account, sign-up, or paywall.
Do these exercises include model answers?
Yes. Each interview question gives you several possible responses and asks you to pick the one that communicates most clearly and completely — the explanation then breaks down exactly why that answer works, including the specific vocabulary a strong candidate would use.
What if I choose an answer that isn't the strongest one?
You'll see which option was correct and read a full explanation of why it's stronger than the alternatives, plus the key vocabulary and phrasing worth reusing in a real interview.
Can I retry the questions?
Yes — use the "Try again" button on the results screen to reset and go through the set again.
Is this the same as a real technical or behavioural interview?
No — it's focused practice for the language side of interviewing: recognising which phrasing sounds precise and confident versus vague, and knowing the vocabulary interviewers expect for this role. It won't replace mock interviews, but it builds the vocabulary you'll need in one.
Where can I find interview prep for other roles?
Browse the full Interview exercises hub for 170+ modules covering behavioural, technical, and system design rounds across dozens of IT roles, or check the "Next up" link below to continue.
Do I need an account, and is my progress saved?
No account is needed. Progress is tracked only for your current visit — reloading or leaving the page resets the counter.
Who writes these interview questions?
Every question is written by the CoderSlingo team based on real technical interview patterns for this role, then reviewed for accuracy and clarity.