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.

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