Topic: PostgreSQL

PgBouncer does not make PostgreSQL infinite: budget connections before choosing a pool mode

Use the boundaries between connections, sessions, and transactions to reason about PostgreSQL connection budgets, PgBouncer modes, session-state compatibility, and `pg_stat_activity`.

When an API reaches too many connections, the first impulse is often to raise max_connections. Sometimes that is necessary, but it is not a free scaling switch. PostgreSQL creates a server process for a client connection, and max_connections affects some resource allocation. The official documentation defines it as the limit on concurrent database connections, not the number of SQL statements the database can run per second.

Connection pooling solves a different problem. It lets many client connections reuse a smaller, controlled set of PostgreSQL server connections over time. That cuts connection setup work and stops each application instance from independently pushing the database to its limit. A slow query that is already running does not become fast merely because PgBouncer sits in front of it.

Separate connections, sessions, and transactions first

These words are often used interchangeably, which is how pooling configuration becomes confusing:

PgBouncer accepts client connections, then lends real PostgreSQL connections from a pool. The question is not whether a pool exists. It is when a borrowed connection can safely be handed to another client.

Imagine a service growing from two containers to eight, with an application pool maximum of ten connections in each container. That configuration alone can demand 80 database connections. It excludes background workers, admin tools, overlapping old and new deployments, and operational headroom. A pool that looks small in one container can still exceed the database budget in total.

max_connections is a budget, not a performance knob

Measure the current state before allocating a budget. pg_stat_activity exposes one row of activity for each server process, which lets us split “how many connections” into “how many are active, idle, or left inside a transaction.” A useful first aggregation is:

SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY state;

An ordinary idle connection still exists but is not executing a query. It is not automatically a leak. idle in transaction deserves earlier investigation because the transaction is still open. It can hold a lock or an old snapshot, making other work and cleanup harder. The pg_stat_activity documentation is the source of truth for its fields and states.

A connection budget needs every application instance, worker, admin connection, and some usable slack. Do not treat each service’s pool maximum as unrelated. Before raising max_connections, look at total server connections, memory, query concurrency, and peak wait time rather than reacting only to an error message.

Pool mode sets the reuse boundary

PgBouncer has three modes, distinguished by when it returns a server connection to the pool:

• session returns a connection after the client disconnects. It fits admin tools, listeners, and work that needs a fixed session; it offers the least reuse, but session state persists.
• transaction returns a connection after a transaction finishes. It fits many short, independent OLTP requests; the next transaction cannot assume the same session.
• statement returns a connection after each query. It only fits rare, single-statement work; multi-statement transactions are not allowed.

PgBouncer’s configuration documentation defines these return points. Transaction pooling is often worth evaluating for OLTP, or Online Transaction Processing: frequent, short online operations such as creating an order, reading account data, or updating inventory. That differs from long-running reports, analytics, or ETL work that scans and transforms much more data.

Transaction pooling trades session state for reuse

Transaction pooling works because another client can borrow the server connection after a transaction ends. The next transaction may therefore run in a different PostgreSQL session. Do not assume these needs carry over through transaction pooling:

• A temporary table used across transactions.
• A long-lived LISTEN/NOTIFY listener.
• A session-level SET setting expected to survive into a later transaction.
• Coordination that depends on a session-level advisory lock.

When a setting belongs to one transaction, keep it inside that transaction. SET LOCAL expires with the transaction. That is a different design from setting something at application startup and assuming the same server connection will remain available. PostgreSQL’s SET documentation describes the session and transaction-local lifetimes.

BEGIN;
SET LOCAL statement_timeout = '2s';
SELECT * FROM orders WHERE id = 42;
COMMIT;

Prepared statements also need a driver, PgBouncer version, and configuration check. Current PgBouncer can track protocol-level named prepared statements in transaction pooling when max_prepared_statements is enabled. SQL-level PREPARE and EXECUTE do not use that same transparent rewrite mechanism. The PgBouncer FAQ documents the boundary. If work depends on session state, a direct connection or session pooling is usually easier to maintain than forcing it through transaction pooling.

max_client_conn can queue clients, not create database capacity

max_client_conn controls how many client connections PgBouncer can accept. default_pool_size is the default server-connection limit for each user/database pool. Neither number is the database’s global execution capacity. Different users or databases can form several pools, so the total server-connection count can add up across them. A whole-database bound may also need max_db_connections.

If clients are waiting in PgBouncer while available server connections are all executing queries, raising max_client_conn only allows more clients to queue at the entrance. It does not reduce scanned rows, remove a lock, or add CPU. Look at query duration, wait types, pool queues, active connections, and application concurrency together. A recent PostgreSQL community discussion describes the same boundary: transaction pooling can reduce connection setup and idle-session pressure, but it cannot eliminate genuinely concurrent high-load queries.

Use a compatibility checklist and a load test before rollout

I would roll out a pooler with a small set of checks instead of applying a fixed “connections per CPU” formula:

1. List the pool maxima for every service, worker, and instance, then calculate the possible peak demand.
2. Use pg_stat_activity to separate active, idle, and idle in transaction connections, then identify the real source of pressure.
3. Search for temporary tables, LISTEN, session-level settings, advisory locks, and prepared statements. Choose a direct, session, or transaction path for each need.
4. Test with representative traffic, watching pool queues, query latency, timeouts, and error rates before adjusting limits.

The aim is not the smallest possible connection count. It is a predictable concurrency level with room for operations during a peak. A pooler is a protective boundary, not a black box that hides queues and slow queries.

What I learned

• I will add up pool limits across every application instance before choosing a PostgreSQL connection budget. A single container’s setting can hide the total.
• Transaction pooling returns a connection at the transaction boundary. It fits short, independent OLTP work, while session-state needs require a compatible connection path.
• max_client_conn increases admission or waiting space, not the database’s ability to execute a slow query.
• Before rollout, I should measure connection state, pool queues, and query duration while preserving room for administrative work and spikes.

External references

• PostgreSQL: Connections and Authentication
• PostgreSQL: Monitoring Database Activity
• PostgreSQL: SET
• PgBouncer: Configuration
• PgBouncer: FAQ
• Canonical: Connection pooling
• Reddit: PostgreSQL high connection load with PgBouncer

Further learning

• Canonical: Connection pooling gives a short introduction to pooling and PostgreSQL process cost.
• PgBouncer: Configuration is the reference for pool modes, queues, and pool-size semantics.
• EDB: Postgres Connection Pooling is an English public video that introduces PgBouncer and pgpool. Use the current official documentation for configuration details.