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.
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.
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.
"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.
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.
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.
Pick it when being exactly right matters more than being flexible.
Pick it for a fast website where most lookups are by ID.
Pick it when records all look different and you need to spread across many computers.
Sharding in the real world: Slack and Shopify both moved their MySQL to Vitess, and both stories are on the Stories page.
The five logs, one by one: PostgreSQL: WAL · MySQL: redo log and binary log · MongoDB: journal and oplog
Each row is a log, the way it fails in production, and the window math that would have predicted it.
| Failure mode | Root cause | Production blast radius | Mitigation |
|---|---|---|---|
| WAL cannot recycle; pg_wal/ fills the disk | Archiving failing, or a dead standby/replication slot holding segments indefinitely | Primary panics and stops accepting writes when the filesystem reaches 100%. Recovery requires freeing space without deleting held segments by hand | Alert on pg_wal size and slot lag; drop dead slots; fix archiving before it is an outage |
| Redo log wraps into unflushed pages | Checkpoint age approaches innodb_redo_log_capacity because page flushing cannot match the write rate | InnoDB forces aggressive flushing; write latency spikes at peak load, in the worst case to near-stall | Raise redo capacity (8.0.30+ allows up to 512 GiB); alert at 75% checkpoint age |
| Binlog purged before a replica reads it | binlog_expire_logs_seconds shorter than the replica's downtime | Replica cannot resume; full re-copy of the source dataset, hours at production scale | Retention longer than the longest planned replica outage; alert on oldest unconsumed binlog |
| Journal on the same contended volume as data files | Journal fsyncs queue behind checkpoint writes on one device | Every j:true write inherits the contention; commit latency degrades instance-wide, not per-query | Separate journal volume; monitor journal commit latency in serverStatus |
| Secondary down longer than the oplog window | Oplog size ÷ write rate < downtime duration | Full initial sync at production traffic levels: hours of elevated load on the primary, plus a second copy of the dataset in flight | Size the oplog so the window exceeds the longest maintenance; recompute monthly as write rate grows |
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.
App pool sized from DB capacity, pooler in place (PgBouncer / ProxySQL), timeouts set for idle-in-transaction and long statements.
You cannot fix what you never recorded. Enable it before launch, with a threshold (100 ms-1 s) and a review habit.
Run the app's top 20 queries through EXPLAIN. Then find indexes nobody reads: they still tax every write.
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.
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.
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.
Before logs, before dashboards: ask the database what it is doing this second. One query answers half of all incidents.
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.
Three numbers rule out three incident classes in under a minute: pool/connections saturated? replica lag spiking? disk about to fill?
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.
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.
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.
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.