Originally published at makroumi.hashnode.dev
A teammate pushed a query on a Friday afternoon. No red flags in review.
It had been running fine in staging for weeks.
By 2am Saturday, the on-call engineer was paged. Response times had gone from 50ms to 8s. We didn't recover until Sunday.
One query. One pattern that nobody caught. A weekend gone.
I've catalogued the SQL patterns that cause incidents, not the obscure edge cases, but the ones that pass code review, behave on small datasets, and silently wait for your table to grow past a threshold nobody anticipated.
These are the seven I see most often, with short diagnostics and fixes you can apply in code review, CI, and monitoring.
- The leading wildcard
-- This query will never use your index
SELECT * FROM users WHERE email LIKE '%@gmail.com';
Why it fails: A leading wildcard (%...) prevents index seeks; the database must scan the entire table. Works fine on small data, catastrophic at scale.
Fixes:
Use trailing wildcards when possible (LIKE 'gmail%').
Use full-text search for substring/suffix matching.
Store and index a reversed string column if you frequently need suffix matches.
Consider trigram or specialized indexes (pg_trgm in Postgres) for substring searches.
Detection: EXPLAIN shows sequential scan; cardinality estimate equals table size.
- A function wrapped around an indexed column
-- Index on created_at is ignored
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- Index-friendly version
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
Why it fails: Applying a function to a column in WHERE disables sargability, the engine can't use the index for seeks.
Fixes:
-Push computation to the constant side (range predicates).
-Create a functional/indexed expression if you must query that function (functional indexes in Postgres, expression indexes in MySQL 8+).
- Avoid repeated function evaluation in hot paths.
Detection: EXPLAIN shows index not used; add an index on the expression if necessary.
- SELECT * in production queries
-- Fetches 47 columns when you need 3
SELECT * FROM events WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20;
Why it fails:
Rakes unnecessary bytes across the wire and memory.
Breaks covering/index-only plans.
On columnar warehouses (BigQuery/Redshift/Athena) you pay per byte scanned — SELECT * can become extremely expensive.
Fixes:
Explicitly list columns you need.
Use narrower projections in APIs and services.
For warehouses, test cost on representative data.
Detection: Review changesets for SELECT *; run explain/cost estimation on representative data.
- DELETE or UPDATE without WHERE
-- Runs at 3am in a cleanup job
DELETE FROM sessions;
-- What was intended
DELETE FROM sessions WHERE expires_at < NOW();
Why it fails: A missing or accidentally removed WHERE clause can wipe an entire table. This often happens during refactors, copy/paste, or when a developer runs a query interactively and forgets to reapply the filter in application code.
Fixes:
Use explicit transactions and backups for risky maintenance jobs.
Add safety clauses (e.g., LIMIT with repeated runs) or require an explicit flag to run destructive jobs.
Author review checklists and CI/PR checks to catch mass-deletes.
Run destructive jobs from separate, audited service accounts.
Detection: Linting, CI checks that flag DELETE/UPDATE without WHERE, changelogs and deployment gating.
- Missing or inefficient pagination
-- OFFSET scans previous rows every time
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 10000;
Why it fails: OFFSET causes the database to scan and discard rows up to the offset. At high offsets this becomes O(offset). Cursor-based or keyset pagination keeps response time stable.
Fixes:
Use keyset pagination (WHERE created_at < last_seen_created_at ORDER BY created_at DESC LIMIT 20).
Use indexed columns for pagination predicates.
For APIs, return opaque cursors instead of numeric offsets.
Detection: Look for OFFSET in queries; test with high offsets on representative data.
- JOINs without restrictive predicates (fan-out)
-- Produces massive intermediate results
SELECT u.id, o.id, oi.*
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
WHERE u.region = 'EU';
Why it fails: Missing or weak join/filter conditions cause explosive intermediate result sets. If one side is large, the join multiplies rows and memory/IO usage spikes.
Fixes:
Filter early, push predicates into join drivers.
Ensure join keys are indexed.
Use EXISTS/IN appropriately to avoid duplication when you only need existence.
Denormalize or pre-aggregate in OLAP workloads.
Detection: EXPLAIN shows large row estimates for join stages; monitoring spikes in temporary disk usage.
- Implicit type conversions and mismatched types
-- Implicit cast prevents index usage
SELECT * FROM users WHERE id = '123';
Why it fails: Comparing different types often triggers implicit casting; the database may cast the column, not the constant, disabling index use. It also leads to unexpected behavior.
Fixes:
Use consistent types for columns and constants.
Cast constants, not columns (e.g., WHERE id = 123::bigint).
Add strict linting and schema checks in CI.
Detection: EXPLAIN shows index ignored; static analysis/linting can catch literal-type mismatches.
Why these keep shipping
None of these are exotic. Every engineer reading this has probably written at least one of them.
They ship because code review is done by humans under time pressure who are focused on logic and architecture, not query execution plans. They ship because they work perfectly in staging where tables have thousands of rows, not millions. They ship because there's no automated check in the pipeline asking whether a WHERE clause is missing or a wildcard is leading.
The fix isn't better engineers. It's automated checks that run before anything merges.
I built SlowQL to do exactly that. It's a SQL static analyzer, 171 rules across performance, security, reliability, compliance, quality and cost. Zero dependencies. Completely offline. Point it at your SQL files and it catches these patterns before they ship.
pip install slowql
slowql --input-file ./sql
It won't catch everything. It doesn't execute your queries or read your schema. But it catches the universal patterns - the ones in this article - statically, in CI, before they ever reach a table with 10 million rows.
If you've been on call at 2am because of a query that should have been caught in review, this is for you.
GitHub: https://github.com/makroumi/slowql
Conclusion and quick checklist:
Always EXPLAIN queries that will run on production tables.
Reject SELECT * in PRs; require explicit projections.
Add CI checks (static analysis, linting) to catch simple sargability and safety issues.
Prefer keyset pagination and index-friendly predicates.
Add functional/expression indexes only when necessary and after benchmarking.
Run realistic load tests on representative data sizes.
Canonical URL: https://makroumi.hashnode.dev/7-sql-patterns-that-look-fine-in-review-and-destroy-you-in-production
Top comments (0)