2026-09-14 · 11 min
Database Read Replica Routing with Prisma in Production: Route Reads Without Serving Stale Data
Your primary Postgres is at 80% CPU during evening peak, and 90% of the queries hitting it are reads. That is the moment database read replica routing stops being an architecture-diagram nicety and becomes the thing standing between you and a 2 AM page. This post is about wiring read replica routing into a Prisma + PostgreSQL service in production — routing reads to replicas, keeping writes on the primary, and not shipping the stale-read bugs that come free with the setup.
I run this on a payments-adjacent service — the team sits in an office off Jalan Pahlawan Seribu in BSD, close enough to the ruko strip near ITC Serpong that lunch debates about replication lag are a real thing. The primary lives in a managed Postgres cluster with two replicas behind it. The read/write split cut primary CPU roughly in half. It also introduced two production incidents in the first month, both of which were entirely my fault and entirely predictable. I’ll get to those.
When you actually need read replicas (and when you don’t)
Replicas solve exactly one problem: too much read load on a single primary. They do nothing for write throughput — every write still funnels to the primary and then streams to replicas over WAL. So before adding this complexity, confirm the shape of your load.
Check it directly:
-- Read vs write ratio from pg_stat_statements
SELECT
sum(calls) FILTER (WHERE query ILIKE 'select%') AS read_calls,
sum(calls) FILTER (WHERE query ILIKE 'insert%'
OR query ILIKE 'update%'
OR query ILIKE 'delete%') AS write_calls
FROM pg_stat_statements;
If reads dwarf writes and your primary is CPU- or connection-bound on those reads, replicas help. If your bottleneck is write-heavy (bulk imports, high-frequency updates), replicas make things worse — you now have the same write load plus replication overhead. And if a handful of unindexed queries are eating your CPU, fix those first. I have watched a team stand up two replicas to dodge a single sequential scan on a 40-million-row table that a composite index would have killed in an afternoon.
The Prisma read replicas extension
The official extension is the fastest path. It ships as @prisma/extension-read-replicas.
npm install @prisma/extension-read-replicas
// db.ts
import { PrismaClient } from "@prisma/client";
import { readReplicas } from "@prisma/extension-read-replicas";
const prisma = new PrismaClient().$extends(
readReplicas({
url: [
process.env.REPLICA_URL_1!,
process.env.REPLICA_URL_2!,
],
})
);
export { prisma };
That is the whole setup. With this in place, the extension applies a set of routing rules automatically:
- Read operations (
findMany,findFirst,findUnique,count,aggregate,groupBy,$queryRaw) go to a replica, chosen round-robin when you pass more than one URL. - Write operations (
create,update,delete,upsert,$executeRaw) go to the primary. - Every transaction — batch or interactive — goes to the primary.
You override per call with two escape hatches: prisma.$primary() forces a read onto the primary, and prisma.$replica() forces a query onto a replica. Those two methods are where the real work is.
Incident #1: read-after-write staleness
The first bug shipped within a week. A user edits their profile, we update on the primary, then the very next handler reads the profile back to render the confirmation screen — and that read lands on a replica that hasn’t caught up yet. The user sees their old display name after saving. Support tickets, confusion, the works.
Replication is asynchronous. On our cluster the lag sits around 30-60ms under normal load and spikes past 300ms during heavy write bursts. Any read that must reflect a write from the same request cannot go to a replica inside that window.
The fix is surgical, not global. Send that one read to the primary:
// After a write, read-your-own-writes must hit the primary.
async function updateProfile(userId: string, data: ProfileInput) {
await prisma.user.update({
where: { id: userId },
data,
});
// This read reflects the write we just made — force primary.
return prisma.$primary().user.findUniqueOrThrow({
where: { id: userId },
});
}
The instinct is to slap $primary() on everything to be safe. Resist it. If most reads go to the primary, you have paid for replicas and gotten nothing. The discipline is to identify the small set of handlers where the user reads data they just mutated — profile edits, an order right after checkout, a comment they just posted — and scope $primary() to only those. A feed, a search result, someone else’s timeline: 200ms of staleness there is invisible.
Incident #2: the accidental primary transaction
The second one was subtler. We had a read-only reporting query — three findMany calls that populate a dashboard — and someone wrapped them in $transaction([...]) for batching convenience. It looked like a read. It ran on the primary, because all transactions run on the primary, and it hammered the primary during business hours exactly when we were trying to offload it.
If a batch of reads genuinely belongs on a replica, do not wrap it in $transaction. Either issue the queries independently, or force the replica explicitly:
// Read-only dashboard: keep it off the primary.
const [orders, revenue, topProducts] = await Promise.all([
prisma.$replica().order.findMany({ where: { status: "PAID" } }),
prisma.$replica().order.aggregate({ _sum: { total: true } }),
prisma.$replica().product.findMany({ orderBy: { sold: "desc" }, take: 10 }),
]);
Audit your $transaction calls before rollout. Any read-only one is silently loading your primary.
Pooling: replicas make your connection math worse
Here is the part people skip. Each replica is a separate Postgres server with its own max_connections. Point a Node process with a pool of 10 at a primary plus two replicas and you now hold connections against three servers, not one. Serverless or autoscaled instances multiply this fast — 40 instances times 3 servers times a small pool and you exhaust max_connections on a replica while the primary looks fine, which produces a baffling “half our reads are timing out” incident.
Put a pooler in front of every node, primary and replicas alike. We run PgBouncer in transaction mode:
; pgbouncer.ini
[databases]
primary = host=primary.internal port=5432 dbname=app
replica_1 = host=replica-1.internal port=5432 dbname=app
replica_2 = host=replica-2.internal port=5432 dbname=app
[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
Then your REPLICA_URL_* values point at the PgBouncer ports, not directly at Postgres. One caveat that bites Prisma users specifically: transaction-mode pooling does not support session-level features like prepared statements the way session mode does, so append ?pgbouncer=true to your Prisma connection strings to disable prepared statements. Skip that flag and you get intermittent prepared statement "s0" already exists errors under load — the kind that never reproduce locally.
Guard against a lagging replica
A replica that falls far behind is worse than no replica, because it serves confidently wrong data. Monitor lag and be ready to pull a replica out of rotation. From the primary:
-- Replication lag per connected replica, in bytes
SELECT
client_addr,
state,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;
Alert when lag_bytes crosses a threshold you have actually tested against — for our write volume, sustained lag above ~8MB means a replica is minutes behind, and reads there are dangerous. When that fires, the operational move is to drop that replica’s URL from the extension config and redeploy, or if you front replicas with a load balancer, mark it unhealthy so it stops receiving reads. Design for this before you need it; a lagging replica during peak is not the time to be reading the Postgres docs.
The rule I wish I’d started with
Default every read to a replica. Force $primary() only for reads that immediately follow a write in the same request. Never wrap read-only work in a transaction if you want it on a replica. Pool every node. Monitor lag and have a one-line path to eject a bad replica.
Read replicas are not a scaling silver bullet — they buy read capacity at the cost of consistency you now have to reason about explicitly. For a read-heavy Prisma service pinned on primary CPU, that trade is worth it. For a write-heavy one, or one where a missing index is the real culprit, you are adding operational surface area to solve a problem you do not have. Measure the read/write ratio first. Then, and only then, split.
Ditulis oleh Reza Pradipta