WAL, replication, and production concerns

Postgres deep-dive · Part 11 of 11. Prev: Transactional DDL and safe migrations.

The storage model, the index toolbox, the transaction semantics — those are all features of the Postgres engine itself. This final part is about the infrastructure around the engine: how it survives crashes, how changes flow to replicas, and why you need a connection pooler even when you don’t think you do.

#WAL: write-ahead logging

Every change Postgres makes — to heap pages, index pages, the commit log — is first written to the Write-Ahead Log (WAL) as a sequential stream of records. The data page is updated in the buffer pool and eventually flushed to disk, but the WAL record for that change must be flushed to disk before the transaction commits. The log comes first; the data page follows.

-- Current WAL position (log sequence number):
SELECT pg_current_wal_lsn();

-- Volume of WAL generated by an operation:
SELECT pg_current_wal_lsn() AS before;
INSERT INTO events SELECT ... FROM generate_series(1, 1000000);
SELECT pg_current_wal_lsn() AS after;
-- after - before gives WAL bytes generated

Why WAL first? At crash recovery, Postgres replays WAL from the last checkpoint forward. If the data page was written but the WAL record wasn’t, or if the WAL record was written but the data page wasn’t flushed — WAL replay covers both cases. It’s idempotent: replaying the same WAL record twice produces the correct result.

Checkpoint: periodically, Postgres flushes all dirty buffer-pool pages to disk and writes a checkpoint record to WAL. Recovery starts from the most recent checkpoint and replays forward. Without regular checkpoints, crash recovery would read WAL from the beginning of time. checkpoint_completion_target (default 0.9) spreads flush I/O over 90% of the checkpoint interval to avoid I/O spikes.

#fsync: the one setting you must never disable in production

fsync = off tells Postgres not to flush WAL to disk before committing. In testing or benchmarking: dramatically higher throughput (2–5×). In production: after a single kernel crash or power loss, you have an unrecoverable, silently corrupted data directory. Not “potentially corrupted” — corrupt. Data pages were written assuming WAL was safely on disk; the WAL wasn’t.

# postgresql.conf
fsync = on   # NEVER change this to off in production

The risk is identical to MySQL’s innodb_flush_log_at_trx_commit = 0. Both trade durability for throughput. The cost is full data loss on any unexpected shutdown.

#Streaming replication: physical, version-locked

Streaming replication sends the raw WAL byte stream from the primary to one or more standbys. Standbys replay the WAL continuously, maintaining a physical clone of the primary.

Properties:

• Replicas are read-only — they’re replaying WAL, not running a separate write path
• WAL is physical: same data file formats, same major Postgres version, same CPU architecture required
• DDL replicates automatically — catalog pages are just heap pages; WAL captures them
• Lag measured in bytes or seconds of WAL not yet applied

-- On the primary: check replication status
SELECT application_name,
       state,
       sent_lsn,
       write_lsn,
       flush_lsn,
       replay_lsn,
       (sent_lsn - replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

Streaming replication is the right choice for: high-availability standby (promote on primary failure), read scaling (route read-only queries to replicas), and physical backup foundations.

The version constraint: you cannot stream between PG 17 and PG 18. Major version upgrades cannot use streaming replication as the migration path — this is the most common major-version upgrade mistake.

#Logical replication: row-level, cross-version

Logical replication decodes WAL into a stream of row-level changes (INSERT, UPDATE, DELETE) and sends those changes, not raw WAL pages.

Properties:

• Selective: per-table subscriptions, not whole-cluster
• Cross-version: you can publish from PG 17 and subscribe on PG 18
• Cross-architecture: different CPU architectures work
• DDL is NOT replicated — the schema must be pre-created on the subscriber manually, and ALTER TABLE on the publisher does not propagate

-- Publisher (PG 17 primary):
CREATE PUBLICATION pub_orders FOR TABLE orders, order_items;

-- Subscriber (PG 18 instance, schema pre-created):
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=primary dbname=app user=replicator'
  PUBLICATION pub_orders;

The major version upgrade pattern:

1. Start PG 18 instance alongside PG 17.
2. Pre-create the schema on PG 18 (script it from the PG 17 schema).
3. Set up logical replication from PG 17 to PG 18.
4. Wait for replication lag to reach near-zero.
5. Flip application connections to PG 18.
6. Promote or decommission PG 17.

Total downtime: seconds (connection flip), not hours.

#Replication slots: handle with care

A replication slot tells the primary “don’t discard WAL until this subscriber has consumed it.” This guarantees the subscriber never falls behind and loses WAL.

-- On the primary: monitor slot lag
SELECT slot_name,
       active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn))
         AS lag_size
FROM pg_replication_slots;

The danger: a stale or disconnected slot holds WAL indefinitely. A subscriber that stops consuming for hours while the primary takes heavy write traffic can cause the primary’s disk to fill with unread WAL. When the disk fills, Postgres stops accepting writes.

Set max_slot_wal_keep_size in PG 13+ to cap WAL retention per slot:

max_slot_wal_keep_size = 10GB

A slot that exceeds this cap is automatically invalidated (the subscriber must resync from scratch) rather than filling the disk.

#PgBouncer: connection pooling is not optional

Part 1 introduced the process-per-connection model: each Postgres connection is a full OS process with ~5–10MB of overhead. 500 connections = 2.5–5GB of RAM in idle processes before a single query runs. Postgres also scans the process array on each snapshot creation — at high connection counts this becomes a shared-lock hotspot.

MySQL uses threads, not processes, and can absorb hundreds of idle connections cheaply. Postgres cannot.

PgBouncer is a lightweight connection pooler that multiplexes thousands of application connections onto a small pool of actual Postgres backends. There are two pooling modes:

Session mode: one Postgres connection per application session for its lifetime. Fewer restrictions; session-level features (SET, LISTEN, prepared statements, advisory locks, temp tables) all work. Less multiplexing — not suitable for high-concurrency apps where sessions are long-lived but often idle.

Transaction mode: a Postgres connection is returned to the pool after each transaction. Highest multiplexing — 1000 application connections can share 20 Postgres backends if transactions are short. Session-level features do not survive across transactions: SET is lost after commit, PREPARE doesn’t persist, advisory lock sessions end, temp tables vanish.

# pgbouncer.ini
[databases]
mydb = host=postgres_host dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 25

Set Postgres max_connections to match the pooler’s connection count (the pool size), not the application’s connection count. 25–100 Postgres backends is appropriate for most workloads; PgBouncer handles thousands of app clients.

#Day-one extensions

A handful of extensions belong on every new Postgres cluster:

pg_stat_statements (bundled, not auto-loaded): accumulates per-normalized-query execution statistics. Total calls, total time, mean time, rows returned. This is your first stop for “what’s actually slow in production.”

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries by total time:
SELECT query,
       calls,
       total_exec_time / 1000.0       AS total_sec,
       mean_exec_time / 1000.0        AS mean_sec,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Add to postgresql.conf: shared_preload_libraries = 'pg_stat_statements' (requires restart).

pg_trgm (Part 5): trigram-based substring and fuzzy text search with GIN indexes. One line: CREATE EXTENSION IF NOT EXISTS pg_trgm;

pgcrypto: application-layer encryption (pgp_sym_encrypt, gen_random_bytes). Use when column-level encryption is needed without key management infrastructure.

pg_partman: automates partition creation and maintenance for time-based or serial-based partitioning. Manages the partition calendar, creates future partitions in advance, and runs maintenance tasks.

PostGIS: if you have any geospatial requirement, PostGIS is the answer — geometric types, spatial indexing, coordinate projections. Add it when needed, not preemptively.

#Built-in: pg_stat_activity

Not an extension — always available. Your first stop when something is blocking:

SELECT pid,
       now() - query_start         AS query_age,
       state,
       wait_event_type,
       wait_event,
       left(query, 120)            AS query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start
LIMIT 20;

wait_event_type = 'Lock' identifies queries waiting for a lock — the first clue in a blocking investigation. wait_event = 'relation' means waiting for a table-level lock (common during DDL). wait_event = 'tuple' means waiting for a row lock.

#The misconception

“PgBouncer is an optional optimization for high-traffic sites.”

It’s not optional at any meaningful scale. The process-per-connection model is a hard physical constraint. Each idle connection is a process with its own memory, and each new connection requires forking a new OS process. Modern application frameworks (Node.js, Rails, Django, Go services) maintain connection pools per instance; with multiple application instances, the total connection count against Postgres can exceed hundreds without PgBouncer.

PgBouncer is infrastructure, not optimization. Add it when you add the database, not when you’re already having problems.

---

That’s the full series. The Postgres mental model in eleven parts:

1. Heap — rows live in an unordered page store; every index points at TIDs
2. MVCC — old versions live in the heap as dead tuples; rollback is O(1)
3. Vacuum — cleans dead tuples, maintains the VM, prevents XID wraparound
4. Types — text over varchar, timestamptz, IDENTITY, uuidv7()
5. Indexes — GIN, GiST, BRIN, partial, expression, INCLUDE, index-only scans
6. EXPLAIN — tree-shaped plans, find the divergent node, read beliefs forward
7. Planner — statistics + independence assumption + three join methods
8. Transactions — RC default, SSI aborts, SKIP LOCKED, advisory locks
9. JSONB — two operator families, GIN for containment, expression B-tree for navigation-equality
10. Migrations — transactional DDL, CIC, NOT VALID + VALIDATE, partitioning
11. WAL + ops — streaming vs logical, replication slots, PgBouncer, extensions

Understand the heap and you understand why vacuum is mandatory, why rollback is instant, why index-only scans need the visibility map, and why HOT updates matter. Almost everything else in this series is a consequence of that first design choice.