Read‑your‑writes on replicas: PostgreSQL WAIT FOR LSN and MongoDB Causal Consistency

Source: Dev.to

Overview

In databases designed for high availability and scalability, secondary nodes can fall behind the primary. Typically a quorum of nodes is updated synchronously to guarantee durability while maintaining availability, and the remaining standby instances are eventually consistent to handle partial failures.

To balance availability with performance, synchronous replicas acknowledge a write only when it is durable and recoverable, even if it is not yet readable.

Result: If your application writes data and then immediately queries another node, it may still see stale data.

Example anomaly

You commit an order on the primary and then try to retrieve it from a reporting system. The order is missing because the read replica has not yet applied the write.

Both PostgreSQL and MongoDB provide mechanisms to avoid this problem, but they take different approaches.

PostgreSQL: WAIT FOR LSN (PG 19 – currently in development)

PostgreSQL records every change in the Write‑Ahead Log (WAL). Each WAL record has a Log Sequence Number (LSN) – a 64‑bit position usually shown as two hexadecimal halves, e.g. 0/40002A0 (high/low 32 bits).

Streaming replication ships WAL records from the primary to standbys, which then:

1. Write WAL records to disk
2. Flush them to durable storage
3. Replay them, applying changes to data files

A recent commit for PostgreSQL 19 adds a WAIT FOR LSN command that lets a session block until one of these points reaches a target LSN.

Typical workflow

1. Write on the primary and commit.
2. Fetch the current WAL insert LSN.
3. Wait on a replica until it has caught up to that LSN.

-- 1. Start a transaction on the primary
BEGIN;

-- 2. Insert a row
INSERT INTO orders VALUES (123, 'widget');

-- 3. Commit the transaction
COMMIT;

-- 4. Get the current WAL insert LSN
SELECT pg_current_wal_insert_lsn();

Result (example):

 pg_current_wal_insert_lsn
---------------------------
 0/18724C0
(1 row)

Now block reads on a replica until it has replayed that LSN:

-- On the replica
WAIT FOR LSN '0/18724C0'
  WITH (MODE 'standby_replay', TIMEOUT '2s');

If the replica reaches the requested LSN within the timeout, the command returns; otherwise it aborts with a timeout error.

When to use it

• Expensive reads that you want to off‑load to replicas while still guaranteeing read‑your‑writes semantics.
• Event‑driven / CQRS architectures where the LSN itself serves as a change marker for downstream consumers.

For many workloads, simply reading from the primary is simpler and faster.

MongoDB: Causal Consistency

MongoDB tracks causality using oplog timestamps and a Hybrid Logical Clock (HLC).

• In a replica set, each write on the primary creates an entry in local.oplog.rs.
• Each entry carries an HLC timestamp that combines physical time with a logical counter, yielding a monotonically increasing cluster time.
• Replica set members apply oplog entries in timestamp order.

Because MongoDB allows concurrent writes, “oplog holes” can appear: a later‑timestamped write may commit before an earlier‑timestamped one. A naïve reader could skip the earlier operation.

MongoDB solves this by tracking an oplogReadTimestamp, the highest hole‑free point in the oplog. Secondaries are prevented from reading past this point until all prior operations are visible, ensuring causal consistency even with concurrent commits.

Enforcing causal consistency

• Drivers track the operationTime of the last operation in a session.
• When a session is created with causalConsistency: true, the driver automatically includes an afterClusterTime equal to the highest known cluster time on subsequent reads.
• The server blocks the read until its cluster time has advanced beyond afterClusterTime.

Example (Node.js driver)

// Start a causally consistent session
const session = client.startSession({ causalConsistency: true });

const coll = db.collection("orders");

// Write in this session
await coll.insertOne({ id: 123, product: "widget" }, { session });

// The driver automatically injects afterClusterTime into the read concern
const order = await coll.findOne({ id: 123 }, { session });

With any read preference that allows reading from secondaries as well as the primary, this guarantees read‑your‑writes behavior.

Note: Causal consistency is not limited to snapshot reads; it applies across all read‑concern levels. The session ensures later reads observe at least the effects of earlier writes, regardless of which replica serves the read.

Conclusion

Both PostgreSQL and MongoDB provide mechanisms to achieve read‑your‑writes semantics in distributed environments, but they differ in implementation:

Choose the approach that best matches your application’s consistency requirements, latency tolerance, and architectural style.

Physical Byte Offset in the WAL (LSN) vs. Hybrid Logical Clock (HLC)

Key Takeaways

• Both PostgreSQL’s WAIT FOR LSN and MongoDB’s causal consistency ensure that reads can observe prior writes, but they operate at different layers:

  • PostgreSQL offers manual, WAL‑level precision.
  • MongoDB provides automatic, session‑level guarantees.

• If you want “read‑your‑writes” semantics to just work without extra coordination calls, MongoDB’s session‑based model is a strong fit.

• Despite persistent myths about consistency, MongoDB delivers strong consistency in a horizontally scalable system while offering a simple developer experience.