Choose direct or pooled connections

Supabase exposes three ways to reach the same Postgres database, and picking one by copy-paste is how production incidents start. The right choice depends on what runs your code and how long it lives.

direct        db.abcdefghijkl.supabase.co:5432
session pool  aws-0-eu-central-1.pooler.supabase.com:5432
transaction   aws-0-eu-central-1.pooler.supabase.com:6543

A direct connection is plain Postgres. Every client costs the database a real connection. It fits migrations, pg_dump, and admin work — tools that need full session behavior and run one at a time.

A session pooler hands each client a server connection for its whole session. It behaves like direct Postgres while letting the platform manage the connection supply.

A transaction pooler (port 6543) is the interesting one. Transaction pooling reuses a server connection between transactions and cannot preserve every session feature: prepared statements, session-level SET, advisory locks. In exchange it absorbs the pattern serverless creates — hundreds of short-lived function invocations that would each hold a direct connection and exhaust the database limit.

Now classify the three workloads from the exercise. A migration command: direct. A persistent API server: direct or session pooling, with a bounded pool. A burst of serverless functions: transaction pooler, no exceptions.

A long-lived server should also bound itself:

import pg from 'pg'

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 5000,
})

Every replica creates its own pool, so max times replicas must stay under the project’s connection limit — leave headroom for migrations and emergency admin access.

Match the driver to the mode

The transaction pooler needs the driver to cooperate. Prisma, for example, wants ?pgbouncer=true appended to the pooled connection string so it disables prepared statements — and still needs the direct string for prisma migrate, which the pooler cannot serve.

The failure smells like this: everything works locally, then production intermittently throws prepared statement "s0" already exists. That is a driver using prepared statements through transaction pooling, where consecutive transactions can land on different server connections. Fix the configuration, not the retry logic. Match the driver and ORM configuration to the chosen mode instead of copying a connection string blindly.