Module 5: Query Profiling in Production

In module 4 you killed the bookstore's N+1. You took an endpoint that fired 51 queries and left it at 2, measuring it with echo=True and nplusone. The flow worked for you because you knew in advance which endpoint was suspicious and because you had the app running on your laptop with all the logs on.

In production none of that applies. You have 50 endpoints, thousands of unique queries per hour, and a database you can't turn echo=True on for because it would collapse the log and because, to be honest, you don't even have access to the app server. All you see is a p95 chart that goes up and down, and when it goes up nobody tells you why.

This module teaches you not to fly blind. You'll configure the two tools DBAs use to identify problematic queries in production: pg_stat_statements (which queries consume the most total time and which run the most times) and auto_explain (execution plans captured automatically to the log for queries that exceed a certain threshold). Plus pg_stat_activity for live debugging when something is breaking right now, and a clear picture of when it's worth leaning on external tools like pganalyze or pgwatch2.

Where are we? Where are we going?

You already know (modules 1-4):

Measure latency with wrk, pgbench, and locust (module 1).
Read PostgreSQL plans with EXPLAIN (ANALYZE, BUFFERS) (module 2).
Design advanced indexes the planner actually uses (module 3).
Detect and eliminate N+1 in SQLAlchemy with nplusone and eager loading (module 4).

In this module you'll learn to:

Enable pg_stat_statements from scratch in PostgreSQL 16 (with shared_preload_libraries, restart, CREATE EXTENSION).
Read the four useful views of the top queries: by total time, mean time, number of calls, and blocks read from disk.
Configure auto_explain to capture slow query plans to the log without having to run EXPLAIN manually.
Use log_min_duration_statement as a slow query log complement at the statement level.
Inspect currently active queries with pg_stat_activity (including locks and idle transactions).
Decide when PostgreSQL's built-in tools are enough and when it's worth jumping to pganalyze or pgwatch2.
Apply the complete workflow: reset counters, generate load, read the top, prioritize by impact, and propose a fix.

Afterward (module 6) the focus shifts. pg_stat_statements tells you which queries are expensive, but there's a kind of problem that doesn't show up there: the app running out of connections under load, pool timeouts, prepared statements broken by PgBouncer in transaction mode. That's pooling, and it's covered with that specific tool.

Professional objective

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

Enable pg_stat_statements and auto_explain on any PostgreSQL you have access to (local Docker, RDS, Supabase, Neon).
Identify an app's top 10 queries by four different criteria (total time, mean time, frequency, blocks read) and recognize what kind of problem each ordering suggests.
Capture slow query plans automatically to the log with auto_explain, configuring an appropriate threshold that doesn't fill the disk.
Diagnose a live incident with pg_stat_activity: hung queries, locks, transactions in idle in transaction.
Justify when a company needs pganalyze (or equivalent) vs when pg_stat_statements + homemade scripts are enough.
Reset counters with discipline before and after each change to measure real impact.

This is the first layer of "database observability" that any serious backend team expects you to know how to operate. In senior interviews, "how do you identify the most expensive query in your system?" is a standard question — and the correct answer starts with pg_stat_statements.

Why does this module matter?

Without profiling, optimizing is guesswork.

Without pg_stat_statements the typical team flow is: "the API is slow" → check Datadog → see that the DB span is high → look at the code and pick three endpoints "that could be the problem" → enable detailed logs on one → find nothing → try another → repeat. Days lost diagnosing by intuition.

With pg_stat_statements the flow changes: "the API is slow" → connect to the DB → query the top 5 by total_exec_time → query #1 represents 70% of the system's aggregate time → attack it first. Thirty seconds of diagnosis, a data-based decision, the correct priority.

Three specific reasons this skill sets you apart:

1. It teaches you to prioritize by impact, not by intuition. An individually slow query (high mean_exec_time) may matter less than a fast query executed 10,000 times per minute (high calls). Profiling shows you the aggregate cost, which is what moves the system's latency.

2. It connects you to the real DBA conversation. When a DBA or platform engineer tells you "this query has high shared_blks_read, it doesn't fit in cache", you understand exactly what it means and what to do. Without this capsule, that sentence is jargon.

3. It prepares you for incidents. When the API is down at 3am and someone asks you "what's running?", pg_stat_activity is the first query. Recognizing an idle in transaction transaction that's blocking others is the difference between resolving the incident in 5 minutes or in 5 hours.

A scenario that illustrates the module

Imagine you walk into the office on a Monday and see this in the #incidents channel:

"API down since 7am. p95 at 12 seconds, p99 at timeout. No recent deploy, no obvious traffic spike. The SRE team already ruled out network and app server CPU. We need to know what's happening in the DB."

Before module 5, your only answer would be "I'll look at the app logs and see if there's some weird query". After module 5, you open psql against the production database and make three queries:

pg_stat_activity filtering by state != 'idle' and ordering by duration: you see a transaction from the etl_user user running for 4 hours in idle in transaction state. It's holding locks on the orders table.
pg_stat_statements ordered by total_exec_time DESC: the top 3 are queries from the /orders endpoint. All with a very high mean_exec_time lately, compared to the historical baseline.
auto_explain logs from the last hour: you see that those queries are running with a Seq Scan instead of an Index Scan. Why? Because the ETL transaction blocks the statistics update, the planner is making bad decisions.

Diagnosis in 10 minutes: kill the ETL transaction, the stats update, the planner goes back to using the index, the API recovers. Postmortem documented with concrete data, not with theories.

That's what this module teaches you to do.

Module map

Capsule	Topic	What you'll learn
02	`pg_stat_statements`: installation and fundamentals	Enable the extension from scratch, understand query normalization, key columns
03	Reading `pg_stat_statements`: top queries	The 4 useful queries by ordering and what kind of problem each one suggests
04	`auto_explain`: capturing plans in production	Configure automatic plan capture for slow queries, read them from the log
05	`slow query log` and when to use it	`log_min_duration_statement` as an `auto_explain` complement, cases where it works better
06	`pg_stat_activity` and live locks	Real-time debugging: active queries, locks, idle transactions
07	External tools: pganalyze, pgwatch2, others	Decision matrix: when to invest in SaaS vs set up Grafana vs homemade scripts
08	Project: profiling the bookstore in production	Apply the whole workflow to the module 1 baseline + a prioritized report

The narrative flow is: first you install the main tool (02), you learn to read it in its 4 useful dimensions (03). Then you add auto_explain to have plans automatically (04) and the slow query log as an additional net (05). You move to real-time debugging (06), look at the external ecosystem (07), and close with an integrative project (08) where you apply everything.

Connection with the capstone project (module 8)

The module 8 final project asks you to identify the 3 most expensive queries of the Bookstore API without seeing them in the code. Capsule 08 of this module is the dress rehearsal: you'll load pg_stat_statements, generate synthetic load with wrk (module 1), query the top 3, and propose a prioritized action plan connecting each problematic query with the corresponding module (index → module 3, eager loading → module 4, pool → module 6).

The difference with module 8 is that here you work on the bookstore you already know and where you (roughly) know what problems it has. In module 8 you'll do it on a "deliberately broken" version of the bookstore with additional problems you didn't anticipate. The methodology is the same; the rigor of applying it without prior hints is what changes.

What is NOT covered in this module

❌ Basic EXPLAIN ANALYZE — covered in module 2. We assume you already know how to read plans; here we only capture them automatically.
❌ Index design — covered in module 3. If a problematic query is fixed with an index, you go to module 3.
❌ N+1 with SQLAlchemy — covered in module 4. Here you only identify it in pg_stat_statements (high calls + low mean_time) and defer to module 4 to fix it.
❌ Connection pooling and PgBouncer — covered in module 6. If your bottleneck is connection saturation, pg_stat_statements isn't the tool — it's module 6.
❌ Autovacuum and planner statistics — covered in module 7. Here we mention that a poorly informed planner leads to slow queries, but the deep dive goes there.
❌ Commercial APMs (Datadog, New Relic, Sentry) — out of scope. This guide covers database-level observability. Application APMs are a complementary tool, not a substitute.

Traps to avoid while taking it

1. "I already have Datadog, I don't need this." Datadog (and APMs in general) show you aggregate latency per endpoint, not per specific SQL query. pg_stat_statements operates at a lower level than any APM can reach. They're complementary, not competitors.

2. Skipping the pg_stat_activity capsule because "it's not that important". It's the tool for live incidents. The difference between resolving an outage in 10 minutes or in 3 hours. Don't underestimate it.

3. Configuring auto_explain with too low a threshold. If you set log_min_duration = 0 you capture EVERYTHING to the log, the log explodes, and nobody can read it. Start conservative (1000ms) and lower it gradually.

4. Treating pg_stat_statements as live data. The statistics are cumulative since the last reset (or PostgreSQL restart). Without reset discipline, you're seeing months of history mixed with today's. Capsule 02 insists on this.

5. Jumping to the pganalyze hype without understanding the built-in tools first. pganalyze is excellent, but it's built on top of pg_stat_statements and auto_explain. If you don't understand the primitives, you don't understand what the SaaS shows you. Capsule 07 helps you decide when it's worth it.

Self-evaluation question

Before starting this module, can you answer?

What exactly does pg_stat_statements measure? If your answer is "slow queries", you don't know yet — the correct answer has nuances this capsule clarifies for you.
What's the difference between total_exec_time and mean_exec_time and why does ordering by one tell you different things than ordering by the other?
How do you ask PostgreSQL to capture the execution plan of a slow query without having to be online running EXPLAIN manually?
What do you do if the API is slow right now and you need to know what's running in the DB?

If you're unsure about any, this module answers them for you. If they all seem obvious, you probably already have experience with PostgreSQL profiling and can use this module as a structured refresher.

Evidence of success

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

✅ You have pg_stat_statements and auto_explain running on your local PostgreSQL with the recommended configuration documented in a postgresql.conf (or a Docker equivalent).
✅ You can write, without consulting the capsule, the 4 useful queries to pg_stat_statements (top by total time, mean time, calls, shared_blks_read).
✅ You know how to diagnose a live incident with pg_stat_activity and recognize a problematic idle in transaction transaction.
✅ You have the judgment to decide, in an interview or a planning meeting, whether your team needs pganalyze, pgwatch2, or whether homemade scripts are enough.
✅ You completed the module project (capsule 08) with a prioritized report of the bookstore's problematic queries.

We start in the next capsule

Capsule 02 installs pg_stat_statements from scratch. You'll edit postgresql.conf (or the Docker equivalent), restart PostgreSQL, create the extension, and verify it's capturing data. You'll also understand query normalization, which is the concept that confuses people most at first: why WHERE id = 1 and WHERE id = 2 appear as a single WHERE id = $1 entry in the view.

Before moving on, make sure you have the module 1 bookstore running (with seeded data) and psql available. You'll use both throughout the module.

Resources for the module

PostgreSQL 16 — pg_stat_statements — the extension's official documentation, columns, configuration.
PostgreSQL 16 — auto_explain — the official reference for the automatic plan capture module.
PostgreSQL 16 — pg_stat_activity view — the live activity view.
Lukas Fittl — "Effective query analysis with pg_stat_statements" — a practical overview from the creator of pganalyze.
Hubert "depesz" Lubaczewski — blog — a classic reference for PostgreSQL debugging and profiling in production.
Bruce Momjian — "Database Hardware Selection Guidelines" — general performance context from one of PostgreSQL's core developers.

Module 5 — Database Performance & Query Tuning Guide

← PreviousModule 4 project: eliminating N+1 from the bookstore Next →`pg_stat_statements`: installation and fundamentals