Route Read Queries to a PostgreSQL Replica Safely

1. Confirm the replica is in recovery and streaming, and record the current replay lag before routing traffic.

psql "$DATABASE_URL_PRIMARY" -Atc "SELECT pg_is_in_recovery();"
psql "$DATABASE_URL_REPLICA" -Atc "SELECT pg_is_in_recovery();"
psql "$DATABASE_URL_PRIMARY" -c "SELECT application_name, state, replay_lag, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind FROM pg_stat_replication;"

Expected output: The primary returns f, the replica returns t, and the replication row shows state streaming with a small replay_lag and bytes_behind.

2. Create two pools, one request-scoped routing state, and explicit helpers for reads, writes, and primary-only transactions.

import { AsyncLocalStorage } from 'node:async_hooks';
import pg from 'pg';

const primary = new pg.Pool({
  connectionString: process.env.DATABASE_URL_PRIMARY,
  max: 20,
});

const replica = new pg.Pool({
  connectionString: process.env.DATABASE_URL_REPLICA,
  max: 20,
  application_name: 'app-replica-1',
});

type RouteState = { pinnedToPrimary: boolean };
const routeState = new AsyncLocalStorage<RouteState>();

export function withRequestRouting<T>(fn: () => T): T {
  return routeState.run({ pinnedToPrimary: false }, fn);
}

export function pinToPrimary(): void {
  const state = routeState.getStore();
  if (state) state.pinnedToPrimary = true;
}

export async function readQuery<T extends pg.QueryResultRow>(
  text: string,
  params: unknown[] = [],
): Promise<pg.QueryResult<T>> {
  const pool = routeState.getStore()?.pinnedToPrimary ? primary : replica;
  return pool.query<T>(text, params);
}

export async function writeQuery<T extends pg.QueryResultRow>(
  text: string,
  params: unknown[] = [],
): Promise<pg.QueryResult<T>> {
  return primary.query<T>(text, params);
}

export async function withTransaction<T>(
  fn: (client: pg.PoolClient) => Promise<T>,
): Promise<T> {
  const client = await primary.connect();
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

Expected output: Reads use the replica by default, writes and transactions use the primary, and pinToPrimary can force later reads in the same request to the primary.

3. Add a cached lag guard that falls back to the primary when the standby is disconnected, behind, or too old.

let lagCache = { checkedAt: 0, fresh: false };
const LAG_TTL_MS = 5_000;
const MAX_LAG_MS = 2_000;
const MAX_LAG_BYTES = 16 * 1024 * 1024;

async function replicaIsFresh(): Promise<boolean> {
  if (Date.now() - lagCache.checkedAt < LAG_TTL_MS) return lagCache.fresh;

  try {
    const { rows } = await primary.query<{
      lag_ms: string | null;
      bytes_behind: string | null;
    }>(
      `SELECT
         EXTRACT(EPOCH FROM replay_lag) * 1000 AS lag_ms,
         pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind
       FROM pg_stat_replication
       WHERE application_name = $1 AND state = 'streaming'`,
      ['app-replica-1'],
    );

    if (!rows[0]) {
      lagCache = { checkedAt: Date.now(), fresh: false };
      return false;
    }

    const lagMs = rows[0].lag_ms === null ? 0 : Number(rows[0].lag_ms);
    const bytesBehind =
      rows[0].bytes_behind === null ? Number.NaN : Number(rows[0].bytes_behind);
    const fresh =
      Number.isFinite(lagMs) &&
      Number.isFinite(bytesBehind) &&
      lagMs <= MAX_LAG_MS &&
      bytesBehind <= MAX_LAG_BYTES;

    lagCache = { checkedAt: Date.now(), fresh };
    return fresh;
  } catch {
    lagCache = { checkedAt: Date.now(), fresh: false };
    return false;
  }
}

Expected output: A disconnected or lagging standby makes replicaIsFresh return false for the next five seconds, so reads are served by the primary.

4. Update readQuery to consult the lag guard and pin the request to the primary immediately after a write.

export async function readQuery<T extends pg.QueryResultRow>(
  text: string,
  params: unknown[] = [],
): Promise<pg.QueryResult<T>> {
  const pinned = routeState.getStore()?.pinnedToPrimary === true;
  const pool = pinned || !(await replicaIsFresh()) ? primary : replica;
  return pool.query<T>(text, params);
}

// app.ts
import express from 'express';
import { pinToPrimary, readQuery, withRequestRouting, writeQuery } from './db.js';

const app = express();
app.use(express.json());
app.use((_req, _res, next) => withRequestRouting(next));

app.patch('/profiles/:id', async (req, res) => {
  const { rows } = await writeQuery<{ user_id: string; display_name: string }>(
    `UPDATE app.profiles
       SET display_name = $1
     WHERE user_id = $2
     RETURNING user_id, display_name`,
    [req.body.displayName, req.params.id],
  );

  if (rows.length === 0) {
    res.status(404).json({ error: 'not found' });
    return;
  }

  pinToPrimary();
  const profile = await readQuery<{ user_id: string; display_name: string }>(
    'SELECT user_id, display_name FROM app.profiles WHERE user_id = $1',
    [req.params.id],
  );
  res.json(profile.rows[0]);
});

Expected output: The PATCH response reads the updated row from the primary rather than racing replication on the replica.

5. Verify routing with a real profile row: the primary must report the write, the replica must be in recovery, and the immediate PATCH read must return the new value.

psql "$DATABASE_URL_PRIMARY" <<'SQL'
INSERT INTO app.profiles (user_id, display_name)
VALUES ('11111111-1111-4111-8111-111111111111', 'Before')
ON CONFLICT (user_id) DO UPDATE SET display_name = EXCLUDED.display_name;
SQL
curl -s -X PATCH http://localhost:3000/profiles/11111111-1111-4111-8111-111111111111 \
  -H 'content-type: application/json' \
  -d '{"displayName":"Ada Lovelace"}' | jq -r '.display_name'
psql "$DATABASE_URL_REPLICA" -Atc "SELECT display_name FROM app.profiles WHERE user_id = '11111111-1111-4111-8111-111111111111';"

Expected output: The PATCH response prints Ada Lovelace immediately; the replica query prints Ada Lovelace once replication catches up.

Problem

Queries fail with cannot execute UPDATE in a read-only transaction

Solution

A write reached the replica pool. Route every mutation and every transaction that may mutate through writeQuery or withTransaction, never through readQuery.

Problem

A user sees an old value immediately after saving

Solution

The request did not pin to the primary, or a second service handled the follow-up read. Pin the request after writes and use primary reads for user-visible consistency.

Problem

The replica pool keeps failing after a failover

Solution

Pools hold connections to the old host. Recreate pools from refreshed topology data, or let an external failover manager restart the service.