PgBouncer: Effectively Managing Your PostgreSQL Connection Pool

Why HikariCP might not be enough and where PgBouncer steps in.

1. Why are Connections Expensive in PostgreSQL?

PostgreSQL forks a separate process at the operating system level for each new connection. This differs from the thread-based model of other databases like MySQL. Each idle connection consumes approximately 5–10 MB of RAM (with default work_mem; this value can be much higher for active queries).

Consequently, the math is simple:

PostgreSQL’s default max_connections value is 100. While increasing this might work in the short term, it directly increases RAM consumption and complicates PostgreSQL’s shared memory management. The correct way to scale the number of connections is to use fewer connections but use them efficiently — that’s exactly where a connection pooler comes in.

2. What is PgBouncer and How Does it Work?

PgBouncer is a lightweight connection pooling proxy that sits between your application and PostgreSQL. Because it’s written in C, its memory footprint is minimal. It fully supports the PostgreSQL wire protocol, making it indistinguishable from PostgreSQL to applications.

Architecturally, it works like this:

[App instance 1] \
[App instance 2] ---> [PgBouncer :6432] ---> [PostgreSQL :5432]
[App instance 3] /

• The application connects to PgBouncer as if it were connecting to PostgreSQL (port 6432). PgBouncer allocates an existing PostgreSQL connection from its pool. When the operation (transaction or session) finishes, the connection is returned to the pool.
• Effect: 500 application connections → PgBouncer → only 20 actual PostgreSQL connections. From PostgreSQL’s perspective, there are only 20 clients.

3. PgBouncer Pooling Modes

PgBouncer has three main modes that determine when connections are returned to the pool. Choosing the right mode for your needs is critical:

Session Pooling

• Connection Assignment: A database connection is allocated when the client (application) connects.
• Return Process: That database connection remains busy until the client completely closes the connection.
• Ideal Use Case: Stateful sessions requiring things like LISTEN/NOTIFY or the use of temporary tables.

Transaction Pooling (Recommended)

• Connection Assignment: A connection is allocated when a database transaction begins.
• Return Process: The connection returns to the pool immediately after the COMMIT or ROLLBACK command runs.
• Ideal Use Case: This is the most efficient mode for most web applications and microservices architectures.

Statement Pooling

• Connection Assignment: A separate connection is allocated for each individual SQL query (statement).
• Return Process: The connection becomes free as soon as the query runs and returns results.
• Ideal Use Case: Systems running only with “Autocommit.”
• Warning: In this mode, BEGIN…COMMIT blocks (transactions containing multiple queries) are not supported.

⚠️ Transaction Mode Gotcha: Prepared Statements

There’s an important limitation in Transaction mode: server-side prepared statements don’t work. The reason is simple — each transaction can go to a different backend connection, so a statement created with PREPARE on one connection cannot be accessed with EXECUTE on another connection.

Furthermore, SET commands and session variables are not preserved outside the active transaction.

The solution — disable prepared statements at the driver level:

• pgx (Go): Add default_query_exec_mode=simple_protocol to the connection string.
• JDBC (Java): Set prepareThreshold=0.
• Alternatively, you can use Session mode, but multiplexing efficiency will decrease.
• Note: The server_reset_query = DISCARD ALL parameter is ineffective in transaction mode — PgBouncer runs this command only in session mode.

4. What’s the Difference with HikariCP?

HikariCP is a widely used in-application connection pool in the Java/Spring Boot ecosystem. It aims for the same goal as PgBouncer — managing connections — but their fundamental approaches differ.

Critical difference: HikariCP keeps a separate pool for each application instance. If you’re running 10 pods and each pod opens 20 connections, 200 connections in total go to PostgreSQL. Because PgBouncer is centralized, these 200 connections are reduced to 20 actual connections in the pool.

5. When is PgBouncer Needed?

If you’re running a single application instance, in-application pooling is mostly sufficient. PgBouncer becomes critical in the following scenarios:

• High Number of Application Instances (Kubernetes): When each pod opens its own pool, the max_connections limit is quickly exceeded.
• Polyglot Architecture: If Go, Python, and Node.js services use the same DB, a centralized pool is a must.
• Serverless (Lambda, Cloud Run): Each invocation tends to open a new connection; PgBouncer absorbs this load.
• Error Management: If you see FATAL: too many connections errors, this is an alarm state.

6. Pros and Cons

Pros:

• Dramatically reduces the number of connections, lowering RAM load.
• Pause mode: Holds connections during maintenance so the application doesn’t get errors.
• Language and framework independent.

Cons:

• Additional infrastructure component (Maintenance overhead).
• Limitations on prepared statements and session variables in Transaction mode.
• Requires a redundant setup for HA (High Availability).

7. Conclusion: Should I Use It?

Single application instance?
└── Yes → HikariCP / pgx pool is sufficient.
└── No → How many instances?
├── 3–5 pods → Monitor max_connections.
└── 10+ pods or serverless → PgBouncer is a must.

PgBouncer is a small binary that solves a big problem. When every new service you add to your infrastructure loads directly onto PostgreSQL, your database finds it hard to breathe. Anticipating this problem in advance is always better than managing a crisis in production.