Reference

I use this page when something is already slow. You do not need a theory here. You need a number that tells you what to fix. This page gives you that number three ways. Annotated EXPLAIN output. A side-by-side compare of the three engines. The five log failure modes. And a checklist with the exact commands. After it, you will read a plan and name the fix from one line.

In Practice · reading the evidence

Explain output, annotated line by line. Red flags named.

Mechanism knowledge is only useful if it ends in a diagnosis. These are the two outputs that settle most production query incidents. Each line below is annotated with what it proves. The red flags are the lines that change the decision.

PostgreSQL: EXPLAIN (ANALYZE, BUFFERS) on a lookup by email

Index Scan using users_email_idx on users
  (cost=0.43..8.45 rows=1 width=68) ← planner estimate: 1 row
  (actual time=0.041..0.043 rows=1 loops=1)
  Index Cond: (email = 'asha@example.com')
  Buffers: shared hit=4             ← 4 pages, all from cache. Healthy.
Planning Time: 0.081 ms
Execution Time: 0.052 ms

What proves health: estimated rows (1) match actual rows (1). Statistics are current. Buffers: shared hit=4 is the index root, internal, leaf, and one heap page. That is exactly the 4 reads the B+ tree math predicts. Red flags in this output shape: Seq Scan in place of Index Scan on a large table. Rows estimated 1, actual 48,000. That is stale statistics. Run ANALYZE before touching the query. Buffers: shared read dominating hit. The working set no longer fits in shared_buffers. On an Index Only Scan, Heap Fetches in the hundreds. The visibility map is stale and the "index-only" scan is silently fetching the heap per row.

MongoDB: explain("executionStats") on an orders query

"winningPlan": { "stage": "FETCH",
  "inputStage": { "stage": "IXSCAN",
    "indexName": "status_1_createdAt_-1_amount_1" } }
"executionStats": {
  "nReturned": 42,
  "totalDocsExamined": 42,      ← equals nReturned: perfect selectivity
  "totalKeysExamined": 43,
  "executionTimeMillis": 3,
  "stage": "SORT"              ← RED FLAG if present in the winning plan
}

The ratio is the diagnostic: totalDocsExamined ÷ nReturned = 1.0 here. At 10×, the index is scanning ten documents for every one returned. That is poor selectivity, usually a low-cardinality leading field. At 1,000×, the index is decorative. A SORT stage in the winning plan means the index does not cover the sort. Results are buffered and sorted in memory, and the query aborts outright at the 100 MB in-memory sort limit (Sort exceeded memory limit of 104857600 bytes). COLLSCAN as the winning stage means no usable index exists for the filter at all. At 10M documents of about 2 KB, that is ~20 GB read to return what may be a single document.

Derivation: why ESR is a theorem, not a conventionA compound index stores keys lexicographically. Sorted by field 1, then within equal field-1 values by field 2, then within equal (field 1, field 2) pairs by field 3. Three consequences follow mechanically. Equality first: an equality predicate on field 1 collapses the scan to one contiguous key range, located in O(log N). Sort second: within that contiguous range, field-2 keys are already stored in sort order, so the scan emits rows sorted. No SORT stage. No 100 MB abort. Range last: a range predicate on a field terminates the usable ordering. Every key after the range boundary must still be visited for subsequent fields, so a range field placed before the sort field destroys the sort guarantee. Reverse the order and the equality condition degrades from an O(log N) seek to a scan of every key inside the range. On a range covering 80% of the index, the index is 80% of a full scan. ESR is the only ordering that preserves all three properties simultaneously, because of how the keys are physically stored. Nothing about it is stylistic.

Paste your own plan: EXPLAIN, annotated

The outputs above are mine. Yours is the one that matters. Paste your EXPLAIN ANALYZE output below. Everything runs in your browser. Nothing leaves this page, and nothing is stored. If your plan has names you do not want to share, replace them before pasting. Prefer an external tool? explain.depesz.com and pgMustard read the same format.

Side by side · the same job, three ways

The same work, done by three different file layouts

Read down a column for one engine's complete physical design. Read across a row for the same job in three layouts. The differences that matter operationally are all here. Page size: 8 KB vs 16 KB vs variable compressed blocks. Log count: one log doing two jobs vs two specialized logs. Where old versions live: in the table file, in separate undo tablespaces, or in cache. Those three choices predict each engine's characteristic failure modes more reliably than any benchmark.

PostgreSQL

Pick it when being exactly right matters more than being flexible.

Write-ahead log
WAL: 16 MB segments in pg_wal/, named like 000000010000000000000003. One log does crash recovery and replication.
The row lives in
A heap file: base/<db oid>/<relfilenode>, 8 KB pages. Find it with pg_relation_filepath('users').
Kept hot in memory by
shared_buffers (plus the OS page cache).
Old versions
UPDATE writes a new tuple in the same file; VACUUM / autovacuum removes dead tuples later.
Replicas stay in sync by
Streaming the WAL byte-for-byte. Check pg_stat_replication.
Look inside with
psql: \d+ users · pg_waldump · EXPLAIN ANALYZE
Watch out
Dead tuples piling up when autovacuum falls behind, and too many connections, front it with PgBouncer.
MySQL · InnoDB

Pick it for a fast website where most lookups are by ID.

Write-ahead logs: two of them
Redo log (#innodb_redo/#ib_redo0-31) for crash recovery, plus the binary log (mysql-bin.000001…) for replicas and point-in-time recovery.
The row lives in
shop/users.ibd, one file per table, and the file is the primary-key B+ tree: rows in ID order, 16 KB pages.
Kept hot in memory by
The InnoDB buffer pool: innodb_buffer_pool_size.
Old versions
Row updated in place; old version rebuilt from undo records in undo_001/undo_002, removed by the purge thread.
Replicas stay in sync by
Replaying the binary log, positioned exactly by GTID. Check SHOW REPLICA STATUS.
Look inside with
SHOW BINARY LOGS · mysqlbinlog · SHOW ENGINE INNODB STATUS
Watch out
Random IDs (e.g. random UUIDs) as primary keys: inserts land all over the .ibd file and split pages constantly.
MongoDB · WiredTiger

Pick it when records all look different and you need to spread across many computers.

Write-ahead logs: two of them
Journal (journal/WiredTigerLog.*) for crash recovery, plus the oplog (local.oplog.rs, a capped collection) for replica sets.
The document lives in
collection-*.wt, one compressed file per collection; _mdb_catalog.wt maps names to files. Indexes are separate index-*.wt files.
Kept hot in memory by
The WiredTiger cache: by default ~50% of (RAM − 1 GB).
Old versions
Timestamped versions kept in the cache; readers read at a snapshot timestamp. Checkpoints (~60 s) write a consistent state to the .wt files.
Replicas stay in sync by
Tailing the primary's oplog. If the primary dies, members elect a new one. Check rs.status().
Look inside with
mongosh: rs.printReplicationInfo() · db.serverStatus().wiredTiger · explain('executionStats')
Watch out
A shard key chosen badly, it is nearly permanent, and queries that must scatter to every shard because they don't include the shard key.

Sharding in the real world: Slack and Shopify both moved their MySQL to Vitess, and both stories are on the Stories page.

The log files · the pattern

Five logs run everything. Know these five.

All 3

The pattern behind all five

log first → data file later
The rule
Two kinds of log appear above. Physical logs (WAL, redo, journal) record what changed in which page, they exist for crash recovery on the same server. Logical logs (binlog, oplog) record what operation happened, they exist so other servers can redo the same operations.
Why two kinds
A replica can't apply "bytes 40-88 of page 7 changed" unless its pages are byte-identical, streaming WAL replication does exactly that. A logical log is portable across versions and layouts, at the cost of re-executing each operation.
The commit rule
In all three engines, COMMIT means one thing: the log record is on disk. The data file catching up is housekeeping (the checkpoint). If you remember one sentence from this page, make it that one.

The five logs, one by one: PostgreSQL: WAL · MySQL: redo log and binary log · MongoDB: journal and oplog

Where the logs break: the five failure modes that page people

Each row is a log, the way it fails in production, and the window math that would have predicted it.

Failure modeRoot causeProduction blast radiusMitigation
WAL cannot recycle; pg_wal/ fills the diskArchiving failing, or a dead standby/replication slot holding segments indefinitelyPrimary panics and stops accepting writes when the filesystem reaches 100%. Recovery requires freeing space without deleting held segments by handAlert on pg_wal size and slot lag; drop dead slots; fix archiving before it is an outage
Redo log wraps into unflushed pagesCheckpoint age approaches innodb_redo_log_capacity because page flushing cannot match the write rateInnoDB forces aggressive flushing; write latency spikes at peak load, in the worst case to near-stallRaise redo capacity (8.0.30+ allows up to 512 GiB); alert at 75% checkpoint age
Binlog purged before a replica reads itbinlog_expire_logs_seconds shorter than the replica's downtimeReplica cannot resume; full re-copy of the source dataset, hours at production scaleRetention longer than the longest planned replica outage; alert on oldest unconsumed binlog
Journal on the same contended volume as data filesJournal fsyncs queue behind checkpoint writes on one deviceEvery j:true write inherits the contention; commit latency degrades instance-wide, not per-querySeparate journal volume; monitor journal commit latency in serverStatus
Secondary down longer than the oplog windowOplog size ÷ write rate < downtime durationFull initial sync at production traffic levels: hours of elevated load on the primary, plus a second copy of the dataset in flightSize the oplog so the window exceeds the longest maintenance; recompute monthly as write rate grows
The DBA checklist · commands included

What to check before go-live, and in the first 5 minutes of an incident

Left: the checks that prevent most of the incidents above, each with its threshold stated as a number. Connections by state. Lag in seconds. Disk at 70% with a growth rate. Oldest transaction under 5 minutes. Right: the triage order when something is already broken. The order is deliberate. "What is running right now" eliminates half of all incident classes in one query, before any dashboard is opened. Every item carries the exact command per engine.

Before go-live

Connections are pooled and capped

App pool sized from DB capacity, pooler in place (PgBouncer / ProxySQL), timeouts set for idle-in-transaction and long statements.

-- PG: SELECT count(*), state FROM pg_stat_activity GROUP BY state; -- MySQL: SHOW VARIABLES LIKE 'max_connections'; SHOW PROCESSLIST; -- Mongo: db.serverStatus().connections

Slow-query capture is ON

You cannot fix what you never recorded. Enable it before launch, with a threshold (100 ms-1 s) and a review habit.

-- PG: log_min_duration_statement = 1000 + pg_stat_statements -- MySQL: slow_query_log=ON, long_query_time=1 (+ pt-query-digest) -- Mongo: db.setProfilingLevel(1, {slowms: 100})

Indexes match the real queries, and unused ones are dropped

Run the app's top 20 queries through EXPLAIN. Then find indexes nobody reads: they still tax every write.

-- PG: SELECT relname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0; -- MySQL: SELECT * FROM sys.schema_unused_indexes; -- Mongo: db.users.aggregate([{$indexStats:{}}])

Replication lag is measured and alerted

Know normal lag (usually < 1 s), alert at 2-5 s, and know the oplog/binlog/WAL window: how long a replica can be down and still catch up.

-- PG: SELECT replay_lag FROM pg_stat_replication; -- MySQL: SHOW REPLICA STATUS\G (Seconds_Behind_Source) -- Mongo: rs.printReplicationInfo(); rs.status()

Disk, bloat, and log retention are bounded

Log retention set by time and size, autovacuum/purge verified working, disk alert at 70% with a growth-rate view, retention jobs for events/sessions tables.

-- PG: SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5; -- MySQL: SHOW BINARY LOGS; (check binlog_expire_logs_seconds) -- Mongo: db.stats(); db.serverStatus().wiredTiger.cache

Backup exists, and a restore has actually been tested

An untested backup is a hope, not a backup. Restore to a scratch server, time it, and verify point-in-time recovery (backup + log replay) reaches a chosen minute.

-- PG: pg_basebackup + WAL archive → restore & recovery_target_time test -- MySQL: XtraBackup / mysqldump → restore + mysqlbinlog replay to a timestamp -- Mongo: mongodump / snapshot → mongorestore to a test replica set

When something breaks, first 5 minutes, in order

1 · What is running right now?

Before logs, before dashboards: ask the database what it is doing this second. One query answers half of all incidents.

-- PG: SELECT pid, state, wait_event_type, now()-query_start AS age, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY age DESC; -- MySQL: SHOW FULL PROCESSLIST; -- Mongo: db.currentOp({active:true, secs_running:{$gt:5}})

2 · Is someone blocking everyone else?

Find the head of the lock chain and the oldest transaction. Killing the blocker is often the whole fix, then fix why it ran long.

-- PG: SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0; -- MySQL: SELECT * FROM performance_schema.data_lock_waits; SHOW ENGINE INNODB STATUS\G -- Mongo: db.currentOp({waitingForLock:true})

3 · Vitals: connections, lag, disk

Three numbers rule out three incident classes in under a minute: pool/connections saturated? replica lag spiking? disk about to fill?

-- Connections: pg_stat_activity count / SHOW PROCESSLIST count / serverStatus().connections -- Lag: pg_stat_replication / SHOW REPLICA STATUS\G / rs.status() -- Disk: df -h + biggest relations (see go-live list)

4 · What changed?

Deploys, a new report, a marketing email, a batch job that “only runs monthly.” Correlate the slowdown start time with the slow log's first bad entries and the top query by total time.

-- PG: SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5; -- MySQL: pt-query-digest on the slow log around the start time -- Mongo: db.system.profile sorted by ts around the start time

5 · Read one bad plan before changing anything

Pick the single worst query from step 4 and explain it. Seq scan on a big table, rows examined ≫ rows returned, or a sort spilling to disk, the plan names the fix.

-- PG: EXPLAIN (ANALYZE, BUFFERS) <query>; -- MySQL: EXPLAIN FORMAT=TREE <query>; -- Mongo: db.coll.find(…).explain("executionStats")

6 · Fix the cause, write down the check

After the fire: the lasting fix (index, pool, timeout, routing, partition) plus one alert that would have caught it at minute one. An incident without a new alert will repeat.

-- Leave behind: slow-log alert · lag alert · oldest-transaction alert · disk growth alert -- All four are one query each, see the cards in “When the app starts hurting”.

Before you leave, check this: run SELECT pid, state, wait_event_type, now()-query_start AS age, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY age DESC; If one old query sits at the top, you found your incident.