Stage 3 · Build

PostgreSQL Operations

Connection Pooling with PgBouncer

Session, transaction, statement modes — sizing pools and preventing connection exhaustion.

7 min readDatabases for SREBuild

Why Connection Pooling?

PostgreSQL forks a new process for every connection. Each process consumes ~10MB of memory. With 500 connections, that is 5GB just for connection overhead — before any query runs. Connection pooling reuses a smaller set of database connections across many clients.

Connection overhead


# Check current connection count
SELECT count(*) FROM pg_stat_activity;

# Check memory per connection
SHOW shared_buffers;  # shared across all connections
SHOW work_mem;        # multiplied by active operations per connection

# Process list showing connection memory
ps aux | grep postgres | awk '{sum += $6} END {print sum/1024 " MB total"}'

Each PostgreSQL backend process is a separate OS process (not a thread). This is simpler but more expensive than thread-based databases like MySQL. Connection pooling is essential for PostgreSQL at scale.

PgBouncer Architecture

PgBouncer is a lightweight connection proxy that sits between clients and PostgreSQL. Clients connect to PgBouncer, which multiplexes them onto a smaller pool of actual database connections. The overhead of PgBouncer itself is minimal — a single process handling thousands of connections.

PgBouncer is the standard

Every production PostgreSQL deployment should have a connection pooler. PgBouncer is the most widely used due to its simplicity, performance, and battle-tested reliability. Use it even if you think you do not need it.

Pooling Modes

Mode	Behavior	Use Case
Session	Client gets a connection until disconnect	SET commands, prepared statements
Transaction	Connection returned after transaction	Most applications, default choice
Statement	Connection returned after each statement	Stateless apps only, risky

PgBouncer pooling modes


; Session mode: connection held for entire client session
; Pro: Full PostgreSQL session features work
; Con: No connection reuse until client disconnects
pool_mode = session

; Transaction mode: connection returned after COMMIT/ROLLBACK
; Pro: High connection reuse, most efficient
; Con: SET commands, prepared statements lost between transactions
pool_mode = transaction

; Statement mode: connection returned after each statement
; Pro: Maximum reuse
; Con: Multi-statement transactions not possible, very restrictive
pool_mode = statement

Transaction mode is the recommended default. It provides excellent connection reuse while supporting standard application patterns. Use session mode only for legacy apps that require persistent connections.

Configuration and Sizing

PgBouncer configuration


; pgbouncer.ini

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; Pool sizing
default_pool_size = 25       ; connections per user/database pair
min_pool_size = 10           ; keep at least this many idle connections
reserve_pool_size = 5        ; extra connections for bursts
reserve_pool_timeout = 3     ; seconds before using reserve pool

; Limits
max_client_conn = 1000       ; total client connections allowed
max_db_connections = 50       ; max connections to any single database

; Timeouts
server_idle_timeout = 300    ; close idle server connections after 5 min
client_idle_timeout = 0       ; 0 = no timeout

default_pool_size = 25 with 10 databases means PgBouncer opens up to 250 actual PostgreSQL connections. Set max_client_conn higher than your application count to absorb traffic spikes.

Do not set default_pool_size too high

A pool of 25 connections per database is usually sufficient. PostgreSQL performance degrades beyond ~100 connections due to lock contention and context switching. Fewer connections with faster queries beats many connections with slow queries.

Monitoring PgBouncer

PgBouncer admin commands


# Connect to PgBouncer admin console
psql -h 127.0.0.1 -p 6432 -U admin pgbouncer

# Show pool status
SHOW POOLS;
-- database | user  | cl_active | cl_waiting | sv_active | sv_idle
-- mydb     | app   |         5 |          0 |         5 |       2

# Show server statistics
SHOW SERVERS;

# Show client statistics
SHOW CLIENTS;

# Show overall stats
SHOW STATS;
-- Total queries, average time, server assignments

# Show configuration
SHOW CONFIG;

cl_waiting > 0 means clients are waiting for a connection. This indicates the pool is saturated — increase default_pool_size or optimize query performance to release connections faster.

Common Pitfalls

Prepared statements break in transaction mode — use DEALLOCATE ALL before releasing connections
SET commands are lost between transactions — use connection attributes or session mode
LISTEN/NOTIFY requires session mode or a dedicated connection
Temporary tables are lost between transactions — use real tables or CTEs
Advisory locks may release early if connection is reused

Handling prepared statements in transaction mode


-- Application code must deallocate before transaction ends
BEGIN;
PREPARE my_plan AS SELECT * FROM orders WHERE user_id = $1;
EXECUTE my_plan(42);
DEALLOCATE my_plan;  -- Critical in transaction mode
COMMIT;

In transaction mode, the connection is returned to the pool after COMMIT. The prepared statement does not exist on the next connection the client gets. DEALLOCATE ALL prevents confusing server-side errors.

Use PgBouncer with transaction mode and deallocate

Add DEALLOCATE ALL (or DEALLOCATE ALL) to your transaction cleanup code. Most ORMs handle this automatically — check your ORM documentation for connection pool settings.

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.