I built this page for the moment you need an answer fast. You already know the deep dives exist. This page is the short version. One rule. One plain line. One small picture built from real file names and real commands. Every number here is the number from the deep dive it links to. 4 buffers, not "fast". A 100 MB sort limit, not "large". Read a card first. When a rule surprises you, follow its Deep dive ↓ link. It drops you into the full problem and diagram pair in the guided path. After this page you will be able to name the rule that explains a slow query, and know exactly where to go deep on it.
8 rules for a database that never loses a commit. One data directory. One log. 8 KB pages.
The log hits disk before the commit returns. The data page follows minutes later, at checkpoint.
Deep dive ↓ Level 0 · WAL first, commit secondOne commit, in order
Cost of durability: 1 sequential fsync of a few hundred bytes, not 4+ random 8 KB page writes. That trade is the entire design.
No index, or stale statistics, and the same query reads 1,250,000 pages instead of 4.
Deep dive ↓ Reading EXPLAIN, line by lineEXPLAIN (ANALYZE, BUFFERS): the two shapes
| Plan | Pages read | Time at 10M rows | Verdict |
|---|---|---|---|
| Index Scan · Buffers: shared hit=4 | 4 | 0.05 ms | ✓ healthy |
| Seq Scan · rows est. 1, actual 48,000 | ~1,250,000 | 3-8 s | ✗ stale stats, run ANALYZE |
Every UPDATE writes a new row version and leaves the old one behind. Until VACUUM removes it, every scan walks past the corpse.
Deep dive ↓ Level 3 · Vacuum, freeze, wraparoundOne page, before and after vacuum
Table "grows" with zero new rows. This is bloat.
Check it: n_dead_tup in pg_stat_user_tables.
On restart, PostgreSQL replays every WAL byte since the last checkpoint. That distance is a setting, not luck.
Deep dive ↓ Level 5 · max_wal_size is a checkpoint budgetmax_wal_size budgets the replay
Default budget: 1 GB. At 50 MB/s of WAL it fills in about 20 seconds, so a checkpoint fires about every 20 seconds and at most 1 GB ever replays. Replay time is that 1 GB divided by your replay speed. Measure the speed from the server log: the "redo starts at" line and the "redo done at" line carry timestamps, and their LSNs give you the bytes. Bytes divided by seconds is your replay speed. In pg_stat_checkpointer, num_requested outnumbering num_timed means the budget is too small for your write rate.
The OS page cache holds the second copy. Setting shared_buffers to 80% double-buffers everything and evicts the working set.
Deep dive ↓ Level 1 · Shared buffers & checkpoints64 GB of RAM, split the way production splits it
Proof it is working: Buffers: shared hit dominates shared read in EXPLAIN output.
The scan checks the visibility map before skipping the heap. A stale map turns it into an index scan plus one heap fetch per row.
Deep dive ↓ Level 2 · Index-only scans & the visibility mapThe visibility-map check, per row
Vacuum cannot remove any version newer than the oldest open snapshot. One idle-in-transaction session from Friday freezes cleanup all weekend.
Deep dive ↓ Level 3 · The cleanup horizonThe horizon is set by the oldest transaction
synchronous_commit = on + a sync standby means zero data loss on failover, paid for in commit latency on every single write.
Deep dive ↓ Level 4 · WAL streaming & sync standbysWhat the commit waits for
| Setting | Commit returns after | Lost on primary crash |
|---|---|---|
| synchronous_commit = on, no sync standby | local WAL fsync | 0 committed rows |
| + synchronous_standby_names set | standby confirms WAL received | 0, and the standby has it |
| synchronous_commit = off | nothing · ~3× WAL-writer delay | last ~600 ms of commits ✗ |
8 rules for lookups by ID at huge scale. The table file is the index. There are two logs, not one.
Redo rebuilds pages after a crash. The binlog feeds replicas and point-in-time recovery. Losing either one breaks a different promise.
Deep dive ↓ Level 0 · Redo + binlog two-phase commitOne commit, written twice: on purpose
Crash recovery replays redo. A replica replays the binlog. Two-phase commit is what keeps the two in agreement.
Rows live inside users.ibd in primary-key order. A lookup by ID is a walk down the file's own B+ tree, there is no separate heap.
Deep dive ↓ Level 2 · Change buffer & clustered indexshop/users.ibd: 16 KB pages, keys in order
Consequence: a secondary index stores the primary key, not a row pointer, so a wide PK is paid for in every index you add.
Almost every read is served from the InnoDB buffer pool. Size it to the working set, typically 70-80% of RAM on a dedicated server.
Deep dive ↓ Level 1 · Buffer pool & checkpoint ageWhere a read actually goes
Watch it: Innodb_buffer_pool_reads ÷ read_requests, physical reads should stay a small fraction of logical ones.
Redo is a ring. When uncheckpointed changes fill it, InnoDB stops accepting writes and flushes pages until there is room.
Deep dive ↓ Level 1 · Redo capacity & checkpoint ageCheckpoint age, as a share of redo capacity
Old versions live in undo until purge deletes them. A long-running transaction pins them all, and every read walks a longer version chain.
Deep dive ↓ Level 3 · Undo, purge & the history listOne row, three versions deep
History list length in SHOW ENGINE INNODB STATUS: hundreds is normal, millions is an incident, find the oldest open transaction.
Inserts into a secondary index whose page is not in memory are recorded in the change buffer and merged later, writes get faster, cold reads pay the merge.
Deep dive ↓ Level 2 · Change buffer & adaptive hashWrite now, merge when the page is next read
Wins on write-heavy, non-unique secondary indexes. It never applies to the clustered index or unique indexes, uniqueness must be checked now, not later.
A replica that is down longer than the binlog is kept cannot catch up, it must be rebuilt from a backup. Retention is a recovery decision.
Deep dive ↓ The log files, one by oneThe race every replica runs
Every transaction gets a global ID. A promoted replica knows precisely what it has applied, no guessing file names and byte positions at 3 AM.
Deep dive ↓ Level 4 · GTID & semi-sync replicationFinding position after a failover
✗ "mysql-bin.000042, position… 819203? 819230?", one wrong byte and the replica applies a transaction twice or skips it.
✓ Executed set 3E11FA47-…:1-10482, the new primary asks for exactly the GTIDs it is missing. Nothing else is ambiguous.
8 rules for documents at scale. Journal for crashes. Oplog for the replica set. Everything timestamped.
j:true waits for the journal on disk. The oplog is a separate, ordered record that secondaries tail, two logs, two promises.
Deep dive ↓ Level 0 · Journal vs oplogOne write, two destinations
Delete the journal and you lose the last ~60 s since checkpoint. Fall out of the oplog window and a secondary must resync from scratch.
Index keys are stored sorted field by field. Equality collapses to one range, sort rides free inside it, and a range ends the usable ordering, so range goes last.
Deep dive ↓ The ESR derivation, from the key layoutOne compound index, built the ESR way
totalDocsExamined ÷ nReturned = 1 means a perfect index. At 10× the index is weak. At 1,000× it is decoration.
Deep dive ↓ explain("executionStats"), annotatedexplain("executionStats"): read these three numbers
| nReturned | totalDocsExamined | Ratio | Verdict |
|---|---|---|---|
| 42 | 42 | 1.0× | ✓ index fits the query |
| 42 | 420 | 10× | low-cardinality leading field |
| 42 | 10,000,000 · COLLSCAN | - | ✗ no usable index · ~20 GB read at 10M docs |
WiredTiger eviction keeps the cache usable. If application threads get pulled into evicting, query latency spikes with no slow query to blame.
Deep dive ↓ Level 1 · WiredTiger cache evictionWiredTiger cache: the two thresholds that matter
Oplog size ÷ write rate = hours. A secondary down longer than the window cannot catch up, full resync, at full data size.
Deep dive ↓ Level 5 · The oplog window mathThe only oplog formula there is
Compare that one number to your longest maintenance. rs.printReplicationInfo() prints it directly as "log length start to end".
Checkpoints make the .wt files consistent. The journal only ever has to cover the gap since the last one, that gap is the recovery work.
Deep dive ↓ Level 3 · WiredTiger checkpoints & timestampsRecovery after a crash, on a timeline
No checkpoint design removes the journal's job, it shrinks it, from the whole dataset to one minute of writes.
A write acknowledged by one node can vanish when that node loses an election. A majority-acknowledged write exists on a majority, the next primary must have it.
Deep dive ↓ Level 4 · Elections, write & read concern3-node replica set, one write
Chunks split at ~128 MB. A chunk that cannot split (one shard key value holds it all) can never migrate, so one shard keeps growing while others idle.
Deep dive ↓ Level 5 · Chunk migration & jumbo chunksShard key choice, visible in chunk sizes
Balancer skips it forever. Cause is almost always a low-cardinality or ever-increasing key chosen at design time.
Before you leave, check this: run EXPLAIN (ANALYZE, BUFFERS) on your slowest query and read Buffers: shared hit. If the plan says Seq Scan on a big table, you found your next fix.