Opening the Hood of PostgreSQL WAL: From Transaction Logs to Production‑Grade HA, Replication, and Performance Tuning

1. A Real‑World Incident that Exposes WAL Bottlenecks

A high‑traffic order system migrated its primary SSD to a shared cloud disk, switched synchronous_commit to remote_apply, added a Debezium CDC task without slot limits, and moved WAL archiving to an object‑storage gateway. During peak write load the application threads filled up, many sessions blocked on COMMIT, and pg_stat_activity showed waits on WALWrite and WALSync. One replica lagged severely, and the primary slowed further because synchronous replication waited for it.

2. WAL Fundamentals

2.1 Why PostgreSQL Writes WAL Before Data Pages

PostgreSQL’s cache management relies on two classic assumptions: STEAL (dirty pages may be evicted before commit) and NO‑FORCE (data pages need not be flushed on commit). These improve throughput but introduce risk: uncommitted dirty pages must be rolled back after a crash, and committed pages not yet on disk must be replayed. WAL solves this by enforcing the rule that every WAL record describing a page modification must be persisted before the page itself is written.

2.2 The Commit Path

SQL executor modifies a page in shared_buffers.

PostgreSQL generates a WAL record and appends it to the WAL buffer.

On COMMIT, the system flushes all WAL records up to the transaction’s last record to durable storage.

Background processes later write the dirty pages back to data files.

During crash recovery, PostgreSQL starts from the latest checkpoint and replays WAL to restore a consistent state.

The commit latency is therefore dominated by the “log plane” (WAL) rather than the data plane.

2.3 Key Internal Roles

Backend Process : Executes SQL, generates WAL, may participate in synchronous flush.

WAL Buffer : Shared memory buffer that holds WAL records before they are written.

WAL Writer : Periodically moves WAL from the buffer to the OS cache.

Checkpointer : Triggers checkpoints and writes dirty pages back to data files.

Background Writer : Helps flush dirty pages to reduce front‑end write pressure.

Note the distinction: WAL Writer handles the log, while Checkpointer handles data pages; both affect latency in different ways.

2.4 LSN – PostgreSQL’s Logical Timeline

The Log Sequence Number (LSN) is a byte offset within the WAL stream. Important LSN positions are:

insert LSN : Position where the record entered the WAL buffer.

write LSN : Position where the record was written to a WAL file.

flush LSN : Position where the record is truly durable on disk.

replay LSN : Position up to which a replica has replayed.

When these LSNs diverge, different parts of the system fall behind: insert‑flush lag blocks local commits, flush‑replay lag indicates replica lag, and a stagnant restart_lsn shows a replication slot holding back WAL recycling.

2.5 WAL Segments, Timelines, and full_page_writes

WAL is stored as 16 MB segment files under pg_wal. Each checkpoint or pg_rewind operation can create a new timeline. The full_page_writes setting causes PostgreSQL to write a full page image to WAL the first time a page is modified after a checkpoint, preventing “half‑page” corruption during recovery. Frequent checkpoints increase the number of full‑page writes, inflating WAL size.

3. WAL‑Powered Capabilities in PostgreSQL

3.1 Crash Recovery

On a crash, PostgreSQL finds the latest checkpoint, then replays all WAL records after that checkpoint to reconstruct a consistent state. More frequent checkpoints reduce replay time but increase write amplification.

3.2 Physical Replication

Streaming replication streams WAL records from the primary to replicas, which replay them in order. This enables high‑availability, read‑write splitting, and cross‑datacenter disaster recovery, but hot spots in the write workload can cause replica lag, and synchronous replication adds the replica’s latency to the primary’s commit path.

3.3 Logical Replication / CDC

Logical decoding extracts row‑level change events from WAL, which tools like Debezium or Flink consume. Enabling wal_level = logical increases WAL volume. Large transactions, slot backlogs, and downstream consumer slowdown can cause WAL to accumulate.

3.4 Point‑In‑Time Recovery (PITR)

Base backups provide a starting image; continuous WAL archiving allows recovery to any target time or LSN. Without WAL archiving, only the backup timestamp is recoverable.

4. Commit Latency in High‑Concurrency Workloads

4.1 Sources of COMMIT Latency

commit latency = wait for WALWriteLock/insert slot
                + wait for WAL write
                + wait for fsync/fdatasync
                + synchronous‑replication ack (if enabled)

Thus, fast disks solve only part of the problem; contention for WAL buffers, aggressive synchronous settings, checkpoint storms, or large WAL volume also matter.

4.2 Group Commit

PostgreSQL can batch multiple transactions into a single fsync, dramatically reducing fsync calls and improving throughput under steady high load. However, bursty large transactions can negate the benefit.

4.3 Estimating max_wal_size

Instead of blindly increasing max_wal_size, measure WAL generation rate during a stable period: SELECT now() AS ts, wal_bytes FROM pg_stat_wal; After a known interval compute:

wal_rate = (wal_bytes_2 - wal_bytes_1) / seconds

Then size max_wal_size so that the allowed replay window (RTO) multiplied by the replay throughput stays below the allocated WAL budget. Adjust related settings ( checkpoint_timeout, wal_buffers, archive bandwidth) accordingly.

4.4 When to Tune wal_buffers

wal_buffers

is not “bigger is better”. Increase it when:

Single‑node write concurrency is high.

Frequent bulk imports generate bursts. pg_stat_wal.wal_buffers_full is rising continuously.

Log writes exhibit clear spikes.

After raising the value, verify that wal_buffers_full events drop and that overall WALWrite wait time improves.

4.5 synchronous_commit Spectrum

The setting is not a simple on/off switch. Options: remote_apply: Wait until the replica has replayed the transaction (safest, slowest). on: Wait until the primary’s local WAL is flushed (default, balanced). local: Same as on but does not wait for replica acknowledgment. off: Do not wait for local flush, allowing a small data‑loss window.

Best practice is to set the level per business tier rather than globally. Example mapping:

Payment or inventory confirmation – remote_apply or on with compensation.

Typical order writes – on.

Audit logs or telemetry – local or off for higher throughput.

Per‑transaction control can be achieved with SET LOCAL synchronous_commit = 'remote_apply'; inside a transaction block.

5. Production‑Grade WAL Architecture

5.1 Single‑Node Performance

Place pg_wal on low‑latency, high‑throughput storage.

Separate WAL directory from the data directory to avoid I/O contention.

Fine‑tune checkpoint parameters to smooth write spikes.

5.2 HA with Patroni, etcd/Consul, and Streaming Replication

Typical stack:

PostgreSQL streaming replication for data flow.

Patroni for leader election and automatic failover.

etcd or Consul as the distributed configuration store (DCS).

HAProxy / PgBouncer / VIP as the traffic entry point.

Four logical layers are required: write layer (application → PgBouncer → primary), replication layer (primary → WAL → replica), orchestration layer (Patroni + pg_rewind), and recovery layer (WAL archiving for PITR). Missing any layer leaves the system vulnerable.

5.3 Kubernetes Considerations

Do not co‑locate pg_wal and data pages on a high‑latency shared volume.

Deploy PostgreSQL and PgBouncer in a StatefulSet, but use an external DCS (etcd) for coordination.

Ensure pg_wal uses a fast, low‑latency PersistentVolume, while the data directory can use a more durable volume.

Continuously archive WAL to object storage (e.g., S3 or MinIO).

Topology example:

Application → PgBouncer → Patroni primary → Streaming replica → WAL archive (S3/MinIO) → etcd/K8s API for leader election

5.4 Handling a Former Primary Re‑join

After a failover, a former primary must be fenced and either rewound with pg_rewind (requires wal_log_hints or data checksums) or rebuilt from a fresh base backup. Incomplete WAL archives or missing timelines prevent successful rewind.

6. Baseline Configuration for OLTP with Replication & CDC

6.1 postgresql.conf Sample

# WAL basics
wal_level = logical
wal_buffers = 64MB
wal_writer_delay = 20ms
wal_writer_flush_after = 1MB
wal_compression = on
full_page_writes = on
wal_log_hints = on

# Checkpoint
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 16GB
min_wal_size = 4GB

# Archive
archive_mode = on
archive_timeout = 60s
archive_command = '/usr/local/bin/wal-g wal-push %p'

# Replication
max_wal_senders = 16
max_replication_slots = 16
wal_keep_size = 2048MB
max_slot_wal_keep_size = 50GB
hot_standby = on

# Consistency
synchronous_commit = on

Key notes: wal_level = logical only when logical replication or CDC is needed. wal_keep_size supplements, not replaces, replication slots. archive_timeout is for timely archiving, not performance. wal_compression reduces WAL size at a CPU cost.

6.2 Patroni Configuration (YAML excerpt)

scope: order-db
namespace: /service/
name: pg-0
bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    synchronous_mode: true
    postgresql:
      use_pg_rewind: true
      parameters:
        wal_level: logical
        hot_standby: "on"
        wal_log_hints: "on"
        max_wal_senders: 16
        max_replication_slots: 16
        wal_keep_size: 2048MB
        max_slot_wal_keep_size: 50GB
        archive_mode: "on"
        archive_command: "/usr/local/bin/wal-g wal-push %p"
        checkpoint_timeout: 15min
        checkpoint_completion_target: 0.9
        max_wal_size: 16GB
        min_wal_size: 4GB
        synchronous_commit: "on"
postgresql:
  data_dir: /var/lib/postgresql/data/pgdata
  authentication:
    replication:
      username: replicator
      password: strong-password
    superuser:
      username: postgres
      password: strong-password

6.3 PgBouncer Settings

[databases]
orderdb = host=postgres-primary port=5432 dbname=orderdb

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 100
reserve_pool_size = 20
reserve_pool_timeout = 3
server_reset_query = DISCARD ALL

PgBouncer does not solve fsync latency but prevents connection explosion and transaction‑level resource bloat.

7. Backup, Archiving, and Recovery Practices

7.1 Base Backup vs. Continuous WAL Archive

Base backup alone only restores to the backup timestamp.

Base backup + continuous WAL archive enables PITR to any target time or LSN.

7.2 WAL‑G Archiving Script (Bash)

#!/usr/bin/env bash
set -euo pipefail
wal_file="$1"
attempt=0
max_attempts=5
while (( attempt < max_attempts )); do
  if /usr/local/bin/wal-g wal-push "$wal_file"; then
    exit 0
  fi
  attempt=$((attempt + 1))
  sleep $((attempt * 2))
 done
 echo "wal archive failed after ${max_attempts} attempts: ${wal_file}" >&2
 exit 1

7.3 Recovery Configuration Example

restore_command = '/usr/local/bin/wal-g wal-fetch "%f" "%p"'
recovery_target_time = '2026-06-22 16:30:00+08'
recovery_target_action = 'promote'

Key operational points:

Monitor archive failures and latency; missed WAL leads to pg_wal growth.

Include archive health in alerts.

Regularly test restores to verify that both base backup and WAL chain are usable.

7.4 Recovery Drills

Restore the latest base backup into a fresh instance.

Fetch WAL up to the desired recovery point.

Run recovery and validate key tables, totals, and CDC start LSN.

Record recovery duration, missing WAL, and object‑storage fetch rates.

Common failure modes discovered during drills include missing WAL segments, permission changes on object storage, and outdated archive paths.

8. Common Production Incidents and Troubleshooting

8.1 pg_wal Disk Full

Check three root causes:

Archive failures.

Replica lag.

Replication slots holding back WAL.

Useful queries:

SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

SELECT application_name, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;

Mitigations: set max_slot_wal_keep_size, drop unused CDC slots, alert on prolonged archive failures.

8.2 Rising Commit Latency Without Slow SQL

Inspect: pg_stat_wal for WAL write/ sync metrics. pg_stat_bgwriter for checkpoint activity.

Disk latency at the host level.

Synchronous replication status.

Typical root causes: overly frequent checkpoints, synchronous replica delay, archive or backup I/O contention, and large transactions generating massive WAL bursts.

8.3 Former Primary Cannot Re‑join

Reasons:

Missing wal_log_hints or data checksums prevents pg_rewind.

Incomplete WAL archive for the required timeline.

Lack of fencing allows the old primary to serve writes erroneously.

Standard actions: decide whether to rewind or rebuild, verify archive completeness, and ensure proper fencing before bringing the node back.

8.4 CDC Lag Growing

Key failure points:

Large transactions increase decoding time.

Downstream consumer slowdown.

Network jitter affecting Kafka or other brokers.

Replication slot LSN lag.

Two‑layer defense:

Database side: monitor restart_lsn, retained WAL size, slot activity.

Downstream side: monitor consumer lag, batch commit time, deserialization errors.

Remember that Debezium/Kafka Connect provides at‑least‑once delivery; downstream services must implement idempotency or deduplication (e.g., an event_id primary‑key table).

9. Integrating WAL with Microservices and Messaging

9.1 End‑to‑End CDC Flow

Transaction Commit → WAL generation & persistence → Logical Decoding → Debezium pulls & writes to Kafka → Downstream services consume → Business idempotency / compensation

Three boundaries:

PostgreSQL guarantees commit order and WAL consistency.

CDC middleware transports changes but does not define business semantics.

Consumers must enforce idempotency and ordering.

9.2 WAL vs. Message Queue

When coupling DB changes to a message system, watch for feedback loops: bulk updates inflate WAL, which inflates CDC traffic, which can further back‑pressure the primary. Recommended limits:

Cap rows per transaction.

Shard CDC topics per table or database.

Alert on downstream consumer lag.

Set replication‑slot lag thresholds and auto‑detach policies.

9.3 Read‑Write Splitting and Visibility

Answer the key question: “How long after a write does the replica see the change?” Visibility is governed by the replica’s replay LSN. Strategies:

Eventual consistency – allow seconds of lag.

Session stickiness – route reads to primary shortly after a write.

Strong consistency – block reads until the required LSN is replayed.

10. Engineering Checklist for Production‑Ready WAL

10.1 Architecture Checks

Separate roles for primary, local replica, remote replica, and archive/recovery.

Store pg_wal on low‑latency, high‑throughput media.

Use an automatic orchestrator (Patroni) for failover.

Ensure point‑in‑time recovery (PITR) is configured.

10.2 Parameter Checks

Validate checkpoint_timeout and max_wal_size against RTO goals.

Set max_slot_wal_keep_size to bound replication‑slot WAL retention.

Fine‑tune synchronous_commit per business tier.

Match wal_level to physical or logical replication needs.

10.3 Observability Checks Continuously collect pg_stat_wal , pg_stat_replication , and pg_stat_bgwriter . Alert on archive failures, pg_wal directory growth, and replication‑slot lag. Correlate transaction commit latency with WALSync wait times. 10.4 Recovery Checks Run periodic base‑backup restore drills. Verify arbitrary‑time‑point restores. Document fallback procedures when rewind fails. Measure RPO, RTO, and any missing WAL during drills. 10.5 Application Checks Avoid large, long‑running transactions and remote calls inside transactions. Use PgBouncer to control connection explosion and transaction lifetimes. Implement idempotent consumption and deduplication for CDC downstream. Incorporate replica lag into read‑routing decisions. 11. Evolution Roadmap: From Running to Resilient Stage 1 – Stable Single Node: Separate WAL storage, tune checkpoints, monitor pg_stat_wal . Stage 2 – HA with Physical Replication: Add streaming replicas, manage failover with Patroni, introduce PgBouncer. Stage 3 – Recoverability: Implement base backup + continuous WAL archive, enable PITR, automate restore drills. Stage 4 – Data Platform Integration: Deploy logical replication / Debezium, set governance for slots, CDC backlog, and downstream idempotency. Stage 5 – Multi‑Region & Tiered Consistency: Add cross‑region replicas, use stronger synchronous settings for critical transactions, keep weaker settings for high‑throughput paths with compensation. The goal is not merely adding features, but turning WAL from an internal mechanism into an explicit, engineered capability that spans commit latency, replication visibility, CDC throughput, and disaster recovery. Conclusion WAL determines when a transaction is truly safe, when a replica catches up, when CDC starts consuming, and how far a system can recover after a failure. Mature PostgreSQL operations treat COMMIT , LSN, replication slots, checkpoints, PITR, and CDC as a unified data‑plane that can be designed, tuned, monitored, and validated, turning a low‑level log into a production‑grade backbone.