Pooling and failover topology

The question, stated precisely

You have a PostgreSQL cluster and you are deciding what sits in front of it. The candidates in practice are three:

1. PgBouncer alone.
2. Pgpool-II alone.
3. PgBouncer for pooling plus Patroni, HAProxy and keepalived for promotion and addressing: the topology described in PostgreSQL high availability.

The mistake that makes this decision hard is treating it as one choice. It is three independent requirements, and the right topology falls out of which of them you genuinely have:

PostgreSQL provides none of the three. It forks a backend per connection, replicas are read-only, and promotion is a manual operation unless something external decides. That is the whole reason this decision exists.

What is not being decided here: replication mode and durability. synchronous_commit and synchronous_standby_names are a separate trade (a durability risk converted into an availability risk, or the reverse), and every option below is compatible with every setting of them.

The two factors that genuinely decide it

1. Which of the three requirements you actually have

Be strict about this, because each requirement you accept adds a component and a failure mode.

Pooling you almost certainly need. PostgreSQL forks a backend per connection, so an application pool of a few thousand connections will exhaust a database that would comfortably serve twenty. This is not a scale-dependent requirement; it applies to a single modest application server with a framework pool.

Read routing you need only if reads genuinely exceed what the primary can serve and the application cannot be told about replicas. That second condition is the one that gets skipped. If you can give the application a read DSN and a write DSN, or use a driver that honours target_session_attrs, you do not need a query router. A query router you do not need can only misroute. Note also that routing reads to replicas introduces staleness the application must tolerate, whichever component does the routing.

Automatic promotion you need only if your recovery time objective is shorter than a human can achieve. Write that number down before choosing. If the objective is thirty minutes and someone is paged within five, a documented manual promotion meets it with no new components. If the objective is sixty seconds, no manual process meets it and you need a decision maker.

2. Whether a consensus store belongs in your availability path

This is the factor people discover afterwards instead of deciding in advance, and it is the most consequential one.

Automatic promotion is only safe if some component can answer “did this write succeed against a majority?”, because a node cannot distinguish “the primary is dead” from “I cannot currently see the primary”. Patroni answers it by holding a leader lease in etcd, Consul or ZooKeeper. That is correct, and it has a direct consequence: the consensus store’s disk latency becomes the database’s availability budget. An etcd cluster with an fsync problem will cause Patroni to demote a healthy primary to read-only. Every process will be up; writes will fail.

You either accept that or you do not. Accepting it means running etcd properly: dedicated low-latency disks, monitored fsync-duration histograms and leader-change counters, and a timing budget where loop_wait + 2 × retry_timeout <= ttl with real slack. It also means Patroni’s failsafe mode, so a primary that can still reach every member keeps running when the store is unreachable. Declining it means either no automatic promotion, or accepting Pgpool-II’s watchdog, which votes among Pgpool-II instances rather than consulting a consensus store and needs at least three nodes to avoid split brain.

There is a third answer that is often the right one: let a managed PostgreSQL service own promotion and the endpoint. Then the consensus store exists, and it is not yours.

The differences that do not decide it

• Component count. “One component instead of three” sounds like simplicity and often is not. Pgpool-II is a multi-process server with its own clustering state, quorum logic and SQL parser; the Patroni stack has more processes but each answers exactly one question. Count failure domains, not binaries.
• Query cache. Pgpool-II has one and it is a genuine capability, but it is rarely the reason a topology is chosen, and an application-level cache is usually a better fit for the invalidation rules you actually have.
• Which tool is more actively developed. All three are maintained. This tells you nothing about whether the topology matches your requirements.
• Benchmark throughput of the pooler. PgBouncer is a single-threaded non-blocking event loop, so one process saturates one core, and the documented fix is several processes sharing a port via so_reuseport with a [peers] section. That is a sizing exercise, not a selection criterion.

A decision path

1. If a managed PostgreSQL service is acceptable, use it and add a pooler only if the service does not provide one. It already owns promotion and the endpoint; running Patroni beside it duplicates the decision maker, and running Pgpool-II’s watchdog beside it duplicates the address layer.
2. If you need pooling only (one primary, replicas absent or addressed directly by the application, recovery objective met by a manual promotion or a restore), run PgBouncer in transaction mode and stop. Do not add a topology manager for a cluster whose likelier incident is a bad migration, which failover does not help with.
3. If you need pooling and read routing but not automatic promotion, the cheapest correct answer is usually PgBouncer plus application-level read/write splitting. Choose Pgpool-II here only if the application genuinely cannot be taught about replicas. That is the case it exists for, and the price is a SQL parser in the data path whose classification decisions are version-coupled to the PostgreSQL grammar it was built against.
4. If you need automatic promotion and you accept a consensus store, run PgBouncer for pooling, Patroni for promotion, HAProxy for routing and keepalived for the address, as in PostgreSQL high availability. Each component does one job and the promotion decision is grounded in a majority write rather than in a vote among proxies. On most cloud networks, replace keepalived with a cloud load balancer, because VRRP multicast and claiming an unallocated address generally do not work there.
5. If you need automatic promotion and you refuse a consensus store, Pgpool-II with watchdog on at least three nodes is the coherent option. Be explicit that you have traded a consensus store for a quorum among proxies, and that health checking is off by default: health_check_period is 0, so without setting it a backend that dies while no client is connected is not noticed at all.

Two configuration consequences follow from the path; they do not precede it. In transaction mode, anything session-scoped stops being reliable: SET outside a transaction, session-level advisory locks, LISTEN/NOTIFY, WITH HOLD cursors and temporary tables. Isolate those onto a session-mode pool. And with Pgpool-II, backend_clustering_mode cannot be changed after start, so choosing streaming_replication - the mode where PostgreSQL remains the authority on replication - is a design decision made once.

What would change the answer

• The recovery time objective tightens. This is the usual trigger for moving from step 2 to step 4. It should be a written number, changed deliberately, not an assumption that drifted.
• Read volume outgrows the primary. Adds the routing requirement. Try the application-level split first; it removes a component from the data path instead of adding one.
• The application acquires session-scoped state. A new dependency on LISTEN/NOTIFY, temporary tables or advisory locks changes the pool mode, and possibly the topology, because transaction-mode pooling silently breaks all three.
• You move to a cloud network. The address layer changes. keepalived’s assumptions about VRRP and unallocated addresses do not hold in most virtual networks; the rest of the topology survives.
• You stretch across regions. The timing arithmetic in the Patroni stack assumes a single low-latency segment. Across regions you get elections caused by latency, not by failure. At that point the answer is a different architecture, not a tuned one.
• PostgreSQL or Pgpool-II majors change. Pgpool-II’s routing correctness depends on its imported parser: 4.6 imported PostgreSQL 17’s and 4.7 imported 18’s, so running a newer PostgreSQL than your Pgpool-II was built against means unparseable statements fall back to the primary.

Common wrong reasons

Choosing PgBouncer alone because failover “has not been a problem yet”. That is not a decision, it is a deferral. It is defensible when the recovery objective is genuinely loose; say so, write the manual promotion runbook, and test it. Untested manual promotion is worse than no plan, because it gets attempted under pressure.

Choosing Pgpool-II because it does everything. Doing everything is the cost. Its pooling is a per-child-process cache bounded by num_init_children × max_pool (128 backend connections per instance at the defaults of 32 and 4), not a multiplexer, so it does not give PgBouncer’s ratio of thousands of clients over tens of server connections. If pooling is the main requirement, this is the wrong tool for it.

Choosing Pgpool-II for read routing when the application could be told about replicas. The parser can only ever be as right as its grammar coverage, and a misclassification sends a write to a replica or a read to the primary. If you can pass two DSNs, pass two DSNs.

Choosing the full Patroni stack because it is what serious teams run. It is four independent failure domains stacked on one another, and most incidents in it are not database incidents: an etcd fsync problem demoting a healthy primary, a health check pointed at the wrong endpoint sending writes to a replica, a virtual IP that never moved. That is a reasonable trade for a tight recovery objective and a poor one for a database whose objective a restore would meet.

Assuming failover fixes client connections. It does not. A correct promotion still leaves pooled sessions attached to the demoted node, which shows up as an error rate that stays elevated long after patronictl list reports a healthy leader. The fix is on-marked-down shutdown-sessions on the HAProxy write backend and a pool that validates connections, plus PgBouncer’s PAUSE/RESUME around planned switchovers so clients block and do not error.

Deploying automatic promotion without fencing. Automatic promotion without a watchdog and pre-promote fencing converts a single-node failure into split brain, where the old primary keeps accepting commits after another node has been promoted. The application-side symptom is rows that exist on one node only, which is discovered later and is not repairable by failing back.

Related

• PgBouncer - pool modes, sizing, auth_query
• Pgpool-II - clustering modes, routing, watchdog quorum
• PostgreSQL high availability - the assembled topology
• Read/write splitting - the routing requirement in detail
• PostgreSQL - why connection count is expensive
• Choosing a relational database - the prior decision