Read Replicas in Go and Routing Reads Safely in Services

One database server has a ceiling, and for most services the first limit to arrive is reads: dashboards, search pages, reports and list endpoints that outnumber writes ten to one. A read replica answers them. It is a second PostgreSQL server that streams the primary's write ahead log and applies it, so it holds a copy of the data that is a little behind. You send writes to the primary and spread reads over replicas. The catch is the word "behind". This guide builds a real replica on Go 1.25.6 and PostgreSQL 18.6, measures how often it is stale, and writes the router that makes reads safe: the one that sends a read to the replica only when the replica has caught up with the user's own last write.

It extends the database cornerstone and relies on the pool behaviour in connection pool settings. Its transaction rules come from unit of work.

The setup

The lab uses the two containers from the other articles, with a streaming replica added. On the primary, the replication line in pg_hba.conf allows a connection from the Docker network, followed by a reload:

docker exec gh-lab-pg bash -c 'echo "host replication all all scram-sha-256" >> $PGDATA/pg_hba.conf'
docker exec gh-lab-pg psql -U postgres -c "select pg_reload_conf()"

The replica is a base backup of the primary started as a standby. -R writes the settings that make it follow the primary, -X stream includes the log it needs, and -c fast asks for an immediate checkpoint, without which the backup waited minutes:

docker run -d --name gh-lab-replica --user postgres -p 127.0.0.1:55433:5432 \
  -e PGPASSWORD=secret -e PGDATA=/var/lib/postgresql/replica postgres:18-alpine \
  bash -c 'pg_basebackup -h 172.17.0.2 -U postgres -D $PGDATA -R -X stream -c fast && exec postgres -D $PGDATA'

These commands were run by hand before the program, and the replica began streaming at once. PostgreSQL 18 has wal_level = replica by default, so the primary needed no other change. Managed services create replicas with a button; the semantics below are the same.

The program

package main

import (
	"context"
	"errors"
	"fmt"
	"log"
	"time"

	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

const (
	primaryDSN = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"
	replicaDSN = "postgres://postgres:[email protected]:55433/lab?sslmode=disable"
)

// Cluster sends writes to the primary and reads to a replica when the replica is current enough.
type Cluster struct {
	Primary, Replica *pgxpool.Pool
}

// Write runs the statement on the primary and returns the log position that includes it.
func (c Cluster) Write(ctx context.Context, q string, args ...any) (lsn string, err error) {
	if _, err = c.Primary.Exec(ctx, q, args...); err != nil {
		return "", err
	}
	err = c.Primary.QueryRow(ctx, "select pg_current_wal_lsn()::text").Scan(&lsn)
	return lsn, err
}

// ReadPool returns the replica if it has replayed at least up to lsn, waiting briefly for it to catch up.
func (c Cluster) ReadPool(ctx context.Context, lsn string, wait time.Duration) (*pgxpool.Pool, string) {
	deadline := time.Now().Add(wait)
	for {
		var caught bool
		err := c.Replica.QueryRow(ctx, "select pg_last_wal_replay_lsn() >= $1::pg_lsn", lsn).Scan(&caught)
		if err == nil && caught {
			return c.Replica, "replica"
		}
		if time.Now().After(deadline) {
			return c.Primary, "primary"
		}
		time.Sleep(5 * time.Millisecond)
	}
}

func count(ctx context.Context, p *pgxpool.Pool) int {
	var n int
	if err := p.QueryRow(ctx, "select count(*) from notes619").Scan(&n); err != nil {
		log.Fatal(err)
	}
	return n
}

func main() {
	ctx := context.Background()
	primary, err := pgxpool.New(ctx, primaryDSN)
	if err != nil {
		log.Fatal(err)
	}
	defer primary.Close()
	replica, err := pgxpool.New(ctx, replicaDSN)
	if err != nil {
		log.Fatal(err)
	}
	defer replica.Close()
	c := Cluster{primary, replica}

	for _, q := range []string{"drop table if exists notes619", "create table notes619 (id bigserial primary key, body text not null)"} {
		if _, err := primary.Exec(ctx, q); err != nil {
			log.Fatal(err)
		}
	}
	lsn, _ := c.Write(ctx, "insert into notes619 (body) values ('seed')")
	for count(ctx, replica) != 1 {
		time.Sleep(10 * time.Millisecond)
	}

	var inRecovery bool
	_ = replica.QueryRow(ctx, "select pg_is_in_recovery()").Scan(&inRecovery)
	fmt.Println("replica is in recovery:", inRecovery)

	_, err = replica.Exec(ctx, "insert into notes619 (body) values ('write to replica')")
	var pgErr *pgconn.PgError
	if errors.As(err, &pgErr) {
		fmt.Printf("write to the replica: SQLSTATE %s %s\n", pgErr.Code, pgErr.Message)
	}

	// 1. Write, then read from the replica at once, 300 times.
	stale := 0
	for i := 0; i < 300; i++ {
		before := count(ctx, primary)
		if _, err := c.Write(ctx, "insert into notes619 (body) values ('x')"); err != nil {
			log.Fatal(err)
		}
		if count(ctx, replica) < before+1 {
			stale++
		}
	}
	fmt.Printf("1. replica read straight after a write was behind %d times out of 300\n", stale)

	// 2. Pause replay, as a long query on the replica or a busy network would.
	if _, err := replica.Exec(ctx, "select pg_wal_replay_pause()"); err != nil {
		log.Fatal(err)
	}
	lsn, _ = c.Write(ctx, "insert into notes619 (body) values ('written while replay is paused')")
	fmt.Printf("2. replay paused: primary has %d rows, replica has %d\n", count(ctx, primary), count(ctx, replica))
	var lagBytes int64
	_ = primary.QueryRow(ctx, "select pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) from pg_stat_replication").Scan(&lagBytes)
	fmt.Println("   the primary sees replay lag of", lagBytes, "bytes in pg_stat_replication")

	// 3. Read your own write: the router checks the position the write returned.
	pool, which := c.ReadPool(ctx, lsn, 100*time.Millisecond)
	fmt.Printf("3. read after write while paused: served by the %s, sees %d rows\n", which, count(ctx, pool))

	if _, err := replica.Exec(ctx, "select pg_wal_replay_resume()"); err != nil {
		log.Fatal(err)
	}
	pool, which = c.ReadPool(ctx, lsn, time.Second)
	fmt.Printf("4. after resuming: served by the %s, sees %d rows\n", which, count(ctx, pool))
}

replica is in recovery: true
write to the replica: SQLSTATE 25006 cannot execute INSERT in a read-only transaction
1. replica read straight after a write was behind 4 times out of 300
2. replay paused: primary has 302 rows, replica has 301
   the primary sees replay lag of 200 bytes in pg_stat_replication
3. read after write while paused: served by the primary, sees 302 rows
4. after resuming: served by the replica, sees 302 rows

Cluster holds two pgx pools. Write runs a statement on the primary and then asks the primary for pg_current_wal_lsn(), the log position that includes the write. ReadPool is the router: given a position, it asks the replica for pg_last_wal_replay_lsn() >= position and returns the replica if it has caught up, waiting up to a limit and otherwise returning the primary. The output walks through what that achieves.

What the lab shows

The replica is read only. pg_is_in_recovery() is true, and an insert sent to it fails with SQLSTATE 25006, cannot execute INSERT in a read-only transaction. A service that sends a write to the wrong pool gets this error at once, which is a better failure than silently losing the write. It also means a pool for replicas must never be handed to code that writes, including a select ... for update, which counts as a write lock and fails the same way.

Replicas are stale, a little. For 300 rounds the program counted the primary's rows, inserted one, and immediately counted on the replica. The replica was behind 4 times out of 300. That is on one machine, with no network between the containers beyond Docker's bridge and one small write at a time. The replica applies the log asynchronously, so there is always a window between "committed on the primary" and "visible on the replica". On the same machine the window is tiny and still there. Across a network, under write load, or with a long query running on the replica, it grows from milliseconds to seconds or minutes.

Lag is visible and measurable. The program paused replay with pg_wal_replay_pause(), which simulates a replica that is behind, and wrote one more row. The primary then had 302 rows and the replica 301. pg_stat_replication on the primary reported the replica's replay position as 200 bytes behind. That view, with its write_lag, flush_lag and replay_lag columns, is what to export as a metric. Alert on replay lag in seconds, and decide for each read path how much it tolerates.

The router sends the read to the right place. With replay still paused, ReadPool was given the position returned by the last write. The replica had not replayed it, so the router waited 100 milliseconds and returned the primary, which showed 302 rows: the user's own write. After pg_wal_replay_resume(), the same call found the replica caught up almost at once and served the read from it, also 302 rows. That is read your writes consistency, with the replica taking the load whenever it is safe.

Read your writes

A user saves a profile and the next page shows the old one, because the page read from a replica that had not caught up. This is the most visible bug replicas cause, and it has several fixes, from simple to precise:

1. Send the read to the primary for a short time after a write. Set a cookie or an in memory marker for ten seconds after any write by that user, and route that user's reads to the primary until it expires. Easy, and it needs no database features. It sends more traffic to the primary than necessary and guesses the lag.
2. Route by the data. Reads in the same request as a write, and reads of what the user just changed, use the primary. Everything else, such as lists, search and public pages, uses a replica.
3. Track the log position (the lab's method). After a write, remember the position returned by pg_current_wal_lsn() for that user or session. Before a read, serve it from a replica only if pg_last_wal_replay_lsn() has reached that position, or wait briefly, or fall back to the primary. It is exact. It costs an extra query on the replica per read, so check once per request, not once per query, and remember that a position is per session: store it in the session or pass it in a header.
4. Synchronous replication makes the primary wait for a replica to confirm each commit. It removes the lag for that replica and slows every write by a round trip, and a replica that is down can stall writes. Use it for a failover standby, not as a read scaling tool.

Whichever you choose, write it down for each endpoint: "this reads from a replica and tolerates five seconds of lag" is a decision someone made, and "uses db" is an accident.

Wiring it into a Go service

Keep the routing in one small type and make the choice explicit at the call site. A Store holds both pools and exposes two methods, Writer() returning the primary and Reader(ctx) returning a replica or the primary. Repositories take what they need from it, so a method that reads for display calls Reader and a method that decides a write calls Writer. A single exported *sql.DB that everything uses makes the decision for you, and wrongly for half the calls.

Reader takes the context for a reason. The consistency token, the log position of the user's last write, travels with the request: a middleware reads it from a cookie or a header, puts it in the context, and Reader passes it to the check in the lab. After a write, the handler stores the new position in the response. The token is a short string such as 0/B1000148, safe to expose, and it makes read your writes work across several instances of the service, which the cookie style marker also does and an in memory marker does not.

Count which pool serves each request and export it with the replica lag. A router that quietly sends everything to the primary because the replica is always slightly behind is a router that has lost its benefit, and you want to see that in a graph and not in the next capacity review. If many reads fall back, loosen the wait, check the lag, or accept that those endpoints belong on the primary.

Testing a router

The lab is also the shape of the test. Run it against a real primary and replica, pause replay on the replica with pg_wal_replay_pause(), write, and assert that a read with the write's position goes to the primary and sees the row. Resume replay and assert that the same read goes to the replica. A mock cannot test this, because the behaviour under test is the database's own replay position. Run it in CI with the same two container setup, or against a staging pair, and keep one test that kills the replica entirely to prove reads fall back to the primary instead of failing.

Reads that must not go to a replica

• Any read inside a transaction that writes. The transaction is on the primary and so are its reads.
• Read then write decisions. A check on stale data followed by a write on the primary can make the wrong choice, such as selling stock the replica still shows as available. Do both on the primary, with the locks in SELECT FOR UPDATE.
• Uniqueness and validation checks done before an insert ("is this email taken?"). The constraint on the primary is the truth, so rely on it and handle the violation as in unique violation errors.
• Authorization and security state. A revoked permission or a changed password must take effect on the next request, so read them from the primary, or cache with a short life and invalidate on change.
• Anything just created and linked to immediately, such as a redirect to /orders/123 right after creating it. A 404 from a replica that has not seen order 123 is the classic symptom.

Operating replicas

Conflicts with long queries. A replica applies changes from the primary while it serves queries, and the two can conflict: replaying a vacuum that removes row versions a long running query still needs. PostgreSQL either delays replay (max_standby_streaming_delay) or cancels the query with canceling statement due to conflict with recovery. hot_standby_feedback tells the primary not to remove rows a replica query needs, at the price of table bloat on the primary. Long analytic queries are better on a dedicated replica configured for them, with a generous delay, than mixed in with the replica that serves your web traffic. Treat the cancellation error as retryable in Go.

Pools. A replica pool is its own *sql.DB or pgxpool.Pool, with its own limits. Size it from the replica's connection capacity, and remember that the replicas together must not receive more connections than their max_connections allows.

Failover. If the primary dies, a replica is promoted, and the others must follow the new one. Your connection strings should point at a name that a failover tool or a managed service moves, not at an address. After a failover, existing connections to the old primary fail, and the pool must reconnect: set ConnMaxLifetime so connections are replaced regularly, and make writes retry on the error classes that mean "wrong server". Test the procedure before the day it is needed.

Health checks. A replica with a stuck replay is worse than none, because it serves old data with no error. Check replay lag, not only that the port answers, and take a replica out of rotation above a threshold; see database health checks.

Cost. A replica doubles storage and needs the same compute as the primary to replay changes and serve reads. It adds read capacity and a standby for failover, and it adds nothing to write capacity, which is still one server. If writes are the bottleneck, you need a different tool: batching, better indexes, partitioning or sharding, as outlined in sharding and partitioning basics.

When not to use one

Before adding a replica, look for cheaper wins. An index that turns a scan into a seek, a query that stops fetching a thousand rows to display ten, a connection pool that stops starving, and a cache for the hot read often remove the need. A single well tuned PostgreSQL server handles far more than most services send it. Replicas also add a class of bug, stale reads, that you must handle everywhere, and that is a permanent cost for a temporary capacity problem. Add one when measured read load, or a need for a standby, justifies it.

The mechanisms are described in the PostgreSQL manual's chapters on high availability and replication and on hot standby, which covers the query conflict settings, and the monitoring view in the statistics views page.