0Pricing
SQL Academy · Lesson

Read Replicas and Connection Routing

Route read-only traffic to replicas with PgBouncer or a smart driver, and accept the staleness trade-off.

Read Replicas and Connection Routing is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Read Replicas?

Read-heavy workloads can offload SELECTs to replicas:

  • Primary handles writes
  • Replicas handle most reads
  • Horizontal read scaling without sharding

Eventual Consistency Caveat

Replicas lag the primary. A user that just placed an order may not see it on the replica yet. Either:

  • Route reads-after-writes to the primary
  • Wait for replica catch-up
  • Cache the post-write state in the app

Routing Strategies

  1. Application-level routing — app code picks primary vs replica
  2. Driver-level routing — JDBC, pgjdbc, pgxpool pool with master/replica awareness
  3. Proxy-level routing — pgpool, pgbouncer, HAProxy with read/write detection

Application Routing

Two connection pools, one per role:

const primary = new Pool({ host: 'primary', ... });
const replica = new Pool({ host: 'replica', ... });

// reads go to replica:
await replica.query('SELECT ...');

// writes (and read-your-writes) go to primary:
await primary.query('INSERT ...');

PgPool-II

Proxy that parses SQL and routes SELECT to replicas, writes to primary. Supports load balancing and connection pooling:

# pgpool.conf
backend_hostname0 = 'primary'
backend_hostname1 = 'replica1'
load_balance_mode = on

HAProxy with pg_isready Checks

HAProxy doesn't understand SQL but can route by port — one frontend for writes (to primary), another for reads (to replicas with health checks):

frontend writes
  bind *:5432
  default_backend primary_pool

frontend reads
  bind *:5433
  default_backend replicas_pool
  balance roundrobin

AWS RDS Endpoints

RDS provides a single writer endpoint and a reader endpoint that load-balances across all replicas — simplest in cloud.

Replication Lag Considerations

Track lag and avoid routing to replicas that are too far behind:

-- On primary:
SELECT client_addr, replay_lag FROM pg_stat_replication;

-- HAProxy / pgpool can use a custom health check that fails if replay_lag > threshold.

Sticky Session Pattern

For "read your own writes": pin the user's session to primary for N seconds after a write. Or stamp the write's LSN and wait for replica replay before serving.

LSN-Based Wait

After a write, capture LSN; before reading on replica, wait until replica has replayed up to that LSN:

-- After write on primary:
SELECT pg_current_wal_lsn();        -- save this

-- Before read on replica:
SELECT pg_last_wal_replay_lsn() >= $saved_lsn;
-- spin or wait until true

Caching as a Replica

Sometimes Redis / Memcache is a "replica" for read scaling — much faster than another Postgres instance for hot keys.

When to NOT Use Replicas

If your workload is write-heavy, replicas mostly add cost without speed. If reads are already fast on the primary, you may not need them.

Disaster Recovery vs Read Scaling

HA failover replicas are a different concern from read-scaling replicas. Often you have both.

Recap

Read replicas scale reads but carry lag.

  • App / driver / proxy routing
  • Replication lag = eventual consistency
  • Sticky-to-primary or LSN-wait for read-your-writes
  • Monitor lag, fail back to primary if too far behind

Quick Check

A user posts a comment and immediately refreshes the page. The page reads from a replica and shows no comment. What's the problem and one fix?

Frequently asked questions

Is the “Read Replicas and Connection Routing” lesson free?

Yes — the full text of “Read Replicas and Connection Routing” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Read Replicas and Connection Routing”?

Route read-only traffic to replicas with PgBouncer or a smart driver, and accept the staleness trade-off. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Read Replicas and Connection Routing” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Streaming Replication and WAL
  2. Logical Replication for Sharding
  3. Failover and Leader Election (Patroni, Stolon)
  4. Read Replicas and Connection Routing
← Back to SQL Academy