More in Backend Dev — page 13
What is the CAP theorem?
WHAT IT TESTS: grasp of fundamental distributed-systems limits. OUTLINE: Consistency, Availability, Partition tolerance; during a network partition you must choose between staying consistent or staying available.
How do you choose between relational and NoSQL databases?
WHAT IT TESTS: ability to match a data model to requirements. OUTLINE: relational gives schema, joins, and ACID for structured related data; document gives flexible schema and horizontal scale for varied or denormalized data.
How does a hash join handle memory overflow?
WHAT IT TESTS: understanding of query execution under memory pressure. OUTLINE: the build table is partitioned by hash and spilled to disk, then probe rows are partitioned the same way, and pairs are joined per partition.
Sorting data larger than memory
WHAT IT TESTS: external sorting. OUTLINE: 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.
Hash join versus sort-merge join
WHAT IT TESTS: choosing between two equi-join strategies. OUTLINE: 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…
Joining a large table with a small one
WHAT IT TESTS: matching join algorithm to data size. OUTLINE: with a tiny table the optimizer often picks a hash join, building a hash table on the small side in memory, then probing it once per row of the large table in a single pass.
Why developers read query plans
WHAT IT TESTS: basic EXPLAIN literacy. OUTLINE: the plan shows the operators the optimizer chose to run a query; developers read it to find why a query is slow; a common thing to look for is a full table scan where an index was expected.
Stages of executing a SELECT query
WHAT IT TESTS: the query pipeline. OUTLINE: parse the SQL into a tree, bind and validate against the catalog, optimize into a physical plan, then execute the plan operators fetching data and returning rows.
Using page LSN to decide redo
WHAT IT TESTS: idempotent redo via LSNs. OUTLINE: each page stores the LSN of its last applied change; during redo the engine reapplies a log record only if its LSN exceeds the page's LSN, meaning the change is not yet reflected on disk.
Row-oriented versus columnar storage
WHAT IT TESTS: storage layout for OLTP versus OLAP. OUTLINE: row stores keep whole rows together, ideal for point reads and writes; columnar stores keep each column contiguous, enabling reading only needed columns and strong compression, ideal for scans and…
The three phases of ARIES recovery
WHAT IT TESTS: crash-recovery internals. OUTLINE: Analysis rebuilds dirty-page and transaction tables from the last checkpoint, Redo replays all logged changes to restore state, Undo rolls back losers; Redo is idempotent via per-page LSN comparison so…
B+ Tree range queries across pages
WHAT IT TESTS: how B+ Trees map to disk. OUTLINE: 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.
Lifecycle of a single row update
WHAT IT TESTS: end-to-end write path. OUTLINE: 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.
Heap file versus clustered index
WHAT IT TESTS: physical table organization. OUTLINE: 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…
Database checkpoints with WAL
WHAT IT TESTS: balancing recovery time and runtime I/O. OUTLINE: 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…
Purpose of the Write-Ahead Log
WHAT IT TESTS: durability mechanics. OUTLINE: WAL records changes sequentially and is flushed to disk before commit; the rule is log first, then data pages may lag; on crash the database replays the log to recover committed work.
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.