Start here

The full beginner course, in order. Vocabulary first, then the insert, then the incidents.

I run production databases for a living. PostgreSQL, MySQL, MongoDB, Aurora. Ten years of it.

Most of what I know about how databases work, I learned the expensive way. A page at 2 AM. A query that was fine yesterday and slow today. A disk that filled up while nobody was watching. A replica that was three seconds behind and a user staring at data they had just saved.

Nobody handed me a page that started with the files on disk. So I wrote one. This site teaches three databases the way they actually exist on a server: a process, a block of memory, and a directory of files. If you know where the bytes sit, the performance follows. If you do not, you are guessing, and guessing is how you end up restarting a database at 2 AM hoping the problem goes away.

It goes from zero to staff level. Start at the top if you are new. Skip to the incident cards if something is broken right now.

4 buffersAn index lookup at 10M rows: root → internal → leaf, plus the heap page holding the row. At 1 billion rows the tree is one level deeper, 5 buffers. Tree depth grows logarithmically; scan cost grows linearly. That asymmetry is the whole subject of this page.
1,250,000 pagesThe same lookup with no index: a sequential scan of a 10 GB table file at 8 KB per page. At 3 GB/s of NVMe throughput that is 3.3 seconds of pure I/O before a single row is evaluated.
1 fsyncWhat a commit actually costs: one sequential write of the log record, forced to disk. Not the data page. The log. pg_wal, #innodb_redo/#ib_redo0, WiredTigerLog.*. Everything else is deferred.

The whole subject of this page, to scale

Index lookup, 10M rows
4 buffers
Full scan, 10 GB table
1,250,000 pages, 3.3 s

The 4-buffer bar is drawn at 1% so you can see it. At true scale it is 0.0003% as wide as the scan bar. Tree depth grows logarithmically. Scan cost grows linearly.

Three rules that run every database on this site

1

Durability is an ordering rule, not a feature

The log record reaches disk before the data page is required to. That one ordering rule is why a commit survives a power cut that kills everything in memory. Recovery is replay, and replay needs only the log.

PG: pg_wal/ · MySQL: #innodb_redo/#ib_redo0 (redo) · MongoDB: journal/WiredTigerLog.*
2

Lookup cost is page count, nothing else

Rows live in fixed-size pages: 8 KB in PostgreSQL, 16 KB in InnoDB. An index is a sorted structure that cuts a lookup from N pages to roughly logfanout(N) pages. At a fanout near 400, each extra tree level covers about 400 times more data.

PG: base/16384/16397 · MySQL: shop/users.ibd · MongoDB: collection-*.wt
3

MVCC trades disk for non-blocking reads

Old row versions stay around until no open snapshot can still see them, so readers never wait for writers. The cost is deferred, not gone. Every old version is cleanup debt, collected by VACUUM, purge, or WiredTiger reconciliation. One long transaction can block all three.

PG: old tuples + VACUUM · MySQL: undo_001/undo_002 · MongoDB: timestamped versions in cache
Vocabulary: each term with its physical referent

Six terms. Every one of them is a file, a size, or a number.

PageThe fixed-size chunk a database reads and writes. Never one row at a time. Always a whole page.PostgreSQL: 8 KB · InnoDB: 16 KB · WiredTiger: variable blocks, compressed
Write-ahead log (WAL)The log file where every change is recorded before the data file changes. "Write-ahead" is the whole trick.PG: pg_wal/ · InnoDB: redo log · WiredTiger: journal/
Index (B+ tree)A sorted structure that maps a key to the exact page holding the row, in about 3 jumps instead of a full scan.PG: its own file in base/ · MySQL: inside the .ibd · MongoDB: index-*.wt
TransactionA group of changes that either all commit or all roll back. COMMIT means the log record is forced to disk.BEGIN; … COMMIT; · tracked by xid (PG), trx id (InnoDB), txnNumber (MongoDB)
CheckpointThe moment dirty pages in memory are written to their real data files, so the log before that point can be recycled.PG: checkpoint · InnoDB: checkpoint LSN · WiredTiger: every ~60 s
ReplicaA second server that replays the main server's log and ends up with identical data. The delay is called lag.PG: streams WAL · MySQL: replays binlog · MongoDB: tails the oplog
Start here · for the beginner

What each database actually is

Three programs, one job: keep data in files on disk, serve it from memory, and prove that a commit survived. They differ in exactly three physical choices: how the data file is organized, how many logs there are, and where old row versions live. Every behavior difference in the rest of this page follows from those three choices. Start with the one plain line per engine. The architecture, the log files, the people, and the outage parameters follow.

EngineData fileLogsOld versions live
PostgreSQLHeap files in base/, 8 KB pagesWAL, pg_wal/Old tuples in the heap, removed by VACUUM
MySQL · InnoDBusers.ibd, 16 KB pages, clusteredRedo plus binlogUndo records, removed by purge
MongoDB · WiredTigercollection-*.wt, compressed blocksJournal plus oplogTimestamped versions in cache, removed by reconciliation

Who manages what, on-prem

On-prem, three teams touch the same server. Most 2 AM arguments are boundary disputes. So here are the boundaries, plainly. The DBA owns everything the database process does. The sysadmin owns the machine it runs on. Storage owns the bytes underneath both.

Database administrator (DBA)

  • Installs and configures the engine; owns every parameter in the tables below
  • Schema, indexes, and query performance; reviews plans for new queries
  • Backups, point-in-time recovery, and restore tests
  • Replication topology, lag, failover decisions
  • Users, roles, and access inside the database
  • Cleanup health: autovacuum / purge / checkpoint tuning

Sysadmin / OS team

  • The machine or VM: CPU, RAM, OS patching, kernel limits (open files, huge pages)
  • Filesystems mounted with the right options; the data directory exists and is owned by the DB user
  • Process supervision (systemd), NTP/time sync, DNS
  • Network between app, primary, and replicas; firewall rules
  • Host-level monitoring: CPU steal, memory pressure, I/O wait

Storage team

  • The volumes themselves: RAID / SAN / cloud disks, IOPS and throughput provisioning
  • Separate volumes where it matters: log files (WAL / redo / journal) vs data files
  • Snapshots at the storage layer, and their retention
  • Disk expansion when the DBA's growth forecast says so
  • Latency evidence when the DBA says "the disk is slow": device-level stats to prove or disprove it

The seams, where incidents actually live: a full disk is a storage problem until the cause turns out to be WAL the DBA's archiving failed to move. A "slow database" is a DBA problem until device latency shows the volume is saturated. Run the check in your layer first, with numbers, before you escalate across the seam.

SymptomLooks likeCheck first
Full diskA storage problemWAL archiving, the DBA side, before blaming the volume
"Slow database"A DBA problemDevice latency, the storage side, before blaming the queries

The standard outage runbook: same steps, every engine

Engines differ. The order of operations does not. These eight steps are the checklist triage, expanded into the full incident arc. The step most often skipped is step 7. That is why emergency parameter values are still in production years later.

1

Declare and timestamp. Name an incident lead, note the start time, and check what changed in the last hour: deploy, config push, batch job, marketing send. A majority of outages correlate with a change. Find it before touching the database.

2

Ask what is running right now. pg_stat_activity / SHOW FULL PROCESSLIST / db.currentOp({active:true}). One query eliminates half of all incident classes before any dashboard is opened.

3

Find the blocker and the oldest transaction. Lock chains have a head. Cleanup horizons have an owner. Kill the runaway report or the idle-in-transaction session. Confirm first that it is not a payment mid-flight.

4

Take the three vitals. Connections by state, replication lag, and disk broken down by owner (data files vs log files). Three numbers, three incident classes ruled in or out, under a minute.

5

Apply the smallest lever that matches the evidence. Kill the blocker, fail over, shed reads to replicas, or raise the one reloadable parameter the evidence names (max_wal_size, redo capacity, oplog size). Restarting is the last resort, not the first. A restart replays the log and often returns the same load to a colder cache.

6

Verify recovery with numbers, not relief. Lag draining toward zero, error rate falling, checkpoint age / history list length / dirty bytes moving the right direction. State the values out loud in the incident channel.

7

Record every change you made. Old value, new value, why, and a scheduled time to revisit. Emergency settings left in place become next quarter's mystery configuration. The flush=2 that ships is always one that was "temporary".

8

Postmortem within days. Root cause, blast radius, and the one alert that would have caught it at minute one. Then actually add that alert. An incident without a new alert is scheduled to repeat.

The main event · one insert, start to finish

Follow one row: INSERT INTO users, end to end

On a healthy system the whole insert returns in about 1 ms. Inside that millisecond, 11 steps run in a fixed order across four places: the log file, the data page, the index, and the replica. Pick an engine, press Play, and step through all 11. The four panels underneath show the state of each place changing. Nothing on this page is asserted without a location you can inspect.

Problem

The order is not an implementation detail. It is the durability guarantee.

If the data page reached disk before its log record, one reordering, a crash in that window leaves a page describing a change that recovery cannot verify or undo. Every engine below enforces the same order: log first, page later, commit only after the log is on disk. The file names differ. The order does not.

INSERT INTO users (id, name) VALUES (42, 'Asha');

One new user: id 42, name 'Asha'. The table already has users 10, 25 and 87. Follow row 42.

1 · Appsends the statementlibpq / JDBC / driver
2 · Serverparse, check, find pagebackend process
3 · Log filewritten FIRSTpg_wal/
4 · Data pagerow placed in memoryshared_buffers
5 · Indexkey → pointer addedusers_pkey
6 · Replica + disklog replayed, page flushed latercheckpoint

Press Play, or tap any step below

Every step names the real file or memory area it touches, and the four boxes underneath show the state changing for real: what landed in the log file, what is in the data page, what is in the index, and what the replica has.

Log file: on disk
Empty. Nothing written yet.
Data page
Existing rows live here. Row 42 is not in yet.
Index: sorted keys
102587
42 has no entry yet. A search for 42 would find nothing.
Replica
The replica has users 10, 25, 87, same as the primary. No row 42 yet.
Step 0 of 11

The one moment that matters: the row is truly safe at the COMMIT step, when the log is forced to disk. Not when the data page is written. The page can follow minutes later, at the next checkpoint. Switch databases above and replay it. The file names change (WAL, redo log plus binlog, journal plus oplog). The order of the trick does not.

Try it live: a real Postgres, in your browser

No install, no account. This panel runs a real PostgreSQL in your browser. It starts with the same users table as the walkthrough above, ids 10, 25, and 87. Run the insert, then the select, then the EXPLAIN, and watch the same numbers come out of a real plan.

Live Postgres is not started yet. Press the button below.

Nothing run yet.

The same table as a file: seed.sql. Run it in psql, the mysql client, or mongosh and every example on this site reproduces.

Every operation · end to end, like the insert

The other three journeys: read, update, delete, and a crash

Same path as the insert. Parse, index, page, log. Writes append to the log before they modify any page. Reads append to no log at all. Crash recovery is a function of exactly one input, the log written since the last checkpoint. The default max_wal_size of 1 GB caps that log: at 50 MB/s of WAL, the budget fills in about 20 seconds, so a checkpoint fires about every 20 seconds and at most 1 GB replays. How long that replay takes is 1 GB divided by your replay speed, and the server log tells you the speed: during recovery PostgreSQL logs a "redo starts at" line and a "redo done at" line, each timestamped, and the difference between their LSNs is the bytes replayed. Bytes divided by seconds is your replay speed. That number, not the crash itself, sets your recovery time.

Checkpoint math, from the defaults

Log budget1 GB, max_wal_size
÷
WAL rate50 MB/s
=
Checkpointabout every 20 s
Replay time: 1 GB divided by your replay speed. The server log prints a "redo starts at" line and a "redo done at" line, each timestamped. Bytes between the two LSNs, divided by seconds between the two lines, is your replay speed.

SELECT: reading one row

Problem: Find row 42 without reading the whole table file, and without seeing a half-written row.

SELECT * FROM users WHERE id = 42;
1

Parse + plan. The planner checks the catalog and picks a route: index lookup on users_pkey (4 buffers: 3 index pages, 1 heap page) beats scanning the whole table file. EXPLAIN shows this decision.

2

Index walk. Root page → internal page → leaf page of the index: key 42 → pointer “data page 7, slot 3”. No other pages are touched.

3

Page from memory if possible. Hit in shared_buffers / buffer pool / WiredTiger cache = instant. Miss = read that one page from the data file (base/…/16397 / users.ibd / collection-*.wt) once and cache it.

4

Visibility check (MVCC). Is this row version committed, and was it live at your snapshot? If a newer version exists, follow to the version your snapshot should see.

5

Row returned. Nothing was written: no WAL record, no redo, no binlog event, no journal entry, no oplog entry. Reads leave no trace in any log file.

End state: log files unchanged. Data files unchanged. Indexes unchanged. Replica unchanged. A SELECT writes nothing. Its cost is exactly the pages it read: 3 to 4 on an index hit, the full table on a bad plan.

UPDATE: changing row 42's name

Problem: Change a row without anyone ever seeing a half-changed row, and without locking readers out.

UPDATE users SET name='Asha K.' WHERE id=42;
1

Find the row first, exactly like SELECT steps 1 to 3: index walk, then page into memory.

2

Lock just that row so two updates can't collide. Readers don't take this lock. They read snapshots instead.

3

Log FIRST. PostgreSQL writes a WAL record with the new tuple. InnoDB writes redo for the page change plus an undo record holding the old values. WiredTiger writes a journal entry.

4

New version created. PostgreSQL: a brand-new tuple in the heap file, old one marked dead. InnoDB: row updated in place in users.ibd, old version rebuildable from undo. MongoDB: new version in the WiredTiger cache.

5

Indexes fixed only if needed. The id index is untouched. Id did not change. An index on name gets its 'Asha' entry replaced with 'Asha K.'.

6

COMMIT, log forced to disk, visible. The old version stays until no snapshot needs it, then VACUUM / purge removes it. The replica replays the same change from the WAL / binlog / oplog.

Compare to INSERT: same road, one extra idea. The old version must survive until old readers finish.

UPDATE: the old version is not gone yet

UPDATEwrites a new version
→
Old versionstays, old readers still see it
→
No snapshot needs itcleanup horizon passes
→
RemovedVACUUM / purge

DELETE: removing row 42

Problem: Remove a row instantly for new readers, but not while an old reader still needs it.

DELETE FROM users WHERE id = 42;
1

Find + lock the row via the index, same as UPDATE steps 1-2.

2

Log FIRST. The delete is recorded: WAL record, redo plus binlog event, journal plus oplog entry. Including enough of the old row to undo it or replay it on a replica.

3

The row is only marked deleted, not erased. PostgreSQL stamps xmax on the tuple. InnoDB sets a delete flag on the record. WiredTiger removes it from the visible version chain. The bytes are still in the file.

4

COMMIT, log on disk, new readers see nothing. A search for 42 returns “not found”. Readers who started earlier still see the row, correctly, from their snapshot.

5

The replica replays the delete from its WAL stream / binlog / oplog and marks its copy the same way.

6

Much later: real erasure. When no snapshot can still see it, VACUUM / purge / the next WiredTiger checkpoint removes the row bytes and its index entries.

This is why a DELETE of 10 million rows returns in seconds and the file does not shrink by one byte. Step 3 writes one stamp per row. Step 6, the physical removal, is deferred to a background process. Its throughput decides when the space comes back. A table that is deleted from heavily and never vacuumed or purged grows forever while its live row count stays flat.

DELETE: marked, not erased

DELETEstamps the row: xmax / delete flag
→
New readerssee nothing
→
Bytesstill in the file
→
LaterVACUUM / purge / checkpoint removes them

CRASH + RESTART: the log files earn their keep

Problem: Power cuts mid-work. Some transactions said “saved”, some were half-done. Sort it out perfectly on restart.

-- power cut -- → restart the database
1

Memory is gone. Every dirty page that never reached its data file is lost: shared_buffers, buffer pool, WiredTiger cache, all empty. This is expected, and fine.

2

Find the last checkpoint. PostgreSQL reads global/pg_control for the checkpoint's WAL position. InnoDB finds the checkpoint LSN in the redo log. WiredTiger reads its metadata in WiredTiger.wt. Everything before that point is already in the data files.

3

Replay (redo) the log after the checkpoint, in order: pg_wal segments / #innodb_redo/#ib_redo0 / journal/WiredTigerLog.*. Every committed change is rebuilt, page by page.

4

Undo the unfinished. InnoDB rolls back uncommitted transactions from its undo records. PostgreSQL simply never marks them committed in pg_xact, so their tuples stay invisible. Either way, no COMMIT record in the log means it never happened.

5

Open for business. Committed = present, uncommitted = gone, nothing half-way. Only now are new connections accepted.

6

Replicas re-sync from wherever they stopped: WAL LSN (PostgreSQL), GTID (MySQL), oplog timestamp (MongoDB).

The rule that makes all of this work is INSERT step 5. The log reaches disk before the data page ever needs to. Measure your own replay speed after a real crash: the server log prints a "redo starts at" line and a "redo done at" line, each timestamped, and the "redo done at" line prints its own elapsed time. Bytes between the two LSNs, divided by seconds between the two lines, is your replay speed.

Measure your own replay speed

"redo starts at"LSN and time
→
"redo done at"LSN and time
→
Replay speedbytes between LSNs ÷ seconds between lines
Real examples

3 real problems, 3 different answers

Each workload has a dominant cost. Correctness under concurrency, read volume, or write distribution. Each engine's layout prices those costs differently. The settings named are the ones that move the number.

WorkloadDominant costAnswer
Payments: handling moneyCorrectness under concurrencyPostgreSQL
Product catalogue: lots of browsingRead volume, almost nobody buyingMySQL plus read replicas, or MongoDB
User profiles: users in 40 countriesWrite distribution, data close to each userMongoDB, sharded by region

Payments: handling money

Problem: Never lose or double-count a single payment.

Answer: PostgreSQL. Strongest correctness defaults, and any question can be asked of the data later.

  • Append-only ledger table; never UPDATE a posted entry
  • UNIQUE constraint on the payment reference, so a retry can't double-charge
  • Synchronous standby: synchronous_commit = on + a sync replica in pg_stat_replication
  • Base backups + WAL archiving, and restore tests on a schedule

Product catalogue: lots of browsing

Problem: 200,000 people browsing at once, almost nobody buying.

Answer: MySQL + read replicas, or MongoDB if every product has totally different attributes.

  • Covering indexes for the top 5 queries. Check for “Using index” in EXPLAIN
  • Reads served from replicas replaying mysql-bin.*; a few seconds of lag is acceptable for browsing
  • Sequential product IDs so inserts append neatly in the .ibd files
  • Cache hot pages in the buffer pool: size innodb_buffer_pool_size first

User profiles: users in 40 countries

Problem: Data must be fast and close to each user, all over the world.

Answer: MongoDB, sharded by region. Each region's shard holds its own users' documents.

  • Shard key = { region: 1, _id: 1 }, so a profile lookup routes to exactly one shard
  • Writes with writeConcern: { w: 'majority', j: true }. Majority plus journal before “saved”.
  • One document per profile, shaped like the profile screen reads it
  • Watch the oplog window (rs.printReplicationInfo()) before any planned downtime

Quick check: did it click?

6 questions, each about a real file or log you met above. Tap an answer.

Real world · symptom first

When the app starts hurting

The app reports symptoms in its own words: p99 latency, pool exhaustion, random 500s. The database holds the cause in exactly three places. What is running right now (pg_stat_activity / SHOW PROCESSLIST / db.currentOp()). What is slow in total (pg_stat_statements / slow log / profiler). And why one query costs what it costs (EXPLAIN ANALYZE / explain("executionStats")). Run them in that order. Every card below follows it.

All 3 · most common

1 · Pages that were fast are now slow. No deploy, no traffic spike

App sees: p99 goes 60 ms → 3 s. Timeouts on product / checkout pages. DB CPU high, app CPU normal.

Inside the DB: Usually a missing or unused index. The planner stops using the B+ tree and scans the whole table file, page by page: base/…, users.ibd, or collection-*.wt. Every page it reads evicts a hot page from shared_buffers, the buffer pool, or the WiredTiger cache. The growth curve is the tell. At 10K rows the scan was 5 to 20 ms and invisible. At 1M rows it is 0.5 to 2 s. The incident happens somewhere between 500K and 2M rows, with no deploy and no code change. Only data volume crossed a threshold. Or stats went stale after a bulk load, so the planner thinks a scan is cheaper. Blast radius: the scan does not only slow its own query. Every page it reads evicts a cached page another query needed, so unrelated endpoints degrade at the same time.

DBAs usually suggest

“Add CPU / bigger instance.” “Restart it.” “Add an index on every column in the WHERE.” All three mask it for a day and make writes slower.

What to actually check

Find the top query first, then read its plan. Look at rows examined versus rows returned. 1,000,000 examined for 1 returned means a missing index.
-- PG SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10; EXPLAIN (ANALYZE, BUFFERS) SELECT …; -- MySQL SHOW FULL PROCESSLIST; EXPLAIN SELECT …; -- rows + Extra // MongoDB db.users.find({email:"…"}) .explain("executionStats") // totalDocsExamined vs nReturned

Fix that lasts: Add the one index the plan asks for, or refresh stats with ANALYZE. Then verify the plan flips to an index scan. Drop unused indexes. Prevent: slow-query log on from day one. Alert on pg_stat_statements mean time. Review plans in code review for new queries.

All 3

2 · “Connection pool exhausted”: app queues, DB looks idle

App sees: Requests hang waiting for a connection, then 500. DB CPU is low. Restarting the app “fixes” it for an hour.

Inside the DB: Every connection costs a process or thread plus memory plus bookkeeping. A few long or leaked transactions hold connections while doing nothing (idle in transaction). The DB is not busy. The app is starved of slots.

DBAs usually suggest

“Raise max_connections to 5,000.” That increases memory and lock contention and makes a real overload worse.

What to actually check

Count connections by state first. Lots of idle-in-transaction means an app leak or a missing commit, not a sizing problem.
-- PG: who is holding what? SELECT state, count(*) FROM pg_stat_activity GROUP BY state; SELECT pid, now()-xact_start AS age, query FROM pg_stat_activity WHERE state='idle in transaction' ORDER BY age DESC; -- MySQL SHOW PROCESSLIST; -- long Sleep / Query SELECT COUNT(*) FROM information_schema.processlist; -- MongoDB db.serverStatus().connections db.currentOp({active:true})

Fix that lasts: Put a pooler in front (PgBouncer, ProxySQL, driver maxPoolSize), set statement and idle-transaction timeouts, and fix the code path that leaks transactions. Size the pool from DB cores, not app instances. Prevent: alert on connections by state, not just total.

All 3

3 · Random failures: deadlocks and lock waits

App sees: Sporadic “deadlock detected, retry” errors, or one request hangs for 30 s then succeeds. Worse at peak, fine in tests.

Inside the DB: Two transactions lock the same rows in opposite order (A locks row 1 then 2, B locks 2 then 1), or one long transaction holds a row lock while everyone else queues. The engine kills one victim, correctly. The app must retry.

DBAs usually suggest

“Increase the lock timeout” or “set isolation to the weakest level.” Longer waits just make the queue longer.

What to actually check

Find the blocker, not the blocked. One PID is usually at the head of every chain.
-- PG: blocker → blocked pairs SELECT pid, pg_blocking_pids(pid) AS blocked_by, query, wait_event_type FROM pg_stat_activity WHERE wait_event_type='Lock'; -- MySQL: read the last deadlock in full SHOW ENGINE INNODB STATUS\G -- section: LATEST DETECTED DEADLOCK SELECT * FROM performance_schema.data_lock_waits; -- MongoDB db.currentOp({waitingForLock:true})

Fix that lasts: Always lock rows in the same order (sort IDs before updating), keep transactions short, add the index so the update touches fewer rows, and retry deadlocks in the app with jitter. Prevent: log lock waits over 1 s, alert on deadlock rate.

All 3

4 · Stale reads: “I saved it, refreshed, and it's old”

App sees: User edits a profile, saves, reloads, old value. A minute later it's correct. Support calls it “data loss.” It isn't.

Inside the DB: Writes go to the primary, reads go to a replica. The replica replays the log (WAL stream, mysql-bin.*, local.oplog.rs) and is seconds behind under load. The read hit a replica that had not replayed that commit yet.

DBAs usually suggest

“Send all reads to the primary.” It fixes it and wastes the replicas you bought for scale.

What to actually check

Measure lag at the moment of the complaint, and check why: write burst, long query on the replica, or single-threaded apply.
-- PG SELECT client_addr, state, replay_lag, pg_size_pretty(pg_wal_lsn_diff( pg_current_wal_lsn(), replay_lsn)) AS behind FROM pg_stat_replication; -- MySQL SHOW REPLICA STATUS\G -- Seconds_Behind_Source, Replica_SQL_Running -- MongoDB rs.status() // optime lag per member rs.printSecondaryReplicationInfo()

Fix that lasts: Read-your-own-write routing. After a write, read that user's data from the primary for a few seconds. Replicas serve browsing and search, where staleness is fine. Fix the lag cause if it is structural. Prevent: alert on lag > 2 s. Never route “just wrote it” reads to replicas blindly.

PostgreSQLMySQL

5 · Everything slows down over days. Disk grows, rows do not.

App sees: Gradual slowdown all week. Table has the same live rows but the file is 5× bigger. A restart or a quiet weekend doesn't help.

Inside the DB: Bloat from a pinned snapshot. One long or idle transaction stops cleanup. PostgreSQL VACUUM cannot remove dead tuples, so the heap file bloats. InnoDB purge cannot drop undo, so the history list grows and every read walks a longer version chain. The cause is one forgotten transaction, often a report or an idle app server.

DBAs usually suggest

“Run VACUUM FULL tonight” (locks the table) or “just restart.” It reclaims space once and the bloat returns next week.

What to actually check

Find the oldest open transaction first. That is the bug. Then measure the bloat it caused.
-- PG: oldest transaction + dead tuples SELECT pid, now()-xact_start AS age, state, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 5; SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC; -- MySQL: undo backlog SHOW ENGINE INNODB STATUS\G -- TRANSACTIONS: History list length -- >10k and growing = purge is blocked

Fix that lasts: Kill or fix the long transaction, set idle_in_transaction_session_timeout and statement_timeout, run reports on a replica, and tune autovacuum per hot table instead of globally. Prevent: alert on oldest transaction age > 5 min and on n_dead_tup / history list growth.

All 3

6 · Disk full: writes fail, database goes read-only

App sees: Inserts start failing, then the DB refuses writes entirely. Often at 2 AM, always “suddenly”. It never is.

Inside the DB: Three usual fillers. Logs that pile up, bloat (card 5), or a genuinely unbounded table (events, audit, sessions never deleted). The log rules differ by engine. In PostgreSQL a dead standby or a replication slot can hold WAL past every checkpoint. In MySQL the binlog is purged by time, not by replica: binlog_expire_logs_seconds sets the age, 2592000 seconds (30 days) by default, auto-purge is on by default, and expired files go at startup and when the binlog is flushed. A replica that is disconnected holds nothing back. Only a connected replica that has not yet read a file keeps that one file. So a MySQL disk full of binlogs means retention times write rate outgrew the disk, or retention was set to 0, or auto-purge was off, or a write burst filled it faster than purge could keep up. Deleting log files by hand corrupts recovery. The engine must release them.

DBAs usually suggest

“rm the old WAL / binlog files to free space now.” Never do this. You can destroy crash recovery and replication in one command.

What to actually check

Break the disk down by owner before touching anything: data, WAL/binlog, temp, bloat.
$ du -sh $PGDATA/pg_wal $PGDATA/base -- PG: biggest relations + WAL held by whom? SELECT pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10; SELECT * FROM pg_stat_replication; -- which standbys are connected? SELECT * FROM pg_replication_slots; -- dead slot holding WAL? -- MySQL SHOW BINARY LOGS; -- how far back, how big? SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'; -- default 2592000 seconds (30 days) SHOW REPLICA STATUS\G -- which replicas are connected, and how far behind? -- MongoDB db.stats() // per-db storageSize rs.printReplicationInfo() // oplog size

Fix that lasts: PostgreSQL: drop a dead replication slot and repair WAL archiving, that is what releases held WAL. MySQL: check every replica position first, then free space with PURGE BINARY LOGS, then set binlog_expire_logs_seconds to a value your write rate and disk can actually hold. A replica that still needs a purged file fails with error 1236 and must be rebuilt from a backup, so check positions before you purge. Set log retention by time and size. Partition time-series tables so old months drop as files. Alert at 70 percent, not 98 percent. Prevent: a disk growth-rate alert. “Full in 3 days at this rate” beats “full now.”

App design

7 · One page, 500 queries: the N+1 problem

App sees: A list page with 100 items takes 100× longer than a detail page. Each query is 1 ms. The total is seconds. DB “QPS” looks enormous.

Inside the DB: Nothing is broken in the DB. The app runs 1 query for the list, then 1 query per item for its author, price, status. Every round trip pays parse, plan, pool checkout, and network. The DB is doing exactly what it was asked, 500 times.

DBAs usually suggest

“The database is slow, scale it up.” The query is 1 ms. A bigger DB runs the same 500 round trips.

What to actually check

Look at calls, not just time. One query shape with 500k calls per hour is the fingerprint.
-- PG: same shape, absurd call count SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY calls DESC LIMIT 10; -- MySQL SELECT DIGEST_TEXT, COUNT_STAR FROM performance_schema.events_statements_summary_by_digest ORDER BY COUNT_STAR DESC LIMIT 10; // MongoDB: profiler, same shape repeated db.system.profile.find({ns:"shop.orders"}) .sort({ts:-1}).limit(5)

Fix that lasts: Fetch in one query: a SQL JOIN or WHERE id IN (…), an ORM eager-load, a DataLoader batch, or MongoDB $lookup or one $in query. Prevent: count queries per request in the app (APM), set a budget (say 10 per page) and fail CI over it.

MongoDBAll 3

8 · One tenant / one product / one shard is hot, the rest is fine

App sees: One big customer's dashboard times out; everyone else is fast. Or one flash-sale product page melts while the catalogue is fine. One node is at 100%, the rest idle.

Inside the DB: Load is concentrated, not large. All writes hit one row (a counter), one page range, or one shard chunk. A monotonic shard key sends every new write to the same shard. Or one tenant is 100× the others and shares their tables and indexes.

DBAs usually suggest

“Add more shards / nodes.” New nodes sit idle. The hot key still routes to the same place.

What to actually check

Distribution, not totals: per-shard load, per-key lock waits, which single row everyone updates.
-- PG/MySQL: lock waits concentrated on one row? SELECT * FROM pg_stat_activity WHERE wait_event_type='Lock'; -- + slow log grouped by literal id -- MongoDB: is traffic even across shards? sh.status() // chunk distribution db.users.getShardDistribution() mongostat --discover // per-node rates side by side db.currentOp({secs_running:{$gt:5}})

Fix that lasts: Spread the key. Bucket counters (write to 1 of 32 counter rows and sum on read), hash-prefix a monotonic shard key, give the whale tenant its own database or shard. Cache the hot read. Prevent: choose shard and partition keys from the query pattern before launch. See the design map below.

The pattern in all eight: the usual suggestion treats the loudest symptom. Add hardware, raise a limit, restart. The check that works measures first. pg_stat_activity, SHOW PROCESSLIST, db.currentOp() for what is running right now. The slow log and pg_stat_statements for what is slow in total. EXPLAIN ANALYZE for why this one query. Run them in that order.

The three checks, in order

1 · What is runningpg_stat_activity / SHOW PROCESSLIST / db.currentOp()
→
2 · What is slow in totalpg_stat_statements / slow log / profiler
→
3 · Why this one queryEXPLAIN ANALYZE / explain("executionStats")
Design doc · which app sits where

Pick the database from the workload, not the fashion

"Which database?" is the wrong question. "Which workload, at what volume, with what correctness requirement?" is the one you can answer. Each workload below is stated with the property that decides it: access pattern, consistency requirement, retention shape. When two workloads with incompatible properties share one engine, the failure does not appear at launch. It appears in the incident cards above, at the volume where the properties start to conflict.

Problem

One database forced to serve OLTP, analytics, search, and cache workloads degrades all four. Measurably, and in a predictable order.

The analytical scan evicts the OLTP working set first (incident #1), then its long snapshot pins cleanup (incident #5), then its result set pressures the pool (incident #2). Match each workload to its natural home first. Argue about vendors second.

One database, four workloads: the predictable order

1analytical scan evicts the OLTP working set (incident 1)
→
2long snapshot pins cleanup (incident 5)
→
3result set pressures the pool (incident 2)

OLTP: orders, payments, bookings, accounts

→ PostgreSQL or MySQL (relational, ACID)

Many small reads and writes, strict correctness, joins across entities, constraints that must hold under concurrency. This is the home workload of everything above.

Real examples: checkout & ledger, airline reservations, SaaS billing. Start here for almost every product.

Analytics: dashboards, reports, BI

→ A warehouse / columnar store (or a replica, if small)

Scans billions of rows, sums a few columns, tolerates minutes-old data. Columnar storage plus a separate replica keeps scans off the OLTP primary. Running them on the primary is incidents 1 and 5 waiting to happen.

Real examples: revenue dashboards, funnel reports. Small scale: read replica + partitioning. Large: sync OLTP → warehouse by WAL/binlog CDC.

Full-text search: “find products like…”

→ Elasticsearch / OpenSearch, or Postgres FTS if modest

Search needs an inverted index (word → documents), ranking, typos, facets. A B+ tree on a text column is not that. Postgres tsvector + GIN is genuinely fine until ranking and facets get serious.

Real examples: product search, log search, support-ticket search. Source of truth stays in the OLTP DB; the search index is derived and rebuildable.

Cache & sessions: sub-millisecond, disposable

→ Redis / Memcached. Never the source of truth

Session tokens, rate-limit counters, hot product pages. In-memory, TTL on everything. Loss is an inconvenience, not a disaster. If losing it loses money or users, it does not belong here alone.

Real examples: login sessions, shopping-cart cache, leaderboard (Redis sorted sets).

Queues & background jobs

→ A real queue (SQS, Kafka, Redis Streams). Postgres can do small scale

Jobs need at-least-once delivery, retries, and ordering trade-offs. Postgres SELECT … FOR UPDATE SKIP LOCKED is a legitimate queue up to thousands of jobs a minute and saves an extra system. Beyond that, or for event streaming, use the dedicated tool.

Real examples: email sending, image processing, event pipelines (Kafka when many consumers replay the same events).

Time series: metrics, IoT, events over time

→ Partitioned Postgres / TimescaleDB / a TSDB

Append-mostly. Queries are always “last N hours” or “by day”. Old data compresses or drops. Time partitioning makes retention a file drop, not a billion-row DELETE (which is bloat, card 5).

Real examples: sensor readings, clickstream, application metrics. Key design point: partition + retention policy on day one.

Documents: catalogues, profiles, content

→ MongoDB, or Postgres JSONB if the rest is relational

Records whose fields genuinely differ per item, read whole as one aggregate. If you constantly join documents back together, you wanted relational. If only some fields vary, JSONB in Postgres often beats a second database.

Real examples: product catalogue with per-category attributes, user profiles, CMS content.

Graph: relationships are the query

→ Neo4j / a graph store, recursive SQL if shallow

“Friends of friends who bought X”, fraud rings, dependency trees. Traversals that are one query in a graph store are recursive joins in SQL. Depth 2 to 3: a Postgres recursive CTE is fine. Deep, constant traversal: a dedicated graph DB.

Real examples: recommendations, fraud detection, org/permission hierarchies.

Same app, three answers: an online store

One store: products, orders, payments, seller dashboard. All three engines can run it. Here is when each is the right call, honestly stated.

Pick PostgreSQL when…

Correctness and questions-you-haven't-asked-yet matter most.

Why it fits
Orders, payments and inventory in one ACID database, rich SQL for the seller dashboard, JSONB for the few product attributes that vary. No second DB needed early.
You get
Strongest constraints, best planner, partitioning and logical replication when you outgrow one box.
Watch out
Connection counts (front with PgBouncer), autovacuum on the hot orders table.
Pick MySQL when…

The workload is mostly lookups by ID at very large web scale.

Why it fits
Product page = PK lookup in products.ibd, read replicas are operationally simple, and the Vitess path exists if you ever need true sharding.
You get
Huge operational ecosystem, fast simple reads, GTID replication.
Watch out
Weaker for complex analytical SQL on the same box; random UUID PKs hurt (use sequential IDs).
Pick MongoDB when…

The catalogue is the product, and it refuses to fit a fixed schema.

Why it fits
Each product is one document shaped like its page; sharding by region is built in when one store becomes forty countries.
You get
Flexible documents, built-in replica sets + elections, native sharding.
Watch out
Money and inventory still want transactions and joins. Model orders as aggregates carefully. The shard key choice is near-permanent.

When to split across two databases

Splitting is not failure. Forcing one engine to do a job it is shaped wrong for is. The rule that keeps splits safe: one source of truth. Every other copy is derived and rebuildable.

One source of truth

Source of truthOLTP database
→
Derived copiessearch index, warehouse, cache
→
Rebuildablelag is fine, loss is not

OLTP + Search

Postgres/MySQL holds orders and products (truth). Elasticsearch holds the search index, fed by WAL/binlog change capture. Search can lag seconds and can be rebuilt from the source at any time.

Split when: search needs ranking, facets, typo tolerance beyond FTS.

OLTP + Analytics

Transactions on the primary; a replica or warehouse takes dashboards and reports. Analysts can run a 10-minute scan without causing incident #1 on checkout.

Split when: the first dashboard query slows a customer-facing page. Not before.

Any DB + Redis

Sessions, rate limits, and the 20 hottest pages live in Redis; the relational/document DB stays the truth. Redis dying means a slow minute, not lost data.

Split when: the same read repeats thousands of times per minute and tolerates being seconds stale.

Don't split on day one, and do not split on a benchmark. Every additional database adds a backup regime, a monitoring surface, a deploy pipeline, and a sync path with its own lag and failure modes. The split is justified by a measured number: a dashboard query demonstrably evicting the checkout working set, a search requirement FTS measurably cannot meet. Never by anticipation.

Further reading

These are the books and blogs this site leans on

Read them when you want the full story behind a page here.

  1. Designing Data-Intensive Applications, 2nd edition, Martin Kleppmann and Chris Riccomini, O'Reilly, 2026. The book to read first if you read one. Replication, partitioning, transactions, consistency, and stream processing, all as trade-offs instead of recipes. dataintensive.net
  2. Database Internals, Alex Petrov, O'Reilly, 2019. The deep dive this site is closest to. B-tree and log-structured storage engines, page cache, write-ahead log, and how clusters reach consistency. Read it after Kleppmann, when you want the implementation. goodreads.com
  3. Use The Index, Luke, Markus Winand. Free online, the web edition of his book SQL Performance Explained. The fastest way to stop writing slow queries. B-trees from the leaf nodes up, then exactly how the WHERE clause, joins, and sorting use an index or fail to. use-the-index-luke.com
  4. PostgreSQL 14 Internals, Egor Rogov, Postgres Pro, 2023. Free PDF. The best single source on how Postgres actually works: MVCC, buffer cache, WAL, locking, the planner, and every index type. If one page on this site made you curious, this book is the next step. postgrespro.com
  5. The Art of PostgreSQL, 2nd edition, Dimitri Fontaine, 2026 update. The practical one. How to write SQL that does the work instead of thousands of lines of application code, with real datasets to run it against. theartofpostgresql.com
  6. Building with Patterns, Daniel Coupal and Ken W. Alger, MongoDB blog. Twelve schema design patterns (polymorphic, attribute, bucket, computed, subset, outlier, and more) with the trade-off of each. Read this before you design a MongoDB schema, not after. mongodb.com

Before you leave, check this: pick your slowest query, run EXPLAIN (ANALYZE, BUFFERS) on it, and count the pages it reads. That number tells you whether you have an index problem or a volume problem.