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, 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. That is a 25-50x reduction in database resource consumption.
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=yesPgCat: 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, PgCat is younger than PgBouncer and has a smaller community. For straightforward connection pooling, PgBouncer remains the safer choice. For read/write splitting and multi-threaded performance, PgCat is compelling.
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.
Related Reading:
Resources:
In conclusion, 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, monitor wait times, and size your pool based on actual query duration rather than guesswork.