Skip to content

PgBouncer config lives at /opt/homebrew/etc/pgbouncer.ini

Greetings, brave adventurer! You stand at the final gate of the Data Keep. Inside, your database can serve thousands of queries a second - but only if visitors enter through an orderly gate rather than battering down a fresh door each time. This quest, Connection Pooling, teaches you to be the gatekeeper: to understand the true cost of a connection, to size a pool so it neither starves nor floods the database, to deploy PgBouncer, and to hunt the connection leaks that quietly exhaust a healthy system.

Opening a database connection is expensive - far more expensive than most developers realize. A pool reuses a small set of warm connections instead of paying that cost on every request. Get pooling right and a modest database serves an army; get it wrong and a handful of users brings it to its knees with "too many connections."

📖 The Legend Behind This Quest

Every PostgreSQL connection spawns an entire operating-system process with its own memory. A few hundred is fine; a few thousand will consume gigabytes of RAM and grind the server to dust. Yet modern applications, especially serverless and high-concurrency ones, want to open connections freely. The connection pool resolves this tension: a thin layer that maintains a fixed, reusable set of database connections and lends them out for the brief moments each request actually needs the database. PgBouncer, the most famous PostgreSQL pooler, weighs only a few megabytes yet lets thousands of clients share a few dozen real connections.

This quest teaches the lifecycle, the math of pool sizing, and the practical configuration that keeps the gate flowing.

🎯 Quest Objectives

By the time you complete this journey, you will have mastered:

Primary Objectives (Required for Quest Completion)

  • The Connection Lifecycle - Understand what opening, using, and closing a connection costs
  • Pool Sizing - Calculate a pool size that maximizes throughput without overload
  • PgBouncer - Deploy and configure a real connection pooler
  • Connection Leaks - Detect and fix connections that are never returned

Secondary Objectives (Bonus Achievements)

  • Pooling Modes - Choose session, transaction, or statement pooling correctly
  • Application Pools - Tune an in-app pool (HikariCP, SQLAlchemy, pgbouncer in front)
  • Monitoring - Watch active vs idle connections to catch trouble early

Mastery Indicators

You'll know you've truly mastered this quest when you can: - [ ] Explain why opening a connection per request is wasteful - [ ] Apply a pool-sizing formula to a given core count and workload - [ ] Pick the right PgBouncer pooling mode for an application - [ ] Diagnose a "too many connections" error as a leak versus undersized DB

🗺️ Quest Prerequisites

📋 Knowledge Requirements

  • Understanding of how applications open database connections
  • Comfort editing config files and running services
  • Completion of Database Fundamentals (recommended)

🛠️ System Requirements

  • Modern operating system (Windows 10+, macOS 10.14+, or Linux)
  • PostgreSQL 14+ installed, or Docker to run it
  • PgBouncer (via package manager or Docker)

🧠 Skill Level Indicators

This 🟡 Medium quest expects: - [ ] You can run and configure background services - [ ] You are ready to reason about concurrency and limited resources - [ ] Ready for 45-60 minutes of focused, hands-on learning

🌍 Choose Your Adventure Platform

Run PostgreSQL, then put PgBouncer in front of it. The Docker path is the quickest way to run both.

🍎 macOS Kingdom Path

Click to expand macOS instructions 
brew install postgresql@16 pgbouncer
brew services start postgresql@16
createdb gatekeeper
# PgBouncer config lives at /opt/homebrew/etc/pgbouncer.ini

🪟 Windows Empire Path

Click to expand Windows instructions 
winget install PostgreSQL.PostgreSQL.16
createdb gatekeeper
# PgBouncer on Windows is easiest via WSL or the Docker path below.

🐧 Linux Territory Path

Click to expand Linux instructions 
sudo apt update && sudo apt install -y postgresql pgbouncer
sudo systemctl enable --now postgresql
sudo -u postgres createdb gatekeeper
# PgBouncer config lives at /etc/pgbouncer/pgbouncer.ini

☁️ Cloud Realms Path

Click to expand Cloud/Container instructions 
docker run --name gatekeeper-db -e POSTGRES_PASSWORD=quest -p 5432:5432 -d postgres:16
docker run --name pgbouncer --link gatekeeper-db -p 6432:6432 \
  -e DATABASES_HOST=gatekeeper-db -e DATABASES_USER=postgres \
  -e DATABASES_PASSWORD=quest -d edoburu/pgbouncer
# Apps connect to port 6432 (PgBouncer), which forwards to 5432 (PostgreSQL).

🧙‍♂️ Chapter 1: The Connection Lifecycle and Its Cost

A database connection is not a free abstraction. In PostgreSQL each one is a full OS process - costing a TCP handshake, authentication, and several megabytes of server memory. Opening one per HTTP request is like hiring a new gatekeeper for every visitor and firing them at the door.

⚔️ Skills You'll Forge in This Chapter

  • The steps of opening, using, and closing a connection
  • Why connections are expensive in PostgreSQL specifically
  • How reuse via a pool changes the economics

🏗️ Why Reuse Beats Reconnect

# ❌ Anti-pattern: open and close a connection on every single request.
def handle_request(query):
    conn = psycopg2.connect("dbname=gatekeeper")  # TCP + auth + process spawn
    result = conn.execute(query)                  # the actual work (milliseconds)
    conn.close()                                  # tear it all down again
    return result
# Under load this spends most of its time connecting, not querying.

# ✅ Pooled: borrow a warm connection, use it, return it to the pool.
pool = ConnectionPool("dbname=gatekeeper", min_size=5, max_size=20)
def handle_request_pooled(query):
    with pool.connection() as conn:   # borrow (instant - already open)
        return conn.execute(query)    # ...returned to the pool automatically

The lifecycle of a pooled connection is: created once when the pool starts, borrowed for the brief span of a query, returned immediately after, and reused thousands of times. The expensive setup is paid once, not per request. This is why a pool can turn a database that handled 50 requests/second into one that handles thousands.

🔍 Knowledge Check: Lifecycle

  • What expensive steps happen when you open a new PostgreSQL connection?
  • Why is a PostgreSQL connection more costly than, say, an HTTP request?
  • What does a pooled connection's lifecycle look like across many requests?

⚡ Quick Wins and Checkpoints

  • Cost understood: You can name why per-request connections are wasteful
  • Pool grasped: You can describe borrow-and-return reuse

🧙‍♂️ Chapter 2: Sizing the Pool - The Goldilocks Number

A pool too small starves requests, which queue and time out. A pool too large floods the database with more concurrent work than its CPUs and disks can handle, and everything slows down. The right size is surprisingly small.

⚔️ Skills You'll Forge in This Chapter

  • The counterintuitive math of pool sizing
  • Why more connections often means less throughput
  • Accounting for multiple app instances

🏗️ A Sizing Formula

A widely cited starting point (from the HikariCP project) is:

pool_size = (core_count * 2) + effective_spindle_count

  core_count             = CPU cores on the database server
  effective_spindle_count = number of disks that can seek in parallel
                            (use ~1 for SSD-backed or cloud storage)

Example: an 8-core database on SSD storage
  pool_size = (8 * 2) + 1 = 17  ->  start around 15-20, not 200.

Why so small? Because a database can only truly do as many things at once as it has cores and disk channels. Beyond that, extra connections just context-switch and contend, making everything slower. The classic surprise: reducing a pool from 100 to 20 connections often increases throughput.

Crucially, the pool limit is per application instance. If you run 10 app servers each with a pool of 20, that is 200 connections at the database - which may exceed PostgreSQL's max_connections (often 100). This is exactly the scenario where a shared external pooler like PgBouncer becomes essential: hundreds of app-side connections collapse into a few dozen real database connections.

🔍 Knowledge Check: Sizing

  • Why can a smaller pool deliver higher throughput than a larger one?
  • How do multiple app instances multiply your total connection count?
  • What value do you use for effective_spindle_count on SSD/cloud storage?

🧙‍♂️ Chapter 3: PgBouncer and Hunting Connection Leaks

PgBouncer is a tiny, battle-tested pooler that sits between your apps and PostgreSQL. And the bug it most often saves you from is the connection leak - a borrowed connection that is never returned, slowly exhausting the pool until the database screams "too many connections."

⚔️ Skills You'll Forge in This Chapter

  • Configuring PgBouncer and its pooling modes
  • Choosing session vs transaction pooling
  • Detecting and fixing connection leaks

🏗️ Configure PgBouncer

; pgbouncer.ini - a minimal transaction-pooling setup
[databases]
gatekeeper = host=127.0.0.1 port=5432 dbname=gatekeeper

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
pool_mode = transaction          ; return the connection after each transaction
max_client_conn = 1000           ; many clients may connect to PgBouncer...
default_pool_size = 20           ; ...but only 20 real connections to PostgreSQL

Pooling modes trade flexibility for efficiency:

  • session: a client holds a server connection for its whole session (safest, least sharing).
  • transaction: the connection is returned after each transaction (the sweet spot for web apps - far more sharing). Avoid session-level features like prepared statements across transactions.
  • statement: returned after every statement (most aggressive; no multi-statement transactions).

Now hunt the leak. A leak is code that borrows a connection and never returns it - usually a missing close() or a with block escaped by an exception:

-- Ask PostgreSQL who is connected and what they are doing.
SELECT pid, state, query, state_change
FROM pg_stat_activity
WHERE datname = 'gatekeeper'
ORDER BY state_change;
-- Many rows stuck in 'idle in transaction' for a long time = a LEAK.

Connections piling up as idle in transaction are the classic fingerprint of a leak: the app opened a transaction, then wandered off without committing or rolling back. The fix is always to ensure connections are returned in a finally block or a context manager (with), even when exceptions fire.

🔍 Knowledge Check: PgBouncer & Leaks

  • What does pool_mode = transaction change compared to session?
  • How does PgBouncer let 1000 clients share 20 real connections?
  • What does a row stuck in idle in transaction usually indicate?

🎮 Mastery Challenges

🟢 Novice Challenge: Size a Pool

Objective: Given a database server's specs, compute a sensible pool size.

Requirements: - [ ] State the core count and storage type - [ ] Apply the sizing formula - [ ] Account for the number of app instances

Validation: Your total connections stay under PostgreSQL's max_connections.

🟡 Intermediate Challenge: Put PgBouncer in Front

Objective: Configure PgBouncer in transaction mode and route an app through it.

Requirements: - [ ] A working pgbouncer.ini with a bounded default_pool_size - [ ] The app connects to PgBouncer's port, not PostgreSQL's - [ ] Verify queries still succeed through the pooler

Validation: Many client connections map to a small number of server connections.

🔴 Advanced Challenge: Catch a Leak

Objective: Reproduce a connection leak, observe it in pg_stat_activity, and fix it.

Requirements: - [ ] Write code that borrows a connection without returning it - [ ] Show connections accumulating as idle in transaction - [ ] Fix it with a context manager or finally and confirm the count stabilizes

Validation: After the fix, idle-in-transaction connections no longer grow unbounded.

🏆 Quest Rewards & Achievements

🎖️ Badges Earned: - 🏆 Gatekeeper of the Pool - You sized and configured a connection pool - 🛡️ Hunter of Leaks - You traced and sealed a connection leak

🛠️ Skills Unlocked: - Connection Pool Configuration - Deploy and tune PgBouncer or an app pool - Resource-Aware Scaling - Match concurrency to the database's real limits

🔓 Unlocked Quests: - You have completed the Level 0110 Database Mastery quest line! Advance to Level 0111: API Development.

📊 Progression Points: +75 XP

🗺️ Next Steps in Your Journey

Continue the Main Story: - 🎯 Advance to Level 0111: API Development - serve your well-pooled database over HTTP

Explore Side Adventures: - ⚔️ Query Optimization - Fewer, faster queries need fewer connections - ⚔️ Backup and Recovery - Protect the database behind the gate

Character Class Recommendations

💻 Software Developer: Continue to Level 0111: API Development 🏗️ System Engineer: Explore Backup and Recovery 📊 Data Scientist: Revisit Query Optimization

📚 Resources

Official Documentation

Community Resources

Learning Materials

🤝 Quest Completion Checklist

  • ✅ Completed all primary objectives
  • ✅ Configured a pool in front of PostgreSQL
  • ✅ Answered all knowledge check questions
  • ✅ Completed at least one mastery challenge
  • ✅ Explored the resource library
  • ✅ Identified your next quest in the journey

🕸️ Knowledge Graph

Structured wiki-links connect this quest to the IT-Journey knowledge graph. Open the Obsidian Graph View to explore connections.

Level hub: [[Level 0110 - Database Mastery]] Overworld: [[🏰 Overworld - Master Quest Map]] Prerequisites: [[Database Fundamentals: The Relational Model and ACID]] Unlocks: [[Level 0111 - API Development]] Obsidian docs: [[Obsidian Knowledge Graph and Wiki Links]]