Scaling AWS RDS PostgreSQL with a PgBouncer Connection Pooler Proxy on EC2

PgBouncer on EC2 — Production Connection Pooling for AWS RDS PostgreSQL

A step-by-step guide to setting up production-grade connection pooling for your PostgreSQL database on AWS — without paying for managed proxies or third-party services.

Why You Need a Connection Pooler

If you're running a serverless application — whether on Vercel, AWS Lambda, or similar platforms — every function invocation can open a new database connection. PostgreSQL has a hard limit on concurrent connections determined by your instance's RAM:

max_connections ≈ DBInstanceClassMemory / 9,531,392

For a db.t4g.small (2GB RAM), that's roughly 225 max connections. At moderate traffic, a serverless app can exhaust this in seconds, resulting in:

Error: too many connections for role "myuser"

The solution is a connection pooler — a proxy that sits between your app and the database, accepting thousands of client connections while maintaining only a small pool of real database connections.

Architecture

Vercel / Your App (thousands of connections)
              │
        port 5432 (SSL)
              │
  ┌───────────▼───────────┐
  │    EC2 t4g.nano        │
  │                        │
  │      PgBouncer         │
  │  10,000 client conns   │
  │  → 180 real DB conns   │
  └───────────┬───────────┘
              │
     private VPC — port 5432 (SSL)
              │
  ┌───────────▼───────────┐
  │   AWS RDS PostgreSQL   │
  │     db.t4g.small       │
  │   max ~225 connections │
  └────────────────────────┘

Why not RDS Proxy? AWS's managed proxy is VPC-only — it cannot be accessed from outside AWS (e.g. Vercel). Making it publicly accessible is architecturally not supported.

Why not Prisma Accelerate? It works great but routes through Prisma's infrastructure and costs $10–20/mo. This guide keeps everything on AWS.

PgBouncer on EC2 is the right balance: publicly accessible, extremely lightweight, and costs ~$3/mo on a t4g.nano.

Prerequisites

An AWS RDS PostgreSQL instance (this guide uses db.t4g.small)
AWS account with EC2 access
RDS instance in a VPC with a public subnet

Step 1 — Launch the EC2 Instance

Go to AWS Console → EC2 → Launch Instance and configure:

Name: my-pgbouncer
AMI: Ubuntu Server 24.04 LTS
Instance type: t4g.nano (~$3/mo — PgBouncer uses ~30MB RAM)
Key pair: Create new, download the .pem file
VPC: Same VPC as your RDS instance
Security group (inbound): SSH port 22 from your IP, PostgreSQL port 5432 from 0.0.0.0/0

Launch and wait for the instance to reach running state.

Step 2 — SSH into the Instance

chmod 400 your-key.pem
ssh -i your-key.pem ubuntu@<ec2-public-ip>

Step 3 — Install PgBouncer

sudo apt update && sudo apt install -y pgbouncer postgresql-client

Verify:

pgbouncer --version

Step 4 — Generate SSL Certificates

PgBouncer handles SSL in two independent legs:

Client → PgBouncer: encrypted using a certificate you generate
PgBouncer → RDS: encrypted using RDS's built-in SSL

Generate a self-signed certificate for the client-facing side:

sudo openssl req -x509 -newkey rsa:4096 \
  -keyout /etc/pgbouncer/server.key \
  -out /etc/pgbouncer/server.crt \
  -days 3650 -nodes \
  -subj "/CN=<ec2-public-ip>"

sudo chown postgres:postgres /etc/pgbouncer/server.key /etc/pgbouncer/server.crt
sudo chmod 600 /etc/pgbouncer/server.key

Step 5 — Configure PgBouncer

sudo nano /etc/pgbouncer/pgbouncer.ini

Paste the following (replace the RDS endpoint with your own):

[databases]
; Route your database to your RDS instance
; PgBouncer connects to RDS without sslmode here — configured below
mydb = host=your-rds-endpoint.rds.amazonaws.com port=5432 dbname=your-db-name

[pgbouncer]

; ── Networking ────────────────────────────────────────────────
listen_port = 5432          ; same port as postgres — transparent to clients
listen_addr = *             ; accept from anywhere (EC2 security group is your firewall)

; ── Authentication ────────────────────────────────────────────
auth_type = scram-sha-256   ; matches RDS default
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgbouncer     ; allows admin console access

; ── TLS: Client → PgBouncer ───────────────────────────────────
client_tls_sslmode = require
client_tls_cert_file = /etc/pgbouncer/server.crt
client_tls_key_file = /etc/pgbouncer/server.key

; ── TLS: PgBouncer → RDS ──────────────────────────────────────
server_tls_sslmode = require

; ── Connection Pooling ────────────────────────────────────────
pool_mode = transaction
; transaction mode: connection returned to pool after each transaction
; ideal for serverless — functions hold connections only during queries

max_client_conn = 10000     ; accept unlimited app connections
default_pool_size = 180     ; real RDS connections (t4g.small max ~225)
min_pool_size = 10          ; keep 10 warm — avoids cold-start latency
reserve_pool_size = 20      ; burst capacity for traffic spikes
reserve_pool_timeout = 3    ; seconds to wait before tapping reserve pool
max_db_connections = 200    ; hard cap — never exhaust RDS

; ── Timeouts ──────────────────────────────────────────────────
server_connect_timeout = 10     ; fail fast if RDS unreachable
server_idle_timeout = 600       ; close idle RDS connections after 10 min
server_lifetime = 3600          ; recycle connections every hour (handles RDS failover)
client_idle_timeout = 0         ; don't kick idle clients
query_timeout = 0               ; no query timeout (set to 30 to kill runaway queries)

; ── TCP Keepalive ─────────────────────────────────────────────
tcp_keepalive = 1
tcp_keepidle = 60
tcp_keepintvl = 10
tcp_keepcnt = 5

; ── Logging ───────────────────────────────────────────────────
log_connections = 0     ; off in prod — too noisy
log_disconnections = 0
log_pooler_errors = 1   ; always on
stats_period = 60       ; log stats every 60s

Save with Ctrl+X, Y, Enter.

Step 6 — Set Credentials

sudo nano /etc/pgbouncer/userlist.txt

"your-db-user" "your-db-password"
"pgbouncer" "pgbouncer"

The pgbouncer user is for the admin console only — it doesn't exist in RDS.

Lock it down:

sudo chmod 600 /etc/pgbouncer/userlist.txt
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt

Step 7 — Start PgBouncer

sudo systemctl enable pgbouncer
sudo systemctl restart pgbouncer
sudo systemctl status pgbouncer

You should see active (running). If not, check logs:

sudo journalctl -xeu pgbouncer.service

Step 8 — Test the Connection

From your local machine:

psql "postgresql://your-db-user:your-password@<ec2-public-ip>:5432/your-db-name?sslmode=require"

Run a quick query:

SELECT COUNT(*) FROM users;

If it returns data — PgBouncer is working end to end. ✅

Step 9 — Update Your Application

Update your environment variables:

# Pooled runtime connection — via PgBouncer
DATABASE_URL="postgresql://your-db-user:your-password@<ec2-public-ip>:5432/your-db-name?sslmode=require"

# Direct connection — for migrations only (bypasses pooler)
DIRECT_URL="postgresql://your-db-user:your-password@your-rds-endpoint.rds.amazonaws.com:5432/your-db-name?sslmode=require"

If you're using Prisma with node-postgres, append &uselibpqcompat=true to both URLs to silence SSL deprecation warnings.

For Prisma, ensure your schema.prisma has:

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

The directUrl ensures migrations always use the direct connection — important because PgBouncer in transaction mode doesn't support all DDL operations.

Step 10 — Monitor the Pool

Connect to the PgBouncer admin console from the EC2 instance:

psql "postgresql://pgbouncer:pgbouncer@127.0.0.1:5432/pgbouncer?sslmode=require"

Useful commands:

-- Pool status: client vs real server connections
SHOW POOLS;

-- Query throughput and latency
SHOW STATS;

-- All connected clients
SHOW CLIENTS;

-- Real RDS connections open right now
SHOW SERVERS;

-- Current configuration
SHOW CONFIG;

What to look for in SHOW STATS:

avg_xact_time — Average transaction time in microseconds
avg_wait_time — How long clients wait for a free connection — keep this low
total_xact_count — Total transactions handled

If avg_wait_time starts climbing under load, increase default_pool_size (up to your RDS max connections limit).

Pool Mode Reference

session — Connection returned after the client disconnects. Behaves like no pooling. Best for traditional long-lived apps.

transaction — Connection returned after each transaction completes. The right choice for serverless (Vercel, Lambda). A function holds a connection only while a query is running, then releases it immediately.

statement — Connection returned after every single statement. Rarely used — breaks any operation that spans multiple statements (transactions, prepared statements).

Always use transaction mode for serverless deployments.

Key Configuration Parameters

max_client_conn = 10000 Max simultaneous app connections PgBouncer accepts. Set this high — client connections are cheap (~2KB each).

default_pool_size = ~80% of RDS max The number of real database connections maintained. For a db.t4g.small with ~225 max connections, set this to 180.

min_pool_size = 5–10 Keeps a minimum number of connections warm at all times. Prevents cold-start latency when traffic resumes after idle periods.

reserve_pool_size = 10–20 Extra connections available during traffic bursts, used after default_pool_size is exhausted.

server_lifetime = 3600 Forces connections to recycle every hour. Critical for RDS failover — stale connections get replaced automatically.

server_idle_timeout = 600 Closes RDS connections that have been idle for 10 minutes. Keeps the pool lean during low-traffic periods.

Cost Breakdown

EC2 t4g.nano (PgBouncer) — ~$3/mo
RDS db.t4g.small (PostgreSQL) — ~$25/mo
Total — ~$28/mo

For comparison: RDS Proxy costs ~$18/mo and doesn't work with Vercel. Prisma Accelerate costs ~$20/mo and routes through external infrastructure. PgBouncer on EC2 keeps everything on AWS at a lower cost.

Conclusion

PgBouncer on EC2 gives you production-grade connection pooling that's:

Publicly accessible (works with Vercel, Lambda, any serverless platform)
Fully on AWS (your data never leaves your infrastructure)
Extremely cheap (~$3/mo for the EC2)
Proven at scale (PgBouncer handles millions of connections in production globally)

The setup takes about 20 minutes and eliminates too many connections errors permanently, regardless of how much traffic your application receives.

Have questions or improvements? Email me at hi@surya.dev

*Scaling AWS RDS PostgreSQL with a PgBouncer Connection Pooler Proxy on EC2