ADR: Choosing PgBouncer over Supabase for 8-Schema Multi-Tenant Postgres in 2026

Context

We operate a self-hosted Postgres 16.4 instance serving 8 tenant schemas on a single bare-metal node (128 GB RAM, 32 cores, NVMe RAID). Each schema holds identical table structures but isolated data. Application instances (Go services, Python workers, and some legacy Ruby) connect via separate roles per schema. Total concurrent connections peaked at ~400 during batch jobs, with a steady state of ~150 active connections.

Our previous setup used direct connections to Postgres, which led to connection exhaustion and OOM kills during spikes. We needed a connection pooler. Two candidates emerged: PgBouncer (transaction pooling mode) and Supabase's hosted connection pooler (which uses PgBouncer under the hood but adds a management layer).

Decision

We chose PgBouncer 1.23.1 running locally on the same host as Postgres, configured in transaction pooling mode with auth_user and auth_query for per-role authentication. No Supabase stack was used.

Rationale

1. Latency and Network Hop

Supabase's pooler is a cloud service. Even with a nearby region, each connection traverses TLS termination, load balancer, and their infrastructure. In 2026, we measured an additional 2-4 ms per query round-trip compared to local Unix socket. For OLTP workloads with ~50 queries per request, that adds 100-200 ms. Not acceptable for our real-time agents.

Local PgBouncer over Unix socket adds <0.1 ms overhead.

2. Data Sovereignty and EU AI Act Compliance

Our tenants include EU healthcare and finance. Supabase stores connection logs and metrics in their US-based infrastructure. The EU AI Act (2025) and GDPR require that all telemetry data stays within EU borders. Running our own PgBouncer ensures zero data leaves the host. We control audit logs, retention, and encryption.

3. Configuration Flexibility

Supabase's pooler limits pool_mode to transaction, and you cannot set max_db_connections per database or server_idle_timeout below 60 seconds. We needed:

• pool_mode = transaction
• default_pool_size = 25 per schema (8 schemas = 200 backend connections max)
• server_idle_timeout = 120 seconds to keep warm connections for bursty batch jobs
• query_wait_timeout = 10 seconds to fail fast under load

PgBouncer's pgbouncer.ini gives us full control. Example config:

[databases]
* = host=/var/run/postgresql port=5432 auth_user=pgbouncer_auth

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /var/run/pgbouncer
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
pool_mode = transaction
default_pool_size = 25
max_client_conn = 600
max_db_connections = 200
server_idle_timeout = 120
query_wait_timeout = 10

4. No Lock-in

Supabase's pooler is proprietary even though it wraps open-source PgBouncer. Migrating away would require reconfiguring all connection strings and retraining ops. With vanilla PgBouncer, we can swap to HAProxy, pgpool-II, or even remove the pooler without application changes.

5. Observability

PgBouncer exposes SHOW STATS, SHOW POOLS, and SHOW CLIENTS via its admin console. We feed these into Prometheus via a small exporter (pgbouncer_exporter 0.15.0). Supabase provides a dashboard, but we cannot export raw metrics to our own monitoring stack.

Consequences

Positive

• Connection pooling works reliably. Backend connections never exceed 200, even under 600 client connections.
• Latency negligible.
• Full control over config and upgrades.
• No external dependency.

Negative

• We manage another daemon: systemd unit, log rotation, updates. PgBouncer is stable but requires attention during Postgres major upgrades (auth method changes).
• No built-in autoscaling. If traffic triples, we manually adjust default_pool_size and monitor.
• Authentication via auth_query means we must keep pg_shadow accessible; a minor security consideration.

Alternatives Considered

• pgpool-II: Heavier, more features (load balancing, failover). Overkill for single-node. Added complexity with watchdog processes.
• Supabase Pooler: Rejected for latency, sovereignty, and lock-in.
• Odyssey: Modern, multithreaded pooler. Good but less battle-tested in 2026. We may evaluate it next quarter.
• No pooler: Direct connections led to OOM during spikes. Not viable.

Conclusion

PgBouncer in transaction mode, local to Postgres, is the right choice for our multi-tenant schema-per-tenant setup in 2026. It's simple, fast, and sovereign. Supabase's pooler is a fine product for teams that want managed infrastructure and don't have strict latency or data residency requirements. We do.

Status: Accepted. Reviewed quarterly.