Read/Write Splitting and Sticky Reads in Laravel: A Production Guide

Read/Write Splitting and Sticky Reads in Laravel

Most Laravel apps start with a single database connection and graduate to a replica setup only after pain. When you finally add a read replica, a subtle class of bugs appears: you write a record, immediately redirect and query it, and get a 404 — because the replica hasn't caught up yet. Laravel's sticky option exists precisely for this, but its behaviour is often misunderstood.

How Laravel Splits Connections

Laravel's DatabaseManager inspects each query. SELECT statements are routed to a randomly chosen read host; everything else goes to write. The configuration is straightforward:

// config/database.php
'pgsql' => [
    'driver' => 'pgsql',
    'read' => [
        ['host' => env('DB_READ_HOST_1', '10.0.1.2')],
        ['host' => env('DB_READ_HOST_2', '10.0.1.3')],
    ],
    'write' => [
        'host' => env('DB_WRITE_HOST', '10.0.1.1'),
    ],
    'sticky' => true,
    'database' => env('DB_DATABASE', 'app'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'charset' => 'utf8',
    'prefix' => '',
    'schema' => 'public',
],

With sticky => true, once a write occurs during a request, all subsequent reads in that same request are routed to the write connection. This prevents the classic post-write redirect returning stale data.

The Sticky Read Lifecycle

The flag lives on the Connection instance for the duration of the request (or Octane worker cycle — more on that shortly):

// Illuminate\Database\Connection
public function recordsHaveBeenModified($value = true)
{
    if (! $this->recordsModified) {
        $this->recordsModified = $value;
    }
}

Every INSERT/UPDATE/DELETE calls recordsHaveBeenModified(). The DatabaseManager::connection() method checks this flag when deciding which PDO instance to return for the next SELECT.

Important: sticky is per-request, not per-session. If a user writes in request A and reads in request B (a different process), they may still hit the replica. For truly critical reads after writes across requests, consider forcing the write connection explicitly:

$user = DB::connection('pgsql')->selectOne(
    'SELECT * FROM users WHERE id = ?', [$id]
);
// or via Eloquent:
$user = User::on('pgsql')->find($id); // always write connection

Forcing the Write Connection Selectively

Eloquent models expose ->onWriteConnection() for exactly this case:

$order = Order::onWriteConnection()->find($orderId);

For a broader scope, wrap a block:

DB::usingConnection('pgsql_write', function () use ($orderId) {
    return Order::find($orderId);
});

Connection Pooling Considerations

Laravel itself does not pool connections — each PHP-FPM worker holds one persistent connection per configured database. With 50 workers × 2 connections (read + write), you're already at 100 open connections. PgBouncer (for PostgreSQL) or ProxySQL (for MySQL) sit in front of your replicas and multiplex these:

App workers → PgBouncer (transaction mode) → Primary + Replicas

Use transaction pooling mode in PgBouncer for maximum efficiency, but be aware it resets session-level settings (e.g., SET search_path) on every transaction. If your app relies on session state, use statement mode instead.

Octane and Sticky State Leakage

Under Laravel Octane, workers are long-lived. The recordsModified flag is not automatically reset between requests unless you explicitly reset the connection state. Add a middleware or use Octane's RequestHandled event:

// In AppServiceProvider::boot()
app('events')->listen(
    \Laravel\Octane\Events\RequestHandled::class,
    function () {
        DB::connection()->forgetRecordModificationState();
    }
);

Without this, the first write in worker boot will make every subsequent request in that worker use the write connection — defeating the entire replica strategy.

Takeaways

• sticky => true routes post-write reads to the write host within the same request only.
• Cross-request consistency requires explicit onWriteConnection() or application-level logic.
• PgBouncer/ProxySQL are essential for connection count management at scale.
• Octane workers must reset recordsModified between requests to avoid sticky state leakage.
• Monitor replica lag with pg_stat_replication or SHOW SLAVE STATUS and alert when it exceeds your acceptable threshold.