Read Replica Routing

Directing traffic to save the Primary Database from exhaustion.

The idea

In most web apps, 90% of traffic is reading data, and only 10% is writing. To scale, we create Read Replicas—exact copies of the Primary Database that stay continuously updated. The Application must be smart enough to route all SELECT queries to the Replicas, while sending INSERT, UPDATE, and DELETE queries to the Primary. This takes the massive read load off the Primary, keeping the whole system fast.

How it works (Connection Pools)

Your application maintains two separate connection pools: a WritePool and a ReadPool. When a web request comes in, your ORM or application code inspects the query type. If it's a read, it borrows a connection from the ReadPool. If it mutates data, it borrows from the WritePool.

# Example: Rails Multi-DB Routing
ActiveRecord::Base.connected_to(role: :reading) do
  # This SELECT goes to the Replica!
  User.where(active: true).to_a
end

ActiveRecord::Base.connected_to(role: :writing) do
  # This UPDATE goes to the Primary!
  User.create(name: "Alice")
end

Cost

Read Replicas introduce Replication Lag. When you write to the Primary, it takes a few milliseconds (or seconds under load) for that data to copy over to the Replica. If a user updates their profile picture on the Primary, and the next page load reads from the Replica, they might see their old picture! This is called "Read-After-Write Consistency" failure.

Watch out for

• Transactions: If you open a database Transaction, all queries within that transaction must go to the Primary, even if they are SELECTs. If you split a transaction across the Primary and a Replica, the Replica won't see uncommitted writes, and you'll get garbage data. Good ORMs handle this automatically.