How to Implement the Read Replica Pattern for Database Scaling

The read replica pattern separates write traffic to a primary database from read traffic to replica instances, enabling horizontal scaling of read-heavy workloads through a routing layer that can be implemented either in application code or via database middleware like ProxySQL.

According to the ByteByteGoHq/system-design-101 repository, this architectural pattern eliminates the primary database as a bottleneck by distributing read queries across multiple replicas while maintaining a single source of truth for writes. The implementation relies on continuous replication of the primary's binary log or write-ahead log (WAL) to keep replicas synchronized.

Core Architecture of the Read Replica Pattern

The architecture consists of three distinct components working in concert to route queries efficiently.

Primary and Replica Roles

The primary (writer) database receives all data-modifying statements including INSERT, UPDATE, and DELETE operations. The replica(s) (readers) continuously replicate the primary's binlog or WAL and serve only SELECT queries. This separation allows read capacity to scale horizontally by adding more replica instances without affecting write performance.

The Routing Layer

As documented in data/guides/how-to-implement-read-replica-pattern.md, an order service should never communicate with the database directly. Instead, it sends every query to a routing layer—either embedded in application code or implemented as dedicated database middleware—which forwards writes to the primary and reads to the replicas.

Why Implement Read Replicas?

Implementing this pattern delivers three primary advantages for production systems:

• Read scalability – Multiple replicas can handle concurrent reads, dramatically increasing read QPS beyond the limits of a single database instance.
• Simplified application code – When using middleware, the application does not need to be aware of the database topology or handle connection switching logic.
• Better compatibility – Middleware speaks the native MySQL protocol, so any standard MySQL client works unchanged without code modifications.

Challenges and Mitigation Strategies

Replication lag represents the primary operational challenge. Replicas may lag seconds behind the primary, causing stale reads for recently written data. Mitigate this by:

• Routing latency-sensitive reads to the primary.
• Sending "read-after-write" queries to the primary for immediate consistency.
• Using the database's "catch-up" status API to decide dynamically whether a replica is current enough to serve a specific query.

Implementation Approaches

You can implement query routing through two distinct strategies, though the repository strongly recommends one for large systems.

Application-Level Routing

Embedding routing logic directly in application code requires checking each query type and choosing the appropriate database endpoint. This approach couples infrastructure logic with business code and becomes difficult to maintain as the topology changes.

Database Middleware (Recommended)

Deploying a proxy that performs routing transparently decouples the application from database topology. As detailed in data/guides/database-middleware.md, MySQL-compatible proxies such as ProxySQL or MySQL Router intercept queries and route them based on SQL patterns, hostgroup configurations, and custom rules.

Step-by-Step Middleware Implementation

Follow these steps to implement the read replica pattern using ProxySQL, based on the configuration examples in data/guides/how-to-implement-read-replica-pattern.md:

1. Provision primary and replica instances (e.g., AWS RDS primary with read replicas).
2. Set up replication by enabling binary logging on the primary and configuring replicas to pull from it.
3. Deploy the middleware (e.g., ProxySQL) on dedicated instances or alongside application servers.
4. Configure routing rules in ProxySQL:

-- ProxySQL configuration snippet
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
  VALUES (10, 'primary-db.example.com', 3306),   -- Writes
         (20, 'replica1-db.example.com', 3306), -- Reads
         (20, 'replica2-db.example.com', 3306);

-- Queries ending with SELECT go to hostgroup 20 (read replicas)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
  VALUES (1, 1, '^SELECT', 20);

-- All other queries (writes) go to hostgroup 10
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
  VALUES (2, 1, '^.*$', 10);

5. Point the application at the middleware endpoint (e.g., proxy-db.example.com:6033). The application now uses a single DSN while the middleware handles the read/write split transparently.

6. Handle lag-sensitive reads by adding specific rules for read-after-write scenarios:

-- Force a specific query to the primary when fresh data is required
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, comment)
  VALUES (3, 1, '^SELECT .* FROM orders WHERE user_id = \\d+ AND created_at > NOW\\(\\) - INTERVAL 5 SECOND', 10, 'read‑after‑write');

Practical Code Example

When using middleware, application code remains agnostic to the underlying database topology. Here is a complete Node.js implementation connecting to ProxySQL:

// db.js – single connection pool pointing at the middleware
import mysql from 'mysql2/promise';

export const pool = mysql.createPool({
  host: 'proxy-db.example.com',
  port: 6033,
  user: 'app_user',
  password: process.env.DB_PASSWORD,
  database: 'ecommerce',
  waitForConnections: true,
  connectionLimit: 20,
});

// Standard read operation (routed to replica)
export async function getOrder(orderId) {
  const [rows] = await pool.query('SELECT * FROM orders WHERE id = ?', [orderId]);
  return rows[0];
}

// Write operation with immediate read (primary routing handled by middleware rules)
export async function placeOrder(order) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    await conn.query('INSERT INTO orders SET ?', order);
    // Immediately read the order from the primary (middleware routes it because of the rule)
    const [row] = await conn.query(
      'SELECT * FROM orders WHERE id = LAST_INSERT_ID()'
    );
    await conn.commit();
    return row;
  } finally {
    conn.release();
  }
}

The repository files data/guides/read-replica-pattern.md and data/guides/how-to-implement-read-replica-pattern.md provide the conceptual foundation and implementation details referenced above.

Summary

• The read replica pattern separates write and read traffic to scale read-heavy workloads horizontally.
• Database middleware like ProxySQL is the recommended implementation approach for large systems, offering protocol compatibility and transparent routing.
• Replication lag must be addressed through intelligent routing rules that send critical reads to the primary.
• Configuration involves defining hostgroups for primary and replica instances, then creating query rules that route SELECT statements to replicas and all other operations to the primary.
• Application code connects to a single middleware endpoint, remaining decoupled from database topology changes.

Frequently Asked Questions

What is the primary benefit of the read replica pattern?

The primary benefit is horizontal scaling of read capacity. By distributing SELECT queries across multiple replica instances, you can handle significantly higher read QPS than a single database could manage alone, while isolating write operations to the primary node.

How do you handle replication lag in a read replica architecture?

Handle replication lag by implementing routing rules that detect stale data requirements. Send time-sensitive queries, "read-after-write" operations, and transactions requiring immediate consistency to the primary database. Use middleware rules or application logic to check replication status before routing critical reads to replicas.

Should I implement routing in application code or use database middleware?

For most production systems, database middleware is strongly recommended. While application-level routing works for simple cases, it couples infrastructure logic with business code and becomes unmaintainable as topology grows. Middleware like ProxySQL provides transparent routing, native protocol compatibility, and dynamic configuration without code changes, as documented in data/guides/database-middleware.md.

Can I use read replicas with databases other than MySQL?

Yes, the read replica pattern applies to any database system supporting replication, including PostgreSQL, SQL Server, and MongoDB. While the specific implementation details differ—such as using WAL streaming instead of binlog—the architectural concept of separating write and read traffic through a routing layer remains consistent across database engines.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}