Open Project Settings → Database in the Supabase dashboard and you are offered three connection strings that differ by a hostname and a port number:
# Direct connection
postgresql://postgres:[password]@db.<ref>.supabase.co:5432/postgres
# Session pooler (Supavisor, session mode)
postgresql://postgres.<ref>:[password]@aws-0-<region>.pooler.supabase.com:5432/postgres
# Transaction pooler (Supavisor, transaction mode)
postgresql://postgres.<ref>:[password]@aws-0-<region>.pooler.supabase.com:6543/postgres
Choosing wrong does not fail immediately. It fails the first time you get real concurrent traffic, which is the worst possible moment to learn the difference.
What each one is
Direct (db.<ref>.supabase.co:5432) is a real Postgres connection to your
instance. One client connection equals one Postgres backend. Your project's
max_connections is small (on the smaller compute sizes, tens, not hundreds)
and Postgres reserves some for itself. It is also IPv6-only on the free tier
now, which is the cause of the mystifying ENETUNREACH when a build machine or a
serverless runtime has no IPv6 route.
Session pooler (...pooler.supabase.com:5432) is Supavisor holding the
connection open for the whole client session. It behaves like a direct
connection (prepared statements, SET, LISTEN, advisory locks all work) but
gives you an IPv4 address and shields Postgres from connection storms a little.
One client connection still occupies one backend for as long as it is open.
Transaction pooler (...pooler.supabase.com:6543) is Supavisor in
transaction mode. A backend is assigned for the duration of a transaction and
then handed to the next client. Thousands of clients, a few dozen backends.
The rule
- Serverless runtime (Vercel functions, edge, Lambda): transaction pooler, 6543.
- Long-lived server (a container, a VM, a worker you control): session pooler or direct.
- Migrations,
db diff, introspection,pg_dump: direct, or session pooler on 5432. Never 6543.
In this project that is DATABASE_URL (6543, what src/db/client.ts opens) and
DIRECT_URL (5432, what the Supabase CLI and migrations use).
Why serverless needs the transaction pooler
A serverless platform does not queue requests behind a pool; it creates instances, and each instance opens its own connections. Sixty concurrent invocations with a pool of five each is 300 connections against a project that allows sixty. You get:
FATAL: remaining connection slots are reserved for non-replication superuser connections
FATAL: sorry, too many clients already
The transaction pooler exists precisely to absorb that. It is not an optimisation; on serverless it is the only thing that works.
Why the transaction pooler breaks prepared statements
Transaction mode gives you a different backend per transaction. A named
prepared statement lives on one backend. So a driver that prepares statements by
name will, under concurrency, prepare s1 on backend A and later try to execute
s1 on backend B:
PostgresError: prepared statement "s1" already exists
PostgresError: prepared statement "s3" does not exist
Intermittent, load-dependent, and impossible to reproduce locally. The fix is to turn prepared statements off when you are on 6543:
// src/db/client.ts already does this; the predicate lives in src/db/url.ts so
// the /verify check and anything you write next cannot disagree about it.
const pooled = isTransactionPooler(url); // url.includes(":6543")
postgres(url, {
max: pooled ? 1 : 10,
prepare: !pooled,
});
For other drivers the same switch has different names: node-postgres (under
Prisma 7's PrismaPg adapter) sends unnamed statements unless you name them,
and some clients call it statement_cache_size=0. Same idea every time.
Note the max: 1 as well. Supavisor is the pool now. A client-side pool of ten
per instance multiplies your pooler slots by ten for no benefit: a serverless
instance serves one request at a time.
Why migrations must not use 6543
Migration tools take a session-level advisory lock so two deploys cannot
apply the same migration at once. Session-level state does not survive
transaction pooling: the lock is taken on one backend and looked for on another.
Add to that create index concurrently (cannot run inside a transaction block)
and multi-statement DDL (can land on different backends), and the failure modes
range from "hangs forever" to "half-applied schema".
So the CLI and the migration runner read DIRECT_URL:
bunx supabase db push --db-url "$DIRECT_URL"
If you only ever set one variable, the day you deploy a migration is the day you find out.
The IPv6 problem, concretely
The direct host resolves to IPv6 only. Plenty of build environments and CI runners have no IPv6 route, so:
Error: connect ENETUNREACH 2600:1f16:...:5432
This is not a firewall on Supabase's side and not a credential problem. Use the session pooler on 5432 instead: it is IPv4-reachable and behaves like a direct connection for every purpose migrations care about. That is the single most useful thing to know when a migration works on your laptop and not in a deploy.
Local development
bun run db:start runs Postgres in Docker on localhost:54322 with no pooler
in front of it, so prepared statements and session state all work, and there is
no pooled/direct distinction to get wrong. Point both DATABASE_URL and
DIRECT_URL at the local URL while you develop. src/db/client.ts already
disables TLS for localhost.
How to check what you have
-- run against DATABASE_URL, under load
select count(*), state from pg_stat_activity
where datname = current_database() group by state;
Flat and small while traffic climbs means the pooler is doing its job. Climbing with concurrency means you are on a direct or session connection and are counting down to an outage.
And in code, the cheap assertion bun run verify makes: DATABASE_URL should
contain :6543 in every deployed environment, DIRECT_URL should not.
Summary table
| Use | Host | Port | Prepared statements |
|---|---|---|---|
| App on serverless | ...pooler.supabase.com | 6543 | off |
| App on a long-lived server | ...pooler.supabase.com | 5432 | on |
Migrations, db diff, pg_dump | db.<ref>.supabase.co or pooler | 5432 | on |
| Local dev | localhost | 54322 | on |