El Pop

My personal interests, reflections, and thoughts


Use of PGBouncer, PG-Pool and Patroni with PostgreSQL Database

When managing PostgreSQL at scale, the primary bottleneck is often the overhead of creating and maintaining connections. Both PgBouncer and PgPool-II address this, but they solve different problems.

1. PgBouncer: The Lightweight Specialist

PgBouncer is a connection pooler. Its sole purpose is to reduce the cost of connecting to the database.

  • How it works: It sits between your application and the database. The app connects to PgBouncer; PgBouncer maintains a small pool of “real” connections to Postgres and rotates them among the app’s requests.
  • Key Strength: Extremely lightweight. It can handle thousands of incoming client connections while keeping the actual Postgres connection count very low.
  • Tradeoff: It does not know about your data or the state of your cluster. It cannot balance reads/writes or perform failovers.
  • Best Mode: Transaction pooling. This allows a connection to be returned to the pool as soon as a transaction ends, maximizing efficiency.

2. PgPool-II: The Swiss Army Knife

PgPool-II is a database proxy. While it can pool connections, it provides a full suite of cluster management features.

  • How it works: It acts as a sophisticated middleware layer that understands the SQL being sent.
  • Key Strengths:
    • Load Balancing: It can split traffic—sending SELECT queries to replicas and INSERT/UPDATE to the primary.
    • High Availability: It can detect a primary node failure and trigger a failover.
    • Connection Pooling: It provides pooling, though it is generally heavier than PgBouncer.
  • Tradeoff: High complexity. It is significantly harder to configure and maintain than PgBouncer. Because it parses SQL to perform load balancing, it introduces more overhead.

Practical Comparison

FeaturePgBouncerPgPool-II
Primary GoalConnection EfficiencyCluster Management
Resource FootprintNegligibleModerate to High
Load BalancingNoYes (Read/Write splitting)
Failover/HANoYes
ConfigurationSimpleComplex
SQL AwarenessNone (Transparent pipe)High (Parses SQL)

Operational Reality: Which one to use?

Use PgBouncer if:

  • You have a “too many connections” error (FATAL: sorry, too many clients already).
  • You use a serverless architecture (AWS Lambda, etc.) where connections are opened/closed rapidly.
  • You already have a separate HA solution (like Patroni) and just need to manage connection overhead.

Use PgPool-II if:

  • You need a single entry point for your app that automatically handles read-replica routing.
  • You want a built-in mechanism for automatic failover and health checking.
  • Your infrastructure is relatively static and you can afford the administrative overhead of managing the proxy.

The “Pro” Setup:
In high-traffic professional environments, it is common to use both. PgPool-II is used at the top level for load balancing and HA, and PgBouncer is deployed on the database nodes themselves to ensure the actual Postgres processes aren’t overwhelmed by the proxy’s connections.

Patroni

If PgBouncer and PgPool-II are about how the app talks to the database, Patroni is about who is actually in charge of the data.

Patroni is a Cluster Manager. It does not handle connections or load balancing; it ensures that your PostgreSQL cluster remains available and consistent, even if the primary server dies.

How Patroni Works

Patroni runs as a “sidecar” process on every database node. It manages the Postgres process and uses a Distributed Configuration Store (DCS)—typically etcd, Consul, or ZooKeeper—to keep track of the cluster’s state.

  1. Leader Election: Patroni nodes compete for a “leader lock” in the DCS. The node that holds the lock is the Primary (Read/Write).
  2. Automatic Failover: If the Primary crashes or loses its connection to the DCS, the lock expires. The remaining Patroni nodes detect this and hold a new election. One is promoted to Primary, and the others are reconfigured as Replicas.
  3. Configuration Management: Changes made to the cluster configuration in the DCS are automatically pushed by Patroni to all nodes.

The Operational Gap

A critical point: Patroni does not provide a single IP address or endpoint for your application.

If Patroni promotes a new leader, the IP address of the Primary changes. Your application cannot “magically” know this. This is where the tools from the previous turn come back into play. You need a routing layer to point the app to the current leader:

  • Patroni + HAProxy/Keepalived: HAProxy checks a health-check API provided by Patroni to see which node is the leader and routes traffic there.
  • Patroni + PgBouncer: You place PgBouncer in front of the cluster. When the leader changes, you update PgBouncer’s configuration to point to the new leader.

Comparison: Patroni vs. PgPool-II

People often confuse them because PgPool-II claims to do failover. Here is the pragmatic difference:

FeaturePgPool-II (Failover)Patroni (HA)
ApproachProxy-driven (The proxy decides)Consensus-driven (The nodes decide via etcd/Consul)
ReliabilityProne to “Split Brain” if not tuned perfectlyExtremely robust; prevents Split Brain via DCS locks
ComplexityModerate (all-in-one)High (requires etcd/Consul cluster)
Primary GoalRouting & basic recoveryAbsolute data integrity and availability

The “Gold Standard” Production Stack

In a professional, industrial-grade PostgreSQL deployment, you typically don’t choose one; you layer them:

Application →→ PgBouncer (Connection Pooling) →→ HAProxy (Routing) →→ Patroni (Cluster Mgmt) →→ PostgreSQL

  • Patroni ensures a leader always exists and manages the Postgres process.
  • HAProxy asks Patroni “Who is the leader?” and routes the request there.
  • PgBouncer ensures the app doesn’t kill the database with too many concurrent connections.
  • etcd/Consul provides the “source of truth” for the whole system.