Module 8: Anti-Patterns and Final Project

Anti-patterns: Premature optimization + `SELECT *`

Next →

Donald Knuth, 1974: "premature optimization is the root of all evil." The quote gets repeated so much that it lost its context. Knuth wasn't saying "never optimize" — he was saying "don't optimize without having measured and understood what matters." In 50 years of software, it's still true, and it applies directly to database tuning.

The anti-pattern: a junior dev spends 2 days creating a complex index, optimizing a query, refactoring an endpoint. After their change, latency improved by 5ms on an endpoint with 50 calls/day. Meanwhile, the endpoint with 50,000 calls/day has a Seq Scan that takes 800ms and nobody touched it. The priorities are inverted — optimizing the low-impact one, ignoring the high-impact one.

This anti-pattern is about mindset: your intuition about "which query is slow" or "which endpoint matters" is almost always wrong. The correct methodology is measure first, prioritize by impact, attack the top N. pg_stat_statements already gives you the prioritized list — you just have to look at it.

In this capsule we also cover SELECT * — a visible and simple anti-pattern that has real impact: it blocks index-only scans, transfers data that isn't used, breaks schema contracts when columns are added. Two anti-patterns that share the idea of discipline over instinct.

Premature optimization: the pattern

The junior and the senior facing the same problem.

Junior

"The /users/{id} endpoint takes 200ms. I'm going to create a composite index with all the columns it returns, optimize the JOIN, add caching in Redis."

After 2 days of work: latency dropped to 180ms. A marginal improvement. Meanwhile, /orders/list with 10x more calls/day still takes 1.5 seconds.

The junior chose by intuition: "this endpoint looks complex, I'm going to optimize it." Without measuring what made it slow, without measuring whether it was the most impactful one.

Senior

"Which are the 5 queries that consume the most total time? I'm going to pull them from pg_stat_statements and attack the top."

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    ROUND((100 * total_exec_time / SUM(total_exec_time) OVER ())::NUMERIC, 2) AS pct_total_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Typical output:

query                                    | calls    | total_time | mean_time | pct
SELECT * FROM orders WHERE user_id = ?   | 234567   | 12,345 sec | 52 ms     | 38%
SELECT * FROM products WHERE active = T  |  56789   |  4,123 sec | 73 ms     | 13%
SELECT COUNT(*) FROM events              |  12345   |  3,456 sec | 280 ms    | 11%
...

The first query consumes 38% of all the DB time. If you go from 52ms to 26ms (a 50% improvement), you free up 19% of the total DB time. That optimization is worth more than the 50 marginal optimizations of low-impact queries.

The senior attacked #1, not the one that "looked complex."

Knuth in context: when you SHOULD optimize

Knuth's full quote:

"We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%."

The "97% of the time don't optimize" is the famous part. The forgotten part is "don't pass up the opportunities in the critical 3%." Applied to the database:

Don't optimize (97%):

  • Queries that run rarely.
  • Queries that are already <50ms and aren't a bottleneck.
  • Micro-optimizations that change milliseconds in a total of seconds.
  • "Just in case" without measuring.

Do optimize (3%):

  • Queries in the top 5 of pg_stat_statements.
  • Queries in the critical path of checkout, login, or any critical flow.
  • Queries with a clear regression after a change.
  • Queries that pg_stat_statements shows with high total time.

The difference is which 3%. Without measuring, you optimize the 3% that looks interesting. With measuring, you optimize the 3% that matters.

The measure → prioritize → attack workflow

Step 1: measure a baseline.

Enable pg_stat_statements (module 5) if you don't have it already:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- And add to postgresql.conf: shared_preload_libraries = 'pg_stat_statements'

Reset the stats to have a clean window:

SELECT pg_stat_statements_reset();

Wait 24-48 hours of production traffic.

Step 2: identify the top.

SELECT
    query,
    calls,
    total_exec_time / 1000 AS total_seconds,
    mean_exec_time AS mean_ms,
    rows / GREATEST(calls, 1) AS avg_rows_returned
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'  -- exclude meta
ORDER BY total_exec_time DESC
LIMIT 10;

Step 3: classify the top.

For each query in the top, determine the category:

  • Frequent and fast: high frequency, low mean_time (10-20ms). High total = aggregate impact. Optimizing 50% of mean_time = a large aggregate win.
  • Infrequent and slow: low frequency, high mean_time (several seconds). Each call is slow but the aggregate impact is small. Probably don't prioritize.
  • Frequent and slow: high frequency + high mean_time. Top priority: high aggregate impact + each individual call also affects UX.
  • Infrequent and fast: low priority, almost never the problem.

Step 4: diagnose and attack the top.

For query #1 of the top, apply the flow from modules 2-7:

  1. EXPLAIN ANALYZE to see the plan.
  2. Identify the bottleneck: Seq Scan? Sort? Nested Loop with N+1?
  3. Apply the corresponding fix: index, refactor, ANALYZE.
  4. Re-measure with pg_stat_statements (after a reset and a new window).

Step 5: iterate.

After fixing #1, the ranking changes. What was #2 can now be #1 (because you fixed the biggest one, others become proportionally more significant). Repeat.

SELECT *: the capsule's other anti-pattern

SELECT * FROM orders WHERE id = X is readable and convenient. It's also an anti-pattern for three distinct reasons.

1. It blocks index-only scans

An index-only scan is when PostgreSQL answers a query using only the index, without touching the table. It requires that all the columns the query returns be in the index.

-- Schema
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC,
    category TEXT,
    stock INTEGER,
    description TEXT
);

CREATE INDEX idx_products_name_price ON products(name, price);

-- Query that CAN use an index-only scan
SELECT name, price FROM products WHERE name = 'Foo';
-- Plan: Index Only Scan ✓

-- Query with SELECT * — CANNOT use an index-only scan
SELECT * FROM products WHERE name = 'Foo';
-- Plan: Index Scan + heap fetch

An Index Only Scan avoids going to the heap (the table) — it's significantly faster on queries that return many rows. SELECT * blocks it automatically because you'd have to go to the heap to fetch columns that aren't in the index.

2. It transfers unnecessary data

-- Table with a large BLOB column
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,           -- can be MB
    full_text_index TSVECTOR  -- can be MB
);

-- List titles to show in the UI
SELECT * FROM documents WHERE category = 'recent';
-- Fetches title + content + full_text_index — unnecessary MB
-- over the network between PostgreSQL and the app

-- Better
SELECT id, title FROM documents WHERE category = 'recent';

On queries that return many rows (cursor pagination, lists), the difference between fetching 3 columns vs all of them can be from hundreds of KB to several MB per response.

3. It breaks contracts when you add columns

# Anti-pattern: ORM with `model_to_dict()` or automatic serialization
@router.get("/orders")
async def list_orders():
    orders = await db.execute(select(Order))
    return orders.scalars().all()
    # Returns ALL the columns as JSON

When you add an internal_notes column (private to the team), you accidentally expose it in the JSON. The frontend receives it. A tester finds that private data is in the response. Bug.

# Better: explicit schema
class OrderResponse(BaseModel):
    id: int
    total: Decimal
    status: str
    # internal_notes is NOT here

@router.get("/orders")
async def list_orders() -> list[OrderResponse]:
    orders = await db.execute(select(Order))
    return [OrderResponse.model_validate(o) for o in orders.scalars()]

The Pydantic schema acts as a contract: it only exposes what's declared.

When SELECT * is acceptable

  • Ad-hoc queries for debugging in psql.
  • Loading a complete object when the ORM is going to use all the columns in the subsequent logic.
  • Small tables with all-small columns: the cost of transferring all the columns is negligible.

In public APIs, SELECT * is almost always wrong.

How to apply the discipline of explicit columns

In SQLAlchemy 2.0

# Anti-pattern
from sqlalchemy import select
result = await db.execute(select(Order))  # equivalent to SELECT *

# Better: explicit columns
result = await db.execute(
    select(Order.id, Order.total, Order.status)
)
# Returns tuples, not full objects

Or with load_only for an ORM with specific properties:

from sqlalchemy.orm import load_only

result = await db.execute(
    select(Order).options(load_only(Order.id, Order.total, Order.status))
)
# Returns Order objects but with only those columns loaded

In Pydantic responses

# Response schema
class OrderListItem(BaseModel):
    id: int
    total: Decimal
    status: str

# Endpoint
@router.get("/orders", response_model=list[OrderListItem])
async def list_orders():
    result = await db.execute(
        select(Order.id, Order.total, Order.status)
    )
    return [OrderListItem(id=r.id, total=r.total, status=r.status)
            for r in result]

FastAPI automatically validates that each item complies with the schema. If you add an internal_notes column in the database, it doesn't leak accidentally.

In raw SQL

-- ❌
SELECT * FROM orders WHERE customer_id = 123;

-- ✅
SELECT id, total, status, created_at FROM orders WHERE customer_id = 123;

When you use raw SQL via text(), always list explicit columns.

Traps and common mistakes

1. "I'm going to optimize the whole app prophylactically."

No. Only optimize what pg_stat_statements tells you is a problem. Optimizing everything is premature optimization in another form.

2. Ignoring pg_stat_statements and trusting intuition.

Your intuition about which endpoint is slow is almost always wrong. pg_stat_statements doesn't lie. Look at the numbers before deciding.

3. Optimizing before measuring a baseline.

If you didn't measure before, you can't know whether your change improved or worsened things. And if you have an incident afterward, you don't know whether it was your change or something else. Always a baseline before a change.

4. Using SELECT * "because after all the frontend uses everything."

Except when you add internal columns, sensitive data, or large columns the frontend doesn't show. An explicit schema is protection, not just performance.

5. SELECT * with an ORM because "the ORM handles it."

The ORM fetches all the columns and serializes them all. If you add a password_hash column (worst case), the ORM will put it in the JSON. The Pydantic schema is the barrier.

6. Thinking that SELECT * and SELECT col1, col2, ... have the same plan.

At the planner level, it's almost always similar. The difference is in:

  • Whether an index-only scan is possible (not if you fetch non-indexed columns).
  • Bytes transferred over the network.
  • App memory processing.

7. Knuth as an excuse not to improve.

"Premature optimization is the root of all evil" doesn't mean "never optimize." It means "optimize what matters." If your #1 endpoint takes 2 seconds in p95, that's not premature — it's necessary.

8. Optimizing and forgetting the commit message.

When you optimize something, make it clear what changed and what impact it had in the commit message. Teams that don't document changes re-introduce the problems in future refactors.

git commit -m "Add index idx_orders_customer_status

Reduces /orders/by-customer p95 from 850ms to 65ms.
pg_stat_statements showed this as #1 query (38% of total DB time).
"

Exercise: prioritize and attack the top

Setup: a database with pg_stat_statements active (assume it is — if not, enable it in postgresql.conf and restart).

Step 1: simulate load.

If you have a real app, generate realistic traffic for 30 minutes. If not, simulate with a script:

import asyncio
import asyncpg
import random

async def simulate_traffic(pool):
    for _ in range(1000):
        async with pool.acquire() as conn:
            choice = random.random()
            if choice < 0.5:
                # 50%: frequent and fast query
                await conn.fetch("SELECT * FROM users WHERE id = $1",
                                 random.randint(1, 10000))
            elif choice < 0.8:
                # 30%: frequent and slow query (Seq Scan)
                await conn.fetch("SELECT * FROM events WHERE event_type = $1",
                                 'click')
            elif choice < 0.95:
                # 15%: infrequent and fast query
                await conn.fetch("SELECT * FROM products LIMIT 10")
            else:
                # 5%: infrequent and very slow query
                await conn.fetch("SELECT COUNT(*) FROM orders")

async def main():
    pool = await asyncpg.create_pool('postgresql://...')
    await simulate_traffic(pool)

asyncio.run(main())

Step 2: identify the top.

SELECT
    query,
    calls,
    total_exec_time / 1000 AS total_sec,
    mean_exec_time AS mean_ms,
    ROUND((100 * total_exec_time / SUM(total_exec_time) OVER ())::NUMERIC, 1) AS pct_time
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 5;

Step 3: classify each one.

For each query in the top 5, assign:

  • Category (frequent+fast, infrequent+slow, frequent+slow, infrequent+fast).
  • Priority (high, medium, low).
  • Proposed action (attack, monitor, ignore).

Step 4: attack #1.

For the query with the highest pct_time:

  1. EXPLAIN ANALYZE.
  2. Identify the problem (Seq Scan? Missing index? N+1?).
  3. Apply the fix.
  4. Reset stats, wait for new traffic, re-measure.

Step 5: document the impact.

## Optimization applied: `events.event_type` index

**Before:**
- Query: `SELECT * FROM events WHERE event_type = ?`
- Calls: 234,567
- Total time: 12,345 seconds
- Mean: 52ms
- pct_time: 38%

**Change:**
`CREATE INDEX CONCURRENTLY idx_events_type ON events(event_type);`

**After:**
- Mean: 8ms
- pct_time: 6%

**Impact:** freed up ~32% of the total DB time. The `/events/list` endpoint's p95 dropped from 180ms to 25ms.
See discussion

Lessons from the exercise:

  1. The top 5 typically represents 60-80% of the total DB time. Attacking the top has a disproportionate impact.
  2. The most frequent queries with a "not terrible" mean time usually win. A query with 100k calls × 20ms (2,000s) consumes more than one with 100 calls × 5,000ms (500s).
  3. mean_time alone isn't enough: you have to multiply by calls. That's the impact metric.
  4. pg_stat_statements changes after each major deploy. Reset and re-evaluate after significant changes.
  5. The process is iterative. You attack #1, then #2 (what was #2 may not be now). It's not "fix all at once."

Anti-junior pattern: looking at the query with the highest mean_time. The one that takes 5 seconds per call is probably not the problem if it's only called 10 times a day. The 50ms one with 100k calls/day is the problem.

Summary and next step

What you learned:

  • Premature optimization = optimizing without measuring, based on intuition. It's counterproductive.
  • The correct methodology is measure a baseline → prioritize by aggregate impact (pg_stat_statements) → attack the top → re-measure.
  • Knuth in context: 97% of the time don't optimize, but don't miss the critical 3%. Without measuring, you choose the wrong 3%.
  • SELECT * blocks index-only scans, transfers unnecessary data, and breaks contracts when you add columns.
  • An explicit schema (Pydantic) is protection, not just performance.
  • Discipline over instinct: measure → prioritize → attack is a repeatable methodology. Intuition isn't.

Before moving on, you should be able to:

  • Pull the top 5 queries from pg_stat_statements with pct_time computed.
  • Classify each query as frequent/slow and prioritize.
  • Apply load_only or explicit columns in SQLAlchemy.
  • Justify a Pydantic schema as a contract barrier (not just validation).

In the next capsule you're going to see three "minor" anti-patterns that individually look like details but together add up impact: non-immutable functions in WHERE (that get evaluated per row), ORDER BY without LIMIT (that forces the planner to sort everything), and IN (...) with thousands of values (that degrades the plan). Three patterns that appear frequently in code review and are worth recognizing at first glance.

Resources

  1. Donald Knuth — "Structured Programming with go to Statements" — the original paper with the quote in context.
  2. PostgreSQL Docs — pg_stat_statements — the complete official reference.
  3. Brendan Gregg — "Methodology of Performance Analysis" — the USE and RED methodologies for measure-first.
  4. PostgreSQL Docs — Index-Only Scans — an explanation of the feature.
  5. Markus Winand — "SELECT * is Harmful" — analysis of the impact on index-only scans.
  6. SQLAlchemy 2.0 — load_only — loading selective columns.
  7. Pydantic 2 — Models and validation — schemas as contracts.

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

← PreviousAnti-pattern: Over-indexingNext →Minor anti-patterns: non-immutable functions, `ORDER BY` without `LIMIT`, massive `IN (...)`