Read/Write Splitting, Connection Pooling, and Sticky Reads in Laravel

Read/Write Splitting, Connection Pooling, and Sticky Reads in Laravel

Laravel's database layer has first-class support for read/write splitting, but the defaults hide several sharp edges that only appear under production load. This article walks through the configuration mechanics, the sticky-read guarantee, and how to safely introduce a connection pooler without breaking transactions or session state.

Configuring a Read Replica

The read / write keys in config/database.php let you declare separate hosts while inheriting the rest of the connection config:

'mysql' => [
    'driver' => 'mysql',
    'read' => [
        'host' => [
            env('DB_READ_HOST_1', '10.0.1.11'),
            env('DB_READ_HOST_2', '10.0.1.12'),
        ],
    ],
    'write' => [
        'host' => env('DB_HOST', '10.0.1.10'),
    ],
    'sticky' => true,
    'database' => env('DB_DATABASE', 'app'),
    'username' => env('DB_USERNAME', 'app'),
    'password' => env('DB_PASSWORD'),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
],

Laravel randomly picks one host from the read array per request. All SELECT statements that are not inside an explicit transaction route to the read connection; everything else goes to write.

What sticky Actually Does

When sticky is true, any write performed during the current request causes all subsequent reads in the same request lifecycle to be redirected to the write connection. This prevents the classic "I just inserted a row and immediately can't find it" bug caused by replication lag.

The flag is tracked on the ConnectionInterface instance held by the DatabaseManager. It resets between requests because the manager is resolved fresh from the container on each HTTP request — but not in long-lived Octane workers. Under Octane you must reset it manually or accept that a write in request N keeps reads on the primary for the remainder of that worker's life.

// Octane: reset sticky state between requests
app('db')->purge(); // drops both connections and resets sticky flag

A lighter alternative is to call DB::reconnect() only when you know a write occurred, but purge() is safer.

Forcing a Connection Explicitly

Sometimes you need to bypass the automatic routing — for example, reading a freshly-committed row in a background job that runs milliseconds after the HTTP response:

$user = User::on('mysql::write')->find($id);

The on() method accepts the connection name. The ::write suffix is a Laravel convention that resolves to the write PDO instance of the named connection.

Layering PgBouncer or ProxySQL

Connection poolers sit between your app servers and the database. The key concern is transaction-mode pooling: PgBouncer in transaction mode reuses a server connection after each transaction, which means session-level state (prepared statements, SET LOCAL, advisory locks) does not survive across queries.

Disable prepared statements when using PgBouncer in transaction mode:

// config/database.php — PostgreSQL
'pgsql' => [
    ...
    'options' => [
        PDO::ATTR_EMULATE_PREPARES => true,
    ],
],

For MySQL behind ProxySQL, set wait_timeout on the ProxySQL hostgroup to a value lower than MySQL's own wait_timeout to avoid "MySQL server has gone away" errors on idle connections.

Transactions Always Use the Write Connection

Laravel wraps DB::transaction() and DB::beginTransaction() in the write connection automatically. Reads inside the transaction callback also go to write, regardless of the sticky flag. This is correct behaviour — you need read-your-own-writes consistency inside a transaction.

DB::transaction(function () {
    $order = Order::create([...]);      // write
    $items = $order->items()->get();    // also write — correct
});

Monitoring Replication Lag

No amount of sticky-read configuration helps if your replica is 30 seconds behind. Expose replication lag as a metric (via SHOW SLAVE STATUS or pg_stat_replication) and alert on it. Consider routing reads to the primary automatically when lag exceeds a threshold using a custom Connection resolver.

Takeaways

• sticky => true prevents read-after-write anomalies within a single request but resets per request — not per Octane worker.
• Use Model::on('mysql::write') to force primary reads in jobs or event listeners that run close to a write.
• Disable PDO prepared statements (ATTR_EMULATE_PREPARES) when PgBouncer runs in transaction mode.
• Transactions always use the write connection; reads inside them do too.
• Monitor replication lag independently — application-level sticky reads cannot compensate for a lagging replica.