Read/Write Splitting for Database Replication in PHP

1. Why read/write splitting becomes necessary at all

Read/write splitting becomes relevant as soon as an application produces more read requests than a single database server can reliably serve, while write access stays comparatively rare. Typical ratios in web applications are 80 to 95 percent reads versus a few percent writes. Without read/write splitting, the single database server has to carry both the write load and the much larger read load, which turns it into the bottleneck of the entire application.

The solution is database replication: a primary server accepts all writes and asynchronously replicates every change to one or more replica servers. Read/write splitting at the application level ensures that read requests go specifically to these replicas, while writes reach only the primary. This considerably relieves the primary and allows horizontal scaling of read load by simply adding more replicas.

It is important to set the right expectation: read/write splitting solves a scaling problem, not a consistency problem. Replication is asynchronous in most setups, meaning a replica can deliver stale data for a brief moment. Anyone who does not deliberately account for this behavior risks confusing bugs where a user does not immediately see a change they just saved.

2. Architecture: primary, replicas and the routing layer

The basic architecture for read/write splitting consists of three components: the primary server for all writes, one or more replica servers for read requests, and a routing layer inside the PHP application that decides which connection to use for a given query. This routing layer should be implemented centrally in one place, ideally inside the database abstraction, so application code never has to distinguish between primary and replica directly.

A common architectural mistake is making the decision for read/write splitting manually in every single repository or controller. That leads to inconsistencies as soon as a developer forgets to explicitly route a critical read request to the primary. A more robust approach is a central connection manager that automatically picks the correct connection based on the query type, so the decision is made and tested in a single place in the code.

3. Building a connection manager for primary and replicas

The connection manager holds one PDO instance each for the primary and for every configured replica. It provides two methods, such as getWriteConnection() for the primary and getReadConnection() for one of the replicas, where the latter can additionally apply a load balancing strategy when several replicas exist. This encapsulation is the core of a working read/write split in PHP, because it completely hides the actual connection choice from business logic.

The connection manager should also offer a way to force reads onto the primary for a specific block of code even for read queries. This becomes necessary when a read request must happen right after a write and the replication lag cannot be tolerated.

<?php

declare(strict_types=1);

final class DatabaseConnectionManager
{
    private PDO $writeConnection;
    /** @var array<int, PDO> */
    private array $readConnections = [];
    private bool $forcePrimaryForReads = false;

    /**
     * @param array<int, array{host: string, port: int}> $replicaConfigs
     */
    public function __construct(
        array $primaryConfig,
        array $replicaConfigs,
        private readonly string $user,
        private readonly string $password,
    ) {
        $this->writeConnection = $this->connect($primaryConfig);
        foreach ($replicaConfigs as $config) {
            $this->readConnections[] = $this->connect($config);
        }
    }

    public function getWriteConnection(): PDO
    {
        return $this->writeConnection;
    }

    public function getReadConnection(): PDO
    {
        if ($this->forcePrimaryForReads || $this->readConnections === []) {
            return $this->writeConnection;
        }

        // Simple round robin across replicas
        $index = array_rand($this->readConnections);
        return $this->readConnections[$index];
    }

    /** Runs a callback with all reads forced onto the primary connection. */
    public function withPrimaryReads(Closure $callback): mixed
    {
        $previous = $this->forcePrimaryForReads;
        $this->forcePrimaryForReads = true;
        try {
            return $callback();
        } finally {
            $this->forcePrimaryForReads = $previous;
        }
    }

    private function connect(array $config): PDO
    {
        $dsn = "mysql:host={$config['host']};port={$config['port']};charset=utf8mb4";
        return new PDO($dsn, $this->user, $this->password, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        ]);
    }
}

4. Query classification: detecting reads versus writes

For the connection manager to automatically pick the right connection, some layer must decide whether a given query is a read or a write. When using a query builder or repository pattern, this classification is trivial, because explicit methods like select(), insert() or update() already exist there and can directly request the matching connection without having to parse the SQL string itself.

If raw SQL is used instead, only a textual check of the SQL prefix remains, for example whether the query starts with SELECT. This method is error prone with more complex constructs like SELECT ... FOR UPDATE, which places a write lock on the primary despite its SELECT prefix and therefore must be routed to the primary regardless. For this reason, an explicit method like query(string $sql, bool $isWrite) is considerably more robust than pure text heuristics.

<?php

declare(strict_types=1);

final class ReplicationAwareRepository
{
    public function __construct(private readonly DatabaseConnectionManager $connections)
    {
    }

    public function findById(int $id): ?array
    {
        // Explicit read — routed to a replica
        $statement = $this->connections->getReadConnection()
            ->prepare('SELECT * FROM orders WHERE id = ?');
        $statement->execute([$id]);

        $row = $statement->fetch(PDO::FETCH_ASSOC);
        return $row === false ? null : $row;
    }

    public function lockForUpdate(int $id): ?array
    {
        // SELECT ... FOR UPDATE must always go to the primary
        $statement = $this->connections->getWriteConnection()
            ->prepare('SELECT * FROM orders WHERE id = ? FOR UPDATE');
        $statement->execute([$id]);

        $row = $statement->fetch(PDO::FETCH_ASSOC);
        return $row === false ? null : $row;
    }

    public function updateStatus(int $id, string $status): void
    {
        // Explicit write — routed to the primary
        $statement = $this->connections->getWriteConnection()
            ->prepare('UPDATE orders SET status = ? WHERE id = ?');
        $statement->execute([$status, $id]);
    }
}

5. Understanding and measuring replication lag

Replication lag describes the time span between a write on the primary and the moment the same change becomes visible on a replica. This lag is normally in the millisecond range, but can grow to seconds or even minutes under load, during network issues, or with particularly large transactions. For read/write splitting, understanding this lag is essential, because it directly determines how current the data read from a replica actually is.

In MySQL, replication status can be queried through SHOW REPLICA STATUS, which among other things provides the Seconds_Behind_Source value. A PHP application can periodically monitor this value and temporarily remove a replica whose lag exceeds a threshold from the load balancing rotation. This active monitoring prevents users from being routed to an already noticeably stale replica.

6. Read your writes: consistency after your own writes

The most common visible problem with read/write splitting is the so called read-your-writes problem: a user saves a change that lands on the primary, gets redirected right afterwards to a page that reads the same data from a replica, and does not see their own change because replication has not caught up yet. From the user's point of view this looks like a bug, even though the system is technically working correctly.

The most robust solution is to specifically redirect read requests from the same user to the primary for a short time span after a write, for example through the withPrimaryReads() method shown earlier. Alternatively, a flag can be set in a session that forces all reads for the duration of the request or for a short time. For read/write splitting in payment related or security critical flows, this deliberate handling of consistency is not optional, it is a basic requirement.

7. Load balancing across multiple replicas

As soon as more than one replica is in use, the connection manager needs to implement a load balancing strategy. A simple round robin scheme that picks each available replica in turn is enough for many applications. More advanced strategies take each replica's current load or its measured replication lag into account, in order to preferentially route requests to less loaded or more current replicas.

For read/write splitting in production environments, a health check that regularly verifies a replica is actually reachable before it is included in the rotation is also recommended. An unreachable replica must never be part of the random or round robin based selection, otherwise every failed connection attempt turns into a visible error for the user.

8. Failover: handling a replica or primary outage

If a single replica goes down, the connection manager should automatically fall back to another available replica or, as a last resort, to the primary, instead of letting an exception propagate outward. This behavior can be implemented with a simple retry mechanism that tries the next replica in the list on a connection error before actually giving up.

An outage of the primary is considerably more critical, because writes are no longer possible at all. In production read/write split setups, an external orchestrator such as Orchestrator for MySQL or Patroni for PostgreSQL handles automatically promoting a replica to the new primary. The PHP application should be configured in this case to reach the new primary through a stable DNS name or proxy, instead of maintaining hardcoded IP addresses in the code.

9. Read/write splitting compared to other scaling approaches

Read/write splitting is one of several strategies for database scaling and combines well with other approaches like caching or sharding. The decision about which approach makes sense depends on the concrete load profile of the application.

In practice, many applications combine read/write splitting with a caching layer: frequently read, rarely changed data comes from Redis, less frequently read or personalized data comes from the replicas, and only writes reach the primary. Sharding usually only becomes relevant once even the write load on the primary becomes the bottleneck, which happens considerably less often than pure read load problems.

10. Summary

Read/write splitting consistently routes writes to a primary server and reads to replicas in order to scale the database layer horizontally for read load. The core of the implementation in PHP is a central connection manager that recognizes query types, routes connections accordingly, and deliberately handles replication lag as well as the read-your-writes problem.

Load balancing across multiple replicas, health checks and a well thought out failover concept round off a production ready read/write split. It remains important to draw a clear line to other scaling approaches: read/write splitting solves read load problems, while sharding is the better tool for write load problems and caching for repeated, identical queries.

11. FAQ: Read/Write Splitting in PHP