Module 6: Advanced Connection Pooling

Module 6: Advanced Connection Pooling

Next →

You reached the module where you stop looking at individual queries and start looking at how your app connects to PostgreSQL as a whole. Until now, in modules 2 through 5, you assumed that opening a connection is free and that PostgreSQL can handle any amount. In production neither of those two things is true: each connection costs ~10MB of RAM in PostgreSQL, opening a new connection takes 50-200ms, and max_connections rarely goes above 100. If your API serves 200 RPS without a decent pool, you run out of connections in seconds.

This module teaches you to configure the complete connection flow — from SQLAlchemy's internal pool to PgBouncer as an industrial external pool — so your FastAPI API can sustain hundreds of RPS without saturating the database. You're going to understand the three PgBouncer modes, when each one is appropriate, and you're going to learn the most expensive gotcha when introducing PgBouncer with asyncpg: the prepared statement cache breaks silently if you don't configure it explicitly.

By the end of the module, you'll be able to diagnose "connection pool exhausted" errors, configure PgBouncer in transaction mode with asyncpg correctly, and size your pool with concrete criteria instead of copying numbers from a Stack Overflow post.

Where are we? Where are we going?

What you already know (modules 1 through 5):

  • Measure an API's baseline with wrk or locust, distinguish p50/p95/p99 (module 1).
  • Read plans with EXPLAIN (ANALYZE, BUFFERS) and understand cost, scan types, JIT (module 2).
  • Design advanced indexes (composite, covering, partial, expression) that the planner actually uses (module 3).
  • Eliminate the N+1 problem with selectinload, joinedload, subqueryload, and know the special rules of async (module 4).
  • Identify the most expensive queries in production with pg_stat_statements and auto_explain (module 5).

What you're going to build this time:

A complete pooling setup for async FastAPI: a well-tuned internal SQLAlchemy pool, PgBouncer as an external pool in transaction mode, and the operational knowledge to diagnose pool problems before they take down your API.

Why this module comes here:

Module 5 gave you the tools to identify slow queries. But sometimes the problem is not the query — it's that your pool is exhausted, your connections stay idle in transaction, or PgBouncer is reassigning connections and breaking prepared statements. Without correct pooling, optimizing queries is useless: the app collapses under load anyway because it can't even open the connection to run the optimized query.

And this module comes before module 7 (autovacuum, statistics, planner) because pooling is an application problem: you control it from your code and from the intermediate layer. What's in module 7 lives inside PostgreSQL. The order reflects "from the outside in".

Professional objective

By the end of this module you'll be able to:

  • Diagnose "connection pool exhausted" errors using PgBouncer's SHOW POOLS and PostgreSQL's pg_stat_activity.
  • Configure PgBouncer in transaction mode with asyncpg, including the statement_cache_size=0 setting that prevents random prepared statement breakages.
  • Decide among the three PgBouncer modes (session, transaction, statement) based on the features your app needs to preserve.
  • Tune the SQLAlchemy 2.0 async pool parameters (pool_size, max_overflow, pool_pre_ping, pool_recycle, pool_timeout) with well-founded criteria.
  • Calculate a reasonable pool sizing for an async app, understanding why the classic HikariCP formula doesn't apply directly.

Why does this module matter?

Connection pooling is the #1 problem that takes down APIs in production when they scale. It's not theoretical: every time an API starts receiving 100+ sustained RPS, the following errors appear almost without fail:

  • sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out
  • psycopg2.OperationalError: FATAL: too many connections for role "app_user"
  • asyncpg.exceptions.InvalidSQLStatementNameError: prepared statement "__asyncpg_stmt_xxx__" does not exist (the PgBouncer + asyncpg gotcha)
  • "idle in transaction" connections that never close because someone forgot a commit/rollback

Any of the four takes down your API. And all four are prevented with the content of this module.

In the real role of a senior backend dev, configuring pooling is one of the things that most differentiates you vs a junior. The junior copies pool_size=20 from a blog. The senior calculates the size based on PostgreSQL's max_connections, the number of FastAPI instances, the PgBouncer mode, and the expected load. The difference shows up in production when the Black Friday deploy doesn't take down the app.

For senior interviews this topic comes up directly: "how would you configure the pool of an async FastAPI API behind PgBouncer?" or "what happens if you enable PgBouncer in transaction mode and your app uses prepared statements?". If you can't answer these, they leave you at mid-level no matter how well you know FastAPI.

A scenario that illustrates the module

Your team launched a FastAPI API three months ago. It worked perfectly in staging with 10 RPS. In production, with 80 RPS, everything went fine for two weeks. Then a marketing promo came that pushed 250 sustained RPS for an hour.

Five minutes into the promo, monitoring alerts you: the /orders endpoint is responding with 503. Logs show:

sqlalchemy.exc.TimeoutError: QueuePool limit of size 10 overflow 20 reached

You connect to PostgreSQL to see what's happening. pg_stat_activity shows 95 active connections (out of 100 allowed in max_connections). Question: is the problem the app's pool or PostgreSQL's limit?

Without what you learn in this module, the answer is "no idea, raise max_connections and pool_size and pray". With this module, the debugging goes like this:

  1. Capsule 02 (pool fundamentals): you identify that your pool is saturated and what exactly "saturated" means (all connections idle in transaction, all active, or waiting in the queue).
  2. Capsule 03 (SQLAlchemy pool tuning): you raise pool_size and max_overflow with criteria — but you detect that pool_pre_ping is off and the corporate firewall is cutting idle connections.
  3. Capsule 04 (asyncpg specific): you confirm that your AsyncEngine is created correctly and that there's no leak from a missing await session.commit().
  4. Capsule 05 (PgBouncer fundamentals): you realize that with 4 FastAPI instances × pool_size=10, you have 40 connections — but PostgreSQL only handles 100 total (with shared_buffers, autovacuum, replication = ~70 available). PgBouncer is necessary.
  5. Capsule 06 (asyncpg + PgBouncer gotchas): you configure PgBouncer in transaction mode and apply statement_cache_size=0 to avoid the prepared statements bug.
  6. Capsule 07 (sizing and monitoring): you size the pool with the correct formula for async and configure alerts on SHOW POOLS.
  7. Capsule 08 (project): you apply everything to the bookstore and measure before/after. The goal: go from saturating at 50 RPS to sustaining 200 RPS without errors.

After the next marketing promo, the incident doesn't repeat. That's what you're going to be able to do.

Module map

CapsuleTopicWhat you'll learn
01Module introductionYou are here. Map, expectations, scenario.
02Connection pool fundamentalsWhy pooling exists, the lifecycle of a connection, client pool vs PostgreSQL's max_connections.
03SQLAlchemy pool tuningpool_size, max_overflow, pool_pre_ping, pool_recycle, pool_timeout: what each one does and how to choose values.
04asyncpg and AsyncEnginePool patterns for async FastAPI, asyncpg's statement cache, correct configuration of create_async_engine.
05PgBouncer fundamentalsArchitecture, the 3 modes (session/transaction/statement), decision matrix with criteria.
06PgBouncer + asyncpg gotchasThe prepared statements gotcha, statement_cache_size=0, other features that break in transaction mode.
07Pool sizing formulas and monitoringThe HikariCP formula and why it doesn't apply to async, observability with SHOW POOLS and pg_stat_database.
08Project: tuning the bookstore's poolComplete setup with docker-compose, before/after benchmark, demonstration of the 3 modes.

Narrative flow: first you understand why there's a pool and how SQLAlchemy's works (02-04). Then you understand why SQLAlchemy alone doesn't scale and you need PgBouncer (05-06). Finally you close with sizing, observability, and an integrative project (07-08).

Connection with the capstone project

The guide's final project (module 8) is a bookstore API with five performance problems deliberately planted. One of those problems is: "an untuned pool that saturates at 50 RPS". This module is the one that gives you the tools to solve it.

In the module 8 project you're going to:

  1. Reproduce the "connection pool exhausted" with wrk launching 80 RPS against the base API.
  2. Apply PgBouncer in transaction mode with the configuration you learn here.
  3. Tune the SQLAlchemy pool with the asyncpg gotchas.
  4. Validate that the API now sustains 200 RPS without errors.
  5. Report the quantified improvement in BENCHMARKS.md.

This module's mini-project (capsule 08) does exactly that but at a smaller scale, so you arrive at the final project with the pattern already internalized.

What is NOT covered in this module

  • EXPLAIN, indexing, N+1: already covered in modules 2-4.
  • pg_stat_statements and auto_explain: module 5.
  • Autovacuum, statistics, planner internals: module 7.
  • Caching with Redis as an alternative to pooling: it belongs to guide #10.
  • Read replicas and load balancing between primary/replica: it belongs to the "Database Scaling Patterns" guide (future). PgBouncer here is covered only pointing at a primary PostgreSQL.
  • PgPool-II as an alternative to PgBouncer: PgBouncer is the de facto standard for async apps. PgPool-II has use cases (replication, load balancing) that aren't the scope of this guide.
  • RDS Proxy, Supabase Pooler, Neon Pooler as managed services: they use PgBouncer internally, so what you learn applies directly. We don't cover them separately.
  • PostgreSQL's max_connections from the server-tuning perspective: we assume it as a given (typically 100). Server-side tuning belongs to pure DBA guides.

Traps to avoid while taking the module

1. "I already know pooling, I saw it in guide #8." Guide #8 mentioned pool_size and max_overflow. That's 5% of the module. Here you're going to see the connection lifecycle, the three PgBouncer modes, async-specific gotchas, and the difference between a client pool and an external pool. If you skip capsules 02 and 05 because "you already know them", the rest of the module won't make sense.

2. "I'll use PgBouncer transaction mode because it's the one everyone recommends." It's the correct recommendation for async FastAPI, but it breaks prepared statements, LISTEN/NOTIFY, persistent SET LOCAL, and some advisory locks. If your app uses any of those features, you find out in production. Capsule 05 gives you the decision matrix; capsule 06 teaches you the specific asyncpg workaround.

3. "I'll copy pool_size=20 from the first post I find." The correct formula depends on your PgBouncer mode, the number of FastAPI instances, PostgreSQL's max_connections, and the expected load. Copying numbers without understanding the tradeoffs is why in production you have errors that "don't appear locally". Capsule 07 teaches you to calculate it.

4. "If I enable pool_pre_ping, everything is solved." pool_pre_ping adds a SELECT 1 before each query. It solves one problem (dead connections behind proxies/firewalls) but adds latency (1 round-trip per query). In low-latency apps, the cost matters. Capsule 03 explains when to enable it and when not to.

5. Assuming that "more connections = better." More connections = more memory in PostgreSQL + more context switching + more contention on internal locks. Going past the optimum worsens performance. Capsule 07 explains the "saturation point" and how to measure it.

Self-assessment questions

Before starting this module, can you answer these questions?

  • What exactly does pool_size=10 do in SQLAlchemy?
  • What happens if your app opens 11 simultaneous connections with pool_size=10 and max_overflow=0?
  • What is the default value of max_connections in PostgreSQL?
  • What is pg_stat_activity and what is it for?
  • If your FastAPI API runs with 4 uvicorn workers, how is the pool distributed?
  • What is a prepared statement and why does pg_stat_statements show them parameterized?

If you hesitate on more than two, don't worry — this is exactly what we explain in the next capsules. If you hesitate on all of them, consider reviewing the connection pooling section of guide #8 before continuing (it's base context that we're going to deepen).

Success criteria

By the end of the module, you'll know you succeeded if:

  • You can explain to a colleague the difference between the three PgBouncer modes in less than 2 minutes, including what features break in each one.
  • You know why statement_cache_size=0 is mandatory in asyncpg + PgBouncer transaction mode, and you can show the exact code.
  • You can diagnose a "connection pool exhausted" following a 3-4 step flow without googling.
  • Your SQLAlchemy pool in the bookstore is sized with explicit criteria, not copied.
  • In the module's mini-project (capsule 08) your setup goes from saturating to sustaining at least 4x more RPS.

We start in the next capsule

We start with capsule 02: connection pool fundamentals. You're going to understand why opening a connection to PostgreSQL is expensive, what the lifecycle of a connection is, and what exactly happens when your app requests a connection and the pool has none available. It's the conceptual foundation you need before tuning any parameter.

Before moving on, make sure you have PostgreSQL 16 running locally (or in Docker) with your FastAPI app connected via SQLAlchemy 2.0 async. If you followed the guide's setup up to here, you already have it.

Module resources

  1. PgBouncer official documentation — the canonical reference for the 3 modes and their configuration.
  2. SQLAlchemy 2.0 — Connection Pooling — the complete official reference for pools.
  3. asyncpg — Connection pools — asyncpg's native pool and differences with SQLAlchemy.
  4. Brandur Leach — "Postgres connection pooling" — an architectural view of why pooling exists.
  5. HikariCP — "About Pool Sizing" — the classic formula and why "more connections" is not better.
  6. Supabase — "How PgBouncer fits into our stack" — a real case of PgBouncer at scale with the tradeoffs explained.

Module 6 — Database Performance & Query Tuning Guide

← PreviousProject: profiling the bookstore in productionNext →Connection pool fundamentals