Named DataSources, replica routing via an interceptor, failover, and keeping transactions on the primary so reads see your own writes.
The first time I wired up a reader replica behind a NestJS service, I broke read-your-writes within an hour. A user updated their profile. The next page load hit the reader. The replica hadn’t caught up. Support thought it was a caching bug. It wasn’t. The reader was doing its job, and we’d handed it a request we should not have.
That was at a community and talent product I CTO on the side. Postgres on AWS, NestJS, a single primary and a single reader. Once you have more than one DataSource, you’ve signed up for a pile of decisions, and most of them are about consistency, not throughput.
Here’s how I set it up now. Named DataSources, a routing interceptor that defaults to primary, transactions pinned to the writer, and a failover path that doesn’t pretend the reader is the writer.
First thing I do is stop using the default connection. Default connections drift. Someone imports a repository the wrong way, and half your queries end up on whichever DataSource was loaded last. Name them. Both of them.
import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { ConfigModule, ConfigService } from '@nestjs/config';
@Module({
imports: [
ConfigModule.forRoot(),
TypeOrmModule.forRootAsync({
name: 'primary',
inject: [ConfigService],
useFactory: (cfg: ConfigService) => ({
type: 'postgres',
url: cfg.getOrThrow<string>('PRIMARY_DATABASE_URL'),
entities: [__dirname + '/**/*.entity{.ts,.js}'],
synchronize: false,
logging: ['error', 'warn'],
extra: { max: 20, statement_timeout: 5000 },
}),
}),
TypeOrmModule.forRootAsync({
name: 'replica',
inject: [ConfigService],
useFactory: (cfg: ConfigService) => ({
type: 'postgres',
url: cfg.getOrThrow<string>('REPLICA_DATABASE_URL'),
entities: [__dirname + '/**/*.entity{.ts,.js}'],
synchronize: false,
logging: ['error'],
extra: { max: 40, statement_timeout: 3000 },
}),
}),
],
})
export class DatabaseModule {}Two things worth saying out loud. The replica pool is bigger because reads dominate. The replica’s statement timeout is tighter because a slow read is something I’d rather kill than let pile up. Writes get a longer leash because they’re rarer and they’re the ones that actually matter when they hang.
Repositories get injected by name. No magic.
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { Post } from './post.entity';
@Injectable()
export class PostsService {
constructor(
@InjectRepository(Post, 'primary') private readonly writer: Repository<Post>,
@InjectRepository(Post, 'replica') private readonly reader: Repository<Post>,
) {}
async create(authorId: string, body: string) {
return this.writer.save(this.writer.create({ authorId, body }));
}
async listForAuthor(authorId: string) {
return this.reader.find({ where: { authorId }, take: 50, order: { createdAt: 'DESC' } });
}
}Enough for a small service. Not enough once you have more than a couple of controllers, because every developer has to remember which repository is which. They won’t. I didn’t.
The pattern I land on: every request runs against the primary by default, individual routes opt into the replica. Defaulting to safe and opting into fast is the right way around. The other direction breaks production.
AsyncLocalStorage carries a dataSource per request, and a small decorator flips it.
import { AsyncLocalStorage } from 'node:async_hooks';
import { Injectable, NestInterceptor, ExecutionContext, CallHandler, SetMetadata } from '@nestjs/common';
import { Reflector } from '@nestjs/core';
import { Observable } from 'rxjs';
type Ctx = { dataSource: 'primary' | 'replica' };
export const dbContext = new AsyncLocalStorage<Ctx>();
export const READ_REPLICA = 'use_read_replica';
export const UseReadReplica = () => SetMetadata(READ_REPLICA, true);
@Injectable()
export class DataSourceRoutingInterceptor implements NestInterceptor {
constructor(private readonly reflector: Reflector) {}
intercept(ctx: ExecutionContext, next: CallHandler): Observable<unknown> {
const useReplica = this.reflector.getAllAndOverride<boolean>(READ_REPLICA, [
ctx.getHandler(),
ctx.getClass(),
]);
const dataSource: Ctx['dataSource'] = useReplica ? 'replica' : 'primary';
return new Observable((subscriber) => {
dbContext.run({ dataSource }, () => {
next.handle().subscribe(subscriber);
});
});
}
}The service asks the context which DataSource it’s allowed to use, and falls back to the writer if anything looks off. If a transaction is open, the context is ignored. Transactions live on the primary, always.
import { Injectable } from '@nestjs/common';
import { DataSource, EntityManager } from 'typeorm';
import { InjectDataSource } from '@nestjs/typeorm';
import { dbContext } from './db-context';
@Injectable()
export class PostQueries {
constructor(
@InjectDataSource('primary') private readonly primary: DataSource,
@InjectDataSource('replica') private readonly replica: DataSource,
) {}
private pickManager(): EntityManager {
const ctx = dbContext.getStore();
if (!ctx || ctx.dataSource === 'primary') return this.primary.manager;
return this.replica.manager;
}
findRecent(limit: number) {
return this.pickManager().query(
'select id, body, created_at from posts order by created_at desc limit $1',
[limit],
);
}
}Controllers decorate the routes that are safe to send to the reader. Anything that mutates, anything that needs read-your-writes, doesn’t get the decorator. That’s the whole policy.
@Controller('posts')
export class PostsController {
constructor(private readonly q: PostQueries) {}
@Get('feed')
@UseReadReplica()
feed() {
return this.q.findRecent(50);
}
@Post()
create(@Body() dto: CreatePostDto) {
return this.q.create(dto);
}
}A transaction that touches the writer must run every read inside it against the writer too. Otherwise you read stale data, decide based on it, and you’ve reintroduced the same bug class you used the writer to avoid.
Transactions don’t go through the routing interceptor at all. They take an explicit EntityManager from the primary and use it for everything inside the boundary.
async transferPostOwnership(postId: string, newOwnerId: string) {
return this.primary.transaction('REPEATABLE READ', async (tx) => {
const post = await tx.query('select id, owner_id from posts where id = $1 for update', [postId]);
if (!post[0]) throw new Error('post_not_found');
await tx.query('update posts set owner_id = $1 where id = $2', [newOwnerId, postId]);
await tx.query('insert into post_ownership_log (post_id, from_user, to_user) values ($1, $2, $3)', [
postId,
post[0].owner_id,
newOwnerId,
]);
});
}The SELECT FOR UPDATE is doing work too. The read inside a transaction needs to participate in the lock, which a replica cannot do.
A Tuesday morning at the creator-economy platform I spent the last few years at. Aurora Postgres on the writer, three reader replicas behind a routing layer. I was floating across squads and got tagged on a Slack thread because the community feed had gone weird. Datadog’s replica-lag alert fired around 10:14 a.m. Pacific. The /communities/:id/posts p99 climbed from ~120 ms to over 8 seconds in four minutes. By the time I joined, lag was at 14 minutes and growing.
The on-call’s first move was to bump the reader instance class up two tiers. Reasonable hypothesis, wrong root cause. The readers were not CPU-bound. They were starved of WAL. Lag didn’t move.
The real cause was on the writer. pg_stat_activity showed a long-running ANALYZE on one of the hottest tables on the platform, holding write-side locks and starving WAL emission. A maintenance cron that didn’t respect peak hours. We killed the analyze. Replica lag drained in about six minutes. Same week, I shipped a tiny Ruby helper, db_safe_maintenance.rb, that refuses to run maintenance commands between 06:00 and 22:00 UTC. The runbook now leads with a literal sentence. Before touching reader scaling, check pg_stat_activity on the writer. I’m the reason that sentence is in there.
The lesson back in NestJS land: if your reader is allowed to be 14 minutes behind, your routing layer needs to know. A liveness ping isn’t enough. Read pg_last_xact_replay_timestamp(), compare to now, and if the gap crosses a threshold, fail the route over to the writer.
@Injectable()
export class ReplicaHealth {
constructor(@InjectDataSource('replica') private readonly replica: DataSource) {}
async lagSeconds(): Promise<number> {
const rows = await this.replica.query(
"select extract(epoch from (now() - pg_last_xact_replay_timestamp())) as lag",
);
return Number(rows[0]?.lag ?? 0);
}
}The interceptor reads this through a cached signal, and if lag is above budget, requests fall back to the primary. The fallback is silent. The alert is not.
Different shape, same family. At the combat-sports tournament platform I CTO’d in London, the rankings page ran off Elasticsearch projected from Postgres via a Kafka consumer. One Saturday a tournament ended, the new champion’s ranking did not update for hours, and we found out from the athlete tweeting a screenshot. The indexer was happily consuming from Kafka. It just wasn’t writing to Elasticsearch. The bulk-write client had silently entered a circuit-open state after a transient blip and never closed it back.
What I took from that and applied since: “is the replica connected” is not the same question as “is the replica fresh enough to serve this read.” Liveness and freshness are different metrics. You need both.
Failover gets oversold. AWS RDS will fail a writer over to a replica without asking, and your app will start writing to what used to be a replica. That part is fine. The part that isn’t fine is your app pretending it never happened. After failover, in-flight transactions are gone and the connection pool has stale handles. Catch the errors that look like read-only transaction or terminating connection due to administrator command, drop the pool, and let the request fail. Do not retry the write transparently. The caller needs to know the write didn’t happen.
async function withWriterRetry<T>(fn: () => Promise<T>, max = 2): Promise<T> {
for (let i = 0; i < max; i++) {
try {
return await fn();
} catch (e: any) {
const msg = String(e?.message ?? '');
if (msg.includes('read-only transaction') || msg.includes('terminating connection')) {
if (i === max - 1) throw e;
await new Promise((r) => setTimeout(r, 200 * (i + 1)));
continue;
}
throw e;
}
}
throw new Error('unreachable');
}Two attempts. Small backoff. After that, the request fails and the caller decides. The worst version of this code I’ve seen retried writes ten times with exponential backoff and managed to double-bill a customer during a failover. Idempotency keys live downstream of this.
ANALYZE on a hot table will starve your readers.Thanks for reading. If you’ve got thoughts, send them my way.