Database Internals: Indexes, Transactions, and Plans

A database index is an auxiliary structure that helps find rows without scanning every table page.

An interactive concept flow for how a b-tree index narrows a database search, from its assumptions through the main operation and boundary checks.

Follow the key state changes and boundary checks involved in how a b-tree index narrows a database search.

Use EXPLAIN to predict scans and joins, then compare row estimates with execution observations on a safe SELECT query.

A query planner estimates alternative ways to produce a result and chooses one using statistics and cost assumptions.

An interactive concept flow for reading a query plan as an execution hypothesis, from its assumptions through the main operation and boundary checks.

Follow the key state changes and boundary checks involved in reading a query plan as an execution hypothesis.

A transaction groups operations under a database contract such as atomicity and consistency.

Follow the key state changes and boundary checks involved in transactions, isolation, and mvcc snapshots.

Explain the model, execution steps, complexity, and limits of transactions, isolation, and mvcc snapshots.