Design PostgreSQL Read Replicas with Claude Code: Read/Write Splitting, Prisma

#claudecode #postgres #typescript #prisma

Introduction

When DB reads are slow, use read replicas to split read/write traffic — route SELECT queries to replicas to reduce primary load. Let Claude Code design Prisma + PgBouncer patterns.

CLAUDE.md Rules

## DB Read Replica Rules
- Writes (INSERT/UPDATE/DELETE): primary only
- Reads (SELECT): route to replica
- Inside transactions: always use primary
- Post-write reads: use primary for 2 seconds

Generated Implementation

// src/db/router.ts
import { AsyncLocalStorage } from 'async_hooks';

const dbContext = new AsyncLocalStorage<{ db: PrismaClient }>();

export function getReadDb(): PrismaClient {
  const ctx = dbContext.getStore();
  if (ctx?.db) return ctx.db; // Inside transaction: use that DB
  return replicaPool.getHealthyReplica();
}

// Post-write consistency: use primary for 2 seconds
export async function withPostWriteConsistency<T>(fn: () => Promise<T>): Promise<T> {
  return dbContext.run({ db: primaryDb }, fn);
}

// All transactions go to primary
export async function withTransaction<T>(
  fn: (tx: Prisma.TransactionClient) => Promise<T>
): Promise<T> {
  return primaryDb.$transaction(async (tx) => {
    return dbContext.run({ db: tx as any }, () => fn(tx));
  });
}

// Replica pool with health checks
class ReplicaPool {
  private healthStatus: Map<PrismaClient, boolean> = new Map();

  constructor(private replicas: PrismaClient[]) {
    replicas.forEach(r => this.healthStatus.set(r, true));
    setInterval(() => this.checkHealth(), 10_000);
  }

  private async checkHealth() {
    await Promise.all(this.replicas.map(async (r) => {
      try {
        await r.$queryRaw`SELECT 1`;
        this.healthStatus.set(r, true);
      } catch {
        this.healthStatus.set(r, false);
      }
    }));
  }

  getHealthyReplica(): PrismaClient {
    const healthy = this.replicas.filter(r => this.healthStatus.get(r));
    return healthy.length > 0 ? healthy[0] : primaryDb; // Fallback to primary
  }
}