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

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

Scaling a Laravel application past a single database node almost always means introducing a replica. Laravel's database layer has first-class support for this, but the details around sticky reads, connection pooling, and transaction safety trip up even experienced engineers. This article walks through each layer concretely.

Configuring Read/Write Connections

Laravel accepts a read / write key inside any connection definition. Both keys accept an array of hosts, and the framework picks one at random per request.

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

Keys defined outside read/write are merged into both, so you only override what differs.

What sticky Actually Does

When sticky is true, Laravel records whether the write connection was used during the current request. If it was, all subsequent reads in that same request are also routed to the write host.

This prevents the classic race condition:

1. User submits a form → INSERT on the primary.
2. Redirect → SELECT on a replica that hasn't replicated yet.
3. User sees a blank page or stale data.

The flag is stored on the DatabaseManager instance, which is a singleton per request lifecycle. Under Octane (shared worker), you must reset it yourself — more on that below.

// Force a read from the write host explicitly when you need it
$user = DB::connection('pgsql')
    ->table('users')
    ->useWritePdo()
    ->find($id);

useWritePdo() is available on the query builder and bypasses the sticky logic entirely — useful in console commands where no write has occurred but you need fresh data.

Transactions Always Use the Write Connection

Laravel automatically routes all queries inside DB::transaction() to the write PDO. You don't need to think about this, but it's worth knowing so you don't add useWritePdo() calls inside transactions unnecessarily.

DB::transaction(function () use ($dto) {
    $order = Order::create($dto->toArray());
    // This SELECT also hits the write host — correct behaviour
    $inventory = Inventory::lockForUpdate()->find($dto->productId);
    $inventory->decrement('stock', $dto->quantity);
});

Connection Pooling: PgBouncer in Transaction Mode

PHP opens a new PDO connection per request. At scale this exhausts PostgreSQL's max_connections. PgBouncer in transaction mode multiplexes many PHP connections onto a small pool.

Gotcha: PgBouncer transaction mode breaks prepared statements and SET session variables. Laravel uses PDO prepared statements by default.

Disable them per connection:

'options' => [
    PDO::ATTR_EMULATE_PREPARES => true,
],

With ATTR_EMULATE_PREPARES, PDO sends plain SQL strings rather than server-side prepared statements, which is safe through a transaction-mode pooler.

For MySQL with ProxySQL, the same principle applies: ProxySQL rewrites queries to route SELECT to replicas and writes to the primary, but Laravel's own read/write config and ProxySQL can conflict. Pick one layer to own routing — usually ProxySQL at scale, with Laravel's read/write disabled (single host).

Sticky Reads Under Laravel Octane

Octane reuses the same application container across requests. The DatabaseManager singleton retains the $recordsModified flag between requests, meaning a write in request N can cause request N+1 to read from the primary unnecessarily.

Register a terminating callback to reset it:

// In a service provider
public function boot(): void
{
    if (app()->bound('octane')) {
        app('events')->listen(
            \Laravel\Octane\Events\RequestTerminated::class,
            function () {
                app('db')->forgetRecordModificationState();
            }
        );
    }
}

forgetRecordModificationState() was added in Laravel 10. On earlier versions, resolve the DatabaseManager and call recordsHaveBeenModified(false) directly.

Takeaways

• sticky => true prevents post-write stale reads within a single request; it is not a global setting.
• useWritePdo() on the query builder gives explicit control without relying on the sticky flag.
• Transactions always use the write PDO — no extra configuration needed.
• PgBouncer transaction mode requires PDO::ATTR_EMULATE_PREPARES => true to avoid prepared-statement errors.
• Under Octane, reset recordsHaveBeenModified on each RequestTerminated event to avoid sticky-read bleed between requests.
• Let one layer own replica routing: either Laravel's config or a proxy like ProxySQL, not both.