Database connection pooling basics

Topic: Databases core

Use a connection pool (PgBouncer, ProxySQL, or application-level) so many application threads share a smaller number of database connections. Reduces connection churn and stays under max_connections. Use this when you have many app instances or high concurrency and hit connection limits.

Intent: How-to

Quick answer

  • Pool sits between app and DB: app opens many connections to the pool; pool maintains a smaller set of connections to the DB. PgBouncer for PostgreSQL; ProxySQL or built-in for MySQL.
  • Configure pool size: total pool connections should be under DB max_connections (leave room for admin and replicas). Per-app pool size = pool_total / number of app instances. Use transaction or session mode (PgBouncer: transaction preferred for most apps).
  • Deploy pool on same host as app or as a sidecar; point app to pool host:port instead of DB. Monitor pool usage and queue; size DB max_connections and pool so connections are not exhausted.

Prerequisites

Steps

  1. Choose pooler and mode

    PgBouncer: transaction mode (connection returned to pool after transaction) or session mode. Transaction mode allows more concurrency with fewer DB connections. Install PgBouncer (apt/dnf) or use app-level pool (e.g. HikariCP).

  2. Configure pool size

    default_pool_size and max_client_conn in PgBouncer. Ensure default_pool_size * databases (or total) is under PostgreSQL max_connections. Reserve connections for admin and replication.

  3. Point app to pool

    Change app connection string from DB host to PgBouncer host:port. Use same database and user; pooler handles auth (same password or auth query). Test under load; check for prepared statement issues (use session mode if needed).

  4. Monitor and tune

    SHOW POOLS in PgBouncer; monitor active and queued clients. If queue grows, increase pool size or DB max_connections (and shared_buffers if needed). Alert on connection errors.

Summary

Deploy a connection pooler (e.g. PgBouncer) between the app and the database; size the pool under max_connections. Use this when you hit connection limits or want to reduce connection churn.

Prerequisites

Steps

Step 1: Choose pooler and mode

Install PgBouncer (or use app-level pooling); choose transaction or session mode.

Step 2: Configure pool size

Set pool size so total DB connections stay under max_connections; leave headroom for admin.

Step 3: Point app to pool

Change the app to connect to the pooler; verify auth and behavior (prepared statements in session mode if needed).

Step 4: Monitor and tune

Monitor pool usage and queue; increase pool or max_connections if needed; alert on errors.

Verification

  • App connects via pool; connection count at DB is stable and under limit; no connection errors under load.

Troubleshooting

Prepared statement errors — Use session mode in PgBouncer or disable prepared statements in the app. Queue growing — Increase pool size or DB max_connections; check for long-running transactions.

Next steps

Continue to