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

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

Scaling a Laravel application's database layer is rarely about raw query optimisation alone. Once you add a read replica, you immediately inherit a class of subtle bugs caused by replication lag. Laravel ships with first-class support for read/write connections, but the defaults can surprise you in production.

Configuring Read/Write Connections

Laravel's config/database.php accepts a read and write key inside any connection definition. Both accept an array of hosts, and Laravel picks one at random per request.

'mysql' => [
    'driver' => 'mysql',
    'read' => [
        'host' => [
            env('DB_READ_HOST_1', '10.0.1.10'),
            env('DB_READ_HOST_2', '10.0.1.11'),
        ],
    ],
    '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' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
],

Every SELECT goes to a random read host; every INSERT, UPDATE, DELETE, and DDL goes to the write host.

The sticky Option and Why It Exists

Without sticky, a user who just submitted a form might immediately read their own write from a replica that hasn't caught up yet — a classic "I just saved this, where did it go?" bug.

When sticky => true, Laravel records whether the write connection was used during the current request. If it was, all subsequent reads in that same request are routed to the write host instead of a replica.

// Internally, Connection::$recordsModified drives this.
// After any write, Laravel sets it to true and useReadPdo() returns false.

This is a per-request flag, so it resets on the next HTTP request. It is not a session-level concept — long-running queue workers or CLI commands do not benefit from it automatically.

Forcing a read from the write host explicitly:

$user = DB::connection('mysql::write')
    ->table('users')
    ->find($id);

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

Use this sparingly — it defeats the purpose of replicas — but it is the right tool after a critical write in a job.

Connection Pooling with PgBouncer

Laravel opens a new PDO connection per worker process. Under Octane or high-concurrency FPM, this means hundreds of connections hitting your database simultaneously. PgBouncer (PostgreSQL) and ProxySQL (MySQL) sit in front of the database and multiplex many application connections onto a smaller pool.

PgBouncer in transaction-mode is the most efficient but breaks SET statements, advisory locks, and prepared statements. Configure Laravel to disable server-side prepared statements:

'pgsql' => [
    'driver' => 'pgsql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '6432'), // PgBouncer port
    'options' => [
        PDO::ATTR_EMULATE_PREPARES => true, // avoids named prepared statements
    ],
    // ...
],

Alternatively, run PgBouncer in session mode if you rely on advisory locks or LISTEN/NOTIFY — you lose some multiplexing efficiency but retain full feature compatibility.

Handling Replication Lag in Jobs

Queue workers are long-lived processes. sticky does not help them. After a job writes data and then reads it back, it may hit a stale replica.

class ProcessOrderJob implements ShouldQueue
{
    public function handle(): void
    {
        // Write
        $order = Order::create([...]);

        // Explicitly read from write host to avoid lag
        $fresh = Order::on('mysql::write')->find($order->id);

        // Or simply use the model already in memory:
        $fresh = $order->refresh(); // still hits read by default!
        // Better:
        DB::connection()->recordsModified(); // check flag — not reliable in jobs
    }
}

The cleanest pattern: pass the already-hydrated model or its ID into subsequent steps rather than re-querying immediately after a write.

Takeaways

• Enable sticky => true to prevent users from reading stale data after their own writes within the same HTTP request.
• Use DB::connection('mysql::write') explicitly in jobs and CLI commands where sticky has no effect.
• Disable PDO prepared statements (ATTR_EMULATE_PREPARES) when routing through PgBouncer in transaction mode.
• Prefer session-mode PgBouncer if your application uses advisory locks, LISTEN/NOTIFY, or pg_temp schemas.
• Avoid re-querying immediately after a write in queue workers; pass hydrated models or use the write connection explicitly.