WAL, replication and read replicas

Why log first

Writing a few sequential WAL records is much cheaper than writing every changed page in random places at every commit. Pages are flushed in bulk at checkpoints. The settings that trade safety for speed live here:

• synchronous_commit = on (the default): a commit waits for its WAL to be flushed locally (and, if synchronous standbys are configured, for them as well).
• synchronous_commit = off: the commit returns before the flush. A crash can lose the last few hundred milliseconds of commits, but never corrupts the database. It can be set per transaction, for data you could afford to lose.
• fsync = off: never in production; a crash can corrupt data.

Streaming replication

A standby connects to the primary and receives WAL records as they are generated, then replays them. With hot_standby on (the default), it accepts read-only queries while it does. Two consequences follow.

Replicas lag. Streaming replication is asynchronous by default. The primary commits and replies, and the standby applies the change a little later, usually milliseconds, sometimes much more under heavy writes or a slow network. Measure it on the primary:

1SELECT application_name, state, write_lag, flush_lag, replay_lag2FROM pg_stat_replication;

replay_lag is how long it takes for a commit on the primary to be visible on that standby. On the standby itself, now() - pg_last_xact_replay_timestamp() gives a rough figure (misleading when the primary is idle).

Failover can lose commits. If the primary dies, commits it had acknowledged but not yet sent are gone when you promote a standby. Synchronous replication closes that window:

1synchronous_standby_names = 'ANY 1 (replica_a, replica_b)'2synchronous_commit = on            # wait until a standby has flushed the WAL3# remote_apply: wait until it has also applied it, so reads there see the commit

Every commit now waits for a network round trip, and if no synchronous standby is available, commits wait indefinitely. That is why "ANY 1" of two or more is common.

Reading from replicas

Read replicas spread read load, but the application has to cope with lag:

• Send reads that must see the user's own recent writes to the primary, for example for a few seconds after a write, or remember the WAL position of the write (pg_current_wal_lsn()) and read from a replica only once pg_last_wal_replay_lsn() has passed it.
• Use replicas for work that tolerates staleness: reports, search indexing, dashboards.

Long queries on a standby can conflict with replay: if the primary vacuums away rows the query still needs, the standby holds back replay up to max_standby_streaming_delay (30 seconds by default) and then cancels the query. hot_standby_feedback = on tells the primary to keep those rows instead, at the cost of bloat there.

Logical replication is a different mechanism: it decodes the WAL into row changes per table and publishes them to subscribers, which can run other major versions or have different indexes. It is how change data capture tools read PostgreSQL.