Read Replicas & Database Scaling

Also Known As

TL;DR

Explanation

Most web applications read far more than they write. Read replicas receive a continuous stream of changes from the primary via replication (MySQL binary log, PostgreSQL streaming replication) and serve SELECT queries independently. PHP applications implement this with a connection manager routing writes to the primary DSN and reads to a replica DSN. Laravel supports multiple connections natively. Caveats: replication lag means replicas may serve slightly stale data — never read from a replica immediately after a write in the same request without a mitigation strategy (e.g. read-your-writes from the primary for the current session). Monitor replication lag as a key operational metric.

Common Misconception

Why It Matters

Common Mistakes

• Sending write queries to replicas — they are read-only; writes silently fail or error.
• Reading from replica immediately after a write — replication lag means the new data may not be there yet.
• Not routing long-running analytics queries to replicas — they block the primary's query queue.
• Using a single connection for both reads and writes when separate read/write connections are configured.

Code Examples

// All queries to primary — no read scaling:
$pdo = new PDO('mysql:host=primary;dbname=app', ...);
$pdo->query('SELECT * FROM orders'); // Should go to replica
$pdo->query('UPDATE orders SET ...');  // Correctly on primary

// With read/write splitting:
$read  = new PDO('mysql:host=replica;dbname=app', ...);
$write = new PDO('mysql:host=primary;dbname=app', ...);

// Route reads to replica, writes to primary
class DatabaseManager {
    public function __construct(
        private PDO $primary,
        private array $replicas,
    ) {}

    public function write(): PDO { return $this->primary; }

    public function read(): PDO {
        // Round-robin across replicas
        return $this->replicas[array_rand($this->replicas)];
    }
}

// Usage
$db->write()->prepare('INSERT INTO orders ...')
$db->read()->prepare('SELECT * FROM orders WHERE user_id = ?')

// Caution: read your own writes — after a write, use primary for a short window
// to avoid reading stale replica data (replication lag)

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/replication.html

Tags

Related Terms