Database connection pooling

HOW IT FITS TOGETHER

KEY TERMS

Connection setup cost

Postgres uses a process-per-connection model: each connection is a forked backend process. Add the TCP and TLS handshakes and SCRAM authentication and opening a connection costs far more than running a typical indexed query on it.

Max size and the queue

The pool's max is a hard cap on concurrent database work from one process. When it is reached, callers queue. A short queue is healthy; a growing one means slow queries, leaked connections or a pool that is too small for the work.

Acquire timeout

How long a caller waits in the queue before failing. Defaults vary wildly: node-postgres waits forever (0), HikariCP and SQLAlchemy wait 30 s. Set it below your request timeout so exhaustion surfaces as a fast, clear error.

Idle timeout and max lifetime

Idle timeout closes connections nobody is using; max lifetime retires connections by age so firewalls, proxies, failovers and credential rotation do not leave the pool holding dead sockets. Keep max lifetime a few seconds below any infrastructure kill timer.

PgBouncer pool modes

Session mode pins a server connection for the whole client session. Transaction mode lends it per transaction, which breaks SET/RESET, LISTEN, session advisory locks, WITH HOLD cursors, temp tables kept across transactions and SQL-level PREPARE. Statement mode also forbids multi-statement transactions.

Prepared statements behind a pooler

Since PgBouncer 1.21, max_prepared_statements tracks protocol-level named prepared statements in transaction mode and re-prepares them on whichever server connection you land on; 1.24 made 200 the default. SQL-level PREPARE/EXECUTE is still not tracked. RDS Proxy for Postgres pins the session on PREPARE, SET and similar state changes instead.

Sizing

Start from the database: a pool near (cores x 2) + effective spindles per database server, then divide across replicas so replicas x pool max stays under max_connections with headroom for admin, migrations and replication. If you need more client concurrency than that, add an external pooler rather than raising the cap.

IN YOUR STACK

import { Pool } from "pg";

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                        // per process: multiply by replicas
  idleTimeoutMillis: 10_000,
  connectionTimeoutMillis: 3_000, // also bounds the wait in the queue
  maxLifetimeSeconds: 1_800,
});
pool.on("error", (err) => console.error("idle client error", err));

// One statement: acquire, run and release are handled for you.
const { rows } = await pool.query("SELECT * FROM orders WHERE id = $1", [orderId]);

// A transaction needs one client for every statement.
const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [amount, from]);
  await client.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [amount, to]);
  await client.query("COMMIT");
} catch (err) {
  await client.query("ROLLBACK");
  throw err;
} finally {
  client.release();
}

import { PrismaClient } from "../prisma/generated/client";
import { PrismaPg } from "@prisma/adapter-pg";

// In Prisma 7 the pool belongs to the driver adapter, configured with pg Pool options.
const adapter = new PrismaPg({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 5_000,
  idleTimeoutMillis: 300_000,
});

export const prisma = new PrismaClient({ adapter });

import (
	"database/sql"
	_ "github.com/jackc/pgx/v5/stdlib"
)

db, err := sql.Open("pgx", os.Getenv("DATABASE_URL")) // validates config, does not connect
if err != nil {
	return err
}
db.SetMaxOpenConns(20)            // default is unlimited
db.SetMaxIdleConns(20)            // default 2 churns connections under load
db.SetConnMaxLifetime(30 * time.Minute)
db.SetConnMaxIdleTime(5 * time.Minute)

ctx, cancel := context.WithTimeout(r.Context(), 2*time.Second) // also bounds the acquire wait
defer cancel()
rows, err := db.QueryContext(ctx, "SELECT id, total FROM orders WHERE user_id = $1", userID)
if err != nil {
	return err
}
defer rows.Close() // an open Rows keeps its connection checked out

s := db.Stats()
log.Printf("in_use=%d idle=%d waits=%d waited=%s", s.InUse, s.Idle, s.WaitCount, s.WaitDuration)

from sqlalchemy import create_engine, text
from sqlalchemy.pool import NullPool

engine = create_engine(
    "postgresql+psycopg://app@db/app",
    pool_size=10,        # connections kept open
    max_overflow=5,      # burst connections, closed when returned
    pool_timeout=5,      # seconds to wait for a checkout (default 30)
    pool_recycle=1800,   # replace connections older than 30 minutes
    pool_pre_ping=True,  # test on checkout and replace dead connections
)

with engine.connect() as conn:  # checkout; returned to the pool on exit
    rows = conn.execute(text("SELECT id FROM orders WHERE user_id = :u"), {"u": user_id}).all()

# Behind PgBouncer or in short-lived workers, let the external pooler pool.
pooled_engine = create_engine("postgresql+psycopg://app@pgbouncer:6432/app", poolclass=NullPool)

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://db:5432/app");
config.setUsername("app");
config.setPassword(System.getenv("DB_PASSWORD"));
config.setMaximumPoolSize(10);
config.setConnectionTimeout(3_000);       // ms to wait for a connection (default 30 s)
config.setMaxLifetime(1_740_000);         // just under a 30 min proxy or DB kill
config.setKeepaliveTime(120_000);
config.setLeakDetectionThreshold(10_000); // log connections held longer than 10 s
HikariDataSource ds = new HikariDataSource(config);

try (Connection conn = ds.getConnection();
     PreparedStatement ps = conn.prepareStatement("SELECT total FROM orders WHERE id = ?")) {
  ps.setLong(1, orderId);
  try (ResultSet rs = ps.executeQuery()) {
    if (rs.next()) return rs.getBigDecimal("total");
  }
}

[databases]
app = host=10.0.0.5 port=5432 dbname=app

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

pool_mode = transaction
max_client_conn = 2000        ; client sockets are cheap
default_pool_size = 20        ; server connections per user/database pair
reserve_pool_size = 5
max_prepared_statements = 200 ; protocol-level prepared statements in transaction mode
query_wait_timeout = 30       ; disconnect clients queued longer than this
server_idle_timeout = 600

use sqlx::postgres::PgPoolOptions;
use std::time::Duration;

let pool = PgPoolOptions::new()
    .max_connections(10)
    .min_connections(2)
    .acquire_timeout(Duration::from_secs(3))
    .idle_timeout(Duration::from_secs(600))
    .max_lifetime(Duration::from_secs(1800))
    .connect(&std::env::var("DATABASE_URL")?)
    .await?;

let count: i64 = sqlx::query_scalar("SELECT count(*) FROM orders WHERE user_id = $1")
    .bind(user_id)
    .fetch_one(&pool)
    .await?;

let mut tx = pool.begin().await?; // holds one connection until commit or rollback
sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2")
    .bind(amount).bind(from)
    .execute(&mut *tx)
    .await?;
tx.commit().await?; // dropping tx without commit rolls back

WHERE IT BITES

• Sizing each service's pool in isolation: 12 replicas x pool max 20 is 240 server connections, before workers, cron jobs and migrations join in.
• Leaving the acquire timeout at infinity, so pool exhaustion shows up as slow requests and upstream 504s instead of a clear pool error.
• Holding a connection, often inside an open transaction, while calling an external HTTP API; the pool drains at the speed of the slowest dependency.
• Relying on session state (SET search_path, session advisory locks, LISTEN, temp tables) through PgBouncer transaction mode or RDS Proxy, where it either leaks to other clients or pins the connection.
• Opening a pool per serverless invocation or per request, which turns pooling back into connect-per-query; use a module-level client plus an external pooler.
• Running schema migrations through a transaction-mode pooler; they need a direct, session-level connection.

WHEN IT BREAKS IN PRODUCTION

• Requests hang waiting for a database connection
• An endpoint gets slower as results grow
• A migration locks a table and requests pile up

SOURCES

• PostgreSQL 18: How Connections Are Established (opens in new tab)Checked 2026-10-07
• PgBouncer configuration (opens in new tab)Checked 2026-10-07
• PgBouncer features: SQL feature map for pooling modes (opens in new tab)Checked 2026-10-07
• HikariCP wiki: About Pool Sizing (opens in new tab)Checked 2026-10-07
• Amazon RDS Proxy: Avoiding pinning (opens in new tab)Checked 2026-10-07
• Prisma ORM 7: Connection pool (opens in new tab)Checked 2026-10-07