PostgreSQL Internals: WAL, Replication, and Scaling

An application begins with one PostgreSQL server. Eventually, product searches compete with order writes, reporting queries consume memory, and a database restart interrupts every request.

“Add replicas” sounds like one solution. It actually raises several questions. Can a replica accept writes? When does a committed order become visible there? Which copy should take over if the primary disappears? Would two writable servers double capacity?

The answers start with write-ahead logging, or WAL. It connects transaction durability, crash recovery, replication, and backups. Understanding that path makes PostgreSQL infrastructure much easier to reason about.

This guide assumes you can write SQL and connect an application to PostgreSQL. It builds the infrastructure model from there. Examples and behavior use PostgreSQL 18 as the documentation baseline and focus on ordinary WAL-logged tables with fsync=on.

Separate the Three Scaling Goals

Before choosing a topology, name the problem:

A replica can help two of these goals, but it does not automatically solve all three.

A physical standby repeats the primary's changes. It cannot independently accept application writes while remaining a physical standby. Adding five such servers does not turn one primary's write workload into six parallel write workloads.

Also separate RPO, the amount of committed data loss you can tolerate, from RTO, the time you can tolerate before service recovers. A fast promotion can meet an RTO while losing recent asynchronous transactions and missing the RPO.

Follow a Transaction into WAL

Suppose the application changes an order from pending to paid.

PostgreSQL stores table and index data in fixed-size pages, normally 8 KiB. It loads pages into shared buffers, a shared memory area accessible to server processes.

An update normally creates a new row version under PostgreSQL's MVCC model. Older versions remain available to transactions whose snapshots need them, until cleanup can reclaim them. Index maintenance depends on the update; eligible HOT updates can avoid new index entries.

The important point for infrastructure: the logical action “update one row” can create table work, index work, WAL, and later vacuum work. Row size alone does not predict write cost.

The write ahead rule

As PostgreSQL modifies pages, it generates WAL records describing changes needed for recovery. WAL is a binary recovery stream, not a file containing the original SQL statements.

The central rule is: before PostgreSQL writes a changed page to its data file, the WAL describing that change must already be durable.

This shows the local durability path; synchronous standby waits come later. A data page may be written before or after its transaction commits. The invariant concerns WAL-before-data, not “all data files are written after COMMIT.”

At normal durable commit, PostgreSQL flushes WAL through the transaction's commit record. It does not need to flush every table and index page changed by that transaction immediately.

If the process or machine crashes, recovery can reconstruct missing page changes from WAL. That is why delaying data-page writes is safe under the durability assumptions.

Why WAL helps performance

Without WAL, a transaction touching scattered pages could require making all those pages durable before returning success. WAL turns the commit-critical path into flushing a sequential log.

Concurrent transactions can also benefit from group commit: one WAL flush can cover several commit records. This reduces expensive synchronization operations without weakening their durability.

WAL does not remove table writes. It separates their scheduling from each transaction's immediate acknowledgement, allowing PostgreSQL to spread and combine work.

The operating system's file cache adds another layer. A successful write to a file is not necessarily durable against power loss. PostgreSQL uses flush mechanisms, and storage must honor them.

Inspect the stream with LSNs

A log sequence number, or LSN, identifies a byte position in the WAL stream. You can compare positions to estimate how many WAL bytes separate two points.

Run this in psql against a scratch database on the primary. The table name is deliberately specific so the exercise is easy to recognize.

CREATE TABLE wal_walkthrough_orders (
    id bigint PRIMARY KEY,
    status text NOT NULL
);
 
INSERT INTO wal_walkthrough_orders VALUES (42, 'pending');
 
SELECT pg_current_wal_insert_lsn() AS before_lsn \gset
 
BEGIN;
UPDATE wal_walkthrough_orders SET status = 'paid' WHERE id = 42;
COMMIT;
 
SELECT pg_wal_lsn_diff(
    pg_current_wal_insert_lsn(),
    :'before_lsn'::pg_lsn
) AS wal_bytes_since_sample;

The result is not the number of bytes in 'paid'. WAL includes record overhead and can include full-page images. Other database activity also shares the cluster-wide WAL stream, so this is an observation exercise, not isolated transaction accounting.

Checkpoints Make Recovery Practical

If WAL kept growing forever and recovery always started at database creation, restarts would become impractical.

A checkpoint establishes a recovery starting point by ensuring the relevant dirty pages are written and recording checkpoint information. Checkpoint writes are spread over time; it is not a single instant when every workload stops and every page is dumped.

The redo position can precede checkpoint completion. Recovery does not simply start after the timestamp printed for the latest checkpoint.

PostgreSQL replays page changes and reconstructs transaction status. WAL can contain changes from transactions that never committed; that does not make those row versions visible as committed data. MVCC and transaction status still govern visibility.

With full_page_writes=on, the first modification of a page after a checkpoint normally includes a full-page image in WAL. This protects against a partially written data page after a crash. It also explains why frequent checkpoints can increase WAL volume.

checkpoint_timeout and max_wal_size influence checkpoint frequency. A larger interval may reduce checkpoint pressure but increase recovery work. max_wal_size is a soft checkpoint-related limit, not a hard maximum for the pg_wal directory.

Old WAL can only be recycled when recovery, archiving, and replication retention requirements allow it. A failed archiver or abandoned replication slot can keep WAL far beyond that setting.

How a Standby Gets Its Data

A new physical standby needs both a base backup and enough WAL to make that copy consistent and bring it forward.

The base backup supplies the existing database files. Streaming only today's WAL to an empty directory cannot recreate years of earlier database state.

After initialization, the primary runs a WAL sender for the replication connection. The standby's WAL receiver receives and writes WAL. A recovery process replays those records into the standby's data pages.

Streaming replication transmits WAL incrementally. It does not need to wait for an entire WAL segment to fill. File-based archiving, by contrast, normally operates on completed segments.

Physical replication reproduces the whole database cluster's logged state, not selected tables. Keep primary and standby on the same major PostgreSQL version and compatible platforms; keep minor versions aligned as part of maintenance.

A concrete bootstrap example

Assume PostgreSQL 18 binaries, an existing primary at primary.internal, and a stopped standby with an empty data directory. The primary must permit the standby's network address, have a login role with REPLICATION, and have available WAL sender and replication slot capacity. Supply authentication through a protected .pgpass file, and use the deployment's TLS configuration.

Run as the operating-system account that will own the standby:

pg_basebackup \
  --dbname="host=primary.internal port=5432 user=replicator application_name=ha1" \
  --pgdata=/var/lib/postgresql/18/standby \
  --wal-method=stream \
  --write-recovery-conf \
  --create-slot \
  --slot=ha1_slot \
  --progress

The slot name must not already exist. Streaming WAL during the backup ensures the backup has the WAL it needs; the persistent slot also protects subsequent catch-up.

--write-recovery-conf creates standby.signal and writes connection settings into postgresql.auto.conf, including slot use. PostgreSQL 18 does not use the old recovery.conf workflow.

Check configuration appropriate to the standby host, then start it through your service manager. A successful connection is only the beginning: verify replay progress and monitor the slot's retained WAL.

Understand the Replication Modes

Several terms describe different dimensions rather than competing products:

A server can be both a hot standby and a synchronous HA candidate. “Standby” does not mean idle, and “read replica” does not automatically mean the right promotion candidate.

Routing arrows show connection destinations; replication arrows show WAL flow. The HA standby can serve reads, but keeping heavy reports off it preserves capacity for replay and promotion.

Cascading reduces direct primary connections and can reduce repeated cross-site traffic. It adds another dependency and potential delay. In PostgreSQL 18, cascading replication is asynchronous; the primary does not wait directly for a downstream cascade member.

Physical versus logical replication

Logical replication decodes WAL into changes associated with published tables. A subscriber applies those changes to its own tables. That permits selective replication, different indexes, and supported cross-major-version migrations.

It is not the same as replaying SQL statements, nor is it a physical copy. For updates and deletes, PostgreSQL needs a suitable replica identity, commonly the primary key, to locate the target row.

Core logical replication does not automatically replicate schema changes or sequence state. A subscriber intended for eventual writes needs deliberate schema, sequence, and conflict handling. These differences matter when using logical replication for migration rather than a reporting feed.

See replication topologies and logical decoding and CDC for more examples.

What Synchronous Commit Actually Waits For

Physical streaming replication is asynchronous by default. With ordinary local durable commit, the primary can return success before a standby has received the transaction.

If the primary's storage is lost at that point, promoting a lagging standby can lose acknowledged writes. The risk depends on the candidate's durable WAL position, not just whether a dashboard says “replica healthy.”

For synchronous replication, synchronous_standby_names selects eligible standbys and the required count. synchronous_commit selects the milestone the transaction waits for.

With an empty synchronous_standby_names, the non-off modes all provide local flushing without a remote wait.

This is an acknowledgement model, not a claim that transport cannot overlap local work. The critical question is which completed milestones permit success.

For the earlier standby named ha1, the primary configuration can require its durable copy:

# Primary postgresql.conf; requires a connected standby named ha1.
synchronous_standby_names = 'FIRST 1 (ha1)'
synchronous_commit = on

Reload the primary after changing these settings. Confirm application_name=ha1 on the standby connection and inspect pg_stat_replication.sync_state.

If ha1 is unavailable, qualifying commits wait. PostgreSQL does not silently weaken this contract because the wait is inconvenient. An orchestrator can change the policy, but that is an explicit durability/availability decision.

With multiple candidates, FIRST 1 (ha1, ha2) uses priority; ANY 1 (ha1, ha2) accepts one candidate's acknowledgement. The latter is a commit-acknowledgement quorum, not a distributed primary-election system.

Durability is different from read visibility

on can acknowledge a transaction whose WAL is durable on a standby but not yet replayed. A query sent there can still see the old state.

remote_apply waits for replay on the required synchronous standbys. It does not guarantee freshness on an unrelated asynchronous replica. A transaction holding an older snapshot also does not suddenly get a newer snapshot because replay advanced.

Synchronous replication adds network and storage latency to the commit path. Across distant regions, the network round trip alone can dominate. Keeping the synchronous candidate in another nearby zone and an asynchronous disaster-recovery copy farther away is a common compromise.

“Zero data loss” needs qualifications: protected transactions must use the required setting, the durable copy must survive, and failover must choose a candidate containing those writes. Promoting an arbitrary asynchronous replica defeats the guarantee.

Read Scaling Changes Application Semantics

Read replicas help when queries can tolerate their consistency model. Product browsing may accept slightly stale stock information. The confirmation page immediately after checkout often cannot.

Three practical approaches are:

1. Read from the primary for operations that require current committed state.
2. Wait for a replay position on a chosen replica before reading. This requires passing a suitable WAL position and handling timeouts and timeline changes.
3. Use remote_apply with deliberate routing to the standby that satisfied the requirement.

Keeping a user on the primary for a fixed interval after a write is a useful heuristic, not a proof of consistency. Replication can lag longer than the timer during an incident.

Route whole transactions appropriately. Splitting statements from one transaction across independent primary and replica connections does not preserve one transactional snapshot. PgBouncer pools connections; it is not, by itself, a SQL-aware read/write splitter.

Measure write and replay lag separately

On the primary, this query shows how far each directly connected standby has progressed. Run with a role permitted to inspect replication statistics.

SELECT application_name,
       state,
       sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS unsent_bytes,
       pg_wal_lsn_diff(sent_lsn, write_lsn) AS sent_not_written_bytes,
       pg_wal_lsn_diff(write_lsn, flush_lsn) AS written_not_flushed_bytes,
       pg_wal_lsn_diff(flush_lsn, replay_lsn) AS flushed_not_replayed_bytes
FROM pg_stat_replication;

These are reported positions sampled at different times, not a perfectly synchronized trace. Startup states can produce nulls. Use trends alongside workload and infrastructure metrics.

If WAL arrives quickly but replay falls behind, more network bandwidth may not help. The standby might lack I/O capacity or be competing with expensive queries.

now() - pg_last_xact_replay_timestamp() is also easy to misread. On an idle primary, it grows even when the standby has replayed everything. An old last-transaction timestamp is not automatically replication delay.

Reporting can conflict with recovery

A long query may need row versions that the primary has already vacuumed away. When the corresponding cleanup WAL reaches the standby, replay and the query can conflict.

The standby can delay replay within its configured limits, then cancel conflicting queries. max_standby_streaming_delay controls tolerated replay delay, not a simple independent runtime allowance for each query.

hot_standby_feedback can reduce cleanup conflicts by telling the primary about snapshots still needed on the standby. The cost can be extra dead-row retention and bloat on the primary. It does not remove every kind of recovery conflict.

This is why a heavily loaded reporting replica and a low-lag failover candidate may deserve separate roles.

Failover Requires More Than Replication

PostgreSQL can promote a standby. It does not provide a complete built-in system that decides the primary is dead, fences it, elects a replacement, and redirects every client.

An HA system such as Patroni with its coordination service, an operator, or a managed database service supplies that orchestration. The exact safety mechanisms differ, but the requirement is the same: only the authorized primary may accept writes.

Fencing can involve shutting down a node, revoking access, or a correctly enforced lease/watchdog mechanism. Merely changing DNS does not stop an isolated old primary from accepting writes through existing connections.

Without fencing, both machines can become writable: split brain. Two independent histories are much harder to repair than a short outage.

Promotion starts a new timeline

A promoted standby begins a new WAL timeline, identifying a new branch of database history. The old primary may contain changes absent from that branch.

Do not restart it and assume replication will merge the histories. Rejoin it using pg_rewind when its prerequisites and required WAL are available, or rebuild it from a new base backup. Rewind discards divergent changes; it does not resolve them as business transactions.

A planned switchover can drain writes and catch up the target before promotion. An unplanned failover has less certainty, particularly with asynchronous replication.

Clients need reconnect behavior too. Existing connections do not magically migrate. A commit response lost during failover leaves an uncertain outcome: the transaction may have committed. Use idempotency keys for operations whose retries must not charge or create twice.

Why Master Master Is a Different Problem

“Master/master,” “multi-primary,” and “active-active” usually mean accepting writes at multiple nodes. Core PostgreSQL physical replication does not offer that mode.

Logical replication allows writes on a subscriber, and bidirectional arrangements are possible. But copying changes in both directions is not the same as preserving application invariants under concurrent writes.

Suppose two regions each believe one ticket remains:

Both row values might eventually converge to zero. The business result is still wrong. Last-write-wins resolution cannot undo one customer's confirmed purchase cleanly.

Other problems include duplicate unique keys, conflicting updates and deletes, foreign-key dependencies, sequence allocation, DDL coordination, and preventing replication loops. UUIDs help avoid some key collisions; they do not solve concurrent updates to the same account balance.

There are three broad choices:

• Partition ownership: each tenant or entity has one writable home. Replicate elsewhere for reads and route writes to the owner.
• Coordinate conflicting writes: add cross-node agreement or locking. This changes latency and availability during network partitions.
• Accept and resolve conflicts: suitable only when the domain has explicit, acceptable merge rules.

Third-party PostgreSQL multi-writer systems, such as EDB Postgres Distributed, add machinery around these problems. Evaluate their supported conflict rules, DDL behavior, operational requirements, and failure semantics. They are not a core setting that makes any application safely multi-primary.

For many backend systems, single-writer ownership per shard is easier to reason about than allowing every node to modify every row.

Scale the Infrastructure Around the Bottleneck

Reduce work before multiplying servers

Use query plans and workload statistics to identify expensive reads and writes. An unnecessary index consumes update work, WAL, cache, and vacuum effort. A missing index can make a small update scan and lock far more data than expected.

Keep transactions short. A large transaction holds resources longer and creates bursts of replication and recovery work. Autovacuum capacity is part of write capacity because dead-row accumulation changes future I/O cost.

Pool connections rather than maximizing them

PostgreSQL uses a process per client connection. Thousands of active backends compete for CPU, memory, and locks. Raising max_connections does not create more database capacity.

PgBouncer can multiplex application connections onto a bounded set of server connections. Transaction pooling needs compatibility checks for session-level features. Size pools across all application instances, not independently as though each service owns the whole database.

The connection-pooling guide covers those application trade-offs.

Storage has several limits

Capacity in terabytes is only one dimension. Watch random-read IOPS, sustained throughput, write latency, and especially WAL flush latency. Cloud volumes and instances can impose separate limits even when the disk looks underutilized.

More RAM can improve caching. More CPU can help concurrent execution. Neither fixes a serialized hot-row lock. A faster WAL volume may reduce commit waits, but separating WAL onto another volume is useful only when measurements and the underlying storage isolation justify it.

Size the HA standby to handle the primary workload after promotion, not merely its normal idle load. A low-cost replica that can barely replay WAL is a weak failover target.

Replication slots exchange safety for retained storage

A replication slot tracks WAL that a consumer may still need. It prevents premature recycling, which makes disconnection recovery easier.

It can also fill the primary's disk. At 10 MB/s of WAL generation, one disconnected slot retaining six hours of history can pin roughly 216 GB, before segment rounding and other retention requirements.

Inspect slots on the primary:

SELECT slot_name,
       slot_type,
       active,
       wal_status,
       pg_size_pretty(
           pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal
FROM pg_replication_slots
ORDER BY restart_lsn NULLS LAST;

max_slot_wal_keep_size can limit slot retention at checkpoint time. It is not a universal hard disk cap. Exceeding the retention allowance can make a consumer unable to resume from its slot, requiring recovery from retained archives or reinitialization.

Logical slots can also hold back cleanup of catalog or row history. Monitor their age and progress, not only whether a client is currently connected. A forgotten CDC pipeline is an infrastructure dependency.

Partitioning is not sharding

Table partitioning splits a logical table into smaller relations within a PostgreSQL deployment. It can improve pruning, index management, and data expiry. It does not distribute one primary's writes across independent servers by itself.

Sharding gives different database nodes ownership of different data subsets, often by tenant or account. Each shard can have its own primary and standbys. This adds write parallelism when transactions mostly stay within one shard.

The costs are routing, cross-shard queries, global uniqueness, distributed transactions, and rebalancing. Tools such as Citus can supply distributed-table machinery, but the shard key and transaction boundaries remain application design decisions.

In multiple regions, put ownership close to the writers where possible. An asynchronous copy improves local read access but brings stale reads. A distant synchronous copy adds commit latency. Two writable copies introduce coordination or conflict handling. Geography does not remove those choices.

Put the Pieces into One Deployment

Return to the earlier topology: a primary in zone A, an HA standby in zone B, and a read replica in zone C.

Use it as a set of explicit contracts:

• Writes and consistency-sensitive reads use the primary endpoint through a bounded connection pool.
• The HA standby uses durable synchronous acknowledgement for protected transactions and has capacity to become primary.
• Lag-tolerant reads use the asynchronous replica, with monitoring and a policy for excessive lag.
• The HA control plane authorizes promotion and fences the previous primary before the new endpoint accepts writes.
• An optional remote asynchronous standby provides a different disaster-recovery trade-off, with its own RPO.

With only one eligible synchronous standby, losing that standby can block commits even while the primary is healthy. Decide whether to provision another eligible candidate or deliberately relax durability during that incident. This decision belongs in the operating model, not an improvised configuration change.

Backups protect against different failures

Replication faithfully copies mistakes. An accidental DELETE can reach all replicas before anyone notices. Standbys are therefore not substitutes for backups.

Maintain base backups and a continuous WAL archive in independently durable storage. Point-in-time recovery, or PITR, restores a base backup and replays WAL up to a selected point before the damaging transaction.

The recovery window requires an unbroken WAL chain from the chosen backup. Check archive failures and retain the segments needed by the backup policy, not just the segments a current standby needs.

Perform restore exercises into a separate environment. Measure how long downloading the backup, replaying WAL, validating data, and switching the application actually take. A backup job returning success does not establish the RTO.

Managed services and Kubernetes operators automate parts of this lifecycle. They do not change WAL semantics or remove application retry and consistency requirements. The PostgreSQL on Kubernetes guide discusses that deployment layer.

The Scaling Model to Remember

WAL makes committed changes recoverable before every changed data page has reached disk. Physical replicas receive that recovery stream and replay it. Synchronous settings choose how much remote progress must happen before a commit returns.

Read replicas move eligible query work. HA orchestration moves the writer role after failure. Sharding distributes write ownership. Multi-writer replication adds a separate problem: coordinating or resolving concurrent changes.

Start with those distinctions, then choose the smallest topology that meets the workload's capacity, consistency, RPO, and RTO requirements. Every additional server should have a clear job and a failure behavior you can explain.

References

• PostgreSQL 18 write-ahead logging
• WAL settings and synchronous commit modes
• Streaming replication, slots, cascading, and synchronous standbys
• Hot standby and recovery conflicts
• Failover and pg_rewind
• Logical replication restrictions
• Continuous archiving and point-in-time recovery