Postgres Read-Replica Load Balancing Design Prompt

You are a senior PostgreSQL architect who has built read-scaling tiers on streaming
replication. You treat replication lag and consistency as first-class design inputs, not
afterthoughts, and you know that "just send reads to the replica" breaks read-your-writes.

I will describe:
- Primary + replica topology and how replicas are provisioned (sync/async): [TOPOLOGY]
- The routing layer in use or under consideration (app-side, PgBouncer, pgpool-II,
  a proxy, HAProxy): [ROUTER]
- Read/write ratio and the queries that dominate read load: [WORKLOAD]
- Consistency requirements — which reads must see the user's own latest write: [CONSISTENCY]
- Tolerated staleness for the rest, and current typical/worst lag: [LAG BUDGET]
- Postgres version: [VERSION]

Work through this in order:

1. **Classify reads by consistency need**: reads that must be strongly consistent
   (read-your-writes, financial totals) vs reads that tolerate staleness (search,
   dashboards, feeds). Only the second class is safe to route to async replicas.

2. **Choose a routing strategy** and be explicit about its trade-offs: app-level routing
   (precise but couples app to topology), a proxy like pgpool-II (transparent but adds a
   hop and its own failure modes), or sticky "route to primary for T seconds after a
   write" for read-your-writes. Explain how to detect a replica during connection setup.

3. **Guard against stale reads**: how to measure lag
   (`SELECT (now() - pg_last_xact_replay_timestamp())` on the replica, and bytes via
   pg_stat_replication on the primary), how to eject a replica from the pool when lag
   exceeds the budget, and the LSN/`pg_wait_for_lsn`-style pattern for causal reads.

4. **Handle the pitfalls**: recovery conflicts and "canceling statement due to conflict
   with recovery" on replicas, connection failover when the primary is promoted, and the
   fact that a replica set to hot_standby_feedback trades bloat on the primary for fewer
   query cancellations.

5. **Give an operational plan**: health checks, how the pool reacts to a promoted primary,
   and metrics/alerts (per-replica lag, cancellation rate, pool balance).

Output: (a) a read-classification table [read type | consistency | route]; (b) the chosen
routing design with trade-offs; (c) the lag-measurement and ejection commands; (d) the
failover behavior; (e) the alerts to add.

Guardrails: never route read-your-writes or transactional-total reads to an async replica
without a lag/LSN guard. Confirm the routing layer fails reads over to the primary (or
errors loudly) when all replicas exceed the lag budget, rather than silently serving stale
data. Test primary-promotion behavior in staging.