File size: 4,129 Bytes
c09f67c | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 | # 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)
|