Postgres Logical Replication Reorders Writes That Sync Standbys Already Committed

A synchronous standby and a logical subscriber can acknowledge entirely different histories. The standby confirms that bytes landed; the subscriber confirms that a decoded transaction applied. When those two diverge, an application that reads from the logical replica can miss a row that the primary already committed, and no error is raised anywhere. This piece explains why the divergence happens and how to detect it.

The Standby Commit Illusion

Synchronous replication in PostgreSQL is a byte-level promise. The primary waits for a standby to confirm that WAL records reached durable storage before returning success to the client. That confirmation says nothing about what the standby can show a reader. Visibility depends on replay, and replay depends on the standby's own recovery process, which may lag behind the last flushed WAL segment by a small but nonzero window.

Logical replication decodes that same WAL stream into row-level changes and ships them to a subscriber. The decode happens independently of standby acknowledgment. A subscriber can apply a transaction only after the WAL sender has decoded it, serialized it, and sent it across the network. The synchronous standby, meanwhile, has already told the primary the bytes are safe. Durability and logical consistency are two separate properties that happen to share a file.

This matters because operators often treat "the standby has it" as shorthand for "the data is everywhere." In a deployment with both a synchronous standby and a logical subscriber, that shorthand is wrong. The standby has the bytes. The subscriber may not have the row. Readers hitting the subscriber see a state that the standby would never show them.

How Logical Decoding Reorders Work

Logical decoding runs as a separate process on the primary, reading WAL through a replication slot. It reconstructs transactions from their individual record changes and emits them in commit order as decoded. That order is the primary's commit order, not the order any subscriber applies them. The subscriber's apply worker receives a stream and applies transactions sequentially, but parallel apply workers can interleave changes from different transactions when they touch disjoint tables.

Subtransactions add another layer. A transaction that uses savepoints emits multiple subtransaction records, and the decoder must reassemble them into a single logical commit. If the reassembly or the apply step fails partway, the subscriber can end up with a partial state that never existed on the primary. The slot retains WAL until the subscriber confirms, so the primary keeps the data, but the subscriber's visible state is a construction, not a copy.

The result is that commit order on the subscriber can diverge from commit order on the primary. For a single-table workload with no parallel apply, the divergence is usually small. For a multi-table workload with parallel workers, or a workload with heavy savepoint use, the window widens and the reordering becomes observable.

Read-Your-Writes Breaks Quietly

An application writes to the primary, then reads from a logical replica to offload query load. The primary commits. The synchronous standby confirms the bytes. The application immediately reads the row back from the logical replica and gets nothing. No error is returned, because the replica is healthy and the query is valid. The row simply has not arrived yet.

Connection pooling hides which node serves each read. A pool with a primary endpoint and a replica endpoint may route the read to either, depending on configuration and timing. When the read lands on the replica before the logical apply worker has caught up, the application sees a missing row. Retry logic often papers over this by re-reading until the row appears, which masks the problem and adds latency without fixing the underlying ordering gap.

This is the same class of failure that shows up in mobile and embedded backends, where a device writes through one path and reads through another. A related piece on this site about payment terminals shipping with default keys covers a different layer of the same trust problem: the system assumes a consistency that the transport never promised.

Cascading and Multi-Hop Amplify Drift

A logical subscriber can itself be configured as a publisher for a downstream node. Each hop adds decode latency, network latency, and apply latency. Two hops roughly double the window during which a downstream reader can miss a row that the original primary committed. Three hops widen it further.

Conflict resolution on the subscriber rewrites history. When a subscriber receives a change that conflicts with a local row, the resolution policy decides which version wins. That decision is local to the subscriber and is not reflected back to the primary. Downstream nodes inherit the rewritten state, not the original.

Origin filtering drops rows without warning. A subscription can be configured to ignore changes that originated from a particular node, which is useful for avoiding loops in bidirectional setups. When the filter is misconfigured, changes disappear silently. The primary has them, the standby has them, and the subscriber does not.

Instrumenting for Reorder Detection

Track commit timestamps per node. PostgreSQL can record commit timestamps when the relevant parameter is enabled, which lets an operator compare the timestamp of a transaction on the primary with its arrival time on the subscriber. A consistent offset is normal. A growing offset or an out-of-order arrival is the signal.

Compare pg_stat_replication with subscriber lag. The view shows the primary's view of each standby's progress. The subscriber's own statistics show what it has applied. Reading both side by side reveals which node is behind and by how much.

Log WAL positions at transaction boundaries. A trigger or an application-level log line that records the current WAL insert position alongside the application's own transaction ID makes it possible to reconstruct ordering after an incident.

Alert on out-of-order sequence numbers. If the application uses a monotonic sequence per entity, the subscriber can detect a gap or a regression and raise it before a user notices a missing row.

Practical Mitigations for Operators

Route read-your-writes traffic to the primary or to the synchronous standby. The standby may lag in visibility, but it is closer to the primary's committed state than a logical subscriber, and its lag is bounded by replay rather than by decode and network transit.

Pin sessions to a single replica per transaction. A session that writes and then reads within the same transaction should not be routed to a node that has not applied the write. Session pinning removes the pool's ability to choose the wrong node mid-transaction.

Use quorum commit for cross-node consistency. A quorum commit forces the primary to wait for acknowledgment from a majority of standbys, which tightens the window during which a subscriber can be behind. It costs write latency, and the cost grows with the number of nodes in the quorum.

Monitor logical slot lag separately from physical lag. The two numbers measure different things, and a healthy physical lag can coexist with a large logical slot lag. A related piece on this site about maintainer burnout deciding which microservices get patched makes the broader point that operational signals are only useful when someone is assigned to read them.

Test failover with logical subscribers in the path. A failover that promotes a standby can leave a logical subscriber pointed at a node that no longer exists or that has a different WAL history. Rehearsing the failover with the subscriber in place surfaces the ordering assumptions before an incident does.

Trade-offs and When to Avoid Logical Replication

Logical replication is not a drop-in replacement for synchronous standby. It is a separate mechanism with its own costs and failure modes. The primary trade-off is between flexibility and consistency. Logical replication offers selective table replication, cross-version upgrades, and the ability to feed multiple downstream consumers. Those benefits come at the cost of a wider consistency window and additional operational complexity.

Consider the write amplification. Each decoded transaction is serialized, sent over the network, and applied on the subscriber. For high-throughput workloads, this can become a bottleneck. The WAL sender process on the primary consumes CPU and memory. The subscriber's apply worker may fall behind if it cannot keep up with the incoming stream. Monitoring the apply rate versus the generation rate is essential. If the apply rate consistently lags, the slot will grow, consuming disk space on the primary.

Network partitions can cause the subscriber to fall behind by minutes or hours. When the network recovers, the subscriber must catch up by applying a backlog of transactions. During this catch-up, the subscriber's state is even further behind the primary. Applications that read from the subscriber may see stale data for an extended period. This is a fundamental difference from synchronous standbys, which stop accepting writes if they cannot confirm replication.

For workloads that require strict read-your-writes, logical replication may be inappropriate. If the application cannot tolerate any window of inconsistency, synchronous standby or direct reads from the primary are safer choices. Logical replication shines in scenarios where eventual consistency is acceptable, such as analytics, caching, or cross-region distribution where latency is more important than immediate consistency.

Another trade-off is the complexity of conflict resolution. In bidirectional setups, conflicts are inevitable. The resolution policies are limited and can lead to data loss if not carefully configured. Testing conflict scenarios is crucial before deploying to production. A misconfigured policy can silently drop changes, leading to data divergence that is hard to detect and repair.

Finally, consider the operational overhead. Logical replication requires monitoring replication slots, managing publications and subscriptions, and handling schema changes carefully. Schema changes on the primary do not automatically propagate to the subscriber. The subscriber must be altered manually, and if the schema change is not applied in time, the apply process can fail. This adds a coordination burden that does not exist with physical standbys.

In summary, logical replication is a powerful tool, but it is not a substitute for synchronous replication when strong consistency is required. Understanding the trade-offs helps operators choose the right tool for the job and avoid unexpected data inconsistencies.