CAP Thr
Starting point¶
HLD Pillar 4, Phase 1. CAP theorem was the next concept to cover. Known gap from Siemens interview — described CAP correctly but blanked on the acronym. No prior formal grounding on read replicas, routing, or how CAP manifests in practice.
What we did¶
Started from read replicas and built up to CAP theorem through practical grounding rather than theory first.
Read replica routing — established that writes always go to primary, reads can go to replicas. Application manages this via two connection strings. .NET Npgsql manages connection pooling natively — no PgBouncer needed at small scale.
Replication mechanics — PostgreSQL uses WAL streaming to replicate. Primary writes to WAL, replica replays it. Replica tracks its position via LSN. Async replication by default — replica can lag behind primary.
Sync vs async replication — sync replication blocks the write acknowledgement to the app until at least one replica confirms receipt. Eliminates staleness but adds write latency. If replica goes down, primary stalls. Hard CP. Not commonly used in production for general consistency — used mainly for zero-data-loss failover on one designated replica.
CAP theorem — built from the partition scenario. Partition is a network break between nodes holding the same data. P is non-negotiable. Real choice is CP vs AP.
- CP — reject request rather than serve wrong data. Commands always primary, sensitive reads always primary.
- AP — serve response even if stale. Acceptable for display reads, reporting, catalogues.
Where CAP actually lives — not in DB config. In your routing decisions and your failure handling code. Normal operation — no visible tradeoff. Partition — your catch block is where you choose CP or AP.
Commands always primary — aggregate hydration reads inside commands go to primary. Not because of CAP theory but because you're making consistency-sensitive decisions that drive writes. CAP is just the formal name for what you were already doing.
When queries go to primary — not all queries go to replica. Consistency-sensitive queries go to primary: anything feeding a subsequent write, compliance/audit reads, anything where wrong data causes real damage.
Read-your-own-writes — user expects to see their own recent write. Hard to solve cleanly in stateless REST. Options: return data from command response, route to primary always for that resource type, or LSN-based routing.
LSN-based routing — Bitbucket approach — after a write, save primary's LSN to Redis keyed by user ID. At start of next request, check Redis. If entry exists, find a replica that has caught up past that LSN and route reads there. If none have caught up, route to primary. One LSN check per request, not per query. Request is then pinned to the chosen replica for its duration.
Tata 1mg approach — similar but smarter for multiple replicas. Store write LSN per table in Redis. Also store minimum replay LSN across all replicas via pg_stat_replication queried on primary. Before a read, compare write LSN against minimum replay LSN. If minimum has caught up, all replicas are safe — route to any. If not, primary.
SELECT write_lsn, replay_lsn, client_addr
FROM pg_stat_replication
ORDER BY replay_lsn ASC
LIMIT 1
This returns the most lagging replica's position. If even that one has caught up, every replica has.
Dead replicas — a completely dead replica disconnects from primary and disappears from pg_stat_replication. Not an infinite lag problem. A degraded but connected replica is the real problem — handled via blacklisting in Bitbucket's implementation or via HAProxy/NLB health checks removing it from the pool.
Proxy options — PgBouncer is connection pooling only, no routing, no health checks. Pgpool-II is full middleware — connection pooling, SQL-based read/write routing, health checks, failover. HAProxy is TCP proxy with health checks, no SQL parsing. NLB on AWS is the managed equivalent of HAProxy.
Managed cloud — standard RDS PostgreSQL gives you one endpoint per replica, you manage routing and load balancing yourself. Aurora gives you a writer endpoint and a reader endpoint — reader endpoint load balances across all replicas, handles health checks, removes dead replicas. That is the managed equivalent of HAProxy/NLB in front of replicas. Azure Database for PostgreSQL Flexible Server is closer to standard RDS — no Aurora-equivalent reader endpoint.
Concepts covered¶
CAP theorem — Consistency, Availability, Partition Tolerance. P non-negotiable. Real choice is CP vs AP per operation, not per system.
Replication lag — async replication means replica is always slightly behind. Normal, not a failure state.
Sync replication — eliminates lag by blocking writes until replica confirms. Hard CP. Write latency cost. Primary stalls if replica dies.
LSN — Log Sequence Number — monotonically increasing position in the WAL. Used to determine whether a replica has replayed a specific write. pg_current_wal_lsn() on primary, pg_last_wal_replay_lsn() on replica, pg_stat_replication on primary for all replicas simultaneously.
Read-your-own-writes — session consistency guarantee. Solved via LSN routing in production. TTL on Redis entry is a safety net, not an assumption about replication speed.
PgBouncer vs Pgpool-II — PgBouncer pools only. Pgpool-II pools plus routes plus health checks. PgBouncer needs HAProxy alongside for replica proxying. Pgpool-II is one tool for everything but heavier.
What we messed up¶
Multi-master framing — initially built the CAP example as Mumbai/Chennai writing independently. Wrong framing for primary-replica setup. Corrected by going back to basics.
"Session" for read-your-own-writes — incorrectly suggested session state to track recent writes. REST is stateless, server has no memory of previous requests. Corrected to Redis-based LSN tracking.
"Return data from command response" — presented as a canonical solution for read-your-own-writes. It's a UI convenience, not a consistency solution. Corrected after pushback.
PgBouncer as proxy — repeatedly said PgBouncer can front replicas. It cannot. PgBouncer points at one instance. HAProxy or NLB is the replica proxy. Corrected.
Per-query LSN checks — described checking replica LSN per query in a loop. Wrong. The check happens once per request at routing time, the request is then pinned to the chosen node.
"Most teams use PgBouncer and manage routing in application" — said without explaining what proxies the replicas. Incomplete. Corrected to: PgBouncer needs HAProxy alongside, or use Pgpool-II which does both.
Key values to remember¶
| Concept | Value |
|---|---|
| PostgreSQL WAL timeout default | 60 seconds before replica marks connection dead |
| Bitbucket LSN overhead | ~10ms per request |
| Bitbucket result | 80% of load moved off primary |
pg_stat_replication |
View on primary showing all replica positions |
pg_last_wal_replay_lsn() |
Query on replica for its own current position |
| Aurora replication lag | Typically under 100ms |
Unanswered questions¶
- Azure Database for PostgreSQL Flexible Server — does it offer anything equivalent to Aurora's reader endpoint or is routing always application-managed?
- RDS Proxy read-only endpoints — the AWS blog mentioned these work for standard RDS PostgreSQL too, not just Aurora. Needs verification — conflicted with the FAQ answer we found.
What's next¶
Consistency models — strong, eventual, causal. Causal consistency is where read-your-own-writes formally lives. Strong consistency maps to CP. Eventual consistency maps to AP. Causal is the middle ground most production systems actually implement.