Everything in Backend Dev, page 14
Optimizer picks nested loop over hash join
Nested loop wins on few rows, so bad row estimates from stale stats or skewed data trick it; fix by refreshing statistics, adding histograms, rewriting predicates, or ensuring memory for hashing.
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.
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.
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 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.
Clustered versus non-clustered indexes
A clustered index orders the table's actual rows, one per table; a non-clustered index is a separate structure pointing to rows.
Composite index column order for multi-column filters
Create one index on (last_name, first_name); order matters because the index serves leftmost-prefix lookups.
What a database index is and when it helps
An index is a sorted lookup structure avoiding full scans, helps selective WHERE/JOIN columns, but costs write overhead.
How the write-ahead log ensures atomicity and durability
Log changes before applying, flush log at commit, replay redo and undo on recovery.
Write skew under snapshot isolation
Two transactions read an overlapping set, each writes disjoint rows, jointly violating an invariant.
Two-Phase Locking and serializability
A growing phase only acquires locks, a shrinking phase only releases, no lock taken after one is freed.
How MVCC enables non-blocking reads
Writers create new row versions instead of overwriting, readers see a consistent snapshot, so readers never block writers.
Database deadlocks and how engines resolve them
Define a deadlock as mutual waiting on locks, name detection plus victim rollback, and prevention by consistent lock ordering.
The lost update anomaly explained
Both transactions read the same value, each adds one, the second overwrite erases the first.
The ACID properties of transactions
Define Atomicity, Consistency, Isolation, Durability and why each matters.
Shard key impact on uniqueness and cross-shard lookups
Uniqueness and FKs hold only within a shard; non-shard-key lookups need scatter-gather or a secondary index.
Relational versus wide-column for a news feed
Relational gives flexible joins but read-time fan-out; Cassandra precomputes per-user feed rows for fast writes-side fan-out.
Polymorphic associations and referential integrity
A single column can't FK two tables, so integrity is unenforced; alternatives use exclusive arcs or per-type tables.
3NF versus BCNF and the overlapping-key gap
BCNF requires every determinant be a superkey; 3NF allows exceptions for prime attributes.
Adjacency List versus Nested Set for hierarchies
Adjacency list is simple writes but recursive reads; nested set is fast subtree reads but costly writes.
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