Pooling Best Practices

Size connection pools from database capacity, not microservice replica count. Use this checklist when deploying PgBouncer in front of PostgreSQL 18.4.

How to Use This List

Validate staging through the pooler, not only direct 5432.
Monitor cl_waiting before raising max_client_conn.
Pair pool changes with pg_stat_activity snapshots.

A - Sizing

Size server pool from CPU cores and p95 query latency. Not pods * pool_max.
Keep peak server connections below 70% of max_connections. Reserve admin/replication headroom.
Use transaction pooling for stateless web OLTP. Session pooling only when required.
Disable or limit server-side prepared statements in transaction mode. Prevent intermittent errors.
Set max_client_conn high enough for apps but bounded. Pooler is cheap; Postgres is not.

B - Configuration

Configure server_reset_query = DISCARD ALL in transaction mode. Clear session state between checkouts.
Use separate pool aliases per role (oltp, reporting, admin). Independent pool_size caps.
Terminate TLS at pooler or app consistently. Document trust store rotation.
Run PgBouncer 1.x with SCRAM toward PostgreSQL 18. Align auth mechanisms.
Health-check pooler in load balancer. Not only Postgres primary.

C - Timeouts and Safety

Set statement_timeout via ALTER ROLE defaults. Survives pooler resets.
Set idle_in_transaction_session_timeout on app roles. Free server backends.
Use lock_timeout on OLTP roles. Fail fast under migration contention.
Bypass pooler only for controlled admin DDL windows. Document break-glass URL.
Alert on sustained cl_waiting and connection errors. Page before user flood.

D - Operations

Include pooler in failover drills. DNS or config switch to new primary.
Version-control pgbouncer.ini and userlist/auth_query. Review in PRs.
Load-test deploy connection storms. Rolling pod restarts through pooler.
Separate pools for read replicas when routing analytics. Do not share primary pool_size.
Review pg_stat_activity.application_name for mystery connections. Map to pool aliases.

FAQs

Pool size starting point?

Often 2-4x CPU cores total server pool; tune with metrics.

JDBC maximumPoolSize?

Small per pod; rely on PgBouncer for multiplexing.

session vs transaction?

transaction default; session for temp tables/listen.

prepared statements?

Usually off in transaction mode for ORMs.

multiple clusters?

Separate PgBouncer instance or logical separation per cluster.

observability?

SHOW POOLS/STATS plus Postgres pg_stat_activity.

serverless?

External pool required; never raw max_connections burst.

cloud managed pool?

Same principles; verify prepare and timeout behavior.

admin console exposure?

Restrict PgBouncer admin socket; no public internet.

Next section?

OLTP vs OLAP tuning for workload isolation beyond pooling.

Pooling Basics - Fundamentals
PgBouncer Modes - pool_mode
Prepared Statements & Pooling - ORM config
Connection Math - Formulas

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Pooling Best Practices

Size connection pools from database capacity, not microservice replica count. Use this checklist when deploying PgBouncer in front of PostgreSQL 18.4.

How to Use This List

Validate staging through the pooler, not only direct 5432.
Monitor cl_waiting before raising max_client_conn.
Pair pool changes with pg_stat_activity snapshots.

A - Sizing

Size server pool from CPU cores and p95 query latency. Not pods * pool_max.
Keep peak server connections below 70% of max_connections. Reserve admin/replication headroom.
Use transaction pooling for stateless web OLTP. Session pooling only when required.
Disable or limit server-side prepared statements in transaction mode. Prevent intermittent errors.
Set max_client_conn high enough for apps but bounded. Pooler is cheap; Postgres is not.

B - Configuration

Configure server_reset_query = DISCARD ALL in transaction mode. Clear session state between checkouts.
Use separate pool aliases per role (oltp, reporting, admin). Independent pool_size caps.
Terminate TLS at pooler or app consistently. Document trust store rotation.
Run PgBouncer 1.x with SCRAM toward PostgreSQL 18. Align auth mechanisms.
Health-check pooler in load balancer. Not only Postgres primary.

C - Timeouts and Safety

Set statement_timeout via ALTER ROLE defaults. Survives pooler resets.
Set idle_in_transaction_session_timeout on app roles. Free server backends.
Use lock_timeout on OLTP roles. Fail fast under migration contention.
Bypass pooler only for controlled admin DDL windows. Document break-glass URL.
Alert on sustained cl_waiting and connection errors. Page before user flood.

D - Operations

Include pooler in failover drills. DNS or config switch to new primary.
Version-control pgbouncer.ini and userlist/auth_query. Review in PRs.
Load-test deploy connection storms. Rolling pod restarts through pooler.
Separate pools for read replicas when routing analytics. Do not share primary pool_size.
Review pg_stat_activity.application_name for mystery connections. Map to pool aliases.

FAQs

Pool size starting point?

Often 2-4x CPU cores total server pool; tune with metrics.

JDBC maximumPoolSize?

Small per pod; rely on PgBouncer for multiplexing.

session vs transaction?

transaction default; session for temp tables/listen.

prepared statements?

Usually off in transaction mode for ORMs.

multiple clusters?

Separate PgBouncer instance or logical separation per cluster.

observability?

SHOW POOLS/STATS plus Postgres pg_stat_activity.

serverless?

External pool required; never raw max_connections burst.

cloud managed pool?

Same principles; verify prepare and timeout behavior.

admin console exposure?

Restrict PgBouncer admin socket; no public internet.

Next section?

OLTP vs OLAP tuning for workload isolation beyond pooling.

Pooling Basics - Fundamentals
PgBouncer Modes - pool_mode
Prepared Statements & Pooling - ORM config
Connection Math - Formulas

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Pooling Best Practices

Search Documentation

Pooling Best Practices