DatabasesIntermediate

Database Replication

Why asynchronous replication can silently lose the most recent commits if the primary crashes, what synchronous_commit's three levels actually each guarantee, and how to measure replication lag instead of assuming it's small.

Updated 2026-07-243 min read

On this page

Overview

Streaming replication keeps a standby up to date by continuously sending WAL (write-ahead log) records from the primary as they're generated, rather than waiting for a log file to fill, and it's asynchronous by default: a commit on the primary succeeds without waiting for the standby, typically under a second behind, but genuinely at risk of losing the most recent commits if the primary crashes before they replicate. Synchronous replication trades that risk for added commit latency, and its three synchronous_commit levels each guarantee something different: remote_write waits only for the standby to receive the record, on waits for the standby to flush it to disk, and remote_apply waits for the standby to actually replay it, the level that guarantees a standby read immediately after commit sees the change. Replication lag isn't something to assume is small, it's measured directly by comparing WAL positions on the primary and standby.

Quick Reference

Setting	Waits for	Durability
Asynchronous (default)	Nothing from the standby	Small window of possible data loss on primary crash
`synchronous_commit = remote_write`	Standby receives + hands to its OS	Lost only if standby OS itself crashes before its flush
`synchronous_commit = on`	Standby flushes to its own disk	Lost only if primary and standby crash simultaneously
`synchronous_commit = remote_apply`	Standby replays and makes visible	Standby reads see the commit immediately

Syntax

ini

# postgresql.conf on the standby
primary_conninfo = 'host=10.0.0.5 port=5432 user=replicator'

# On the primary, to require synchronous replication
synchronous_standby_names = 'standby1'
synchronous_commit = on

Examples

sql

-- Measure actual replication lag instead of assuming it's small.
SELECT pg_current_wal_lsn() AS primary_position; -- run on primary
SELECT pg_last_wal_replay_lsn() AS standby_position; -- run on standby
-- A growing byte gap (pg_wal_lsn_diff) between these two values is real, actionable lag.

Asynchronous replication can lose the most recent commits

The primary confirms a commit to the client without waiting for the standby. If the primary crashes before that WAL record replicates, the transaction the client was told succeeded is gone from the standby. This is documented, expected behavior for asynchronous replication, not a bug.

Visual Diagram

What each synchronous_commit level actually waits for

Common Mistakes

Assuming asynchronous replication has "basically no risk" because the typical lag is small, rather than understanding it's a real, non-zero data-loss window on primary crash.
Choosing synchronous_commit = on without understanding it adds real round-trip latency to every commit, then being surprised by the throughput cost.
Reading from a standby immediately after a primary commit under asynchronous or remote_write replication and expecting to see that change, when only remote_apply guarantees that.
Never actually monitoring replication lag via WAL position, and only discovering it was large during a failover that lost more data than expected.

Performance

Synchronous replication's minimum added latency per commit is the round-trip time to the standby; this is a direct, unavoidable cost of the durability guarantee, not a tunable inefficiency.
Replication lag under asynchronous replication grows under primary write load, network delay, or standby resource pressure, and widening lag is a leading indicator of standby health problems before they become a failover data-loss incident.

Best Practices

Choose the synchronous_commit level deliberately based on the actual durability requirement, not by defaulting to either extreme.
Monitor replication lag directly via WAL position comparison, not by assuming it stays small.
Use remote_apply specifically when read queries against a standby need to see a transaction immediately after the primary reports it committed.
Understand and accept asynchronous replication's data-loss window explicitly as a trade-off for lower commit latency, rather than treating it as risk-free.

Interview questions

A PostgreSQL primary crashes right after committing a transaction, under asynchronous replication. Is that transaction guaranteed to exist on the standby?

No. Asynchronous replication (the default) confirms a commit on the primary without waiting for the standby to receive or apply the corresponding WAL records, there's typically a small delay, often under a second, between a commit and its visibility on the standby. If the primary crashes in that window, before the WAL records reached the standby, that transaction is lost even though the client was already told it committed successfully. This is the specific, documented risk asynchronous replication accepts in exchange for not adding network round-trip latency to every commit.

What is the actual difference between synchronous_commit = on, remote_write, and remote_apply?

`on` (the standard synchronous setting) waits for the standby to write the commit record to its own disk, "2-safe" durability, data is lost only if both primary and standby crash simultaneously. `remote_write` only waits for the standby to receive the record and hand it to its operating system, not for a disk flush, weaker durability (a standby OS crash before its own flush could still lose it) in exchange for a faster commit. `remote_apply` is the strongest of the three, it waits until the standby has actually replayed the transaction and made it visible to queries, which is what allows read queries against the standby to see a transaction immediately after the primary reports it committed.

How do you actually measure replication lag, rather than assuming it's negligible?

Replication lag is the gap between WAL records generated on the primary and WAL records actually applied on the standby, and it's measured concretely by comparing the primary's current WAL write position, from `pg_current_wal_lsn()`, against the standby's last replayed position, from `pg_last_wal_replay_lsn()`, via `pg_wal_lsn_diff()`. A large or growing byte gap is a real health signal, it points at the primary generating WAL faster than the network or standby can keep up, or the standby itself being under heavy load, not something to infer from "it's usually under a second" folklore. Monitoring this value directly, not assuming a small delay, is what actually catches a standby falling dangerously behind before a failover makes that lag visible as lost data.

References

PostgreSQL Documentation - Log-Shipping Standby Servers

DatabasesIntermediate

Database Connection Pooling

Why transaction pooling gives far better connection reuse than session pooling, and why that same efficiency silently breaks SQL PREPARE, SET, LISTEN, and session-level advisory locks, even though PgBouncer can support protocol-level named prepared statements under transaction pooling when max_prepared_statements is enabled.

Updated 2026-07-24

3 min read

DatabasesIntermediate

Database Locking & Deadlocks

How PostgreSQL's row-level lock modes actually differ in strength, why it can't predict which transaction a deadlock will abort, and why acquiring locks in a consistent order is the real fix, not a "nice to have".

Updated 2026-07-24

3 min read

DatabasesIntermediate

Database Partitioning

How range, list, and hash partitioning each split one logical table into physical pieces, and why partition pruning depends entirely on the WHERE clause matching partition bounds directly, not on any index.

Updated 2026-07-24

3 min read

DatabasesIntermediate

Database Transactions & Isolation Levels

Why PostgreSQL's default Read Committed isolation still allows non-repeatable reads and phantom reads, and why Repeatable Read and Serializable trade that risk for transactions your application has to be ready to retry.

Updated 2026-07-24

3 min read

Announcement