Top 30 Advanced Databases & Architecture Interview Questions and Answers
30 advanced multiple-choice Databases & Architecture interview questions, the deep end: internals, failure modes, and the design calls a senior engineer is expected to defend. They come from 30 bites in the Databases & Architecture library, the hardest slice of the 134 Databases & Architecture interview questions in the library. Answer them here or read straight down. Every question carries the correct option, why it is correct, and a link to the bite it came from.
SQL, NoSQL, system design, microservices, APIs
30 questions. Pick an answer, or open “Show the answer” to read it.
Answers are graded in your browser. Nothing is saved, and no XP or streak is earned here. The app keeps score.
Question 1 of 30
A transaction reads a row twice and gets different committed values each time. Which isolation level permits this while still forbidding dirty reads?
Show the answer
Answer: d · Read Committed
Read Committed forbids dirty reads but allows non-repeatable reads, so committed changes can appear between two reads. Read Uncommitted would also allow dirty reads, which is stricter than the scenario describes.
Read the full bite: Read Committed versus Serializable isolation levels
Question 2 of 30
An endpoint fires 1 query for a list and then one extra query per item to load a relation. Which fix most directly reduces the number of round trips?
Show the answer
Answer: c · Eager-load the relation with a JOIN or single batched IN query
Eager loading collapses the per-item queries into one or two statements, attacking the round-trip count itself. Caching only masks the volume and adds invalidation work without removing the structural N+1 pattern.
Read the full bite: Diagnosing and fixing the N+1 query problem
Question 3 of 30
A social app adds a like_count column directly on the posts table instead of counting rows in a likes table on every read. According to the card, what new problem does this denormalization introduce?
Show the answer
Answer: a · The count can drift out of sync if updates aren't handled atomically, requiring periodic reconciliation
The card's example warns that a missed update or race condition can leave the count wrong, requiring periodic reconciliation, that is the core cost of denormalizing. The tempting wrong answer has it backwards: the point of adding the column is that reads become a fast single column fetch, not a join.
Read the full bite: When to intentionally denormalize a schema
Question 4 of 30
Why can't a standard foreign key enforce integrity on the commentable_id column in a polymorphic comments table?
Show the answer
Answer: b · A foreign key can reference only one specific table, not a target chosen at runtime
A foreign key is bound to one referenced table at definition time, so it cannot validate an ID whose parent table varies per row. Foreign keys can reference any unique column and text can be indexed, so those options are wrong.
Read the full bite: Polymorphic associations and referential integrity
Question 5 of 30
A Cassandra feed table partitioned by user_id makes feed reads fast. What is the main cost this design imposes compared to a relational read-time join?
Show the answer
Answer: b · Each new post must be written into every follower's partition
Fan-out on write copies each post into all followers' partitions, creating heavy write amplification, which is the trade-off for cheap single-partition reads. Cassandra avoids cross-partition joins and favors availability over strong consistency, so those options are wrong.
Read the full bite: Relational versus wide-column for a news feed
Question 6 of 30
A users table is sharded by user_id. What is the most efficient way to support frequent logins that look users up by email?
Show the answer
Answer: a · Maintain a secondary email-to-user_id index to resolve the shard in one hop
A secondary mapping from email to user_id lets a login resolve the correct shard directly, avoiding a broadcast. A UNIQUE constraint only enforces uniqueness within a single shard, and scatter-gather wastes resources on every login.
Read the full bite: Shard key impact on uniqueness and cross-shard lookups
Question 7 of 30
Why is simple row-level locking insufficient to prevent write skew under snapshot isolation?
Show the answer
Answer: a · The two transactions write to different rows, so no lock conflict arises
Write skew involves transactions that update disjoint rows based on an overlapping read, so locking individual written rows produces no conflict. The shared dependency is a predicate over a set, which needs serializable isolation or predicate locks, not single-row locks.
Question 8 of 30
Under write-ahead logging, what must be guaranteed durable on disk before a transaction's commit is acknowledged?
Show the answer
Answer: c · The transaction's log records describing its changes
Only the log records must be flushed at commit, which is why commits are cheap yet durable; the data pages can be written later. Forcing all dirty data pages on every commit is exactly what the WAL avoids.
Read the full bite: How the write-ahead log ensures atomicity and durability
Question 9 of 30
The optimizer chose a nested loop because it estimated 10 outer rows, but 5 million were returned. What is the most appropriate first action?
Show the answer
Answer: c · Refresh table statistics so cardinality estimates reflect reality
The root cause is a cardinality misestimate, usually from stale statistics, so refreshing them lets the optimizer choose correctly on its own. Hints mask the problem, dropping indexes worsens it, and isolation level is unrelated to join choice.
Read the full bite: Optimizer picks nested loop over hash join
Question 10 of 30
Why does the optimizer often ignore a plain B-Tree on a column with only three values when filtering on it?
Show the answer
Answer: b · Each value matches a large fraction of rows, so random heap fetches cost more than a sequential scan
Low selectivity means the index returns a big share of rows, and chasing those via scattered heap reads is slower than one sequential scan, so the optimizer skips it. The other options invent nonexistent structural or corruption limits.
Read the full bite: Indexing a low-cardinality status column
Question 11 of 30
During the ARIES Redo phase, how does the engine avoid applying a logged change to a page that already reflects it?
Show the answer
Answer: a · It compares the log record's LSN with the page's stored LSN and skips records at or below it
Idempotency comes from per-page LSN tracking: if the page's LSN already covers a log record, that record is skipped, so restarting recovery never double-applies. Redo repeats history for all transactions, not just committed ones.
Question 12 of 30
Why is columnar storage dramatically more efficient for an aggregate over two columns of a wide table?
Show the answer
Answer: c · It reads only the referenced columns and their highly compressible homogeneous data, cutting I/O
Columnar layout lets the engine read just the needed columns and exploit strong same-type compression, slashing I/O versus a row store that drags whole rows through memory. It does not auto-index every column or turn scans into point lookups.
Question 13 of 30
During redo, a data page on disk has page-LSN 700. A log record for that page has LSN 650. What does the engine do and why?
Show the answer
Answer: b · Skip it, because 650 is at or below the page-LSN, so the change is already on the page
A log-record LSN at or below the page-LSN means the page already reflects that change, so redo skips it to stay idempotent. Reapplying it would double-apply a change already persisted; lower LSNs are older, not newer.
Question 14 of 30
When a hash join's build table cannot fit in memory, what does a Grace hash join do to still complete the join correctly?
Show the answer
Answer: b · Partitions both inputs with the same hash function and joins matching partition pairs from disk
Grace hash join partitions both relations by the same hash so matches co-locate, then joins partition pairs that each fit in memory. Sorting and merging is a different algorithm, not what a spilling hash join does.
Read the full bite: How does a hash join handle memory overflow?
Question 15 of 30
What guarantee does a Saga provide that distinguishes it from a true multi-document ACID transaction?
Show the answer
Answer: b · It provides atomicity via compensating transactions but not isolation, so partial states can be observed
A Saga achieves all-or-nothing through compensations but cannot isolate intermediate steps, so partial results are visible. Locking all participants describes two-phase commit, and Sagas yield eventual, not instant strong, consistency.
Read the full bite: How do you keep consistency without multi-document transactions?
Question 16 of 30
In an availability-first system facing partitions, which approach best prevents silently losing concurrent updates to the same data?
Show the answer
Answer: d · Merging divergent replicas with a conflict-free replicated data type that combines updates deterministically
A CRDT deterministically merges concurrent updates, so neither side's change is dropped. Last-write-wins silently discards one concurrent update, linearizability conflicts with staying available under partition, and disabling replication harms availability.
Read the full bite: What consistency do you sacrifice in an AP system?
Question 17 of 30
What is the key advantage of separating storage and compute compared with a traditional shared-nothing MPP warehouse?
Show the answer
Answer: b · Compute and storage scale independently and multiple isolated clusters can query the same data without contending
Decoupling lets you scale compute and storage separately and run isolated clusters over shared data with no contention. Node-owned slices describe MPP, columnar storage and optimization remain essential, and compute does read from shared remote storage, mitigated by caching.
Read the full bite: Why separate storage and compute in a cloud warehouse?
Question 18 of 30
Which describes the OLAP operations correctly, and when does pre-aggregating into a cube make most sense?
Show the answer
Answer: c · Slice fixes one dimension value and roll-up aggregates to coarser granularity; pre-aggregate for repeated low-latency queries on stable dimensions
Slice fixes one dimension and roll-up moves to coarser granularity, and cubes pay off for repeated low-latency queries on stable dimensions. The distractors swap slice and dice, reverse drill-down and roll-up, or misstate when precomputation helps.
Read the full bite: What is an OLAP cube and its operations?
Question 19 of 30
In a 3-replica shard using a write quorum of 2, when is it safe to confirm a write to the client?
Show the answer
Answer: d · After the WAL entry is durably persisted on at least two replicas
A write quorum of two means the entry must be durably persisted on two of three replicas before confirming, so it survives one node loss. Memory-only or routing-layer acks are not durable, and waiting for all three sacrifices availability unnecessarily.
Read the full bite: Durable write path in a sharded KV store
Question 20 of 30
A 5-node Raft cluster partitions into groups of 3 and 2. What happens on the 2-node side?
Show the answer
Answer: d · It cannot reach quorum, so it cannot elect a leader or commit writes
Raft requires a majority (3 of 5) to elect a leader and commit, so the 2-node minority cannot make progress, preventing split-brain. The 3-node majority side continues normally, so the cluster does not fully freeze.
Question 21 of 30
A shard is hot because writes use a sequential timestamp key. Which fix actually resolves the root cause rather than just buying time?
Show the answer
Answer: a · Change the partition key to a hash so writes spread evenly
A sequential key sends all new writes to one shard; hashing the key spreads writes across shards, fixing the cause. Read replicas and caching only help reads, and vertical scaling postpones the same write hot spot.
Question 22 of 30
A database is slow yet CPU and memory are normal. What does this pattern most strongly suggest?
Show the answer
Answer: d · Sessions are waiting (e.g., on locks or I/O) rather than computing
Low CPU and memory with poor performance means work is blocked, so wait-event analysis (locks, I/O) is the right lens. Adding RAM or assuming throttling ignores that the resources are idle precisely because sessions are waiting.
Read the full bite: Diagnosing degradation with normal CPU and memory
Question 23 of 30
Parameterization is in place, but how does a least-privilege database account further reduce SQL injection risk?
Show the answer
Answer: c · It limits what an injection can access even if one query is exploitable
Least privilege caps the blast radius so a successful injection can only touch the limited tables and verbs granted. It does not rewrite queries, replace parameterization, or encrypt parameters; it is a containment layer.
Question 24 of 30
In a stateless web app, an entity loaded in one request is edited and saved in a later request. What concurrency risk does the Unit of Work pattern most directly expose?
Show the answer
Answer: b · Lost updates from overwriting a row another user changed meanwhile
Because the session is short-lived per request, an entity can go stale between read and write, so a blind update overwrites another user's change, a lost update, mitigated by optimistic version checks. Dirty reads and pool exhaustion are unrelated concerns.
Question 25 of 30
An ORM hydrates thousands of full entities and aggregates a report in app memory, taking seconds. Which fix best balances performance and maintainability for this report?
Show the answer
Answer: b · Use a targeted SQL query with GROUP BY and a covering index, mapped to a DTO
Pushing aggregation into a tuned SQL query with proper indexing and a lightweight DTO fixes the root inefficiency for the report. Adding servers or pool size does not cut the wasteful work, and abandoning the ORM everywhere is overkill for one report.
Read the full bite: Fixing an ORM's inefficient aggregation query
Question 26 of 30
What is a fundamental architectural difference in how Aurora and Spanner scale writes?
Show the answer
Answer: c · Aurora has a single regional writer over shared storage; Spanner shards with per-split Paxos leaders
Aurora scales reads via replicas but writes through one regional writer over shared storage, whereas Spanner shards data and runs a Paxos leader per split, scaling writes horizontally. TrueTime belongs to Spanner, not Aurora.
Question 27 of 30
After failing over to the passive region, why is fencing the original primary the most critical step before it can recover?
Show the answer
Answer: b · It prevents a split-brain where two writers accept conflicting writes
Fencing ensures only one writable primary exists, avoiding split-brain and conflicting writes. Async replication still implies a non-zero RPO, and reconciliation is a separate manual step.
Read the full bite: Multi-region active-passive DR with Aurora
Question 28 of 30
Why do millions of tiny files slow down lake queries even when the total data volume is modest?
Show the answer
Answer: c · Fixed per-file overhead for listing, opening, and metadata dominates actual data reading
Each file incurs fixed listing, open, and metadata costs plus a scheduled task, so huge file counts swamp the engine regardless of total size. The other options are fabricated limits.
Question 29 of 30
How do Iceberg and Delta Lake handle two concurrent writers committing to the same table?
Show the answer
Answer: a · They use optimistic concurrency: a conflicting writer detects the new version and retries or fails
Both formats use optimistic concurrency over immutable storage, atomically advancing the version and forcing a loser to retry on the latest snapshot. Object stores do not provide the directory locking option B assumes.
Question 30 of 30
Why is a TSM-tree often more efficient than a general LSM-tree for time-series writes and range scans?
Show the answer
Answer: b · It organizes data by series and time in columnar, type-compressed blocks suited to ordered appends
A TSM-tree is an LSM derivative that sorts by series and time and stores columnar, heavily compressed blocks, so ordered writes are cheap and range scans read contiguous data. It still flushes immutable files and compacts.
Could you explain these out loud?
That is what an interview actually tests. Tezvyn gives you questions like these with what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.