PgBouncer Connection Pooling Explained for App Developers
PgBouncer is a lightweight connection pooler that sits between your application and a PostgreSQL database, reusing a small pool of actual database connections instead of opening a new one for every request.
If you have ever seen your Node.js or Python app throw too many connections errors under load, PgBouncer is the fix — and understanding it is essential before you scale anything past a handful of users.
Why Postgres Struggles With Many Connections
Postgres allocates a dedicated OS process for every client connection. Each process consumes roughly 5–10 MB of RAM at idle, plus a file descriptor and shared-memory overhead.
A database server with max_connections = 100 can hold exactly 100 simultaneous clients. When a Node.js app spins up 10 workers, each maintaining a pool of 20 connections, you hit that ceiling fast — before a single real user arrives.
This is not a Postgres bug; it is an architectural trade-off that pooling was invented to solve.
What PgBouncer Actually Does
PgBouncer presents itself to your app as a normal Postgres server. Your app connects to PgBouncer on port 6432 (by default), thinks it has a direct connection, and sends queries normally.
Behind the scenes, PgBouncer maintains a small pool of real Postgres connections — say, 10 — and maps the much larger set of application connections onto those 10 as queries arrive. When a query finishes, the server-side connection returns to the pool instead of being closed.
The result: 500 concurrent app connections, 10 real Postgres connections, and a database that stays well under its max_connections limit.
The Three Pooling Modes — Choose Carefully
This is the part most tutorials rush past. PgBouncer supports three modes, and picking the wrong one breaks things silently.
Session Mode
A real Postgres connection is assigned to a client for the entire session — from connect to disconnect. This is the safest mode and behaves identically to a direct connection.
Use it when your app relies on session-level state: SET variables, temporary tables, advisory locks, or LISTEN/NOTIFY. The downside is that it saves fewer connections than the other modes.
Transaction Mode
A real connection is held only for the duration of a transaction block. The moment your COMMIT or ROLLBACK fires, the connection returns to the pool.
This is the most efficient mode and the one most production deployments use. The catch: anything that persists across transactions — SET LOCAL, prepared statements (unless you enable server_reset_query), advisory locks — will break or behave unpredictably. Audit your ORM before enabling this.
Statement Mode
The connection is released after every single statement, even within a transaction. Multi-statement transactions are impossible in this mode.
Statement mode is rarely useful for modern web apps. Avoid it unless you have a specific read-only analytics workload.
Prepared Statements and PgBouncer — the Common Trap
Prepared statements are a frequent source of errors when switching to transaction mode. When your app prepares a statement, Postgres stores it in the session context of that connection. In transaction mode, the connection may be handed to a different client on the next request — and that new client has no idea the statement was prepared.
Fixes:
– Disable client-side prepared statements in your driver. In node-postgres (pg): set statement_timeout and avoid pg.Pool prepared statement caching. In Prisma: set connection_string to include pgbouncer=true&prepared_statements=false.
– In SQLAlchemy (Python): use pool_pre_ping=True and avoid use_native_enum or server-side cursors in transaction mode.
– Enable server_reset_query = DEALLOCATE ALL in pgbouncer.ini so prepared statements are cleared when a connection returns to the pool.
A Minimal pgbouncer.ini
Below is a production-ready starting point. Replace the values in angle brackets.
[databases]
myapp = host=<postgres-host> port=5432 dbname=<dbname>
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
reserve_pool_size = 5
server_reset_query = DEALLOCATE ALL
log_connections = 0
log_disconnections = 0
max_client_conn is how many app-side connections PgBouncer will accept. default_pool_size is how many real Postgres connections it opens per database/user pair. Keep default_pool_size well below your Postgres max_connections.
The userlist.txt file stores hashed passwords in the format:
"myuser" "md5hash_or_scram_verifier"
Generate the scram verifier with psql -c "SELECT rolpassword FROM pg_authid WHERE rolname='myuser'".
Monitoring Pool Health
PgBouncer exposes a virtual pgbouncer database you can connect to and query like a normal Postgres server.
-- Connect: psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer
SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
Watch these columns in SHOW POOLS:
– cl_waiting — clients waiting for a free server connection. If this is consistently > 0, increase default_pool_size or reduce app concurrency.
– sv_idle — idle server connections available. If this is always 0 alongside cl_waiting > 0, you are under-pooled.
– sv_used — server connections in use. This should stay below default_pool_size.
Expose SHOW STATS to Prometheus via pgbouncer_exporter for production alerting.
Where to Run PgBouncer
You have three options:
- Same host as the app — lowest latency, simplest config. Works well on a single-server setup.
- Sidecar process on every app node — each app worker talks to localhost:6432. Scales horizontally; each node pools independently.
- Dedicated proxy node — all app nodes point to one PgBouncer instance. Easier to monitor but adds a network hop and a single point of failure. Use two instances with a load balancer for redundancy.
For apps deployed on CM Cloud App Hosting, PgBouncer runs as a separate process managed by systemd. Because CM Cloud manages the full runtime layer (nginx, systemd, process supervision), you can run PgBouncer as a background service alongside your app process — no separate VM needed at the Starter tier.
CM Cloud App Hosting vs Competitors for Postgres-Backed Apps
App Hosting on CM Cloud is purpose-built for backend runtimes: Node.js, Python (FastAPI/Django/Flask), Laravel/PHP, and PocketBase. You push code; CM Cloud handles nginx, systemd, and process supervision. A CI/CD deploy hook auto-redeploys on every git push via a secret URL you generate in the portal.
For Postgres-backed apps, connect your external managed Postgres (or a self-managed instance on a Cloud VPS) and wire PgBouncer in as described above.
| Provider | Starter Price (USD) | XAF | EUR | Key Feature |
|---|---|---|---|---|
| CM Cloud App Hosting | $7.99/mo | XAF 5,000 | €7.40 | Node/Python/Laravel/PocketBase, CI/CD hook, managed runtime |
| Railway | ~$5/mo (usage-based, spikes) | ~XAF 3,100 | ~€4.60 | Git deploy, usage billing |
| DigitalOcean App Platform | $12/mo (basic) | ~XAF 7,500 | ~€11.10 | Managed containers, auto-scaling |
| Render | $7/mo (starter, sleeps) | ~XAF 4,400 | ~€6.50 | Git deploy, free tier sleeps |
Railway and DigitalOcean prices are indicative — verify at checkout. CM Cloud prices are fixed (no promotional rate that renews higher). See the full breakdown at cmcloudhosting.com/pricing.
When You Do Not Need PgBouncer
PgBouncer adds operational complexity. Skip it if:
– Your app has fewer than 20 concurrent connections and no scaling plans.
– You are on a managed Postgres service (e.g., Neon, Supabase) that already provides built-in connection pooling — adding a second pooler can cause subtle bugs.
– Your workload is batch/offline with no concurrency.
If you are building a realtime app and want auth, subscriptions, and file storage without a separate Supabase subscription, consider PocketBase running on CM Cloud App Hosting instead. It handles its own connection management internally and pairs with any Node.js or static frontend.
Quick Reference: PgBouncer Checklist
- Set
pool_mode = transactionfor most web apps - Disable prepared statements in your driver when using transaction mode
- Add
server_reset_query = DEALLOCATE ALL - Keep
default_pool_sizeto 10–25% of Postgresmax_connections - Monitor
cl_waiting— it is your primary health signal - Use TLS between PgBouncer and Postgres in production (
server_tls_sslmode = require) - Never expose PgBouncer’s admin interface on a public IP
Deploy Your App on CM Cloud From $7.99/mo
CM Cloud App Hosting gives you a managed runtime for Node.js, Python, Laravel, and PocketBase apps — with a CI/CD deploy hook included — starting at $7.99/mo (XAF 5,000 / €7.40).
Need a Postgres database alongside it? Spin up a Cloud VPS at $7.99/mo for a self-managed instance, or connect any external managed Postgres. See all plans at cmcloudhosting.com/pricing.