Read replicas and database replication explained

A read replica is a copy of your database that continuously applies changes from the primary and serves read-only queries, so dashboards, course pages and reports can be spread across several servers while every write still goes to one primary. In PostgreSQL, replicas usually use streaming replication, which is asynchronous by default: a replica can lag slightly behind the primary. The design question is therefore which reads can tolerate that lag, and what to do about the ones that can't.

Why replicate

• Read scaling. Most traffic on a learning platform is reads: course pages, dashboards, question banks, results. Replicas add capacity for those without touching the primary.
  
  
• Isolating heavy reads. A month-end fee report or an analytics export can run on a replica without slowing a live test on the primary.
  
  
• High availability. If the primary fails, a replica can be promoted to take its place.
  
  
• Proximity. A replica in another region can serve reads closer to users there.
  
  

Replicas don't scale writes, since every write still lands on the primary and is repeated on every replica. They don't protect you from mistakes either: a bad DELETE replicates within seconds. Keep backups with point-in-time recovery regardless, and for write scaling look at sharding vs partitioning.

How Postgres replication works

Every change in PostgreSQL is first written to the write-ahead log (WAL). Replication ships that log, or changes decoded from it, to other servers.

For read replicas, you want streaming replication. The replica connects to the primary, receives WAL records as they are generated and replays them, and with hot standby on, it answers read-only queries while doing so. Managed services work the same way underneath; AWS says RDS for PostgreSQL uses PostgreSQL's native replication for its read replicas.

Synchronous vs asynchronous

By default the primary confirms a commit as soon as it is safely on its own disk, and replicas catch up afterwards. The PostgreSQL replication documentation says this delay is typically under a second if the standby can keep up with the load. The risk is on failover: transactions committed on the primary but not yet received by the replica are lost.

Synchronous replication makes commits wait for one or more named standbys, chosen by synchronous_standby_names. The synchronous_commit setting then decides how long each commit waits:

The costs are longer commits, at least one network round trip each and much more for remote_apply, and a hazard: if the only synchronous standby goes down, commits stop completing. List several candidates (FIRST 1 (s1, s2) or ANY 1 (s1, s2)) so one failure doesn't freeze writes. Because synchronous_commit can be set per transaction, you can keep a payment confirmation synchronous and let activity logs commit asynchronously.

Routing reads to replicas

In Django, replicas are extra entries in DATABASES plus a router that decides where each query goes:

# settings.py (PRIMARY and REPLICA are ordinary connection dicts)
DATABASES = {"default": PRIMARY, "replica": REPLICA}
DATABASE_ROUTERS = ["core.routers.PrimaryReplicaRouter"]

# core/routers.py
class PrimaryReplicaRouter:
    def db_for_read(self, model, **hints):
        return "replica"
    def db_for_write(self, model, **hints):
        return "default"
    def allow_relation(self, obj1, obj2, **hints):
        return True
    def allow_migrate(self, db, app_label, model_name=None, **hints):
        return db == "default"

The Django documentation is candid that this kind of router is flawed: it does nothing about replication lag, and it ignores transactions. A request that writes a row and then reads it back through the router reads from the replica, which may not have the row yet. Treat the router as a starting point, and be explicit where it matters: reports can call .using("replica"), and anything that must see the latest data can call .using("default").

Each replica also needs its own share of connections, because replicas have connection limits just as the primary does. Put a pooler in front of each; our guide to PgBouncer connection pooling covers the settings.

Replication lag and read-your-writes

Here is an illustrative case. A student submits a mock test at 11:59:58 and is redirected to the results page. The submission went to the primary; the results page reads from a replica running 800 ms behind. The page says "Not attempted", the student panics and submits again, or raises a ticket. Nothing is broken; the read simply arrived before the write did.

The guarantee you want is read-your-writes: after a user changes something, that user's reads reflect it. Ways to get it, roughly from simplest to strongest:

1. Route by page. Pages shown right after a write, such as the post-submission result or the payment receipt, always read from the primary.
   
   
2. Pin the user to the primary briefly. After any write, store a "use primary until" time a few seconds ahead in the session, and route that user's reads to the primary until it passes. Pick the window from your observed lag, with a margin.
   
   
3. Check the replica's position. Record the primary's WAL position after the write (pg_current_wal_lsn()) and read from a replica only once pg_last_wal_replay_lsn() on that replica has reached it.
   
   
4. Use remote_apply for the transactions that need it, so the commit returns only after the synchronous standby can show it.
   
   

Caches make this worse if you're not careful: a cache refilled from a lagging replica stores old data under a fresh TTL. Refill caches from the primary after a write, as our guide to cache invalidation explains.

Measure lag instead of assuming it. On the primary, pg_stat_replication shows write_lag, flush_lag and replay_lag for each standby. On a replica, now() - pg_last_xact_replay_timestamp() gives the time since the last replayed transaction, but it keeps growing when the primary is idle, so don't alert on it at 3 a.m. AWS notes the same effect for RDS: an idle primary can make a PostgreSQL replica report up to five minutes of lag.

The other replica-side surprise is cancelled queries. When replaying WAL would remove rows a long query still needs, the standby waits up to max_standby_streaming_delay (30 seconds by default) and then cancels the query. Turning on hot_standby_feedback avoids most cancellations, at the cost of delayed cleanup and possible bloat on the primary. The hot standby documentation covers the trade-off; the usual answer is a separate replica for long reports.

Replicas for failover

Promoting a replica (pg_promote(), or your platform's equivalent) turns it into a writable primary. Doing it safely takes more than that one call:

• Accept or avoid data loss. With asynchronous replication, whatever the replica hadn't received is gone; with synchronous replication, nothing committed is lost, provided you promote a synchronous standby. Our guide to RTO and RPO helps you decide which you need.
  
  
• Make sure the old primary stays down. Two primaries accepting writes is worse than an outage. Tools such as Patroni and repmgr handle leader election and fencing; managed services do it for you.
  
  
• Point everything at the new primary, usually by DNS or a virtual IP, and rebuild the old primary as a replica.
  
  

On AWS, RDS read replicas replicate asynchronously and can be promoted to standalone instances, but for PostgreSQL the promotion is one-way. RDS allows up to 15 read replicas per primary. Its Multi-AZ standby, in the classic single-standby deployment, is a different thing: synchronous, used for automatic failover, and not available for reads.

Read replicas vs sharding

Replicas copy all of the data to every server, so they multiply read capacity but leave write capacity and storage per server unchanged. Sharding splits the data so that each server holds and writes only part of it. Replicas are far simpler and cover most platforms for a long time, because reads usually far outnumber writes. Reach for sharding when the primary can't keep up with writes even after tuning, database indexing and a bigger machine.

Key takeaways

• A read replica applies the primary's changes continuously and serves read-only queries; it scales reads, not writes.
  
  
• PostgreSQL streaming replication is asynchronous by default, typically under a second behind, with a small data-loss window on failover.
  
  
• Synchronous replication trades commit latency for durability; list several candidate standbys so one failure doesn't block writes.
  
  
• Route reads deliberately and give users read-your-writes: primary reads after writes, brief pinning or position checks.
  
  
• Monitor lag and query cancellations on replicas, and rehearse promotion before you need it.
  
  

Frequently asked questions

What is read replica in database?

A read replica is a copy of a database kept up to date by replication from a primary server and used only for reads. Applications send writes to the primary and spread read queries such as page views, dashboards and reports across one or more replicas. Replication is usually asynchronous, so a replica may be slightly behind, and reads that must include a user's latest change should go to the primary.

What is read replica in AWS RDS?

In Amazon RDS, a read replica is a read-only copy of a DB instance that RDS creates from a snapshot and then keeps current using the engine's asynchronous replication. You can have up to 15 per primary, including in other regions, and they are billed as normal instances. A replica can be promoted to a standalone database, which for PostgreSQL can't be reversed. It is separate from the Multi-AZ standby used for failover.

What is read replica in Postgres?

In PostgreSQL, a read replica is a hot standby server: it receives the primary's write-ahead log through streaming replication, replays it continuously and accepts read-only queries meanwhile. Replication is asynchronous unless you configure synchronous standbys. Long queries on a replica can be cancelled when they conflict with replay, which max_standby_streaming_delay and hot_standby_feedback let you tune.