Module 6 project: tuning the bookstore's pool

What are you going to build and why?

You're going to take a version of the bookstore with a deliberately misconfigured pool (that saturates at 50 sustained RPS with connection pool exhausted errors) and bring it up to sustaining at least 200 RPS without errors, measuring the quantified before/after improvement.

The project integrates the whole module:

• Capsule 02 (fundamentals): you're going to diagnose the problem with pg_stat_activity and SHOW POOLS.
• Capsule 03 (SQLAlchemy tuning): you're going to tune pool_size, max_overflow, pool_pre_ping, pool_recycle.
• Capsule 04 (asyncpg + AsyncEngine): you're going to apply the canonical async FastAPI patterns.
• Capsule 05 (PgBouncer fundamentals): you're going to add PgBouncer in transaction mode.
• Capsule 06 (gotchas): you're going to apply statement_cache_size=0 to avoid the prepared statements bug.
• Capsule 07 (sizing and monitoring): you're going to size with criteria and verify with SHOW POOLS during the benchmark.

By the end, you'll have:

1. A docker-compose.yml with PostgreSQL + PgBouncer + FastAPI working.
2. Your FastAPI app tuned according to the module's recommended configuration.
3. Before/after benchmark results in a table with concrete numbers.
4. A brief analysis of which intervention moved the needle most (typically: introducing PgBouncer and applying statement_cache_size=0).

This is the module 6 mini-project. The final capstone project in module 8 will apply techniques from the whole guide (not just this module) on the bookstore, including this pool tuning as one of the five measured optimizations.

---

Project objective

By completing this project:

• You'll have reproduced a real saturated-pool scenario with wrk launching sustained load.
• You'll have applied all the module's techniques in a coherent order.
• You'll have a quantitative benchmark that demonstrates a ≥ 4x improvement in sustained throughput without errors.
• You'll have documented your decisions in a BENCHMARKS.md that serves as a portfolio artifact.

---

How it fits into what you learned

Module concept	Where it's used in the project
Capsule 02: pool fundamentals	Initial diagnosis with pg_stat_activity and SHOW POOLS.
Capsule 03: SQLAlchemy tuning	Configure pool_size, max_overflow, pool_pre_ping, pool_recycle.
Capsule 04: asyncpg + AsyncEngine	The Depends(get_db) pattern, expire_on_commit=False, lifespan handler.
Capsule 05: PgBouncer fundamentals	PgBouncer setup in transaction mode, SHOW POOLS for diagnosis.
Capsule 06: gotchas	Apply statement_cache_size=0 to avoid InvalidSQLStatementNameError.
Capsule 07: sizing	Size pool_size empirically, monitor with SHOW POOLS.

Think of the project as an ordered intervention: first you measure the problem, then you apply the cheapest fix (tuning the SQLAlchemy pool), then the most structural one (introducing PgBouncer), then the most subtle one (statement cache). At each step, you measure and compare.

---

Technical specifications

Stack

• Database: PostgreSQL 16
• Connection pooler: PgBouncer 1.22+
• App: FastAPI 0.110+
• ORM: SQLAlchemy 2.0+
• Driver: asyncpg 0.29+
• Benchmarking: wrk
• Orchestration: Docker Compose

Initial setup

You need to have Docker and wrk installed.

# wrk on macOS
brew install wrk

# wrk on Linux
sudo apt-get install wrk

# Create the project directory
mkdir bookstore-pool-project
cd bookstore-pool-project

---

Project structure

bookstore-pool-project/
├── docker-compose.yml
├── pgbouncer/
│   └── pgbouncer.ini  (optional, you can use env vars from docker-compose)
├── app/
│   ├── __init__.py
│   ├── main.py
│   ├── db.py
│   ├── models.py
│   └── routes.py
├── seed.sql
├── benchmarks/
│   ├── run_bench.sh
│   └── monitor_pools.sh
├── BENCHMARKS.md  ← deliverable artifact
├── Dockerfile
└── requirements.txt

---

Required features

1. Base setup with a reproducible problem

A docker-compose.yml that brings up:

• PostgreSQL 16 with max_connections=100.
• (Initially without PgBouncer.)
• A FastAPI app with SQLAlchemy + asyncpg, misconfigured on purpose (pool_size=5, no pool_pre_ping, no pool_recycle).
• A schema with a books table and at least 10,000 seeded rows.
• A GET /books/{id} endpoint that runs SELECT * FROM books WHERE id = $1.

Validation: be able to reproduce the problem with:

wrk -t4 -c100 -d30s http://localhost:8000/books/1

Expected: saturated pool errors, throughput limited to ~50 RPS, p99 latency > 500ms.

2. Configure PgBouncer in docker-compose

Add PgBouncer to the docker-compose.yml:

• In transaction mode (POOL_MODE: transaction).
• DEFAULT_POOL_SIZE: 25, MAX_CLIENT_CONN: 200.
• Exposed on port 6432.
• The app points at PgBouncer (not at PG directly).

Validation: be able to do psql -h localhost -p 6432 -U bookstore and connect.

3. Tune app/db.py with the recommended configuration

Apply all the module's techniques:

• pool_size=20, max_overflow=0 (PgBouncer absorbs).
• pool_timeout=10, pool_pre_ping=True, pool_recycle=3600.
• connect_args with statement_cache_size=0 (capsule 06 gotcha).
• application_name in server_settings to identify in pg_stat_activity.
• expire_on_commit=False in async_sessionmaker.
• Depends(get_db) with session-per-request.
• A lifespan handler with await engine.dispose().

Validation: the app boots without errors and serves requests correctly.

4. Before/after benchmarking with concrete numbers

A run_bench.sh script that:

• Runs wrk -t4 -c100 -d60s against /books/{id} with random IDs.
• Reports: throughput, p50, p95, p99, errors.
• Also logs SHOW POOLS every 5 seconds during the benchmark.

Expected results (order of magnitude):

Configuration	Throughput	p50	p95	p99	Errors
Before (misconfigured)	~50 RPS	200ms	1500ms	5000ms	Many pool exhausted
After (tuned)	≥200 RPS	<30ms	<100ms	<300ms	0

5. Document in BENCHMARKS.md

A markdown with:

• The setup described.
• A before/after table.
• An analysis of which intervention moved the needle most.
• Lessons learned.

---

Validations and error handling

What must be validated

• The app responds 200 OK on /books/1 after the setup.
• PgBouncer appears running in docker compose ps.
• PgBouncer admin accepts a connection: psql -h localhost -p 6432 -U bookstore pgbouncer -c "SHOW POOLS".
• The app connected via PgBouncer (verify with application_name in pg_stat_activity).
• Benchmark without 5xx errors at the target throughput.

Errors that must be handled

• connection pool exhausted (before the fix): document that it's the expected thing in the "before" phase. It's not a project bug.
• InvalidSQLStatementNameError (without statement_cache_size=0): demonstrate that it appears without the fix and disappears with it. It's part of the learning.
• The app doesn't start: check the logs (docker compose logs app). Typically: a misconfigured DATABASE_URL or PgBouncer not ready yet.

---

Minimal implementation example

This is the functional base skeleton. You extend it with the seed, monitor, etc. logic.

requirements.txt

fastapi==0.110.0
uvicorn[standard]==0.27.0
sqlalchemy[asyncio]==2.0.25
asyncpg==0.29.0
pydantic==2.5.0

Dockerfile

FROM python:3.11-slim

WORKDIR /app

COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

COPY app/ ./app/

CMD ["uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "8000"]

docker-compose.yml (final version with everything)

version: "3.9"

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: bookstore
      POSTGRES_PASSWORD: bookstore
      POSTGRES_DB: bookstore
    command:
      - postgres
      - -c
      - max_connections=100
      - -c
      - shared_buffers=256MB
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./seed.sql:/docker-entrypoint-initdb.d/seed.sql
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U bookstore"]
      interval: 5s
      timeout: 3s
      retries: 5

  pgbouncer:
    image: edoburu/pgbouncer:1.22.1
    environment:
      DB_USER: bookstore
      DB_PASSWORD: bookstore
      DB_HOST: postgres
      DB_PORT: "5432"
      DB_NAME: bookstore
      POOL_MODE: transaction
      MAX_CLIENT_CONN: "200"
      DEFAULT_POOL_SIZE: "25"
      RESERVE_POOL_SIZE: "5"
      AUTH_TYPE: scram-sha-256
      ADMIN_USERS: bookstore
      STATS_USERS: bookstore
    ports:
      - "6432:5432"
    depends_on:
      postgres:
        condition: service_healthy

  app:
    build: .
    environment:
      DATABASE_URL: "postgresql+asyncpg://bookstore:bookstore@pgbouncer:5432/bookstore"
    ports:
      - "8000:8000"
    depends_on:
      - pgbouncer

volumes:
  postgres_data:

seed.sql

CREATE TABLE IF NOT EXISTS books (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    year INT,
    pages INT
);

-- Seed 10,000 books
INSERT INTO books (title, author, year, pages)
SELECT
    'Book ' || gs,
    'Author ' || (gs % 100),
    1900 + (gs % 125),
    100 + (gs % 800)
FROM generate_series(1, 10000) AS gs;

CREATE INDEX idx_books_author ON books(author);

app/db.py (FINAL tuned version)

import os
from contextlib import asynccontextmanager
from typing import AsyncGenerator

from fastapi import FastAPI
from sqlalchemy.ext.asyncio import (
    AsyncSession,
    async_sessionmaker,
    create_async_engine,
)

DATABASE_URL = os.environ.get(
    "DATABASE_URL",
    "postgresql+asyncpg://bookstore:bookstore@localhost:6432/bookstore",
)

engine = create_async_engine(
    DATABASE_URL,
    # Client pool (capsule 03)
    pool_size=20,
    max_overflow=0,           # PgBouncer absorbs peaks
    pool_timeout=10,
    pool_pre_ping=True,
    pool_recycle=3600,
    # Async / asyncpg (capsules 04 + 06)
    echo=False,
    connect_args={
        "statement_cache_size": 0,             # FIX for PgBouncer transaction mode
        "prepared_statement_cache_size": 0,    # defensive
        "server_settings": {
            "application_name": "bookstore-api",
            "statement_timeout": "10000",
        },
    },
)

SessionLocal = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
    autoflush=False,
)


async def get_db() -> AsyncGenerator[AsyncSession, None]:
    async with SessionLocal() as session:
        try:
            yield session
        finally:
            await session.close()


@asynccontextmanager
async def lifespan(app: FastAPI):
    yield
    await engine.dispose()

app/models.py

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Book(Base):
    __tablename__ = "books"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    author: Mapped[str]
    year: Mapped[int | None]
    pages: Mapped[int | None]

app/routes.py

from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.db import get_db
from app.models import Book

router = APIRouter()


@router.get("/health")
async def health():
    return {"status": "ok"}


@router.get("/books/{book_id}")
async def get_book(book_id: int, db: AsyncSession = Depends(get_db)):
    book = await db.scalar(select(Book).where(Book.id == book_id))
    if not book:
        raise HTTPException(404, "Book not found")
    return {
        "id": book.id,
        "title": book.title,
        "author": book.author,
        "year": book.year,
        "pages": book.pages,
    }

app/main.py

from fastapi import FastAPI
from app.db import lifespan
from app.routes import router

app = FastAPI(lifespan=lifespan)
app.include_router(router)

benchmarks/run_bench.sh

#!/bin/bash
# benchmarks/run_bench.sh — runs wrk with a PgBouncer monitor in parallel

set -e
DURATION=60s
CONCURRENCY=100
THREADS=4
TARGET="http://localhost:8000/books/1"

echo "=== Benchmark: c=$CONCURRENCY t=$THREADS d=$DURATION ==="
echo "Target: $TARGET"
echo ""

# Launch the monitor in the background
./benchmarks/monitor_pools.sh > /tmp/pool_monitoring.log 2>&1 &
MONITOR_PID=$!

# Wait 2s for the monitor to capture a baseline
sleep 2

# Run wrk
wrk -t$THREADS -c$CONCURRENCY -d$DURATION --latency $TARGET | tee /tmp/wrk_output.txt

# Stop the monitor
kill $MONITOR_PID 2>/dev/null || true

echo ""
echo "=== Pool monitoring ==="
cat /tmp/pool_monitoring.log

echo ""
echo "=== Connections in PostgreSQL during the benchmark ==="
docker compose exec -T postgres psql -U bookstore -d bookstore -c "
SELECT application_name, state, count(*)
FROM pg_stat_activity
WHERE datname = 'bookstore'
GROUP BY application_name, state
ORDER BY count DESC;
"

benchmarks/monitor_pools.sh

#!/bin/bash
# benchmarks/monitor_pools.sh — logs SHOW POOLS every 5s

while true; do
    TS=$(date +%H:%M:%S)
    OUTPUT=$(PGPASSWORD=bookstore psql -h localhost -p 6432 -U bookstore pgbouncer -t -A -F, \
        -c "SELECT database, cl_active, cl_waiting, sv_active, sv_idle, maxwait_us FROM pgbouncer.pools WHERE database = 'bookstore';" 2>/dev/null)
    echo "$TS,$OUTPUT"
    sleep 5
done

---

Evaluation rubric (self-check)

Total: 100 points. Passing: ≥70 points.

Setup and reproducibility (25 points)

• (5 pts) docker compose up -d brings up postgres + pgbouncer + app without errors.
• (5 pts) The /books/1 endpoint responds 200 OK.
• (5 pts) The books table has ≥10,000 seeded rows.
• (5 pts) PgBouncer is accessible from port 6432 (psql -h localhost -p 6432 -U bookstore pgbouncer).
• (5 pts) application_name = bookstore-api appears in pg_stat_activity.

Correct configuration (30 points)

• (5 pts) app/db.py has pool_size=20, max_overflow=0 (PgBouncer multiplexes).
• (5 pts) pool_pre_ping=True and pool_recycle=3600 configured.
• (5 pts) connect_args["statement_cache_size"] == 0 (capsule 06 gotcha).
• (5 pts) expire_on_commit=False in async_sessionmaker.
• (5 pts) Depends(get_db) with async with SessionLocal() correct.
• (5 pts) Lifespan handler with await engine.dispose() on shutdown.

Benchmarking and measurable improvement (30 points)

• (5 pts) You ran the "before" benchmark (with the bad configuration) and documented the results.
• (5 pts) You ran the "after" benchmark (tuned configuration) and documented the results.
• (10 pts) The "after" throughput is ≥4x the "before" in sustained RPS without errors.
• (5 pts) The "after" p95 latency < 100ms.
• (5 pts) 0 pool exhausted or InvalidSQLStatementNameError errors in the "after" benchmark.

Documentation (15 points)

• (5 pts) BENCHMARKS.md exists with a before/after table.
• (5 pts) An analysis of which intervention moved the needle most (1-2 paragraphs).
• (5 pts) SHOW POOLS output captured during the "after" benchmark.

Extra credit (optional, up to +20 points)

• (+5 pts) Compare transaction mode vs session mode with a side-by-side benchmark.
• (+5 pts) Demonstrate that WITHOUT statement_cache_size=0 intermittent errors appear (capture them in the logs).
• (+5 pts) Set up prometheus-pgbouncer-exporter with a basic cl_waiting dashboard.
• (+5 pts) Size empirically with a benchmark of 5 different pool_size values.

---

Common mistakes in this project

Mistake 1: the app can't connect to pgbouncer

Symptom: app logs: connection refused or could not translate host name.

Why it happens: PgBouncer hadn't come up yet when the app tried to connect. Docker Compose depends_on only waits for the container to be up, not for it to be ready.

How to fix it: add a healthcheck on pgbouncer or retry logic in the app:

# Option 1: retry in the lifespan
async def lifespan(app: FastAPI):
    for attempt in range(10):
        try:
            async with engine.connect() as conn:
                await conn.execute(text("SELECT 1"))
            break
        except Exception:
            await asyncio.sleep(1)
    yield
    await engine.dispose()

Mistake 2: InvalidSQLStatementNameError during the benchmark

Symptom: intermittent 500 errors during sustained load. Logs show prepared statement "__asyncpg_stmt_xxx__" does not exist.

Why it happens: you forgot statement_cache_size=0 in connect_args. It's exactly the capsule 06 bug.

How to distinguish: a specific error, appears intermittently under load, not locally with low throughput.

How to fix it: add connect_args={"statement_cache_size": 0} in create_async_engine. Restart the app.

Mistake 3: the "after" throughput doesn't improve

Symptom: after applying all the techniques, throughput is still limited. SHOW POOLS shows cl_waiting=0 and low sv_active.

Why it happens: the bottleneck isn't the pool — it's something else (a saturated client CPU, very fast queries that are already at the theoretical limit).

How to distinguish: monitor the app container's CPU. If it's at 90%, the bottleneck is the client, not the pool.

How to fix it: this project is designed for a pool bottleneck. If your hardware is very powerful, the "before" problem may not reproduce with pool_size=5. Lower it to pool_size=2 to force saturation, or increase the concurrency in wrk (-c 500).

Mistake 4: PgBouncer auth fails

Symptom: FATAL: password authentication failed for user "bookstore".

Why it happens: a mismatch between PgBouncer's AUTH_TYPE and PostgreSQL's authentication method. PG 16 default is scram-sha-256, PgBouncer must use the same.

How to distinguish: an error in PgBouncer's logs when trying to connect to PG.

How to fix it: ensure AUTH_TYPE: scram-sha-256 in PgBouncer (compatible with the PG 16 default).

Mistake 5: you forgot application_name

Symptom: during debugging you can't distinguish your connections from others.

Why it happens: application_name not configured in connect_args.server_settings.

How to distinguish: pg_stat_activity shows generic values.

How to fix it: add "application_name": "bookstore-api" in server_settings. Restart the app.

Mistake 6: over-sized pool

Symptom: you raised pool_size to 200 expecting better throughput. PostgreSQL responds with FATAL: too many connections.

Why it happens: pool_size × num_instances > max_connections. Without PgBouncer, this is the absolute cap.

How to distinguish: an error in the logs when you try to open more connections than allowed.

How to fix it: with PgBouncer, this problem shouldn't appear (the client pool goes to PgBouncer, not to PG directly). If you see it, verify that your app really points at PgBouncer (port 6432), not at PG directly (5432).

---

What to do if you get stuck

• If the setup doesn't work → check docker compose logs <service>. Most problems are about startup order or auth.
• If a module technique isn't clear → go back to the corresponding capsule. The rubric links each criterion to the capsule that teaches it.
• If the numbers don't improve → check SHOW POOLS during the benchmark. If cl_waiting > 0 constantly, the pool is the bottleneck. If not, it's something else.
• If InvalidSQLStatementNameError appears → it's capsule 06. statement_cache_size=0.
• If the app responds slowly even with a tuned pool → the bottleneck may be disk I/O or the PG's CPU. This is covered in later modules and is not the scope of this project.

---

Resources for the project

1. PgBouncer documentation — usage — the administrative reference.
2. SQLAlchemy 2.0 — async docs — canonical patterns.
3. asyncpg — connection pools — the official driver.
4. wrk — benchmarking tool — the tool used to measure.
5. edoburu/pgbouncer Docker image — the docker-compose image.
6. Module capsule 06 (gotchas) — a refresher on the statement_cache_size=0 fix.
7. Module capsule 07 (sizing) — a refresher on monitoring.

---

What comes next

What you built here is a prerequisite for the final capstone project in module 8. In module 7 you're going to learn about statistics, autovacuum, and planner internals — topics that live inside PostgreSQL (not at the app/pool level). In module 8 you're going to take a complete version of the bookstore with five different problems (including a misconfigured pool, N+1, a large OFFSET, a slow COUNT, a missing GIN index) and you're going to apply techniques from the whole guide to optimize them, measuring before/after for each one.

The pool tuning from this project will be one of those five problems. When you reach module 8, you'll have this component already internalized: PgBouncer setup, configuring SQLAlchemy correctly, validating with SHOW POOLS, measuring with wrk. The difference there will be the integration with the other four problems and a more realistic project in scale.

Before moving on to module 7, make sure you:

• Have your bookstore-pool-project running and the benchmark documented in BENCHMARKS.md.
• Can explain to a colleague why each pool parameter has the value it has.
• Know that the statement_cache_size=0 fix is the most important thing when adopting PgBouncer transaction mode.
• Are able to read SHOW POOLS and pg_stat_activity without googling the columns.

---

Suggested BENCHMARKS.md template

# Bookstore benchmark — pool tuning

## Setup

- PostgreSQL 16, Docker container, max_connections=100.
- PgBouncer 1.22, transaction mode, default_pool_size=25.
- FastAPI with SQLAlchemy 2.0 + asyncpg.
- Benchmark with wrk: 4 threads, 100 connections, 60 seconds.
- Measured endpoint: `GET /books/1`.

## Results

### Before (bad configuration)

- pool_size=5, no pool_pre_ping, no pool_recycle, no PgBouncer.

| Metric | Value |
|---------|-------|
| Throughput | 47 RPS |
| p50 latency | 215ms |
| p95 latency | 1,650ms |
| p99 latency | 5,200ms |
| Errors | 1,234 (TimeoutError) |

### After (module's recommended configuration)

- pool_size=20, max_overflow=0, pool_pre_ping=True, pool_recycle=3600.
- statement_cache_size=0, expire_on_commit=False.
- PgBouncer in transaction mode, default_pool_size=25.

| Metric | Value |
|---------|-------|
| Throughput | 2,150 RPS |
| p50 latency | 18ms |
| p95 latency | 52ms |
| p99 latency | 115ms |
| Errors | 0 |

### Quantified improvement

- **Throughput: ~46x better** (47 → 2,150 RPS).
- **p95 latency: ~30x better** (1,650ms → 52ms).
- **Errors: eliminated** (1,234 → 0).

## Analysis

The intervention that moved the needle most was **introducing PgBouncer in transaction mode** combined with `statement_cache_size=0`. Without PgBouncer, raising `pool_size` hits PostgreSQL's `max_connections` cap (100). With PgBouncer, we can have `pool_size=20` per FastAPI instance without saturating PG.

`pool_pre_ping=True` didn't affect throughput (negligible overhead) but improved reliability: 0 intermittent errors after the fix.

`statement_cache_size=0` was mandatory: without it, during the benchmark intermittent `InvalidSQLStatementNameError` errors appeared that, without understanding the context, would be extremely difficult to diagnose.

## Lessons learned

1. **Pooling is the #1 problem when scaling.** With the default configuration, the app saturated at 50 RPS. With correct tuning, it sustains 2,000+ RPS — 40x more capacity without adding hardware.
2. **`statement_cache_size=0` is invisible until it breaks.** Without having read capsule 06, I would have debugged for days.
3. **PgBouncer metrics (`SHOW POOLS`) are indispensable.** They let you see in real time whether the bottleneck is the client pool or PgBouncer's.
4. **Three levels of pool matter:** SQLAlchemy → PgBouncer → PostgreSQL. Each with its parameter and its specific error.

---

Module 6 — Database Performance & Query Tuning Guide