PostgreSQL Connection Pooling with PgBouncer Guide

PgBouncer is one of the highest-leverage PostgreSQL tools you can add to a busy application stack.

Not because it makes individual queries faster. It usually does not.

What it does is make connection handling much more efficient.

That matters because PostgreSQL is process-based. Every direct client connection has real cost:

• memory
• process overhead
• authentication work
• coordination overhead
• and operational pressure once connection counts grow

If your application fleet grows faster than your database should, connection management becomes part of performance.

That is exactly the problem PgBouncer is built to solve.

This guide explains:

• what PgBouncer actually does
• when to use it
• how the pool modes work
• which settings matter most
• how prepared statements fit in now
• and the production mistakes to avoid

What PgBouncer Actually Does

PgBouncer sits between your application and PostgreSQL.

Instead of every client holding its own dedicated PostgreSQL backend connection, PgBouncer allows many client connections to share a smaller number of server-side connections.

That means your application can have:

• many connected clients
• bursty request traffic
• or horizontally scaled workers

without forcing PostgreSQL to hold the same number of active backend connections.

Why this helps

PostgreSQL’s max_connections is not something you should increase casually.

As PostgreSQL’s own docs note, increasing max_connections increases allocation of certain resources, including shared memory. That is one reason connection pooling matters so much in real systems. :contentReference[oaicite:1]{index=1}

The real goal

PgBouncer helps you separate:

• client concurrency from
• database backend concurrency

That is often the difference between:

• an app that scales awkwardly and
• one that can absorb more traffic without turning the database into a connection-management bottleneck

When PgBouncer Is Most Useful

PgBouncer is especially helpful when you have:

• many application instances
• bursty API traffic
• serverless or autoscaling workloads
• background workers with many short-lived queries
• a database that should not accept hundreds or thousands of direct client connections
• applications where transaction duration is short and predictable

It is often a very good fit for:

• web APIs
• SaaS backends
• job-processing systems
• microservice fleets
• managed PostgreSQL stacks
• multi-tenant applications with many workers

It is less magical when the main problem is:

• slow queries
• missing indexes
• lock contention
• bad pagination
• or long-running transactions

PgBouncer is not a replacement for query tuning. It is a solution to connection pressure.

The Three PgBouncer Pool Modes

PgBouncer supports three pooling modes:

• session
• transaction
• statement

Understanding these is the most important part of using PgBouncer correctly.

1. Session pooling

In session mode, a server connection is assigned to a client for the lifetime of that client session.

That means:

• the client keeps the same PostgreSQL backend while connected
• session-level state behaves as expected
• and compatibility is the strongest

Best for

• applications that rely on session state
• applications using features incompatible with transaction pooling
• tools that expect long-lived session semantics
• safer first migrations when you want PgBouncer without changing application assumptions much

Trade-off

You get less multiplexing benefit than transaction pooling.

That means session pooling is more compatible, but not as efficient for high client-count workloads.

2. Transaction pooling

In transaction mode, PgBouncer releases the server connection back to the pool as soon as the transaction ends.

This is the mode most people mean when they talk about “using PgBouncer properly” for modern high-concurrency apps.

Why it is so useful

It gives you far more multiplexing benefit than session mode because backend connections are reused much more aggressively.

This is usually the best fit for:

• request/response applications
• short transactions
• well-behaved ORMs and service backends
• systems where most work is naturally scoped to quick transactions

The big warning

Transaction pooling changes session semantics.

PgBouncer’s feature matrix explicitly warns that transaction pooling breaks some client expectations by design. In this mode, session-level features like SET/RESET, LISTEN, WITH HOLD cursors, session-level advisory locks, and normal PREPARE/DEALLOCATE SQL behavior are not compatible in the usual way. :contentReference[oaicite:2]{index=2}

That means your application must cooperate with transaction pooling.

Best for

• stateless app requests
• short, explicit transactions
• high-concurrency API servers
• background workers with fast database operations
• reducing backend connection pressure significantly

3. Statement pooling

In statement mode, the server connection is released after each statement.

This is the most aggressive pooling model.

Why it is rare

Statement pooling does not allow multi-statement transactions, so it is unsuitable for many normal applications.

It is powerful when the workload truly fits:

• single-statement behavior
• autocommit style usage
• very strict pooling efficiency

But for most production application stacks, statement mode is not the first recommendation.

Which pool mode should you choose?

A simple practical rule works well:

Choose transaction mode if:

• your app uses short transactions
• you want the main multiplexing benefits
• you do not rely on session-level state in unsafe ways

Choose session mode if:

• your app depends on session state
• you are using features that transaction pooling breaks
• compatibility matters more than maximum connection efficiency

Choose statement mode if:

• you know exactly why you need it
• and your application is designed around that restriction

For most web backends, transaction mode is the best default if the application is compatible.

The Most Important PgBouncer Settings

PgBouncer has many settings, but a smaller set does most of the real work.

pool_mode

This defines whether pooling is:

• session
• transaction
• or statement

Example:

pool_mode = transaction

For many production applications, this is the most important line in the config.

max_client_conn

This sets how many client connections PgBouncer will accept.

The official PgBouncer config docs list the default as 100. They also note that increasing this can require raising OS file descriptor limits, because total descriptor use can exceed max_client_conn. :contentReference[oaicite:3]{index=3}

Example:

max_client_conn = 1000

Practical note

max_client_conn is not the same thing as how many PostgreSQL backend connections you want. It is how many clients PgBouncer will accept.

default_pool_size

This sets how many server connections are allowed per user/database pair by default.

The official docs list the default as 20. :contentReference[oaicite:4]{index=4}

Example:

default_pool_size = 20

Why it matters

This is one of the most important sizing controls because it limits backend pressure.

Too small:

• requests queue unnecessarily

Too large:

• PostgreSQL backend count grows more than needed

reserve_pool_size and reserve_pool_timeout

These settings let PgBouncer allow additional connections after a client has waited long enough.

The docs describe reserve_pool_size as extra connections available to a pool and reserve_pool_timeout as the wait time before they are used. Defaults are:

• reserve_pool_size = 0
• reserve_pool_timeout = 5.0 seconds :contentReference[oaicite:5]{index=5}

Example:

reserve_pool_size = 5
reserve_pool_timeout = 2

Why this helps

Reserve pools can soften short bursts without permanently raising the main pool size too high.

max_db_connections and max_db_client_connections

These allow you to cap connections at the database level, not just globally.

That matters in multi-tenant or multi-database PgBouncer setups where one database should not monopolize everything.

max_user_connections and max_user_client_connections

These let you contain specific users more tightly.

That is especially useful for:

• noisy background jobs
• reporting users
• untrusted workloads
• or service classes you want to isolate

How to Size PgBouncer Practically

This is one of the most common operational questions.

The best sizing process usually starts from the database backward, not the application forward.

Step 1: Decide how many PostgreSQL backend connections you actually want

This should reflect:

• database CPU
• workload type
• memory pressure
• query mix
• and whether the database is also serving replicas, maintenance, or other services

Do not simply set PostgreSQL max_connections high and call it scaling.

Step 2: Decide what per-database or per-user pool size is reasonable

This becomes your default_pool_size or more specific per-database pool size.

Step 3: Let PgBouncer absorb the higher client count

Now max_client_conn can be much larger than the actual number of PostgreSQL backends, because many clients are multiplexed over fewer server connections.

Practical pattern

It is very normal to see:

• hundreds or thousands of clients
• sharing dozens of real PostgreSQL server connections

That is the point.

A Simple Starting Configuration

A simple production-leaning baseline might look like this:

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

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 2
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres, pgbouncer_admin
stats_users = postgres, pgbouncer_admin

That is not universally correct, but it is a reasonable shape for many app workloads.

Prepared Statements and PgBouncer

This is one of the biggest topics people still get wrong because PgBouncer behavior improved here over time.

PgBouncer’s current docs state that protocol-level prepared plans are supported in transaction pooling if max_prepared_statements is set to a non-zero value. The config docs explain that PgBouncer can track and rewrite protocol-level named prepared statements in transaction and statement modes, making sure a statement prepared by the client is available on the server connection it gets assigned later. :contentReference[oaicite:6]{index=6}

What this means in practice

Prepared statements are no longer an automatic reason to reject transaction pooling entirely.

But you still need to understand the distinction:

• protocol-level prepared statements can work with the right PgBouncer setting
• plain SQL PREPARE / EXECUTE semantics are different and less compatible in pooled modes

Good practical advice

If your application or driver relies on prepared statements:

• verify whether it uses protocol-level prepared statements
• test with transaction pooling explicitly
• configure max_prepared_statements thoughtfully
• do not assume all “prepared statements” are identical from PgBouncer’s perspective

Example

max_prepared_statements = 100

The right number depends on the application’s frequently used prepared statement set.

Trade-off

Higher values improve compatibility and prepared-statement reuse, but also increase memory usage and some CPU overhead in PgBouncer because it must inspect and rewrite more prepared-statement traffic. The PgBouncer docs discuss this trade-off directly. :contentReference[oaicite:7]{index=7}

Features That Break or Need Care in Transaction Pooling

This is where many PgBouncer rollouts go wrong.

PgBouncer’s features matrix is the source of truth here, and the practical message is simple:

transaction pooling is great when the app is stateless between transactions.
It is risky when the app quietly relies on session state. :contentReference[oaicite:8]{index=8}

Be careful with or avoid relying on:

• session-level SET changes that must persist
• LISTEN / UNLISTEN patterns
• WITH HOLD cursors
• SQL-level PREPARE
• session-level advisory locks
• temp tables that expect session persistence
• application logic that assumes one client always talks to one backend session

Better habits

• keep transactions short
• keep session state out of application assumptions where possible
• prefer explicit per-transaction behavior
• test driver behavior, not just SQL behavior

PgBouncer and ORMs

Many teams meet PgBouncer through an ORM, not a raw driver.

That means you should test:

• connection lifecycle
• transaction handling
• prepared statement behavior
• session-variable behavior
• migration tooling
• and long-running management commands

Common ORM risks

• implicit session state
• long-lived transactions
• migration tools that expect session pooling behavior
• idle transactions
• prepared statement assumptions that differ by driver version

Good rule

Treat ORM + PgBouncer compatibility as something to verify, not assume.

Use Short Transactions

PgBouncer works best when transactions are short.

That is especially true in transaction pooling, because the backend connection is reusable only after the transaction ends.

Long transactions reduce the pooling benefit and increase contention pressure at the same time.

Better pattern

• open transaction late
• do minimal DB work
• commit quickly

Worse pattern

• begin transaction
• call other services
• perform slow application logic
• then finally write and commit

PgBouncer is not a fix for long transaction design. It benefits most from short, clean transactions.

PgBouncer Is Not a Slow Query Fix

This is worth stating clearly.

PgBouncer improves connection efficiency. It does not fix:

• bad indexes
• missing indexes
• deep offset pagination
• poor joins
• N+1 query patterns
• table bloat
• or lock contention

In fact, if your transactions are slow, pooling may simply make it easier to saturate the database with more concurrent pressure.

Use PgBouncer for:

• connection scalability
• backend connection containment
• better client/backend multiplexing

Use query tuning for:

• actual SQL speed

You often need both, but they solve different problems.

Operational Tips for Production

1. Put PgBouncer where it makes sense operationally

Common deployment patterns include:

• sidecar or local host near the app
• dedicated PgBouncer tier
• managed-service-provided pooler
• small pooler fleet behind internal load balancing

The right pattern depends on:

• your platform
• failover design
• and operational simplicity

2. Watch PgBouncer stats

PgBouncer has SHOW commands and stats surfaces that matter.

You want visibility into:

• active clients
• waiting clients
• server connection usage
• pool saturation
• per-database or per-user pressure
• average wait behavior

Connection pooling without observability becomes guesswork quickly.

3. Set application_name intentionally

PgBouncer supports application_name_add_host, which can append client host and port to the application name at connection start. This can help identify bad queries or noisy sources more easily. :contentReference[oaicite:9]{index=9}

That is not mandatory, but it can be very useful operationally.

4. Mind file descriptor limits

PgBouncer’s docs explicitly warn that when max_client_conn is increased, OS file descriptor limits may also need to be increased because the total file descriptor requirement can exceed the raw client-connection count. :contentReference[oaicite:10]{index=10}

This is a classic rollout gotcha.

5. Separate admin and stats users

Use:

• admin_users
• stats_users

deliberately so operational access is controlled.

Common Mistakes With PgBouncer

1. Using transaction pooling without checking app compatibility

This is the biggest one.

2. Treating PgBouncer as a substitute for query tuning

It is not.

3. Setting pool sizes by guesswork

Size from the database backward.

4. Raising max_client_conn without OS descriptor planning

This can create operational problems fast.

5. Forgetting prepared statement behavior changed

Modern PgBouncer can support protocol-level prepared statements in transaction mode with the right setting, but you still need to understand exactly how your driver behaves. :contentReference[oaicite:11]{index=11}

6. Leaving long transactions in the app

That weakens pooling benefits.

7. Assuming replicas or failover behavior are automatic

Connection pooling still needs to fit your HA design.

When PgBouncer Is a Great Fit

PgBouncer is a great fit when:

• the application has many clients
• transactions are short
• connection churn is high
• the database should stay at a sane backend connection count
• and the app does not depend heavily on session-bound behavior

It is especially powerful for:

• APIs
• worker fleets
• SaaS backends
• horizontally scaled web apps
• multi-tenant services

FAQ

What is PgBouncer used for in PostgreSQL?

PgBouncer is a lightweight connection pooler that sits between your application and PostgreSQL so many client connections can share a smaller pool of backend database connections.

What is the best PgBouncer pool mode?

For many web applications, transaction pooling is the best starting point because it gives the best multiplexing benefits. Session pooling is safer when the application depends on session-level state.

Does PgBouncer work with prepared statements?

Yes, but the details matter. Protocol-level prepared statements can work in transaction pooling when PgBouncer is configured correctly with max_prepared_statements, while SQL-level PREPARE behavior is more limited in pooled modes.

Should I put PgBouncer in front of every PostgreSQL app?

Often yes for high-concurrency applications, but the exact setup depends on whether the app uses session state, long transactions, advisory locks, temp tables, or other behaviors that do not fit transaction pooling well.

Conclusion

PgBouncer is one of the best ways to make PostgreSQL handle higher client concurrency without forcing the database to hold the same number of backend connections.

That is the real win.

The key decisions are:

• choosing the right pooling mode
• sizing the server pool sensibly
• understanding prepared statement behavior
• keeping transactions short
• and verifying that the application does not depend on incompatible session features

For many production workloads, the best default is:

• transaction pooling
• short transactions
• measured pool sizing
• explicit compatibility testing
• and good observability

When you do that well, PgBouncer becomes one of the simplest and most effective PostgreSQL scaling tools you can deploy.