Module project: the Bookstore API baseline

What are you going to build and why?

You're going to produce the performance baseline of the guide's capstone project: the Bookstore API, a simplified FastAPI API with performance problems planted on purpose. Your deliverable is a reproducible BENCHMARKS.md that documents how the API behaves today — before you touch a single line to optimize it.

This baseline is the line against which all the optimizations from modules 2 through 7 are compared. Every time you apply a technique (eager loading, composite index, OFFSET refactor, pool tuning), you come back to this BENCHMARKS.md and add an "After Module X" section — seeing concretely how much you improved.

Why does this project integrate the whole module?

• Mindset (capsules 01-02): you'll resist the temptation to "while I'm here, let me add an index".
• Reproducible baselines (capsule 03): you'll document context, methodology, multiple runs, warmup.
• pgbench (capsule 04): you'll measure the problematic queries in isolation to confirm whether the bottleneck lives in the app or in the DB.
• wrk (capsule 05): you'll produce the serious HTTP benchmark, percentiles and throughput.
• locust (capsule 06): you'll simulate a realistic mix of users with flows.
• Reporting (capsule 07): you'll deliver a BENCHMARKS.md that any reviewer would accept without objection.

By the end, you'll have a public repo (or one in your portfolio) with code + BENCHMARKS.md that demonstrates mastery of the full measure → document → communicate flow. That repo is presentable in senior interviews.

---

Project objective

By completing this project you will have:

• ✅ Brought up the simplified Bookstore API (3-4 endpoints with known performance problems) running locally with representative data
• ✅ Produced version-controlled measurement scripts: bench/pgbench-baseline.sh, bench/wrk-baseline.sh, bench/locustfile.py
• ✅ Generated the BENCHMARKS.md file with the complete "Baseline (Module 1)" section, following the canonical template from capsule 07
• ✅ Identified (without fixing) which endpoint suffers from N+1, which suffers from a large OFFSET, and what hypothesis you have for each
• ✅ Resisted, throughout the whole guide, the temptation to "while I'm here, let me optimize": module 1 is measurement only

---

How it fits with what you learned

Module concept	Where it's used in the project
The "no optimization without measurement" mindset (capsule 01)	The whole attitude — you only measure, you don't tune
Latency vs throughput (capsule 02)	Report p50/p95/p99 + sustained RPS
Percentiles, not averages (capsule 02)	The baseline table reports percentiles, NOT averages
Reproducible baselines (capsule 03)	The "Environment context" + "Methodology" sections of the BENCHMARKS.md
pgbench for pure queries (capsule 04)	The bench/pgbench-baseline.sh script that measures 1-2 key queries in isolation
wrk for simple HTTP (capsule 05)	The bench/wrk-baseline.sh script that measures each endpoint individually
locust for flows (capsule 06)	bench/locustfile.py with 2 user types
Honest reporting (capsule 07)	BENCHMARKS.md applies the canonical template

Think of it as a "forensic investigation": you arrive at an API that's in bad shape and your job is to collect evidence, not deliver a verdict. The verdict (what to tune, how) comes in the following modules.

---

Technical specifications

Stack

• Language: Python 3.11+
• Framework: FastAPI 0.110+
• ORM: SQLAlchemy 2.0+ async
• Driver: asyncpg
• DB: PostgreSQL 16+
• Measurement tools: pgbench 16+, wrk 4.2+, locust 2.27+

Repo structure

bookstore-baseline/
├── README.md                    # what it is and how to run it
├── BENCHMARKS.md                # your main deliverable
├── pyproject.toml               # dependencies
├── docker-compose.yml           # PostgreSQL in a container
├── app/
│   ├── main.py                  # FastAPI endpoints
│   ├── models.py                # SQLAlchemy models
│   ├── db.py                    # DB and pool config
│   └── seed.py                  # script to fill in data
└── bench/
    ├── pgbench-baseline.sh      # benchmark of isolated queries
    ├── pgbench-queries.sql      # queries that pgbench runs
    ├── wrk-baseline.sh          # HTTP benchmark per endpoint
    └── locustfile.py            # benchmark with mixed scenarios

---

Initial setup

1. Create the project structure

mkdir bookstore-baseline
cd bookstore-baseline
git init

mkdir -p app bench

python -m venv venv
source venv/bin/activate

# App dependencies
pip install fastapi uvicorn sqlalchemy[asyncio] asyncpg

# Measurement dependencies
pip install locust httpx numpy

2. Bring up PostgreSQL in Docker

# docker-compose.yml
version: "3.9"
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_USER: bookstore
      POSTGRES_PASSWORD: bookstore
      POSTGRES_DB: bookstore
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U bookstore"]
      interval: 5s
      timeout: 5s
      retries: 5

docker compose up -d
docker compose ps  # check healthy

3. Verify wrk, pgbench, locust are installed

wrk --version       # wrk 4.2.0+
pgbench --version   # pgbench (PostgreSQL) 16.x
locust --version    # locust 2.27.0+

If any is missing, review capsules 04/05/06.

---

Required features

App: 4 endpoints with problems planted on purpose

app/db.py — base config

# app/db.py
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from sqlalchemy.orm import DeclarativeBase

DATABASE_URL = "postgresql+asyncpg://bookstore:bookstore@localhost:5432/bookstore"

engine = create_async_engine(
    DATABASE_URL,
    pool_size=20,
    max_overflow=10,
    echo=False,
)

AsyncSessionLocal = async_sessionmaker(engine, expire_on_commit=False)


class Base(DeclarativeBase):
    pass

app/models.py — models

# app/models.py
from sqlalchemy import ForeignKey, String, Integer, Text
from sqlalchemy.orm import Mapped, mapped_column, relationship

from .db import Base


class Author(Base):
    __tablename__ = "authors"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(200))

    books: Mapped[list["Book"]] = relationship(back_populates="author")


class Book(Base):
    __tablename__ = "books"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(300))
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))
    published_year: Mapped[int] = mapped_column(Integer)

    author: Mapped["Author"] = relationship(back_populates="books")
    reviews: Mapped[list["Review"]] = relationship(back_populates="book")


class Review(Base):
    __tablename__ = "reviews"

    id: Mapped[int] = mapped_column(primary_key=True)
    book_id: Mapped[int] = mapped_column(ForeignKey("books.id"))
    rating: Mapped[int] = mapped_column(Integer)
    body: Mapped[str] = mapped_column(Text)

    book: Mapped["Book"] = relationship(back_populates="reviews")


class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    book_id: Mapped[int] = mapped_column(ForeignKey("books.id"))
    quantity: Mapped[int] = mapped_column(Integer)

app/main.py — endpoints

# app/main.py
from fastapi import FastAPI, HTTPException, Query
from sqlalchemy import select

from .db import AsyncSessionLocal, engine, Base
from .models import Author, Book, Order, Review

app = FastAPI(title="Bookstore (intentionally broken)")


@app.on_event("startup")
async def startup():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)


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


@app.get("/books")
async def list_books():
    """
    Endpoint 1: simple listing of books.
    Planted problem: none significant. Serves as a 'fast' baseline.
    """
    async with AsyncSessionLocal() as session:
        result = await session.execute(select(Book).limit(50))
        books = result.scalars().all()
        return [
            {"id": b.id, "title": b.title, "year": b.published_year}
            for b in books
        ]


@app.get("/books-with-author")
async def list_books_with_author(author_name: str = Query(...)):
    """
    Endpoint 2: lists an author's books AND their reviews.
    Planted problem: OBVIOUS N+1.
    For each book we fire 1 query for reviews (lazy loading).
    If the author has 50 books, that's 51 queries per request.
    """
    async with AsyncSessionLocal() as session:
        author_q = select(Author).where(Author.name == author_name)
        author = (await session.execute(author_q)).scalar_one_or_none()
        if not author:
            raise HTTPException(404, "Author not found")

        # Load the author's books
        books_q = select(Book).where(Book.author_id == author.id)
        books = (await session.execute(books_q)).scalars().all()

        # N+1: for each book, a separate query for its reviews
        result = []
        for book in books:
            reviews_q = select(Review).where(Review.book_id == book.id)
            reviews = (await session.execute(reviews_q)).scalars().all()
            result.append({
                "id": book.id,
                "title": book.title,
                "review_count": len(reviews),
                "avg_rating": (
                    sum(r.rating for r in reviews) / len(reviews)
                    if reviews else None
                ),
            })

        return {"author": author.name, "books": result}


@app.get("/orders")
async def list_orders(page: int = Query(1, ge=1), page_size: int = Query(20)):
    """
    Endpoint 3: pagination with OFFSET.
    Planted problem: on deep pages (page=1000+), OFFSET is O(n).
    """
    offset = (page - 1) * page_size
    async with AsyncSessionLocal() as session:
        q = select(Order).order_by(Order.id).limit(page_size).offset(offset)
        result = await session.execute(q)
        orders = result.scalars().all()
        return [
            {"id": o.id, "book_id": o.book_id, "quantity": o.quantity}
            for o in orders
        ]


@app.get("/books/{book_id}")
async def get_book(book_id: int):
    """
    Endpoint 4: simple detail by PK.
    Planted problem: none; serves to compare against the problematic ones.
    """
    async with AsyncSessionLocal() as session:
        book = await session.get(Book, book_id)
        if not book:
            raise HTTPException(404, "Book not found")
        return {
            "id": book.id,
            "title": book.title,
            "year": book.published_year,
        }

Important note: the full version of the capstone project (module 8) has 5 endpoints with more problems (including COUNT(*) over a large table and a search with a sequential scan). This simplified version focuses on the two most obvious problems — N+1 and large OFFSET — so you can practice the measurement flow without needing a huge API. When you get to module 8 you'll extend (not replace) this baseline.

app/seed.py — generate representative data

# app/seed.py
"""
Fills the DB with enough data for the performance problems to show up.

Run: python -m app.seed

Target volume:
  - 5,000 authors
  - 100,000 books (~20 per author on average)
  - 500,000 reviews (~5 per book on average)
  - 200,000 orders
"""
import asyncio
import random
from sqlalchemy import text

from .db import engine, AsyncSessionLocal, Base
from .models import Author, Book, Review, Order

N_AUTHORS = 5_000
N_BOOKS = 100_000
N_REVIEWS = 500_000
N_ORDERS = 200_000


async def seed():
    # Recreate schema
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)
        await conn.run_sync(Base.metadata.create_all)

    async with AsyncSessionLocal() as session:
        print(f"Seeding {N_AUTHORS} authors...")
        authors = [
            Author(id=i, name=f"author_{i}")
            for i in range(1, N_AUTHORS + 1)
        ]
        # Known authors for the benchmarks
        authors[41].name = "tolkien"
        authors[100].name = "asimov"
        authors[200].name = "le_guin"

        session.add_all(authors)
        await session.commit()

        print(f"Seeding {N_BOOKS} books...")
        # Bulk insert with raw SQL (faster than the ORM for large volumes)
        await session.execute(text(f"""
            INSERT INTO books (id, title, author_id, published_year)
            SELECT
                g,
                'book_' || g,
                ((g - 1) % {N_AUTHORS}) + 1,
                1900 + (random() * 125)::int
            FROM generate_series(1, {N_BOOKS}) g
        """))
        await session.commit()

        print(f"Seeding {N_REVIEWS} reviews...")
        await session.execute(text(f"""
            INSERT INTO reviews (id, book_id, rating, body)
            SELECT
                g,
                ((g - 1) % {N_BOOKS}) + 1,
                (random() * 4 + 1)::int,
                'review body ' || g
            FROM generate_series(1, {N_REVIEWS}) g
        """))
        await session.commit()

        print(f"Seeding {N_ORDERS} orders...")
        await session.execute(text(f"""
            INSERT INTO orders (id, book_id, quantity)
            SELECT
                g,
                ((g - 1) % {N_BOOKS}) + 1,
                (random() * 5 + 1)::int
            FROM generate_series(1, {N_ORDERS}) g
        """))
        await session.commit()

        print("Done.")


if __name__ == "__main__":
    asyncio.run(seed())

Run the seed:

python -m app.seed
# Takes ~30-60s depending on hardware.

Verify:

psql postgresql://bookstore:bookstore@localhost:5432/bookstore -c \
  "SELECT
     (SELECT count(*) FROM authors) AS authors,
     (SELECT count(*) FROM books) AS books,
     (SELECT count(*) FROM reviews) AS reviews,
     (SELECT count(*) FROM orders) AS orders;"
# authors |  books  | reviews  | orders
# --------+---------+----------+--------
#    5000 |  100000 |   500000 | 200000

Bring up the app

uvicorn app.main:app --workers 4 --host 0.0.0.0 --port 8000

Verify it responds:

curl http://localhost:8000/health
# {"status":"ok"}

curl "http://localhost:8000/books-with-author?author_name=tolkien"
# It'll be slow (it's the endpoint with the N+1)

---

Required measurement scripts

These three scripts are part of the deliverable. They're version-controlled in bench/.

bench/pgbench-baseline.sh — isolated queries

#!/bin/bash
# bench/pgbench-baseline.sh
# Measures key queries in isolation (without the app on top).
# Usage: ./bench/pgbench-baseline.sh

set -euo pipefail

DB_URL="postgresql://bookstore:bookstore@localhost:5432/bookstore"
DURATION=30
CLIENTS=10
JOBS=2

echo "=== pgbench: query listing an author's books ==="
pgbench -n \
  -f bench/pgbench-queries.sql \
  -c $CLIENTS -j $JOBS -T $DURATION -P 5 \
  --log \
  "$DB_URL"

echo ""
echo "To extract percentiles from the log:"
echo "  python bench/percentiles_pgbench.py"

bench/pgbench-queries.sql — query to measure

-- bench/pgbench-queries.sql
-- Query equivalent to what the /books-with-author endpoint fires
-- (without the N+1 — just the author lookup + the main query)
\set author_id random(1, 5000)

SELECT b.id, b.title, b.published_year, a.name
FROM books b
JOIN authors a ON a.id = b.author_id
WHERE a.id = :author_id
ORDER BY b.id;

(Optional, add a Python script to parse the percentiles from the pgbench log — it's in capsule 04, exercise 2.)

bench/wrk-baseline.sh — HTTP per endpoint with discipline

#!/bin/bash
# bench/wrk-baseline.sh
# HTTP baseline of each endpoint with warmup + 5 runs.
# Usage: ./bench/wrk-baseline.sh

set -euo pipefail

BASE="http://localhost:8000"
THREADS=4
CONNECTIONS=50
DURATION="60s"
WARMUP="30s"
RUNS=5

# Endpoints to measure
ENDPOINTS=(
  "$BASE/books"
  "$BASE/books-with-author?author_name=tolkien"
  "$BASE/orders?page=1"
  "$BASE/orders?page=2000"
  "$BASE/books/42"
)

for url in "${ENDPOINTS[@]}"; do
  echo ""
  echo "===================================================="
  echo "ENDPOINT: $url"
  echo "===================================================="

  echo "[warmup] $WARMUP discarded..."
  wrk -t$THREADS -c$CONNECTIONS -d$WARMUP --timeout 30s "$url" > /dev/null

  for i in $(seq 1 $RUNS); do
    echo ""
    echo "[run $i/$RUNS]"
    wrk -t$THREADS -c$CONNECTIONS -d$DURATION --latency --timeout 30s "$url" \
      | grep -E "(Requests/sec|Latency Distribution|50%|75%|90%|99%|Socket errors)"
  done
done

Make it executable:

chmod +x bench/wrk-baseline.sh

bench/locustfile.py — mixed scenarios

# bench/locustfile.py
import random
from locust import HttpUser, task, between


# Known authors from the seed
AUTHORS = ["tolkien", "asimov", "le_guin"]
# Random authors (may not exist → 404, but they still generate load)
RANDOM_AUTHOR_IDS = list(range(1, 5001))


class BookstoreReader(HttpUser):
    """Read-heavy user: lists, views details."""

    wait_time = between(1, 3)
    weight = 4  # 4x more common than the navigator

    @task(5)
    def list_all(self):
        self.client.get("/books", name="/books")

    @task(3)
    def list_by_author_known(self):
        author = random.choice(AUTHORS)
        self.client.get(
            f"/books-with-author?author_name={author}",
            name="/books-with-author"
        )

    @task(2)
    def book_detail(self):
        book_id = random.randint(1, 100_000)
        self.client.get(f"/books/{book_id}", name="/books/<id>")


class BookstoreNavigator(HttpUser):
    """User who paginates orders, including deep pages."""

    wait_time = between(2, 5)
    weight = 1

    @task(3)
    def first_page(self):
        self.client.get("/orders?page=1", name="/orders?page=<low>")

    @task(1)
    def deep_page(self):
        page = random.randint(1000, 5000)
        self.client.get(
            f"/orders?page={page}",
            name="/orders?page=<deep>"
        )

Headless baseline run with locust:

locust -H http://localhost:8000 \
  -f bench/locustfile.py \
  --users 50 --spawn-rate 5 --run-time 60s \
  --headless \
  --csv reports/baseline-locust

(Create reports/ if it doesn't exist.)

---

Your deliverable: BENCHMARKS.md

This is the most important file. Apply the canonical template from capsule 07.

Minimum required structure

# BENCHMARKS — Bookstore Baseline

## Environment context

[exhaustive: client hardware, server hardware, OS, PostgreSQL, Python,
 FastAPI, SQLAlchemy, asyncpg, pool config, app server, topology, other loads]

## Data

- `authors`: 5,000 rows
- `books`: 100,000 rows
- `reviews`: 500,000 rows
- `orders`: 200,000 rows
- Generated with `app/seed.py`, commit hash [hash]

## Methodology

- Tools: `wrk` 4.2.0, `pgbench` 16.2, `locust` 2.27.0
- Exact commands: see `bench/wrk-baseline.sh`, `bench/pgbench-baseline.sh`, `bench/locustfile.py`
- Warmup: 30s
- Runs: 5 runs of 60s
- HTTP concurrency: -t4 -c50
- locust concurrency: 50 users with weight 4:1
- Aggregation: median of the percentiles across runs

## Baseline (Module 1) — date: [today], commit: [hash]

### HTTP results per endpoint (wrk)

| Endpoint | p50 | p95 | p99 | Sustained RPS | Errors |
|----------|-----|-----|-----|---------------|--------|
| `GET /books` | [your number] | ... | ... | ... | 0% |
| `GET /books-with-author?author_name=tolkien` | ... | ... | ... | ... | ... |
| `GET /orders?page=1` | ... | ... | ... | ... | ... |
| `GET /orders?page=2000` | ... | ... | ... | ... | ... |
| `GET /books/<id>` | ... | ... | ... | ... | ... |

### Isolated query results (pgbench)

| Query | p50 | p95 | p99 | TPS |
|-------|-----|-----|-----|-----|
| An author's books (random author_id) | ... | ... | ... | ... |

### Mixed-scenario results (locust)

| Endpoint | # reqs | # fails | p50 | p95 | p99 |
|----------|--------|---------|-----|-----|-----|
| ... | ... | ... | ... | ... | ... |

### Observations

- [Your qualitative observations]
- [Hypothesis for each problematic endpoint]

### Caveats

- [Your caveats — setup limitations, variance, whatever]

What you should see (order of magnitude — real numbers vary by hardware)

Important: the following numbers are illustrative so you know what kind of output to expect. Your real numbers will depend 100% on your hardware, exact PostgreSQL version, and system load. Don't copy them — measure them on your machine.

Endpoint	Expected pattern
GET /books	Fast (p99 < 100ms on typical modern hardware). Serves as the "everything is fine" baseline.
GET /books-with-author?author_name=tolkien	Slow. p99 can range from 1s to 10s+. The difference from /books is enormous. Confirms N+1.
GET /orders?page=1	Fast (p99 < 50ms). Initial pages with a small OFFSET are cheap.
GET /orders?page=2000	Slow. p99 very different from page=1. Confirms a large OFFSET.
GET /books/<id>	Very fast (p99 < 20ms). A lookup by PK is the best thing PostgreSQL can do.

What matters is the contrast. If all your endpoints are around the same number, something is wrong in your setup (probably small tables or a warm cache).

Comparison with pgbench

When you measure the query equivalent to the problematic endpoint (without the N+1) with pgbench, you'll see something like:

• pgbench (direct query with JOIN): p99 ~5-50ms.
• wrk (endpoint /books-with-author): p99 ~1,000-10,000ms.

That gigantic difference is the forensic proof that the problem does NOT live in PostgreSQL — it lives in how the app fires queries. It's exactly the distinction from capsule 04. You'll cite it many times in the following modules.

---

Validations and error handling

Before measuring

• docker compose ps shows postgres healthy
• psql can connect and the seed counts are correct (5k, 100k, 500k, 200k)
• curl http://localhost:8000/health returns {"status": "ok"}
• wrk, pgbench, locust installed (<tool> --version works)
• Slack, browser and other apps closed (control the noise)

Common errors during the seed

• relation "authors" does not exist: FastAPI's startup creates the tables. Bring up the app at least once before running app/seed.py.
• duplicate key value violates unique constraint: there's already data. The seed drops and recreates, but if you fail halfway through, do it manually: psql -c "DROP TABLE orders, reviews, books, authors CASCADE;" and start over.
• Very slow seed (>5 min): confirm you're using the bulk INSERTs with generate_series, not one-by-one ORM inserts.

Common errors during the benchmarks

• wrk reports Socket errors: timeout N: the /books-with-author endpoint can take more than 2s per request. Raise the timeout: --timeout 30s (already in the example script).
• locust shows many 404s on /books/<id>: the random id may not exist if it exceeds 100,000. Adjust the range in locustfile.py.
• Very variable numbers across runs: confirm you're doing a warmup and that there's no other load on the machine.

---

Self-evaluation rubric

Total: 100 points. Passing: ≥70.

Functional app (20 pts)

• (5 pts) The 4 endpoints respond correctly (HTTP 200 / 404 as appropriate).
• (5 pts) The seed produces the exact volumes: 5k authors, 100k books, 500k reviews, 200k orders.
• (5 pts) The "fast" endpoints respond well; the problematic ones are measurably slow.
• (5 pts) docker-compose up brings up PostgreSQL without additional manual intervention.

Measurement scripts (25 pts)

• (8 pts) bench/wrk-baseline.sh runs the 5 endpoints with warmup + 5 runs.
• (8 pts) bench/pgbench-baseline.sh runs the query with concurrency and --log.
• (5 pts) bench/locustfile.py defines at least 2 user classes with weight.
• (4 pts) All the scripts are executable without modifying absolute paths.

BENCHMARKS.md (40 pts)

• (8 pts) An exhaustive "Environment context" section (everything from the canonical template).
• (5 pts) A "Data" section with exact counts and a reference to the seed script.
• (5 pts) A "Methodology" section with tools, commands, warmup, runs, aggregation.
• (10 pts) An HTTP baseline table with p50/p95/p99 + RPS + errors per endpoint.
• (5 pts) A table with the pgbench result for the isolated query.
• (3 pts) A table with locust stats (at least the main endpoint).
• (4 pts) An "Observations" section with at least 3 hypotheses (one for each problematic endpoint).

Quality and discipline (15 pts)

• (5 pts) You applied a warmup in ALL the measurements.
• (5 pts) You reported percentiles, NOT just averages.
• (3 pts) You documented at least 2 real caveats.
• (2 pts) You didn't touch any index or endpoint to "fix" it. Measurement only.

Extra credit (up to +20 pts)

• (+5 pts) A Python script to parse pgbench logs and extract percentiles automatically.
• (+5 pts) Concurrency variation (stress test) to find the knee of each endpoint.
• (+5 pts) HTTP vs isolated-query comparison to confirm where the bottleneck lives in each problematic endpoint.
• (+5 pts) BENCHMARKS.md version-controlled in git with commits explaining each update.

---

Common mistakes in this project

Mistake 1: measuring without warmup

Symptom: the first run gives a very high p99, the following ones are consistent.

Why it happens: a cold connection pool, empty shared_buffers, planner JIT without warmup.

How to avoid it: the scripts include an explicit 30s warmup. Don't modify them by removing it "to save time" — the result will be contaminated.

Mistake 2: not contrasting wrk with pgbench

Symptom: you report "endpoint X's p99 is 8s" without knowing whether it's the DB or the app.

Why it matters: modules 2-7 attack different layers. Without knowing where the bottleneck lives, you can't prioritize.

How to avoid it: run pgbench with the equivalent query and report both numbers in BENCHMARKS.md. The gigantic difference is exactly the evidence you need.

Mistake 3: the temptation to "while I'm here, let me optimize"

Symptom: you see the obvious N+1 in app/main.py:list_books_with_author and want to change it to selectinload right away.

Why to avoid it: module 1 is measurement only. If you "fix" it while measuring, you have no baseline to compare the module 4 fix against. The pedagogical value is lost.

How to handle it: note the hypothesis in BENCHMARKS.md ("hypothesis: N+1 in list_books_with_author, see module 4") and move on. You save the fix for when you see it with the right tool.

Mistake 4: a small dataset

Symptom: all the endpoints are fast, the problems don't show up.

Why it happens: with 100 rows instead of 100,000, a sequential scan is faster than an index scan, a large OFFSET doesn't exist, and an N+1 with 5 books isn't noticeable.

How to avoid it: use the seed's volumes (100k books, 500k reviews). If your machine doesn't have space, you can scale to 50% (50k books, 250k reviews) but document the caveat. Don't go down to tiny "test data".

Mistake 5: measuring from the same terminal where uvicorn runs

Symptom: uvicorn is logging every request to stdout, stealing IO during the benchmark.

How to avoid it: uvicorn in one terminal without verbosity (--log-level warning), benchmarks in another terminal. Or better: redirect the logs to a file.

Mistake 6: forgetting to version-control BENCHMARKS.md

Symptom: you wrote it locally, didn't commit it, lost it.

How to avoid it: git add BENCHMARKS.md with every update. Consider adding a pre-commit hook if you work on a team: if performance-relevant code changes, require the file to be updated.

---

What to do if you get stuck?

Symptom	Diagnosis	Solution
Docker won't bring up postgres	Port 5432 is occupied	docker compose down, kill local postgres, retry
Seed takes a long time	ORM inserts instead of raw	Use the given seed, don't improvise
wrk gives timeouts on /books-with-author	The endpoint is legitimately slow	Raise --timeout 30s (already in the script)
locust saturates itself	Virtual users / hardware	Reduce --users or distribute
Very variable numbers	Missing warmup or another load	Close other apps, ensure a warmup
BENCHMARKS.md feels "empty"	Missing context/observations	Re-read capsule 07, complete the sections

---

Resources for the project

1. FastAPI Documentation — async — to confirm your app uses async correctly.
2. SQLAlchemy 2.0 async ORM — a reference for AsyncSession and select().
3. PostgreSQL generate_series — to understand the bulk seed.
4. Capsule 03 — Reproducible baselines — re-read if you have doubts about warmup/runs/aggregation.
5. Capsule 04 — pgbench — to extract percentiles from the log.
6. Capsule 07 — Reporting improvements — the canonical BENCHMARKS.md template.
7. Locust — --csv output — to save version-controllable evidence.

---

What comes next

Your BENCHMARKS.md with the "Baseline (Module 1)" section is now the reference point for the whole guide. In every following module you'll:

1. Learn a diagnostic or optimization technique (EXPLAIN, indexing, eager loading, profiling, pool tuning, etc.).
2. Apply it to the corresponding problematic endpoint.
3. Re-measure with the same scripts from module 1 (bench/wrk-baseline.sh, bench/locustfile.py).
4. Add a new "After Module X" section to BENCHMARKS.md with a before/after table + observations + caveats.

You'll watch that file grow. In module 8, when you integrate all the optimizations into the final consolidating project (the full version of the Bookstore with all 5 problems), you'll have a document with 6+ "After" sections that tells the whole optimization story with honest numbers.

Before moving on to module 2, make sure that:

• ✅ The repo is version-controlled in git, with BENCHMARKS.md committed.
• ✅ You can run ./bench/wrk-baseline.sh and get the same numbers (±5%) on a second run.
• ✅ You can explain in your own words why /books-with-author is slow and what your hypothesis is (which you'll confirm with EXPLAIN ANALYZE in module 2 and resolve with eager loading in module 4).
• ✅ You can explain why /orders?page=2000 is slow (OFFSET is O(n)), and you understand that this problem waits until module 8.

Final deliverable of module 1: a commit in your repo with:

1. All the app code (app/).
2. All the measurement scripts (bench/).
3. A complete BENCHMARKS.md with the "Baseline (Module 1)" section.
4. A README.md explaining how to bring up the project and reproduce the baseline.

Once you have it: congratulations! You went from "measured it wrong" to "measured it like a senior". See you in module 2 with EXPLAIN ANALYZE.

---

Module 1 — Database Performance & Query Tuning Guide

Next module: EXPLAIN ANALYZE in Depth — we'll take the /books-with-author endpoint (the worst one in the baseline) and break it down at the query-plan level. You'll see exactly what PostgreSQL does in every millisecond of those 8 seconds.