Read/Write DB Splitting

Read/write splitting offloads SELECT queries onto one or more read replicas while directing INSERT, UPDATE, and DELETE queries to the primary (write) database. This reduces load on the primary server and improves overall throughput for read-heavy applications.

How Laravel Supports It

Laravel's database configuration accepts read and write sub-arrays within a single connection. Eloquent routes queries automatically based on the type of operation:

// config/database.php
'mysql' => [
    'driver' => 'mysql',
    'read' => [
        'host' => [
            env('DB_READ_HOST_1', '127.0.0.1'),
            env('DB_READ_HOST_2', '127.0.0.1'),
        ],
    ],
    'write' => [
        'host' => env('DB_WRITE_HOST', '127.0.0.1'),
    ],
    'database' => env('DB_DATABASE', 'laravel'),
    'username' => env('DB_USERNAME', 'root'),
    'password' => env('DB_PASSWORD', ''),
    'charset'  => 'utf8mb4',
    'prefix'   => '',
],

Laravel round-robins across the hosts listed in the read array, distributing SELECT queries across your replicas.

Sticky Connections

Replication is asynchronous. If a user writes a record and immediately reads it back, the read replica may not have it yet. Laravel solves this with sticky connections: once a write happens on a connection, all subsequent reads in that request use the primary server instead of a replica.

Enable sticky mode in your config:

'mysql' => [
    'driver' => 'mysql',
    'read' => [
        'host' => [env('DB_READ_HOST', '127.0.0.1')],
    ],
    'write' => [
        'host' => env('DB_WRITE_HOST', '127.0.0.1'),
    ],
    'sticky' => true, // Reads go to primary after any write in the same request
    'database' => env('DB_DATABASE', 'laravel'),
    'username' => env('DB_USERNAME', 'root'),
    'password' => env('DB_PASSWORD', ''),
],

Forcing a Connection

If you need to bypass the automatic routing for a specific query, use onWritePdo() or onReadPdo():

// Force this read onto the primary (write) connection
$freshData = DB::connection('mysql')->table('orders')
    ->where('id', $orderId)
    ->first();

In Eloquent, you can also use the onWritePdo method available on the connection:

$order = Order::on('mysql')->find($orderId);

When Read/Write Splitting Is Worthwhile

It makes sense when:

• Your application is read-heavy (most traffic is SELECT queries).
• Your primary database server is becoming a bottleneck.
• You are already running replicas for backup or failover purposes.

It is not worthwhile when:

• Your application is write-heavy. Replicas do not help with write performance.
• You are running a small application on a single server. The added complexity outweighs the gains.
• Your replication lag is high. Sticky connections mitigate this, but they do not eliminate it entirely.

Tips

• Monitor replication lag on your replicas. If lag grows, sticky connections will route more traffic back to the primary, reducing the benefit of splitting.
• Test with sticky connections enabled. Bugs caused by reading stale data from a replica are notoriously difficult to reproduce in development.
• Keep your .env file as the single source of truth for read and write host addresses. Changing a host should never require a code change.
• If you are on a managed database service (AWS RDS, Google Cloud SQL), read replicas are typically a one-click feature, making this pattern very low effort to adopt.