Read/write splitting in application code

Routing queries to the right copy.

Adding replicas does nothing until the app actually sends queries to them. Somewhere in your code, each query must be classified as a write (primary) or a read (replica or primary). That decision is read/write splitting. It can live in a database proxy, but many teams do it in the application, which gives the most control over the tricky cases.

The simplest design: two connection pools, one pointing at the primary and one at a pool of replicas. A small wrapper picks the pool. Anything inside a transaction goes to the primary, because a transaction may contain writes and needs one consistent view. Reads default to a replica unless the caller says it needs fresh data.

type Freshness = "any" | "after-write";

function pickPool(kind: "read" | "write", fresh: Freshness, userId: string) {
  if (kind === "write") return primaryPool;
  if (fresh === "after-write" && recentlyWrote(userId)) return primaryPool;
  const healthy = replicas.filter((r) => r.lagSeconds < 5);
  if (healthy.length === 0) return primaryPool; // safe fallback
  return healthy[hash(userId) % healthy.length]; // sticky per user
}

Notice three safety choices in the sketch. First, the replica is picked by hashing the user id, so a user keeps seeing the same replica and their view never jumps backwards. Second, replicas with too much lag are skipped. Third, if no replica is healthy, reads fall back to the primary: slower for everyone, but correct. Without that fallback, one bad replica day becomes an outage.