One rule runs the whole path: no diagram without its problem, no problem without its diagram. After this path you will be able to walk any of the 18 problems from the symptom to the check that proves it. Mark levels as understood to track progress.
The pair template, every pair on this path follows it
A commit must survive a power cut, even though the data page is still only in memory.
The data page cannot be forced to disk on every commit, that is far too slow, yet a committed row must never be lost.
Situation, the physical organization, drawn for this problem
$ pg_waldump pg_wal/000000010000000000000003 | head -3 rmgr: Heap len (rec/tot): 59/ 59, tx: 801, lsn: 0/03A4F8C0 desc: INSERT off 3 flags 0x00 ← the change, recorded before the page rmgr: Transaction len (rec/tot): 24/ 24, tx: 801 desc: COMMIT 2026-11-15 09:41:07.221 PST ← this record fsynced = the row is durable SELECT pg_current_wal_lsn(); → 0/03A4F928
What proves health: commit records appear in the segment in order, and pg_wal size is stable across a day. Red flags: archive_status/ accumulating .ready files (archiving is failing and segments cannot be recycled); replay_lag in pg_stat_replication climbing at a constant write rate (the standby's apply, not the network, is behind).
Root cause: WAL archiving fails, or a dead standby / replication slot holds segments past the reach of every checkpoint.
Blast radius: pg_wal/ grows until the filesystem reaches 100%, at which point the primary panics and stops accepting writes. A durability mechanism becomes the outage, and recovery is blocked until space is freed without deleting held segments by hand.
Mitigation: Alert on pg_wal size and per-slot lag in bytes; drop dead slots; fix archiving before it is an emergency; keep pg_wal on its own filesystem so it cannot starve the data files.
InnoDB and the binary log are two separate logs; a crash must never leave a row in one and not the other.
Situation, the physical organization, drawn for this problem
SELECT @@innodb_flush_log_at_trx_commit, @@sync_binlog; → 1, 1 ← the only fully safe pair → 2, 0 ← inherited config: up to ~1 s of commits losable per crash SHOW ENGINE INNODB STATUS\G (LOG section) Log sequence number 9182736455 ← redo position (LSN) Log flushed up to 9182736455 ← equal to LSN = redo fully flushed
What proves health: the variable pair reads 1, 1 and 'Log flushed up to' tracks the sequence number. Red flags: either variable relaxed on a server holding real data; gtid_executed differing between source and replica after a failover, the set difference is exactly the transactions one side has and the other does not.
Root cause: Durability settings relaxed for a benchmark or copied from a tuning blog, then never restored.
Blast radius: An OS crash loses up to ~1 second of commits from redo and/or the binlog. Because nothing errors, replicas diverge quietly and the divergence surfaces days later as missing or phantom rows.
Mitigation: flush=1 + sync_binlog=1 on anything holding real data; keep the pair under config management so a benchmark cannot ship it; compare gtid_executed on both sides after every failover.
Two different logs get confused constantly: one protects against a crash on this node, the other keeps the replica set identical.
Situation, the physical organization, drawn for this problem
rs.printReplicationInfo() configured oplog size: 10240 MB log length start to end: 26.4 hours ← the window: max survivable downtime oplog first event time: Sun Nov 15 2026 08:12:44 GMT-08:00 oplog last event time: Mon Nov 16 2026 10:37:02 GMT-08:00 db.serverStatus().wiredTiger.log ← journal flush + commit latency lives here
What proves health: the window (26.4 h here) exceeds your longest planned maintenance with margin. Red flags: window shrinking month over month at the same oplog size (write rate is eating it); per-member lag in rs.status() growing linearly, that is apply throughput, not network.
Root cause: Journal and data files share one contended volume, so journal fsyncs queue behind checkpoint writes; or writes are acknowledged with w: 1 and never reach a majority before an election.
Blast radius: Either every j:true write inherits the volume contention (commit latency degrades instance-wide), or acknowledged writes roll back in an election and land in rollback/, application-level data loss on a cluster that reports itself healthy.
Mitigation: Put the journal on its own volume; use { w: 'majority', j: true } for any write that must survive an election; alert on oplog window hours and lag slope, not just lag.
Every page a query needs is either already in RAM (~100 ns to use) or must be read from disk (~80 µs on NVMe, ~10 ms on a hard disk), a 1,000× to 100,000× difference decided entirely by what the cache kept and what it evicted.
When a backend needs a page that is not cached, something else must leave, and if the victim is dirty, someone must write it first.
Situation, the physical organization, drawn for this problem
SELECT num_timed, num_requested, buffers_written FROM pg_stat_checkpointer; num_timed = 812 num_requested = 37 ← requested are 4% of all checkpoints: timed still dominate, healthy SELECT buffers_clean, maxwritten_clean, buffers_alloc FROM pg_stat_bgwriter; buffers_alloc climbing fast ← constant page loading = misses, not hits
What proves health: timed checkpoints dominate, requested are a small share, and buffers_alloc is flat. Red flags: num_requested outnumbering num_timed; latency graphs that spike on the checkpoint interval.
Root cause: The working set outgrew the cache, or checkpoint settings (small max_wal_size, short checkpoint_timeout) force write bursts faster than the bgwriter's steady pace.
Blast radius: Foreground queries pay eviction and dirty-write cost directly: p99 latency sawtooths in sync with checkpoints across the whole instance, including queries that touch none of the hot data.
Mitigation: Size shared_buffers around 25% of RAM; raise max_wal_size to stretch checkpoints; tune bgwriter aggressiveness; verify with buffers_* deltas over a peak hour, not by feel.
Dirty pages can only be overwritten in the redo log after they are flushed; if flushing falls behind writes, the log wraps into unwritten work.
Situation, the physical organization, drawn for this problem
SHOW ENGINE INNODB STATUS\G (LOG + FILE I/O sections) Log sequence number 9182736455 Last checkpoint at 9174011223 Checkpoint age = 8,725,232 bytes of redo not yet flushed to data files Modified db pages 61,204 ← dirty pages in the buffer pool right now Pending normal aio writes: 0 ← nonzero + growing = flush path saturated
The derived check: checkpoint age ÷ innodb_redo_log_capacity. Under ~50% is headroom; sustained above ~75% means flushing cannot match the write rate. Red flags: 'Modified db pages' large and still growing at constant load; pending writes queuing in the FILE I/O section.
Root cause: Redo capacity sized for the average write rate instead of the peak, or flush throughput (storage) lower than the dirty-page generation rate.
Blast radius: Write latency spikes at peak load, InnoDB deliberately throttles all writers to protect crash recovery. In the worst case, near-stalls on the primary during the highest-value traffic of the day.
Mitigation: Raise innodb_redo_log_capacity (resizable online in 8.0.30+); alert at 75% checkpoint age; if age stays high after resizing, the bottleneck is flush throughput, fix storage, not settings.
Reads and writes happen in the cache; when it fills with dirty pages, eviction, not disk speed, becomes the latency.
Situation, the physical organization, drawn for this problem
db.serverStatus().wiredTiger.cache "bytes currently in the cache" : 15.8 GB "tracked dirty bytes in the cache" : 4.6 GB ← ~29% of cache: above the 20% trigger "pages evicted by application threads": 182334 ← must be 0; nonzero = users are paying eviction cost "pages evicted by worker threads" : 9.1M ← background eviction: healthy
What proves health: dirty bytes under the trigger and application-thread evictions at exactly zero. Red flag: the app-thread eviction counter moving at all, it is the single most diagnostic number in WiredTiger serverStatus output.
Root cause: The working set or the write burst exceeds what the cache can hold and clean at the rate operations dirty it, eviction workers fall behind and the debt is handed to application threads.
Blast radius: Instance-wide multi-second operation stalls while CPU and disk utilisation look unremarkable, the hardest WiredTiger incident to recognise, because every standard dashboard says the hardware is fine.
Mitigation: Reduce dirty-page generation (batch writes, fix the scan that churns the cache), add RAM or shrink the working set; tune eviction threads only with serverStatus evidence; alert on application-thread evictions > 0.
Every index adds one B-tree write to every INSERT and UPDATE on its table, forever; in exchange it cuts a lookup from N pages to about 3. The staff skill is pricing both sides of that trade in numbers, not adjectives.
A normal index scan still fetches the heap page for every row, mostly to check visibility, random I/O that dwarfs the index walk on big lookups.
Situation, the physical organization, drawn for this problem
-- healthy table: Index Only Scan using users_email_idx on users Heap Fetches: 0 ← visibility map current: truly index-only Buffers: shared hit=4 ← 4 pages total for the lookup -- same plan, update-heavy table: Index Only Scan using users_email_idx on users Heap Fetches: 48,213 ← the plan lies by omission: 48k heap fetches behind it
Read the Heap Fetches line, not the scan type. The planner prints 'Index Only Scan' in both cases; only the fetch count separates a 4-page lookup from a 48,000-fetch one. Red flag: Heap Fetches in the hundreds on any top-10 query.
Root cause: Autovacuum is not keeping the visibility map current on an update-heavy table, so the per-page 'all visible' bits are unset and every row needs a heap visit to confirm visibility.
Blast radius: The covering index delivers almost none of its promised win while its full write cost remains, read latency stays high on the hot query and write throughput is permanently lower than before the index was added.
Mitigation: Tune autovacuum per table (lower scale factor on the hot tables) so the visibility map stays current; track Heap Fetches on the top queries; only widen indexes after the map is proven current.
If every UPDATE rewrites every index entry, a hot row with 4 indexes costs 5 writes and floods vacuum with dead index entries.
Situation, the physical organization, drawn for this problem
SELECT relname, n_tup_upd, n_tup_hot_upd, round(100.0*n_tup_hot_upd/NULLIF(n_tup_upd,0)) AS hot_pct FROM pg_stat_user_tables ORDER BY n_tup_upd DESC; sessions | upd 12,400,000 | hot 11,900,000 | 96% ← HOT working counters | upd 9,800,000 | hot 41,000 | 0.4% ← HOT broken: indexed column in SET, or fillfactor 100
What proves health: hot_pct above ~90% on tables whose updates avoid indexed columns. Red flag: hot_pct under 50% on an update-dominated table, before touching autovacuum, check the SET list of the top updater and the table's fillfactor.
Root cause: An indexed column appears in the UPDATE's SET list, or fillfactor 100 leaves no free space on the page for the new tuple version, either one breaks HOT eligibility.
Blast radius: Update cost on the hottest table multiplies 5-10×, dead index entries multiply vacuum work by the same factor, and bloat accelerates, while every dashboard attributes it to 'growth'.
Mitigation: Set fillfactor 70-80 on update-heavy tables; keep indexed columns out of hot UPDATE statements (split the row if necessary); alert on hot_pct below ~50%.
Inserting into a secondary index whose page is not in memory forces a random disk read for every row, unless the change can be deferred.
Situation, the physical organization, drawn for this problem
SHOW ENGINE INNODB STATUS\G (INSERT BUFFER AND ADAPTIVE HASH INDEX) Ibuf size 1, free list len 6192, seg size 6194 ← buffered changes waiting to merge Hash table size 4425293, node heap has 812 buffer(s) 15230.44 hash searches/s, 402.11 non-hash searches/s ← AHI serving 97% of searches: it is load-bearing
What proves health: ibuf size fluctuates but trends flat, merges keep pace with inserts. Red flags: ibuf size growing for days without merging (the merge debt will land on readers); a hash-search ratio near 100% on a server where someone proposes disabling AHI from a blog post.
Root cause: Write bursts into cold secondary indexes defer merge work; the bill arrives attached to whichever reads touch those pages next.
Blast radius: Reads slow down after write bursts although the read workload did not change, incident effort goes to the wrong queries, and the deferred-merge mechanism that caused it is invisible in per-query statistics.
Mitigation: Leave the change buffer enabled for write-heavy non-unique secondary indexes; size the buffer pool so hot index pages stay resident; watch ibuf size trend after every bulk-load job.
Readers never block writers because old row versions are kept, and every kept version is storage a background process (VACUUM, purge, WiredTiger reconciliation) must remove later, at a rate one long transaction can reduce to exactly zero.
Dead tuples are reusable space only after vacuum, and every transaction ID is borrowed from a finite 32-bit counter that must be recycled by freezing.
Situation, the physical organization, drawn for this problem
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC; appdb | 1,204,551,930 ← past 1 billion: anti-wraparound territory, act now appdb | 84,120,553 ← healthy: freeze is keeping up SELECT pid, now()-xact_start AS age, state FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 5; ← the oldest row here is usually the reason vacuum cannot advance
What proves health: age(datfrozenxid) flat or falling week over week. Red flags: the age trending up on any database; an 'idle in transaction' session older than your longest legitimate report.
Root cause: A long-lived transaction (classically 'idle in transaction' from an app server) pins the vacuum horizon, and cold tables are never vacuumed because nothing in them changes, so their freeze age only ever moves in one direction.
Blast radius: Bloat grows on every table the blocker touches; in the limit the database enters protective read-only mode, the one PostgreSQL failure that stops all writes by design, during business hours, with a multi-hour vacuum as the only exit.
Mitigation: Set idle_in_transaction_session_timeout; alert on age(datfrozenxid) trend per database; per-table autovacuum on hot tables; scheduled freeze vacuum for large cold tables.
A reader snapshot rebuilds old row versions from undo; undo can only be purged when the oldest open read view no longer needs it.
Situation, the physical organization, drawn for this problem
SHOW ENGINE INNODB STATUS\G (TRANSACTIONS section) History list length 1,842,311 ← purge is pinned; this is the incident ---TRANSACTION 4213765, ACTIVE 6h 12min, 0 rows modified ← the cause: one read view SELECT trx_id, trx_started, trx_state FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 5; ← oldest first: the top row is the suspect
What proves health: history list length drains back to the hundreds after load peaks. Red flags: length above ~10k and still growing; an innodb_trx row older than your longest legitimate transaction.
Root cause: One long transaction or forgotten session holds the oldest read view, so the purge thread is forbidden from freeing any undo that view might still need.
Blast radius: Undo tablespaces grow without bound, every consistent read on the instance walks longer version chains, and read latency degrades for all users, caused by a single idle connection.
Mitigation: Kill or fix the oldest transaction; move analytical reads to a replica; alert on history list length above ~10k and growing, with the oldest trx_started in the alert text.
The .wt files are only consistent at checkpoints; between them, versions live in cache at timestamps, and a long-lived snapshot pins all of them.
Situation, the physical organization, drawn for this problem
db.serverStatus().wiredTiger cache: "oldest timestamp pinned" : present, age growing ← a snapshot is holding versions checkpoint: last checkpoint time lengthening week over week ← more dirty data per checkpoint transaction: snapshot windows open for hours ← find the reader: aggregation? change stream? backup?
What proves health: checkpoint duration stable and the oldest pinned timestamp seconds-to-minutes old. Red flags: pinned-timestamp age and checkpoint duration trending up together, that pairing points at a long-lived reader, not at the storage layer.
Root cause: A long-lived snapshot, an hour-long aggregation, a change stream, a backup read, pins the oldest timestamp, and WiredTiger may not free any version newer than it.
Blast radius: Version cleanup stops while writes continue: cache fills with versions nobody can reclaim, eviction stalls application threads, and crash recovery lengthens by exactly the journal accumulated since the last clean checkpoint.
Mitigation: Bound snapshot lifetimes in application code; run long analytical reads against secondaries; alert on oldest-pinned-timestamp age and checkpoint duration together.
A replica that lags, or a failover that loses acknowledged writes, is worse than no replica, because everyone trusted it.
Async replication can lose the last transactions in a crash; sync replication can block every commit if the standby disappears.
Situation, the physical organization, drawn for this problem
SELECT client_addr, state, sync_state, replay_lag FROM pg_stat_replication; 10.0.1.52 | streaming | sync | 00:00:00.003 ← healthy synchronous standby 10.0.1.53 | streaming | async | 00:00:41.208 ← 41 s of acknowledged writes at risk in a failover SHOW synchronous_standby_names; ← empty = nobody is synchronous, whatever the runbook says
What proves health: sync_state matches the intended design and replay_lag at peak load is inside the loss budget the business accepted. Red flags: a sync standby in the same failure domain as the primary; lag measured only at idle times.
Root cause: The sync/async choice was left at the default instead of being made as an explicit loss decision, or the synchronous standby shares the primary's availability zone, so one zone event kills both.
Blast radius: Two failure shapes: commits hang instance-wide when a synchronous standby dies (an outage caused by the safety feature), or a failover silently loses seconds of acknowledged writes (data loss caused by its absence).
Mitigation: Quorum ANY 1 of two standbys in different zones; alert on replay_lag at peak write load; measure the commit p99 delta before and after enabling sync so the price is known, not discovered.
Position-based replication breaks on failover: which transactions did the new source have that the old one never sent?
Situation, the physical organization, drawn for this problem
SHOW STATUS LIKE 'Rpl_semi_sync_source_status'; → OFF ← plugin installed, protection OFF: it timed out and degraded → ON ← actually waiting for replica ACKs SELECT @@gtid_executed; ← run on source and replica; the difference is the failover gap SHOW REPLICA STATUS\G ← Seconds_Behind_Source + applier state
What proves health: Rpl_semi_sync_source_status is ON now, not at install time. Red flags: the status OFF with the enabled variable ON; applier lag (SQL thread) growing while I/O lag stays zero, the replica receives fine and cannot apply fast enough.
Root cause: Semi-sync degraded to asynchronous after a timeout event, and nothing alerted on the status variable, the system's belief about its durability diverged from its actual durability.
Blast radius: A failover loses writes that every dashboard and runbook claimed were replicated; the loss is discovered later as application-level inconsistencies, when the window to reconcile cheaply has closed.
Mitigation: Alert on Rpl_semi_sync_source_status != ON; use the AFTER_SYNC wait point; verify GTID set differences as a scripted step of every failover, before promoting.
When the primary dies, the set must elect a replacement that actually holds the acknowledged writes, automatically, at 3 AM.
Situation, the physical organization, drawn for this problem
rs.status() // per member: stateStr + optime lag db1 PRIMARY optime lag 0s ← the writer db2 SECONDARY optime lag 1s ← a viable winner db3 SECONDARY optime lag 47s ← if this member wins an election, 47 s of writes are at risk ls $dbPath/rollback/ ← any file here = writes that were acknowledged and then rolled back
What proves health: all members within a second or two of optime and an empty rollback/ directory. Red flags: any election at all (each deserves a postmortem); a member whose lag would make it a data-losing winner.
Root cause: A network partition leaves a primary in the minority still accepting w: 1 writes until it steps down; or read-your-own-write flows are served from secondaries with default concerns, so users read state that a later election erases.
Blast radius: Acknowledged writes disappear after failover, the rollback files are the physical evidence, and users report phantom data loss against a cluster whose own health checks are green.
Mitigation: w: 'majority' + j: true for writes that must survive elections; readConcern majority for read-your-write flows; postmortem every election and alert on rollback file count.
One server ends three ways: the log cannot recycle, the data must split, or a copy falls outside the window where catching up is possible.
WAL segments can only be recycled after a checkpoint; write faster than checkpoints complete and the safety log becomes the outage.
Situation, the physical organization, drawn for this problem
SELECT num_requested, num_timed FROM pg_stat_checkpointer; num_requested = 214 num_timed = 30 ← 88% of checkpoints are size-triggered: the budget is the problem SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir(); → 14 GB ← current pg_wal footprint; compare against the filesystem, not against zero
What proves health: timed checkpoints dominate and pg_wal size is a known, bounded share of its filesystem. Red flags: num_requested in the majority; pg_wal growth during any archiving error, at production write rates that is a countdown, measured in hours.
Root cause: max_wal_size left at an install-era default (1 GB thinking) while the write rate grew by orders of magnitude, or archiving broke, removing the only exit for segments.
Blast radius: Either checkpoint I/O storms every few minutes indefinitely, or the terminal case: pg_wal fills the filesystem and the primary panics and stops, a full write outage caused by the durability log itself.
Mitigation: Size max_wal_size to hold 1-2 hours of peak WAL; alert on the requested:timed checkpoint ratio and on pg_wal as a share of its filesystem; treat any archive failure as urgent at high write rates.
Shards only help if data spreads evenly; a range that cannot split migrates forever or never moves at all.
Situation, the physical organization, drawn for this problem
sh.status() // chunk distribution per shard users shard01: 412 chunks shard02: 38 chunks ← 11:1 skew: the balancer is losing jumbo chunks flagged: 7 ← unsplittable ranges; migration skips these db.users.getShardDistribution() ← per-shard data + doc counts to confirm
What proves health: chunk counts within ~10% across shards and zero jumbo flags. Red flags: skew growing while the balancer reports active; any jumbo count above zero on a collection still receiving writes.
Root cause: The shard key was chosen from the data model, monotonic (timestamps, ObjectIds) or low-cardinality (one whale tenant), instead of from the top five queries the system actually runs.
Blast radius: One shard saturates while the rest of the cluster idles; every remedy that does not change the key distribution (more shards, bigger nodes) changes nothing, and the eventual fix is a data migration under production load.
Mitigation: Choose shard keys from query patterns before launch; prefer hashed or compound keys where locality is not required; alert on chunk-count skew and jumbo count; use refineShardKey where the version supports it.
A secondary catches up by replaying the oplog; entries older than the window are gone, and gone means a full resync.
Situation, the physical organization, drawn for this problem
rs.printReplicationInfo() configured oplog size: 5120 MB log length start to end: 3.1 hours ← shorter than the 4 h maintenance planned for Saturday oplog first event time: Fri Nov 14 2026 22:03:11 GMT-08:00 oplog last event time: Sat Nov 15 2026 01:11:48 GMT-08:00 ← window = last − first, in wall-clock hours
What proves health: the printed window exceeds your longest planned outage with margin, recomputed at current write rates. Red flag: the window falling month over month while the oplog size never changed, growth is spending the budget.
Root cause: The oplog was sized once, as a percentage of disk at install time, and never recomputed after write volume grew, the window is a rate-dependent quantity being managed as a static one.
Blast radius: A routine 4-hour maintenance exceeds a 3-hour window; the member returns to a full initial sync during Monday peak traffic, hours of extra load on the primary and a second full copy of the dataset moving across the network.
Mitigation: Size the oplog so the window exceeds the longest planned maintenance plus margin; resize live with replSetResizeOplog; recompute the window monthly against the current write rate and alert when it falls below your maintenance bound.
Three structures decide nearly every performance number on this page. The data page is the unit of all I/O. The B+ tree is the reason lookup cost is logarithmic. The MVCC version chain is the reason reads do not block, and the reason cleanup exists. Each diagram is the real layout, not a simplification of it.
Databases never read or write one row at a time. They move whole pages between disk and memory.
So the page layout decides speed, waste, and how versions are tracked. If you understand the page, you understand most of the rest.Pick a database first. Then tap each part of the page.
Each part has one job.
A sequential scan of a 10-million-row table reads ~1,250,000 pages. A B+ tree lookup of the same table reads 3.
The derivation is arithmetic, not convention: an 8 KB internal page holds roughly 400 key-pointer pairs (fanout ≈ 400), so depth = log400(rows). 400² = 160,000 rows at depth 2; 400³ = 64 million at depth 3; 400⁴ = 25.6 billion at depth 4. Internal pages hold only guide keys; leaf pages hold keys plus row pointers. That is the entire mechanism, every index behavior on this page, including the ESR rule in the reference atlas, follows from how these pages store keys.Type a number and watch the lookup walk: root, internal, leaf. Only 3 pages are read, no matter how big the table file gets.
The consequence: at 1 billion rows the tree is about 4 levels deep. That is one additional page read versus 10 million rows. A missing index is not a percentage regression. It is a change of complexity class, from O(log N) page reads to O(N). That is why the same query passes QA at 10K rows and pages the on-call at 10M, with no code change in between.
See the same lookup on a B-tree next to a B+tree, and a range scan gliding along the leaf chain: Data Structure Simulators. It runs entirely in your browser.
Transaction B is reading a balance while transaction A changes it. Locking the row would freeze B; overwriting would corrupt B's total.
Fix (MVCC): never overwrite. A creates a new version; B keeps reading the version from its snapshot. Old versions are removed later, by VACUUM in PostgreSQL, by purge of the undo log in InnoDB.One account balance starts at 100. A changes it to 150. B started reading before A committed. So B should still see 100. Run the steps in order:
Same trick, 3 mechanisms: PostgreSQL stores both tuple versions in the heap file until VACUUM. InnoDB stores the new version in users.ibd and rebuilds the old one from undo records. WiredTiger keeps timestamped versions in its cache.
Same structure as the path, compressed. The bold line is the one problem. Then the diagram of the real structures, the mechanism, the tradeoff, and where it breaks plus the exact check. Search any word. Try "crash", "wal", "ibd", "oplog", "slow", "freeze", "purge".
Problem: A table must stay fast to scan even when some rows hold huge values.
Diagram, one problem, one diagram
Every table is one file, base/
Tradeoff: TOAST keeps scans fast but a wide-row read pays an extra fetch; compress first, out-of-line second.
What breaks & the check: Huge values inline in your mental model only, check pg_column_size() and TOAST size in \d+; a bloated TOAST table shows up as slow point reads on wide rows.
Problem: Looking up by primary key should need no separate structure at all.
Diagram, one problem, one diagram
With innodb_file_per_table (default), each table is one .ibd file whose pages form the primary-key B+ tree: rows physically sit in ID order. Sequential IDs append at the end; random IDs insert all over the file and split pages.
Tradeoff: Clustered storage makes PK lookups one walk, but every secondary index stores the PK and random PKs fragment the whole table.
What breaks & the check: Random UUID PKs → page splits and a file far bigger than the data. Detect: SHOW TABLE STATUS (Data_free) and fragmentation in information_schema.
Problem: Flexible documents shouldn't cost flexible amounts of disk.
Diagram, one problem, one diagram
Each collection is one WiredTiger file (collection-*.wt), each index another (index-*.wt). On disk everything is block-compressed (Snappy by default); in the WiredTiger cache it is uncompressed. _mdb_catalog.wt maps names to files.
Tradeoff: Compression saves disk and I/O but costs CPU; cache holds the uncompressed form, so RAM, not disk size, is the working-set limit.
What breaks & the check: Assuming disk size equals memory need. Detect: db.users.stats() storageSize vs size, and cache pressure in serverStatus().wiredTiger.cache.
Problem: Most reads should never touch a disk at all.
Diagram, one problem, one diagram
All three engines cache hot pages in RAM: PostgreSQL shared_buffers, InnoDB buffer pool, WiredTiger cache (~50% of RAM − 1 GB by default). If the working set doesn't fit, no index or query trick will save you, size memory first.
Tradeoff: Cache sized for the working set beats any query tuning; oversizing starves the OS cache these engines also use.
What breaks & the check: Working set outgrows RAM → hit ratio falls off a cliff. Detect per engine: pg_statio_user_tables, buffer pool hit ratio in INNODB STATUS, WT cache bytes read into cache.
Problem: Some questions aren't 'equal to', they're 'contains', 'overlaps', or 'in this range'.
Diagram, one problem, one diagram
GIN indexes what's inside a value (JSONB keys, array elements, full-text words). GiST handles ranges and geometry. BRIN stores min/max per block range, tiny, and perfect for time-ordered append-only tables.
Tradeoff: Specialized indexes answer questions B-trees cannot, but each adds write cost; BRIN is nearly free yet only works when physical order matches value order.
What breaks & the check: Using a B-tree on a JSONB containment query → seq scan forever. Detect: EXPLAIN shows Seq Scan on a query with @> or && operators.
Problem: A lookup shouldn't open the table file if the index already has the answer.
Diagram, one problem, one diagram
If every column the query needs is in the secondary index, InnoDB answers from the index alone, EXPLAIN shows “Using index”. The index stores the primary key, so non-covered lookups hop index → clustered row.
Tradeoff: Wider covering indexes speed one query and tax every write; cover the top queries, not all of them.
What breaks & the check: EXPLAIN loses 'Using index' after a column is added to the SELECT list. Detect: watch Extra in EXPLAIN for the top 5 queries after every deploy.
Problem: A combined index silently stops helping if its fields are in the wrong order.
Diagram, one problem, one diagram
Derived, not conventional: a compound B-tree stores keys sorted by field 1, then within equal field-1 values by field 2, then by field 3. Equality on field 1 therefore collapses the scan to one contiguous range (O(log N) to locate); within that range field 2 is already in stored sort order, so no SORT stage is needed; a range predicate ends the usable ordering, so range fields must come last or they destroy the sort guarantee for everything after them. Equality → Sort → Range (ESR) is the only order preserving all three. Full derivation in 'Reading the evidence' above.
Tradeoff: One compound index serves a family of queries only in ESR order; the wrong order silently serves half the query.
What breaks & the check: Index exists but explain shows a COLLSCAN or huge docsExamined. Detect: explain('executionStats'), index used yet totalDocsExamined >> nReturned.
Problem: Indexing rows nobody ever searches for wastes disk and slows every write.
Diagram, one problem, one diagram
Index only the rows that match a condition, e.g. only active users, WHERE deleted_at IS NULL. PostgreSQL and MongoDB support it directly; smaller index = faster writes and faster lookups.
Tradeoff: Smaller and faster, but the query filter must match the index predicate exactly or the planner cannot use it.
What breaks & the check: Query adds one extra condition and the partial index is ignored. Detect: EXPLAIN before/after, look for the index name disappearing.
Problem: UPDATE and DELETE leave dead row versions behind; something must reclaim them or files bloat forever.
Diagram, one problem, one diagram
VACUUM marks dead-tuple space reusable and updates the visibility map; autovacuum runs it automatically per table. If it falls behind, tables and indexes bloat, and in the extreme, transaction ID wraparound forces a shutdown. Never disable it.
Tradeoff: Steady background I/O now versus bloat and wraparound risk later; per-table tuning beats global on any uneven workload.
What breaks & the check: Long transactions block it → bloat + age(datfrozenxid) climbing. Detect: pg_stat_user_tables n_dead_tup and pg_database age(datfrozenxid).
Problem: Readers need old row versions, and failed transactions need to be undone, both come from the same place.
Diagram, one problem, one diagram
InnoDB writes undo records (in undo_001/undo_002 tablespaces) before modifying a row. A reader's snapshot rebuilds old versions from them; rollback replays them in reverse. The purge thread deletes undo only when no open transaction can still need it, one long transaction makes undo grow without bound.
Tradeoff: Undo enables consistent reads without locking, priced as purge debt that one long transaction can make unbounded.
What breaks & the check: History list length in the millions. Detect: SHOW ENGINE INNODB STATUS, TRANSACTIONS section; oldest entry in information_schema.innodb_trx.
Problem: A secondary that was down must be able to catch up without copying the whole database.
Diagram, one problem, one diagram
Secondaries catch up by replaying local.oplog.rs, a capped collection. If downtime exceeds the window the oplog covers (size ÷ write rate), the needed entries have rolled off and the member must do a full initial sync.
Tradeoff: Bigger oplog buys downtime tolerance with disk on every member; the window must exceed your longest planned outage.
What breaks & the check: Secondary returns after the window → full initial sync at peak. Detect: rs.printReplicationInfo() 'log length start to end'.
Problem: When two transactions touch the same rows, someone must decide what each is allowed to see.
Diagram, one problem, one diagram
Levels trade surprises for speed: Read Committed (fresh snapshot per statement), Repeatable Read (one snapshot per transaction), Serializable (as if transactions ran one at a time; PostgreSQL may abort one to keep that promise). Know your engine's default before debugging 'weird reads'.
Tradeoff: Stronger isolation removes surprises and adds aborts/retries; weaker is faster and pushes correctness into the application.
What breaks & the check: Debugging 'impossible' reads without knowing the default. Detect: SHOW transaction_isolation (PG) / @@transaction_isolation (MySQL) before anything else.
Problem: Sometimes you need an exact clone; sometimes you need only some tables, on a different version.
Diagram, one problem, one diagram
Streaming replication ships WAL bytes, the standby must be the same version and ends up byte-identical. Logical replication decodes WAL into per-table row changes, use it for major-version upgrades and selective copies.
Tradeoff: Streaming is exact and version-locked; logical is flexible (subsets, major upgrades) and needs primary keys plus conflict thinking.
What breaks & the check: Logical replica without PKs → full scans per update. Detect: pg_stat_subscription lag and REPLICA IDENTITY on source tables.
Problem: A replica must always know exactly which transactions it has applied, no guessing after a failover.
Diagram, one problem, one diagram
Every transaction committed with the binlog on gets a GTID (server UUID + sequence). A replica records the GTIDs it has executed, so reconnecting or promoting is arithmetic, not archaeology. Use ROW binlog format for exactness.
Tradeoff: ROW format is exact and heavier; STATEMENT is light and can diverge on nondeterministic functions. GTID makes failover set arithmetic.
What breaks & the check: gtid_executed differs unexpectedly after failover. Detect: SELECT @@gtid_executed on both sides; SHOW REPLICA STATUS\G.
Problem: If the primary dies at 3 AM, the replica set must recover without a human.
Diagram, one problem, one diagram
Members heartbeat each other; if the primary is unreachable, the most up-to-date secondaries elect a new one. writeConcern w:'majority' means an acknowledged write exists on a majority of members, so it survives any single election. Pair with j:true for journal durability.
Tradeoff: Majority concern prices every write at the slowest majority member; anything weaker can roll back and must be treated as provisional.
What breaks & the check: Rollback files appear after an election. Detect: rs.status() states/optimes and ls of the rollback/ directory in dbPath.
Problem: Data must spread across servers evenly, and queries must land on as few of them as possible.
Diagram, one problem, one diagram
The shard key is the field(s) mongos uses to route each document. Hashed keys spread perfectly but range queries scatter to all shards; ranged keys keep neighbours together but risk hotspots. Refining a shard key is possible in newer versions, but the original choice is close to permanent.
Tradeoff: Hashed spreads writes, ranged keeps locality; the choice is near-permanent, so it is made from query patterns, not from the schema diagram.
What breaks & the check: Jumbo chunks + one hot shard. Detect: sh.status() chunk distribution and jumbo flags; getShardDistribution().
Problem: Old data should be droppable in milliseconds, and queries on recent data shouldn't scan history.
Diagram, one problem, one diagram
One logical table, many physical files, PARTITION BY RANGE (created_at) gives each month its own file. Dropping a month = DROP TABLE on one partition. Queries with a date filter are pruned to the relevant partitions only.
Tradeoff: Partitioning makes retention and pruning cheap but adds planning overhead per partition; hundreds of partitions need discipline.
What breaks & the check: Query without the partition key scans every partition. Detect: EXPLAIN shows all partitions under Append instead of a pruned few.
Problem: MySQL has no built-in sharding, something has to route queries when data spans servers.
Diagram, one problem, one diagram
Vitess (built at YouTube) sits between app and MySQL, routes each query to the right shard by keyspace ID, and manages topology. ProxySQL is the lighter tool: connection pooling, read/write splitting, and query routing without sharding.
Tradeoff: A routing layer buys sharding and pooling at the cost of another system to run, upgrade, and debug at 3 AM.
What breaks & the check: Read/write splitting sends a just-written read to a lagging replica. Detect: ProxySQL stats + replica lag during the complaint window.
Problem: Thousands of app connections crush every engine, each one costs memory and bookkeeping.
Diagram, one problem, one diagram
A pooler keeps a small set of real connections and multiplexes app sessions onto them: PgBouncer (PostgreSQL), ProxySQL (MySQL), or the driver's maxPoolSize (MongoDB). Measure queue wait at the pooler, not just query time.
Tradeoff: Pooling caps DB load but adds queueing at the pooler; transaction pooling breaks session state (prepared statements, SET) if misused.
What breaks & the check: Pool queue wait exceeds query time. Detect: PgBouncer SHOW POOLS cl_waiting, ProxySQL connection pool stats, driver maxPoolSize vs currentOp count.
Problem: You can't fix the slow queries you haven't identified.
Diagram, one problem, one diagram
Each engine can log statements over a time threshold, with rows examined: PostgreSQL log_min_duration_statement, MySQL slow_query_log (+ pt-query-digest), MongoDB profiler (db.setProfilingLevel). The fix order is almost always: missing index → bad plan from stale stats → chatty app.
Tradeoff: Logging everything costs overhead; logging nothing costs blindness. Threshold + digest review is the working compromise.
What breaks & the check: Fixing by mean time instead of total time. Detect: pg_stat_statements / events_statements_summary_by_digest ordered by total, not average.
Problem: The WAL can't grow forever; dirty pages must eventually reach their files so segments can be recycled.
Diagram, one problem, one diagram
A checkpoint writes all dirty pages in shared_buffers to their heap/index files and records the point in pg_control; WAL segments before it are renamed and reused. Checkpoints too frequent (max_wal_size too small) = I/O storms; too rare = slow crash recovery.
Tradeoff: Frequent checkpoints = I/O storms; rare = slow recovery and a huge pg_wal. max_wal_size is the budget knob.
What breaks & the check: num_requested outnumbering num_timed. Detect: pg_stat_checkpointer and pg_ls_waldir() size.
Problem: A committed change must survive a crash, and a page being written during the crash must not end up torn in half.
Diagram, one problem, one diagram
Redo (#innodb_redo/#ib_redo0-31; ib_logfile0/1 before 8.0.30) replays page changes after a crash. The doublewrite buffer (#ib_16384_*.dblwr) writes each flushed page to a safe area first, then to its real place; on recovery a torn page is restored from the doublewrite copy.
Tradeoff: Doublewrite costs an extra write per flush to make torn pages impossible to serve; disabling it on non-atomic storage is gambling.
What breaks & the check: Checkpoint age near redo capacity → flush stalls. Detect: SHOW ENGINE INNODB STATUS LOG + FILE I/O sections.
Problem: Data files are only consistent at checkpoints, something must cover the minutes in between.
Diagram, one problem, one diagram
Every write goes to journal/WiredTigerLog.* first (group-committed, up to every 100 ms by default). A checkpoint (~60 s) writes a consistent snapshot of all dirty pages into the .wt files; restart = last checkpoint + journal replay. j:true makes a write wait for the journal specifically.
Tradeoff: Journal group-commit batches fsyncs (up to 100 ms window); j:true removes the batching risk for that write at latency cost.
What breaks & the check: Crash loses up to the checkpoint gap when journaling is off or misdirected. Detect: ls journal/ and storage.journal.enabled in getCmdLineOpts.
Problem: A planner choosing from stale statistics will pick a full scan for a needle, the classic 'slow overnight, no deploy' bug.
Diagram, one problem, one diagram
Planners cost plans from row counts and value histograms: PostgreSQL ANALYZE (autovacuum runs it), MySQL ANALYZE TABLE, MongoDB updates stats on index builds. After bulk loads or big deletes, refresh stats before blaming the query.
Tradeoff: Auto-analyze lags after bulk loads; manual ANALYZE after a big import is cheaper than a night of wrong plans.
What breaks & the check: Plan flips to seq scan the morning after a bulk load. Detect: compare EXPLAIN estimated vs actual rows; check last_analyze in pg_stat_user_tables.
24 ideas shown
Before you leave, check this: run SELECT pid, now() - xact_start AS age, state FROM pg_stat_activity ORDER BY age DESC; The top row owns your bloat if it is old.