Connection pooling

Why pool

Pooling matters only in server mode — the embedded path has no sockets and no pool. Two pressures make a pooler worthwhile in front of engine-server:

• SHOULD pool when clients are serverless or bursty: each invocation opens a connection, so a spike produces thousands of short-lived connections that would otherwise each hold engine resources (a session, cached statements, a snapshot).
• SHOULD pool to enable scale-to-zero: with idle backends drained, the engine can idle-stop and the storage bytes bill at rest while compute goes to nothing.
• MAY skip pooling for a small, fixed set of long-lived clients — there is no burst to absorb.

Use transaction mode

Run the pooler in transaction mode. It multiplexes N client connections onto a small pool of backend connections, returning a backend to the pool at each transaction boundary (COMMIT/ROLLBACK) rather than pinning it for the client's whole lifetime.

PgBouncer config sketch

A minimal transaction-mode PgBouncer in front of engine-server. The client edge absorbs the burst; the backend pool stays small and can drain to zero so the engine idle-stops.

# pgbouncer.ini (sketch)
[databases]
srv = host=127.0.0.1 port=5433 dbname=srv

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

pool_mode           = transaction   # the required mode
default_pool_size   = 20            # backend conns per (db,user)
min_pool_size       = 0            # allow scale-to-zero: no idle backends
max_client_conn     = 5000         # absorb the burst on the client edge
server_idle_timeout = 30           # drop idle backends → engine can idle-stop

Clients then connect to the pooler (port 6432) instead of the server directly:

psql "host=127.0.0.1 port=6432 user=postgres dbname=srv sslmode=disable"

pgcat is the alternative when you also want sharding or load-balancing across many backend nodes; it offers the same transaction pooling plus query routing. Either way the pooler is a separate process — keep it out of engine-server.

Transaction-mode caveats

In transaction mode a backend is not pinned to a client across transactions, so session-scoped state does not survive between transactions. This is standard PgBouncer guidance and applies unchanged here.

• MUST NOT rely on session-level SET, advisory session locks, LISTEN/NOTIFY, or server-side prepared statements persisting across transactions when pooled in transaction mode.
• SHOULD keep each unit of work inside one transaction so the backend can be returned to the pool cleanly at its boundary.
• SHOULD set min_pool_size = 0 and a short server_idle_timeout so idle backends drain and the engine can scale to zero.

Pooling does not add write parallelism

A pooler multiplies connections, not write lanes. The engine enforces one writer per database — writers serialize through a single write lane while readers run concurrently against a snapshot and never block. A pool of 5,000 client connections all writing the same database still serializes at the engine; the pooler only keeps those clients from each tying up a backend.

• SHOULD spread write load across many databases or branches to recover write lanes — per-DB fan-out is the parallelism lever, not the pool size.
• MAY route reads and writes to the same backend pool; reads attach to a snapshot LSN and do not contend with the single writer.

Operational notes

• SHOULD terminate TLS at the pooler when exposing the service beyond a trusted network; the server itself serves cleartext (sslmode=disable).
• SHOULD coordinate drain with the pooler on shutdown (pause new backend connections) so clients see graceful reconnects rather than reset sockets.
• MUST NOT expect the pooler to buffer writes for durability — a commit is durable only after the engine's WAL append returns; the pooler is a connection multiplexer, not a write cache.

Related