Intermediate interview questions in Databases & Architecture
Describe 1NF, 2NF, 3NF, normalization's purpose, and its performance trade-off.
1NF atomic values; 2NF no partial dependencies; 3NF no transitive dependencies; prevents update anomalies but adds join overhead.
Explain database indexes, the classic data structure, and write-heavy trade-offs
Tests the read-write trade-off of indexing. A strong answer names B-Trees, explains they avoid full scans, and notes that inserts, updates, and deletes must update the index, adding write amplification and storage cost. Red flag: claiming indexes are free.
Find users who never placed an order and explain JOIN choice
This tests SQL anti-joins and NULL semantics. A strong answer uses LEFT JOIN with IS NULL or NOT EXISTS, explains why NOT IN is risky with NULLs, and why NOT EXISTS is preferred. Red flag: using INNER JOIN or ignoring NULLs.
What is the difference between WHERE and HAVING in SQL?
Tests SQL execution order and aggregation. A strong answer states WHERE filters rows before grouping, HAVING filters groups after aggregation, and gives an aggregate example that WHERE cannot evaluate.
Adjacency List versus Nested Set for hierarchies
Adjacency list is simple writes but recursive reads; nested set is fast subtree reads but costly writes.
3NF versus BCNF and the overlapping-key gap
BCNF requires every determinant be a superkey; 3NF allows exceptions for prime attributes.
How MVCC enables non-blocking reads
Writers create new row versions instead of overwriting, readers see a consistent snapshot, so readers never block writers.
Two-Phase Locking and serializability
A growing phase only acquires locks, a shrinking phase only releases, no lock taken after one is freed.
What is a query execution plan?
The plan is the optimizer's chosen tree of operators; run EXPLAIN or EXPLAIN ANALYZE; watch for sequential scans, bad row estimates, and costly joins.
Trade-offs of adding indexes to a table
Indexes speed reads but slow writes since every INSERT, UPDATE, and DELETE must maintain them; they consume storage and can be unused on low-selectivity columns.
What is a covering index?
A covering index contains every column a query needs so the engine answers from the index alone, skipping the table heap; build it by including filter, join, and selected columns.
B-Tree versus Hash indexes
B-Trees keep keys sorted, supporting equality, range, prefix, and ORDER BY; hash indexes give O(1) equality only, no ranges or ordering.
Database checkpoints with WAL
A checkpoint flushes dirty pages and records a known-good point so recovery can start later in the log; frequent checkpoints shorten recovery but add I/O spikes, infrequent ones lengthen…
Heap file versus clustered index
A heap stores rows unordered with cheap inserts but no inherent ordering; a clustered or index-organized table stores rows in primary-key order, giving fast key range reads but costlier inserts and page…
Lifecycle of a single row update
Buffer manager faults the page in, the row is modified in memory marking the page dirty, a WAL record is written, and commit fsyncs the log while the dirty page is flushed later by a checkpoint.
B+ Tree range queries across pages
Internal nodes are pages of keys guiding the search, all data sits in linked leaf pages; a range query descends to the start key then follows the leaf chain sequentially until the upper bound.
Hash join versus sort-merge join
Hash join builds and probes a hash table, great for unsorted equality joins with enough memory; sort-merge sorts both inputs then merges, winning when inputs are already sorted or output must…
Sorting data larger than memory
External merge sort reads memory-sized chunks, sorts each in RAM and writes them as sorted runs to disk, then merges many runs together in passes until one sorted output remains.
Embed or reference likes in a document database?
Embedding is fast for small bounded lists but unbounded likes hit document size limits; referencing scales for high-cardinality, write-heavy likes.
What are eventual consistency and the BASE model?
Eventual consistency means replicas converge given no new writes; BASE is Basically Available, Soft state, Eventually consistent.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles