DEV Community

Sir Max
Sir Max

Posted on

3 Database Query Patterns That Kill Performance (And How to Fix Them)

#webdev #sql #tutorial #database

3 Database Query Patterns That Kill Performance (And How to Fix Them)

I've spent more hours than I'd like to admit staring at slow database queries in production. Every time, I think "this should be fast" — and every time, I'm wrong. Here are three patterns I've stumbled over repeatedly, plus the fixes that actually worked.

1. The N+1 Problem: Your ORM Is Lying to You

This is the classic. You fetch a list of users, then loop through and fetch each user's orders — one query at a time.

# ❌ The N+1 trap
users = db.execute("SELECT id, name FROM users LIMIT 100").fetchall()
for user in users:
    orders = db.execute(
        "SELECT * FROM orders WHERE user_id = ?", [user.id]
    ).fetchall()
    # 101 queries total. Oof.
Enter fullscreen mode Exit fullscreen mode

The ORM makes this look innocent. Django's user.order_set.all() in a template loop? That's N+1. SQLAlchemy's lazy-loading relationships? Same thing.

The Fix: JOIN or Preload

# ✅ One query
rows = db.execute("""
    SELECT u.id, u.name, o.id as order_id, o.total, o.created_at
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.id
    WHERE u.id IN (SELECT id FROM users LIMIT 100)
    ORDER BY u.id, o.created_at
""").fetchall()

# Group in application code
from collections import defaultdict
user_orders = defaultdict(list)
for row in rows:
    user_orders[row['id']].append(row)
Enter fullscreen mode Exit fullscreen mode

If you're stuck with an ORM, use select_related() / prefetch_related() in Django or joinedload() / selectinload() in SQLAlchemy. Just know what SQL they generate — run EXPLAIN and check.

2. LIKE '%wildcard%' Searches: The Index Killer

A leading wildcard in LIKE (%something) prevents any B-tree index from being used. The database has to scan every row.

-- ❌ Full table scan — index is useless
SELECT * FROM articles WHERE title LIKE '%postgresql%';
Enter fullscreen mode Exit fullscreen mode

On a table with a few thousand rows, you might not notice. At 500K rows? Your API timeout is gone.

The Fix: Full-Text Search

PostgreSQL has built-in full-text search. MySQL has it too. Use it.

-- PostgreSQL: create a tsvector column and index it
ALTER TABLE articles ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- Query with proper ranking
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'postgresql performance') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

For simpler cases, ILIKE with a trigram index (pg_trgm) handles partial matches reasonably well:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_articles_title_trgm ON articles USING GIN(title gin_trgm_ops);

-- Now this uses the index
SELECT * FROM articles WHERE title ILIKE '%postgresql%';
Enter fullscreen mode Exit fullscreen mode

When to NOT do full-text search: If your search is always prefix-based (WHERE title LIKE 'postgresql%'), a regular B-tree index works fine. Don't over-engineer.

3. Missing Composite Indexes: One Column at a Time Won't Cut It

I've seen this everywhere: a table with separate indexes on user_id, status, and created_at, and a query like:

SELECT * FROM orders
WHERE user_id = 42 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

PostgreSQL might use the user_id index and then filter status manually. Or it might scan the status index and filter user_id. Either way, it's sorting in memory and throwing away rows.

The Fix: Composite Index That Matches the Query

-- ✅ One index covers the full query pattern
CREATE INDEX idx_orders_user_status_created
    ON orders(user_id, status, created_at DESC);
Enter fullscreen mode Exit fullscreen mode

Column order matters. Put the equality filters first, then the range/sort column:

  • user_id = 42 → equality, goes first
  • status = 'pending' → equality, goes second
  • ORDER BY created_at DESC → sort column, goes last with the sort direction

With this index, the query scans exactly the right rows in order — no sorting, no filtering junk rows.

Use EXPLAIN ANALYZE to verify:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 10;

-- Look for: Index Scan (not Seq Scan), actual rows close to planned rows
Enter fullscreen mode Exit fullscreen mode

What I Actually Do Now

Every time I write a query that touches more than one table or has a WHERE clause beyond a primary key lookup, I:

  1. Run EXPLAIN before merging the PR. Not in production — in the PR.
  2. Set statement_timeout on read replicas. A 30-second query at 3 AM is a 3-second query times 10 concurrent users at 3 PM.
  3. Log slow queries with log_min_duration_statement. You can't fix what you don't measure. 
-- In postgresql.conf or per-session
SET statement_timeout = '10s';
SET log_min_duration_statement = '1s';
Enter fullscreen mode Exit fullscreen mode

Most of the "mysterious" performance issues I've debugged came down to one of these three patterns. The fix usually took 10 minutes once I found it — the hard part was knowing where to look.

What query patterns have bitten you in production? I'd love to hear about them.

Top comments (0)