Database Internals

What is MVCC (Multi-Version Concurrency Control)?

MVCC (Multi-Version Concurrency Control) is a concurrency-control technique where the database keeps multiple versions of a row so readers see a consistent sna…

How Does the Hash Join Algorithm Work?

A hash join builds an in-memory hash table from the smaller input table keyed on the join column, then probes that table with each row of the larger input, mat…

How Does the Merge Join (Sort-Merge Join) Algorithm Work?

A merge join, also called a sort-merge join, works by taking two inputs that are already sorted on the join key (or sorting them first), then walking both sort…

What is Query Plan Caching in a Database?

Query plan caching is the database engine's practice of storing a compiled execution plan for a query the first time it is optimized, then reusing that same pl…

What is the Parameter Sniffing Problem in SQL?

Parameter sniffing is the situation where a database compiles and caches a query plan optimized for the specific parameter values passed on the first execution…

What Role Do Statistics and Histograms Play in Query Planning?

Statistics and histograms are metadata the database keeps about the distribution of values in each column, and the query optimizer uses them to estimate how ma…

What is a Write-Ahead Log (WAL) and Why Do Databases Use One?

A write-ahead log (WAL) is a durability mechanism where the database records every change as a sequential log entry on stable storage before it modifies the ac…

How Does the Write-Ahead Log Enable Crash Recovery?

The write-ahead log enables crash recovery because, on restart, the database replays log records to redo committed changes that never made it to data pages, th…

What is Checkpointing in a Database and Why is it Needed?

Checkpointing is the process of periodically flushing all dirty (modified but unwritten) data pages from memory to disk and recording a marker in the write-ahe…

Redo Log vs Undo Log: What is the Difference?

A redo log records the new value produced by a change so committed effects can be reapplied after a crash, while an undo log records the old value that existed…

What is Transaction Log Shipping and How is it Used?

Transaction log shipping is a high-availability technique where the write-ahead or transaction log records generated on a primary database are continuously cop…

How Does PostgreSQL Clean Up Dead Tuples?

PostgreSQL cleans up dead tuples — old row versions left behind by UPDATE and DELETE under MVCC — through VACUUM (run manually or by the autovacuum daemon), wh…

How Do Databases Detect Deadlocks?

Databases detect deadlocks by building a wait-for graph, where each transaction is a node and an edge points from a waiting transaction to the transaction hold…

What is a Bloom Filter and How Do Databases Use It?

A Bloom filter is a compact, probabilistic data structure that tests whether an element is possibly in a set or definitely not in it, letting a database quickl…

What is an LSM-Tree and How Does it Handle Writes?

A Log-Structured Merge-tree (LSM-tree) is a storage engine design that turns random writes into fast sequential appends by first buffering writes in an in-memo…

What is a B-Epsilon Tree and How Does it Differ from a B-Tree?

A B-epsilon tree (Bε-tree) is a write-optimized variant of a B-tree that attaches a message buffer to each internal node, so inserts, updates, and deletes are…

What is Write Amplification and Why Does it Matter?

Write amplification is the ratio between the amount of data actually written to physical storage and the amount of data the application logically intended to w…

What is External Sorting?

External sorting is a family of algorithms for sorting data too large to fit in main memory, most commonly external merge sort: split the data into memory-size…

What is a Write-Ahead Log (WAL) and Why Do Systems Use It?

A write-ahead log is an append-only file that records every change durably before it is applied to the actual data structures on disk, so a crash can never los…

How Does a Write-Ahead Log Relate to an LSM-Tree?

A write-ahead log provides crash durability for a single write, while an LSM-tree is the broader storage engine architecture that uses that WAL alongside an in…

How Do You Choose an Indexing Strategy at Scale?

Choosing an indexing strategy at scale means matching the index structure to the query pattern — B-tree indexes for range scans and ordered access, hash indexe…

What is Write Amplification and Why Does It Matter for Storage Engines?

Write amplification is the phenomenon where a single logical write from an application triggers a much larger amount of physical data actually written to durab…

What is Read Amplification in Storage Engines?

Read amplification is the ratio between the amount of data a storage engine actually reads from disk and the amount of data the application logically requested…

What is Space Amplification in Storage Engines?

Space amplification is the ratio between the actual disk space a storage engine consumes and the size of the logical, deduplicated data it is meant to represen…