Laravel Eloquent Read/Write Connection Configuration

Problem statement: you added replica hosts to Laravel’s database config, the app still hammers the primary, and nothing in the framework tells you why.

Laravel’s read/write split is a small amount of configuration with two behaviours that dominate its outcome: the sticky flag and transaction handling. Both default in directions that send far more traffic to the primary than most teams expect. This page covers the configuration and the verification. The general pattern is described in ORM middleware for automatic query routing.

Symptom Identification

  • Replica hosts are configured and pg_stat_activity on them shows few or no application connections.
  • The primary’s SELECT volume is essentially unchanged after adding replicas.
  • Read-after-write works perfectly — suspiciously so — because nothing is going to a replica at all.
  • Some endpoints use replicas and others never do, with no obvious pattern until you notice which ones write.
  • Queue workers behave differently from web requests for the same code path.

The third point is the one to internalise: a read/write split that never produces a stale read is a read/write split that is not being used.

Root Cause Analysis

Laravel’s split is per statement, but two rules override it.

The sticky rule. With 'sticky' => true, once any write has been executed during the current request lifecycle, the connection records that fact and routes every subsequent read to the write connection. The intent is read-your-writes; the granularity is the whole request. A request that writes once at the start sends every later read to the primary, however unrelated those reads are.

The transaction rule. Everything inside DB::transaction() — or between beginTransaction and commit — goes to the write connection unconditionally, sticky or not. This is correct: a transaction split across nodes is not a transaction. But it means any code path that wraps reads in an unnecessary transaction moves them all to the primary.

Together these explain nearly every “the replicas get no traffic” report in a Laravel application. Neither is a bug; both are conservative defaults that trade capacity for safety, and neither is visible without measurement.

A third, quieter factor is connection resolution. Laravel picks one host at random from the read array when it first needs a read connection, and keeps it for the lifetime of that connection. Under PHP-FPM, where processes are long-lived and each handles many requests, this produces the same distribution skew that DNS round robin produces elsewhere.

The same request under three Laravel routing configurations A request performing three reads, one write and four more reads. With sticky true, the first three reads use a replica and everything after the write goes to the primary. With sticky false, only the write goes to the primary and all seven reads use a replica, at the cost of a possible stale read immediately after the write. Wrapped in a transaction, all eight statements go to the primary regardless of the sticky setting. one request: R R R W R R R R (R = read, W = write) sticky = true the default in most setups 5 of 8 on the primary sticky = false handle read-after-write explicitly 1 of 8 on the primary inside a transaction sticky is irrelevant here 8 of 8 on the primary replica primary (read) primary (write)

Step-by-Step Resolution

Step 1 — Declare read and write hosts

php
// config/database.php
'pgsql' => [
    'driver' => 'pgsql',

    // Replicas. Laravel picks ONE at random per connection and keeps it,
    // so under PHP-FPM the distribution is decided at process start.
    'read' => [
        'host' => [
            env('DB_READ_HOST_1', 'replica-1.internal'),
            env('DB_READ_HOST_2', 'replica-2.internal'),
        ],
    ],

    'write' => [
        'host' => [env('DB_WRITE_HOST', 'primary.internal')],
    ],

    // See step 2 — this single flag decides most of your replica utilisation.
    'sticky' => env('DB_STICKY', false),

    // Everything below is shared by both connections.
    'port'     => env('DB_PORT', '5432'),
    'database' => env('DB_DATABASE'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'charset'  => 'utf8',
    'sslmode'  => 'require',
],

Inline verification: php artisan tinker then DB::connection('pgsql')->select('select inet_server_addr()') returns a replica address, while DB::connection('pgsql::write')->select(...) returns the primary.

Step 2 — Choose sticky deliberately

sticky => true guarantees read-your-writes for the rest of the request and costs you every read that follows any write. sticky => false keeps reads on replicas and requires you to handle the read-after-write cases explicitly:

php
// With sticky = false, mark ONLY the reads that must see the just-written row.
$user->update(['name' => $request->name]);

// This specific read must reflect the update — force it to the primary.
$fresh = User::onWriteConnection()->find($user->id);

// Everything else in this request still uses replicas.
$recent = Order::where('user_id', $user->id)->latest()->take(20)->get();

Inline verification: with sticky = false, a request that writes then reads shows one primary connection and the rest on replicas in pg_stat_activity.

Step 3 — Verify where each query actually ran

php
// tests/Feature/RoutingTest.php — assert against the SERVER, not the config.
public function test_reads_use_a_replica_after_a_write_when_not_sticky(): void
{
    config(['database.connections.pgsql.sticky' => false]);

    DB::table('users')->where('id', 1)->update(['seen_at' => now()]);

    $onReplica = DB::selectOne('SELECT pg_is_in_recovery() AS r')->r;
    $this->assertTrue($onReplica, 'read after write did not reach a replica');
}

public function test_transaction_bodies_stay_on_the_primary(): void
{
    DB::transaction(function () {
        $onReplica = DB::selectOne('SELECT pg_is_in_recovery() AS r')->r;
        $this->assertFalse($onReplica, 'a transaction body reached a replica');
    });
}

Inline verification: both tests pass against a real primary/standby pair, as described in writing integration tests that assert query routing.

Step 4 — Handle queue workers

php
// A long-running worker keeps the connection between jobs, so the recorded
// write state must be reset per job or stickiness leaks across jobs.
// Laravel's queue worker does this via the JobProcessing event; verify it.
Queue::before(function (JobProcessing $event) {
    DB::connection('pgsql')->recordsHaveBeenModified(false);
});
bash
# Confirm workers are not pinned: replica connections should exist from
# worker hosts, not only from web hosts.
psql -h replica-1.internal -c "
  SELECT client_addr, count(*) FROM pg_stat_activity
   WHERE backend_type='client backend' GROUP BY 1 ORDER BY 2 DESC"

Inline verification: worker host addresses appear in the replica’s connection list.

Step 5 — Measure the replica share

sql
-- The number that tells you whether the split is delivering capacity.
-- Run on the primary and on each replica, then compute the ratio.
SELECT pg_is_in_recovery() AS is_replica,
       sum(tup_returned)   AS tuples_returned
  FROM pg_stat_database WHERE datname = current_database()
 GROUP BY 1;

Inline verification: replica tuples returned are a substantial majority for a read-heavy application. If they are not, the cause is almost certainly sticky or an unnecessary transaction.

Configuration Snippet

php
// config/database.php — a configuration that gets the capacity and keeps
// the correctness, by scoping the read-your-writes guarantee.
'pgsql' => [
    'driver' => 'pgsql',
    'read'   => ['host' => explode(',', env('DB_READ_HOSTS'))],
    'write'  => ['host' => [env('DB_WRITE_HOST')]],

    // false: reads stay on replicas after a write. Read-after-write is
    // handled per query with onWriteConnection(), which is far cheaper than
    // pinning the whole request.
    'sticky' => false,

    'port' => env('DB_PORT', '5432'),
    'database' => env('DB_DATABASE'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'sslmode'  => 'require',

    // PDO options apply to BOTH connections. A short connect timeout keeps
    // a dead replica from blocking a request for the OS default.
    'options' => [
        PDO::ATTR_TIMEOUT => 3,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ],
],
php
// app/Providers/AppServiceProvider.php — make routing observable in production.
DB::listen(function (QueryExecuted $query) {
    // $query->connectionName is 'pgsql' for reads and 'pgsql::write' for
    // writes and sticky-pinned reads, which is exactly the split you want
    // to graph. Sample rather than logging every query.
    if (random_int(1, 100) === 1) {
        Log::channel('metrics')->info('db.query', [
            'connection' => $query->connectionName,
            'ms'         => $query->time,
        ]);
    }
});

The DB::listen hook is the cheapest observability available in Laravel for this problem, because connectionName already distinguishes a replica read from a primary read. Sampling at one percent keeps the log volume trivial while giving an accurate ratio — which is the number that would have revealed a sticky misconfiguration months earlier in most of the applications that have one.

Choosing the sticky setting from the write rate of your requests A horizontal axis showing the fraction of requests that perform at least one write. Below about twenty percent, sticky false is clearly better because few requests need the guarantee and pinning them all wastes replica capacity. Between twenty and sixty percent, sticky false with explicit per-query write connections is still better but requires discipline. Above about sixty percent, most requests write anyway so sticky true costs little and removes a class of bug. 0 % 50 % share of requests that write → sticky = false scope read-after-write per query — clear win sticky = false, with discipline every read-after-write path must be marked; a missed one is a stale read sticky = true is defensible most requests write anyway, so the pinning cost is already being paid Measure your own write rate before choosing: the fraction of requests issuing at least one write. Most read-heavy applications sit far left and have sticky enabled anyway, which is where the capacity goes.

Verification and Rollback

Confirm the split is delivering capacity:

promql
# From the DB::listen sampling, grouped by connection name.
sum by (connection) (rate(laravel_db_query_total[5m]))

The pgsql series should dominate for a read-heavy application. A pgsql::write series close to or above it means reads are being pinned.

Confirm read-after-write still works after turning sticky off:

php
// A feature test per path that writes and immediately reads back.
public function test_profile_update_is_visible_immediately(): void
{
    $this->actingAs($user)->put('/profile', ['name' => 'New Name']);
    $this->get('/profile')->assertSee('New Name');   // must not be stale
}

Run these against a fixture with genuine replication lag — pausing replay on the standby during the test is the only way to prove the guarantee rather than accidentally passing because the replica happened to be current.

Rollback. The sticky flag is a single environment variable, so reverting is a config change and a deploy:

bash
DB_STICKY=true    # revert: pin reads after any write, request-wide

Revert immediately if stale-read reports appear after disabling it, then re-approach path by path: add onWriteConnection() to the specific reads that need it, verify with the feature tests above, and disable sticky again. That sequence takes longer and ends with both the capacity and the correctness, which the global flag cannot give you at the same time.

Edge Cases and Gotchas

sticky state persists for the whole request, including after a rollback

A transaction that begins, writes, and then rolls back still sets the “records have been modified” flag, so every subsequent read in that request goes to the primary even though nothing was written. In a request that speculatively attempts a write and rolls back on validation failure, this pins the entire remainder of the request for no reason.

Read replicas in the read array are not health-checked

Laravel picks a host from the array and connects; if that host is down, the request fails. There is no retry against another entry and no health awareness. For anything beyond two replicas, put a proxy in front and give Laravel a single read host — the reasoning is in choosing a read replica routing proxy.

Octane and long-lived workers change the connection lifecycle

Under Laravel Octane the application boots once and serves many requests in one process, so a connection chosen at boot serves everything until it is recycled. This amplifies the random-host skew: one worker may send every read of its lifetime to the same replica. Configure a connection lifetime, or front the replicas with a balancer that decides per connection rather than per process.

FAQ

What does sticky actually do in Laravel?

When sticky is true and any write has occurred during the current request lifecycle, every subsequent read in that lifecycle is routed to the write connection instead of a replica. It is a read-your-writes guarantee implemented at the coarsest granularity — the whole request — so a request that writes once and reads twenty times sends all twenty-one statements to the primary.

Do queries inside a transaction go to a replica?

No. Laravel routes everything inside DB::transaction to the write connection regardless of the sticky setting, because splitting a transaction across nodes would break its semantics. This means wrapping read-heavy code in a transaction that does not need one sends all of it to the primary, which is a common cause of an underused replica tier.

Does sticky work in a queue worker?

It works per job rather than per request, and only if the framework’s lifecycle events fire — which they do in queue:work. The risk is the opposite of the request case: a long-running worker that performs a write early can carry the sticky flag longer than intended if the recorded state is not reset between jobs. Verify with an assertion in a test job rather than assuming.

How do I force a specific query onto the primary?

Use DB::connection('pgsql::write') for that query, or call onWriteConnection() on the query builder. Both are explicit and local, which is far better than turning sticky on globally to solve one read-after-write case. Reserve the global setting for applications where nearly every request writes.

← Back to ORM Middleware for Automatic Query Routing