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

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

Laravel ships with first-class support for read/write connection splitting, but the defaults hide several sharp edges that only surface under replication lag or high-concurrency workloads. This article walks through the full picture: configuration, sticky-read semantics, and layering a connection pooler without breaking transactions.

Configuring Read/Write Connections

In config/database.php, any connection can declare read and write keys. Laravel merges the top-level keys into both, so you only override what differs:

'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_HOST', '10.0.1.10'),
    ],
    'sticky' => true,
    'database' => env('DB_DATABASE', 'app'),
    'username' => env('DB_USERNAME', 'app'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'schema' => 'public',
],

When multiple hosts are listed under read, Laravel picks one at random per request — a simple client-side load balancer. All SELECT statements route to a read host; everything else goes to write.

The Sticky Read Problem

Without 'sticky' => true, a write followed immediately by a read in the same request can return stale data because the read hits a replica that hasn't caught up yet. The sticky flag tells Laravel: if this request has already used the write connection, keep using it for reads too.

// Without sticky: this can return the pre-update row
$user = User::find(1);
$user->update(['name' => 'Alice']);
$fresh = User::find(1); // may hit replica — stale!

// With sticky => true: the second query also uses the write connection

Sticky reads are scoped to the current request lifecycle. They do not persist across requests, so read replicas still get the bulk of your SELECT traffic.

When to disable sticky: Long-running queue workers that perform a write and then immediately read back the result should either use DB::connection('write') explicitly or rely on sticky. Disabling sticky globally to squeeze more replica usage is a common source of subtle race-condition bugs.

Forcing a Connection Explicitly

For critical reads after a write — think payment confirmation pages — be explicit:

$order = DB::connection('pgsql::write')
    ->table('orders')
    ->where('id', $orderId)
    ->first();

Or use the Eloquent onWriteConnection() method available on query builders:

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

This is clearer than relying on sticky and documents intent at the call site.

Connection Pooling with PgBouncer

Laravel opens a new PDO connection per worker process. Under Octane or high-concurrency FPM, this can exhaust PostgreSQL's max_connections quickly. PgBouncer in transaction mode sits between Laravel and Postgres and multiplexes connections.

Critical caveat: PgBouncer transaction mode breaks anything that relies on session-level state — SET LOCAL, advisory locks, LISTEN/NOTIFY, and prepared statements. Disable prepared statements in Laravel:

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

With ATTR_EMULATE_PREPARES, PDO sends plain SQL rather than using the PostgreSQL wire-protocol prepare/execute cycle, which is incompatible with PgBouncer transaction mode.

For MySQL, ProxySQL fills the same role. It also supports query routing rules, so you can push SELECT statements to replicas at the proxy layer rather than relying on Laravel's config — useful when you want to centralise routing logic outside the application.

Transactions Always Use the Write Connection

Laravel automatically routes all queries inside DB::transaction() to the write connection, regardless of the sticky flag. This is correct behavior — never wrap a read-replica query in a transaction expecting it to stay on the replica.

DB::transaction(function () {
    // Both queries hit the write connection
    $balance = Account::lockForUpdate()->find(42);
    $balance->decrement('amount', 100);
});

Key Takeaways

• Enable sticky => true in production to avoid replication-lag read-after-write bugs.
• Use onWriteConnection() or DB::connection('pgsql::write') when you need a guaranteed fresh read.
• PgBouncer transaction mode requires PDO::ATTR_EMULATE_PREPARES => true — missing this causes cryptic errors.
• Laravel always routes transactions to the write connection; don't fight it.
• ProxySQL can centralise read/write routing at the infrastructure layer, reducing per-app config drift across services.