PgBouncer for Connection Pooling

You’ve Got Too Many Database Connections (Probably)

Here’s the thing: Postgres is great. Postgres is stable. Postgres will happily handle hundreds of concurrent connections. But that doesn’t mean you should throw a hundred app instances at it and let them all open their own TCP socket. That’s like hiring a forklift to move a couch—technically it works, but something’s fundamentally wrong with your approach.

If you’re running a home lab with a bunch of microservices, Kubernetes pods, or just several apps hitting the same database, connection overhead gets real fast. Each connection costs memory, CPU cycles, and socket file descriptors. Your Postgres instance starts sweating, your OOM killer gets twitchy, and everyone’s latency goes sideways.

Enter PgBouncer: a lightweight connection pooler that sits between your apps and Postgres. It’s the traffic cop—your apps talk to PgBouncer, and PgBouncer manages a smaller, sane pool of actual Postgres connections. The beauty is, you don’t need to change your app code. Just point your connection string at PgBouncer instead of Postgres directly.

This isn’t a “nice-to-have” optimization for home labs. It’s a “why didn’t you do this sooner” move once you hit 30+ concurrent connections.

How PgBouncer Actually Works

PgBouncer doesn’t execute queries. It’s not a proxy in the sense that it understands SQL. Instead, it’s more like a revolving door: apps open a connection to it, PgBouncer opens a connection to Postgres on their behalf, and when the app is done, PgBouncer keeps that Postgres connection open for the next app to reuse.

This matters because opening a TCP connection is expensive—TCP handshake, SSL negotiation (if you’re being sensible), Postgres authentication, etc. If you’ve got 50 app instances and each opens a fresh connection every 30 seconds, you’re burning CPU and memory on handshakes instead of actual work.

The trick is that PgBouncer operates at different “modes” depending on how aggressive you want to be with pooling. This is where things get spicy.

The Three Pooling Modes (and When Each One Breaks Your Code)

Session Mode: The Safe Default

In session mode, PgBouncer maintains a one-to-one relationship between client connections and Postgres connections. When your app connects, it gets a dedicated Postgres backend. When the app disconnects, that backend goes back to the pool.

This is the gentlest mode. Your app can use:

• Transaction state (SET SESSION)
• Prepared statements
• Temp tables
• Named cursors
• application_name tracking

Basically, everything works like the app is talking directly to Postgres.

Tradeoff: You don’t get much pooling benefit. If you’ve got 100 apps, you still end up with ~100 Postgres connections. You reduce some handshake overhead, but the main problem (connection count) isn’t solved.

Transaction Mode: The Efficient One (With Gotchas)

In transaction mode, PgBouncer returns a Postgres connection to the pool after every transaction, not after every client disconnect. So your app can hold a connection open for 10 seconds, but the underlying Postgres connection might be juggled between your app and 5 others in that same 10 seconds.

This is where the magic happens. You can have 100 apps talking to PgBouncer, but only 20 actual Postgres connections. PgBouncer queues the apps and hands off connections as transactions complete.

The catch: Prepared statements don’t work. Here’s why: prepared statements are connection-specific. You prepare SELECT * FROM users WHERE id = $1 on connection A, get back a statement handle, then send values. But transaction mode might hand off your next query to connection B—which has no idea what your prepared statement handle refers to. Boom. ERROR: prepared statement "..." does not exist.

This kills:

• ORM prepared statement caching (your Django ORM, SQLAlchemy, etc.)
• Raw PREPARE statements
• Any code that relies on statement state persisting across queries

Workaround: Some ORMs let you disable statement caching. Some apps use a separate direct Postgres connection for prep work. Some teams just… live with transaction mode’s limitations because the pooling wins are worth it.

Statement Mode: The Aggressive Mode

In statement mode, PgBouncer returns a connection to the pool after every single query. Even faster pooling. Even fewer Postgres connections needed.

The gotcha: Everything that transaction mode breaks plus you lose transaction state. If you do:

BEGIN;
INSERT INTO users (name) VALUES ('Alice');
SELECT * FROM users WHERE name = 'Alice';
COMMIT;

That might run across three different Postgres connections. Your transaction semantics are wrecked. The SELECT might see uncommitted data from another client (violating isolation), or the connection that executes COMMIT might not be the one that did BEGIN.

Use case: Read-only microservices that never do multi-query transactions. Otherwise, don’t.

Setting Up PgBouncer: The Config

Here’s a bare-minimum pgbouncer.ini that works for home labs:

[databases]
# Map logical database name to actual Postgres
myapp = host=postgres port=5432 dbname=myapp_prod user=appuser password=secret

[pgbouncer]
# Network listen
listen_port = 6432
listen_addr = 127.0.0.1

# Pooling
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

# Timeouts
server_lifetime = 3600
idle_transaction_timeout = 60
query_timeout = 0
idle_in_transaction_session_timeout = 60

# Auth
auth_type = plain
auth_file = /etc/pgbouncer/userlist.txt

# Admin console
admin_users = pgbouncer

Key settings:

• pool_mode: Set to transaction for most home labs. Use session if you need prepared statements.
• max_client_conn: Hard limit on app connections to PgBouncer. Don’t make this massive; queue the apps instead.
• default_pool_size: Target number of Postgres connections per database. Tune based on your workload and Postgres connection limit.
• reserve_pool_size: Emergency connections opened if the main pool is exhausted. Keep small (5–10).
• server_lifetime: Force-close idle connections after this many seconds. Prevents connection leaks.
• idle_in_transaction_session_timeout: Kill connections that have an open transaction but aren’t sending queries. Critical for buggy apps that forget COMMIT.

The userlist.txt file maps app usernames to passwords (plaintext or MD5):

"appuser" "secret"
"readonly_user" "ro_password"
"pgbouncer" "admin_password"

Docker Compose Setup (Home Lab Style)

Here’s a Compose setup that keeps Postgres and PgBouncer separate but linked:

version: "3.9"

services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: myapp_prod
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD: secret
      POSTGRES_INITDB_ARGS: "-c max_connections=100"
    volumes:
      - postgres_data:/var/lib/postgresql/data
    networks:
      - db_network
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U appuser"]
      interval: 10s
      timeout: 5s
      retries: 5

  pgbouncer:
    image: pgbouncer/pgbouncer:1.22-alpine
    environment:
      DATABASES_HOST: postgres
      DATABASES_PORT: 5432
      DATABASES_USER: appuser
      DATABASES_PASSWORD: secret
      DATABASES_DBNAME: myapp_prod
      PGBOUNCER_POOL_MODE: transaction
      PGBOUNCER_MAX_CLIENT_CONN: "100"
      PGBOUNCER_DEFAULT_POOL_SIZE: "20"
      PGBOUNCER_RESERVE_POOL_SIZE: "5"
      PGBOUNCER_SERVER_LIFETIME: "3600"
    ports:
      - "6432:6432"
    depends_on:
      postgres:
        condition: service_healthy
    networks:
      - db_network

  myapp:
    image: myapp:latest
    environment:
      # Point your app at PgBouncer, not Postgres directly
      DATABASE_URL: postgres://appuser:secret@pgbouncer:6432/myapp_prod
    depends_on:
      - pgbouncer
    networks:
      - db_network

volumes:
  postgres_data:

networks:
  db_network:
    driver: bridge

The key: set DATABASE_URL to pgbouncer:6432, not postgres:5432. PgBouncer listens on port 6432 by default.

Monitoring: Know When Things Go Sideways

PgBouncer exposes metrics on its admin console. Connect to port 6432 with user pgbouncer and run:

psql -h localhost -U pgbouncer -p 6432 pgbouncer

Then inside the console:

SHOW STATS;

This gives you per-database stats: total requests, in/out bytes, transaction count, errors. Watch for:

• wait: Number of clients waiting for a server connection. High numbers = your pool is too small.
• errors: Query errors, usually from pooling mode incompatibilities (prepared statements in transaction mode).

Or query it programmatically:

echo "SHOW STATS;" | psql -h localhost -U pgbouncer -p 6432 pgbouncer

For deeper observability, PgBouncer logs to stderr by default. If you’re using the Docker image, check logs:

docker logs pgbouncer

If you really care, wire up Prometheus scraping. Some teams export PgBouncer metrics using custom scripts (since PgBouncer doesn’t natively expose Prometheus format), but for a home lab, the SHOW STATS console is usually enough.

The Prepared Statement Landmine

This deserves its own section because it catches everyone once.

You’re using Django, SQLAlchemy, or some other ORM. The ORM caches prepared statements to make repeated queries faster. You deploy everything pointing at PgBouncer in transaction mode. Everything works great for an hour. Then your app starts throwing ERROR: prepared statement "..." does not exist.

Here’s what happened: your ORM prepared SELECT * FROM users WHERE id = $1 on connection A, got a statement name like _sa_1 back. On the first query, it worked. On the second query, maybe a different connection served your request. That connection doesn’t have _sa_1 prepared, so the ORM’s reference fails.

Fixes:

1. Switch to session mode if you can tolerate the reduced pooling benefit.
2. Disable prepared statement caching in your ORM (usually a config flag).
3. Use a direct connection for DDL/admin work and a separate pooled connection for the app.
4. Accept the error and catch/retry it (lol, no).

Most teams in a home lab just bite the bullet and use session mode unless they’re desperately trying to reduce connection overhead. The performance difference between session and transaction mode is negligible unless you’re running thousands of concurrent clients.

When Do You Actually Need This?

PgBouncer isn’t a “everyone should do this” thing. It’s a “your situation has gotten bad” thing.

You probably need it if:

• You’re running 30+ concurrent app connections
• Your Postgres max_connections setting is approaching its limit
• You’ve got a swarm of microservices, Kubernetes pods, or serverless functions all hitting the same database
• Your home lab has grown from “one thing” to “five things sharing Postgres”
• You’re seeing connection-related errors in Postgres logs

You probably don’t need it if:

• You’ve got 1–2 apps talking to Postgres
• Your Postgres connection pool is sitting at 10/100 with plenty of headroom
• You’re OK with adding connection pooling inside your app (many frameworks have this built-in)

The Real Take

PgBouncer is that thing you deploy when you realize scaling your database to accommodate a hundred individual app connections is dumb. Instead of making Postgres sweat, you make a lightweight proxy sweat for you. It’s one of those underrated tools that just… works. No fancy logic, no distributed consensus, no 500-page book to understand. Just a connection queue and some smart handoff logic.

In a home lab, it’s usually the first piece of infrastructure you add once you move beyond “run everything on one box.” Stick with transaction mode, accept that you can’t use prepared statements, and enjoy the freed-up connections.

Your Postgres instance at 2 AM will thank you.

Related Reading

• DBeaver vs pgAdmin vs DataGrip
• Postgres in Docker: The Durable Setup
• Postgres Replication: Streaming + Logical
• PostGIS for Self-Hosted Mapping
• Postgres Backups: pg_dump vs pgBackRest vs WAL-G