Module 1: Performance Mindset & Benchmarking

Introduction: Performance Mindset & Benchmarking

Description

There's a universal temptation when an API feels slow: open the code, look at the suspicious endpoint, add an index, move a query around, "optimize" whatever you think the bottleneck is. If it feels faster afterward, you leave it. If it doesn't, you keep poking.

That isn't tuning. That's guessing with admin privileges.

This module teaches you the opposite mindset: you don't optimize what you don't measure. Before you touch a single index or change a single SELECT, you need a reproducible baseline — concrete numbers about how your API behaves today. Without a baseline there is no tuning, there's cargo cult on steroids.

By the end of this module you'll have your measurement kit installed (pgbench, wrk, locust), you'll understand why the average lies and percentiles tell the truth, and you'll produce the BENCHMARKS.md for this guide's capstone project: the "Bookstore" API, with all its pre-planted problems, measured in p50/p95/p99 before you touch anything.

Where are we in the guide?

This is Module 1 of the Database Performance & Query Tuning Guide — guide #12 of the Backend Python Developer with FastAPI path, and the first of the Data Layer sub-track.

The full guide has 4 blocks:

Block 1: Fundamentals and Diagnosis (Modules 1-2)     ← YOU ARE HERE
Block 2: Indexing and ORM (Modules 3-4)
Block 3: Profiling and Pooling (Modules 5-6)
Block 4: Systemic Tuning and Anti-Patterns (Modules 7-8)

Whatever you do in modules 2-7 always ends with the same question: "did this improve p95?". If you don't install the tools and the mindset now, the following modules become purely theoretical. Here you build the measure → tune → measure loop you'll use seven more times before finishing the guide.

The fundamental principle: "no optimization without measurement"

Brendan Gregg puts it this way in Systems Performance: any optimization technique you apply without measuring first produces one of these three outcomes, all equally bad:

A real improvement, but you don't know it because you never measured the "before".
No improvement at all, but you think there is one because "it feels faster now".
A regression in another dimension (you improve latency but kill throughput, or you fix p50 but p99 doubles) and you don't detect it.

The difference between a junior backend dev and a senior one isn't knowing more indexes or more PostgreSQL flags. It's knowing that a change without measurement isn't an improvement — it's an opinion.

The correct loop

1. MEASURE baseline ──────────► current p50/p95/p99 documented
        │
        ▼
2. DIAGNOSE ──────────────────► EXPLAIN, pg_stat_statements, profiling
        │
        ▼
3. HYPOTHESIZE ───────────────► "I think a composite index on (a,b) fixes it"
        │
        ▼
4. APPLY change ──────────────► ONE change at a time
        │
        ▼
5. RE-MEASURE ────────────────► compare p50/p95/p99 against baseline
        │
        ▼
6. REPORT ────────────────────► "p95 850ms → 120ms (-86%)" or "didn't move the needle, revert"
        │
        ▼ (back to 2 with the next bottleneck)

Every module in this guide is a tool for step 2 (diagnose) or step 4 (apply). Module 1 trains you in steps 1, 5 and 6 — the ones most people skip.

Professional objective

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

✅ Distinguish latency from throughput, and explain why optimizing one can hurt the other
✅ Report performance with p50/p95/p99 percentiles (and argue why the average lies)
✅ Establish a reproducible baseline of a FastAPI app before touching the code
✅ Use pgbench for pure PostgreSQL benchmarks (without the app on top)
✅ Use wrk for fast HTTP load testing and locust for complex Python scenarios
✅ Produce a BENCHMARKS.md with reproducible context and honest numbers
✅ Identify whether your workload is read-heavy, write-heavy or mixed, and what that changes

These aren't "extra" skills — they're the shared language of any serious performance conversation with a staff engineer, an SRE, or a DBA.

Why does this module matter?

This happens in production all the time:

You show up Monday and the product team reports that "the app is slow". Anxious stakeholders, someone suggests "maybe we just need more servers?". Your CTO asks you to investigate. You open New Relic / Datadog and see a graph going up. What's next?

If your first instinct is to open the code and start reading queries: you're going to lose hours, you're going to apply changes without knowing whether they helped, and you're going to learn very little for next time.

If your first instinct is to measure — which endpoints specifically are slow, at which percentiles, under what load pattern — you have a methodology that scales. You solve the problem and you learn something reusable.

This module trains you for the second instinct. You'll apply it:

When an endpoint starts showing up in alerts and you need to confirm first that it isn't a false positive
When a product manager asks you to "make the app faster" and you have to translate that vague phrase into a measurable goal
In senior technical interviews, where "how would you investigate a slow endpoint?" is a classic question
When you lead a migration (to another DB, to microservices, to a new Python version) and you need to prove there was no regression

The mindset transfers. The tools are interchangeable (tomorrow it might be k6 instead of wrk), but the discipline of measuring before touching is invariant.

A scenario that illustrates the module

Imagine it's your first day at a mid-size startup. Your manager tells you:

"The /books?author=tolkien endpoint takes 'a long time'. People are complaining. Optimize it."

The wrong way to proceed (the one you're learning to avoid):

You open the endpoint's code.
You see it does a JOIN between books and authors.
You decide "surely there's a missing index on author_id".
You add it, deploy, and tell your manager "done".
A week later people are still complaining. The index solved nothing.

The right way (what you'll do by the end of this module):

Reproduce the problem locally with representative data: wrk -t4 -c50 -d30s "http://localhost:8000/books?author=tolkien". Output: p50=45ms, p95=2,100ms, p99=8,400ms. Confirmed: the problem is real and it lives in p95+.
Document the baseline in BENCHMARKS.md: PostgreSQL version, hardware, table size, pool configuration. Reproducible.
Identify with pg_stat_statements that the endpoint fires 1 query to books + 1 query to authors + N queries to reviews (a classic N+1 — but you'll understand that in module 4). For now you only measured it.
You jump to module 2 with this specific endpoint as a case study for EXPLAIN ANALYZE.
You apply the fix (eager loading), re-measure, and report: "p95 2,100ms → 145ms (-93%)". Numbers, not impressions.

Module 1 trains you in steps 1 and 2 — the most underrated ones, but the ones that keep you from losing weeks to false positives.

Module map

Capsule	Topic	What you'll learn
02	Latency, throughput and percentiles	Why the average lies, what p50/p95/p99 are, latency vs throughput, bimodal distributions
03	Reproducible baselines	What an honest baseline is, context (version, hardware, data), warmup, multiple runs
04	`pgbench` for pure PostgreSQL	When to isolate the database, custom scripts, TPS, reading pgbench output
05	`wrk` for HTTP load testing	Smoke / load / stress, reading the latency histogram, basic Lua scripts
06	`locust` for Python scenarios	Complex user flows, scaling users, headless mode, a clean locustfile.py
07	Reporting improvements with `BENCHMARKS.md`	Template, before/after tables, what to say and what not to say with benchmarks
08	Project: baseline of the Bookstore API	Simplified API with known problems + your initial BENCHMARKS.md

Learning flow: First you internalize the mindset and percentiles (02). Then you learn to build reproducible baselines (03). Then you install and master the three tools in order of complexity: pgbench (pure DB) → wrk (simple HTTP) → locust (complex scenarios) in 04, 05 and 06. You learn to communicate what you measured in 07. You close with the capstone project in 08, where you produce the real baseline you'll optimize in modules 2-7.

Connection with the capstone project

The consolidating project for the whole guide is the Bookstore API: a FastAPI app with performance problems planted on purpose (obvious N+1, large OFFSET, COUNT(*) on big tables, an untuned pool). In module 8 you'll optimize it; here in module 1 you'll measure it in its broken state.

Your concrete deliverable at the end of the module (capsule 08) is:

A cloned repo with the simplified version of the Bookstore API running locally (3-4 endpoints with an obvious N+1 + a table with a large OFFSET).
A BENCHMARKS.md file with the "Baseline (Module 1)" section complete: environment context, p50/p95/p99 per endpoint, maximum throughput before errors.
Three measurement scripts versioned in the repo: bench/pgbench-baseline.sh, bench/wrk-baseline.sh, bench/locustfile.py.

Those numbers are the line against which every optimization in modules 2 through 7 will be compared. Each module will add an "After Module X" section to your BENCHMARKS.md.

Important note: the complete version of the Bookstore API (with the 5 problems listed in STRATEGY.md) is delivered in module 8. In this module you work with a simplified version — what matters is that you practice the flow, not that the app is identical to the final one.

What is NOT covered in this module?

An explicit list, with reasons:

❌ Reading EXPLAIN ANALYZE — That's all of module 2. Here we only measure external behavior (how slow the endpoint is), not internally why PostgreSQL does what it does.
❌ Index design — That's module 3. If you discover a slow query here, you flag it and keep measuring. Don't start "fixing" it yet.
❌ N+1 detection with nplusone — Module 4. Your baseline may have an N+1, but you're not going to diagnose it as such yet.
❌ pg_stat_statements and auto_explain — Module 5. Here we use external benchmarks (HTTP / pgbench), not PostgreSQL's internal profiling.
❌ PgBouncer / pool tuning — Module 6. SQLAlchemy's default pool is enough for an honest baseline.
❌ Autovacuum, statistics, planner internals — Module 7.
❌ Anti-patterns and refactors — Module 8.

Golden rule of this module: measure, document, touch nothing. The temptation of "while I'm here, let me add this obvious index" is exactly what the module teaches you to resist. Write it down in a list of hypotheses and hold off until the corresponding module.

Traps to avoid while taking the module

1. "This is basic, I'll skip it and start with EXPLAIN."

Tempting, but counterproductive. People who skip this module end up doing "casual" benchmarks on localhost with a warm cache and reporting numbers that don't hold up. Brendan Gregg, Marc Brooker and any senior engineer with production scars will tell you the same thing: the bottleneck isn't in what to tune, it's in what to measure. Give this module the time it deserves.

2. "I'll use the average because that's what my manager understands."

Your manager will understand your report if you explain it well. And understanding percentiles is part of being senior — if you don't learn it now, you'll learn it under pressure when someone asks you in a post-mortem why nobody noticed p99 was at 12 seconds. Better to learn it now.

3. "I'll measure just once, the numbers 'are what they are'."

A single run can lie to you for a thousand reasons: a warm cache, a GC pause right at that moment, something else running on your machine. You'll learn to run multiple runs, discard the first one (warmup), and report the median of N runs. If you only run once, you're not measuring — you're generating noise.

4. "I'll try wrk and locust and pgbench to 'see which one I like best'."

They're not interchangeable. pgbench isolates the DB; wrk measures the full API with little configuration; locust measures complex scenarios with multiple steps. Each one answers a different question. Capsules 04, 05 and 06 teach you when to use each one — it's not preference, it's diagnosis.

5. "I'll optimize while I measure."

No. The rule is: in this module you only measure. If you find something "obvious", you write it in HYPOTHESES.md or a comment and move on. Optimization starts in module 2.

Self-assessment question

Before starting this module, try to answer honestly:

What's the p95 of your current API (the one you have in production or in a side project)?
If your manager asks you "is it faster after the change you made on Monday?", can you answer with a number or only with a feeling?
Which matters more for user experience: an average of 200ms, or a p99 of 500ms? Why?
If someone asked you to reproduce your last benchmark, could you? Is the context documented?
Do you know the difference between pgbench, wrk and locust? When would you use each one?

If you hesitated on any of them, this module is for you. If you answered all of them confidently, read it anyway — you'll find nuances that only come with scars.

Signs of success

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

✅ You can explain to a colleague why the average is misleading with a concrete example (bimodal distributions)
✅ You have pgbench, wrk and locust installed, and you can run a basic benchmark with each one without consulting the docs
✅ Your BENCHMARKS.md documents enough context for someone else to reproduce your numbers
✅ You report improvements (or the absence of improvements) with concrete percentiles, not with phrases like "it feels faster"
✅ When someone says "let's add an index", your first question is "which specific number are we trying to move?"

How to get the most out of this module

Estimated time: 1-1.5 hours reading + 1-2 hours running the benchmarks in capsules 04, 05, 06 and 08.

Minimum recommended setup:

macOS or Linux (Windows works on WSL2)
Python 3.11+
PostgreSQL 16+ installed locally or in Docker
Basic familiarity with psql and SQLAlchemy 2.0

You install the tools in their respective capsules — you don't need everything ready right now. Each technical capsule starts with its installation section.

We start in the next capsule

Capsule 02 — Latency, throughput and percentiles — is the most theoretical of the module, and the only one where you don't write a single line of code. But it's the capsule you'll cite the most afterward: every time someone shows you a benchmark using the average, you'll remember it.

Before moving on, make sure you can answer: why does a senior dev insist on p95 when a junior settles for the average?

If you don't have a clear answer, perfect — that's what the next capsule is for.

Summary

You don't optimize what you don't measure. Any technique applied without a baseline produces invisible improvements, imaginary improvements, or unnoticed regressions.
The correct loop is: measure → diagnose → hypothesize → apply ONE change → re-measure → report.
This module covers steps 1, 5 and 6 (measure and report) — the ones most people skip.
You'll produce a reproducible BENCHMARKS.md for the simplified Bookstore API that is the baseline of the whole guide's capstone project.
In this module you only measure — the temptation of "while I'm here, let me fix it" is exactly what the module teaches you to resist.

Resources for the module

Brendan Gregg - Systems Performance: Enterprise and the Cloud (2nd Ed.) — the bible of performance engineering. Chapters 1-2 are recommended reading for this entire module.
PostgreSQL Documentation - pgbench — PostgreSQL's official benchmarking tool. Reference for capsule 04.
wrk on GitHub — official repository of the HTTP benchmarking tool most used by SREs. Reference for capsule 05.
Locust Documentation — load testing in Python with complex scenarios. Reference for capsule 06.
Gil Tene - "How NOT to Measure Latency" — a mandatory talk on why almost every latency benchmark is done wrong. 40 min, worth every minute.
Marc Brooker - "Tail latency might matter more than you think" — why p99 matters even in "low priority" systems.
Andy Pavlo - CMU 15-445 Database Systems, Lecture on Query Performance — free academic material on database performance.

Module 1 — Database Performance & Query Tuning Guide

Next capsule: Latency, throughput and percentiles — the language you'll use to talk about performance for the rest of your career.

Next →Latency, throughput and percentiles