Connection pooling

The two endpoints

Every project (and every preview) gets two connection URLs to the same database:

• Direct, port 5432 - a normal Postgres session. Everything works; every connection counts fully against the plan's connection budget for as long as it is open.
• Pooled, port 6432 - CapyDB's managed pooling layer in transaction mode. Your client holds a lightweight connection to the pooler; a real server connection is borrowed for the duration of each transaction and returned the moment it commits or rolls back.

Both terminate TLS and authenticate with the same role and password - rotating credentials updates both at once. The pooled endpoint behaves like PgBouncer in transaction mode, because that is what runs behind it; every documented PgBouncer transaction-pooling caveat applies verbatim.

Why transaction pooling exists

A web app with 50 serverless instances, each holding a 10-connection client pool, wants 500 connections. The plan budget is 15–60 (Limits). Transaction pooling closes that gap: hundreds or thousands of client connections share a small set of real server connections, and since most app transactions take milliseconds, the sharing is invisible - until you use a feature that assumes the server connection is yours.

What breaks on the pooled URL

Anything that stores state on the server connection between transactions can land on a different connection next time:

Inside a single transaction, everything is normal Postgres - SET LOCAL, transaction-scoped advisory locks, and multi-statement transactions all work.

Startup parameters on the pooled URL

Some drivers send GUCs as wire-level startup parameters on every new connection - postgres.js does this for anything in its connection: {...} option, and libpq for anything in options=. A plain Postgres server (and the direct URL) applies them; a transaction pooler cannot, because there is no dedicated server connection to apply them to.

The pooled endpoint accepts and ignores these startup parameters: statement_timeout, idle_in_transaction_session_timeout, lock_timeout, idle_session_timeout, extra_float_digits, and options. Your connection succeeds, but the value is not in effect - if you rely on it, set it durably instead:

-- once, on the direct URL; applies to every future connection on both endpoints
ALTER ROLE your_role SET statement_timeout = '10s';

Any other GUC sent at startup (notably search_path) is rejected with unsupported startup parameter (08P01) rather than silently dropped, because ignoring it would change which objects your queries resolve to. Set those per-role with ALTER ROLE ... SET, per-database with ALTER DATABASE ... SET, or use the direct URL.

Pool sizing and what the numbers mean

Current pooling-layer behavior (operational defaults; they can be tuned, and the plan connection budget is always the final cap):

• The pooler opens up to a plan-sized number of server connections per database - 10 / 20 / 40 on Vibe / Ship / Business - with 5 more in reserve when clients are queueing. The pool size is deliberately set below the plan's connection budget so direct sessions (migrations, psql) always have headroom next to a busy pool.
• Client connections are cheap - the pooler accepts thousands of them; you will hit your plan's budget semantics long before the pooler's client ceiling.
• A query that waits more than 120 seconds for a free server connection fails instead of queueing forever. If you see that, the pool is saturated: look for long transactions hogging server connections in Observability.

The plan's connection budget (15 / 30 / 60 for Vibe / Ship / Business) covers direct and pooled access together - the pooler's server-side connections draw from the same budget as your direct sessions. The practical consequence: keep direct connections few and deliberate, and the pooler gets the rest of the budget to multiplex app traffic across.

The arithmetic behind those numbers

The constraint every pooler configuration has to satisfy is:

(number of pools) × (server connections per pool)  ≤  max_connections − reserved

A pool is one (role, database) pair. That is the detail that surprises people: ten roles against one database is ten pools, not one, and each gets its own server connections. Multiplying without noticing is the standard way to exhaust a connection budget.

Reserved capacity is not optional. Postgres keeps a few connections back for superuser access, and you want headroom for a migration or a psql session during an incident. Sizing a pool to consume the entire budget means the first thing you cannot do during an outage is connect to look at it.

Worked, on a Ship cell (budget 30, pool 20, reserve 5):

So a Ship cell comfortably runs one busy application through the pooled endpoint and leaves room for a migration and a psql session. Add a second role with its own heavy pooled workload and the arithmetic no longer closes - which is the point at which the answer is a bigger plan or a second cell, not a bigger pool.

Sizing a pool larger is rarely the fix for saturation, incidentally. If server connections are all busy, more of them means more concurrent queries competing for the same CPU and disks, and past a certain point total throughput goes down rather than up. Saturation is nearly always long transactions, and the fix is to shorten them.

Client pool size in serverless functions

@capydb/drizzle (and the snippets in the framework guides) default a pooled connection to { prepare: false, max: 1 }. That is right for serverless: every warm function instance holds its own client pool, so a max of 10 across 50 warm instances is 500 client connections chasing a plan-sized server pool, and the multiplexing you want is already happening server-side.

The consequence to know about is that max: 1 makes concurrency inside one invocation serialize. This runs its five queries one after another, not together:

const [a, b, c, d, e] = await Promise.all([
  db.select().from(users),
  db.select().from(posts),
  // …
])

Each still costs a full round trip, so five 20ms queries take 100ms rather than 20ms. Options, in the order worth trying:

1. Make it one query. Usually the right answer - a join, or a few aggregates in one statement.
2. Raise max deliberately for that client if the function really does need parallel queries: createDb(url, { max: 5 }). Do the arithmetic against your plan's budget first.
3. In a long-lived server (a container, a VM, a persistent Node process), use the direct URL defaults (max: 10) instead - the serverless reasoning does not apply.

Round trips are also the reason to keep your functions near your database: see Latency, practically.

Session state left behind on a server connection

There is one failure mode worth understanding in detail, because it is confusing when it happens and it looks like a platform fault.

In transaction pooling, the pooler does not reset a server connection between clients - this is standard PgBouncer behaviour, and it is what makes the endpoint fast. So if a client issues a session-level SET and does not undo it, the setting stays on that server connection for whoever borrows it next.

The classic version:

-- Client A, on the pooled endpoint. Note: no LOCAL, no transaction.
SET default_transaction_read_only = on;

Client A disconnects. Client B borrows that server connection and its perfectly ordinary INSERT fails:

ERROR:  cannot execute INSERT in a read-only transaction
SQLSTATE: 25006

Client B did nothing wrong and there is nothing in its code to find.

The blast radius stops at your own database. Every CapyDB project has its own Postgres instance and its own pooler, so a setting left on a server connection can only ever affect connections to your database - never another customer's. That is a property of the instance-per-project architecture rather than something to configure.

To recover, restart the application connections and, if the setting persists, run DISCARD ALL on the affected connections or rotate the pooler by making any credential change. To prevent it:

• Use SET LOCAL inside an explicit transaction. It is reverted at COMMIT, so it cannot outlive the transaction that set it.
• Or use ALTER ROLE your_role SET ..., which is durable, applies to every connection that role opens, and needs no cooperation from the caller.
• For read-only workloads, prefer a dedicated role granted only SELECT over asking connections to make themselves read-only.

The same reasoning covers SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY, SET ROLE, and SET search_path. Anything that changes the session and is not LOCAL is a hazard on a pooled connection. See Timeouts.

Which URL for which workload

Bulk writes, backfills and data migrations

One-off scripts that copy or rewrite a lot of rows (INSERT INTO … SELECT, backfilling a new column, moving data between tables) run into two ceilings that ordinary request traffic never touches:

• statement_timeout - a single enormous statement is cancelled mid-flight (57014).
• idle_in_transaction_session_timeout - 60–120s depending on plan. Wrapping the whole migration in one transaction while your script does per-row work in the client will get the session cut.

Both are per-plan limits, so "it worked on my 5k-row dev database" is not evidence it will work on production. The fix is the same in every language: use the direct URL and commit in batches rather than one giant statement or one giant transaction.

import postgres from 'postgres'

// Direct URL: migrations need session state and must not hold a pooled slot.
const sql = postgres(process.env.DATABASE_DIRECT_URL, { max: 1 })

const BATCH = 5_000
for (let lastId = 0; ; ) {
  // One transaction per batch - short enough to stay under both ceilings,
  // and restartable: re-running resumes from the last committed id.
  const moved = await sql.begin(async (tx) => {
    const rows = await tx`
      SELECT id FROM source_table
      WHERE id > ${lastId}
      ORDER BY id
      LIMIT ${BATCH}
    `
    if (rows.length === 0) return []
    await tx`
      INSERT INTO target_table (${sql(COLUMNS)})
      SELECT ${sql(COLUMNS)} FROM source_table
      WHERE id > ${lastId} AND id <= ${rows.at(-1).id}
    `
    return rows
  })
  if (moved.length === 0) break
  lastId = moved.at(-1).id
  console.log(`copied through id ${lastId}`)
}
await sql.end()

Keyset pagination (id > lastId) rather than OFFSET keeps each batch cheap as the table grows, and makes the script resumable after a failure instead of restarting from zero.

Two client-side notes that are not CapyDB-specific but cause most of the lost afternoons: a dynamic column list needs its own parentheses in an INSERT (INSERT INTO t (${sql(cols)}) SELECT …) because the array helper renders a bare comma-separated identifier list, and passing the array once (sql(cols)) is not the same as mapping each column through the helper. A malformed statement fails at parse time with a position, so nothing is written - see Troubleshooting for reading those errors.

For moving data into CapyDB from another provider, use imports rather than a hand-written script - it handles the dump/restore and extension mapping for you.

Driver and ORM notes

Each framework guide has the full setup; the pooling-relevant flags in one place:

• Prisma - url (pooled) + directUrl (direct) in the datasource, as in the Next.js + Prisma guide. If you still hit prepared-statement errors on the pooled URL, append ?pgbouncer=true to it - that makes Prisma skip protocol-level prepared statements and DEALLOCATE ALL assumptions.

• postgres.js (standalone or under Drizzle) - postgres(url, { prepare: false }) on the pooled URL.

• node-postgres (pg) - works as-is; it uses the extended protocol per query without named server-side statements. A modest client-side pg.Pool on top of the pooled URL is fine - see the Node guide.

• pgx (Go) - default statement caching breaks behind the pooler; set default_query_exec_mode=simple_protocol on the pooled URL (Go guide).

• asyncpg / SQLAlchemy async - statement_cache_size=0 on the pooled URL (SQLAlchemy guide).

• SQLAlchemy in serverless - use poolclass=NullPool so SQLAlchemy opens one connection per unit of work and lets the server-side pooler do the actual pooling; a client-side pool inside a short-lived function is dead weight that pins connections:

  from sqlalchemy import create_engine
  from sqlalchemy.pool import NullPool
  
  engine = create_engine(os.environ["DATABASE_URL"], poolclass=NullPool)

  In a long-lived server process, the small client pool from the SQLAlchemy guide is the better default.

• Rails - prepared_statements: false on the pooled URL; migrations on the direct URL because Rails serializes them with advisory locks (Rails guide).

• Django - conn_max_age=0 on the pooled URL; persistent Django connections belong on the direct URL (Django guide).

Symptoms decoder

• prepared statement "..." already exists - statement-caching driver on the pooled URL; flags above.
• too many connections - budget exhausted; move app traffic to pooled, shrink client pools, hunt idle direct sessions in Observability.
• Queries randomly missing a SET you swear you ran - session GUC on a borrowed connection; ALTER ROLE ... SET, SET LOCAL, or direct.
• cannot execute INSERT in a read-only transaction (25006) from code that never asked for read-only - a session-level SET default_transaction_read_only left behind on a borrowed connection; see Session state left behind on a server connection.
• unsupported startup parameter: ... (08P01) on the pooled URL - your driver sends that GUC in the startup packet (postgres.js connection: {...}, libpq options=) and it is not one the pooler accepts; set it with ALTER ROLE ... SET and remove it from the driver config, or use the direct URL.
• query_wait_timeout / queries failing after ~2 minutes of waiting - pool saturation; find the long transactions.

More error-message-first triage in Troubleshooting.