PostgreSQL connection pooling for small apps

Quick answer: Start with a database connection budget, then limit how many connections your application instances can open in total. An application pool reuses connections inside each process; a proxy such as PgBouncer can let many clients share a smaller backend pool across processes. If eight instances may each open 12 connections, they can request 96 at once—more than a chosen 90-connection app budget. A proxy can queue admission, but it cannot make slow queries or long transactions disappear.

Important constraint: Transaction pooling changes connection identity. A client can use one PostgreSQL server connection for a transaction and another for its next transaction, so session-level assumptions must be checked. Prepared statements are not one blanket yes-or-no case: PgBouncer documents configurable support for protocol-level named prepared statements, while SQL-level PREPARE and driver behavior need separate review. Sources: PgBouncer features, configuration, FAQ.

Connection pooling is most useful when a small app grows from one process into several workers, replicas or short-lived instances. Each may keep its own idle connections. The database sees the sum of those pools, even when no one process looks large. This guide uses PostgreSQL’s connection limit and PgBouncer’s published modes as examples; choose actual settings from measurements of your database, driver and workload rather than copying the sample numbers.

Diagnose the limit before adding a pooler

A connection-limit error can appear during a scale-out, deploy overlap or traffic burst. First identify the actual PostgreSQL max_connections setting and the slots reserved for administration or other clients. PostgreSQL’s connection settings explain that max_connections is a concurrent server-connection ceiling, with resource implications when raised and restrictions on ordinary clients near reserved limits. The common default is not proof of your managed database’s current cap.

Count where connections come from: web processes, job workers, migration tasks, admin tools, monitoring, and any old instances still draining during a deploy. Inspect active versus idle sessions, connection acquisition time, transaction duration and query load. A server at its connection ceiling with many idle sessions suggests a different remedy from a server whose connections are occupied by long-running transactions. A pooler can reduce backend connection count in the first case; in the second, it may simply move the wait from PostgreSQL to the proxy.

An application pool is a useful first control. It keeps a bounded number of reusable database connections per process and makes callers wait when that process has no free slot. But its limit is per process, not a global ceiling. Adding replicas or background workers multiplies possible concurrent connections unless the deployment has a shared admission layer or coordinates limits across instances.

Check the shape of the workload at the time errors occur. A sudden connection spike during a deploy can be caused by old and new instances overlapping; a slow increase in idle sessions points toward lifecycle or pool settings; a queue that grows while every backend runs a long query points toward database work rather than idle-connection waste. Record these as observations before selecting a remedy. Otherwise the team may add a proxy, move the bottleneck to its waiting clients, and still see the same slow requests under a different error message.

Calculate an upper-bound budget

Suppose a database has an actual cap of 100 concurrent server connections. For planning, reserve 10 for operations, migrations and other clients. That leaves a deliberately chosen 90-connection app budget. This is an example allocation, not a PostgreSQL default or a recommendation that every database reserve exactly 10.

These are upper bounds if each instance fills its pool, not measurements of demand. They expose a configuration risk before a surge or deployment doubles the number of processes. Add separate API workers, queue consumers and scheduled tasks to the same worksheet. A rolling deploy can briefly keep old and new workers alive together, so calculate that overlap too. If the expected peak instance count is 12, a limit chosen only for eight is incomplete.

The budget is also about admission. If you reduce each pool so the sum fits, a caller may wait longer for a connection; if it times out, the app needs a useful response or retry rule. If the workload really requires more simultaneous active queries than the database can handle, a smaller connection number alone will not make it fast. Review query duration, transaction scope and database capacity separately.

App pool versus proxy pool

An app pool usually lives in each application process. It reuses connections for that process, but a new process creates another pool. A proxy pool such as PgBouncer sits between many client connections and PostgreSQL server connections. It can let clients wait while a smaller set of backend connections handles eligible work. That changes when callers get database access; it does not speed up the database CPU, storage or queries.

Imagine a single deliberately limited backend pool of 40 connections for the example app. Eight or twelve instances may present many client connections to the proxy, but only up to that configured backend pool would be admitted to PostgreSQL for that particular pool. The remaining clients wait or time out according to the proxy and application configuration. Forty is a teaching number, not a discovered optimum. Benchmark the real query mix and acceptable wait before selecting a production size.

PgBouncer’s default_pool_size applies to a user/database pair, not automatically to every backend connection across an installation. If the app connects as several database users or to several databases, it can create several backend pools. Additional proxy instances can multiply them again. Recalculate the total backend possibility across those pools and retain room for administrative access. A client connection cap is a separate setting; accepting many clients does not mean giving each a dedicated PostgreSQL backend.

Choose a mode with the application’s session behavior in mind

PgBouncer’s feature table distinguishes session, transaction and statement pooling. In session mode, a backend stays with a client connection for the session. That preserves more session-oriented behavior but may share backends less aggressively when clients remain connected and idle. In transaction mode, a backend is assigned for a transaction and returned afterward. It improves sharing potential but exposes code that assumes a later transaction sees the same server session.

Statement mode returns the backend after a statement and disallows multi-statement transactions. It is a specialized choice, not a general “more pooling” switch for a normal web app. For most teams, the first decision is whether they need session behavior or can verify transaction-mode compatibility.

Audit explicit session features: PgBouncer’s feature map lists LISTEN and session advisory locks as unsupported in transaction mode, while NOTIFY is supported. A long-held transaction also keeps its backend for the duration of that transaction, so transaction pooling will not release it during a long query or an idle-in-transaction wait. If a worker depends on a dedicated session or holds locks across transactions, keep that workload on a compatible connection path rather than forcing every client through the same mode.

Prepared statements need precise language. PgBouncer can track protocol-level named prepared statements in transaction mode when max_prepared_statements is configured above zero, subject to its documented support and the client driver’s behavior. SQL commands such as PREPARE are a different mechanism and should not be assumed to work the same way across changing backend sessions. Check the current PgBouncer FAQ, driver and framework version, and run the application’s actual prepared-query path in staging. Avoid a global “disable prepared statements” setting copied from an old blog post without showing which mechanism your app uses.

Roll out with wait and failure measurements

Start with a representative staging workload and the expected peak instance count. Record PostgreSQL active and idle server connections, proxy client and backend connections, time waiting for a slot, acquisition timeouts, transaction duration and query latency. Check migrations and admin commands separately from web requests; they may need a direct or session-compatible connection path and a reserved slot. Include background workers because they can hold connections longer than a short HTTP request.

Then introduce limits in a controlled order. Set each application pool so scale-out does not accidentally exhaust the budget. If adding a proxy, calculate all backend pools and choose the mode by a feature audit. Run read, write, transaction, prepared-query and session-feature cases before routing production traffic through it. During a gradual rollout, watch whether backend connections fall and whether the app’s wait time, timeout rate or error rate becomes unacceptable. A low connection count with a growing queue is not success.

Write a stop rule before the change: revert the routing or reduce traffic if acquisition timeouts, failed transactions or user-facing errors exceed the service’s acceptable limits. Preserve an administrative access path so a saturated app pool does not prevent investigation. Tune from observed workload, not from the example’s 40-backend number. The hosting hub covers broader infrastructure choices; this calculation answers the narrower question of how many connections the deployment can request and which pooling behavior its code can safely use.