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. At some point you add a read replica, and suddenly a whole class of subtle bugs appears: a user creates a record, gets redirected, and sees an empty list because the replica hasn't caught up yet. Understanding how Laravel's database manager handles this — and where it can silently betray you — is essential before you put a replica in front of real traffic.

How Laravel Splits Reads and Writes

Laravel's DatabaseManager accepts a read / write key inside any connection config. Each key accepts an array of hosts, and Laravel 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',
],

Every SELECT goes to a read host; every INSERT, UPDATE, DELETE, and DDL statement goes to the write host. The split happens inside Illuminate\Database\Connection::select() and statement() — not at the Eloquent layer, so raw queries respect it too.

The sticky Option and Why It Matters

When sticky is true, Laravel records that a write occurred during the current request. Any subsequent read in the same request lifecycle is then routed to the write connection instead of a replica.

// Internally, Connection tracks this flag:
protected $recordsModified = false;

// After any write:
$this->recordsModified = true;

// On the next select, if sticky and recordsModified:
return $this->getWritePdo();

This prevents the classic post-write stale-read bug within a single HTTP request. It does not help across requests (e.g., a redirect after a form POST) — for that you need application-level logic or a short-circuit cache.

Forcing a Connection Explicitly

Sometimes you need deterministic routing regardless of sticky:

// Force read replica
$users = DB::connection('pgsql')->table('users')
    ->useReadPdo()
    ->get();

// Force primary
$user = User::on('pgsql')->find($id); // always write host

// Or via the query builder:
DB::table('orders')->useWritePdo()->where('id', $id)->first();

useReadPdo() and useWritePdo() are available on the query builder and let you override sticky behaviour for a specific query.

Connection Pooling: PgBouncer and ProxySQL

Laravel opens a new PDO connection per worker process (or per request under FPM). At scale this exhausts max_connections on PostgreSQL quickly. PgBouncer in transaction pooling mode is the standard fix for Postgres.

Critical caveat: transaction pooling breaks any feature that relies on session-level state — SET LOCAL, advisory locks, pg_temp tables, and PREPARE statements. Laravel's DB::transaction() is safe because PgBouncer keeps the same backend connection for the duration of a transaction block. Named prepared statements are not safe; disable them:

// config/database.php — disable server-side prepared statements
'pgsql' => [
    // ...
    'options' => [
        PDO::ATTR_EMULATE_PREPARES => true,
    ],
],

For MySQL, ProxySQL can split reads and writes at the proxy layer based on query rules, which means you can point Laravel at a single host and let ProxySQL handle routing. This simplifies config but adds an infrastructure dependency.

Detecting Replica Lag in Application Code

If your workload is write-heavy and replica lag is unpredictable, consider a health-check approach:

// A simple lag guard using a heartbeat table
public function replicaIsAcceptable(): bool
{
    $lag = DB::connection('pgsql')
        ->table('replication_heartbeat')
        ->useReadPdo()
        ->value('lag_seconds');

    return $lag !== null && $lag < 2;
}

Update replication_heartbeat from a scheduled job every second on the primary. If lag exceeds your threshold, route critical reads to the write host for that request.

Takeaways

• Laravel's read/write config splits connections automatically; sticky prevents stale reads within a single request but not across requests.
• Use useReadPdo() / useWritePdo() for explicit per-query routing.
• PgBouncer transaction pooling requires PDO::ATTR_EMULATE_PREPARES => true to avoid named prepared statement errors.
• ProxySQL can absorb the routing logic entirely, but adds a network hop and operational complexity.
• Monitor replica lag actively; don't assume sticky is a complete solution for post-redirect reads.