Final project: setting up the Bookstore with 5 planted problems

You reached the guide's integrative project. Capsules 02-06 covered anti-patterns recognizable one by one; now you're going to apply everything from the guide to a real API with real problems. The project is deliberately compact (5-7 endpoints, 5 planted problems) so it's executable in 8-12 hours and produces a portfolio-worthy deliverable.

In this capsule you're going to see the complete setup of the project: the code of the "Bookstore" API with planted problems, the docker-compose.yml that runs it, the seeded data, and the workflow you're going to follow. Capsule 08 is the execution and the final BENCHMARKS.md.

The project's objective: demonstrate integrated mastery of modules 1-7 plus module 8's anti-patterns. The final deliverable is a public GitHub repo with optimized code and a benchmarks document you can link from your CV.

---

The Bookstore API

It's a reduced version of a real bookstore with 5 endpoints, all with performance problems planted on purpose.

Schema

-- schema.sql

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE publishers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    country TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    author_id INTEGER NOT NULL REFERENCES authors(id),
    publisher_id INTEGER NOT NULL REFERENCES publishers(id),
    isbn TEXT NOT NULL UNIQUE,
    price NUMERIC(10, 2) NOT NULL,
    stock INTEGER NOT NULL DEFAULT 0,
    published_year INTEGER,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    total NUMERIC(10, 2) NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    book_id INTEGER NOT NULL REFERENCES books(id),
    quantity INTEGER NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

-- IMPORTANT: indexes NOT created on purpose.
-- Some you'll create during the project, others you'll decide not to create.

Seeded data

# seed.py
import asyncio
import random
from datetime import datetime, timedelta, timezone

import asyncpg
from faker import Faker

faker = Faker()


async def seed():
    conn = await asyncpg.connect(
        "postgresql://postgres:postgres@localhost:5432/bookstore"
    )

    # 1000 authors
    print("Seeding authors...")
    await conn.executemany(
        "INSERT INTO authors (name, country) VALUES ($1, $2)",
        [(faker.name(), faker.country()) for _ in range(1000)]
    )

    # 100 publishers
    print("Seeding publishers...")
    await conn.executemany(
        "INSERT INTO publishers (name, country) VALUES ($1, $2)",
        [(faker.company(), faker.country()) for _ in range(100)]
    )

    # 100,000 books
    print("Seeding 100k books...")
    books_data = []
    for i in range(100_000):
        books_data.append((
            faker.catch_phrase(),
            faker.text(max_nb_chars=500),
            random.randint(1, 1000),
            random.randint(1, 100),
            f"ISBN-{i:06d}",
            round(random.uniform(5, 100), 2),
            random.randint(0, 50),
            random.randint(1990, 2026),
        ))
    await conn.executemany("""
        INSERT INTO books (title, description, author_id, publisher_id, isbn,
                          price, stock, published_year)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
    """, books_data)

    # 50,000 customers
    print("Seeding 50k customers...")
    customers_data = [
        (faker.unique.email(), faker.name())
        for _ in range(50_000)
    ]
    await conn.executemany(
        "INSERT INTO customers (email, name) VALUES ($1, $2)",
        customers_data
    )

    # 1,000,000 orders
    print("Seeding 1M orders...")
    orders_data = []
    base_date = datetime(2024, 1, 1, tzinfo=timezone.utc)
    for i in range(1_000_000):
        days_ago = random.randint(0, 365 * 2)
        orders_data.append((
            random.randint(1, 50_000),
            round(random.uniform(10, 500), 2),
            random.choice(['pending', 'shipped', 'delivered', 'cancelled']),
            base_date + timedelta(days=days_ago, seconds=random.randint(0, 86400)),
        ))
    await conn.executemany("""
        INSERT INTO orders (customer_id, total, status, created_at)
        VALUES ($1, $2, $3, $4)
    """, orders_data)

    # 3,000,000 order_items (~3 per order avg)
    print("Seeding 3M order_items...")
    items_data = []
    for order_id in range(1, 1_000_001):
        for _ in range(random.randint(1, 5)):
            items_data.append((
                order_id,
                random.randint(1, 100_000),
                random.randint(1, 5),
                round(random.uniform(5, 100), 2),
            ))

    # Batches of 100k so as not to fill up memory
    for i in range(0, len(items_data), 100_000):
        batch = items_data[i:i + 100_000]
        await conn.executemany("""
            INSERT INTO order_items (order_id, book_id, quantity, price)
            VALUES ($1, $2, $3, $4)
        """, batch)
        print(f"  ... {i + len(batch):,}/{len(items_data):,}")

    print("Done. Running ANALYZE...")
    await conn.execute("ANALYZE")

    await conn.close()


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

Resulting data:

• 1,000 authors
• 100 publishers
• 100,000 books
• 50,000 customers
• 1,000,000 orders
• ~3,000,000 order_items

The 5 endpoints with problems

# main.py
from datetime import datetime, timezone
from typing import Optional

from fastapi import FastAPI, Depends, Query
from sqlalchemy import select, func, text
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine, async_sessionmaker
from sqlalchemy.orm import selectinload

from models import Book, Author, Publisher, Order, OrderItem, Customer

# Engine without pool tuning — problem #5
engine = create_async_engine(
    "postgresql+asyncpg://postgres:postgres@localhost:5432/bookstore",
    pool_size=5,        # Problem: too small for real load
    max_overflow=0,     # Problem: no overflow
)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)


async def get_db():
    async with SessionLocal() as session:
        yield session


app = FastAPI()


# PROBLEM 1: N+1 in /books?author=X
@app.get("/books")
async def list_books_by_author(
    author_id: int = Query(...),
    db: AsyncSession = Depends(get_db),
):
    """List books with author and publisher info.
    Problem: N+1 when loading each book's relationships."""
    result = await db.execute(
        select(Book).where(Book.author_id == author_id)
    )
    books = result.scalars().all()

    # Planted N+1: iterate over each book and access relationships
    response = []
    for book in books:
        # Each access fires a query
        response.append({
            "id": book.id,
            "title": book.title,
            "author_name": book.author.name,
            "publisher_name": book.publisher.name,
            "price": float(book.price),
        })

    return response


# PROBLEM 2: large OFFSET in /orders?page=N
@app.get("/orders")
async def list_orders(
    page: int = Query(1, ge=1),
    page_size: int = Query(20, ge=1, le=100),
    db: AsyncSession = Depends(get_db),
):
    """Order pagination by OFFSET.
    Problem: degrades with deep pages."""
    offset = (page - 1) * page_size
    result = await db.execute(
        select(Order).order_by(Order.created_at.desc())
                     .limit(page_size).offset(offset)
    )
    orders = result.scalars().all()

    # Bonus: COUNT(*) on every response — problem 3
    total = await db.scalar(select(func.count(Order.id)))

    return {
        "items": [
            {"id": o.id, "total": float(o.total), "status": o.status,
             "created_at": o.created_at.isoformat()}
            for o in orders
        ],
        "page": page,
        "page_size": page_size,
        "total": total,
    }


# PROBLEM 3: COUNT(*) in /stats/total-sales
@app.get("/stats/total-sales")
async def total_sales(db: AsyncSession = Depends(get_db)):
    """Global statistics.
    Problem: COUNT(*) and SUM on every request, without caching."""
    total_orders = await db.scalar(select(func.count(Order.id)))
    total_revenue = await db.scalar(
        select(func.sum(Order.total)).where(Order.status != 'cancelled')
    )
    pending_orders = await db.scalar(
        select(func.count(Order.id)).where(Order.status == 'pending')
    )

    return {
        "total_orders": total_orders,
        "total_revenue": float(total_revenue or 0),
        "pending_orders": pending_orders,
    }


# PROBLEM 4: Seq scan in /search
@app.get("/search")
async def search_books(
    q: str = Query(..., min_length=2),
    db: AsyncSession = Depends(get_db),
):
    """Full-text search on title and description.
    Problem: ILIKE '%X%' forces a Seq Scan."""
    result = await db.execute(
        select(Book).where(
            (Book.title.ilike(f"%{q}%")) |
            (Book.description.ilike(f"%{q}%"))
        ).limit(20)
    )
    books = result.scalars().all()
    return [
        {"id": b.id, "title": b.title, "price": float(b.price)}
        for b in books
    ]


# PROBLEM 5: pool exhaustion under load
# (the problem is in the engine config above: pool_size=5, max_overflow=0)
@app.get("/health/orders")
async def get_recent_orders(db: AsyncSession = Depends(get_db)):
    """Health-check endpoint that touches the DB."""
    result = await db.execute(
        select(Order).order_by(Order.created_at.desc()).limit(5)
    )
    return [{"id": o.id, "status": o.status} for o in result.scalars()]


# Bonus: stale stats — Problem 6 (module 7)
# After the initial seed, we're going to do a MASSIVE bulk insert without ANALYZE.
# The listing query will use old stats and pick a bad plan.

Complete setup with Docker Compose

# docker-compose.yml
version: '3.8'

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: bookstore
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./schema.sql:/docker-entrypoint-initdb.d/schema.sql
    command:
      - "postgres"
      - "-c"
      - "shared_preload_libraries=pg_stat_statements"
      - "-c"
      - "max_connections=100"
      - "-c"
      - "shared_buffers=256MB"
      # Intentional HDD defaults — you tune them during the project
      - "-c"
      - "random_page_cost=4.0"
      - "-c"
      - "effective_cache_size=4GB"

  pgbouncer:
    image: pgbouncer/pgbouncer:latest
    environment:
      DATABASES_HOST: postgres
      DATABASES_PORT: 5432
      DATABASES_USER: postgres
      DATABASES_PASSWORD: postgres
      DATABASES_DBNAME: bookstore
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 200
      DEFAULT_POOL_SIZE: 20
    ports:
      - "6432:6432"
    depends_on:
      - postgres

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

volumes:
  postgres_data:

# Dockerfile
FROM python:3.12-slim

WORKDIR /app

COPY requirements.txt .
RUN pip install -r requirements.txt

COPY . .

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

# requirements.txt
fastapi==0.110.0
uvicorn[standard]==0.27.0
sqlalchemy[asyncio]==2.0.25
asyncpg==0.29.0
faker==22.0.0

Measurement scripts

# bench/baseline.sh
#!/bin/bash

echo "=== Baseline benchmarks ==="

# Endpoint 1: /books
echo "--- /books?author_id=42 ---"
wrk -t2 -c10 -d30s "http://localhost:8000/books?author_id=42"

# Endpoint 2: /orders, page 1 vs page 1000
echo "--- /orders?page=1 ---"
wrk -t2 -c10 -d30s "http://localhost:8000/orders?page=1"

echo "--- /orders?page=1000 ---"
wrk -t2 -c10 -d30s "http://localhost:8000/orders?page=1000"

echo "--- /stats/total-sales ---"
wrk -t2 -c10 -d30s "http://localhost:8000/stats/total-sales"

echo "--- /search?q=Python ---"
wrk -t2 -c10 -d30s "http://localhost:8000/search?q=Python"

echo "--- /health/orders under load (50 conn) ---"
wrk -t4 -c50 -d30s "http://localhost:8000/health/orders"

---

Project workflow (high-level view)

You're going to follow a 6-step process:

Step 1: setup (~1h)

# Clone the base project repo
git clone <bookstore-skeleton-repo>
cd bookstore

# Bring up the services
docker-compose up -d postgres
docker-compose exec postgres psql -U postgres -c "CREATE DATABASE bookstore;"
docker-compose exec postgres psql -U postgres -d bookstore -f /docker-entrypoint-initdb.d/schema.sql

# Seed (takes ~5-10 min)
python seed.py

# Bring up the app
docker-compose up -d

Step 2: measure the baseline (~30 min)

# Enable pg_stat_statements
docker-compose exec postgres psql -U postgres -d bookstore -c \
  "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

# Reset stats
docker-compose exec postgres psql -U postgres -d bookstore -c \
  "SELECT pg_stat_statements_reset();"

# Run the benchmarks
./bench/baseline.sh > baseline_results.txt 2>&1

# See the top queries
docker-compose exec postgres psql -U postgres -d bookstore -c "
SELECT query, calls, total_exec_time::INT, mean_exec_time::INT
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
"

Document the results in BENCHMARKS.md before touching anything.

Step 3: prioritize (~15 min)

Look at the results. Identify:

• Which endpoint is slowest (p95)?
• Which endpoint consumes the most total time (mean × calls in pg_stat_statements)?
• Are there endpoints that fail under load (errors in wrk)?

Order the 5 problems by impact. Attack from largest to smallest.

Step 4: attack problem by problem (~5-7h, distributed)

For each problem:

1. Diagnose: EXPLAIN ANALYZE the culprit query. Identify the bottleneck.
2. Apply the fix: using the tool from the corresponding module.
3. Validate: EXPLAIN ANALYZE afterward. A different plan, a better time.
4. Benchmark: re-run wrk on the specific endpoint.
5. Document: add to BENCHMARKS.md with before/after numbers.

Mapping of problems to modules:

#	Problem	Module	Fix
1	N+1 in /books	Module 4	selectinload(Book.author, Book.publisher)
2	Large OFFSET in /orders	Module 8 cap 02	Cursor pagination
3	COUNT(*) in /stats and /orders	Module 8 cap 03	Materialized view / remove it
4	Seq Scan in /search	Module 3 + Module 8 cap 06	GIN index with pg_trgm
5	Pool exhaustion in /health/orders	Module 6	PgBouncer + pool tuning
6 (bonus)	Stale stats after a bulk insert	Module 7	ANALYZE and/or autovacuum tuning

Step 5: re-measure at the end (~30 min)

With all the fixes applied:

# Reset stats
docker-compose exec postgres psql -U postgres -d bookstore -c \
  "SELECT pg_stat_statements_reset();"

# Re-run the benchmarks
./bench/baseline.sh > final_results.txt 2>&1

# Final top queries
docker-compose exec postgres psql -U postgres -d bookstore -c "
SELECT query, calls, total_exec_time::INT, mean_exec_time::INT
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
"

Step 6: document (~1-2h)

Complete BENCHMARKS.md with:

• Reproducible setup (versions, hardware, data).
• A table with before/after results per endpoint.
• Analysis of each fix: which problem, which tool, what impact.
• Conclusions: which optimization had the greatest impact, what you learned.

---

Final repo structure

bookstore-perf-project/
├── README.md                  # Setup instructions
├── BENCHMARKS.md              # ← The main deliverable
├── docker-compose.yml
├── Dockerfile
├── requirements.txt
├── schema.sql                 # Initial schema
├── seed.py                    # Initial data
├── main.py                    # API with planted problems
├── main_optimized.py          # API after optimizations
├── models.py                  # SQLAlchemy models
├── bench/
│   ├── baseline.sh            # Before measurements
│   ├── final.sh               # After measurements
│   └── results/
│       ├── baseline.txt
│       └── final.txt
└── docs/
    ├── problem-1-n-plus-one.md
    ├── problem-2-offset-pagination.md
    ├── problem-3-count-star.md
    ├── problem-4-seq-scan-search.md
    ├── problem-5-pool-tuning.md
    └── problem-6-stats-bulk-load.md

Each docs/problem-X.md documents a problem in detail: diagnosis, fix, EXPLAIN before/after, quantified improvement.

---

Common traps in projects like this

1. Skipping the setup and starting to optimize.

Without measuring a baseline, you have no way to prove improvement. The first 30% of the time is setup and baseline; the other 70% is optimization.

2. Optimizing everything in parallel and not being able to attribute improvements.

If you fix all 5 problems at once, you don't know which one was responsible for which improvement. Attacking one by one and measuring after each one lets you attribute results.

3. Using seed data that's too small.

With 1,000 orders instead of 1M, the problems don't manifest. The seeds are sized so the problems are visible.

4. Not documenting exact commands.

If BENCHMARKS.md says "p95 improved" but doesn't show the exact commands, it's not reproducible. Exact wrk commands, exact EXPLAIN output, exact queries.

5. Skipping problem #5 (pool tuning) because "it's complex."

PgBouncer + asyncpg is complex, yes. But module 6 covered it. We plant it on purpose because without solving it the /health/orders endpoint fails under load, and fixing it is exactly the kind of problem a senior dev should be able to solve.

6. Writing BENCHMARKS.md with weak prose.

"Improved a lot" isn't a metric. "p95 from 850ms to 65ms (-92%)" is. A table with numbers, brief analysis, not narrative.

---

While you wait for capsule 08

Before capsule 08 (where you execute the complete project), you can already:

1. Clone the repo skeleton (or create it following the code above).
2. Bring up the setup with docker-compose up -d.
3. Run seed.py and wait for it to finish (~5-10 min).
4. Run ./bench/baseline.sh to see the initial state.
5. Look at the output: which endpoints are visibly slow? Which ones fail under load? What's the order of impact?

That initial measurement is your input for capsule 08.

---

Summary and next step

What you have set up:

• The "Bookstore" API with 5 endpoints + 5 planted problems (+1 bonus).
• The complete stack: PostgreSQL 16 + PgBouncer + FastAPI + SQLAlchemy 2.0 async.
• Enough data for the problems to be visible (100k books, 1M orders, 3M order_items).
• Benchmark scripts with wrk.
• A 6-step workflow: setup → baseline → prioritize → attack → re-measure → document.

Before moving on, you should have:

• The project skeleton cloned or created.
• docker-compose up running.
• seed.py executed (be patient with the seed time).
• A baseline run and saved in baseline_results.txt.

In the next capsule you're going to execute the complete project: apply the 6 fixes (5 planted + 1 bonus of stats), measure each one, complete BENCHMARKS.md. It closes the guide with a portfolio-worthy deliverable you can show in your next interview.

---

Resources

1. FastAPI — Async setup — the official reference.
2. SQLAlchemy 2.0 — Async ORM — the official reference.
3. asyncpg — Async driver for PostgreSQL — reference.
4. Docker Compose — Postgres setup — common patterns.
5. wrk — Load testing — the tool used in the benchmarks.
6. Faker — Synthetic data — used in the seed.
7. GitHub README templates for portfolio projects — for your final repo.

---

Capsule 07 of 08 — Module 8 — Database Performance & Query Tuning Guide