Databases
229 bites tagged Databases — interview questions with model answers, and 60-second explainers.
How does a hash join handle memory overflow?
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. understanding of query execution under memory pressure.
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. external sorting.
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… choosing between two equi-join strategies.
Joining a large table with a small one
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. matching join algorithm to data size.
Why developers read query plans
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. basic EXPLAIN literacy.
Stages of executing a SELECT query
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. the query pipeline.
Using page LSN to decide redo
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. idempotent redo via LSNs.
Row-oriented versus columnar storage
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… storage layout for OLTP versus OLAP.
The three phases of ARIES recovery
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… crash-recovery internals.
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. how B+ Trees map to disk.
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. end-to-end write path.
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… physical table organization.
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… balancing recovery time and runtime I/O.
Purpose of the Write-Ahead Log
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. durability mechanics.
What is a database page?
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. storage fundamentals.
Indexing a low-cardinality status column
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. index selectivity intuition.
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. optimizer reasoning.
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. matching index structure to access pattern.
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. knowing index-only scans.
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. understanding indexes cost more than they give.
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. practical query debugging. confusing estimated cost with actual time.
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. how index type affects physical row storage. thinking a table can have many clustered indexes.
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. understanding composite index leftmost-prefix rules.
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. basic indexing intuition. indexing everything or ignoring the write and storage cost.
Get Databases bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.