Database Connection Pooling with PgBouncer and PgCat: Production Configuration Guide
A PostgreSQL connection consumes 5-10MB of memory. With 200 application instances each opening 20 connections, you have 4,000 connections consuming 20-40GB of RAM — just for connection overhead. Database connection pooling with PgBouncer or PgCat dramatically reduces this by multiplexing thousands of application connections over a small pool of actual database connections. Therefore, this guide covers configuration, pooling modes, sizing math, and troubleshooting for production deployments.
Why Connection Pooling Matters
PostgreSQL forks a new backend process for every connection. Each process has its own memory allocation for sort buffers, hash tables, and shared buffer references. At 200 connections, this is manageable. At 2,000 connections, PostgreSQL spends more time managing connections than executing queries. Moreover, connection establishment takes 5-20ms (or 50-100ms with SSL), which adds up when your application opens and closes connections frequently.
A connection pooler sits between your application and PostgreSQL. Your application opens connections to the pooler (cheap — pooler connections are lightweight). The pooler maintains a small pool of real PostgreSQL connections (expensive) and assigns them to application requests as needed. With transaction pooling, a single PostgreSQL connection serves hundreds of application connections because each only needs the real connection for the duration of a transaction.
The numbers are dramatic. Without pooling, 500 application servers with 10 connections each need 5,000 PostgreSQL connections. With transaction pooling and a typical 50ms transaction duration, those same 500 servers can share 100-200 PostgreSQL connections — a 25-50x reduction in database resource consumption that also flattens the connection spikes that overwhelm a primary during a thundering-herd restart.
PgBouncer: The Battle-Tested Standard
PgBouncer has been the standard PostgreSQL connection pooler for over 15 years. It is single-threaded, uses minimal memory (around 2KB per connection), and handles tens of thousands of connections on a single core.
; pgbouncer.ini — production configuration
[databases]
; Connect to PostgreSQL on separate host
myapp = host=pg-primary.internal port=5432 dbname=myapp_production
[pgbouncer]
; Listen on all interfaces
listen_addr = 0.0.0.0
listen_port = 6432
; Pool mode: transaction is usually the right choice
pool_mode = transaction
; Connection limits
max_client_conn = 5000 ; Max app connections to PgBouncer
default_pool_size = 50 ; Real PG connections per database/user pair
min_pool_size = 10 ; Keep this many connections warm
reserve_pool_size = 10 ; Extra connections for burst traffic
reserve_pool_timeout = 3 ; Seconds before using reserve pool
; Timeouts
server_idle_timeout = 300 ; Close idle PG connections after 5 min
client_idle_timeout = 0 ; Never close idle client connections
query_timeout = 30 ; Kill queries running longer than 30s
client_login_timeout = 15 ; Timeout for client authentication
; Security
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
; Logging
log_connections = 0 ; Don't log every connect/disconnect
log_disconnections = 0
log_pooler_errors = 1
stats_period = 60 ; Stats every 60 seconds
; Admin access
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats
Session vs Transaction vs Statement Pooling
The pooling mode determines when PgBouncer assigns and releases database connections. This is the most important configuration choice.
Session pooling: A database connection is assigned when the client connects and released when the client disconnects. This is the safest mode — everything works, including prepared statements, LISTEN/NOTIFY, and session variables. However, it provides minimal multiplexing benefit because each client holds a connection for its entire session. Use session pooling only when you need features incompatible with transaction pooling.
Transaction pooling: A database connection is assigned at the start of a transaction and released at the end. Between transactions, the client has no database connection. This provides the best multiplexing — 100 database connections can serve 5,000 clients. However, session-level features break: prepared statements (with some workarounds), SET commands, LISTEN/NOTIFY, and advisory locks. Consequently, transaction pooling requires application changes to avoid these features.
Statement pooling: A database connection is assigned for each individual statement and released immediately after. This provides maximum multiplexing but breaks multi-statement transactions completely. Only use this for simple read-only workloads with no transactions.
POOLING MODE COMPARISON:
Session Transaction Statement
Multiplexing benefit: Low High Highest
Prepared statements: Yes No* No
SET variables: Yes No No
LISTEN/NOTIFY: Yes No No
Multi-stmt transactions:Yes Yes No
Advisory locks: Yes No No
Temp tables: Yes No No
* PgBouncer 1.21+ supports protocol-level prepared statements
in transaction mode with server_prepared_statements=yesSizing the Pool: The Math That Actually Matters
Most pool-exhaustion incidents come from guessing the pool size instead of calculating it. The governing relationship is Little’s Law: the number of in-flight connections you need equals your arrival rate multiplied by the average time each request holds a connection. In other words, pool size should be derived from throughput and transaction duration, not from the number of application pods.
For example, if your service handles 4,000 transactions per second and the average transaction holds a connection for 5ms, you need roughly 4000 × 0.005 = 20 concurrent server connections. Add headroom for variance and slow tails, and a default_pool_size of 30-40 is plenty — even behind hundreds of application instances. By contrast, a pool sized as “20 connections per pod times 200 pods” would request 4,000 backends and defeat the entire purpose of pooling.
pool_size ≈ peak_TPS × avg_transaction_seconds × safety_factor
example: 3500 TPS × 0.008 s × 1.5 ≈ 42 server connections
Rules of thumb:
- Keep total server connections well under PostgreSQL max_connections.
- A common starting point: max_connections ≈ (cpu_cores × 4).
- More backends than ~4× CPU cores rarely improves throughput; it adds
context-switching and lock contention instead.Critically, oversizing the pool is its own failure mode. Beyond a few times the core count, additional PostgreSQL backends do not add throughput — they add lock contention, context switching, and buffer-pool pressure. Benchmarks published by the HikariCP project show this counterintuitive curve, where peak throughput sits at a surprisingly small connection count, so a right-sized pool that occasionally queues for a few milliseconds usually beats a giant one that saturates the database CPU.
PgCat: The Multi-Threaded Alternative
PgCat is a newer connection pooler written in Rust that addresses PgBouncer’s single-threaded limitation. On a 16-core machine, PgBouncer uses one core while PgCat uses all 16. Additionally, PgCat supports query load balancing across read replicas, automatic failover, and sharding — features that require external tools with PgBouncer.
# pgcat.toml — production configuration
[general]
host = "0.0.0.0"
port = 6432
admin_username = "pgcat_admin"
admin_password = "secure_password"
worker_threads = 8 # Use 8 of 16 cores (leave room for OS)
[pools.myapp]
pool_mode = "transaction"
default_role = "primary"
query_parser_enabled = true # Parse queries to route reads/writes
primary_reads_enabled = false # Don't send reads to primary
sharding_function = "pg_bigint_hash"
[pools.myapp.shards.0]
servers = [
["pg-primary.internal", 5432, "primary"],
["pg-replica-1.internal", 5432, "replica"],
["pg-replica-2.internal", 5432, "replica"],
]
database = "myapp_production"
[pools.myapp.users.0]
username = "app_user"
password = "app_password"
pool_size = 30 # Per shard, per user
min_pool_size = 5
statement_timeout = 30000 # 30 secondsPgCat’s query parser can automatically route SELECT queries to replicas and write queries to the primary. This eliminates the need for application-level read/write splitting. However, automatic routing has a sharp edge: a read issued immediately after a write may hit a replica that has not yet caught up, surfacing replication lag as stale data. For read-after-write consistency, pin those reads to the primary or keep the workflow on a connection that stays on the primary for its duration.
Monitoring and Troubleshooting
Connection pooling problems are subtle. Your application works fine under normal load but fails under peak traffic with “connection pool exhausted” errors, or worse, queries time out because they are waiting for a pooled connection.
Monitor these metrics: wait time (how long clients wait for a connection — should be under 10ms), active connections vs pool size (if consistently near max, increase pool size), server connections (actual PostgreSQL connections — watch PostgreSQL’s max_connections limit), and query duration (long queries hold connections, reducing pool availability).
-- PgBouncer admin console: check pool health
-- Connect to PgBouncer admin: psql -p 6432 -U pgbouncer_admin pgbouncer
SHOW POOLS;
-- Look at: cl_active, cl_waiting, sv_active, sv_idle
-- cl_waiting > 0 means clients are waiting for connections
SHOW STATS;
-- avg_query_time: should be low (under 10ms for OLTP)
-- avg_xact_time: transaction duration
-- total_wait_time: cumulative client wait time
SHOW SERVERS;
-- Shows actual PostgreSQL connections and their stateThe most common issue is pool exhaustion caused by long-running queries. A single analytics query holding a connection for 30 seconds blocks 30 seconds worth of other requests from using that connection. The fix: set query_timeout in PgBouncer and route analytical queries to a separate pool with its own connections or directly to a read replica. As a result, slow reporting workloads stop starving the latency-sensitive OLTP path.
When NOT to Use a Pooler — and the Trade-offs
Pooling is not free, and there are cases where it adds more risk than value. If your application already uses a well-tuned client-side pool such as HikariCP or pgx, and you run only a handful of long-lived instances, a transaction-mode pooler may add latency and an extra failure hop without meaningfully reducing the backend count. In that scenario, a session-mode pooler — or no external pooler at all — can be the simpler choice.
Transaction pooling also quietly breaks features teams rely on. Server-side prepared statements, SET search_path, temporary tables, session-scoped advisory locks, and LISTEN/NOTIFY all assume a stable session that transaction pooling does not provide. Before flipping to transaction mode, audit your ORM and migration tooling, since many frameworks issue session-level settings on connect and silently misbehave when those settings vanish between transactions.
Finally, remember that a pooler is both a single point of failure and a potential observability blind spot. Run at least two pooler instances behind a virtual IP or load balancer, set conservative query_timeout and statement_timeout values, and alert on cl_waiting rather than user-facing timeouts. A pooler that hides backpressure is worse than no pooler, because the database stops pushing back and your queue grows invisibly until something topples.
Related Reading:
Resources:
In conclusion, database connection pooling is essential for any PostgreSQL deployment with more than a few dozen connections. PgBouncer is the proven choice — lightweight, stable, and battle-tested. PgCat offers multi-threaded performance and built-in read/write splitting for more complex topologies. Start with transaction pooling mode, size your pool from throughput and transaction duration rather than guesswork, monitor wait times, and keep the pooler itself redundant and observable.