Storage Engines
The physical implementation: heap files, clustered indexes, InnoDB vs MyISAM vs LSM.
Level: advanced
What is it?
A database storage engine implements the physical layout and persistence of records and indexes. Engines may use heap pages, B+ tree indexes, log-structured merge trees, write-ahead logs, buffer pools, and engine-specific concurrency/recovery rules.
Why does it matter?
The storage engine determines much of the database's write/read profile, durability, compression, indexing, and maintenance behavior. Logical SQL alone does not reveal these costs.
Mental model
Separate the logical database from physical storage. A query plan chooses operators; the storage engine decides how rows/pages/indexes are laid out and how changes survive failure.
How it works
- Heap or heap-like tables store records in pages without requiring every row to be physically ordered by a key.
- B+ tree indexes support point and range access with page-oriented balanced trees.
- LSM-tree designs batch writes through memory structures and immutable sorted runs, trading compaction/write behavior against read amplification.
- WAL or another recovery mechanism records sufficient information to restore a consistent state after crash.
- Engine choice and settings affect locking/MVCC, compression, durability, and operational maintenance; compare actual product documentation.
Steps
- Choose a logical schema and query workload.
- Inspect the engine's row/index layout and write path.
- Measure query plans, cache behavior, and write amplification using representative data.
- Plan backup, recovery, compaction/checkpoint/vacuum, and schema maintenance around the chosen engine.
Real-world usage
- Choosing between row-oriented transactional storage and columnar analytical storage.
- Explaining why storage engine and index design affect performance.
Common mistakes
- Assuming every SQL database has identical physical storage and concurrency behavior.
- Choosing an engine from generic benchmarks without matching the workload.
- Treating a replicated copy as a replacement for backups and recovery tests.
Interview questions
What is a storage engine?
The database subsystem responsible for physical record/index organization, persistence, and associated storage-level operations.
Why might an LSM tree suit write-heavy workloads?
It can batch and sequentialize writes, although compaction and read amplification impose their own costs.