User saves profile, refresh shows old data: read replica lag broke read-your-own-writes

Support tickets started with "the settings page does not save". Users changed their display name or notification email, clicked Save, landed back on the settings page, and saw the old values. Many saved again, some three or four times. When they reloaded a few seconds later, the new values were there. It happened to maybe one save in twenty, mostly in the European afternoon.

Our logs showed the update succeeding every time:

[2026-07-08 14:12:03] production.INFO: profile.updated {"user_id":184022,"fields":["display_name"],"ms":7}
[2026-07-08 14:12:03] production.INFO: GET /settings/profile 200 {"user_id":184022,"db":"pgsql::read","ms":11}

The db field told us where the GET read from: the read connection, which points at the replicas.

Reproducing it

On staging we added artificial lag by pausing replay on the replica, which is a quick way to simulate a busy standby:

-- on the staging replica
SELECT pg_wal_replay_pause();

Then: save the profile, follow the redirect, and the page shows the old name every time. Resume with SELECT pg_wal_replay_resume(); and the new value appears.

What we ruled out

• Caching. We checked the response headers and the application cache. The settings page is not cached, and the stale value came straight from SQL.
• Failed transaction. The update ran on the primary and committed; querying the primary directly showed the new value immediately.
• Laravel sticky misconfiguration. It was enabled. It just does not do what we assumed.

Root cause

Two things combined. First, our replicas were lagging. A nightly-turned-hourly import job was updating millions of rows during business hours, and replay lag on the two replicas reached 2 to 6 seconds at peak.

Second, the save flow spans two HTTP requests. Laravel's sticky option makes reads go to the write connection only after a write in the same request. The POST wrote to the primary and returned a redirect. The browser then issued a fresh GET, which started with a clean connection state, so its read went to a replica that had not yet replayed the update.

What is happening under the hood

PostgreSQL streaming replication is asynchronous by default. The primary commits, writes WAL, and sends it to standbys, which replay it in order. A standby is always a consistent snapshot of the past, but it can be behind by milliseconds or, under heavy write load or long conflicting queries, by seconds or more.

Applications that split reads and writes get a weaker guarantee than they usually assume. "Read your own writes" means a user always sees their own changes. A primary gives it for free. A replica gives it only if the read happens after the replica has replayed the user's write.

Laravel's read/write split works per connection object, and the sticky flag is tracked on that object for the lifetime of the request:

'pgsql' => [
    'read' => ['host' => [env('DB_READ_HOST_1'), env('DB_READ_HOST_2')]],
    'write' => ['host' => [env('DB_WRITE_HOST')]],
    'sticky' => true,
    // ...
],

The documentation says it plainly: sticky applies to the current request cycle. A redirect is a new request.

Environment

Minimal reproduction

A route pair that shows it with any lagging replica:

Route::post('/demo/name', function (Request $request) {
    $request->user()->update(['display_name' => $request->string('name')]);
    return redirect('/demo/name');
});

Route::get('/demo/name', function (Request $request) {
    // runs on the read connection, new request, sticky does not apply
    return DB::table('users')->where('id', $request->user()->id)->value('display_name');
});

Who is affected

Any stack that routes reads to async replicas: Laravel, Rails multiple databases, Django database routers, ProxySQL or Pgpool-II read routing, and managed "reader endpoints". The window is the replica lag, so it only shows up under load, which is exactly when users are most likely to notice.

The fix

We fixed both sides: route a user's reads to the primary until a replica has caught up with their last write, and reduce the lag itself.

1. Remember the WAL position of the user's last write. After a request that wrote to the primary, we store the current WAL LSN in the session:

final class RememberWriteLsn
{
    public function handle(Request $request, Closure $next): Response
    {
        $response = $next($request);

        $conn = DB::connection('pgsql');
        if ($conn->hasModifiedRecords() && $request->hasSession()) {
            $lsn = $conn->selectOne('SELECT pg_current_wal_lsn()::text AS lsn', [], false)->lsn;
            $request->session()->put('db.last_write_lsn', $lsn);
        }

        return $response;
    }
}

The third argument false to selectOne tells Laravel to use the write connection, so we read the primary's position.

2. On later requests, use the primary until a replica has replayed that position.

final class ReadYourWrites
{
    public function handle(Request $request, Closure $next): Response
    {
        $lsn = $request->session()->get('db.last_write_lsn');

        if ($lsn !== null) {
            $caughtUp = DB::connection('pgsql')->selectOne(
                'SELECT pg_last_wal_replay_lsn() >= ?::pg_lsn AS ok', [$lsn]
            )->ok;

            if ($caughtUp) {
                $request->session()->forget('db.last_write_lsn');
            } else {
                DB::connection('pgsql')->useWriteConnectionWhenReading();
            }
        }

        return $next($request);
    }
}

The check runs on the read connection, so it asks the same replica that would serve the request. If it is behind, the whole request reads from the primary. Once a replica catches up, the session key is dropped and reads go back to replicas.

Both middleware classes are registered for the web group in bootstrap/app.php.

3. Reduce the lag. The import job now commits in batches of 5,000 rows with a short pause between batches, and it runs from 02:00 to 05:00 instead of hourly. Peak replay lag dropped from 6 s to under 300 ms.

Alternatives and trade-offs

• Time-based stickiness. Store "last write at" and use the primary for N seconds. Simple, works with any database, but you must guess N, and it fails exactly when lag is worst.
• Synchronous replication with synchronous_commit = remote_apply. Guarantees the standby has applied the commit before the primary acknowledges it. Correct, but every write waits for a replica, and a slow replica slows all writes. We kept it out.
• Read critical pages from the primary always. Fine for a few low-traffic pages such as settings; it does not scale to every page.
• Return the saved data in the POST response. Avoids the second read altogether for SPA-style forms, but does not help other pages that show the same data.

How to test the fix

A feature test cannot reproduce real lag, but it can check the routing decision. In the test environment there is no replica, and pg_last_wal_replay_lsn() returns NULL on a server that is not in recovery, so the comparison is NULL and the middleware must fall back to the primary and keep the session key. That is also the safe default in production if a read host is ever pointed at a primary by mistake.

public function test_pending_write_lsn_keeps_reads_on_primary(): void
{
    $user = User::factory()->create();

    $this->withSession(['db.last_write_lsn' => 'FFFFFFFF/FFFFFFFF'])
        ->actingAs($user)
        ->get('/settings/profile')
        ->assertOk()
        ->assertSessionHas('db.last_write_lsn');
}

php artisan test --filter=ReadYourWrites

End to end, we repeated the staging check with pg_wal_replay_pause(): the page now shows the new value after save, and log lines show db: pgsql::write for that GET.

Confirming in production

We track lag directly on each replica and from the primary's point of view:

-- on a replica
SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;

-- on the primary
SELECT application_name, client_addr, write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

Note that pg_last_xact_replay_timestamp() grows during idle periods with no writes, so we alert on replay_lag from pg_stat_replication instead.

After the deploy, "settings not saved" tickets dropped to zero within a week. The share of web requests that fell back to the primary is about 1.5 percent, which the primary handles easily. A counter on the middleware decision is in our metrics dashboard, so a rising fallback rate warns us that replicas are lagging.

Regression guard

• Alert when replay_lag exceeds 2 seconds for 5 minutes on any replica.
• The middleware test runs in CI.
• Bulk jobs must declare a batch size and commit interval in review; unbounded single-transaction updates are rejected.

Security implications

Stale reads can be a security problem, not just a UX one. If a user changes their password, revokes an API token, or an admin disables an account, a lagging replica can still serve the old state for a few seconds. We audited those paths: authentication and authorization checks already read from the primary by using ->useWritePdo() on the relevant queries, and we added the same to token revocation lookups. The LSN value stored in the session is not sensitive and is only compared server side as a typed pg_lsn, so there is no injection risk.

Prevention checklist

• Assume replicas lag, and decide per flow whether stale reads are acceptable.
• Remember that framework "sticky" options usually last one request only.
• Route a user's reads to the primary until a replica has replayed their last write.
• Read security-critical state, such as sessions, tokens and permissions, from the primary.
• Monitor replay_lag and throttle bulk writes during business hours.

References

• Laravel 12 docs, Database read and write connections: https://laravel.com/docs/12.x/database
• PostgreSQL 17 docs, Hot Standby: https://www.postgresql.org/docs/17/hot-standby.html
• PostgreSQL 17 docs, System Administration Functions (WAL and recovery): https://www.postgresql.org/docs/17/functions-admin.html
• PostgreSQL 17 docs, pg_stat_replication: https://www.postgresql.org/docs/17/monitoring-stats.html
• PostgreSQL 17 docs, synchronous_commit: https://www.postgresql.org/docs/17/runtime-config-wal.html