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.
The whole subject of this page, to scale
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.
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.
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.
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.
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.
| Engine | Data file | Logs | Old versions live |
|---|---|---|---|
| PostgreSQL | Heap files in base/, 8 KB pages | WAL, pg_wal/ | Old tuples in the heap, removed by VACUUM |
| MySQL · InnoDB | users.ibd, 16 KB pages, clustered | Redo plus binlog | Undo records, removed by purge |
| MongoDB · WiredTiger | collection-*.wt, compressed blocks | Journal plus oplog | Timestamped versions in cache, removed by reconciliation |
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.
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.
| Symptom | Looks like | Check first |
|---|---|---|
| Full disk | A storage problem | WAL archiving, the DBA side, before blaming the volume |
| "Slow database" | A DBA problem | Device latency, the storage side, before blaming the queries |
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.
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.
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.
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.
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.
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.
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.
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".
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.
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.
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.One new user: id 42, name 'Asha'. The table already has users 10, 25 and 87. Follow row 42.
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.
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.
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.
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
Problem: Find row 42 without reading the whole table file, and without seeing a half-written row.
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.
Index walk. Root page → internal page → leaf page of the index: key 42 → pointer “data page 7, slot 3”. No other pages are touched.
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.
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.
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.
Problem: Change a row without anyone ever seeing a half-changed row, and without locking readers out.
Find the row first, exactly like SELECT steps 1 to 3: index walk, then page into memory.
Lock just that row so two updates can't collide. Readers don't take this lock. They read snapshots instead.
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.
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.
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.'.
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.
UPDATE: the old version is not gone yet
Problem: Remove a row instantly for new readers, but not while an old reader still needs it.
Find + lock the row via the index, same as UPDATE steps 1-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.
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.
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.
The replica replays the delete from its WAL stream / binlog / oplog and marks its copy the same way.
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.
DELETE: marked, not erased
Problem: Power cuts mid-work. Some transactions said “saved”, some were half-done. Sort it out perfectly on restart.
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.
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.
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.
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.
Open for business. Committed = present, uncommitted = gone, nothing half-way. Only now are new connections accepted.
Replicas re-sync from wherever they stopped: WAL LSN (PostgreSQL), GTID (MySQL), oplog timestamp (MongoDB).
Measure your own replay speed
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.
| Workload | Dominant cost | Answer |
|---|---|---|
| Payments: handling money | Correctness under concurrency | PostgreSQL |
| Product catalogue: lots of browsing | Read volume, almost nobody buying | MySQL plus read replicas, or MongoDB |
| User profiles: users in 40 countries | Write distribution, data close to each user | MongoDB, sharded by region |
Problem: Never lose or double-count a single payment.
Answer: PostgreSQL. Strongest correctness defaults, and any question can be asked of the data later.
Problem: 200,000 people browsing at once, almost nobody buying.
Answer: MySQL + read replicas, or MongoDB if every product has totally different attributes.
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.
6 questions, each about a real file or log you met above. Tap an answer.
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.
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.
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.
Why it happens → B+ tree diagram · Planner & statistics cards
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.
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.
Why it happens → Connection pooling card · What a transaction holds open
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.
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.
Why it happens → MVCC diagram · UPDATE journey
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.
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.
Why it happens → Binlog & oplog cards · Level 4: copies
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.
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.
Why it happens → VACUUM & undo cards · MVCC: why old versions linger
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.
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.”
Why it happens → Files on disk · The five log files
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.
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.
Why it happens → SELECT journey: cost per round trip
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.
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.
Why it happens → Shard key card · Level 5: splitting
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
"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.
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
→ 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.
→ 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.
→ 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.
→ 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).
→ 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).
→ 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.
→ 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.
→ 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.
One store: products, orders, payments, seller dashboard. All three engines can run it. Here is when each is the right call, honestly stated.
Correctness and questions-you-haven't-asked-yet matter most.
The workload is mostly lookups by ID at very large web scale.
The catalogue is the product, and it refuses to fit a fixed schema.
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
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.
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.
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.
Read them when you want the full story behind a page here.
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.