Zero to staff-level

Guided path · 6 levels · one problem, one diagram each

Zero to staff-level, in order. Every concept is a problem and a diagram.

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

Hooka number contrast, with the math that makes it real
→
Situationthe diagram: real files, memory areas, log flow
→
Problemone line, plus the growth curve: fine at small scale, slow at 1M, an incident at 10M
→
Mechanismderived from the physical layout, with its tradeoff priced
→
In Practicereal output, annotated, red flags named, the exact check
→
Where It Breaksroot cause, blast radius, mitigation, the staff-level mistake, the metric that exposes it first
0 / 6 levels
Problem

A commit must survive a power cut, even though the data page is still only in memory.

PostgreSQL

PostgreSQL, WAL first, commit second

The number, why this mechanism existsA commit costs one sequential fsync: a WAL record of a few hundred bytes. Writing the data instead would mean the heap page plus one page per index, 4 or more random 8 KB writes, each needing its own ordering guarantee. One sequential write at ~0.1-1 ms replaces all of them: commit cost drops from O(pages touched) to O(1).
Problem

The data page cannot be forced to disk on every commit, that is far too slow, yet a committed row must never be lost.

10 MB of WAL/dayAny default survives. Archiving is a nice-to-have.
~1-2 GB/dayArchiving and retention become real jobs; a missed archive is visible within a day.
Transition: 50-100 MB/minCheckpoint and archive throughput become the write ceiling. Requested checkpoints appear in pg_stat_checkpointer.
1+ GB/min at peakWAL generation rate, not table size, not CPU, is the instance's write limit. Sizing is a capacity decision.

Situation, the physical organization, drawn for this problem

Backendwrites change
→
WAL buffer → WAL writerfsync, sequential
→
pg_wal/00000001000000000000000316 MB segment, on disk
→
COMMIT returnsonly after fsync
→
Checkpoint (later)page → base/16384/16397
Mechanism, derived from the layout

Commit latency becomes one sequential fsync instead of random page writes. WAL generation rate (MB/s) is the real write ceiling of the instance, not table size.

The tradeoff

synchronous_commit = on is the safe default. off / local buys latency by risking the last ~3× WAL-writer-delay of commits on an OS crash, acceptable for staging or rebuildable data, never for payments.

In Practice, real output, red flags named
$ 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).

SELECT pg_current_wal_lsn(), pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) FROM pg_stat_replication; -- shell: du -sh $PGDATA/pg_wal ; check archive_status/
Where it breaks

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.

Common staff-level mistake

Sizing max_wal_size by guesswork, or putting pg_wal on the same saturated disk as the data files.

The metric that exposes it first

Requested vs timed checkpoints in pg_stat_checkpointer, plus WAL bytes/s in pg_stat_wal. Requested outnumbering timed means max_wal_size is too small for your write rate.

MySQL · InnoDB

MySQL, redo + binlog must agree (two-phase commit)

The number, why this mechanism existsTwo logs record the same transaction in two vocabularies: redo says these bytes of this page changed, the binlog says this transaction happened. If a crash leaves them disagreeing by even one transaction, the source and every replica hold different data, with no error raised anywhere. Two-phase commit exists so the committed-transaction count in both logs is always identical.
Problem

InnoDB and the binary log are two separate logs; a crash must never leave a row in one and not the other.

Dev / stagingSettings barely matter; crashes are free lessons.
Real traffic, safe settingsflush=1 + sync_binlog=1: full safety, commit cost = 2 fsyncs.
Transition: someone benchmarks with flush=2Throughput looks 20-30% better in the load test. The config ships.
First OS crash in productionUp to ~1 s of commits missing from one or both logs. Replicas diverge silently; PITR replays a history that never happened.

Situation, the physical organization, drawn for this problem

InnoDB redo bufferpage changes
→
#innodb_redo/#ib_redo0crash recovery
→
mysql-bin.000007 + GTIDreplication + PITR
→
2PC: prepare → binlog fsync → commitinnodb_flush_log_at_trx_commit=1, sync_binlog=1
Mechanism, derived from the layout

Redo rebuilds pages on this server; the binlog rebuilds data on other servers. The internal two-phase commit is what keeps replicas and point-in-time recovery consistent with the engine.

The tradeoff

flush=1 + sync_binlog=1 is the only fully safe pair. flush=2 or sync_binlog=0 trades durability for throughput, the classic silent data-loss setting found in inherited configs.

In Practice, real output, red flags named
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.

SHOW ENGINE INNODB STATUS\G -- LOG section SELECT @@innodb_flush_log_at_trx_commit, @@sync_binlog, @@gtid_executed;
Where it breaks

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.

Common staff-level mistake

Benchmarking with flush=2, shipping it to production, and discovering it during the first real crash.

The metric that exposes it first

Binlog fsync latency and redo write waits under peak load; any gap between gtid_executed on source vs replica after a failover.

MongoDB · WiredTiger

MongoDB, journal is durability, oplog is replication

The number, why this mechanism existsTwo numbers that are constantly confused: journal latency is measured in milliseconds (this node's crash safety); the oplog window is measured in hours (the set's catch-up budget). A write with w: 1 can be journaled in ~1 ms and still be rolled back minutes later in an election. Durability on one node is not durability of the set.
Problem

Two different logs get confused constantly: one protects against a crash on this node, the other keeps the replica set identical.

Single node / dev setJournal alone is the whole story. w:1 feels safe.
Replica set, w:1 writesFirst election rolls back acknowledged writes. The rollback/ directory gets its first files.
Transition: write volume grows 10×Same oplog size, one-tenth the window. Nobody recomputed it.
Window < maintenance durationA member down for routine work returns to a full initial sync, during peak traffic.

Situation, the physical organization, drawn for this problem

mongod (primary)accepts write
→
journal/WiredTigerLog.*write-ahead, j:true waits here
→
WiredTiger cachedirty pages
→
local.oplog.rsordered ops for secondaries
→
Secondariestail + apply oplog
Mechanism, derived from the layout

A write can be journaled (safe on this node) yet still roll back in an election if a majority never saw it. Staff-level MongoDB work starts by separating these two guarantees.

The tradeoff

writeConcern { w: 1 } is fast and rollback-prone. { w: 'majority', j: true } costs cross-member latency and is the only setting where acknowledged means survives an election.

In Practice, real output, red flags named
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.

db.serverStatus().wiredTiger.log rs.printReplicationInfo() // oplog window rs.status() // per-member lag
Where it breaks

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.

Common staff-level mistake

Treating the oplog as a backup or as the crash log, it is neither; it is a capped replication stream.

The metric that exposes it first

Journal commit latency in serverStatus, plus secondary lag in seconds. Lag growing linearly = apply throughput, not network, is the bottleneck.

Remember: COMMIT means the log record is on disk. Everything else, checkpoints, replicas, backups, is built on that one ordering rule.
Problem

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.

PostgreSQL

PostgreSQL, shared_buffers, clock sweep, bgwriter, checkpoints

The number, why this mechanism existsThe arithmetic of a hit ratio: at 10,000 queries/second, 99% hits still means 100 disk reads/second; at 95% it is 500/second and the disk is now the database. A miss is not just slower, if the page evicted to make room is dirty, the miss also pays a write first. Eviction is the only place a read can be forced to do write I/O.
Problem

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.

10K rowsThe whole table fits in shared_buffers. Every plan looks fast.
1M rowsWorking set ≈ cache. Hit ratio still above 99%; nothing to see.
Transition: working set crosses total cacheMisses begin; dirty victims make some reads pay a write. Latency develops a sawtooth synced to checkpoints.
10M+ rowsEviction churn dominates: buffers_alloc climbs, unrelated queries slow down because hot pages keep getting evicted by scans.

Situation, the physical organization, drawn for this problem

Backend needs pagenot in shared_buffers
→
Clock sweep handusage_count counts down
→
Dirty victimbgwriter / checkpointer writes it
→
Page loadedfrom base/16384/16397
Mechanism, derived from the layout

Eviction cost lands on foreground queries when background flushing falls behind: the classic sawtooth latency pattern that tracks the checkpoint cycle exactly.

The tradeoff

shared_buffers around 25% of RAM is the usual start. Much larger double-buffers against the OS page cache and makes checkpoints heavier; much smaller forces constant eviction churn.

In Practice, real output, red flags named
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.

SELECT num_timed, num_requested, buffers_written FROM pg_stat_checkpointer; SELECT buffers_clean, maxwritten_clean, buffers_alloc FROM pg_stat_bgwriter;
Where it breaks

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.

Common staff-level mistake

Raising shared_buffers to 70-80% of RAM because more cache must be better.

The metric that exposes it first

num_requested outnumbering num_timed and buffers_alloc climbing fast, the working set does not fit, or max_wal_size forces write bursts.

MySQL · InnoDB

MySQL, buffer pool, redo capacity, and checkpoint age

The number, why this mechanism existsCheckpoint age is a subtraction: redo capacity minus the LSN span of unflushed dirty pages. At ~75% of capacity InnoDB starts flushing aggressively; near 100% it throttles writes until flushing catches up. A 1 GB redo log absorbing 20 MB/s of page changes holds 50 seconds of headroom. That number, not the instance size, is the write-burst budget.
Problem

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.

Low write rateCheckpoint age sits near zero. Redo size is invisible.
Steady writesAge stabilizes where flush rate equals write rate. Still fine.
Transition: burst length × rate > capacityAge crosses ~75% during every peak. Aggressive flushing begins; write latency rises exactly at peak load.
Sustained overloadAge pinned at capacity: writes stall to flush speed. The database is slowest precisely when the business is busiest.

Situation, the physical organization, drawn for this problem

Buffer pooldirty pages accumulate
→
Redo log (capacity)checkpoint age grows →
→
Page cleaner threadsflush dirty pages
→
users.ibdcheckpoint advances
Mechanism, derived from the layout

Checkpoint age is InnoDB headroom. Near capacity, InnoDB switches to aggressive flushing and write stalls, the database slows down precisely at peak write load.

The tradeoff

Bigger innodb_redo_log_capacity (8.0.30+, up to 512 GiB) smooths write bursts and reduces flush pressure, at the cost of disk space and longer worst-case crash recovery.

In Practice, real output, red flags named
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.

SHOW ENGINE INNODB STATUS\G -- FILE I/O + LOG: 'Checkpoint age', 'Modified db pages', pending writes
Where it breaks

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.

Common staff-level mistake

Leaving a small redo log on a write-heavy primary, then tuning everything except the redo log.

The metric that exposes it first

Checkpoint age as a percentage of redo capacity. Sustained above ~75% means flushing cannot keep up with the write rate.

MongoDB · WiredTiger

MongoDB, WiredTiger cache eviction

The number, why this mechanism existsThe WiredTiger cache defaults to 50% of (RAM − 1 GB), and two percentages run it: eviction target 5% dirty, eviction trigger 20% dirty. Below target, background workers evict quietly. Above trigger, application threads are forced to evict before they may proceed, an operation that cost 1 ms now costs a disk write first: 10-100× slower, on every operation, instance-wide.
Problem

Reads and writes happen in the cache; when it fills with dirty pages, eviction, not disk speed, becomes the latency.

Working set fits in cacheEviction is background noise. Dirty bytes hover near target.
Write rate growsDirty bytes oscillate between target and trigger at peak. Still absorbed.
Transition: dirty pinned above the 20% triggerApplication threads start evicting. First multi-second stalls appear while CPU and disk look only moderately busy.
ChronicEvery operation inherits eviction cost. Teams upgrade disks and see no improvement, because the bottleneck was never disk speed.

Situation, the physical organization, drawn for this problem

WiredTiger cache~50% of (RAM − 1 GB)
→
Dirty pages riseeviction target 5%, trigger 20%
→
Eviction workerswrite dirty, free clean
→
App threads evict= stall, latency spike
Mechanism, derived from the layout

The single most common WiredTiger production issue: once application threads are forced to do eviction work, every operation inherits disk latency.

The tradeoff

A larger cacheSizeGB holds more working set but starves the OS filesystem cache that WiredTiger also depends on. Default exists for a reason; change it with evidence.

In Practice, real output, red flags named
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.

db.serverStatus().wiredTiger.cache // read: 'tracked dirty bytes in the cache', // 'pages evicted by application threads'
Where it breaks

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.

Common staff-level mistake

Blaming slow disks and upgrading storage when the real problem is cache pressure from a working set that outgrew RAM.

The metric that exposes it first

Pages evicted by application threads greater than zero, and tracked dirty bytes sustained above the 20% trigger.

Remember: Size the cache for the working set, then watch who evicts: background threads are healthy, application threads evicting is a latency alarm in all three engines.
Problem

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.

PostgreSQL

PostgreSQL, index-only scans and the visibility map

The number, why this mechanism existsCount the pages for a 1,000-row lookup. Index scan: ~3 index pages + 1,000 heap pages, one random fetch per row, the index walk is 0.3% of the cost. Index-only scan with a current visibility map: 0 heap pages. The same query drops from ~1,003 page reads to ~10, and the only thing that changed is one bit per page in a side file (16397_vm) that VACUUM maintains.
Problem

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.

Small tableHeap fetches are cached anyway. The distinction is invisible.
Index-only scan adoptedHeap Fetches: 0 in EXPLAIN. The win is real while vacuum keeps up.
Transition: update rate outpaces vacuumVisibility map goes stale page by page. Heap Fetches creep from 0 to hundreds per query, no deploy, no error.
ProductionThe plan still says Index Only Scan. The cost is back to index-scan levels, but the wider index still adds its write to every INSERT and UPDATE.

Situation, the physical organization, drawn for this problem

Queryneeds key + 2 columns
→
Index file base/…/16405holds all needed columns
→
Visibility map bit set?yes → skip heap
→
Heap fetchonly when bit is unset
Mechanism, derived from the layout

A covering index plus a current visibility map turns a lookup into index pages only. This is often the largest single read win available on a hot query.

The tradeoff

Wider indexes cost write I/O, WAL volume, and cache on every insert and update, and the win evaporates if vacuum lets the visibility map go stale.

In Practice, real output, red flags named
-- 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.

EXPLAIN (ANALYZE, BUFFERS) SELECT …; -- read the 'Heap Fetches: N' line, not just the scan type
Where it breaks

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.

Common staff-level mistake

Adding ever-wider covering indexes to a table whose real problem is vacuum not keeping the visibility map current.

The metric that exposes it first

Heap Fetches per index-only scan on the top queries, and the ratio of index-only to plain index scans in pg_stat_user_indexes.

PostgreSQL

PostgreSQL, HOT updates: change a row without touching any index

The number, why this mechanism existsPrice one UPDATE of a row in a table with 4 indexes. Non-HOT: 1 heap write + 4 index writes, and 5 dead entries left for vacuum. HOT: 1 heap write, 0 index writes, cleaned up by pruning on the next read of that page. At 5,000 updates/second that is 25,000 page writes/second versus 5,000, a 5× write reduction from changing nothing but which columns the UPDATE touches and how full the page is.
Problem

If every UPDATE rewrites every index entry, a hot row with 4 indexes costs 5 writes and floods vacuum with dead index entries.

Low update rateHOT is a curiosity. Bloat is unmeasurable.
Update-heavy table, HOT workinghot_pct above 90%: updates cost one heap write each.
Transition: one indexed column joins the SET listHOT eligibility drops to zero for those updates. No schema change, no error, cost per update jumps 5-10× overnight.
ProductionDead index entries flood vacuum, pages bloat, and the team tunes autovacuum harder to treat a symptom created by the update pattern.

Situation, the physical organization, drawn for this problem

UPDATEno indexed column changes
→
Old tuplesame heap page
→
HOT chain → new tuplesame page, index untouched
→
Pruning on next readdead link removed in passing
Mechanism, derived from the layout

Heap-Only Tuple updates are why fillfactor and column choice decide update throughput on hot tables like sessions, counters, and queues.

The tradeoff

fillfactor 70-80 leaves room in each page for HOT chains but stores fewer rows per page, more pages, more cache pressure on scans.

In Practice, real output, red flags named
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.

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;
Where it breaks

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%.

Common staff-level mistake

Leaving fillfactor at 100 on an update-heavy table, then fighting the resulting bloat with bigger autovacuum.

The metric that exposes it first

HOT percentage below ~50% on a table dominated by updates to non-indexed columns.

MySQL · InnoDB

MySQL, change buffer and adaptive hash index

The number, why this mechanism existsInserting into a secondary index whose leaf page is not in the buffer pool costs one random read (~80 µs-10 ms) just to insert one row. The change buffer records the change in ibdata1 instead, zero reads, and merges it later, in bulk, when the page is next loaded for any reason. The cost is not removed; it is deferred and moved onto the next reader of that page.
Problem

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.

Indexes fit in the buffer poolChange buffer mostly idle; inserts read pages that are already cached.
Cold secondary indexes, steady insertsIbuf grows; merges happen in the background as pages cycle through.
Transition: write burst, then a read stormEvery read of a cold page first merges its backlog of buffered changes. Read latency spikes with no change to the read queries.
ProductionPost-burst slowdowns get misdiagnosed as 'the reads got slower', and someone disables the change buffer, making the original write problem permanent.

Situation, the physical organization, drawn for this problem

Secondary index insertpage not in buffer pool
→
Change buffer (in ibdata1)change recorded, no read
→
Later: page read / purge / mergebuffered changes applied in bulk
→
Adaptive hash indexhot search patterns → hash → row pointer
Mechanism, derived from the layout

The change buffer turns random I/O into sequential merge work for non-unique secondary indexes. The adaptive hash index (AHI) silently accelerates repeated exact-match lookups on hot pages.

The tradeoff

Change buffer can consume up to 25% of the buffer pool and its merge cost lands on later reads. AHI costs memory and can become a latch hotspot; it helps point lookups, never range scans.

In Practice, real output, red flags named
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.

SHOW ENGINE INNODB STATUS\G -- INSERT BUFFER AND ADAPTIVE HASH INDEX: -- 'Ibuf size', merges, 'hash searches/s'
Where it breaks

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.

Common staff-level mistake

Disabling the change buffer (or AHI) from a blog post, on a workload that was quietly depending on it.

The metric that exposes it first

Ibuf size growing without merging, and the hash vs non-hash search ratio, if hash searches dominate, AHI is load-bearing.

Remember: Measure both sides of every index, heap fetches and HOT ratio on the read side, write amplification on the other, before adding it, and again before keeping it.
Problem

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.

PostgreSQL

PostgreSQL, vacuum, freeze, and transaction ID wraparound

The number, why this mechanism existsTransaction IDs are a 32-bit counter: 4.29 billion values, and PostgreSQL treats half that space as 'the past'. A table whose oldest unfrozen transaction ages toward ~2.1 billion approaches the point where the database refuses new writes to protect the data. Freezing is not housekeeping, it is recycling the finite counter every future write depends on.
Problem

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.

Normal loadAutovacuum freezes as it goes. age(relfrozenxid) stays in the low millions.
Busy tablesPer-table scale factors matter; the biggest table needs its own autovacuum settings.
Transition: one idle-in-transaction session lives for daysVacuum's horizon is pinned. Dead tuples stop being removable; relfrozenxid age climbs steadily, week over week.
~200M from the limitAnti-wraparound vacuum arrives as an emergency: aggressive, I/O-heavy, uninvited. Past the limit, writes stop by design.

Situation, the physical organization, drawn for this problem

Dead tuplesfrom UPDATE / DELETE
→
Autovacuum workersspace reusable + visibility map set
→
Freeze: xmin → frozenbefore age 200M (autovacuum_freeze_max_age)
→
Not frozen in timewraparound risk at ~2.1B xids
Mechanism, derived from the layout

Wraparound is the one PostgreSQL failure that can force a read-only shutdown to protect data. Freeze progress on old, cold tables is a staff-level operational duty, not trivia.

The tradeoff

Aggressive per-table autovacuum (lower scale factor on hot tables) spends steady I/O to avoid bloat cliffs. Global-only tuning always loses on the biggest table.

In Practice, real output, red flags named
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.

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC; SELECT pid, now()-xact_start AS age, state FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 5;
Where it breaks

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.

Common staff-level mistake

Disabling autovacuum on a busy table to win a benchmark weekend.

The metric that exposes it first

age(relfrozenxid) per table trending up week over week, and n_dead_tup growing faster than autovacuum removes it.

MySQL · InnoDB

MySQL, undo, purge, and the history list

The number, why this mechanism existsA consistent read that needs an old row version walks the undo chain one record at a time. History list length 1,000,000 means up to a million undo records are being retained because one read view, opened hours ago, might still need them, and every snapshot read on the instance pays longer chains for that one transaction's convenience.
Problem

A reader snapshot rebuilds old row versions from undo; undo can only be purged when the oldest open read view no longer needs it.

Normal loadHistory list length sits in the hundreds. Purge is invisible.
Longer reports on the primaryLength spikes to ~10k during reports, drains after. Watch it, don't panic.
Transition: one transaction forgotten for hoursLength crosses 100k and stops draining. Undo tablespaces start growing on disk.
MillionsEvery consistent read walks long version chains: instance-wide read slowdown with no single slow query to blame.

Situation, the physical organization, drawn for this problem

UPDATEundo record written first
→
undo_001 / undo_002old version lives here
→
Read viewrebuilds old version via roll pointer
→
Purge threadfrees undo when no view needs it
Mechanism, derived from the layout

History list length is the InnoDB equivalent of dead tuples: when purge falls behind, every consistent read walks longer version chains and gets slower, instance-wide.

The tradeoff

Long analytical transactions on the primary are sometimes convenient, and they tax every other reader. Replicas exist precisely to move this cost off the write path.

In Practice, real output, red flags named
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.

SHOW ENGINE INNODB STATUS\G -- TRANSACTIONS: 'History list length' SELECT * FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 5;
Where it breaks

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.

Common staff-level mistake

Hunting slow queries one by one while a single 6-hour transaction is pinning purge for the whole instance.

The metric that exposes it first

History list length above ~10k and still growing. The oldest trx_started in innodb_trx names the culprit.

MongoDB · WiredTiger

MongoDB, WiredTiger checkpoints and snapshot timestamps

The number, why this mechanism existsCrash recovery time is a division: journal written since the last checkpoint ÷ replay speed. Checkpoints run about every 60 seconds; at 50 MB/s of writes that is up to ~3 GB of journal to replay before the node serves again. Separately, a snapshot held open for 1 hour pins every version created in that hour, cleanup of all of them waits for one reader.
Problem

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.

Normal operationCheckpoints complete in seconds; pinned timestamps are seconds old.
Longer aggregationsOldest pinned timestamp ages to minutes during reports. Cache absorbs it.
Transition: a snapshot lives for hoursVersions cannot be freed; cache fills with un-reclaimable data; eviction pressure (Level 1) starts stalling application threads.
Crash in this stateRecovery replays up to a checkpoint interval of journal, minutes of downtime sized by write rate, not by database size.

Situation, the physical organization, drawn for this problem

Writestimestamped versions in cache
→
Checkpoint (~60 s)stable blocks → collection-*.wt
→
WiredTiger.wtmetadata: last stable checkpoint
→
Journalcovers everything since checkpoint
Mechanism, derived from the layout

Checkpoint duration and pinned timestamps decide both recovery time and cache health. A snapshot held open by a slow aggregation or change stream quietly blocks version cleanup.

The tradeoff

More frequent checkpoints shrink journal replay and recovery time but add steady write I/O; the default 60 s is a balance, not a law.

In Practice, real output, red flags named
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.

db.serverStatus().wiredTiger // read: cache 'oldest timestamp pinned', // checkpoint times, transaction snapshot ranges
Where it breaks

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.

Common staff-level mistake

Running hour-long snapshot reads or aggregations against the primary and tuning the cache instead of the reader.

The metric that exposes it first

Age of the oldest pinned timestamp and checkpoint duration trending up together.

Remember: MVCC failures are almost never MVCC itself: they are one long transaction pinning cleanup, vacuum, purge, or timestamp, while versions pile up behind it.
Problem

A replica that lags, or a failover that loses acknowledged writes, is worse than no replica, because everyone trusted it.

PostgreSQL

PostgreSQL, WAL streaming and synchronous standbys

The number, why this mechanism existsThe two prices, stated exactly. Synchronous replication adds standby round-trip + standby fsync to every commit: ~0.5-2 ms to a standby in the same region, 30-80 ms across regions. Asynchronous adds 0 ms, and on failover loses up to write rate × replay lag: at 2,000 commits/s and 5 s of lag, that is 10,000 acknowledged transactions. There is no third price; every deployment pays one of these two.
Problem

Async replication can lose the last transactions in a crash; sync replication can block every commit if the standby disappears.

Low write rateAsync lag is milliseconds. The loss window is theoretical.
Peak loadLag is set by standby apply throughput, not the network. Seconds appear.
Transition: the standby diesSync: commits hang until it returns or the config changes, the safety feature causes the outage. Async: the loss window grows silently.
Failover with unmeasured lagAcknowledged writes are gone. The count is discovered from user complaints, not from monitoring.

Situation, the physical organization, drawn for this problem

Primary WALwalsender streams records
→
Standby walreceiverwrites pg_wal, replays in order
→
Async: commit returns nowlag = risk window
→
Sync: commit waits for acksynchronous_standby_names
Mechanism, derived from the layout

The sync/async dial is a business decision expressed in config: how many acknowledged writes may a failover lose, zero, or a few seconds worth?

The tradeoff

Synchronous commit adds standby RTT + fsync to every commit. Quorum-based ANY 1 of 2 standbys balances safety against one standby taking the whole system down with it.

In Practice, real output, red flags named
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.

SELECT client_addr, state, sync_state, replay_lag FROM pg_stat_replication; SHOW synchronous_standby_names; SHOW synchronous_commit;
Where it breaks

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.

Common staff-level mistake

Placing the synchronous standby in the same availability zone and calling it disaster recovery.

The metric that exposes it first

replay_lag at peak write load, and commit p99 latency before vs after enabling sync, the true price of zero-loss.

MySQL · InnoDB

MySQL, GTID and semi-synchronous replication

The number, why this mechanism existsFailover with GTIDs is set arithmetic: source gtid_executed minus replica gtid_executed = exactly the transactions the replica lacks. Without GTIDs the same question is answered by comparing binlog file names and byte positions across servers that may have diverged, forensics, not arithmetic. And semi-sync adds one ACK wait per commit, then silently stops adding it after a timeout (default 10 s).
Problem

Position-based replication breaks on failover: which transactions did the new source have that the old one never sent?

GTID on, semi-sync ONFailover is arithmetic; the loss window is one ACK.
A network eventSemi-sync times out and degrades to async. Nothing pages; the status variable flips to OFF.
Transition: weeks of silent asyncEvery dashboard still says 'semi-sync enabled', the plugin is installed and the variable was set once.
FailoverWrites everyone believed replicated are missing on the new source. Divergence surfaces as missing rows days later.

Situation, the physical organization, drawn for this problem

Source binlogevery txn gets a GTID
→
Replica I/O threadrelay log
→
Applier threadsapply by GTID set
→
Semi-sync ACKsource waits ≥1 replica before commit returns
Mechanism, derived from the layout

GTIDs make failover arithmetic: compare executed GTID sets and the missing transactions are a set difference, not a forensic investigation. Semi-sync closes the async loss window without full group replication.

The tradeoff

Semi-sync waits for an ACK per commit (rpl_semi_sync_source_wait_point = AFTER_SYNC is the safe point) but degrades to async silently once its timeout passes, the config keeps saying ON long after the protection is gone.

In Practice, real output, red flags named
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.

SHOW REPLICA STATUS\G -- GTID + Seconds_Behind_Source SELECT @@gtid_executed; SHOW STATUS LIKE 'Rpl_semi_sync%';
Where it breaks

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.

Common staff-level mistake

Assuming semi-sync is active because the plugin is installed and the variable is set to ON at some point in the past.

The metric that exposes it first

Rpl_semi_sync_source_status staying ON and the semi-sync wait time, alongside applier (not I/O) lag on replicas.

MongoDB · WiredTiger

MongoDB, elections, write concern, read concern

The number, why this mechanism existsElection arithmetic: heartbeats every 2 s, an election is called after roughly 10 s of silence, and the winner needs a majority of votes and the highest optime among voters. Only majority-committed writes are guaranteed to be on that winner. A w: 1 write acknowledged 5 seconds before the primary died sits exactly inside the window that rolls back.
Problem

When the primary dies, the set must elect a replacement that actually holds the acknowledged writes, automatically, at 3 AM.

Stable setNo elections for months. Concerns feel like ceremony.
First real electionMajority writes survive; any w:1 writes in the final seconds roll back and appear as files in rollback/.
Transition: flapping network or overloaded primaryRepeated elections; each one is a write outage of ~10 s plus a fresh rollback window.
ChronicApplications see intermittent 'data loss' that is actually rollback of writes that were never majority-safe.

Situation, the physical organization, drawn for this problem

Primaryheartbeats every 2 s
→
Heartbeats stopelection timeout ~10 s
→
Majority votesmost up-to-date member wins
→
New primaryw:majority writes survive; w:1 may roll back
Mechanism, derived from the layout

Elections are why write concern exists: only majority-committed writes are guaranteed present on whoever wins. Read concern majority gives the matching read guarantee.

The tradeoff

w: 'majority' + j: true costs the slowest majority member latency on every write. readConcern linearizable is stronger still and should be rare, it is the most expensive read MongoDB offers.

In Practice, real output, red flags named
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.

rs.status() // states, optime lag per member rs.conf() // election + heartbeat settings db.serverStatus().repl
Where it breaks

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.

Common staff-level mistake

Serving read-your-own-write flows from secondaries with default concerns, then reporting phantom data loss.

The metric that exposes it first

Election frequency (any is worth a postmortem), per-member optime lag, and rollback file count after incidents.

Remember: Replication is a contract: what does an acknowledged write guarantee, and how far behind is the copy right now? GTID, LSN, and optime exist to make both answers exact.
Problem

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.

PostgreSQL

PostgreSQL, WAL sizing: max_wal_size is a checkpoint budget

The number, why this mechanism existsCheckpoint frequency is a division: max_wal_size ÷ WAL rate. At 100 MB/min of WAL, a 1 GB budget forces a checkpoint every 10 minutes of writes, and every checkpoint re-flushes the same hot pages. Raise the budget to 16 GB and the interval becomes ~2.7 hours: the same total data written, spread evenly as background I/O instead of arriving in storms.
Problem

WAL segments can only be recycled after a checkpoint; write faster than checkpoints complete and the safety log becomes the outage.

MB of WAL/dayDefaults are fine. Checkpoints are timed, an hour apart.
GB/dayRequested checkpoints appear in pg_stat_checkpointer. Still self-managing, at a price in I/O smoothness.
Transition: hundreds of MB/minCheckpoint I/O competes with query I/O every few minutes. Latency spikes align with checkpoint starts.
Archive failure at this ratepg_wal grows unbounded, GB per hour, until the filesystem fills and the primary panics, by design.

Situation, the physical organization, drawn for this problem

Write rateWAL generated per minute
→
max_wal_size / checkpoint_timeoutwhichever hits first triggers checkpoint
→
Checkpoint writes dirty pagessegments before it recycle
→
Budget too smallrequested checkpoints, I/O storms
Mechanism, derived from the layout

Checkpoint tuning is the most effective PostgreSQL write tuning there is: it converts random write bursts into steady background I/O, or fails to.

The tradeoff

Large max_wal_size (tens of GB on busy primaries) smooths I/O but lengthens crash recovery and the disk pg_wal can consume before recycling.

In Practice, real output, red flags named
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.

SELECT num_timed, num_requested FROM pg_stat_checkpointer; -- requested vs timed SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir();
Where it breaks

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.

Common staff-level mistake

Setting max_wal_size once at install (often 1 GB default-era thinking) for a primary writing hundreds of MB per minute.

The metric that exposes it first

Ratio of requested to timed checkpoints, and peak pg_wal size as a share of the filesystem.

MongoDB · WiredTiger

MongoDB, chunk migration and jumbo chunks

The number, why this mechanism existsA chunk is capped at 128 MB (64 MB in older versions). A chunk whose documents all share one shard-key value cannot be split below that value: a single tenant holding 500 MB under one key is a 500 MB jumbo chunk that the balancer will skip on every pass, forever. Adding shards does not help, the unit that needs to move is the one unit that cannot.
Problem

Shards only help if data spreads evenly; a range that cannot split migrates forever or never moves at all.

Small collectionChunks split and balance automatically. The balancer is boring.
Growth with a good keyChunk counts per shard stay within ~10% of each other.
Transition: skew appearsBalancer runs nightly; the hot shard's load does not fall. Migration logs show chunks being skipped.
Jumbo establishedOne shard at 100% while the cluster idles. The fix is no longer tuning, it is a re-shard migration project.

Situation, the physical organization, drawn for this problem

mongosroutes by shard key ranges
→
Balancermoves chunks off heavy shards
→
Chunk migrationcopy → delta catch-up → cutover
→
Jumbo chunkunsplittable range, will not migrate
Mechanism, derived from the layout

A monotonic shard key routes all new writes to one shard; an unsplittable jumbo chunk (one key value holding more than chunk size, default 128 MB) defeats the balancer permanently.

The tradeoff

Hashed shard keys spread writes perfectly but scatter range queries to every shard. Ranged keys keep locality and risk hotspots. There is no third option, pick per query pattern.

In Practice, real output, red flags named
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.

sh.status() // chunk counts + jumbo flags db.users.getShardDistribution() sh.getBalancerState()
Where it breaks

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.

Common staff-level mistake

Choosing the shard key from the data model instead of from the top five queries, and discovering it after 2 TB.

The metric that exposes it first

Chunk count skew across shards, jumbo chunk count, and per-shard operation rates in mongostat --discover.

MongoDB · WiredTiger

MongoDB, the oplog window is your maximum allowed downtime

The number, why this mechanism existsThe window is one division: oplog size ÷ write rate. 10 GB of oplog at 5 MB/min of writes is a 34-hour downtime budget. Grow the write rate 10× without resizing and the same 10 GB is 3.4 hours, shorter than one routine maintenance. The budget shrinks silently every time the business succeeds; nothing announces it.
Problem

A secondary catches up by replaying the oplog; entries older than the window are gone, and gone means a full resync.

Window measured in daysMaintenance, AZ outages, slow restores all fit comfortably.
Window in tens of hoursPlanned work still fits; unplanned double failures start to matter.
Transition: window < longest planned maintenanceThe next routine patch window becomes a resync event. Nobody changed the oplog, the write rate changed it.
Member returns after the windowFull initial sync at peak traffic: hours of elevated load on the primary plus a second copy of the dataset in flight.

Situation, the physical organization, drawn for this problem

Primary writesoplog entries appended
→
local.oplog.rscapped: fixed size, oldest fall off
→
Secondary down 6 hwindow is 2 h → entries missed
→
Initial syncfull copy, hours, heavy load
Mechanism, derived from the layout

Oplog size divided by write rate is a hard operational budget: maintenance windows, AZ outages, and slow restores must all fit inside it.

The tradeoff

A bigger oplog (oplogSizeMB) buys downtime tolerance with disk on every member and longer retention of rolled-back operations to reconcile.

In Practice, real output, red flags named
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.

rs.printReplicationInfo() // 'log length start to end' = your window in hours db.getSiblingDB('local').oplog.rs.stats().maxSize
Where it breaks

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.

Common staff-level mistake

Sizing the oplog by disk percentage at install and never recomputing the window after write volume grows 10×.

The metric that exposes it first

Oplog window in hours, recomputed monthly against write rate, alert when it falls below your longest planned maintenance.

Remember: Scale work is window math: WAL generated per checkpoint, chunks balanced per shard, oplog hours per outage. If you cannot state the window, you do not know your headroom.
Diagrams · real structures only

Three structures worth seeing with your own eyes. Each one has its one problem.

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.

1. Inside a data page

Problem

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.

Tap a part

Each part has one job.

2. B+ tree lookup: finding one key among millions

Problem

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.
10K rowsScan: 125 pages, 5-20 ms. Passes every test.
1M rowsScan: 12,500 pages, 0.5-2 s. Users start noticing.
500K-2M rowsThe transition zone: scan cost crosses the timeout budget while traffic is also at its highest.
10M rowsScan: ~3-8 s per query. An incident, not a slowdown.

Type a number and watch the lookup walk: root, internal, leaf. Only 3 pages are read, no matter how big the table file gets.

3070
1020
4560
8595
5121827
33486169
72889399
4 buffers, vs scanning every page of the table file
tree depth ≈ 3

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.

3. MVCC: two transactions, one row, no waiting

Problem

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:

Transaction A: the writer

Transaction B: the reader

B sees: -
Try this order: A BEGIN, B BEGIN, A UPDATE, A COMMIT, B SELECT. B still sees 100. That is snapshot isolation, and it is the default behaviour in all three engines.

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.

Quick lookup · reference atlas

Every idea on one card. One problem, one diagram, staff depth.

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".

PostgreSQLStorage

Table files & TOAST

Problem: A table must stay fast to scan even when some rows hold huge values.

Diagram, one problem, one diagram

Heap page 8 KB
→
Value > ~2 KB → TOAST side table
→
Normal rows stay dense

Every table is one file, base//, of 8 KB heap pages. A value bigger than ~2 KB is compressed or moved to a side table (TOAST) and replaced by a pointer, so normal rows stay small and pages stay dense.

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.

Find it: SELECT pg_relation_filepath('users'); · \d+ users
MySQLStorage

users.ibd, the table is the index

Problem: Looking up by primary key should need no separate structure at all.

Diagram, one problem, one diagram

Insert id=42
→
Leaf page in PK order
→
Row lives in the tree

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.

Check: ls $datadir/shop/ · SHOW TABLE STATUS
MongoDBStorage

collection-*.wt files & compression

Problem: Flexible documents shouldn't cost flexible amounts of disk.

Diagram, one problem, one diagram

Document in cache (raw)
→
Checkpoint
→
Compressed blocks in collection-*.wt

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.

Check: db.users.stats().wiredTiger.uri
All 3Storage

The memory cache comes first

Problem: Most reads should never touch a disk at all.

Diagram, one problem, one diagram

Query
→
Cache hit → answer
→
Miss → disk read + cache fill

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.

PG: shared_buffers · MySQL: innodb_buffer_pool_size · Mongo: storage.wiredTiger.engineConfig.cacheSizeGB
PostgreSQLIndexes

GIN, GiST, BRIN, indexes beyond B-tree

Problem: Some questions aren't 'equal to', they're 'contains', 'overlaps', or 'in this range'.

Diagram, one problem, one diagram

Question: contains / overlaps / range
→
GIN · GiST · BRIN
→
Right rows only

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.

Try: CREATE INDEX … USING gin (doc) · USING brin (created_at)
MySQLIndexes

Covering index

Problem: A lookup shouldn't open the table file if the index already has the answer.

Diagram, one problem, one diagram

Secondary index
→
Key + PK stored in leaf
→
Answer without the clustered row

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.

Try: EXPLAIN SELECT name FROM users WHERE name='Asha';
MongoDBIndexes

Compound index order, the ESR rule

Problem: A combined index silently stops helping if its fields are in the wrong order.

Diagram, one problem, one diagram

Equality fields
→
Sort fields
→
Range fields

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.

Try: db.users.find(…).explain('executionStats'), check totalDocsExamined ÷ nReturned
All 3Indexes

Partial (filtered) index

Problem: Indexing rows nobody ever searches for wastes disk and slows every write.

Diagram, one problem, one diagram

All rows
→
Only active rows indexed
→
Smaller index, faster writes

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.

PG: CREATE INDEX … WHERE active · Mongo: partialFilterExpression
PostgreSQLSafety

VACUUM & autovacuum

Problem: UPDATE and DELETE leave dead row versions behind; something must reclaim them or files bloat forever.

Diagram, one problem, one diagram

Dead tuples
→
Autovacuum
→
Reusable space + frozen xids

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).

Check: SELECT relname, n_dead_tup FROM pg_stat_user_tables;
MySQLSafety

Undo log & purge

Problem: Readers need old row versions, and failed transactions need to be undone, both come from the same place.

Diagram, one problem, one diagram

UPDATE
→
Undo record in undo_001
→
Purge when no read view needs it

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.

Watch: “History list length” in SHOW ENGINE INNODB STATUS
MongoDBSafety

Oplog size & the replication window

Problem: A secondary that was down must be able to catch up without copying the whole database.

Diagram, one problem, one diagram

Writes
→
local.oplog.rs (capped)
→
Window = size ÷ write rate

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'.

Try: rs.printReplicationInfo(), 'log length start to end' is your window
All 3Safety

Isolation levels

Problem: When two transactions touch the same rows, someone must decide what each is allowed to see.

Diagram, one problem, one diagram

Two txns, same rows
→
Level decides what each sees
→
Anomalies allowed vs retries paid

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.

Defaults: PG Read Committed · InnoDB Repeatable Read · Mongo snapshot (primary)
PostgreSQLCopies

Streaming vs logical replication

Problem: Sometimes you need an exact clone; sometimes you need only some tables, on a different version.

Diagram, one problem, one diagram

WAL bytes → byte-identical standby
→
Decoded row changes → selective copy
→
Pick per upgrade / subset need

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.

Check: pg_stat_replication · pg_stat_subscription
MySQLCopies

Binlog + GTID

Problem: A replica must always know exactly which transactions it has applied, no guessing after a failover.

Diagram, one problem, one diagram

Commit
→
Binlog event + GTID
→
Replica executes by GTID set

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.

Try: SHOW REPLICA STATUS\G · SELECT @@gtid_executed;
MongoDBCopies

Elections & majority write concern

Problem: If the primary dies at 3 AM, the replica set must recover without a human.

Diagram, one problem, one diagram

Primary lost
→
Majority elects
→
w:majority writes survive

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.

Try: rs.conf() · rs.status() · { writeConcern: { w: 'majority', j: true } }
MongoDBGrowing

Shard key

Problem: Data must spread across servers evenly, and queries must land on as few of them as possible.

Diagram, one problem, one diagram

Document
→
mongos routes by shard key
→
One shard (good) or all shards (scatter)

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().

Try: sh.status() · sh.shardCollection('shop.users', {region: 1, _id: 1})
PostgreSQLGrowing

Declarative partitioning

Problem: Old data should be droppable in milliseconds, and queries on recent data shouldn't scan history.

Diagram, one problem, one diagram

Parent table
→
Pruning by range
→
One month = one file, dropped whole

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.

Try: CREATE TABLE … PARTITION BY RANGE (created_at);
MySQLGrowing

Vitess & ProxySQL

Problem: MySQL has no built-in sharding, something has to route queries when data spans servers.

Diagram, one problem, one diagram

App
→
ProxySQL / Vitess routes
→
Right mysqld shard

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.

Pattern: app → ProxySQL/Vitess → the right mysqld
All 3Speed

Connection pooling

Problem: Thousands of app connections crush every engine, each one costs memory and bookkeeping.

Diagram, one problem, one diagram

1,000 app sessions
→
Pooler multiplexes
→
~cores-sized real connections

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.

PG: PgBouncer · MySQL: ProxySQL · Mongo: maxPoolSize
All 3Speed

Slow query logs

Problem: You can't fix the slow queries you haven't identified.

Diagram, one problem, one diagram

Every statement
→
Over threshold → logged
→
Digest → top by total time

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.

PG: log_min_duration_statement · MySQL: long_query_time · Mongo: db.setProfilingLevel(1, {slowms: 100})
PostgreSQLUnder the hood

WAL & checkpoints

Problem: The WAL can't grow forever; dirty pages must eventually reach their files so segments can be recycled.

Diagram, one problem, one diagram

Dirty pages in shared_buffers
→
Checkpoint
→
WAL segments recycle

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.

Tune: max_wal_size, checkpoint_timeout · Inspect: pg_waldump, pg_stat_checkpointer
MySQLUnder the hood

Redo log & doublewrite buffer

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

Dirty page flush
→
Doublewrite copy first
→
Real place second, torn page recoverable

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.

Tune: innodb_redo_log_capacity · innodb_flush_log_at_trx_commit = 1
MongoDBUnder the hood

Journal & checkpoints

Problem: Data files are only consistent at checkpoints, something must cover the minutes in between.

Diagram, one problem, one diagram

Write
→
journal/WiredTigerLog.*
→
Checkpoint ~60 s makes .wt consistent

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.

Check: ls journal/ · storage.journal.commitIntervalMs
All 3Under the hood

Statistics & the planner

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

Table changes
→
ANALYZE refreshes histograms
→
Planner costs plans from fresh stats

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.

PG: ANALYZE · MySQL: ANALYZE TABLE · Mongo: db.collection.stats()

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.