More in Databases & Architecture — page 6
What is a database page?
WHAT IT TESTS: storage fundamentals. OUTLINE: a page is a fixed-size block, often 8KB, holding multiple rows; databases read and write whole pages because disk and OS I/O are block-oriented, amortizing seek cost and matching the buffer pool unit.
Indexing a low-cardinality status column
WHAT IT TESTS: index selectivity intuition. OUTLINE: with three values each matches a third of rows, so the optimizer prefers a scan over costly random heap fetches; alternatives include partial indexes on rare values and composite indexes leading with status.
Optimizer picks nested loop over hash join
WHAT IT TESTS: optimizer reasoning. OUTLINE: 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
WHAT IT TESTS: matching index structure to access pattern. OUTLINE: 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?
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.