PostgreSQL 19 WAIT FOR LSN: Solving Read-Your-Writes Consistency in .NET

Applications that use PostgreSQL streaming replication often send writes to a primary database and reads to a replica. This architecture improves read scalability, but it introduces an important consistency problem.

A user can successfully update data on the primary and immediately request that same data through a read replica. If replication has not caught up yet, the application may return the old value.

This is commonly described as the read-your-writes consistency problem.

PostgreSQL 19 introduces the WAIT FOR LSN command, which provides a direct way for an application to wait until a specific Write-Ahead Log (WAL) position has been reached on a standby. PostgreSQL documentation specifically identifies standby_replay mode as useful for read-your-writes consistency when writes go to a primary and reads go to an asynchronous replica.

For .NET applications using Npgsql, this creates a practical pattern for coordinating writes on the primary with subsequent reads from a replica.

Understanding the Read-Your-Writes Problem

Consider an application with this architecture:

.NET Application
      |
      +---- Write ----> PostgreSQL Primary
      |
      +---- Read -----> PostgreSQL Replica

Suppose a user changes their profile name:

1. UPDATE executes on primary
2. Primary commits the transaction
3. Application immediately queries replica
4. Replica has not replayed the WAL record yet
5. Application receives the previous value

The write succeeded, but the following read does not immediately observe it.

This is not necessarily a database failure. It is a consequence of asynchronous replication.

PostgreSQL tracks replication progress using Log Sequence Numbers (LSNs). An LSN identifies a position in the WAL stream.

PostgreSQL 19's WAIT FOR LSN allows a client to wait until a target LSN has been written, flushed, or replayed, depending on the selected mode.

What Is `WAIT FOR LSN`?

The basic syntax is:

WAIT FOR LSN '0/306EE20';

By default, PostgreSQL waits for the specified LSN to be replayed on a standby.

PostgreSQL 19 supports four modes:

Mode	Waits For	Typical Use
standby_replay	WAL replayed on standby	Read-your-writes
standby_write	WAL written on standby	Faster, weaker durability
standby_flush	WAL flushed on standby	Replica durability
primary_flush	WAL flushed on primary	Primary durability

The standby modes can only be used while the server is in recovery, while primary_flush is intended for a primary server.

For the read-your-writes scenario, standby_replay is the important mode because the application wants the change to actually be visible through queries executed against the replica.

Capturing the LSN After a Write

The application first needs to identify the WAL position associated with its change.

For example:

UPDATE customer
SET display_name = 'John Smith'
WHERE id = 1001;

SELECT pg_current_wal_insert_lsn();

The result might look like:

0/306EE20

The application can then pass that LSN to a connection connected to the replica:

WAIT FOR LSN '0/306EE20';

Once the command succeeds, the standby has replayed WAL through at least that numeric LSN. PostgreSQL's documentation demonstrates this exact pattern for making changes on the primary visible on a replica.

Implementing the Pattern in .NET

A typical .NET application can maintain separate connections for writes and reads.

For example:

using Npgsql;

var primaryConnectionString =
    "Host=primary;Database=appdb;Username=app;Password=secret";

var replicaConnectionString =
    "Host=replica;Database=appdb;Username=app;Password=secret";

The primary connection performs the update:

await using var primary =
    new NpgsqlConnection(primaryConnectionString);

await primary.OpenAsync();

await using var updateCommand =
    new NpgsqlCommand("""
        UPDATE customer
        SET display_name = @name
        WHERE id = @id
        """, primary);

updateCommand.Parameters.AddWithValue("name", "John Smith");
updateCommand.Parameters.AddWithValue("id", 1001);

await updateCommand.ExecuteNonQueryAsync();

After the write, obtain the WAL position:

await using var lsnCommand =
    new NpgsqlCommand(
        "SELECT pg_current_wal_insert_lsn()",
        primary);

var lsn = (string?)await lsnCommand.ExecuteScalarAsync();

The application can then wait on the replica.

await using var replica =
    new NpgsqlConnection(replicaConnectionString);

await replica.OpenAsync();

await using var waitCommand =
    new NpgsqlCommand(
        "WAIT FOR LSN $1",
        replica);

waitCommand.Parameters.AddWithValue(lsn!);

await waitCommand.ExecuteNonQueryAsync();

After the wait completes, the application can execute its read against the replica.

The exact parameter handling should be verified against the Npgsql version used by the application, but the architectural sequence remains the same: write → capture LSN → wait on replica → read.

Adding a Timeout

A production application should avoid waiting indefinitely.

PostgreSQL 19 allows a timeout:

WAIT FOR LSN '0/306EE20'
WITH (
    TIMEOUT '500ms'
);

If the target LSN is not reached within the specified period, PostgreSQL reports a timeout unless NO_THROW is specified.

A .NET application can use this to establish a bounded consistency window.

For example:

var sql = """
    WAIT FOR LSN $1
    WITH (
        MODE 'standby_replay',
        TIMEOUT '500ms'
    )
    """;

The application can then decide what to do if the replica does not catch up within the expected period.

Possible strategies include:

Retry the wait.
Read from the primary instead.
Return a controlled response.
Record the event for monitoring.

The correct choice depends on the application's consistency requirements.

Using `NO_THROW` for Controlled Handling

PostgreSQL 19 also provides NO_THROW.

For example:

WAIT FOR LSN '0/306EE20'
WITH (
    TIMEOUT '500ms',
    NO_THROW
);

Instead of raising an error for a timeout, the command returns a status such as:

success

or:

timeout

The documentation defines success, timeout, and not in recovery as possible return statuses when NO_THROW is used.

This can make application-level handling easier because the application can explicitly choose a fallback path.

A More Production-Oriented .NET Service

A small service abstraction can keep the replication logic out of controllers.

public interface IReplicaConsistencyService
{
    Task<bool> WaitForReplayAsync(
        string lsn,
        TimeSpan timeout,
        CancellationToken cancellationToken);
}

An implementation can execute the PostgreSQL command:

public sealed class ReplicaConsistencyService
    : IReplicaConsistencyService
{
    private readonly NpgsqlDataSource _replicaDataSource;

    public ReplicaConsistencyService(
        NpgsqlDataSource replicaDataSource)
    {
        _replicaDataSource = replicaDataSource;
    }

    public async Task<bool> WaitForReplayAsync(
        string lsn,
        TimeSpan timeout,
        CancellationToken cancellationToken)
    {
        await using var connection =
            await _replicaDataSource.OpenConnectionAsync(
                cancellationToken);

        const string sql = """
            WAIT FOR LSN $1
            WITH (
                MODE 'standby_replay',
                TIMEOUT '500ms',
                NO_THROW
            )
            """;

        await using var command =
            new NpgsqlCommand(sql, connection);

        command.Parameters.AddWithValue(lsn);

        var result =
            await command.ExecuteScalarAsync(cancellationToken);

        return string.Equals(
            result?.ToString(),
            "success",
            StringComparison.OrdinalIgnoreCase);
    }
}

The service can then be called by the application layer instead of embedding replication-specific SQL in every API endpoint.

Why LSN-Based Consistency Is Useful

Without an LSN, an application generally has to guess whether the replica has caught up.

For example:

Wait 100 ms
   ↓
Query replica
   ↓
Maybe caught up

This is unreliable because replication delay can vary.

With an LSN:

Write transaction
      ↓
Capture target LSN
      ↓
WAIT FOR LSN
      ↓
Replica has reached target
      ↓
Read replica

The application waits for a specific replication position rather than an arbitrary amount of time.

This is the key benefit of the feature.

`WAIT FOR LSN` vs Existing Approaches

Approach	Consistency	Main Problem
Fixed application delay	Uncertain	Delay may be too short or unnecessarily long
Always read from primary	Strong for this path	Increases primary read load
Synchronous replication	Stronger coordination	Can increase write latency
Track replica LSN manually	Possible	More application complexity
WAIT FOR LSN	Explicit target	Requires PostgreSQL 19 and replica access

PostgreSQL already supports synchronous replication configurations where commits can wait for configured standbys, but that changes the behavior of writes globally or according to transaction settings. WAIT FOR LSN instead lets an application explicitly wait for a particular WAL position when it needs the guarantee.

Important Production Considerations

Do Not Assume Every Replica Is Interchangeable

If an application has multiple read replicas, the connection-routing layer needs to know which replica is being used.

Waiting for an LSN on one standby does not automatically guarantee that a different standby has replayed the same WAL position.

Handle Failover

PostgreSQL documents an important edge case: if a standby is promoted while waiting, WAIT FOR LSN can return not in recovery. Promotion creates a new timeline, so the application may need to reconsider whether the original LSN is still relevant.

Understand LSN Timeline Behavior

WAIT FOR LSN compares the numeric LSN and does not itself understand which timeline the WAL record belongs to.

Applications that need to distinguish timelines must validate that separately.

Prepare for Recovery Conflicts

A session waiting on a standby can be interrupted by recovery conflicts. Applications should therefore be prepared to retry or use a fallback strategy.

Common Mistakes

Using the Wrong LSN Function

The application should capture an LSN that represents the WAL generated by the relevant change.

For example:

SELECT pg_current_wal_insert_lsn();

PostgreSQL's documentation uses this function in its WAIT FOR LSN example because it identifies the WAL insertion position associated with the preceding modification.

Waiting on the Primary With a Standby Mode

This is incorrect:

WAIT FOR LSN '0/306EE20'
WITH (MODE 'standby_replay');

when executed on the primary.

standby_replay, standby_write, and standby_flush are standby-only modes.

Holding an Incompatible Transaction Snapshot

WAIT FOR must run as a top-level command. It cannot be executed from a function, procedure, or DO block, and it cannot be used while an active or registered snapshot must remain open, including transactions running above READ COMMITTED.

Using an Unlimited Wait

A replica can become unavailable or fall significantly behind. Production applications should normally define a timeout and have a clear fallback strategy.

Troubleshooting

Start by checking the replication state:

SELECT
    application_name,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn
FROM pg_stat_replication;

PostgreSQL exposes separate sent, written, flushed, and replayed LSN positions for standby connections. These values help identify where replication is currently behind.

If the target LSN is:

0/306EE20

but the replica's replay position is still behind that value, standby_replay will continue waiting until the target is reached or the timeout occurs.

Also verify that the connection used for WAIT FOR is actually connected to the intended standby.

Best Practices

Use WAIT FOR LSN only where read-your-writes consistency is actually required.
Capture the LSN immediately after the relevant write.
Wait using standby_replay when the subsequent read must see the change.
Use a timeout for production workloads.
Provide a fallback when the replica cannot catch up.
Keep write and read connection routing explicit.
Monitor replay lag using PostgreSQL replication statistics.
Handle replica promotion and failover carefully.
Do not assume that waiting on one replica synchronizes every replica.
Keep replication-specific logic inside a dedicated application service.

Advantages and Disadvantages

Advantages

Provides an explicit mechanism for read-your-writes consistency.
Avoids arbitrary sleep-based delays.
Allows applications to wait for replay, write, or flush depending on the requirement.
Supports configurable timeouts.
Can reduce unnecessary reads against the primary.
Fits naturally into applications already using asynchronous replication.

Disadvantages

Requires PostgreSQL 19.
Adds application-level replication awareness.
Waiting increases latency when a replica is behind.
Failover and timeline changes require careful handling.
A replica can still become unavailable while an application is waiting.
Connection routing becomes more important when multiple replicas exist.

Conclusion

PostgreSQL 19's WAIT FOR LSN provides a much cleaner mechanism for applications that need read-your-writes consistency with asynchronous replicas.

For a .NET application, the pattern is straightforward:

Write to primary
      ↓
Capture LSN
      ↓
Connect to intended replica
      ↓
WAIT FOR LSN
      ↓
Read from replica

The feature does not eliminate replication lag. Instead, it gives the application an explicit way to coordinate with that lag.

For systems where most reads can tolerate eventual consistency but specific user flows must immediately observe their own writes, this approach can provide a useful middle ground between always reading from the primary and enabling synchronous replication for every write. PostgreSQL 19's documentation specifically identifies this read-your-writes use case for standby_replay.

Understanding the Read-Your-Writes Problem

What Is WAIT FOR LSN?

Capturing the LSN After a Write

Implementing the Pattern in .NET

Adding a Timeout

Using NO_THROW for Controlled Handling

A More Production-Oriented .NET Service

Why LSN-Based Consistency Is Useful

WAIT FOR LSN vs Existing Approaches

Important Production Considerations

Do Not Assume Every Replica Is Interchangeable

Handle Failover

Understand LSN Timeline Behavior

Prepare for Recovery Conflicts

Common Mistakes

Using the Wrong LSN Function

Waiting on the Primary With a Standby Mode

Holding an Incompatible Transaction Snapshot

Using an Unlimited Wait

Troubleshooting

Best Practices

Advantages and Disadvantages

Advantages

Disadvantages

Conclusion

What Is `WAIT FOR LSN`?

Using `NO_THROW` for Controlled Handling

`WAIT FOR LSN` vs Existing Approaches