postgresql read replicas app

If you want your application to read from PostgreSQL read replicas, the usual pattern is:

• Primary (writer): all INSERT/UPDATE/DELETE and schema changes
• Read replicas: SELECT-heavy traffic, reports, dashboards, search, etc.

Typical app design

1. Use two database connections

• Write connection → primary
• Read connection → replica(s)

Example logic:

• getUser(id) → replica
• createUser(...) → primary
• after createUser, any immediate read that must see the new row should go to primary or use a consistency strategy

2. Route queries based on type

You can do this in:

• application code
• ORM configuration
• a database proxy / load balancer

Common patterns:

• Read/write splitting
• Replica pool for reads
• Sticky reads after write (temporary routing to primary to avoid replication lag issues)

---

Important caveats

Replication lag

Replicas may be behind the primary, so:

• a user may write data and not see it immediately on a replica
• this is normal in async replication

Solutions:

• read your own writes from primary
• use session stickiness after write
• only send eventually-consistent reads to replicas

Failover handling

If the primary changes, your app must:

• reconnect automatically
• refresh DNS/connection info
• not hardcode a single host

Transaction consistency

Do not split one transaction across primary and replica.
Transactions must stay on the primary.

---

Example architectures

Simple

• App connects to:
  • db-primary
  • db-replica-1
  • db-replica-2
• App code decides where queries go

With proxy

Use something like:

• Pgpool-II
• HAProxy
• AWS RDS Proxy + app-side logic
• ProxySQL is more common for MySQL, but some setups use similar proxying patterns for Postgres

---

ORM examples

Rails

Rails supports read replicas with multiple database connections.

Django

You can implement database routers to send reads to replicas and writes to primary.

Node.js / Python / Java

Usually:

• define a read pool and a write pool
• use a query wrapper or repository layer

---

Best practices

• Keep writes on primary only
• Accept that replica reads are eventually consistent
• Use replicas for scale, not for strong consistency
• Monitor replication lag
• Test failover and reconnect behavior

If you want, I can show:

1. a generic app pattern for read/write splitting, or
2. examples in a specific stack like Node.js, Django, Rails, Spring Boot, or Go.