The cheat sheet

The cheat sheet · the whole site in 24 rules, on three screens

Each engine, in 8 rules. Scan it in a minute, then go deep.

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.

Best Practices in PostgreSQL

8 rules for a database that never loses a commit. One data directory. One log. 8 KB pages.

1WAL first, data file later

The log hits disk before the commit returns. The data page follows minutes later, at checkpoint.

Deep dive ↓ Level 0 · WAL first, commit second

One commit, in order

INSERT row 42→pg_wal/000000010000000000000003 · fsync→COMMIT returns ✓→checkpoint → base/16384/16397

Cost of durability: 1 sequential fsync of a few hundred bytes, not 4+ random 8 KB page writes. That trade is the entire design.

2An index only helps if the planner picks it

No index, or stale statistics, and the same query reads 1,250,000 pages instead of 4.

Deep dive ↓ Reading EXPLAIN, line by line

EXPLAIN (ANALYZE, BUFFERS): the two shapes

PlanPages readTime at 10M rowsVerdict
Index Scan · Buffers: shared hit=440.05 ms✓ healthy
Seq Scan · rows est. 1, actual 48,000~1,250,0003-8 s✗ stale stats, run ANALYZE

3Updates never overwrite: vacuum cleans up

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, wraparound

One page, before and after vacuum

Before: dead tuples pile up
row v1 ✗ deadrow v2 ✗ deadrow v3 · live

Table "grows" with zero new rows. This is bloat.

After, VACUUM reclaims the space
row v3 · livefree space · reusable

Check it: n_dead_tup in pg_stat_user_tables.

4Checkpoint distance bounds crash recovery

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 budget

max_wal_size budgets the replay

checkpoint ✓→WAL written since: this is what replays→crash · replay all of it

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.

5shared_buffers is ~25% of RAM: on purpose

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 & checkpoints

64 GB of RAM, split the way production splits it

shared_buffers · 16 GB (25%)OS page cache · ~44 GBwork_mem × connections · rest

Proof it is working: Buffers: shared hit dominates shared read in EXPLAIN output.

6"Index-only" is only true if the visibility map is fresh

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 map

The visibility-map check, per row

index leaf · key + pointer→visibility map: all-visible?→yes → answer from index ✓no → heap fetch ✗
EXPLAIN (ANALYZE, BUFFERS) … ← watch this line Heap Fetches: 0 ← healthy · in the hundreds = stale map, vacuum more

7One long transaction blocks vacuum for everyone

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 horizon

The horizon is set by the oldest transaction

txn 801 · open 3 days · idle in transaction→xmin horizon stuck at 801→every dead tuple since 801 stays ✗
SELECT pid, now() - xact_start AS age, state FROM pg_stat_activity ORDER BY age DESC; ← the top row owns your bloat

8A synchronous standby makes commit wait: price it

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 standbys

What the commit waits for

SettingCommit returns afterLost on primary crash
synchronous_commit = on, no sync standbylocal WAL fsync0 committed rows
+ synchronous_standby_names setstandby confirms WAL received0, and the standby has it
synchronous_commit = offnothing · ~3× WAL-writer delaylast ~600 ms of commits ✗

Best Practices in MySQL · InnoDB

8 rules for lookups by ID at huge scale. The table file is the index. There are two logs, not one.

1Two logs, two different jobs

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 commit

One commit, written twice: on purpose

COMMIT→#ib_redo0 · redo · physical page changesmysql-bin.000042 · logical row changes

Crash recovery replays redo. A replica replays the binlog. Two-phase commit is what keeps the two in agreement.

2The table file IS the primary-key index

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 index

shop/users.ibd: 16 KB pages, keys in order

root page · keys 1…1M→internal page→leaf page = the row itself ✓

Consequence: a secondary index stores the primary key, not a row pointer, so a wide PK is paid for in every index you add.

3The buffer pool is the database

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 age

Where a read actually goes

buffer pool hit · ~0.1 msmiss → read 16 KB page from .ibd · 10-100× slower

Watch it: Innodb_buffer_pool_reads ÷ read_requests, physical reads should stay a small fraction of logical ones.

4Checkpoint age near capacity = writes stall

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 age

Checkpoint age, as a share of redo capacity

age < 50% · writes flow ✓age → 100% · forced flush, write stalls ✗
SHOW ENGINE INNODB STATUS\G ← LOG section: "Log sequence number" minus "Last checkpoint"

5History list length is cleanup debt, in one number

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 list

One row, three versions deep

users.ibd · current row→undo_001 · previous version→undo_002 · older still · purge blocked ✗

History list length in SHOW ENGINE INNODB STATUS: hundreds is normal, millions is an incident, find the oldest open transaction.

6The change buffer defers secondary-index writes

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 hash

Write now, merge when the page is next read

INSERT→change buffer · "merge later"→page read → merge applied → index current

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.

7Binlog retention must beat your longest outage

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 one

The race every replica runs

replica down · day 0→binlog kept 7 days→back by day 6 → replays & catches up ✓back day 9 → binlog gone · full rebuild ✗
SHOW BINARY LOGS; ← oldest file's age vs your longest maintenance window

8GTIDs make failover position exact

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 replication

Finding position after a failover

File / position

✗ "mysql-bin.000042, position… 819203? 819230?", one wrong byte and the replica applies a transaction twice or skips it.

GTID

✓ Executed set 3E11FA47-…:1-10482, the new primary asks for exactly the GTIDs it is missing. Nothing else is ambiguous.

Best Practices in MongoDB

8 rules for documents at scale. Journal for crashes. Oplog for the replica set. Everything timestamped.

1Journal is durability, oplog is replication

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 oplog

One write, two destinations

insert on primary→journal/WiredTigerLog.0000000123 · crash safetylocal.oplog.rs · secondaries tail this

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.

2ESR: Equality, Sort, Range, in that order

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 layout

One compound index, built the ESR way

status: 1 · equality→createdAt: -1 · sort→amount: 1 · range
✓ no SORT stage in the plan✗ range first → scans every key in the range, then sorts in memory

3The examined-to-returned ratio is the verdict

totalDocsExamined ÷ nReturned = 1 means a perfect index. At 10× the index is weak. At 1,000× it is decoration.

Deep dive ↓ explain("executionStats"), annotated

explain("executionStats"): read these three numbers

nReturnedtotalDocsExaminedRatioVerdict
42421.0×✓ index fits the query
4242010×low-cardinality leading field
4210,000,000 · COLLSCAN-✗ no usable index · ~20 GB read at 10M docs

4When the cache is full of dirty pages, your threads evict

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 eviction

WiredTiger cache: the two thresholds that matter

cache ~50% of RAM − 1 GBdirty bytes climb → eviction pressureapp threads evicting ✗ latency spike
db.serverStatus().wiredTiger.cache ← "bytes currently in the cache" + dirty bytes

5The oplog window is your maximum allowed downtime

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 math

The only oplog formula there is

50 GB oplog÷2 GB/hour of writes=25 hours of window

Compare that one number to your longest maintenance. rs.printReplicationInfo() prints it directly as "log length start to end".

6A checkpoint every ~60 s bounds the crash

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 & timestamps

Recovery after a crash, on a timeline

checkpoint ✓ collection-*.wt consistent→journal replays ≤ ~60 s of writes→node rejoins the set

No checkpoint design removes the journal's job, it shrinks it, from the whole dataset to one minute of writes.

7w:majority is what survives an election

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 concern

3-node replica set, one write

primary · has itsecondary 1 · has it ✓secondary 2 · not yet→2 of 3 = majority · survives any single election ✓
db.orders.insertOne(doc, { writeConcern: { w: "majority", j: true } })

8A jumbo chunk cannot move, and blocks balancing

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 chunks

Shard key choice, visible in chunk sizes

Good key: chunks split & move
64 MB96 MB128 MB → splits ✓
Bad key: one value, one chunk
jumbo · 900 MB · cannot split ✗

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.