Internals · Performancesimulatorworking sethit ratioread amplificationwrite amplification

The Database Internals Playground

Five knobs — storage engine, rows, RAM, index, workload — and one thousand operations. The simulator turns the mechanisms of the internals layer into a page budget you can watch move: hits and misses, storage reads and writes, index depth, WAL, compaction, amplification and latency, with two configurations side by side.

▶ Interactive Interview question

Progress

Why this exists

The mechanism as the answer to a problem — read this before the name.

Problem
Each internals lesson explains one mechanism in isolation — the tree, the pool, the log, the compaction. Real latency is their product, and intuition about products is poor: does doubling RAM or switching engines help more for this table?
↓
Naive solution
Benchmark it. Load 100 million rows into two engines with three memory settings and run the workload for an afternoon each.
↓
Why it breaks
Eighteen configurations of a multi-hour benchmark is a week, the result depends on a hundred settings you did not vary, and at the end you know the numbers but still not the shape — why this one lost.
↓
Better idea
Build a cost model from the mechanisms themselves: page counts from tree height and page size, a hit ratio from RAM ÷ data and access skew, latency from 1 µs per hit and 100 µs per miss, write cost from pages dirtied and bytes compacted. It will be wrong in every digit and right in every shape.
↓
Internal mechanism
The playground does exactly that for 1,000 operations: the workload fixes the read/write mix, the engine and index fix how many pages each operation touches, RAM fixes what fraction of them miss, and the sums become bars, a storage strip, an index depiction and a paragraph.
↓
Trade-offs
A model is honest only about what it models. It ignores CPU, concurrency, network, TOAST, vacuum, fragmentation, and the fact that a real cache warms and cools. Use it to rank options and to predict direction, never to promise a number.
↓
Real database
PostgreSQL’s planner is the same idea run per query: seq_page_cost, random_page_cost, cpu_tuple_cost, effective_cache_size. RocksDB ships a compaction cost calculator; InnoDB exposes the same knobs as innodb_buffer_pool_size and innodb_io_capacity. Every capacity plan you will ever write is this model with your constants.

Choose your depth

The same mechanism at four altitudes. Start where you are; come back deeper.

What the knobs do

Storage engine picks how rows are kept: a B+ tree engine has a heap of pages plus an optional secondary index, and every write dirties a page; an LSM engine appends to a memtable and defers the work to compaction. Rows sets the data size (128-byte rows: 1K rows is 125 KB, 100M rows is 12 GB). RAM is given as a fraction of that data — 1%, 10%, more than 100% — because the ratio, not the absolute size, decides the hit ratio.

Indexes picks how a lookup finds a row: none (scan), a B+ tree (height ⌈log₂₀₀ rows⌉), or a hash index (one bucket, but no ranges); in the LSM engine the SSTables are sorted already, so the knob toggles bloom filters. Workload fixes the mix: read heavy is 900 reads and 100 writes, write heavy the reverse, mixed 500/500; three quarters of reads are equality lookups, the rest are 50-row ranges.

The model in one page

The simulator is a few hundred lines of arithmetic (src/db/internals/sim/workload.ts) and every number it shows is derived, not measured. The constants are the ones the lessons used: 8 KB pages, 128-byte rows so 64 rows fit a page, ~200 keys per index page, a 64 MB memtable, fanout 10 between LSM levels, 100 µs per SSD page miss, 1 µs per hit, 200 µs per fsync. Change a knob and the whole chain recomputes: data size → pages → tree height or level count → pages per operation → hit ratio → misses → latency, and on the write side pages dirtied → flushes → bytes → amplification.

It is an educational simulation. Real engines have CPU costs, prefetching, TOAST, vacuum, fragmented free space, concurrency, and caches that are warm for one query and cold for the next. The point of the playground is direction and ratio: which knob moves which metric, and by roughly how much.

How one point read is costed in each configuration

B+ tree engine, B+ tree index, 100M rows (height 4)
  root ──► internal ──► internal ──► leaf ──► heap page
  [hot]    [hot]        [hot]        [hit? 1 µs : 100 µs]  [hit? 1 µs : 100 µs]
  = 5 page accesses, 2 of which can miss

B+ tree engine, hash index
  hash(key) ──► bucket ──► overflow (0.2 avg) ──► heap page       = 2.2 page accesses
  range read ──► full scan of every data page                     = 1,562,500 pages at 100M rows

B+ tree engine, no index
  point read ──► full scan                                        = every data page

LSM engine, 4 levels, bloom filters on
  memtable ──► bloom L0..L3 [hot] ──► index block + data block for the level that has it
  = 4 + 2 × (1 + 3 × 0.01) ≈ 6.1 page accesses, ~2 of which can miss
  range read ──► one index block + one data block per level, merged

Reading the outputs

Start with the buffer hit ratio tile: it is the single number that decides whether latency is memory-shaped (tens of microseconds) or disk-shaped (hundreds). Then read the bars top to bottom. If page accesses dwarfs everything, the workload is scanning — no index, or a hash index on a range workload. If memory misses is close to page accesses, the data outgrew RAM. If storage writes is large under a read-heavy workload, the few writes are dirtying random index pages. If compaction bytes is the largest write component, the engine is an LSM and the deferred cost is visible.

The storage activity strip shows the same traffic as proportions: reads, page flushes plus WAL, compaction. A B+ tree engine under writes is mostly flushes; an LSM engine under writes is mostly compaction; a read-heavy configuration that does not fit RAM is mostly reads. The index depiction draws one lookup as boxes: green boxes are always resident, red boxes can miss with the probability shown. The paragraph under the bars is generated from the same numbers and says what the configuration is doing in words.

Read amplification ≈ 1 is a single page per lookup (a clustered primary-key hit). 5 is a height-4 tree plus heap. Thousands is a scan.
Write amplification ≈ 2–3 is WAL plus a coalesced page flush. 20–40 is random index leaves on a B+ tree engine with a cold pool, or leveled compaction across several levels.
Point vs range latency tells you which half of the workload the index serves; a hash index makes the gap enormous.
Write latency is fsync ÷ group size plus any leaf miss; a B+ tree with a cold pool pays ~100 µs of leaf miss per write that the LSM engine does not.

Five guided experiments

Pin the starting configuration as A, change one knob, and read the comparison table. Each experiment reproduces one internals lesson as numbers.

RAM decides. B+ tree engine, 100M rows, hash index, read heavy. Set RAM small: page accesses explode because the 225 range reads scan 1.5M pages each, and the equality reads miss half the time. Move RAM to large: the same page accesses, but the misses collapse and average latency falls by orders of magnitude. The plan did not change — only the hit ratio did. Lesson: The Buffer Pool.
The hash index and the range. B+ tree engine, 1M rows, medium RAM, read heavy. Compare index = hash against index = B+ tree. Point-read latency is slightly better with hash (2.2 pages vs 4); range latency is a full scan vs a leaf walk. Then set workload to write heavy and watch the gap shrink, because writes cost the same in both. Lesson: Hash Index Internals, B+ Tree Internals: Pages, Splits, Merges.
B+ tree vs LSM under writes. 100M rows, small RAM, B+ tree index, write heavy. Pin the B+ tree engine, switch to LSM: write amplification drops (random leaf flushes become sequential compaction), write latency drops from ~60 µs to ~15 µs, and read amplification rises — more pages per point read, a colder cache, and every range merging four levels. Lesson: Storage Engine Comparison: B+ Tree vs LSM Tree, Write, Read and Space Amplification.
Bloom filters. LSM engine, 100M rows, any RAM, read heavy. Compare index = none (no bloom filters) with index = B+ tree (filters on). Point reads go from probing all four levels (8 page accesses, 4 potential misses) to one level plus four resident filter blocks. Ranges do not change: filters cannot answer "anything between a and b". Lesson: Bloom Filters: Skipping Files That Cannot Contain the Key.
Small tables are free. Any engine, 1K rows, RAM small, no index, mixed. The "no index" scan is 16 pages and the 256 KB floor keeps them all resident: a table scan is ~16 µs and an index would not help. Now set rows to 1M with the same knobs and watch the scan become 15,625 pages of which a quarter miss — the moment "add an index" becomes true. Lesson: Sequential Scan, Page by Page, Should I Add an Index?.

What the model leaves out, deliberately

CPU: a page that is resident still costs comparisons per row, and a hash join or an aggregate can be CPU-bound with a perfect hit ratio; the simulator charges 1 µs per hit and nothing per row. Concurrency: the lock manager, MVCC version chains and group-commit queueing are absent except for the commit-group constant; see The Lock Manager. Clustering: the B+ tree engine assumes a heap with half-correlated ranges, not an InnoDB clustered index where the primary key lookup ends in the leaf; see Physical Layouts Compared: Heap + Secondary Index vs Clustered Index. Space: LSM space amplification, heap bloat and index bloat are not modelled; see UPDATE, DELETE and Dead Tuples. Warm-up: the hit ratio is a steady state; a restarted database starts at zero.

Each omission is a lesson elsewhere in the internals layer. The playground is the place where the mechanisms you have already read are multiplied together; when the multiplication surprises you, the lesson that explains the surprise is one link away.

Which knob moves which metric (direction only)

Knob	Page accesses	Hit ratio	Storage writes	Compaction	Latency
Rows ↑	↑ height, ↑ scans	↓ unless RAM scales	↑ (colder leaves)	↑ levels	↑
RAM ↑	=	↑	↓ (leaves coalesce)	=	↓
Index none → B+ tree	↓↓ (scan → lookup)	↑ (fewer pages cycle)	↑ (one more structure)	=	↓↓
Index B+ tree → hash	↑ if ranges	↓ if ranges	≈	=	point ↓, range ↑↑
Engine B+ tree → LSM	point ↑, range ↑	↓ (churn)	flushes → compaction	↑↑	write ↓↓, read ↑
Workload read → write	↓ reads, ↑ dirty pages	↓ (LSM churn)	↑↑	↑↑ (LSM)	fsync share

Key points

Latency is a product of mechanisms: pages per operation (engine, index, height) × miss probability (RAM ÷ data, skew) × 100 µs, plus fsync and CPU.
RAM as a fraction of data is what matters; the model’s hit ratio is (RAM ÷ data)^0.14, with index internal pages always resident and scans getting no skew benefit.
A B+ tree engine pays for writes in dirty 8 KB pages per structure; an LSM engine pays later in compaction bytes. Write amplification moves between them; read amplification moves the other way.
A hash index is a point-read optimisation and a range-read disaster; bloom filters are the LSM equivalent — they help equality and do nothing for ranges.
Pin A, change one knob, read the table. One knob at a time is the same discipline as one change per EXPLAIN.
Every number is an educational simulation. Trust the direction and the ratio, then measure the real thing.

Database Internals playground

Five knobs, one thousand operations. Every metric is derived from page counts, a working-set cache model and SSD latencies — the same arithmetic the lessons above walked through by hand.

Storage engine

Rows

RAM (of data size)

Indexes

Workload

Data

122.1 MB · 16K pages

RAM

12.2 MB · 10% of data

Buffer hit ratio

72.7%

Ops / 1,000

675 point · 225 range · 100 writes

Per 1,000 operations (log scale)

Page accesses9,400

Memory hits· 1 µs each7,404

Memory misses· 100 µs each1,996

Storage reads1,996 pages

Storage writes· flushes + WAL + compaction39 pages

WAL bytes· 100 fsyncs, group of 119.5 KB

Compaction bytes· not an LSM engine0 B

Read amplification

4× pages / point read

Write amplification

25× bytes / byte written

Latency: point / range

58.1 µs / 731.5 µs

Latency: write / avg op

231.1 µs / 226.9 µs

Storage activity

storage reads15.6 MB
page flushes + WAL312.3 KB
compaction0 B

One lookup: B+ tree, height 3

↓
root
200 separator keys
↓
internal 1
fanout 200
↓
leaf
key → row pointer
heap page
the row itself

green = always resident · red = can miss (27% of the time)

RAM holds 10% of the 122.1 MB data set. With skewed access that still catches 73% of random page reads; the remaining 27% cost 100 µs each and dominate the latency. A B+ tree of height 3 finds a row in 3 index pages plus one heap page; the 2 internal pages are always resident, so only the leaf and the heap page can miss. Ranges walk the linked leaves and then pay one heap page for roughly every other row. The 100 writes each append 152 + 48 bytes of WAL and pay 200 µs of fsync each (group size 1).

Educational simulation. 8 KB pages, 128-byte rows, ~200 keys per index page, working-set hit ratio = (RAM ÷ data)^0.14 with a 256 KB floor, 100 µs per SSD miss, 1 µs per hit, 200 µs per fsync, leveled compaction at ≈ 5.5× per level. Labels: B+ Tree = heap table plus optional secondary index; LSM = memtable plus leveled SSTables. Real engines differ in every constant and agree in every shape.

B+ Tree · 1M rows · RAM 10% · B+ tree index · read heavy

When to use — and when not

Use it when

A page-budget model fits when ranking options before a benchmark: more RAM vs a covering index vs a different engine for one workload shape.
When explaining to someone why a plan that did not change got slower, or why an LSM store is fast to write and slower to read.
When building intuition for the constants in EXPLAIN costs and in capacity planning.

Avoid it when

A model does not fit as a source of numbers for a design document; the constants are illustrative and the omissions (CPU, concurrency, clustering, bloat) can dominate.
It does not fit workloads the knobs cannot express: wide analytical scans with columnar storage, many-table joins, full-text or vector search.
It does not fit as a replacement for EXPLAIN (ANALYZE, BUFFERS) on the real query against real data.

Failure modes

Reading the latency digits as a prediction rather than a ratio, and sizing a server from them.
Concluding that a hash index is faster because point-read latency is lower, without looking at the range column of the same table.
Comparing engines at one RAM setting only: the B+ tree engine wins on writes once its leaves fit in memory, and the model shows that if you move the knob.
Forgetting that the compaction bytes of an LSM engine are background work that competes with reads for the same disk — the strip shows it, the read latency does not.
Treating the 256 KB RAM floor as a feature of the knob rather than a statement about small tables: at 1K rows every setting is "everything fits".