SQL Mastery
Intermediate 14 min read SQL Mastery

SQL Anti-Patterns

Recognize the mistakes that quietly cost you

SELECT *, cursor pagination, N+1 reads, non-sargable predicates and COUNT over huge tables are the recurring mistakes that make warehouse queries slow or wrong.

anti-patternsN+1sargabilitypaginationSELECT *
Prerequisites: CTEs and Subqueries
Before we start

What you will learn

  • Identify the common SQL anti-patterns by signature
  • Rewrite them into sargable, explicit, indexed variants
  • Know when a 'trick' is a real fix versus a smell
Introduction

What is this?

Certain SQL shapes recur in every codebase and are wrong in specific ways: SELECT * couples consumers, explicit row pagination thrashes, non-sargable predicates (func(column)) can't use indexes, and COUNT(*) over a giant table scans everything. Recognizing them by signature lets you fix root cause instead of patching symptoms.

Motivation

Why do you need it?

  • These patterns are the difference between a query that takes 4ms and one that takes 4 minutes on the same table.
  • Each is cheap to fix once recognized and expensive when left to multiply.
  • Interview loops test exactly these traps.
Think about it

Mental model

Imagine

Crossing a street against the light

Think of

A SQL anti-pattern

It usually works, feels convenient, and everyone does it — until the day it doesn't. The SQL traps are the same: tolerable at small scale, catastrophic at production volume.

Mechanics

How it works

  • SELECT * pulls columns the query doesn't need — larger IO, wider rows, brittle to renames.
  • OFFSET/LIMIT walks dropped tuples on every page: OFFSET 100000 means skipping 100k rows.
  • func(column) in WHERE (LOWER(email) = ...) can't use a plain index unless it's an expression index.
  • N+1: one query per related row instead of one join/batch — the API layer's classic.
  • COUNT(*) on a big table scans all rows when an exact count isn't needed; approximate suffices.

Anti-pattern → safe rewrite.

SELECT *
OFFSET big
func(col) filter
N+1 fetch
COUNT * exact
Real world

In production

  • A product-catalog API switched OFFSET to keyset pagination and p99 dropped from 2s to 40ms at 100k products.
  • A report query replaced SELECT * with two explicit columns, halving IO by reading less from wide tables.
  • An analytics dashboard replaced exact COUNT with pg_class.reltuples/statistics to render instantly.
Guidelines

Best practices

  • Always enumerate the columns a query actually needs.
  • Paginate with keyset idioms (WHERE key > :last ORDER BY key) on OLTP-style data.
  • Keep predicates sargable: match the index; use expression indexes when you must wrap columns.
  • Add uniqueness tests so N+1-inducing duplicate keys are caught at the source.
Watch out

Common mistakes

  • Writing OFFSET/LIMIT for deep admin exports that never make it past page 100.
  • Putting LOWER/UPPER/EXTRACT around indexed columns and wondering why scans remain.
  • SELECT * into a 40-column dump that pandas reads every night.
  • Fixing N+1 by adding an index per loop instead of removing the loop.
Root cause analysis

Common issues

Error
Deep pagination slows linearly
Root cause

OFFSET re-scans the skipped rows every page

Fix

Switch to keyset pagination using an indexed unique column.

Error
LOWER(email) filter ignores a perfect index
Root cause

Non-sargable left-hand function

Fix

Create an expression index on LOWER(email) or store a precomputed normalized column.

When things break

Debugging tips

  • Search the codebase for SELECT *, OFFSET, and per-row fetch loops as a triage pass.
  • When a 'small change' slows production, diff the queries for a non-sargable predicate.
  • Measure with EXPLAIN ANALYZE before declaring a pattern 'fixed'.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which pagination approach stays fast at page 100,000?

2

Wrapping a column in LOWER() in the WHERE clause lets most planners use a plain column index.

Go deeper

Official documentation

PostgreSQL — using EXPLAIN well
docs
Use The Index, Luke — pagination
docs