GuideIntermediate
Database Performance & Query Tuning Guide
Master PostgreSQL performance tuning for FastAPI: deep EXPLAIN ANALYZE reading, advanced indexing (composite, covering, partial, expression, GIN), eliminating the N+1 problem in SQLAlchemy, query profiling with `pg_stat_statements` and `auto_explain`, PgBouncer pooling, autovacuum tuning, and refactoring scaling anti-patterns.
- 64
- lessons
- 8
- modules
- English · Spanish
- available in
- Yes
- certificate
- Free
- access
Outcomes
What you'll be able to do
- Read PostgreSQL query plans with confidence (cost, rows, buffers, JIT, scan types) and diagnose why any query is slow
- Design advanced indexes the planner actually uses: composite (with leftmost prefix), covering (INCLUDE), partial (WHERE), expression, and intro to GIN
- Detect and eliminate the N+1 problem in SQLAlchemy using `joinedload`, `selectinload`, and the `nplusone` library
- Profile queries in production with `pg_stat_statements` and `auto_explain` to identify the most expensive queries without guessing
- Configure PgBouncer (transaction vs session mode) and tune async connection pools for FastAPI + asyncpg without collapsing under load
- Tune statistics and autovacuum so the planner makes good decisions and avoid bloat in MVCC tables
- Recognize and refactor scaling anti-patterns: large OFFSET, COUNT(*) on huge tables, over-indexing, premature optimization
- Measure performance improvements with quantified before/after benchmarks using `wrk`, `locust`, and `pgbench`
Before you start
What you need to bring
It's for you if...
- Backend Python developers (intermediate-senior) with FastAPI apps in production showing the first signs of performance issues (slow queries, occasional timeouts)
- Engineers who completed the PostgreSQL & SQLAlchemy guide and want to take what they learned to the next level
- Developers preparing for senior backend technical interviews where EXPLAIN, advanced indexing, and pool sizing are asked
- Teams whose silent latency killer is the N+1 problem and they don't know it yet
- Anyone tired of guessing why a query is slow and ready to start measuring
Requirements and materials
- PostgreSQL & SQLAlchemy guide completed (or equivalent: basic B-tree indexes, basic EXPLAIN, ACID, SQLAlchemy ORM with relationships)
- Functional FastAPI app with real data (>100k rows in at least one table)
- PostgreSQL 14+ installed locally or in Docker
- Familiarity with `psql` and SQLAlchemy 2.0 syntax (`select()`, `Mapped`, `mapped_column`)
- Comfort with async/await in Python
Content
The syllabus, module by module
Open any of them to see its lessons.
- Introduction: Performance Mindset & Benchmarking
- Latency, throughput and percentiles
- Reproducible baselines
- `pgbench`: benchmarking raw PostgreSQL
- `wrk`: fast HTTP load testing
- `locust`: load testing with Python scenarios
- Reporting improvements with `BENCHMARKS.md`
- Module project: the Bookstore API baseline
- Introduction: EXPLAIN ANALYZE in Depth
- `EXPLAIN` vs `EXPLAIN ANALYZE`: the difference that matters
- Reading query plans: structure, order, and metrics
- Cost model and estimates: how the planner decides
- Sequential vs Index scans: when each one wins
- Buffers and JIT: cache, IO, and query compilation
- Query plan visualization tools
- Module project: diagnosing the Bookstore API
- Module 3: Advanced indexing
- B-tree fundamentals revisited with an advanced lens
- Composite indexes: column order and selectivity
- Covering indexes with `INCLUDE`: Index Only Scan
- Partial indexes: indexing only what matters
- Expression indexes and a GIN/JSONB preview
- Index maintenance: bloat, REINDEX, and detecting unused ones
- Module project: Indexing the Bookstore
- Module 4: The N+1 problem with SQLAlchemy
- What is N+1 and why it happens
- Detecting N+1 automatically: logs and the `nplusone` library
- `joinedload` vs `selectinload`: when each one wins
- `subqueryload`: the third strategy you almost never choose
- Relationships and `AsyncSession`: why async changes the rules
- Eager loading anti-patterns: when the cure is worse
- Module 4 project: eliminating N+1 from the bookstore
- Module 5: Query Profiling in Production
- `pg_stat_statements`: installation and fundamentals
- Reading `pg_stat_statements`: top queries
- `auto_explain`: capturing plans in production
- The `slow query log` and when to use it
- `pg_stat_activity` and live locks
- External tools: pganalyze, pgwatch2, and others
- Project: profiling the bookstore in production
- Module 6: Advanced Connection Pooling
- Connection pool fundamentals
- SQLAlchemy pool tuning: five parameters that matter
- asyncpg and AsyncEngine: the driver and the FastAPI patterns
- PgBouncer fundamentals: the external pool and its three modes
- PgBouncer + asyncpg: the prepared statements gotcha and other minor ones
- Pool sizing: formulas, async, and continuous monitoring
- Module 6 project: tuning the bookstore's pool
- Module 7: Statistics, Autovacuum & Planner
- How the planner decides
- Manual ANALYZE and stale statistics
- Extended statistics: when `ANALYZE` isn't enough
- MVCC and bloat: why UPDATE isn't what you think
- Autovacuum: defaults, per-table tuning, and monitoring
- `VACUUM FULL` vs `pg_repack`: how to recover space without downtime
- Cost parameters for SSD + Mini-project: a planner that ignores an index
- Module 8: Anti-Patterns and Final Project
- Anti-pattern: large OFFSET
- Anti-pattern: slow `COUNT(*)`
- Anti-pattern: Over-indexing
- Anti-patterns: Premature optimization + `SELECT *`
- Minor anti-patterns: non-immutable functions, `ORDER BY` without `LIMIT`, massive `IN (...)`
- Final project: setting up the Bookstore with 5 planted problems
- Final project: execution and a portfolio-worthy `BENCHMARKS.md`
Where it fits
This guide is part of something bigger
It's studied inside these programs, with support and dates.
Common questions
What people usually ask
No limit. It's a free guide: come in whenever you like, as often as you like.
No. Modules run from easier to harder, but you can jump to the one you need. Progress is saved per lesson.
Whatever is needed is listed under “What you need to bring”, above. If nothing is listed there, you can start from zero.
In the Club's WhatsApp group, and every two weeks there's a live with an instructor where questions get worked through.
Yes. It's issued automatically once you finish every lesson, with a verifiable code you can share on LinkedIn.
No. This guide is self-paced with no dates. The bootcamp is live, by cohort, with work someone reviews.
Start whenever you like
What students say
These reviews are from enrolled students who completed at least 50% of the course. We moderate reviews only on content grounds (spam, offensive language, personal data), never for being critical or negative.
No approved reviews yet.
Be the first to share your experience!