PostgreSQL Connection Pooling: PgBouncer

Tip: Install PgBouncer

[databases]
myapp = host=localhost port=5432

[pgbouncer]
pool_mode = transaction
max_client_conn = 500
default_pool_size = 25

Gotcha: Transaction Mode Limitations

In transaction mode, you can't use prepared statements or session-level variables.

Tip: Laravel Configuration

'host' => '127.0.0.1',
'port' => 6432, // PgBouncer port

Gotcha: Session Mode

Use pool_mode = session if your app needs session-level features.

Tip: Admin Console

psql -p 6432 -U pgbouncer pgbouncer
SHOW POOLS;

Monitor connection pool status.

Gotcha: Connection Limits

PgBouncer doesn't increase PostgreSQL's max_connections. It queues excess connections.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

Connection pooling with PgBouncer is mandatory for any Laravel application serving more than a few concurrent users. PostgreSQL forks a process per connection, and beyond ~200 connections, the OS scheduler dominates performance. Transaction-level pooling in PgBouncer reuses connections efficiently, but it breaks session-level features like SET statements and LISTEN/NOTIFY. Always test your application with transaction pooling before assuming session-level features work.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)