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 read replica. Laravel ships with first-class support for this, but the details matter: get them wrong and you'll chase phantom bugs caused by replication lag or exhaust your connection limit under load.

Configuring Read/Write Connections

Laravel's config/database.php accepts a read and write key inside any connection. Both are merged with the top-level connection config, 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 at zero infrastructure cost.

The Sticky Option and Why It Exists

Replication is asynchronous. If you write a record and immediately read it back on a replica, the replica may not have caught up yet. Laravel's sticky option solves this: once a write connection is used during a request, all subsequent reads in that same request are also routed to the write connection.

// Without sticky=true this SELECT might miss the just-inserted row
DB::table('orders')->insert(['user_id' => $userId, 'total' => 9900]);
$order = DB::table('orders')->where('user_id', $userId)->latest()->first();

With sticky => true, the second query goes to the primary. Without it, you're gambling on replication lag.

When to disable sticky: Background jobs that only read data, reporting queries, and read-heavy API endpoints that don't write. You can force a read connection explicitly:

$stats = DB::connection('pgsql::read')->table('events')->count();
// Or use the query builder helper:
$stats = DB::table('events')->useReadPdo()->count();

useReadPdo() is available on the query builder and bypasses the sticky logic intentionally.

Connection Pooling: PgBouncer and RDS Proxy

PHP-FPM and Octane workers open a new PDO connection per worker (or per request under FPM). At scale this exhausts max_connections on PostgreSQL fast.

PgBouncer in transaction mode is the standard fix for PostgreSQL:

; pgbouncer.ini
[databases]
app = host=10.0.1.10 dbname=app

[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 25

Point Laravel's write host at PgBouncer's address. Each transaction borrows a server connection and returns it immediately — 2 000 PHP workers can share 25 real Postgres connections.

Caveats with transaction-mode pooling:

• SET statements and advisory locks are connection-scoped and will not survive across transactions.
• Laravel's DB::statement('SET search_path TO tenant_schema') pattern breaks. Use the search_path DSN option or schema-prefix your tables instead.
• Prepared statements must be disabled: set 'options' => ['PDO::ATTR_EMULATE_PREPARES' => true] in your connection config, or configure PgBouncer's server_reset_query.

RDS Proxy handles MySQL and PostgreSQL and is transparent to Laravel — point your host at the proxy endpoint and it manages the pool server-side. It also handles IAM authentication and secret rotation without app changes.

Forcing a Specific Connection in Eloquent

class ReportQuery
{
    public function handle(): Collection
    {
        return Order::on('pgsql') // uses read replica via read/write split
            ->useReadPdo()
            ->where('created_at', '>=', now()->subDays(30))
            ->get();
    }
}

For models that should always write to primary regardless of sticky state:

class AuditLog extends Model
{
    protected $connection = 'pgsql'; // write connection explicitly
}

Takeaways

• Enable sticky => true whenever a request can write then immediately read its own data.
• Use useReadPdo() on reporting queries to explicitly bypass sticky and reduce primary load.
• PgBouncer in transaction mode dramatically reduces Postgres connection count but breaks session-level features — audit your app before enabling it.
• RDS Proxy is the managed alternative; it's transparent to Laravel but adds latency on cold connections.
• List multiple replica hosts in the read array for free client-side load balancing without a proxy.