Your user clicks Create Project. The API writes to the primary Postgres, returns 201 Created, and the frontend redirects to /projects/42. The project detail endpoint reads from the replica. It gets back zero rows. The UI renders a 404 page. The user refreshes twice. On the third refresh, the project appears. They open a support ticket that reads “your app is broken.”

This is the read replica tax. You added replicas to take load off the primary, a good decision. Then you pointed SELECT queries at them, also correct. What most teams miss is that replication is asynchronous. Even under normal load, a read replica can lag by 1–50 ms. Under load, batch replication, or vacuum activity, that lag can stretch to hundreds of milliseconds or seconds. The user does not care about your architecture diagram. They care that their data is gone.

This post shows the routing pattern that keeps the performance win without the stale-data bugs. It is three pieces: measuring lag, request-scoped write tracking, and a fallback gate. All of it is boring infrastructure code that pays rent every day.

The naive split and why it fails

The first implementation is almost always a connection pool split:

// pools.js
import pg from 'pg';
export const primaryPool = new pg.Pool({ connectionString: process.env.PRIMARY_URL });
export const replicaPool = new pg.Pool({ connectionString: process.env.REPLICA_URL });

// usage
export async function getProject(id) {
  const { rows } = await replicaPool.query('SELECT * FROM projects WHERE id = $1', [id]);
  return rows[0];
}

export async function createProject(data) {
  const { rows } = await primaryPool.query(
    'INSERT INTO projects (name) VALUES ($1) RETURNING *',
    [data.name]
  );
  return rows[0];
}

This works in unit tests because latency is zero. In production it fails whenever:

• A write is followed by a read in the same request (create-then-fetch, update-then-list).
• A background job enqueues work that reads its own write.
• A webhook handler writes state, then a provider immediately queries for it.
• Replication lag spikes because of a long-running transaction on the primary or a replica vacuum.

The bug is not intermittent. It is deterministic under the right timing, which means it will show up in demos, user onboarding flows, and integration tests running in CI with real infrastructure.

Measure before you route

You cannot fix what you do not measure. Postgres exposes replication lag in two useful ways. On the replica:

SELECT
  now() - pg_last_xact_replay_timestamp() AS lag;

This gives you wall-clock lag, but pg_last_xact_replay_timestamp() returns null if no transaction has replayed since the replica started. A more robust metric, if you have pg_stat_replication access on the primary, is:

-- run on the primary
SELECT
  client_addr,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication;

For application-layer routing, the simplest approach is a heartbeat table. Create it on the primary:

CREATE TABLE replication_heartbeat (
  id INT PRIMARY KEY,
  tick TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO replication_heartbeat (id) VALUES (1)
ON CONFLICT (id) DO NOTHING;

Update it every few seconds from a background process:

setInterval(async () => {
  await primaryPool.query(
    'UPDATE replication_heartbeat SET tick = now() WHERE id = 1'
  );
}, 5000);

Query it from the replica and compare:

async function getReplicaLagMs() {
  const primary = await primaryPool.query(
    'SELECT tick FROM replication_heartbeat WHERE id = 1'
  );
  const replica = await replicaPool.query(
    'SELECT tick FROM replication_heartbeat WHERE id = 1'
  );
  if (!primary.rows[0] || !replica.rows[0]) return Infinity;
  const lag = primary.rows[0].tick - replica.rows[0].tick;
  return lag; // milliseconds in pg
}

If lag exceeds a threshold (say 100 ms) you stop routing to the replica until it catches up. This is your circuit breaker. It is coarse, but it prevents the worst-case scenario where a lagging replica serves minutes-old data during a failover or heavy batch load.

Request-scoped routing: the write tracker

The real fix is tracking writes inside a single request and routing subsequent reads to the primary for that request only. This gives you read-after-write consistency without abandoning replicas entirely.

In Express, use AsyncLocalStorage to carry a “writes seen” flag:

import { AsyncLocalStorage } from 'node:async_hooks';

const requestStorage = new AsyncLocalStorage();

export function withRequestContext(handler) {
  return (req, res, next) => {
    const store = { wroteToPrimary: false };
    requestStorage.run(store, () => handler(req, res, next));
  };
}

export function markWrite() {
  const store = requestStorage.getStore();
  if (store) store.wroteToPrimary = true;
}

export function shouldUsePrimary() {
  const store = requestStorage.getStore();
  return store ? store.wroteToPrimary : false;
}

Wrap your route handlers:

import express from 'express';
import { withRequestContext, markWrite, shouldUsePrimary } from './context.js';

const app = express();
app.use(withRequestContext);

Update your data layer to mark writes:

export async function createProject(data) {
  markWrite();
  const { rows } = await primaryPool.query(
    'INSERT INTO projects (name) VALUES ($1) RETURNING *',
    [data.name]
  );
  return rows[0];
}

And choose the pool dynamically:

export function getPool() {
  return shouldUsePrimary() ? primaryPool : replicaPool;
}

export async function getProject(id) {
  const pool = getPool();
  const { rows } = await pool.query('SELECT * FROM projects WHERE id = $1', [id]);
  return rows[0];
}

Now the flow works: POST /projects calls createProject, which sets wroteToPrimary = true. The redirect to GET /projects/42 calls getProject, sees the flag, and reads from the primary. The project is there. Every other request on the system that has not written continues to hit the replica.

This is not session stickiness. Session stickiness pins a user to one database for their entire session, which defeats the purpose of replicas if the user is write-heavy. Request-scoped routing is narrower: it lasts for one HTTP request only.

The lag fallback

Combine the write tracker with a lag gate. Even if a request has not written, you may want to avoid the replica when it is unhealthy:

let cachedLagMs = 0;
let lagCheckedAt = 0;

async function getPool() {
  const store = requestStorage.getStore();
  if (store?.wroteToPrimary) return primaryPool;

  // Cache lag for 1s to avoid hammering the heartbeat query
  if (Date.now() - lagCheckedAt > 1000) {
    cachedLagMs = await getReplicaLagMs();
    lagCheckedAt = Date.now();
  }

  if (cachedLagMs > 100) {
    return primaryPool; // fallback
  }

  return replicaPool;
}

Pick a threshold that matches your product requirements. A 100 ms lag gate is safe for most web apps. A 5 ms gate is appropriate for high-frequency trading. A 5 second gate is appropriate only if your users already expect eventual consistency.

Expanding the write tracker

Not every read-after-write pattern is inside one request. Background jobs often write, then read. For those, pass an explicit consistency hint:

export async function getProject(id, { consistent = false } = {}) {
  const pool = consistent || shouldUsePrimary() ? primaryPool : replicaPool;
  const { rows } = await pool.query('SELECT * FROM projects WHERE id = $1', [id]);
  return rows[0];
}

Use it in the job:

const project = await createProject(data);
const full = await getProject(project.id, { consistent: true });
await enqueueIndexJob(full);

Another expansion: mark writes for the whole request if the user is an admin or if the endpoint is known to be write-heavy. The mechanism is the same: set the flag early, route everything to the primary for that request.

Testing the stale-data path

A test suite that only mocks the database will never catch replication lag bugs. You need an integration test that talks to a real primary and replica. In CI, use Docker Compose:

services:
  postgres_primary:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: secret
    volumes:
      - ./primary-init.sql:/docker-entrypoint-initdb.d/init.sql

  postgres_replica:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: secret
    depends_on:
      - postgres_primary

Set up streaming replication in the init scripts, then write a test that deliberately introduces lag. The simplest way is to pause replication:

-- on the replica
SELECT pg_wal_replay_pause();

Then run your create-and-fetch flow. Assert that without the write tracker, the fetch returns null. Assert that with the write tracker, it returns the record from the primary. Then resume:

SELECT pg_wal_replay_resume();

If you cannot pause replication in your test environment, simulate lag by adding a pg_sleep(0.1) in the replica query path and running the fetch immediately after the write. It is not identical, but it exercises the routing logic.

Monitoring that matters

Add three metrics:

1. Query routing split: count of reads on primary vs replica. A healthy system routes 80–95% of reads to replicas. If that drops below 50%, either your write tracker is too aggressive or your replicas are constantly lagged.

2. Replication lag histogram: track the lag distribution, not just the average. P99 lag is what breaks user flows. Alert when P99 exceeds your threshold.

3. Fallback rate: how often the lag gate forces a read to the primary. A spike here is a leading indicator of replica trouble.

// pseudo-metric with any client
replicaLagHistogram.observe(lagMs);
readRoutingCounter.inc({ destination: shouldUsePrimary() ? 'primary' : 'replica' });

Do not alert on single-request 404s. Those are symptoms. Alert on lag and fallback rate; fix the root cause before users notice.

The full middleware

Here is the minimal Express integration in one block:

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

const requestStorage = new AsyncLocalStorage();

const primaryPool = new pg.Pool({ connectionString: process.env.PRIMARY_URL });
const replicaPool = new pg.Pool({ connectionString: process.env.REPLICA_URL });

export function dbMiddleware(req, res, next) {
  requestStorage.run({ wroteToPrimary: false }, next);
}

export function markWrite() {
  const store = requestStorage.getStore();
  if (store) store.wroteToPrimary = true;
}

export async function query(sql, params) {
  const store = requestStorage.getStore();
  const pool = store?.wroteToPrimary ? primaryPool : replicaPool;
  return pool.query(sql, params);
}

// In routes:
// app.use(dbMiddleware);
// app.post('/projects', async (req, res) => { markWrite(); ... });
// app.get('/projects/:id', async (req, res) => { const r = await query(...); ... });

This is fewer than forty lines. The hard part is not the code. It is accepting that replication lag is not a bug you can eliminate; it is a property of the system you must design around.

When to not use replicas at all

Read replicas are not free. They add network hops, operational complexity, failover logic, and the exact class of bug this post describes. If your primary is under 50% CPU and your working set fits in RAM, you may not need replicas yet. A single large Postgres instance with good indexes and query tuning will outperform a poorly routed replica setup every time.

Add replicas when:

• Read CPU on the primary is consistently above 70%.
• You have long analytical queries that would stall OLTP traffic.
• You need a hot standby for failover.

Do not add them because a blog post told you to scale horizontally. Horizontal scaling is a liability until you actually need it.

The takeaway

The pattern is: write to primary, track writes per request, read from primary for the rest of that request, read from replica otherwise, and fall back to primary when lag is high. That gives you almost all the performance of replicas without the stale-data bugs.

Build the write tracker before you need it. The incident that forces you to build it under pressure will happen on a Friday, during a product demo, and the CEO will be the one who sees the 404. Build it on a Tuesday instead.

A note from Yojji

Database infrastructure that actually works under load (replication, routing, failover, and the monitoring that tells you when it is lying) is the kind of backend work that looks invisible when it is done right and existential when it is not.

Yojji is an international custom software development company founded in 2016, with offices across Europe, the US, and the UK. Their teams build custom web applications and scalable backend systems with the same focus on operational correctness, query performance, and infrastructure reliability that keeps read replicas useful instead of dangerous.