Module 8: Anti-Patterns and Final Project

You reached the close of the guide. Modules 1-7 gave you tools to diagnose and solve performance problems: measure a baseline, read plans, index, eliminate N+1, profile queries in production, configure pooling, understand the planner. Now you change the lens: instead of learning more tools, you're going to learn to not need them so much.

This module covers the most common anti-patterns in SQL and SQLAlchemy. They're not advanced tricks — they're patterns that generate the problems the previous modules diagnose. If you recognize them while designing, you don't create them; if you don't create them, you don't need to tune later. The best optimization is the one you didn't have to do.

And at the end of the module is the guide's final integrative project: a "Bookstore" API with five performance problems planted on purpose. You're going to apply everything from the 7 previous capsules to diagnose, refactor, and measure improvements with a portfolio-worthy BENCHMARKS.md. It's the deliverable you'll be able to show in your next senior interview.

Where are we? Where are we going?

What you already know (modules 1 through 7):

Measure a baseline with wrk/locust, p50/p95/p99 (module 1).
Read plans with EXPLAIN (ANALYZE, BUFFERS) (module 2).
Index correctly: composite, covering, partial, expression (module 3).
Eliminate N+1 with selectinload, joinedload, the async rules (module 4).
Profile queries in production with pg_stat_statements, auto_explain (module 5).
Configure pooling with SQLAlchemy + PgBouncer + asyncpg (module 6).
Understand the planner: statistics, autovacuum, cost parameters (module 7).

What you're going to build this time:

Two things: a radar to recognize anti-patterns before creating them, and a final project that demonstrates integrated mastery of the whole guide. The radar is prevention; the project is validation.

Why this module comes here:

Anti-patterns presented before learning the tools would be abstract — "don't do this" without understanding why. After modules 1-7, each anti-pattern has a clear technical context: you know exactly which tool diagnoses it and which tool fixes it. That turns "don't do this" into "this creates this concrete problem you already know how to solve, better not to create it."

And the final project goes at the end for the same reason: integrating requires having seen the pieces separately first. You can't consolidate what you haven't learned yet.

Professional objective

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

Recognize the 8 most common anti-patterns in SQL and SQLAlchemy in code reviews, before they reach production.
Refactor a large OFFSET to cursor pagination, a slow COUNT(*) to one of three alternatives with clear trade-offs, and over-indexing with a usage audit.
Apply the "measure first" discipline — Knuth applied to the database. Don't tune what isn't a problem; do tune what pg_stat_statements tells you is a problem.
Produce a final project with a real optimized API and a BENCHMARKS.md with before/after numbers for each change.
Defend your decisions in a code review or interview: why you chose cursor pagination over OFFSET, why you chose a materialized view over COUNT(*), why you removed three indexes from the table.

Why does this module matter?

80% of production performance problems come from a handful of repeated anti-patterns. They're not mysterious bugs or advanced configuration problems — they're patterns that any junior dev creates unintentionally and that progressively degrade the app. The most common ones:

Large OFFSET for pagination: the endpoint works with 100 rows. Six months later with 1M rows, page 50,000 takes 8 seconds. Refactor to cursor pagination = 17x faster.
COUNT(*) on every listing response: the endpoint returns {"items": [...], "total": COUNT(*)}. With 10M rows, each request takes 2 seconds on the COUNT. Three alternatives solve the problem depending on acceptable trade-offs.
Five indexes "just in case": each UPDATE goes from 5ms to 50ms because PostgreSQL updates every index. When the audit reveals that two of the five are never used, removing them lowers latency 60%.
Premature optimization: a junior dev puts complex indexes on an endpoint with 10 calls/day, ignores an endpoint with 10k calls/day without a basic index. The time allocation is the opposite of what the impact demands.

In the real role of a senior backend dev, this module is what differentiates you. Any dev knows how to create an index. Only people with real operational experience know when not to create it, when to remove it, when COUNT(*) is fine and when it isn't, when cursor pagination is overkill and when it's necessary.

For senior interviews, this topic shows up in questions like "you have an API that takes 5 seconds to load the user list — what do you check?". The answer without this module is vague ("add indexes?"). With this module it's methodical: measure a baseline → identify the culprit query with pg_stat_statements → diagnose with EXPLAIN → recognize whether it's N+1, OFFSET, COUNT, or a missing index → apply the specific refactor → re-measure.

A scenario that illustrates the module

Your team launched a bookstore API a year ago. It worked well at the start. In the last three months, latency went up from p95=120ms to p95=2,400ms. Support is getting complaints. The CEO asks you when it'll be fixed.

Without this module, your reflex is to attack problems as they appear, without prioritizing. With this module, the flow is:

Capsule 02 (OFFSET anti-pattern): you identify that the /books?page=N endpoint uses OFFSET (N-1)*20 LIMIT 20. With 5M books and users paginating deep, the large OFFSET is the culprit. Refactor to cursor pagination → that endpoint's p95 goes from 8s to 80ms.
Capsule 03 (COUNT(*) anti-pattern): you notice that the /orders/stats endpoint does SELECT COUNT(*) FROM orders on every request. With 12M orders, that query takes 1.8s. You decide to use a materialized view with a refresh every 5 minutes. Acceptable because "stats with a 5-minute delay" is OK for the business case.
Capsule 04 (over-indexing): you audit indexes with pg_stat_user_indexes. You find three indexes with idx_scan = 0 (never used). You delete them. INSERTs on orders drop from 45ms to 12ms.
*Capsule 05 (premature optimization + SELECT ): you discover that a dev created a 5-column index on a table with 50 calls/day, while the table with 50,000 calls/day doesn't have a basic index. You reassign priorities. You also notice that many endpoints fetch SELECT * when they only use 3 columns — trivial adjustments with cumulative impact.
Capsule 06 (minor anti-patterns): you find WHERE created_at > NOW() - INTERVAL '1 day' that gets evaluated per row. You pass the computed value from the client. Small improvements but they add up.
Capsules 07-08 (final project): you consolidate everything learned in the optimized bookstore. You document improvements with numbers in BENCHMARKS.md. p95 drops from 2,400ms to 180ms.

Result: in a week of focused work, a 13x improvement. Happy CEO. The team learns a replicable pattern. The next time this symptom appears, the methodology is already internalized.

Module map

Capsule	Topic	What you'll learn
01	Module introduction	You're here. Mental framework, the closing scenario.
02	Anti-pattern: large OFFSET	Why `OFFSET 50000` is O(n), refactor to cursor pagination, limitations, reference to guide #13.
03	Anti-pattern: slow `COUNT(*)`	The three alternatives (estimation with `reltuples`, materialized view, incremental counter) and how to choose.
04	Anti-pattern: Over-indexing	The real cost of each index on writes, auditing with `pg_stat_user_indexes`, how to decide what to remove.
05	Anti-patterns: premature optimization + `SELECT *`	The measure-first mindset, the impact of explicit columns on index-only scans.
06	Minor anti-patterns: non-immutable functions, `ORDER BY` without `LIMIT`, `IN` with thousands	Three subtle patterns that add up impact when they accumulate.
07	Final project design: Bookstore API with 5 planted problems	Setup, problems to diagnose, which tools from which module to apply.
08	Project execution + BENCHMARKS.md	The measure → prioritize → attack → re-measure workflow, a portfolio-worthy document template.

Narrative flow: first the anti-patterns (02-06) — the prevention radar. Then the integrative project (07-08) — validation of integrated mastery. The project uses several anti-patterns recognized in 02-06 plus tools from modules 1-7.

Connection with the integrative project

This module IS the integrative project. Capsules 07 and 08 give you the "Bookstore" API as a starting point and the workflow to refactor it. The structure:

Bookstore with 5 planted problems:

/books?author=X endpoint with N+1 (module 4 fixes it with selectinload).
/orders?page=N endpoint with large OFFSET (capsule 02 fixes it with cursor pagination).
/stats/total-sales endpoint with slow COUNT(*) (capsule 03 fixes it with a materialized view).
/search?q=X endpoint with a seq scan (module 3 fixes it with a GIN index).
Untuned pool that saturates at 100 RPS (module 6 fixes it with PgBouncer).

Bonus: stale statistics on a bulk-loaded table (module 7 fixes it with ANALYZE).

Your deliverable:

Public GitHub repo with optimized code.
BENCHMARKS.md with a table endpoint × p50_before × p95_before × p99_before × p50_after × p95_after × p99_after × improvement_%.
Reproducible documentation: PostgreSQL version, hardware/Docker, seeded data, load tool, parameters.

This is what you're going to link in your CV or portfolio.

What is NOT covered in this module

Deep cursor pagination (tiebreakers, bidirectional pagination, multiple ORDER BY): belongs to guide #13, a dedicated module. Here only the basic pattern.
Soft deletes, audit logs, multi-tenancy with RLS: belong to guide #13.
Advanced JSONB indexing, full-text search, partitioning: belong to guide #14.
Replication, sharding, distributed databases: entirely out of scope for this guide.
Anti-patterns specific to other ORMs (Django ORM, Tortoise, Peewee): the module focuses on SQLAlchemy. Most patterns are universal but the code examples are SQLAlchemy 2.0 async.

Traps to avoid while taking the module

1. "I'll memorize the list of anti-patterns and I'm done." Memorizing names doesn't teach you to recognize them in real code. The capsule presents each anti-pattern with before-and-after code + impact metrics. Internalization comes from seeing the patterns contextually, not from lists. When you review code at work, you're going to recognize the symptom.

2. "The final project isn't necessary, I already understood the concepts." The final project measures something different: integration. Knowing 7 separate tools isn't the same as applying them together in a system with real problems. The problems mask each other (a large OFFSET can hide an N+1; when you fix the OFFSET the N+1 appears). You only understand it by practicing.

3. "I'll apply all the anti-patterns as absolute rules." Anti-pattern means "a problematic pattern in most cases." There are legitimate exceptions. For example, SELECT * is defensible in ad-hoc queries for debugging; a small OFFSET (page 1, 2, 3 with 20 items) is perfectly valid. Capsule 05 covers "when each anti-pattern is NOT an anti-pattern."

4. "I'll do the final project just in my head." The most important deliverable is the BENCHMARKS.md with numbers. Without actually measuring it, there's no validation of mastery. And without a public repo, it's not a portfolio. The time investment (8-12 hours to do everything) has a direct return in your next interview.

5. "Optimize EVERYTHING because now I know how." Capsule 05 is going to remind you: optimize what has impact. Knuth applied to the database. The vast majority of code doesn't need optimization; a handful of critical queries do. pg_stat_statements tells you which ones.

Self-assessment question

Before starting this module, can you answer these questions?

Why does OFFSET 50000 LIMIT 20 get slower as the page advances?
What are the three alternatives to COUNT(*) and what trade-off does each one have?
Why does each extra index make INSERTs/UPDATEs slower?
What did Knuth say about premature optimization and how does it apply to DB tuning?
Why can SELECT * block an index-only scan?
What happens with WHERE created_at > NOW() - INTERVAL '1 day' and why does it matter?

If you hesitate on more than three, the module is well calibrated for you. If you know them all, I still recommend doing the final project — the integration is the real value.

Evidence of success

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

In a code review, you identify an anti-pattern in under 30 seconds and propose the refactor.
Your final project repo is on public GitHub, accessible from your CV/portfolio.
Your BENCHMARKS.md has real numbers (not "it feels faster"), reproducible by anyone who clones the repo.
In a senior interview, you can walk through the repo and explain each decision: what problem you diagnosed, what tool you used, what metric you improved.
In your next production project, you anticipate anti-patterns in the design instead of tuning them later.

We start in the next capsule

We start with capsule 02: the large OFFSET anti-pattern. It's one of the three most common in paginated APIs, and one where the symptom (slow pagination) gets confused with "the database is slow" when in reality it's the badly designed query. You're going to see the problem with numbers — the OFFSET's exponential curve — and refactor to cursor pagination with complete code. It's the first anti-pattern because it has the highest aggregate impact on typical APIs.

Before moving on, make sure you have PostgreSQL 16 running, with your FastAPI app connected via SQLAlchemy 2.0 async. We're also going to use wrk to measure latency in some exercises — install it with brew install wrk (macOS) or apt-get install wrk (Linux).

Resources for the module

PostgreSQL Wiki — Don't Do This — official collection of anti-patterns.
Markus Winand — Use The Index, Luke — classic reference for query anti-patterns.
Brandur Leach — Postgres Queries — an architectural view of queries and maintenance operations.
Lukas Fittl (pganalyze) — Common Postgres Performance Issues — top issues in production.
Donald Knuth — "Premature Optimization" — the original quote in context.
Joe Conway — PostgreSQL Performance Anti-Patterns — talks with examples.
Citus Data — Tips for designing performant SQL — an operational guide to design patterns.

Module 8 — Database Performance & Query Tuning Guide

← PreviousCost parameters for SSD + Mini-project: a planner that ignores an index Next →Anti-pattern: large OFFSET