More in Backend Dev — page 14
What is a covering index?
WHAT IT TESTS: knowing index-only scans. OUTLINE: 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
WHAT IT TESTS: understanding indexes cost more than they give. OUTLINE: 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?
WHAT IT TESTS: practical query debugging. OUTLINE: 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. RED FLAG: confusing estimated cost with actual time.
Clustered versus non-clustered indexes
WHAT IT TESTS: how index type affects physical row storage. OUTLINE: a clustered index orders the table's actual rows, one per table; a non-clustered index is a separate structure pointing to rows. RED FLAG: thinking a table can have many clustered indexes.
Composite index column order for multi-column filters
WHAT IT TESTS: understanding composite index leftmost-prefix rules. OUTLINE: 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
WHAT IT TESTS: basic indexing intuition. OUTLINE: an index is a sorted lookup structure avoiding full scans, helps selective WHERE/JOIN columns, but costs write overhead. RED FLAG: indexing everything or ignoring the write and storage cost.
How the write-ahead log ensures atomicity and durability
WHAT IT TESTS: WAL mechanics behind durability and atomicity. OUTLINE: log changes before applying, flush log at commit, replay redo and undo on recovery. RED FLAG: thinking commit must flush all data pages, or confusing the WAL with a backup.
Write skew under snapshot isolation
WHAT IT TESTS: subtle anomaly snapshot isolation misses. OUTLINE: two transactions read an overlapping set, each writes disjoint rows, jointly violating an invariant. RED FLAG: thinking snapshot isolation equals serializable or that row locks alone fix it.
Two-Phase Locking and serializability
WHAT IT TESTS: how locking enforces serial-equivalent schedules. OUTLINE: a growing phase only acquires locks, a shrinking phase only releases, no lock taken after one is freed. RED FLAG: confusing 2PL with the two-phase commit protocol.
How MVCC enables non-blocking reads
WHAT IT TESTS: grasp of versioned rows and snapshots. OUTLINE: writers create new row versions instead of overwriting, readers see a consistent snapshot, so readers never block writers. RED FLAG: claiming MVCC eliminates all locking including write conflicts.
Database deadlocks and how engines resolve them
WHAT IT TESTS: understanding circular lock waits. OUTLINE: define a deadlock as mutual waiting on locks, name detection plus victim rollback, and prevention by consistent lock ordering. RED FLAG: confusing a deadlock with a slow query or simple lock wait.
The lost update anomaly explained
WHAT IT TESTS: read-modify-write race awareness. OUTLINE: both transactions read the same value, each adds one, the second overwrite erases the first. RED FLAG: thinking the database auto-serializes plain reads, or that the final value is always correct.
The ACID properties of transactions
WHAT IT TESTS: foundational transaction guarantees. OUTLINE: define Atomicity, Consistency, Isolation, Durability and why each matters. RED FLAG: confusing Consistency with Isolation or thinking durability means in-memory only.
Shard key impact on uniqueness and cross-shard lookups
WHAT IT TESTS: understanding constraints break across shards. OUTLINE: uniqueness and FKs hold only within a shard; non-shard-key lookups need scatter-gather or a secondary index. RED FLAG: assuming a global unique index just works across shards.
Relational versus wide-column for a news feed
WHAT IT TESTS: matching data model to feed access patterns. OUTLINE: relational gives flexible joins but read-time fan-out; Cassandra precomputes per-user feed rows for fast writes-side fan-out. RED FLAG: choosing a store without naming the read pattern.
Polymorphic associations and referential integrity
WHAT IT TESTS: knowing why polymorphic columns break foreign keys. OUTLINE: a single column can't FK two tables, so integrity is unenforced; alternatives use exclusive arcs or per-type tables. RED FLAG: claiming a normal FK can target multiple tables.
3NF versus BCNF and the overlapping-key gap
WHAT IT TESTS: precise grasp of functional dependencies and candidate keys. OUTLINE: BCNF requires every determinant be a superkey; 3NF allows exceptions for prime attributes. RED FLAG: claiming 3NF and BCNF are always equivalent.
Adjacency List versus Nested Set for hierarchies
WHAT IT TESTS: read-versus-write trade-offs in tree storage. OUTLINE: adjacency list is simple writes but recursive reads; nested set is fast subtree reads but costly writes. RED FLAG: ignoring recursive CTEs or the wide updates nested sets need.
Modeling one-to-many versus many-to-many relationships
WHAT IT TESTS: cardinality and junction-table modeling. OUTLINE: one-to-many uses a foreign key on the many side; many-to-many needs a junction table with two foreign keys. RED FLAG: storing comma-separated IDs instead of a junction table.
Normalizing a flat orders table to 3NF
WHAT IT TESTS: applying 1NF, 2NF, 3NF rules concretely. OUTLINE: split repeating data, remove partial dependencies, remove transitive dependencies, define keys. RED FLAG: jumping to tables without naming which dependency each step removes.