PgBouncer setup: when connection pooling saves the application
The application opens a connection to Postgres for every request and holds it until processing finishes. While there are only dozens of users, this goes unnoticed.
At peak load, the number of simultaneous connections hits max_connections, and then a cascade begins: new requests get a connection error, the queue for a freed-up slot grows, and response time stretches into seconds.
The worst part is that the failure comes exactly when the load is at its maximum — during a sale, a reporting campaign, or the morning spike.
Every connection in Postgres is a separate process with its own memory. Keeping hundreds of such processes is expensive: the database spends resources switching between them instead of processing queries, and some connections sit idle waiting.
Raising the limit is a dead end: the next peak will hit the ceiling again, only now the failure will be deeper.
PgBouncer solves this differently. The application connects to a pool rather than directly to the database, and the pool reuses a small set of real connections across hundreds of clients.
In transaction mode, a connection is returned to the pool immediately after the transaction completes, so a few dozen backends serve thousands of simultaneous requests.
Setup comes down to three things: choosing the pool mode, setting default_pool_size and max_client_conn, configuring idle timeouts, and correctly separating out the service connections that need session states.
A properly configured pool eliminates connection errors during peaks, smooths out latencies, and reduces the load on the database without rewriting the application code. Let's break down how this is handled in practice.
What a properly configured connection pool delivers
A connection pool is not an "optimization option" but a divider between an application that handles the load and an application that falls over at peak.
When every request opens a new connection to the database, response time grows non-linearly: at 50 concurrent users it is still tolerable, at 300 there is a queue for connections and timeouts.
Below is what exactly changes after configuring PgBouncer, tied to the metrics visible in monitoring.
- Response time stops depending on the number of users. Connections are reused from a ready pool, and the application does not spend time establishing a new connection and authenticating on every request. On the graph, response time holds a flat line where there used to be stepwise growth.
- Connection failures disappear as a class. The database-side connection limit no longer hits a ceiling: excess requests wait in the pool queue for a fraction of a second instead of erroring out. Errors like "too many connections" vanish from the application logs.
- Peak load is absorbed without growing resources. A traffic spike is handled by the pool queue rather than an avalanche of new connections to the database. The database server stops going into memory and CPU overload on every peak.
- Stability under load becomes predictable. Application behavior as traffic grows is described in advance: it is known how many connections we hold, how long we wait, and what happens on overflow. This eliminates "floating" failures that are hard to reproduce.
- Application performance grows without rewriting code. The pool is configured at the infrastructure level, and the application logic does not change. The effect is visible immediately after switching traffic to the pool.
- Server resources are consumed more evenly. The database holds a fixed number of worker processes instead of hundreds of idle connections — the freed memory goes to cache and request processing.
Each point is verified against metrics: response time at percentiles, the number of connection errors, database load at peak. If even one indicator has not moved, the configuration is incomplete, and we see this at the acceptance stage, not in production.
Pool operating modes: comparison of approaches
The pooling mode determines the level at which PgBouncer returns a server connection back to the pool. This affects both throughput under load and compatibility with PostgreSQL features: prepared statements, SET, advisory locks, cursors. Below are the working options that are actually chosen between when configuring.
| Option | When it fits | Limitations |
|---|---|---|
| Session mode | Long sessions, analytics, applications with cursors and temporary tables | The connection is pinned to the client until disconnect — the pool barely saves database resources |
| Transaction mode | Transactional web applications, short queries, high concurrency | State cannot be held between transactions: SET, advisory locks, prepared statements require explicit support |
| Statement mode | Simple queries without transactions, individual utilities | Does not support multi-statement transactions — almost never used under web load |
| Application-side pool | Mixed scenarios where control over the connection lifecycle is needed | The pool lives in each application process — when scaling, the number of connections to the database grows linearly |
For transaction mode, it is critical to set server_reset_query to DISCARD ALL and disable the prepared statement cache on the driver side, or enable their support in PgBouncer itself.
Otherwise, the application will get "prepared statement already exists" errors under load — a classic failure when switching from session mode.
Mixed scenarios are solved by splitting pools: a separate PgBouncer instance for transactional traffic and a separate one for analytics and background tasks.
This removes the conflict of limitations and allows independent configuration of pool_mode, max_client_conn, and default_pool_size for each load profile.
How we implement a connection pool: stages of work
Implementing a connection pool starts not with installing PgBouncer, but with an audit of how the application currently works with the database.
We look at active connections under load, the behavior of the driver and ORM, transaction duration, and the presence of "sleeping" sessions that keep a connection open for hours.
Often already at this stage it becomes clear that some connections are being wasted — for example, background tasks hold a separate pool that does not overlap with web traffic.
Next, we choose the pool mode and distribute the load across groups.
Transaction mode suits most web applications, but it requires that the code does not rely on session state between requests — we verify this separately, because prepared statements and temporary tables behave differently.
If the application uses them actively, we keep session mode for a specific group and do not break the logic just to save connections.
- Connection audit. We capture a load profile: peak number of concurrent sessions, distribution over time, share of idle connections, and the longest transactions.
- Pool design. We determine the number of groups, limits for each, the operating mode, and the rules for routing the application to the required group.
- PgBouncer configuration. We set pool sizes, idle and retention timeouts, per-client limits, and database reconnection parameters.
- Application configuration changes. We switch the driver to the pool port, remove the application's own pool where it duplicates the external one, otherwise the point of the setup is lost.
- Load testing. We run a profile close to production: we check that the number of connections to the database stays within the specified bounds and that response time does not grow at peaks.
- Documentation handover. We provide the configuration, the group scheme, the regulations for changing limits, and diagnostics instructions so that the team can maintain the pool without us.
Separately, we test failure scenarios: restarting PgBouncer, database unavailability, and a traffic spike above the calculated one.
The configuration must survive this without manual intervention — connections recover on their own, and the application receives a clear error instead of a hung request.
What the client receives after setup
We configure the pool so that it can be operated without the author of the work: the configuration, connection diagram, and operating procedures remain in the project repository, not in a chat thread. Below is exactly what we hand over.
- Pool configuration — working
pgbouncer.inianduserlist.txt(passwords in hashed form) fortransactionorsessionmode, with justification for the choice. Themax_client_conn,default_pool_size,reserve_pool_sizeparameters and timeouts are set for the application's load profile, not "by default." - Connection diagram — a diagram: application → pool → PostgreSQL, indicating which services go through PgBouncer and which (migrations, analytics,
LISTEN/NOTIFY) go directly. Points of failure and behavior when the pool is unavailable are marked separately. - Access policies — which roles and databases are visible through the pool, address restrictions, a separate user for monitoring. The rules are aligned with the current permission model in the database so that the pool does not become a workaround.
- Monitoring and metrics — export of pool metrics (utilization, waiting, failures) to your observability system, a dashboard with key indicators, and thresholds for alerts.
- Operating procedures — what to do when the pool is exhausted, how to safely reload the configuration without dropping connections, how to fall back to a direct connection if the pool has degraded.
- Documentation — a brief description of the architecture, instructions for changing parameters, and a post-release verification checklist. It is aimed at the on-call engineer, not at the person who implemented it.
- Verification plan — the load scenarios used to confirm the result, and the recorded values before and after: the number of connections to the database, response time under peak load.
We hand all of this over to your repository and documentation system, with a walkthrough on a call. Describe your task — we will get back to you with an estimate and a plan.
Case study: how a connection pool eliminated failures under peak load
The client — a service with a personal account area and payments — came with a classic picture: during the day the application kept response times normal, but during peak hours, when a mailing and a surge of users coincided, some requests failed with a database connection error.
The logs showed that the application process opened a connection for every request and held it until the end of the transaction, and the database hit its limit on concurrent connections.
Each new connection is a separate process on the database side, and during a spike their number grew faster than the database could service them.
PgBouncer was placed between the application and the database in transaction pooling mode: a connection returns to the pool immediately after the transaction completes, rather than hanging until the session closes.
The number of real connections to the database was fixed at a level it handles comfortably — the rest the application gets from the pool instantly.
Idle timeouts and a per-client limit were configured separately so that a single "stuck" connection wouldn't eat up the pool.
Metrics before and after
| Metric | Before | After |
|---|---|---|
| Connection failures at peak | regular, up to several percent of requests | isolated, within the margin of error |
| Response time at peak | grew severalfold, some requests didn't respond | stable, without drops |
| Concurrent connections to the database | hit the limit | a fixed ceiling with headroom |
| Behavior during a traffic spike | degradation and errors | smooth growth, without failures |
An important effect is predictability: the database stopped being a bottleneck, and peak load is now handled by the pool rather than by spare server resources.
If your response times sag specifically during peak hours and you see connection failures in the logs — describe your task, and we'll come back with an estimate and a configuration plan for your load profile.
How Transaction Mode Differs from Session Mode
Session mode pins a server connection to a client for the entire session: as long as the application holds the connection, it monopolizes one PostgreSQL backend.
This is the only mode where all server capabilities work — session-level prepared statements, temporary tables, SET and LISTEN/NOTIFY, advisory locks, cursors outside a transaction.
The price is scalability: each application connection requires a separate process in the database, and with hundreds of concurrent users PostgreSQL hits memory and context-switching limits long before the CPU is exhausted.
Transaction mode hands the server connection to the client only for the duration of a transaction and returns it to the pool immediately after COMMIT or ROLLBACK.
A single backend serves dozens of clients sequentially, so a pool of hundreds of applications gets by with a couple dozen real database connections.
This is the default mode for web applications: a typical query lives for milliseconds, while the gap between queries from a single user can last for seconds — holding a connection for that is wasteful.
The limitations of transaction mode stem from the fact that the client does not own the backend between transactions. Session-bound state is lost: prepared statements, temporary tables, changes to search_path and parameters via SET, session advisory locks.
Cursors with WITH HOLD and LISTEN/NOTIFY also do not carry over. Workarounds include SET LOCAL inside a transaction instead of SET, named prepared statements with explicit management, and moving background tasks to a separate pool in session mode.
Choosing a mode is a choice between compatibility and density. If an application uses session state or works through a driver with implicit prepared statements, start with session mode and gradually move hot paths to transaction mode.
A mixed setup is normal: two pools on different ports, transaction mode for web traffic, session mode for migrations, analytics, and background workers. The connection limit is set on the pool side, not the application side — otherwise pooling loses its purpose.
Technical doubts before implementing a pool
Before enabling a pool, almost every team goes through the same list of doubts: "will it break the ORM", "will transactions fall apart", "will migrations go through", "what will happen to prepared statements". Below is a point-by-point breakdown, without generalities.
ORM and prepared statements — don't they conflict?
The transaction pooling mode returns a connection to the pool after the transaction completes, not the session. A prepared statement created in one connection may execute in another — and the server will return the error "prepared statement does not exist".
This is solved either by switching the driver to a mode without server-side preparation (prepareThreshold=0 for JDBC, prepare: false for node-postgres), or by enabling max_prepared_statements in the pool itself.
The choice depends on the load profile: if preparation gives a noticeable gain on hot queries — we keep it on the pool side.
Won't transactions and locks lose integrity?
In transaction pooling, all statements of a single transaction must go through one connection — the pool guarantees this and does not hand it to other clients until COMMIT or ROLLBACK.
The problem arises with SET, temporary tables, and session-level advisory locks: they do not survive the return of the connection. We move such places to session pooling selectively or rewrite them to use the transaction-level lock.
Migrations and background tasks — a separate pool?
Migrations, pg_dump, analytical exports, and long-running background tasks hold a connection for minutes. If we let them through the shared pool, they will consume the entire limit and take down web traffic.
Therefore, for them we spin up a separate pool with a small number of connections and a separate user — the web application and service processes stop competing for the same set.
How to roll back if something goes wrong?
A pool is a proxy between the application and the database; it does not touch the data schema. Rolling back means switching the connection string back to the direct database address; it takes minutes and requires no migrations.
Therefore, it is safe to implement in stages: first one service, measure latency and connection count, then the rest.
All four concerns are closed at the design stage — before the pool enters the production loop.
Let's discuss your task and come back with an estimate
To come back with an estimate and a work plan, we need a minimum of inputs: the current PostgreSQL and PgBouncer configuration (pool mode, connection limits, timeouts), the nature of the load — how many concurrent users, whether there are long transactions and heavy reports, how the application holds connections (a pool on the application side or a new connection per request). That is enough to understand the bottleneck and propose a pool mode without guessing.
Then we work step by step:
- analyze the current metrics: the number of active and idle connections, peak queues, timeout failures;
- determine the target pool mode and limits for the load profile, not "by default";
- check compatibility with the driver and ORM: prepared statements, transactions, sessions;
- prepare the configuration and a zero-downtime switchover scheme — via a second instance and traffic cutover;
- run a load test on a copy of the production database and capture before/after metrics;
- roll out to prod with a rollback plan and observability for connections and latency;
- record the final parameters and hand over the documentation to your team.
What we need from you to start: access to the configuration and metrics, a window for the test run, and a contact for an engineer who knows how the application opens connections. The rest is our area: configuration, switchover scheme, tests, and rollback.
Describe your task — we'll come back with an estimate, a work plan, and clear next steps.