Single PostgreSQL → Read/Write Split with Replicas
MediumIntroducing streaming replication and a read routing layer so that read-only queries are served from one or more standby replicas, relieving the primary of read load : at the cost of replication lag, dual connection pool management, and the permanent operational requirement to monitor replica health.
Topology Changes
From
Single PostgreSQL Primary
To
PostgreSQL Primary with Read Replicas and Routing Proxy
Topology Mutations
Physical streaming replica of the primary configured with wal_level=replica on the primary and standby.signal on the replica. Replica replays WAL in near-real-time (typically 10–200ms lag under normal write load).
Operational Impact
Replication lag becomes a permanent operational metric. Replica falling behind during write bursts creates a stale read window that must be accounted for in application design.
A routing layer directs read-only queries to replicas and write queries to the primary. Can be implemented as PgBouncer with separate pools, an ORM-level read/write split (Django, SQLAlchemy), or application-managed connection factories.
Operational Impact
Misconfigured routing sends writes to a read-only replica: these fail silently or with cryptic errors. Routing correctness must be validated in integration tests.
Reads from replicas are eventually consistent with the primary. Any query that must see the result of a recent write must be explicitly routed to the primary.
Operational Impact
Session-level read-after-write consistency (user sees their own writes) requires either routing all user-session reads to primary immediately post-write, or tracking the write LSN and waiting for the replica to confirm it before routing to replica.
Migration Stages
Set wal_level=replica and max_wal_senders >= 2 on the primary. Create a replication user. Provision replica server. Run pg_basebackup to seed the replica from a base backup. Create standby.signal and postgresql.auto.conf with primary_conninfo. Validate replica is streaming (pg_stat_replication on primary shows active WAL sender).
Audit all application query paths. Tag each query as read-only or write. Identify which read queries require immediate post-write consistency (cannot go to replica) vs. which tolerate stale reads. Instrument pg_stat_statements to quantify read vs write query volume.
Route read queries to replica but compare results against primary for a sampled subset. This validates replica correctness and identifies queries that are not safe to serve from a lagging replica. Log replication lag during this phase to characterize worst-case staleness.
Switch background jobs, reporting queries, and non-user-session reads to replica. Monitor replication lag. Validate application correctness: specifically check that no write-then-read sequences within a user action are hitting the replica.
The first replica is now a production critical path: if it fails, all read traffic falls back to the primary. Add a second replica with the same configuration. Configure load balancing across both replicas for read queries. Test replica removal and failover.
Migration Risks
Write-then-read within a single user session hits the replica, which may not yet have replicated the write. User sees stale data immediately after their own mutation.
Mitigation
Track the primary LSN at write commit time per user session. Route all reads in that session to the primary until the replica confirms it has replicated past that LSN. Alternatively, route all writes and the first read after any write within a session to primary.
Replication lag spikes during heavy write batches: bulk inserts, backfills, or migration scripts can cause the replica to fall 30–300 seconds behind. During this window, replica reads are significantly stale.
Mitigation
Alert on replication lag > 10 seconds. Configure max_standby_streaming_delay and max_standby_archive_delay to control how long the replica will pause replay to allow conflicting reads to complete. Throttle bulk write operations to cap lag.
Index changes on the primary propagate to replicas via WAL replay, but the time window between index creation on primary and its availability on replica means routing to replica immediately after an index CREATE CONCURRENTLY may hit the old query plan.
Mitigation
After schema changes, force read traffic back to primary for at least 60 seconds. Validate replica pg_indexes matches primary before resuming replica routing.
Coupling Changes
Read and write query load is separated across different physical hosts
Consequence
Primary CPU and I/O headroom increases; replica can be independently sized for read workloads
Primary health now directly affects replica: primary crash requires replica promotion before reads recover
Consequence
Failure domain that was a single node is now a replication cluster with coordinated failover requirements
Consistency Model Changes
- ·Primary writes remain strongly consistent: ACID within the primary is unchanged
- ·Replica reads are eventually consistent: replication lag determines the staleness window (typically 10–500ms)
- ·Read-after-write consistency for user sessions must be explicitly designed: it is not provided automatically by the replica
Rollback Risks
- ·Reverting read routing from replica back to primary during an incident increases primary load sharply: may require connection pool adjustment
- ·If replica is promoted to primary during a failover, the original topology (primary + replica) must be rebuilt from scratch
- ·Application code changes for routing classification (read vs write) are difficult to revert cleanly after they have been deployed