Database Connection Pooling Explained

The hidden cost of a database connection

Every time your app opens a new database connection, real work happens: a TCP handshake, often a TLS handshake, authentication, and — on PostgreSQL specifically — the server forks a whole backend process for that connection. That setup can cost milliseconds, and on the server side each connection consumes memory and scheduling overhead. Do this per request, under load, and two bad things happen: latency climbs from constant connection churn, and you slam into the database's hard connection limit.

Connection pooling fixes both by maintaining a reusable set of open connections that requests borrow and return.

How a pool works

A connection pool is a managed cache of database connections:

1. 1At startup (or on demand), the pool opens a bounded number of connections and keeps them alive.
2. 2When your code needs the database, it borrows a connection from the pool instead of opening one.
3. 3It runs its queries.
4. 4It returns the connection to the pool — the connection stays open, ready for the next borrower.

The expensive setup happens once per pooled connection, not once per request. A handful of connections can serve thousands of requests because each request holds a connection only briefly.

Why databases force the issue

Databases cap concurrent connections. PostgreSQL's max_connections defaults modestly (often ~100), and each connection is a real backend process consuming memory. If you run 50 app instances that each open 20 connections, that's 1,000 connections — far past the limit — and you get the dreaded FATAL: too many connections, an outage. Pooling bounds total connections to something the database can actually handle.

This is *especially* acute for:

• Serverless / many-instance deployments where each instance wants its own connections.
• Autoscaled apps that multiply instances under load — and multiply connection demand with them.

Application pool vs external pooler

There are two layers where pooling can live, and they're complementary:

The key insight: an application-side pool only bounds connections per process. With many instances, you still overwhelm the database (10 instances × 20-connection pools = 200). An external pooler sits between your fleet and the database and multiplexes *all* instances onto a small shared set of real connections. For serverless and high-instance-count deployments, you usually want both — small app pools plus an external pooler.

PgBouncer pooling modes

For PostgreSQL, PgBouncer is the canonical external pooler, with three modes that trade safety for efficiency:

• Session pooling — a client keeps a server connection for its whole session. Safest, least multiplexing.
• Transaction pooling — a server connection is assigned only for the duration of a transaction, then returned. Far more multiplexing; the common choice for high concurrency. Caveat: session-level features (some prepared statements, SET that outlives a transaction, advisory locks) can break.
• Statement pooling — connection returned after each statement. Most aggressive, most restrictive.

Transaction pooling is the workhorse but requires your app not to rely on session state across transactions.

Sizing the pool: smaller than you think

The instinct is "more connections = more throughput." Usually wrong. Past a point, more connections cause *contention* — context switching, lock contention, cache thrashing — and throughput drops. A widely-cited starting formula (from HikariCP's guidance) for a CPU-bound workload:

connections ≈ (core_count * 2) + effective_spindle_count

For many web apps the sweet spot is surprisingly small — often a few to a couple dozen connections per database, total. Measure: watch p99 latency and database CPU as you adjust. If adding connections doesn't raise throughput, you're past optimal.

Key configuration knobs

# PgBouncer (transaction pooling) — illustrative
[databases]
app = host=db.internal port=5432 dbname=app

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000      # how many clients can connect to PgBouncer
default_pool_size = 20      # real server connections per database
server_idle_timeout = 600   # close idle server conns after 10m

The pattern: thousands of cheap client connections to the pooler, multiplexed onto ~20 real database connections.

Application-side pools have analogous knobs: min/max pool size, connection max-lifetime (recycle connections periodically to avoid stale ones), and acquisition timeout (fail fast rather than hang when the pool is exhausted).

Common pitfalls

• Leaked connections — borrowing without returning (forgetting to close) drains the pool until everything hangs. Use try/finally or context managers.
• Pool too large — overwhelms the database and hurts throughput.
• Pool too small / no queue timeout — requests block indefinitely waiting for a connection; set an acquisition timeout.
• Transaction-pooling assumptions — relying on session state under transaction pooling silently breaks. Know your mode.
• No connection recycling — long-lived connections can go stale; set a max lifetime.

How this maps to platform limits

Connection limits are a real, planned dimension — not an afterthought. PandaStack's managed databases publish connection ceilings per plan: 50 connections on the free tier, scaling up (300 on Pro, 1000 on Premium). Those numbers exist precisely because connections are a finite database resource. The practical advice: with autoscaled or many-instance apps, don't let each instance open a large pool against a small limit — keep app-side pools modest and lean on an external pooler so your *total* connection count stays under the plan ceiling. When an app connects to a PandaStack database, it's wired in via the injected connection string; pool *on top of* that connection responsibly.

References

• [PgBouncer documentation](https://www.pgbouncer.org/usage.html)
• [PostgreSQL: max_connections and connection management](https://www.postgresql.org/docs/current/runtime-config-connection.html)
• [HikariCP: pool sizing guidance](https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing)
• [AWS RDS Proxy documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html)
• [PostgreSQL connection pooling overview (PgBouncer modes)](https://www.pgbouncer.org/features.html)

---

Running autoscaled apps against a database? PandaStack's managed databases publish clear connection limits per plan so you can size pools sanely. Start free at [dashboard.pandastack.io](https://dashboard.pandastack.io).