Module 8: Anti-Patterns and Final Project

Final project: execution and a portfolio-worthy `BENCHMARKS.md`

You close the guide. Capsule 07 gave you the setup; this is the execution. You're going to apply the 6 fixes (5 planted problems + 1 bonus of stats), measure each one with reproducible numbers, and produce the BENCHMARKS.md you can link from your CV.

The objective is to demonstrate integrated mastery: it's not knowing each tool separately, it's applying them together in a system with real problems. And producing a deliverable that clearly communicates what you did, how, and with what impact. In senior interviews, that's worth more than a long résumé.

We're going to go problem by problem. For each one: diagnosis → fix → measurement → documentation. By the end of the capsule, you'll have the complete repo and a BENCHMARKS.md ready.

Before starting: initial state

Assume you completed step 2 of capsule 07: baseline run, pg_stat_statements active, results saved.

Your typical baseline is going to look like this (approximate numbers, they vary by hardware):

Endpoint               | p50    | p95    | p99    | Errors
/books?author_id=42    | 245ms  | 580ms  | 920ms  | 0
/orders?page=1         | 18ms   | 35ms   | 52ms   | 0
/orders?page=1000      | 380ms  | 720ms  | 1100ms | 0
/orders?page=10000     | 3200ms | 5800ms | 8500ms | 0
/stats/total-sales     | 480ms  | 920ms  | 1400ms | 0
/search?q=Python       | 850ms  | 1600ms | 2300ms | 0
/health/orders (50conn)| 42ms   | 4500ms | 9800ms | ~30%

Notes:

/books is slow due to N+1.
/orders with a large OFFSET degrades exponentially.
/stats is slow due to COUNT(*) and SUM without caching.
/search is a full Seq Scan of 100k books with ILIKE.
/health/orders fails under load (50 connections) due to an exhausted pool.

Top of pg_stat_statements:

query                                            | total_time | calls
SELECT * FROM books WHERE author_id = $1         | 4500s      | 1234
SELECT count(*) FROM orders                      | 3200s      | 5678
SELECT * FROM orders ORDER BY created_at LIMIT...| 2800s      | 9876
SELECT * FROM books WHERE title ILIKE ...        | 1900s      | 543
SELECT a.name FROM authors a WHERE a.id = $1     | 1200s      | 89456  (← N+1!)

pg_stat_statements confirms: the N+1 pattern (many calls to the author query) and the COUNT(*) are the most expensive in aggregate. Start there.

Problem 1: N+1 in `/books?author_id=X`

Diagnosis

You already saw it above in pg_stat_statements. The clue is the counter calls = 89,456 for the authors query — clearly an N+1.

Confirmation with EXPLAIN:

EXPLAIN ANALYZE SELECT * FROM books WHERE author_id = 42;
-- Clean plan, ~10ms (this isn't the slow one)

The endpoint's query itself is fine. The problem is the Python code that iterates and fires N relationship queries.

Fix

Apply selectinload (module 4) when loading the query.

from sqlalchemy.orm import selectinload

@app.get("/books")
async def list_books_by_author(
    author_id: int = Query(...),
    db: AsyncSession = Depends(get_db),
):
    result = await db.execute(
        select(Book)
        .where(Book.author_id == author_id)
        .options(
            selectinload(Book.author),
            selectinload(Book.publisher),
        )
    )
    books = result.scalars().all()

    response = [{
        "id": book.id,
        "title": book.title,
        "author_name": book.author.name,
        "publisher_name": book.publisher.name,
        "price": float(book.price),
    } for book in books]

    return response

With selectinload, SQLAlchemy makes 3 queries total (one for the main table, one for authors, one for publishers) instead of 1 + 2N.

Measurement

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

# Re-run
wrk -t2 -c10 -d30s "http://localhost:8000/books?author_id=42"

Typical result:

Endpoint               | Before p95 | After p95 | Improvement
/books?author_id=42    | 580ms      | 45ms      | 12.9x

Document in BENCHMARKS.md

## Problem 1: N+1 in `/books?author_id=X`

**Diagnosis:**
- `pg_stat_statements` showed 89,456 calls to `SELECT a.name FROM authors WHERE a.id = $1` with `total_time = 1200s`.
- Each call to the endpoint fired ~70 additional queries (one per book × 2 relationships).

**Fix:**
- Add `selectinload(Book.author, Book.publisher)` to the main query.
- Reduces from `1 + 2N` queries to `3` queries total.

**Before:**
- p50 = 245ms, p95 = 580ms, p99 = 920ms
- 1 main query + 70-200 relationship queries per request

**After:**
- p50 = 22ms, p95 = 45ms, p99 = 78ms
- Exactly 3 queries per request

**Improvement:** ~12-13x in p95.

**Module applied:** Module 4 (Eliminate N+1 with selectinload).

Problem 2: large OFFSET in `/orders`

Diagnosis

Visible in the baseline: /orders?page=10000 takes 5.8 seconds. The query is LIMIT 20 OFFSET 199980 — the large OFFSET causes a Seq Scan + Sort + discard of 200k rows.

EXPLAIN ANALYZE
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 199980;
-- Without an index on created_at: Seq Scan + Sort + Limit, ~5-6 seconds

Fix

Cursor pagination (module 8 cap 02). Replace the endpoint:

import json
from base64 import urlsafe_b64encode, urlsafe_b64decode
from datetime import datetime
from typing import Optional


def encode_cursor(data: dict) -> str:
    return urlsafe_b64encode(json.dumps(data).encode()).decode()


def decode_cursor(cursor: str) -> dict:
    return json.loads(urlsafe_b64decode(cursor.encode()))


@app.get("/orders")
async def list_orders_cursor(
    cursor: Optional[str] = None,
    page_size: int = Query(20, ge=1, le=100),
    db: AsyncSession = Depends(get_db),
):
    query = (
        select(Order)
        .order_by(Order.created_at.desc(), Order.id.desc())
        .limit(page_size + 1)
    )

    if cursor:
        decoded = decode_cursor(cursor)
        query = query.where(
            (Order.created_at, Order.id) <
            (datetime.fromisoformat(decoded["created_at"]), decoded["id"])
        )

    result = await db.execute(query)
    orders = result.scalars().all()

    has_more = len(orders) > page_size
    if has_more:
        orders = orders[:page_size]

    next_cursor = None
    if has_more and orders:
        last = orders[-1]
        next_cursor = encode_cursor({
            "created_at": last.created_at.isoformat(),
            "id": last.id,
        })

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

Create a composite index for support:

CREATE INDEX CONCURRENTLY idx_orders_created_id
ON orders(created_at DESC, id DESC);

Measurement

For cursor pagination, measure by paginating deep iteratively (you can't simulate OFFSET 200k with a cursor — that's the point):

# Iterate with a cursor 50 times
# (a shell script that follows next_cursor)

Typical result:

Endpoint                       | Before p95 | After p95 | Improvement
/orders (page 1, OFFSET 0)     | 35ms       | 12ms      | 2.9x
/orders (page 1k, OFFSET 20k)  | 720ms      | 12ms      | 60x
/orders (page 10k, OFFSET 200k)| 5800ms     | 12ms      | 480x

Document

## Problem 2: large OFFSET in `/orders`

**Diagnosis:**
- Query with a large OFFSET: `LIMIT 20 OFFSET 199980`.
- PostgreSQL traverses 200,020 rows to return 20.
- O(n) complexity — degrades linearly with the page.

**Fix:**
- Refactor to cursor pagination with a `(created_at, id)` tiebreaker.
- Create composite index `idx_orders_created_id ON orders(created_at DESC, id DESC)`.
- The endpoint changes from `?page=N` to `?cursor=X`.

**Before:**
- Page 1: p95 = 35ms
- Page 1,000: p95 = 720ms
- Page 10,000: p95 = 5,800ms

**After:**
- Any page (via cursor): p95 = 12ms

**Improvement:** O(n) → O(1). Regardless of depth, constant latency.

**Module applied:** Module 8, capsule 02 (Large OFFSET anti-pattern).

Problem 3: COUNT(*) in `/stats` and `/orders`

Diagnosis

pg_stat_statements shows SELECT count(*) FROM orders with 5,678 calls × 480ms average = 3,200s total. Massive aggregate cost.

Fix

Two sub-fixes:

Fix 3a: Materialized view for /stats.

CREATE MATERIALIZED VIEW orders_stats AS
SELECT
    COUNT(*) AS total_orders,
    SUM(total) FILTER (WHERE status != 'cancelled') AS total_revenue,
    COUNT(*) FILTER (WHERE status = 'pending') AS pending_orders,
    NOW() AS last_refresh
FROM orders;

CREATE UNIQUE INDEX ON orders_stats ((1));

Refresh with cron (5min):

CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule(
    'refresh-orders-stats',
    '*/5 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY orders_stats;'
);

Endpoint:

@app.get("/stats/total-sales")
async def total_sales(db: AsyncSession = Depends(get_db)):
    result = await db.execute(text("""
        SELECT total_orders, total_revenue, pending_orders, last_refresh
        FROM orders_stats
    """))
    row = result.mappings().first()
    return dict(row)

Fix 3b: Remove the COUNT(*) from /orders (cursor pagination doesn't need it).

We already did this in fix 2 — the cursor endpoint doesn't include total.

Measurement

Endpoint               | Before p95 | After p95 | Improvement
/stats/total-sales     | 920ms      | 3ms       | 307x
/orders (page 1)       | 35ms       | 12ms      | 2.9x (it was the included COUNT)

Document

## Problem 3: slow `COUNT(*)` in `/stats` and `/orders`

**Diagnosis:**
- `/stats/total-sales` executed 3 aggregation queries (`COUNT`, `SUM`, `COUNT FILTER`) on every request.
- Paginated `/orders` included `COUNT(*)` on every response to show `total`.
- On a 1M-row table, each `COUNT(*)` takes ~480ms.

**Fix:**
- `/stats`: materialized view `orders_stats` with a refresh every 5min via pg_cron.
- `/orders`: removed `COUNT(*)` (cursor pagination doesn't need `total`).

**Before:**
- `/stats/total-sales` p95 = 920ms
- `/orders` p95 = 35ms (with COUNT included)

**After:**
- `/stats/total-sales` p95 = 3ms (view read)
- `/orders` p95 = 12ms (without COUNT)

**Improvement:** `/stats` 307x faster. Accepts a delay of up to 5min in the stats.

**Module applied:** Module 8, capsule 03 (COUNT(*) anti-pattern).

Problem 4: Seq Scan in `/search`

Diagnosis

EXPLAIN ANALYZE
SELECT * FROM books WHERE title ILIKE '%Python%' OR description ILIKE '%Python%';

Seq Scan on books (cost=0.00..23456 rows=1234 width=...)
                  (actual time=0.012..1623 rows=4892)
  Filter: ((title ~~* '%Python%') OR (description ~~* '%Python%'))

A full Seq Scan of 100k books. ILIKE with %X% doesn't use a B-tree.

Fix

A trigram index with pg_trgm (module 8 cap 06):

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY idx_books_title_trgm
ON books USING gin (title gin_trgm_ops);

CREATE INDEX CONCURRENTLY idx_books_description_trgm
ON books USING gin (description gin_trgm_ops);

ANALYZE books;

Re-EXPLAIN:

EXPLAIN ANALYZE
SELECT * FROM books WHERE title ILIKE '%Python%';

Bitmap Heap Scan on books
  Recheck Cond: (title ~~* '%Python%')
  ->  Bitmap Index Scan on idx_books_title_trgm

The plan uses the GIN index, scanning only the relevant pages.

Measurement

Endpoint            | Before p95 | After p95 | Improvement
/search?q=Python    | 1600ms     | 28ms      | 57x

Document

## Problem 4: Seq Scan in `/search`

**Diagnosis:**
- Query with `ILIKE '%X%'` forces a full Seq Scan (B-tree doesn't support substrings).
- On 100k books × 2 columns = 200k rows scanned per request.

**Fix:**
- Create the `pg_trgm` extension.
- Create GIN indexes on `books.title` and `books.description` with `gin_trgm_ops`.
- `CREATE INDEX CONCURRENTLY` so as not to block writes during creation.

**Before:**
- p95 = 1,600ms (Seq Scan).

**After:**
- p95 = 28ms (Bitmap Index Scan via GIN).

**Improvement:** 57x.

**Module applied:** Module 3 (Indexing) + Module 8 cap 06 (LIKE without trigram anti-pattern).

Problem 5: Pool exhaustion in `/health/orders`

Diagnosis

Under load (50 simultaneous connections, wrk -c50), the endpoint fails with ~30% errors. Logs show:

sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 0 reached

Initial engine config: pool_size=5, max_overflow=0. With 50 clients requesting a connection simultaneously, 45 are left without one.

Fix

Configure PgBouncer + tune the SQLAlchemy engine (module 6).

Change in docker-compose: PgBouncer is already in the setup. Point the app to PgBouncer (port 6432) instead of Postgres directly (port 5432).

# docker-compose.yml
app:
  environment:
    DATABASE_URL: postgresql+asyncpg://postgres:postgres@pgbouncer:6432/bookstore

Change in main.py:

engine = create_async_engine(
    "postgresql+asyncpg://postgres:postgres@pgbouncer:6432/bookstore",
    pool_size=20,
    max_overflow=20,
    pool_pre_ping=True,
    pool_recycle=300,
    # CRITICAL: for PgBouncer in transaction mode
    connect_args={"statement_cache_size": 0},
)

statement_cache_size=0 is the module 6 gotcha — without it, prepared statements break randomly.

Measurement

wrk -t4 -c50 -d30s "http://localhost:8000/health/orders"

Endpoint                       | Before p95 | After p95 | Errors
/health/orders (50 conn load)  | 4,500ms    | 38ms      | 30% → 0%

Document

## Problem 5: Pool exhaustion in `/health/orders` under load

**Diagnosis:**
- Initial engine config: `pool_size=5, max_overflow=0`.
- With 50 simultaneous connections, ~45 were left without one.
- Logs: `QueuePool limit reached`.
- Error rate: ~30%.

**Fix:**
- Point to PgBouncer (transaction mode, port 6432).
- Tune the engine: `pool_size=20, max_overflow=20, pool_pre_ping=True, pool_recycle=300`.
- CRITICAL: `statement_cache_size=0` to avoid the prepared statements bug with asyncpg + PgBouncer.

**Before:**
- 50 conn load: p95 = 4,500ms, error rate = 30%.

**After:**
- 50 conn load: p95 = 38ms, error rate = 0%.

**Improvement:** ~120x in p95, 100% reduction in error rate.

**Module applied:** Module 6 (Advanced Connection Pooling).

Problem 6 (bonus): Stale stats after a bulk insert

Diagnosis

Before applying all the fixes, add a bulk insert without ANALYZE to simulate the problem:

# script: simulate_bulk_load.py
import asyncio
import asyncpg
from datetime import datetime, timezone

async def bulk_load():
    conn = await asyncpg.connect("postgresql://...")

    # Insert 1M new orders WITHOUT ANALYZE
    print("Bulk inserting 1M orders...")
    await conn.execute("""
        INSERT INTO orders (customer_id, total, status, created_at)
        SELECT
            (random() * 50000)::INT + 1,
            (random() * 500)::NUMERIC(10, 2),
            'pending',
            NOW() - (random() * INTERVAL '7 days')
        FROM generate_series(1, 1000000)
    """)

    print("Done. NOT running ANALYZE.")
    await conn.close()

asyncio.run(bulk_load())

After running this, a typical query:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days';

Index Scan using idx_orders_created on orders
  (cost=0.43..38.45 rows=12 width=...)
  (actual time=0.024..1234 rows=1000000 loops=1)

rows=12 (estimated) vs actual=1,000,000. An 80,000x underestimation. A terrible plan (Index Scan doing 1M random jumps).

Fix

ANALYZE orders;

And add ANALYZE post bulk-load to the script:

# After the INSERT
print("Running ANALYZE post bulk load...")
await conn.execute("ANALYZE orders")

Measurement

EXPLAIN ANALYZE
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days';

Seq Scan on orders
  (cost=0.00..28432.00 rows=1000000 width=...)
  (actual time=0.012..453 rows=1000000)

Correct plan (Seq Scan because it's 50% of the table). Time: 1234ms → 453ms (~3x).

Document

## Problem 6 (bonus): Stale stats after a bulk insert

**Diagnosis:**
- The bulk load script inserted 1M orders without running `ANALYZE`.
- The planner kept old stats: it estimated `rows=12` for a query that returned 1M.
- Chosen plan: Index Scan with 1M random jumps to disk.

**Fix:**
- `ANALYZE orders` to refresh the stats.
- Add `ANALYZE` to the post bulk-load script as discipline.

**Before:**
- Typical query `WHERE created_at > NOW() - INTERVAL '7 days'`: 1,234ms.
- Plan: Index Scan (incorrectly chosen due to old stats).

**After:**
- Same query: 453ms.
- Plan: Seq Scan (correct for 50% of the table).

**Improvement:** 2.7x. Plus operational discipline: ANALYZE post bulk-load is mandatory.

**Module applied:** Module 7, capsule 03 (Manual ANALYZE).

The final `BENCHMARKS.md`

Here's the complete document that goes in the repo:

# Benchmarks: Bookstore API Performance Optimization

Integrative project of the Database Performance & Query Tuning guide.
Application of techniques from the 8 modules to a real API with planted problems.

## Reproducible setup

- **PostgreSQL:** 16.1
- **PgBouncer:** 1.22 (transaction mode)
- **FastAPI:** 0.110
- **SQLAlchemy:** 2.0.25
- **asyncpg:** 0.29
- **Hardware:** Apple M2 Pro, 16GB RAM, NVMe SSD
- **Data:** 100k books, 1M orders, 3M order_items
- **Load tool:** wrk 4.2.0
- **Repository:** [github.com/USER/bookstore-perf](https://github.com/USER/bookstore-perf)

To reproduce:

```bash
git clone https://github.com/USER/bookstore-perf
cd bookstore-perf
docker-compose up -d
python seed.py
./bench/baseline.sh > before.txt
# Apply fixes
./bench/final.sh > after.txt

Summary results

Endpoint	p50 Before	p95 Before	p99 Before	p50 After	p95 After	p99 After	p95 Improvement
`/books?author_id=42`	245ms	580ms	920ms	22ms	45ms	78ms	12.9x
`/orders?page=1`	18ms	35ms	52ms	8ms	12ms	18ms	2.9x
`/orders?page=10000` (cursor)	3,200ms	5,800ms	8,500ms	8ms	12ms	18ms	480x
`/stats/total-sales`	480ms	920ms	1,400ms	1ms	3ms	5ms	307x
`/search?q=Python`	850ms	1,600ms	2,300ms	14ms	28ms	45ms	57x
`/health/orders` (50 conn load)	42ms	4,500ms	9,800ms	18ms	38ms	65ms	118x + 30% errors → 0%

Analysis of each fix

[Here go the 6 detailed sections we wrote above]

Key learnings

Measure before optimizing. pg_stat_statements prioritized clearly — the N+1 and the COUNT consumed 70% of the total DB time. Attacking the rest without having seen the numbers would have been suboptimal.
The biggest win was cursor pagination. O(n) → O(1) means the endpoint stays fast no matter how deep users paginate. The refactor was moderately complex but the impact fully justified the effort.
Pool tuning is critical under load. Without PgBouncer + statement_cache_size=0, the endpoint failed at 30% under 50 connections. With the right fix, it comfortably sustains 200+ connections.
Post bulk-load stats is mandatory discipline. Problem 6 illustrates that after any massive load, ANALYZE isn't optional — an automated script has to run it.
A materialized view is an elegant solution for stats. A clear trade-off: 5min of delay vs 307x improvement in latency. In the case of business stats, the trade-off is obvious.

Next steps

Optimizations that were NOT applied but would be natural:

Read replica: direct read queries (search, listings) to a replica.
Redis caching layer: stats that change less frequently could have a longer TTL in Redis.
pg_stat_statements in production: a dashboard with alerts for queries that cross thresholds.
Periodic index audit: a monthly cron that reports unused indexes (idx_scan = 0).

Conclusion

The project demonstrates that production performance problems are typically solved with a handful of well-applied techniques, not with exotic tuning. The differentiating factor is methodology: measure → prioritize → attack → re-measure. Without methodology, optimizing is guessing; with methodology, it's engineering.

Generated following NIEVA's Database Performance & Query Tuning guide.


---

## Closing the guide

You reached the end of the guide. What you have now:

- **A public GitHub repo** with optimized code and documentation.
- **A portfolio-worthy `BENCHMARKS.md`** with a quantified table and analysis.
- **The ability to explain each decision**: which problem, which tool, what impact.
- **An internalized workflow**: measure → prioritize → attack → re-measure.
- **Anti-patterns recognizable** in a code review in under 30 seconds.

And across the 8 modules:

1. **Module 1 — Measurement and baseline:** wrk/locust, p50/p95/p99.
2. **Module 2 — EXPLAIN in depth:** plans, cost, scan types, JIT.
3. **Module 3 — Advanced indexing:** composite, covering, partial, expression, GIN.
4. **Module 4 — N+1 with SQLAlchemy:** selectinload, joinedload, the async rules.
5. **Module 5 — Profiling in production:** `pg_stat_statements`, `auto_explain`.
6. **Module 6 — Connection pooling:** SQLAlchemy + PgBouncer + asyncpg gotchas.
7. **Module 7 — Statistics, Autovacuum & Planner:** ANALYZE, MVCC, bloat, pg_repack, cost parameters.
8. **Module 8 — Anti-patterns + Final Project:** OFFSET, COUNT, over-indexing, premature optimization, SELECT *, minor anti-patterns, integrative project.

---

## What comes next

This guide covers database performance from the backend developer's side. To go deeper:

- **Guide #13 — SQL Patterns for Production APIs:** cursor pagination in depth, soft deletes, multi-tenancy with RLS, audit logs, optimistic locking, zero-downtime migrations.
- **Guide #14 — Advanced PostgreSQL for Backend:** deep JSONB, full-text search, partitioning, advanced materialized views, recursive CTEs, useful extensions.

After those two guides, you cover ~95% of everything a senior backend dev is going to need from PostgreSQL in their career.

---

## Your next action

1. **Verify that your repo is public on GitHub.**
2. **Link it in your CV/portfolio.**
3. **Practice explaining it out loud** — the next senior interview is coming.
4. **Apply the pattern** in the production project where you work. There are probably queries at the top of `pg_stat_statements` that deserve an optimization sprint.

---

## Final resources

1. [PostgreSQL Wiki — Performance Optimization](https://wiki.postgresql.org/wiki/Performance_Optimization) — consolidated resources from the official wiki.
2. [Use The Index, Luke!](https://use-the-index-luke.com/) — Markus Winand, a complete online book.
3. [pganalyze blog](https://pganalyze.com/blog) — continuous deep analysis of the ecosystem.
4. [Brandur Leach — Postgres posts](https://brandur.org/) — deep writings from the expert.
5. [PostgreSQL Weekly](https://postgresweekly.com/) — a newsletter to stay up to date.
6. [Stack Overflow — postgresql-performance tag](https://stackoverflow.com/questions/tagged/postgresql-performance) — real cases and solutions.
7. [Github Trending — PostgreSQL projects](https://github.com/trending/postgresql) — modern tools of the ecosystem.

---

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

*End of the guide. Good luck in your next senior interview.*

← PreviousFinal project: setting up the Bookstore with 5 planted problems

Final project: execution and a portfolio-worthy `BENCHMARKS.md`

Before starting: initial state

Problem 1: N+1 in /books?author_id=X

Diagnosis

Fix

Measurement

Document in BENCHMARKS.md

Problem 2: large OFFSET in /orders

Diagnosis

Fix

Measurement

Document

Problem 3: COUNT(*) in /stats and /orders

Diagnosis

Fix

Measurement

Document

Problem 4: Seq Scan in /search

Diagnosis

Fix

Measurement

Document

Problem 5: Pool exhaustion in /health/orders

Diagnosis

Fix

Measurement

Document

Problem 6 (bonus): Stale stats after a bulk insert

Diagnosis

Fix

Measurement

Document

The final BENCHMARKS.md

Summary results

Analysis of each fix

Key learnings

Next steps

Conclusion

Problem 1: N+1 in `/books?author_id=X`

Problem 2: large OFFSET in `/orders`

Problem 3: COUNT(*) in `/stats` and `/orders`

Problem 4: Seq Scan in `/search`

Problem 5: Pool exhaustion in `/health/orders`

The final `BENCHMARKS.md`