docs/database-connection-pooling.md

Database Connection Pooling

Technical documentation for the database connection setup across Supabase and Railway.

Overview

The application connects to Supabase Postgres through Supavisor (Supabase's shared connection pooler) in transaction mode. Each Railway region's API instance reads from the closest Supabase read replica and writes to the primary database in EU.

Connection Modes

Supabase offers two pooling modes via pooler.supabase.com:

Mode	Port	Behavior
Session mode	5432	1:1 client-to-backend mapping. No real pooling — each app connection holds a dedicated Postgres connection for its entire lifetime.
Transaction mode	6543	Real connection pooling. Backend connections are shared between clients and only held during a transaction, then returned to the pool.

We use transaction mode (port 6543). This is critical — session mode on port 5432 provides zero pooling benefit despite routing through pooler.supabase.com.

Connection String Format

postgresql://postgres.<project-ref>:<password>@aws-0-<region>.pooler.supabase.com:6543/postgres

Dedicated Pooler (PgBouncer)

Supabase also offers a dedicated PgBouncer co-located on the database machine at db.<ref>.supabase.co:6543. This has lower latency (no network hop to a separate server) but requires IPv6 connectivity or the Supabase IPv4 add-on ($4/mo per database).

Railway does not support IPv6 to Supabase's direct endpoints, so the shared Supavisor pooler is the correct choice for our infrastructure.

Multi-Region Replica Mapping

The API runs in 3 Railway regions. Each instance reads from the closest Supabase read replica via the RAILWAY_REPLICA_REGION environment variable:

Railway Region	Env Var	Supabase Region	Role
europe-west4-drams3a	DATABASE_FRA_URL	eu-central-1	Primary (reads + writes)
us-east4-eqdc4a	DATABASE_IAD_URL	us-east-1	Read replica
us-west2	DATABASE_SJC_URL	us-west-1	Read replica

• Reads are routed to the regional replica via executeOnReplica and the withReplicas wrapper in packages/db/src/replicas.ts.
• Writes always go to DATABASE_PRIMARY_URL (the primary in eu-central-1).

Pool Configuration

Defined in packages/db/src/client.ts:

Setting	Development	Production
max	8	12
idleTimeoutMillis	5,000ms	60,000ms
connectionTimeoutMillis	5,000ms	5,000ms
maxUses	100	0 (unlimited)
ssl	disabled	enabled (rejectUnauthorized: false)

Each API instance creates up to 2 pools (primary + 1 regional replica), so the maximum backend connections per instance is 12 × 2 = 24. With Supavisor transaction mode, these are multiplexed into a much smaller number of actual Postgres backend connections.

Prepared Statements

Transaction mode does not support named prepared statements. The node-postgres (pg) package with Drizzle ORM uses unnamed parameterized queries (extended query protocol without a name field), which are compatible with transaction mode. No special configuration is needed.

Do not add named queries like pool.query({ name: 'my-query', text: '...' }) — these will fail through the pooler.

Environment Variables

API Service

• DATABASE_PRIMARY_URL — Primary database (EU, writes + reads)
• DATABASE_FRA_URL — EU read replica (same as primary)
• DATABASE_IAD_URL — US East read replica
• DATABASE_SJC_URL — US West read replica

Worker Service

• DATABASE_PRIMARY_POOLER_URL — Primary database (EU)

Dashboard Service

• No database variables — connects to the API via tRPC, not directly to Postgres.

Railway Deploy Configuration

• API: 3 regions × 1 replica each (production), 3 regions × 1 replica each (staging)
• Dashboard: 3 regions × 2 replicas each (production), 3 regions × 1 replica each (staging, with serverless/sleep enabled)
• Worker: 1 region (EU) × 3 replicas (production)