PostgreSQL at Scale: HA, Patroni, PgBouncer, and DR

PostgreSQL at scale is a reliability problem before it is a hardware problem. Replication, failover, connection pressure, query plans, locks, autovacuum, checkpoints, storage latency, backups, and application retry behavior interact. A database can show normal CPU while p99 latency grows, sessions wait on locks, replicas fall behind, and customer requests time out.

This guide is for database owners, SREs, backend leads, and CTOs operating PostgreSQL under real production load. It explains how the parts fit together, which problems each control solves, and how to turn architecture theory into a measured implementation and validation plan.

The practical goal is not “maximum throughput.” It is predictable behavior: bounded latency, controlled concurrency, safe failover, recoverable data, enough capacity headroom, and clear operating decisions during pressure.

Start with workload and failure model

Before choosing bigger hardware, Kubernetes, Patroni, a managed database, read replicas, or sharding, define the workload.

Capture:

• Peak and normal requests per second.
• Read/write ratio.
• p95 and p99 query latency for critical operations.
• Active, idle, waiting, and idle-in-transaction sessions.
• Long transactions and lock waits.
• WAL generation rate and replica replay lag.
• Checkpoint frequency and storage latency.
• Table and index growth.
• Autovacuum lag, dead tuples, and bloat indicators.
• Backup duration, recovery point, and last successful restore test.

Also document the failure model:

• Can the application tolerate stale reads?
• What is the maximum acceptable data loss?
• How long may writes be unavailable?
• What happens when the primary disappears?
• What happens when a replica is hours behind?
• What happens when all application pods reconnect at once?
• Which migrations make rollback unsafe?

Without this baseline, scaling work becomes expensive guessing.

Build a PostgreSQL connection budget

Many PostgreSQL incidents are connection incidents. Application replicas scale out, every process opens its own pool, a deploy restarts all workers, and the database spends memory and scheduler time managing sessions instead of executing useful work.

Create a connection budget from the database inward:

1. Reserve sessions for administration, monitoring, backups, replication, and maintenance.
2. Decide the safe application server connection count.
3. Divide that budget across PgBouncer pools or application instances.
4. Add a small controlled reserve pool.
5. Test deployment and autoscaling behavior against the budget.

Useful checks:

select state, count(*)
from pg_stat_activity
group by state
order by count(*) desc;

select wait_event_type, wait_event, count(*)
from pg_stat_activity
where wait_event is not null
group by 1, 2
order by 3 desc;

select pid, usename, application_name, now() - xact_start as age, state, left(query, 120)
from pg_stat_activity
where xact_start is not null
order by xact_start;

A high max_connections value is not a capacity strategy. It can postpone rejection while increasing memory pressure and making overload slower to recover from.

PgBouncer: choose pool mode from application behavior

PgBouncer can protect PostgreSQL from connection storms, but its pool mode changes session semantics.

Before using transaction pooling, review:

• Session variables and SET behavior.
• Temporary tables.
• Advisory locks.
• Prepared statements and driver behavior.
• LISTEN/NOTIFY usage.
• Long transactions.
• Connection reset behavior.

The implementation example below provides a conservative starting point, but pool size must come from the real connection budget.

Query latency, locks, and autovacuum

Adding replicas or larger instances does not fix poor query plans, long transactions, lock contention, or tables that autovacuum cannot keep up with.

A practical performance review includes:

• pg_stat_statements by total time, mean time, calls, and rows.
• Query plans with representative parameter values.
• Long-running and idle-in-transaction sessions.
• Blocked and blocking process chains.
• Dead tuple growth and autovacuum timestamps.
• Index usage and write amplification.
• Checkpoint timing and WAL volume.
• Storage latency during peak write windows.

Example blocking-chain input:

select
  blocked.pid as blocked_pid,
  blocker.pid as blocker_pid,
  now() - blocked.query_start as blocked_for,
  left(blocked.query, 120) as blocked_query,
  left(blocker.query, 120) as blocker_query
from pg_stat_activity blocked
join pg_locks blocked_locks on blocked_locks.pid = blocked.pid and not blocked_locks.granted
join pg_locks blocker_locks
  on blocker_locks.locktype = blocked_locks.locktype
 and blocker_locks.database is not distinct from blocked_locks.database
 and blocker_locks.relation is not distinct from blocked_locks.relation
 and blocker_locks.page is not distinct from blocked_locks.page
 and blocker_locks.tuple is not distinct from blocked_locks.tuple
 and blocker_locks.virtualxid is not distinct from blocked_locks.virtualxid
 and blocker_locks.transactionid is not distinct from blocked_locks.transactionid
 and blocker_locks.classid is not distinct from blocked_locks.classid
 and blocker_locks.objid is not distinct from blocked_locks.objid
 and blocker_locks.objsubid is not distinct from blocked_locks.objsubid
 and blocker_locks.pid != blocked_locks.pid
join pg_stat_activity blocker on blocker.pid = blocker_locks.pid
where blocker_locks.granted;

Use this as evidence for incident analysis, not as a reason to kill sessions automatically without understanding the transaction and business impact.

Patroni, consensus, and failover discipline

Patroni coordinates leader election and PostgreSQL promotion through a distributed configuration store such as etcd or Consul. It is effective when the failure model, consensus health, routing, watchdog or fencing assumptions, and application reconnect behavior are understood.

Useful checks:

patronictl list
etcdctl endpoint health --cluster
curl -fsS http://127.0.0.1:8008/cluster
psql -X -c "select pg_is_in_recovery();"
psql -X -c "select client_addr, state, sync_state, write_lag, flush_lag, replay_lag from pg_stat_replication;"

The runbook must define:

• When automatic failover is allowed.
• When promotion must be blocked because lag exceeds RPO.
• Who approves manual failover.
• How clients discover the new primary.
• How split-brain risk is controlled.
• How the old primary is fenced or rebuilt.
• How replicas, backups, and monitoring continue afterward.
• Which application transaction proves write recovery.

A patronictl success message is not enough. Measure client reconnect time and verify that only one intended primary accepts writes.

Bare metal, VMs, Kubernetes, or managed PostgreSQL

The correct platform depends on workload, team maturity, storage, compliance, recovery objectives, and desired control.

Kubernetes does not remove database engineering. It adds scheduling, storage, network, and operator behavior that must also be tested during failover and recovery.

Monitoring before users feel pain

A useful PostgreSQL dashboard should show leading indicators, not only CPU and disk percentage.

Monitor:

• p95/p99 query latency for critical operations.
• Active, waiting, and idle-in-transaction sessions.
• Connection usage against the documented budget.
• Lock waits and long transactions.
• Replica write, flush, and replay lag.
• WAL generation rate and archive failures.
• Checkpoint duration and frequency.
• Buffer cache behavior and storage latency.
• Dead tuples, autovacuum progress, and table growth.
• Backup success, last recoverable point, and restore test age.

Alerts should express impact and action. “Database CPU high” is weak. “Primary connection budget is 90% consumed, checkout p99 doubled after deploy, and queue depth is rising” is useful.

Backup and restore testing

Backups become a recovery strategy only after a clean restore proves the path.

A restore drill should answer:

• Which backup and recovery data were used?
• What exact point was recovered?
• How long did restore take?
• Which credentials and infrastructure were required?
• Did PostgreSQL start cleanly?
• Did consistency and application smoke tests pass?
• Did backup, WAL archive, replication, and monitoring resume?
• Which gaps became owned follow-up actions?

Store the drill record with timestamps, commands, selected outputs, recovered point, duration, and validator approval. This evidence is more valuable than a green backup job alone.

PostgreSQL scaling decision matrix

Related SteadyOps reading

• Disaster Recovery Runbook Template — recovery ownership, RPO/RTO, commands, and validation.
• Kubernetes Production Readiness Checklist — storage, secrets, rollout, and operational controls around stateful workloads.
• Load Balancing: Comparative Architectures — routing and health checks for primary or service failover.
• Security Evidence Operations Model — access, backup, change, and incident evidence.

Key takeaways

• PostgreSQL scaling begins with workload and failure evidence.
• Connection budgets and pooling often matter before larger hardware.
• Query plans, locks, autovacuum, WAL, and storage behavior need direct measurement.
• Patroni improves HA only when consensus, routing, fencing, and client reconnect are tested.
• Monitoring must connect database saturation to customer impact.
• Backups are credible only after a clean restore meets RPO and RTO.

[databases]
app = host=postgres-primary port=5432 dbname=app

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 40
reserve_pool_size = 10
reserve_pool_timeout = 3
server_idle_timeout = 60
query_timeout = 30
client_idle_timeout = 300

select state, count(*)
from pg_stat_activity
group by state
order by count(*) desc;

select pid, now() - xact_start as age, state, wait_event_type, wait_event, left(query, 120)
from pg_stat_activity
where xact_start is not null
order by xact_start;

select client_addr, state, sync_state, write_lag, flush_lag, replay_lag
from pg_stat_replication;