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. Ready-to-run versions of this (plus a pgcat equivalent) live in the repo under deploy/pooler/ — pgbouncer.ini, pgcat.toml, and a README.md with the run and soak-test commands.

# 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.

Bun.sql against the pooled endpoint

Bun's built-in Bun.sql is a Postgres client, so it reaches the pooler with an ordinary connection string — point it at the pooler port (6432), not the server. In transaction mode a backend is returned to the pool at each transaction boundary, so wrap related statements in sql.begin() to keep them on one borrowed backend.

import { SQL } from "bun";

// Connect to the POOLER (6432), not engine-server (5433). Cleartext wire.
const sql = new SQL("postgres://[email protected]:6432/srv?sslmode=disable");

await sql`CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)`;

// One transaction = one borrowed backend, returned to the pool at commit.
await sql.begin(async (tx) => {
  await tx`INSERT INTO notes VALUES (1, 'hello from Bun.sql')`;
});

const rows = await sql`SELECT body FROM notes WHERE id = ${1}`;
await sql.end();

This is the same connection string a non-pooled client would use against engine-server directly — only the port changes. The full runnable notes plus a pgbench soak command are in deploy/pooler/README.md.

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