Go Read Replica Routing with pgx and database/sql

Problem statement: you are writing Go against PostgreSQL with no ORM to hook into, and you need reads to reach replicas without a routing mistake becoming a data-correctness bug.

Go’s lack of ORM magic is an advantage here: routing becomes an explicit, testable function rather than framework behaviour to be discovered. What Go adds is a concurrency constraint — per-request state must travel in the context, never in package state. This page builds the router with that constraint respected throughout. The general shape is described in ORM middleware for automatic query routing.

Symptom Identification

  • A single *pgxpool.Pool is shared for everything, so every read reaches the primary regardless of the replicas that exist.
  • Routing state is held in a package-level variable and behaviour under load is not reproducible.
  • A SELECT occasionally runs on a replica while inside a transaction, producing errors that look random.
  • Lag is checked on the request path, so read latency rises during lag events even for reads that succeed.
  • There is no test that would fail if every read silently moved to the primary.

The second symptom deserves emphasis because it is specific to Go and severe. Concurrent requests share package state; a routing flag stored there is read and written by many goroutines at once, and the resulting misroutes are load-dependent and effectively unreproducible in development.

Root Cause Analysis

Go has no implicit per-request storage, and that is the design constraint. Frameworks in other languages hide routing state in thread-locals or request-scoped containers. Go offers context.Context, which is explicit, goroutine-safe, and passed down the call chain by convention. Anything that needs to vary per request — a routing hint, a freshness requirement, a read-your-writes watermark — belongs there and nowhere else.

Two pools are two independent resources. A primary pool and a replica pool have different sizing needs: the primary pool is bounded by write concurrency and by max_connections headroom; the replica pool is bounded by read concurrency across the whole replica set. Sharing one configuration for both means one of them is wrong.

Transactions are where an ORM-free approach can be made safest. In a dynamically-routed ORM, transaction coherence is a runtime property the router must enforce. In Go, you can make it a type property: if the transaction helper accepts only the primary pool, no caller can express a transaction on a replica. That is a stronger guarantee than any runtime check.

Lag checking on the request path is the common performance mistake. Querying each candidate replica’s lag before selecting means a read pays a round trip per candidate. During a lag event, when more candidates must be rejected, that cost rises exactly when latency is already under pressure — the mechanism described in attributing read latency to replica selection with traces.

Package-level routing state versus context-carried state under concurrency Two goroutines handle overlapping requests. In the first case, a package-level routing flag is set by goroutine A and read by goroutine B before A has finished, so B routes according to A's intent; the resulting misroute is load-dependent and unreproducible. In the second case, each request carries its routing hint in its own context, so the two goroutines cannot observe each other's state and each routes correctly. package-level flag — goroutines interfere goroutine A sets forcePrimary = true goroutine B reads forcePrimary — sees A's value B routes to the primary for no reason context-carried hint — goroutines are independent goroutine A ctx A: primary goroutine B ctx B: replica each routes by its own intent

Step-by-Step Resolution

Step 1 — Create separate pools

go
package db

import (
	"context"
	"github.com/jackc/pgx/v5/pgxpool"
)

type DB struct {
	primary  *pgxpool.Pool
	replicas []*pgxpool.Pool
	lag      *lagCache          // see step 4
}

func Open(ctx context.Context, primaryDSN string, replicaDSNs []string) (*DB, error) {
	pcfg, err := pgxpool.ParseConfig(primaryDSN)
	if err != nil {
		return nil, err
	}
	// Sized for write concurrency plus headroom, NOT the same as the read pool.
	pcfg.MaxConns = 20
	pcfg.MinConns = 4

	primary, err := pgxpool.NewWithConfig(ctx, pcfg)
	if err != nil {
		return nil, err
	}

	var reps []*pgxpool.Pool
	for _, dsn := range replicaDSNs {
		rcfg, err := pgxpool.ParseConfig(dsn)
		if err != nil {
			return nil, err
		}
		rcfg.MaxConns = 40                 // reads are the bulk of the traffic
		rcfg.MaxConnLifetime = 5 * 60e9    // recycle so the fleet re-balances
		p, err := pgxpool.NewWithConfig(ctx, rcfg)
		if err != nil {
			return nil, err
		}
		reps = append(reps, p)
	}
	return &DB{primary: primary, replicas: reps, lag: newLagCache(reps)}, nil
}

Inline verification: db.primary.Stat().MaxConns() and db.replicas[0].Stat().MaxConns() return the different values you configured.

Step 2 — Carry the routing hint in the context

go
type routeKey struct{}

type route struct {
	forcePrimary bool
	freshnessMS  int64          // 0 = no freshness requirement
}

// WithPrimary marks this context's reads as requiring the primary — for
// read-after-write, or any read that must not be stale.
func WithPrimary(ctx context.Context) context.Context {
	return context.WithValue(ctx, routeKey{}, route{forcePrimary: true})
}

// WithFreshness caps how stale a replica may be for reads on this context.
func WithFreshness(ctx context.Context, ms int64) context.Context {
	return context.WithValue(ctx, routeKey{}, route{freshnessMS: ms})
}

func routeFrom(ctx context.Context) route {
	r, _ := ctx.Value(routeKey{}).(route)   // zero value = replica-eligible
	return r
}

An unexported struct as the key is the standard idiom: it cannot collide with another package’s key, and callers cannot construct one, so the only way to set the hint is through the exported helpers.

Inline verification: go test -race ./db/... passes with a test that issues concurrent reads with different hints and asserts each gets its own.

Step 3 — Make transactions structurally primary-only

go
// Tx can only ever run on the primary: no replica pool is accepted, so a
// transaction on a replica is not expressible in the type system.
func (d *DB) Tx(ctx context.Context, fn func(pgx.Tx) error) error {
	tx, err := d.primary.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)                  // no-op after a successful commit
	if err := fn(tx); err != nil {
		return err
	}
	return tx.Commit(ctx)
}

// Query routes; Tx does not. Two entry points, one decision each.
func (d *DB) Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error) {
	return d.pick(ctx).Query(ctx, sql, args...)
}

Inline verification: grep the package for Begin( — it appears exactly once, inside Tx, on d.primary.

Step 4 — Add a background lag cache and the selector

go
type lagCache struct {
	mu   sync.RWMutex
	lag  []float64            // parallel to DB.replicas
	pool []*pgxpool.Pool
}

func newLagCache(pools []*pgxpool.Pool) *lagCache {
	c := &lagCache{lag: make([]float64, len(pools)), pool: pools}
	go c.poll(time.Second)
	return c
}

func (c *lagCache) poll(every time.Duration) {
	const q = `SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
	                       THEN 0 ELSE EXTRACT(EPOCH FROM
	                            (now() - pg_last_xact_replay_timestamp())) * 1000 END`
	for range time.Tick(every) {
		for i, p := range c.pool {
			ctx, cancel := context.WithTimeout(context.Background(), 900*time.Millisecond)
			var ms float64
			if err := p.QueryRow(ctx, q).Scan(&ms); err != nil {
				ms = math.Inf(1)        // fail closed: unknown lag is maximal lag
			}
			cancel()
			c.mu.Lock()
			c.lag[i] = ms
			c.mu.Unlock()
		}
	}
}

// pick runs on the request path and does NO network I/O.
func (d *DB) pick(ctx context.Context) queryer {
	r := routeFrom(ctx)
	if r.forcePrimary || len(d.replicas) == 0 {
		return d.primary
	}
	budget := float64(r.freshnessMS)
	if budget == 0 {
		budget = math.Inf(1)
	}
	d.lag.mu.RLock()
	defer d.lag.mu.RUnlock()
	start := int(rand.Int31n(int32(len(d.replicas))))     // spread the starting point
	for i := range d.replicas {
		j := (start + i) % len(d.replicas)
		if d.lag.lag[j] <= budget {
			return d.replicas[j]
		}
	}
	return d.primary                                       // fallback_lag
}

Inline verification: benchmark pick — it should be in the hundreds of nanoseconds, confirming no I/O on the request path.

Step 5 — Assert the destination in tests

go
func servedBy(t *testing.T, ctx context.Context, d *DB) string {
	t.Helper()
	var inRecovery bool
	if err := d.QueryRow(ctx, "SELECT pg_is_in_recovery()").Scan(&inRecovery); err != nil {
		t.Fatal(err)
	}
	if inRecovery {
		return "replica"
	}
	return "primary"
}

func TestReadsGoToReplica(t *testing.T) {
	if got := servedBy(t, context.Background(), testDB); got != "replica" {
		t.Fatalf("plain read reached %s, want replica", got)
	}
}

func TestWithPrimaryForcesPrimary(t *testing.T) {
	if got := servedBy(t, WithPrimary(context.Background()), testDB); got != "primary" {
		t.Fatalf("WithPrimary read reached %s, want primary", got)
	}
}

Inline verification: both tests pass against a real primary/standby pair and fail if pick is changed to always return d.primary.

Configuration Snippet

go
// HTTP middleware that sets the routing hint once per request, so handlers
// do not each have to remember. The default is replica-eligible; specific
// routes opt into stricter guarantees.
func RoutingMiddleware(next http.Handler) http.Handler {
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		ctx := r.Context()

		switch {
		case r.Method != http.MethodGet && r.Method != http.MethodHead:
			// Non-idempotent methods write; keep the whole request coherent.
			ctx = db.WithPrimary(ctx)

		case r.Header.Get("X-Read-Your-Writes") != "":
			// The client just wrote and needs to see it.
			ctx = db.WithPrimary(ctx)

		default:
			// Per-path freshness budget; falls back to the primary if no
			// replica meets it. Sourced from one config, not scattered.
			ctx = db.WithFreshness(ctx, freshnessBudgetMS(r.URL.Path))
		}

		next.ServeHTTP(w, r.WithContext(ctx))
	})
}

// Pool statistics are the cheapest observability available — export them.
func ExportPoolStats(d *db.DB, reg prometheus.Registerer) {
	reg.MustRegister(prometheus.NewGaugeFunc(
		prometheus.GaugeOpts{Name: "db_pool_acquired_conns", ConstLabels: prometheus.Labels{"role": "replica"}},
		func() float64 { return float64(d.ReplicaStat().AcquiredConns()) },
	))
	reg.MustRegister(prometheus.NewGaugeFunc(
		prometheus.GaugeOpts{Name: "db_pool_empty_acquire_total", ConstLabels: prometheus.Labels{"role": "replica"}},
		func() float64 { return float64(d.ReplicaStat().EmptyAcquireCount()) },
	))
}

EmptyAcquireCount is the metric worth watching above all the others in pgxpool.Stat. It counts acquisitions that had to wait because no idle connection was available, which is precisely the pool-checkout queueing that shows up as p99 latency with no corresponding database slowness. A rising value is the signal to raise MaxConns — or, if the database is at its own connection limit, to add a pooler.

Setting the hint in middleware rather than in each handler matters for consistency: a handler that forgets is a handler that quietly gets the default, and the default is the permissive one. Centralising it means the decision is visible in one file and reviewable as a unit.

The pick function: four inputs, no I/O The pick function takes three inputs from the request context — force primary, freshness budget, and whether a transaction is active — plus one input from the background lag cache. It returns either a replica pool or the primary pool. A note marks that all four inputs are already in memory, so the function performs no network calls and executes in hundreds of nanoseconds. inputs (all already in memory) ctx: forcePrimary ctx: freshnessMS in a transaction? (type-level) lagCache: []float64 pick(ctx) no network calls a replica pool first one inside the budget the primary pool forced, or no replica qualifies Because the lag view is refreshed out of band, pick costs hundreds of nanoseconds regardless of fleet size. Measuring lag inside pick would make every read pay a round trip per rejected candidate.

Verification and Rollback

Confirm concurrency safety, which is the failure mode unique to this language:

bash
# The race detector catches shared routing state that a functional test will not.
go test -race -count=5 ./internal/db/...
go
func TestConcurrentRoutingIsIndependent(t *testing.T) {
	var wg sync.WaitGroup
	for i := 0; i < 200; i++ {
		wg.Add(1)
		go func(i int) {
			defer wg.Done()
			ctx := context.Background()
			want := "replica"
			if i%2 == 0 {
				ctx, want = WithPrimary(ctx), "primary"
			}
			if got := servedBy(t, ctx, testDB); got != want {
				t.Errorf("goroutine %d reached %s, want %s", i, got, want)
			}
		}(i)
	}
	wg.Wait()
}

Confirm the pools are being used as intended:

promql
# Replica pool should carry the bulk of acquisitions in a read-heavy service.
sum by (role) (rate(db_pool_acquire_total[5m]))

# Waiting for a connection — the pool-sizing signal.
rate(db_pool_empty_acquire_total{role="replica"}[5m])

Rollback. Because routing is one function, reverting is one line:

go
func (d *DB) pick(ctx context.Context) queryer {
	return d.primary          // TEMPORARY: route everything to the primary
}

This is a genuine and useful emergency action — during a replication incident, forcing all reads to the primary trades capacity for correctness immediately, with no config deploy and no ambiguity about what is in effect. Make sure the primary pool is sized to survive it, or the rollback becomes its own incident; sizing guidance is in connection pool architecture for read replicas.

Edge Cases and Gotchas

context.WithValue chains overwrite rather than merge

Calling WithPrimary and then WithFreshness on the same context replaces the whole route struct, silently discarding the first hint. Either make the setters read the existing value and return a modified copy, or expose a single constructor that takes all fields. The bug is easy to write and produces exactly the wrong behaviour — a read that was supposed to be forced to the primary quietly becoming replica-eligible.

time.Tick leaks if the poller ever needs to stop

for range time.Tick(d) never releases its ticker, which is fine for a poller that lives for the process lifetime and a leak in a test that creates many DB values. Use time.NewTicker with a defer ticker.Stop() and a done channel if the type can be closed, and add a Close method that stops the goroutine.

A cancelled request context cancels the query, not the routing

If the caller’s context is cancelled mid-query, pgx aborts the query — which is correct — but a naive retry that re-runs pick may select a different node, and if the first attempt actually committed something the retry is not idempotent. Keep retries on the read path only, where they are safe, and never retry a statement that ran inside Tx.

FAQ

Why not use a package-level variable for the routing hint?

Because Go serves concurrent requests in separate goroutines sharing package state, so a flag set by one request is visible to every other. The result is non-deterministic routing that is nearly impossible to reproduce. The request context is per-goroutine by construction and is the only correct carrier for this state.

Should I use pgxpool or database/sql?

pgxpool if you are PostgreSQL-only, because it exposes the protocol features and pool statistics you want for replica routing and avoids a conversion layer. database/sql if you need driver portability or an existing ecosystem of wrappers. The routing pattern is identical in both; only the pool type and statistics API differ.

How should transactions be handled?

Make them structurally incapable of using a replica. Expose a helper that accepts only the primary pool and returns the transaction, so there is no code path in which a caller can begin a transaction on a replica pool. That converts a runtime correctness concern into a compile-time one.

Where should the lag check live?

In a background goroutine that refreshes a cached value, never on the request path. Measuring lag synchronously means every read pays a round trip per candidate replica, which turns a lag event into a latency event even for reads served successfully. A ticker at one-second intervals costs one query per replica per second regardless of traffic.

← Back to ORM Middleware for Automatic Query Routing