PostgreSQL Connection Pooling with PgBouncer

A web application that works smoothly in testing can grind to a halt in production with errors such as "too many connections" or requests that hang waiting for the database. Often the database itself is not overloaded with queries; it is overloaded with connections. Each PostgreSQL connection is a separate server process with its own memory, and applications that open connections freely, across many instances and background workers, can exhaust the server's limits. PostgreSQL connection pooling keeps a small set of database connections open and shares them among many clients. This guide explains why connections are expensive, the pooling options, how to size pools, the pitfalls of transaction pooling and what to monitor.

Why connections are expensive

PostgreSQL limits the number of concurrent connections with the max_connections setting. The PostgreSQL documentation on connection settings says the default is typically 100 connections, and that PostgreSQL sizes certain resources based directly on this value, so increasing it increases allocation of those resources, including shared memory. Raising the limit is therefore not a free fix, and it does nothing about the idle connections that caused the problem.

Applications multiply connections quickly. Ten application instances, each with a pool of 20, plus background workers and scheduled jobs, can try to open several hundred connections. Many of those connections sit idle most of the time, holding resources without doing work.

Where PostgreSQL connection pooling happens

Pooling can happen in two places, and most production systems use both.

Application-side pools: most database drivers and frameworks maintain a pool per application instance, reusing connections across requests. They reduce the cost of opening connections but do not limit the total across instances.

A central pooler: a separate service between applications and the database, such as PgBouncer, that accepts many client connections and maps them onto a much smaller number of server connections. This controls the total no matter how many application instances run.

Managed database services often offer a built-in pooler; check yours before running your own.

Pool modes

PgBouncer's features page describes three modes:

• Session pooling: a server connection is assigned to a client for as long as the client stays connected; all PostgreSQL features work
• Transaction pooling: a server connection is assigned only during a transaction and returned to the pool afterwards; this breaks some session-based features
• Statement pooling: connections are returned after each statement, and multi-statement transactions are disallowed

Transaction pooling gives the biggest reduction in server connections for typical web applications, because most requests run short transactions. Session pooling helps less, because each client still holds a connection while connected.

Transaction pooling pitfalls

In transaction mode, consecutive transactions from the same client may run on different server connections. Anything that relies on session state can misbehave. PgBouncer's features page lists features that never work in transaction pooling, including SET and RESET, LISTEN, cursors declared WITH HOLD, and SQL-level PREPARE and DEALLOCATE. In practice:

• Set session parameters per transaction, for example with SET LOCAL inside the transaction, rather than once per connection
• Use a direct connection, not the transaction pooler, for features such as LISTEN
• Check how your driver handles prepared statements, as protocol-level prepared statements behave differently from SQL PREPARE
• Keep transactions short, since a long transaction holds a server connection for its whole duration

Roll out gradually where you can, moving one service or worker type at a time. Test the application against the pooler in a staging environment before switching production, using realistic concurrency, and watch the logs for errors that only appear when consecutive transactions land on different server connections.

Sizing pools

Bigger pools are not faster. Beyond a point, more concurrent queries compete for CPU, memory and disk, and everything slows. Start small and measure:

• Set the central pool size per database and user well below max_connections, leaving room for administration and maintenance connections
• Keep application-side pools modest, since the central pooler does the real limiting
• Watch for clients waiting for a connection; persistent waiting suggests the pool is too small or queries are too slow
• Fix slow queries and long transactions before enlarging pools

Worked example: a multi-tenant SaaS behind PgBouncer

Suppose a multi-tenant SaaS application runs twelve web instances and four background workers against one PostgreSQL server. During peaks it hits connection errors. The team places PgBouncer in front of the database in transaction mode. This simplified, untested configuration shows the main settings; the host, names and numbers are placeholders to adjust after measurement.

[databases]
appdb = host=10.0.0.5 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20

Applications connect to port 6432 instead of the database directly. Up to 2,000 client connections share a pool of 20 server connections per database and user pair, according to the meaning of these settings in PgBouncer's configuration reference. The team moves a LISTEN-based notification feature to a direct connection, replaces per-connection SET statements with SET LOCAL, and plans to compare connection errors, wait times and query latency before and after the change.

Monitoring

Watch both the pooler and the database:

• Client connections and server connections in use, per pool
• Clients waiting for a connection, and how long they wait
• Transaction and query durations, especially long-running transactions
• Database connections by state, including idle in transaction
• Errors from the pooler, such as rejected clients

Connections stuck "idle in transaction" are a common cause of exhausted pools, usually from application code that opens a transaction and then waits on something else. Our guide to Zoho CRM PostgreSQL sync covers a workload where long-running sync jobs need careful transaction handling.

Multi-tenant considerations

Multi-tenant applications add questions. If each tenant has its own database or schema, pools can multiply, since a pooler keeps pools per database and user. Group tenants where possible, or route through a shared database with tenant isolation in queries. If you set the tenant per session, for example through a session variable used by row-level security, transaction pooling requires setting it per transaction instead. Our guide to client portal performance covers other techniques for keeping multi-tenant applications responsive.

Common mistakes

• Raising max_connections instead of pooling
• Large application-side pools on many instances, with no central limit
• Switching to transaction pooling without checking session-dependent features
• Long transactions that hold server connections
• Enlarging pools to hide slow queries
• Not monitoring clients waiting for connections

Frequently Asked Questions

What is PostgreSQL connection pooling?

It keeps a set of database connections open and shares them among many clients, so applications can serve many requests without opening a connection for each.

Should I just increase max_connections?

Usually not. PostgreSQL sizes resources based on max_connections, so raising it costs memory. A pooler controls connections more efficiently.

Which PgBouncer mode should I use?

Transaction pooling suits most web applications with short transactions, but breaks session-based features such as SET and LISTEN. Session pooling keeps all features.

How big should a connection pool be?

Smaller than most expect. Start modest, keep the total well below max_connections, and measure waiting clients and query times before enlarging it.

Keep your database responsive under load

We diagnose connection and query bottlenecks and set up pooling that suits your application. See our custom app development services, or talk to us about your database performance.

About the Author

Manav Sharma is the founder of Insyrge and a CRM solutions architect. He works across Zoho, Salesforce, HubSpot, Odoo and more, designing the CRM setups, integrations and automations that keep sales and operations teams working from the same data.