Laravel Read-Write Splitting: Deterministic Connection Routing Guide

Last month, our primary reporting dashboard started timing out during peak hours, effectively locking our application’s write operations. We were hitting the ceiling of a single RDS instance, and while we considered database sharding for high-concurrency, the complexity was overkill for our immediate problem. We needed a cleaner way to offload read traffic to our existing read replicas without rewriting our entire Eloquent layer.

The Problem with Native Read-Write Splitting

Laravel offers basic read-write splitting out of the box via the read and write keys in config/database.php. It works well for simple applications, but it’s often non-deterministic. If you perform a write operation and immediately need to read that same record, you’re at the mercy of replication lag.

We initially relied on the default configuration, but we saw intermittent "record not found" errors in our logs. This happened because the application occasionally routed the subsequent read to a replica that hadn't received the binlog update yet. We needed a deterministic approach: if a user just performed a mutation, we must force the next read to the primary node.

Implementing Deterministic Connection Routing

To solve this, we moved away from automatic splitting and implemented a middleware-level connection router. This allows us to manually dictate the connection based on the request context or the state of the current session.

First, I created a DatabaseConnectionManager class to track the connection state.

PHP
namespace App\Database;

class ConnectionManager
{
    protected bool $forcePrimary = false;

    public function forcePrimary(bool $value = true): void
    {
        $this->forcePrimary = $value;
    }

    public function shouldUsePrimary(): bool
    {
        return $this->forcePrimary;
    }
}

Next, we register this as a singleton in the service container. The goal is to hook into the Laravel database resolver to switch connections dynamically.

Middleware-Level Routing Logic

We built a middleware that inspects incoming requests. If the request is a POST, PUT, PATCH, or DELETE, we flag the session to use the primary connection for the remainder of the request lifecycle.

PHP
namespace App\Http\Middleware;

use App\Database\ConnectionManager;
use Closure;

class RouteDatabaseTraffic
{
    public function __construct(protected ConnectionManager $manager) {}

    public function handle($request, Closure $next)
    {
        if ($request->isMethod('GET')) {
            return $next($request);
        }

        $this->manager->forcePrimary(true);
        return $next($request);
    }
}

To make this work, we override the default connection resolution in our AppServiceProvider. This ensures that whenever Eloquent fetches data, it checks our ConnectionManager first.

Managing Replication Lag

Even with deterministic routing, you'll eventually need to handle CQRS with materialized views for complex reporting. However, for standard CRUD, forcing the primary after a write is the most effective way to maintain data integrity.

We also added a "sticky" session trait to our base controller. If a user performs a sensitive action, we set a use_primary cookie for about 2 seconds. This covers the typical replication lag window of roughly 280ms we see in our production environment.

Why Avoid Database Proxies?

We experimented with tools like ProxySQL for WordPress performance and database proxy strategies, but it added another layer of infrastructure to manage. By handling the routing inside the Laravel application, we keep our stack leaner.

The primary trade-off here is code maintenance. You are responsible for identifying which read operations are critical and which can tolerate eventual consistency. If you accidentally mark every read as "primary," you’ll end up right back where you started: a bottlenecked primary node.

Key Takeaways for High Availability

1. Be Selective: Use the primary connection only when read-after-write consistency is strictly required.
2. Monitor Lag: Use SHOW SLAVE STATUS (or the equivalent for your cloud provider) to understand your replication lag.
3. Keep it Simple: If your traffic is massive, consider Laravel database sharding before adding complex proxy layers.

I’m still not entirely satisfied with how we handle background jobs. When a queued job processes a write, it doesn't have the "sticky" session context. We’ve been manually calling DB::connection('primary') inside those jobs, which feels a bit brittle. I’m currently looking into custom queue middleware to handle this automatically, but that's a problem for next week’s sprint.

Frequently Asked Questions

Q: Does this approach impact performance? A: Negligibly. The connection switching happens via the service container, which is extremely fast. The overhead is significantly lower than introducing a network proxy.

Q: How do you handle read-heavy reporting queries? A: We explicitly point those queries to the read replica connection (DB::connection('replica')->table(...)) regardless of the middleware state.

Q: What if the primary node goes down? A: This routing logic only dictates which node you talk to, not the failover mechanism itself. You still need a robust HA strategy (like RDS Multi-AZ) to handle node failures.