Database Foundations

A database is a system that turns statements into changes to durable state and promises, under stated conditions, what those changes look like to other sessions and after a crash. In practice that means a read path that finds data through cached pages and indexes, a write path that appends to a write-ahead log (WAL) before it acknowledges a commit, and concurrency control that gives each transaction a coherent view. What exactly is guaranteed depends on the engine and on its configuration, so this lesson says which parts are general mechanisms and which are PostgreSQL behaviour.

Take a customer who changes a shipping address. The application runs UPDATE customers SET address = ... WHERE id = .... What does success mean? The database has to locate the row, decide whether this transaction is allowed to change it right now, record enough information to recover the change, modify its working copy of the data, and eventually persist the affected bytes. Those are five different pieces of work, and an acknowledgment is only meaningful relative to which of them are finished.

-- PostgreSQL. The statement behind "a customer changes a shipping address".
CREATE TABLE customers (
  id         bigint      PRIMARY KEY,
  name       text        NOT NULL,
  address    text        NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

BEGIN;
UPDATE customers
   SET address = '9 New Road', updated_at = now()
 WHERE id = 42
RETURNING id, address;        -- one row back means this transaction found and changed the row
COMMIT;                       -- success is defined here, not at the UPDATE

An acknowledgment before the durability boundary means something different from one after a durable commit. An HTTP handler that returned 200 tells the caller nothing about durability unless the transaction really committed and the storage stack honoured the flush it was asked for. If the handler answered after the UPDATE but before COMMIT, or a retry layer swallowed a failed commit, the 200 was a statement about the application, not about the data.

A workable mental model has three layers. The logical state is what queries see: rows, constraints, committed versus uncommitted. The log is a sequential, append-only record of changes, and the only thing that has to reach stable storage at commit. The pages are the large, randomly accessed structures that hold the data; they live in memory and reach disk lazily. Nearly every behaviour in this lesson, from fast commits to crash recovery to replication lag, follows from keeping the cheap sequential log ahead of the expensive random page writes.

Later lessons build on this one. Data modeling decides what the tables and constraints are, indexing decides which access paths exist, and transactions and concurrency goes much deeper into isolation. This lesson covers the machinery all of them sit on.

The core database concepts in this lesson are durability, the read and write paths, the storage hierarchy, crash recovery and concurrency control, and each is covered in its own section below.

• Read and write paths: how a statement reaches cached pages and the write-ahead log.
• Pages and the buffer pool: why data lives in memory and reaches disk lazily.
• WAL and crash recovery: why a commit survives a crash.
• Concurrency and isolation: how readers and writers coexist.

Figure 1 is the map for the rest of the lesson. The blue path is a read. The green path is a write that ends in a commit acknowledgment. The amber path is the checkpoint that moves dirty pages to the data files later. They share one component, the buffer pool, but they obey different rules about when they must touch disk.

A read goes client → parser and planner → buffer pool. If the page is cached, the query never touches storage; if not, the page is read from the data or index files and may evict another page. Rows travel back through the executor, filtered by what the current transaction is allowed to see.

A write changes the page in the buffer pool (marking it dirty), appends a record describing the change to the WAL buffer, and, at commit, forces the log to disk. The acknowledgment (step 5) waits for that flush. It does not wait for the data page. That is the central asymmetry: the commit path pays for one sequential log flush, while the page write is deferred and batched. The checkpoint (step 6) runs later and writes dirty pages in the background.

The crash marker sits between B and D in the time strip. At that moment the commit is durable only in the log; the data file still holds the old page. On restart the engine reads the log from the last checkpoint forward and re-applies the committed changes, so step D happens even though the process that was going to do it died. A crash before B is the opposite case, where no commit record is durable and the transaction does not survive. Both outcomes are legitimate; what must never happen is an acknowledgment at C for a commit that was not durable at B.

Records live in fixed-size pages (also called blocks). PostgreSQL uses 8 KiB pages by default and MySQL InnoDB uses 16 KiB. The page, not the row, is the unit moved between disk and memory, so reading one 100-byte row costs at least one page of I/O when it is not cached. A buffer pool caches selected pages in memory, indexes provide access paths into the pages, and the storage engine decides how to flush and reclaim them.

Hits, misses and eviction

On a buffer-pool hit the engine serves the page from memory with no storage read. On a miss it reads the page and, if the pool is full, evicts another one; evicting a dirty page forces a write first, so a read can cause write I/O. The access pattern matters as much as the hit rate. A sequential scan reads pages in order and is friendly to read-ahead, though large scans can push useful pages out of the cache unless the engine limits their share. A random index lookup touches a few scattered pages. An update dirties a page that must eventually be written back.

There are several caches between a query and the bytes, and they are distinct. The database buffer pool is managed by the engine. The operating system page cache is managed by the kernel and sits under any buffered file I/O, which is why a page that misses the buffer pool is often still served from RAM. Storage devices and network volumes can add their own caches. PostgreSQL relies on the OS cache alongside its own shared buffers; some engines and configurations bypass the OS cache with direct I/O. Which layers are active, and how large each one is, is engine- and configuration-dependent, so read the documentation for the system you run before reasoning about “is it in memory”.

The layers also decide what “flushed” means. Writing to a file hands bytes to the OS cache. The fsync family of calls asks the OS to push them to the device and the device to make them stable. A drive or controller with a volatile write cache that acknowledges flushes early breaks the guarantee silently, which is why durability claims depend on the hardware path and not only on the database setting.

Row, column and LSM layouts

Row-oriented storage keeps the fields of a row together, which suits transactional workloads: a point lookup or an update touches one place. Column-oriented storage groups the values of each column, which helps scans that read a few columns of many rows, and compresses well because neighbouring values are similar. Neither is universally faster. Predicates, projections, compression, update frequency and indexing all change the result, and wide analytical scans and single-row updates sit at opposite ends of the trade.

The calculator uses a deliberately simple model: 10 million rows, uniform 8-byte columns, no compression, no indexes. With 20 columns and a query that reads 3 of them, a row-layout scan reads about 1.6 GB and a column-layout scan about 240 MB, a ratio of 20 / 3 ≈ 6.7. Real column stores usually do better thanks to compression and skipping, and real row stores do better when an index narrows the scan. In the other direction, fetching one whole row from a column layout touches roughly one page per column, which is why column stores are poor at point updates.

The second axis is how the engine organises writes. B-tree-family engines keep ordered pages and update them in place. A small update dirties a whole page, so a 100-byte change to an 8 KiB page is about 82 times more page data than row data before the WAL is counted, and page splits add random writes. PostgreSQL adds another cost: the first change to a page after a checkpoint can log a full-page image (full_page_writes) to protect against torn page writes; InnoDB uses a doublewrite buffer for the same problem. LSM-tree engines buffer writes in memory, flush them as sorted immutable files, and merge those files in the background (compaction). Writes become sequential and cheap, but compaction rewrites the same data several times (write amplification: for leveled compaction with a fanout of 10, a rough worst case is on the order of 10 rewrites per level), and a read may have to consult several files unless Bloom filters and caches avoid it (read amplification). Treat these as the operational shape of each design, not as a ranking: a write-heavy ingestion workload and a read-mostly OLTP workload can reasonably choose differently.

Consider SELECT status FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20. The parser turns the text into a representation and resolves names. The planner enumerates ways to run it and picks the one with the lowest estimated cost, using table statistics (row counts, value distributions) and cost parameters. The executor then runs the chosen plan, visiting the chosen access paths. Finally the engine returns only the rows that the transaction’s visibility rules allow, which the concurrency section below covers.

An index on (customer_id, created_at DESC) can make this query cheap: entries for customer 42 are adjacent and already ordered newest-first, so the executor can read the first 20 matching entries, fetch those rows, and stop, with no sort of the full set. The planner may still choose a sequential scan when the table is tiny (reading a few pages directly is cheaper than an index probe) or when the predicate matches most of the table. In PostgreSQL an index in ascending order can also be scanned backwards, so the exact DESC matters more for mixed sort directions and multi-column orderings. See Indexing for how composite indexes are chosen.

-- PostgreSQL. Schema and index for the read-path example.
CREATE TABLE orders (
  id          bigserial   PRIMARY KEY,
  customer_id bigint      NOT NULL REFERENCES customers (id),
  status      text        NOT NULL,
  total_cents bigint      NOT NULL CHECK (total_cents >= 0),
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);
ANALYZE orders;               -- refresh the statistics the planner will use

-- EXPLAIN alone shows the estimated plan. ANALYZE actually runs the statement
-- (so wrap data-modifying statements in BEGIN ... ROLLBACK), and BUFFERS adds page counts.
EXPLAIN (ANALYZE, BUFFERS)
SELECT status
  FROM orders
 WHERE customer_id = 42
 ORDER BY created_at DESC
 LIMIT 20;

A plan estimate is not a promise. Read EXPLAIN (ANALYZE, BUFFERS) by comparing the planner’s estimated row counts with the actual ones, because a large mismatch usually means stale or insufficient statistics and leads to bad join and scan choices. Look at Buffers: shared hit=… read=… to see how many pages came from the cache and how many had to be read, and at loops to see repeated work. On a small test table, the expected shape is a Limit over an index scan on orders_customer_created_idx with no Sort node and a handful of buffer hits. On your data the shape can differ, so run it there.

Measurements need care. A second run is faster because the pages are now cached, so note whether you are measuring a cold or warm cache. Test with realistic data distributions: customer 42 with 20 orders and a customer with 2 million are different problems, and the plan can legitimately change between them. Use EXPLAIN to understand a plan and production statement statistics (for example PostgreSQL’s pg_stat_statements) to find which statements matter at all.

The write-ahead rule

A write-ahead log records changes (or the information needed to redo them) before the dirty data pages containing those changes are allowed to reach durable storage. A page is never written to the data file until the log up to that page’s last change is durable. In a WAL-based engine this has a useful consequence: a committed transaction can survive a crash even if none of its modified pages were ever flushed, provided the relevant log records were durably persisted and the configured guarantees hold. The log is sequential and compact, so forcing it at commit is cheap compared with writing scattered pages.

Recovery uses the log in a simple way. After a crash the engine locates the last checkpoint, reads the log forward from there, and re-applies changes to pages (redo). Work from transactions that never committed is handled according to the engine’s design. PostgreSQL does not need to undo it, because an uncommitted row version simply stays invisible and is cleaned up later; engines with in-place updates and undo logs, such as InnoDB, roll incomplete transactions back. A checkpoint bounds the work: it flushes dirty pages and records a point before which the log is no longer needed for recovery. More frequent checkpoints shorten recovery but add write I/O (and more full-page images in PostgreSQL), which is why checkpoint_timeout and max_wal_size are tuning knobs.

A commit, step by step

The sequence for the address update is: (1) begin the transaction, (2) read and check the row, (3) acquire the concurrency controls needed, (4) change the page in memory, (5) append a WAL record, (6) flush the required WAL at commit, (7) acknowledge the client, (8) flush the dirty data pages later. This is illustrative; engines differ in details, but the ordering of steps 5 through 8 is the part that carries the guarantee.

Where a crash can land

The same write can be interrupted at four different points, and the client’s knowledge differs at each. Choose a scenario in Figure 5 and compare.

The interesting case is the third. If the process dies after the commit record is durable but before the acknowledgment is sent, the transaction did commit, yet the client saw a dropped connection. From the client’s side this is indistinguishable from the second case, where the commit did not happen. This is a general property of request/response over a network, not a database bug: the client cannot infer the outcome from the absence of an answer.

When the commit outcome is unknown

The correct recovery strategy is to query authoritative state or to make the operation idempotent, never to assume failure and blindly redo it. Querying works when the intended change is checkable (does customer 42 now have that address?). Idempotency works for everything else: the client generates an operation id once, stores it with the change in the same transaction, and reuses it on every retry. A second attempt then either finds the id already recorded and reports success without repeating the change, or applies it for the first time. The same idea, with the key stored by a service instead of a table, is covered in Idempotency, and the timeout side of it in Retry & Backoff.

-- PostgreSQL. An operation id makes "retry after an unknown commit outcome" safe.
CREATE TABLE operation_log (
  op_id       uuid        PRIMARY KEY,           -- generated by the client once per intended change
  customer_id bigint      NOT NULL,
  applied_at  timestamptz NOT NULL DEFAULT now()
);

BEGIN;
INSERT INTO operation_log (op_id, customer_id)
VALUES ('6f1d6c52-5d3a-4f7e-9a53-3d1f2b0c9e11', 42)
ON CONFLICT (op_id) DO NOTHING
RETURNING op_id;
-- 1 row returned: first time this op_id was seen, so apply the change below.
-- 0 rows returned: it already committed earlier (or a concurrent twin is committing; this
--                  INSERT waits for it). Skip the change and report success.
UPDATE customers SET address = '9 New Road', updated_at = now() WHERE id = 42;
COMMIT;                                          -- the log row and the change commit together

// Node.js with the "pg" package. The op id is created once by the caller and reused on every retry.
import { Pool } from 'pg';
const pool = new Pool();

export async function changeAddress({ opId, customerId, address }) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const first = await client.query(
      `INSERT INTO operation_log (op_id, customer_id) VALUES ($1, $2)
       ON CONFLICT (op_id) DO NOTHING RETURNING op_id`,
      [opId, customerId],
    );
    if (first.rowCount === 1) {
      await client.query(
        'UPDATE customers SET address = $1, updated_at = now() WHERE id = $2',
        [address, customerId],
      );
    }
    await client.query('COMMIT');
    return { applied: first.rowCount === 1 };   // false means "an earlier attempt already did it"
  } catch (err) {
    await client.query('ROLLBACK').catch(() => {});
    throw err;                                  // a caller that sees a connection error retries with the SAME opId
  } finally {
    client.release();
  }
}

Two details make this safe. The operation id and the change commit in one transaction, so there is no state in which one exists without the other. And a concurrent duplicate, a retry that arrives while the first attempt is still running, blocks on the primary-key conflict until the first transaction ends, then skips the change.

Group commit

Forcing the log to stable storage is the expensive part of a commit. If every commit paid for its own flush, throughput would be capped near 1000 / flush_ms commits per second; with a 2 ms flush, that is 500 per second. Group commit amortizes the cost: sessions that reach their commit while a flush is in progress are covered by the next flush together, and the same device completes many commits per flush. In PostgreSQL this happens naturally, since whichever session flushes the log persists every record inserted so far; commit_delay and commit_siblings can add a deliberate wait to gather more commits, and are only worth tuning after measuring.

The figure is an upper-bound model, not PostgreSQL’s exact algorithm. At 2 ms and 32 concurrent sessions the unbatched ceiling is 500 commits per second while the batched ceiling is up to 16,000 (32 × 500), assuming CPU, locks and log bandwidth are not the limit. Commit latency in the batched case is one flush, or a wait for the in-progress flush plus one more, so roughly 2 to 4 ms here. The practical consequence is that a single-threaded loader doing one commit per row is the slow case, while many concurrent small transactions, or one transaction that inserts many rows, make far better use of the device.

Configuring durability in PostgreSQL

Everything above is conditional on configuration. With the default synchronous_commit = on, COMMIT returns after the local WAL flush. Setting it to off returns early and lets the WAL writer flush shortly afterwards; after an OS or power failure the most recent commits can be lost (bounded at about three times wal_writer_delay, 600 ms by default) but the database remains consistent. That is a legitimate choice for low-value, high-rate data and a bad one for payments, and it can be applied per transaction. fsync = off is a different matter: it removes crash safety for the whole cluster and risks corruption. Other engines expose equivalent switches under different names, so check the one you run.

-- PostgreSQL. Inspect the settings that decide what "committed" means on this server.
SHOW synchronous_commit;      -- 'on' (default): COMMIT returns after the local WAL flush
SHOW fsync;                   -- must be 'on' in production; 'off' can corrupt the cluster after a crash
SHOW full_page_writes;        -- 'on': first change to a page after a checkpoint logs the whole page
SHOW checkpoint_timeout;      -- default 5min: upper bound between automatic checkpoints
SHOW max_wal_size;            -- default 1GB: WAL volume that also triggers a checkpoint
SHOW wal_writer_delay;        -- default 200ms: how often the WAL writer flushes in the background

-- Trading durability for latency on ONE transaction (for example, a click-tracking insert):
BEGIN;
SET LOCAL synchronous_commit = off;   -- COMMIT returns before the WAL flush
INSERT INTO page_view (customer_id, url) VALUES (42, '/pricing');
COMMIT;
-- After an OS or power crash the last few moments of such commits can vanish (at most about
-- three times wal_writer_delay of them). The database stays consistent; it just forgets them.
-- Contrast with fsync = off, which gives up crash safety for the whole cluster.

What the WAL does not protect against

The WAL is a recovery mechanism, not a backup. It faithfully makes a bad change durable: a corrupting application bug, an accidental DELETE or DROP TABLE, and a bad deployment are all recorded as committed transactions and survive restarts. Backups plus archived log (for example PostgreSQL’s base backups and WAL archiving for point-in-time recovery) are what allow restoring to a moment before the mistake. Hardware corruption and a lying storage layer are also outside what the log can repair. A restore you have never tested is not yet a backup.

Concurrent transactions can hit a family of anomalies. A dirty read sees another transaction’s uncommitted change. A non-repeatable read sees a committed row change between two reads in one transaction. A phantom sees a different set of rows matching the same predicate. A lost update overwrites a concurrent change that the writer never saw. Write skew has two transactions each validate a rule against the same data and then write different rows, jointly breaking the rule. A deadlock is a cycle of transactions each waiting for a lock another holds. Which of these a database allows depends on the isolation level and the implementation; the SQL standard names levels, but engines realize them differently. In PostgreSQL the default is READ COMMITTED, where every statement takes a fresh snapshot; REPEATABLE READ takes one snapshot for the whole transaction and is snapshot isolation, which also prevents phantoms; SERIALIZABLE adds detection of dangerous patterns. PostgreSQL never allows dirty reads. MySQL InnoDB defaults to REPEATABLE READ with different locking behaviour, so do not carry conclusions between engines.

MVCC: readers do not block writers

Multi-version concurrency control keeps several versions of a row so a reader can see a consistent view without taking locks that block writers. An update does not overwrite in place; it creates a new version and marks the old one as superseded. Each version records which transaction created it and which, if any, deleted it. A transaction’s snapshot determines which of those transactions count as committed from its point of view, and a version is visible when its creator is committed (or is the current transaction) and its deleter is not.

The figure uses PostgreSQL-style metadata (xmin and xmax) because it is easy to inspect there; the idea is general, and engines such as InnoDB keep older versions in an undo log instead. Rollback is cheap in this design since the new version just stays invisible. The cost is that old versions accumulate. They can only be removed once no running snapshot can still see them, so one long-running or idle-in-transaction session holds back cleanup for the whole database. The result is table bloat, slower scans and autovacuum that cannot make progress.

-- PostgreSQL. Row versions are visible through system columns.
SELECT xmin, xmax, ctid, id, address FROM customers WHERE id = 42;
-- After an UPDATE the row has a new xmin (the updating transaction) and usually a new ctid
-- (its physical location): the old version is still in the page until vacuum removes it.

-- Who is holding back cleanup? Long and idle-in-transaction sessions pin old versions.
SELECT pid, state, now() - xact_start AS xact_age, left(query, 60) AS query
  FROM pg_stat_activity
 WHERE xact_start IS NOT NULL
 ORDER BY xact_start
 LIMIT 5;

-- Dead versions per table and when autovacuum last ran.
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
  FROM pg_stat_user_tables
 ORDER BY n_dead_tup DESC
 LIMIT 10;

-- Guard rails (values are examples; tune for the workload):
-- SET idle_in_transaction_session_timeout = '30s';
-- SET statement_timeout = '15s';

Locks still matter

MVCC removes read/write blocking, not write/write conflicts. Two updates of the same row still serialize on a row lock, schema changes take table-level locks, and explicit SELECT ... FOR UPDATE exists because sometimes you need to claim a row before deciding. A deadlock occurs when sessions acquire locks in different orders; the database detects the cycle (PostgreSQL after deadlock_timeout, 1 s by default), aborts one transaction with SQLSTATE 40P01, and the application must retry. The prevention is to take locks in a consistent order and to keep transactions short.

The most common production bugs in this area are application-side “check then write” sequences. Read a balance, compute in the application, write back: that is the lost update in Figure 8. Check that a username is free, then insert: that is the race in the third case. A uniqueness constraint, a foreign key, a CHECK or a carefully chosen transaction boundary usually provides a stronger guarantee than the same check in application code, because the database evaluates it atomically with the write. For rules that span rows, such as the on-call example, use SERIALIZABLE (and retry) or lock the rows that the rule reads. Each fix is shown below, along with a retry wrapper; the full treatment of isolation levels, lock modes and optimistic versus pessimistic control is in Transactions & Concurrency.

-- PostgreSQL. Let the database enforce the rule instead of check-then-write in the application.

-- 1. Lost update: do the arithmetic in the statement, and guard the invariant in its WHERE clause.
UPDATE accounts
   SET balance_cents = balance_cents - 2500
 WHERE id = 1 AND balance_cents >= 2500
RETURNING balance_cents;           -- zero rows: insufficient funds, nothing was changed

-- 1b. When the application must read first and decide, lock the row for the transaction.
BEGIN;
SELECT balance_cents FROM accounts WHERE id = 1 FOR UPDATE;
-- ... decide in application code ...
UPDATE accounts SET balance_cents = balance_cents - 2500 WHERE id = 1;
COMMIT;

-- 2. Check-then-insert: a unique index is the check.
CREATE TABLE users (
  id       bigserial PRIMARY KEY,
  username text      NOT NULL,
  CONSTRAINT users_username_key UNIQUE (username)
);
INSERT INTO users (username) VALUES ('ada')
ON CONFLICT (username) DO NOTHING
RETURNING id;                      -- zero rows: somebody already owns the name

-- 3. Write skew: a rule spanning several rows. SERIALIZABLE makes the database detect the pattern.
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call;
UPDATE doctors SET on_call = false WHERE name = 'alice';
COMMIT;                            -- may fail with SQLSTATE 40001: retry the WHOLE transaction

// Node.js with "pg". Retry the whole transaction on serialization failure (40001) or deadlock (40P01).
const RETRYABLE = new Set(['40001', '40P01']);
const sleep = (ms) => new Promise((r) => setTimeout(r, ms));

export async function runSerializable(pool, work, maxAttempts = 5) {
  for (let attempt = 1; ; attempt++) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      const result = await work(client);       // must be safe to run again from scratch
      await client.query('COMMIT');
      return result;
    } catch (err) {
      await client.query('ROLLBACK').catch(() => {});
      if (!RETRYABLE.has(err.code) || attempt >= maxAttempts) throw err;
    } finally {
      client.release();
    }
    await sleep(Math.random() * 20 * 2 ** attempt);   // jittered backoff after the connection is released
  }
}

A service inserts an order and immediately publishes a success event, but the database transaction later rolls back. The event describes state that never committed. Downstream, a warehouse picks stock and a payment is captured for an order that does not exist. This is the dual-write problem: two systems, one logical change, no shared atomicity. The failure is not rare; commit can fail for ordinary reasons (a deferred constraint, a serialization failure, a dropped connection), and any publish made before commit is then wrong.

The fix is to move publication behind the commit. In a transactional outbox, the order row and an event row are written in one local transaction and a separate relay publishes committed event rows, so an event exists if and only if its order does. The relay can crash between publishing and marking the row sent, so delivery is at least once and consumers need to deduplicate. The details, including ordering and relay design, are in the Outbox Pattern lesson.

-- PostgreSQL. The order and its event commit atomically; a relay publishes later.
CREATE TABLE outbox (
  id           bigserial   PRIMARY KEY,
  aggregate_id bigint      NOT NULL,
  event_type   text        NOT NULL,
  payload      jsonb       NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz
);

BEGIN;
WITH o AS (
  INSERT INTO orders (customer_id, status, total_cents)
  VALUES (42, 'placed', 2599)
  RETURNING id
)
INSERT INTO outbox (aggregate_id, event_type, payload)
SELECT id, 'OrderPlaced', jsonb_build_object('orderId', id, 'customerId', 42) FROM o;
COMMIT;                              -- both rows are durable together, or neither exists

-- Relay (separate process): claim a batch, publish, then set published_at.
SELECT id, event_type, payload
  FROM outbox
 WHERE published_at IS NULL
 ORDER BY id
 LIMIT 100
 FOR UPDATE SKIP LOCKED;

The last two cases in the figure are about a different boundary. A replica is a second copy kept up to date from the primary’s WAL. With asynchronous replication the primary acknowledges after its own durable flush and ships the record afterwards, so a primary can acknowledge a write that no replica has yet received. If the primary is lost and a replica is promoted, that acknowledged write is gone. The size of the window is the recovery point objective (RPO) for failover, and with asynchronous replication it is greater than zero by construction. Synchronous replication shrinks it by delaying the acknowledgment until a standby confirms the record, in PostgreSQL via synchronous_standby_names and synchronous_commit, and costs a network round trip per commit plus the risk that commits stall if the standby is unreachable. Confirming flush on a standby also does not mean the standby has applied it, so a read from it right after the commit can still miss the row unless the stricter apply setting is used.

-- PostgreSQL, on the primary. Is a commit acknowledged before or after a standby has it?
SHOW synchronous_standby_names;       -- empty: replication is asynchronous, COMMIT waits for the local flush only
-- Example policy (postgresql.conf): wait for ANY 1 of two standbys to flush the commit record.
--   synchronous_standby_names = 'ANY 1 (replica_a, replica_b)'
--   synchronous_commit        = on     -- remote_write / remote_apply change what "has it" means

-- How far behind is each standby right now? The gaps are what a failover could lose.
SELECT application_name, state, sync_state, write_lag, flush_lag, replay_lag
  FROM pg_stat_replication;

Replicas also are not backups: an erroneous DELETE replicates as faithfully as any other change. Replication topologies, lag and read-your-writes behaviour are covered in Scaling and Distributed Data.

Each mechanism in this lesson has a symptom you can observe. Commit latency, and the time spent in WAL writes and syncs, tells you whether the durability path is the bottleneck, which on network storage often means flush latency. The buffer hit ratio and the ratio of reads to hits show whether the working set fits in memory; watch the trend, since a single number can mislead. Checkpoint counts (timed versus requested) reveal whether the write rate is forcing early checkpoints; WAL volume and full-page-image counts show the write amplification you are actually paying. Replication lag, split into write, flush and replay, is the current size of your failover gap and your stale-read window. Long-running and idle-in-transaction sessions, dead-tuple counts and autovacuum recency show whether MVCC cleanup is keeping up. Finally, plan changes for the statements that dominate total time are what turn a quiet database into an incident.

-- PostgreSQL 17+ names used where they changed. Starting points, not a dashboard.

-- Buffer cache hit ratio since the last stats reset (a trend matters more than one value).
SELECT datname, blks_hit, blks_read,
       round(blks_hit::numeric / nullif(blks_hit + blks_read, 0), 4) AS hit_ratio
  FROM pg_stat_database
 WHERE datname = current_database();

-- WAL volume and full-page images written (cumulative counters; sample and diff them).
SELECT wal_records, wal_fpi, wal_bytes FROM pg_stat_wal;

-- Checkpoint pressure: many "requested" checkpoints suggest max_wal_size is too small for the write rate.
SELECT num_timed, num_requested, write_time, sync_time FROM pg_stat_checkpointer;  -- PG 17+; earlier: pg_stat_bgwriter

-- Slowest statements by total time (needs the pg_stat_statements extension to be installed and preloaded).
SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 70) AS query
  FROM pg_stat_statements
 ORDER BY total_exec_time DESC
 LIMIT 10;

These queries are starting points. Counters accumulate since the last reset, so sample and difference them, and column names have shifted between PostgreSQL versions (checkpoint counters moved from pg_stat_bgwriter to pg_stat_checkpointer in version 17).

Choose a transactional relational database when constraints, joins and multi-row atomicity dominate: orders, accounts, inventory, anything where an invariant must hold even when two requests race. An e-commerce or banking core typically sits here, with a cache in front for hot reads. Consider columnar analytical storage for broad scans and aggregations over large history, where the row-versus-column arithmetic in Figure 3 favours it. Consider key-value or document engines when their data model and access patterns fit the problem (lookups by key, aggregate-shaped documents with few cross-entity constraints), not simply because they carry the label “NoSQL”. A graph engine earns its place when multi-hop relationship traversal is the core query, and the other specialised stores are covered in Specialized Data.

Do not decide from reputation. “Document stores lack transactions” is out of date: MongoDB has supported multi-document ACID transactions since version 4.0 and DynamoDB offers transactional reads and writes, each with its own scope and limits. What matters is what the specific engine guarantees under your concurrency and failure model. The questions that separate options are practical ones. What are the durability settings, and what do they cost in latency? Can you restore from a backup to a chosen moment, and have you done so? What do you observe when it is slow, and does your team know how to read it? How does it behave under the expected mix of reads, writes and scans?

Several failure modes recur when these basics are skipped: keeping critical data only in process memory, leaving query columns unindexed until a table reaches millions of rows, relying on application code instead of NOT NULL, UNIQUE and foreign keys to keep data valid, running one instance with no replica or tested restore, and cramming unrelated concepts into one table so that every later change is a migration risk. The rest of the sequence addresses them in order: modeling for structure and constraints, indexing for access paths, transactions for correctness under concurrency, scaling for what breaks first as load grows, distributed data for replication and consistency, and specialized data for workloads a general-purpose engine serves badly.