All bites
The whole library, newest first. Filter by what you are here for, or pick a topic if you already know.
8668 bites
Page 47
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…
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.
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.
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…
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…
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.
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.
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.
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.