Database Replication

Replication is often introduced as a "disaster recovery" solution (having backup copies if the primary server fails), but it also directly resolves the PostgreSQL connection limit problem learned earlier (the ~100 default connections). This section begins with a practical question: "how to handle when connections exceed PostgreSQL limits," leading to Replication as a solution and its associated side effect (replication lag) that you must understand to prevent elusive bugs in production.

1. What is primary-replica replication

• Instead of a single DB server, you provision one or more replicas that continuously and automatically synchronize data from the main server (primary/master).
• Writes (INSERT/UPDATE/DELETE) are permitted only on the primary (preventing data conflicts from concurrent writes in multiple locations).
• Reads (SELECT) are distributed across the replicas.

2. Replication solves both problems

• Data loss prevention: if the primary goes down, a replica stands by for failover.
• Connection limit: distributes read workloads across multiple servers so each server only absorbs a fraction of total connections. For example, out of 200 total connections, allocate 150 to reads (spread across 3 replicas, ~50 each) and 50 to writes (pointed at the primary); no single server exceeds the 100-connection limit of Postgres.
• Practical application: the codebase must differentiate "is this query a read or a write" to route correctly to the primary or replica (via 2 separate connection strings, or by using extensions with built-in support like Prisma Accelerate).

3. Replication lag and eventual consistency

• Propagating data from the primary to replicas takes time (milliseconds to several seconds); it is not instantaneous. This delay is known as replication lag.
• Consequence: immediately after writing to the primary, a subsequent read from a replica might:
  • For creations (POST): fail to find the record (404), because the replica has not received the data yet.
  • For updates: return stale data, because the replica has not caught up with the latest mutations.
• This behavior exemplifies eventual consistency (data becomes consistent after a period of time), which differs from Consistency in ACID (which applies strictly within the scope of a single transaction on a single node, not across distributed replica instances).

4. Mitigations for read-after-write anomalies

1. Read-your-writes routing: for workflows requiring immediate reads right after a write (e.g. "create order → view order details"), explicitly route that read query directly to the primary instead of a replica for a short time window following the write.
2. Returning data directly from write responses (best practice): design APIs so that POST /orders returns the entire newly created resource payload directly inside that response (aligning with the 201 Created principle learned in REST API design). The frontend consumes this data immediately without firing a separate GET request, eliminating the problem at the root.
3. Checking replication lag before routing: a more complex approach that checks the replica's sync position before serving reads, typically reserved for very large-scale architectures.