Paul SerbanSoftware Engineer

SQL Patterns, Anti-Patterns, and Pitfalls: A Practical Guide for EngineersHow to write SQL that scales, stays correct, and doesn't quietly rot your database

Introduction

SQL is one of the few technologies in software engineering that has remained relevant, largely unchanged in its core syntax, for over four decades. Despite the rise of NoSQL databases, ORMs, and abstraction layers that promise to hide SQL entirely, most production systems of any real complexity still depend on a relational database underneath. The irony is that many engineers who write SQL every day never formally studied it - they picked it up by copying queries, adapting them, and occasionally being surprised by a slow query in production. This gap between casual familiarity and genuine understanding is where most of the pain in database engineering comes from.

This article is a practical tour through the patterns that make SQL code maintainable and performant, the anti-patterns that quietly sabotage systems over time, and the pitfalls that catch even experienced engineers off guard. It is not a syntax tutorial. Instead, it assumes you already know how to write a SELECT statement and focuses on the judgment calls: when to normalize versus denormalize, how to reason about indexes, why certain query shapes cause exponential slowdowns, and how to structure schema migrations so they do not take down a production system. The goal is to leave you with a mental model of SQL as a system to be designed, not just a language to be written.

Why SQL Still Matters: Context and Problem Overview

Relational databases succeeded because they solved a problem that remains fundamental to most business software: representing structured, interrelated data with strong consistency guarantees. The relational model, introduced by Edgar F. Codd in 1970, gave engineers a declarative way to describe what data they wanted rather than how to retrieve it, leaving the query planner to figure out an efficient execution strategy. This separation of concerns is still the single biggest reason SQL has outlived dozens of competing paradigms - you describe the shape of the answer, and the engine decides the access path.

The problem is that this declarative abstraction leaks constantly. A query that is logically correct can still be catastrophically slow, because the planner's choices depend on statistics, indexes, and data distribution that the query author usually cannot see just by reading the SQL. This is fundamentally different from, say, a well-typed function in an imperative language, where the cost model is mostly visible in the code itself. In SQL, two queries that return identical results can differ in execution cost by several orders of magnitude, and the difference is often invisible until the table grows past some threshold.

This is compounded by the fact that most engineers interact with SQL through an ORM (Object-Relational Mapper) such as Prisma, SQLAlchemy, TypeORM, or ActiveRecord. ORMs are genuinely useful for productivity and type safety, but they also insulate engineers from the actual queries being generated. A single line of application code can silently produce a query that joins six tables or issues a hundred round trips to the database. Understanding SQL patterns and anti-patterns is therefore not just about writing raw SQL well - it is about knowing what your ORM is doing on your behalf, and when to step around it.

Deep Technical Explanation: How the Engine Actually Executes Your Query

To reason about SQL performance, it helps to understand what happens after you hit "execute." A relational database engine typically goes through parsing, then a rewrite/normalization phase, then query planning, and finally execution. During planning, a cost-based optimizer evaluates multiple candidate execution plans - different join orders, different access methods (sequential scan versus index scan), different join algorithms (nested loop, hash join, merge join) - and picks the one it estimates will be cheapest, based on statistics about table sizes and data distribution that the database maintains internally.

This is why the same query can behave very differently on two databases with the same schema but different data volumes or statistics. A table with ten rows will almost always be scanned sequentially, because a full scan is cheaper than the overhead of using an index. The same query on a table with ten million rows might use an index scan instead. Engineers who test locally against a nearly empty development database and then deploy to production frequently discover this gap the hard way: the query plan that worked fine in staging turns into a full table scan against a live dataset that has grown by three orders of magnitude. Tools like PostgreSQL's EXPLAIN ANALYZE or MySQL's EXPLAIN FORMAT=JSON exist precisely to make this invisible decision-making visible, and learning to read their output is arguably the highest-leverage skill in practical SQL engineering.

Implementation and Practical Patterns

A handful of patterns show up repeatedly in well-designed SQL codebases, and recognizing them makes both writing and reviewing SQL substantially easier. The first is the covering index pattern, where an index is designed to include every column a query needs, so the engine can satisfy the query directly from the index without touching the underlying table (an "index-only scan" in PostgreSQL terms). This is especially valuable for read-heavy endpoints that filter and select a narrow, predictable set of columns.

-- Anti-pattern: index only covers the filter column
CREATE INDEX idx_orders_status ON orders (status);

SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE status = 'pending';
-- The engine still has to fetch each matching row from the table (a "heap fetch")

-- Pattern: a covering index avoids the extra table lookup
CREATE INDEX idx_orders_status_covering
  ON orders (status)
  INCLUDE (id, customer_id, total_cents, created_at);

The second recurring pattern is keyset pagination (sometimes called cursor-based pagination), which replaces the common but expensive OFFSET/LIMIT approach. OFFSET forces the database to scan and discard every row before the offset, which becomes linearly more expensive as users page deeper into results. Keyset pagination instead uses the last seen row's sort key as a filter condition, which the index can use directly regardless of how deep the page is.

-- Anti-pattern: OFFSET gets slower the deeper you paginate
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

-- Pattern: keyset pagination stays fast at any depth
SELECT id, title, created_at
FROM articles
WHERE created_at < '2026-03-01 10:15:00'
ORDER BY created_at DESC
LIMIT 20;

A third pattern worth internalizing is expressing business rules as constraints rather than relying purely on application-layer validation. A CHECK constraint, a UNIQUE constraint, or a foreign key with an explicit ON DELETE policy encodes an invariant once, in the one place that can actually enforce it against every writer - including future services, background jobs, or a database console session that bypasses your application entirely. Relying only on application code to enforce "an order total cannot be negative" means the invariant only holds as long as every code path remembers to check it.

Finally, in application code that constructs SQL dynamically, parameterized queries are non-negotiable, both for correctness and security. The following TypeScript example shows the pattern using a typical pg client, contrasted with the string-concatenation approach that leads to SQL injection.

// Anti-pattern: string interpolation enables SQL injection
async function getOrdersByStatusUnsafe(status: string) {
  const query = `SELECT * FROM orders WHERE status = '${status}'`;
  return db.query(query);
}

// Pattern: parameterized query, safe and plan-cacheable
async function getOrdersByStatus(status: string) {
  return db.query(
    `SELECT id, customer_id, total_cents, created_at
     FROM orders
     WHERE status = $1`,
    [status]
  );
}

Anti-Patterns and Common Pitfalls

The most notorious SQL anti-pattern in application code is the N+1 query problem, which happens when code fetches a list of parent records and then issues a separate query for each parent's related child records inside a loop. A single request that should cost one or two queries ends up costing one plus N, where N is the number of parent rows. This is rarely visible in development, where N might be five, but becomes a serious latency and database-load problem in production, where N might be thousands.

# Anti-pattern: N+1 queries
orders = db.execute("SELECT id, customer_id FROM orders WHERE status = 'pending'")
for order in orders:
    items = db.execute(
        "SELECT * FROM order_items WHERE order_id = %s", (order["id"],)
    )
    order["items"] = items

# Pattern: a single query with a join, or a batched IN() lookup
order_ids = [o["id"] for o in orders]
items = db.execute(
    "SELECT * FROM order_items WHERE order_id = ANY(%s)", (order_ids,)
)

A second pervasive anti-pattern is over-normalizing or under-normalizing without an actual justification tied to access patterns. Normalization, formalized through Codd's normal forms, is genuinely valuable for eliminating update anomalies - situations where the same fact is stored redundantly and can drift out of sync. But normalization is a trade-off, not a virtue in itself. A schema normalized to the third normal form purely out of habit, when the dominant access pattern is "fetch a document-like aggregate as a single unit," forces the application to reassemble that aggregate from five joined tables on every read. This is the trigger for the well-known counter-pattern of selective denormalization, where a derived or duplicated column is intentionally maintained (often via a trigger or an application-level write path) to avoid an expensive join on the hot read path.

A third pitfall is treating NULL as an ordinary value rather than the three-valued-logic construct it actually is in SQL. NULL = NULL evaluates to NULL, not TRUE, which means a WHERE column = NULL clause silently matches nothing, and a NOT IN subquery that returns even a single NULL will cause the entire NOT IN to return no rows at all, a mistake that has caused production incidents in codebases where nobody suspected the query itself.

-- Silent pitfall: if customer_id ever contains a NULL, this returns zero rows
SELECT * FROM orders
WHERE customer_id NOT IN (SELECT customer_id FROM banned_customers);

-- Safer: explicitly exclude NULLs, or use NOT EXISTS
SELECT o.* FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM banned_customers b
  WHERE b.customer_id = o.customer_id
);

Finally, the "SELECT *" habit deserves mention not because it is always wrong, but because it silently couples the query to the full shape of the table. Adding a large TEXT or BYTEA column to a table later means every existing SELECT * query now pulls that column across the network, even where it is never used, and any schema change that reorders or renames columns can break code that relied on positional access to the result set.

Trade-offs: Consistency, Concurrency, and Cost

Every SQL design decision is a trade-off, and nowhere is this clearer than in how transactions and isolation levels are chosen. The SQL standard defines four isolation levels - Read Uncommitted, Read Committed, Repeatable Read, and Serializable - each trading some degree of consistency guarantee for concurrency and throughput. Most production systems default to Read Committed, which prevents dirty reads but still permits phenomena like non-repeatable reads and phantom rows. Engineers building financial or inventory systems, where a race condition could mean double-selling the same unit of stock, often need to reach for SELECT ... FOR UPDATE row locking or a stricter isolation level, accepting reduced concurrency in exchange for correctness.

The same tension shows up in index design more broadly. Every index added to a table speeds up the reads it serves but adds overhead to every write, since the engine must maintain that index's data structure - typically a B-tree - on every insert, update, or delete. A table with fifteen indexes optimized for every possible query pattern will have noticeably slower write throughput than one with two or three carefully chosen indexes that match the actual query workload. This is why index design is not a "more is better" exercise; it requires knowing which queries actually run in production, at what frequency, and weighing that against the write volume the table experiences.

Best Practices for Sustainable SQL

Good SQL practice starts before a single query is written, at the schema design stage. Choosing appropriate data types - using a proper DATE or TIMESTAMP type instead of storing dates as strings, using NUMERIC for currency instead of floating point, and using enums or check constraints instead of free-text status columns - prevents entire categories of bugs from ever reaching a code review. Migrations should be written to be backward-compatible with the currently running application version, since a schema change and an application deploy are rarely truly atomic in a live system; adding a new required column, for instance, should usually be done as a nullable column first, backfilled, and only made NOT NULL once every writer has been updated.

Query review should treat EXPLAIN ANALYZE output as a normal part of code review for any query touching a table expected to grow, in the same way a team might expect a diff or a test to accompany a pull request. This is more effective than trying to memorize a list of forbidden patterns, because it grounds the discussion in the actual cost of the actual query against representative data, rather than a stylistic preference. Pairing this with monitoring for slow queries in production - most managed databases expose a slow query log or an equivalent - closes the loop, catching queries whose cost profile changes as data grows even if the SQL text never changes.

Finally, treat schema as a shared contract with explicit ownership, not an implementation detail owned informally by whichever team touched it last. Documenting the intended access patterns for each table, and revisiting index choices when those access patterns change (a new feature that introduces a new dominant query shape, for example) keeps the schema aligned with reality instead of accumulating indexes and denormalized columns that reflect decisions nobody remembers making.

Mental Models That Make SQL Click

One useful mental model is to think of a SQL query as a recipe you hand to a very literal-minded chef who is free to prepare the ingredients in whatever order is fastest, as long as the final dish matches your description exactly. You describe the dish (the SELECT), not the cooking steps - and the chef (the query planner) decides whether to start with the vegetables or the meat based on what's freshest (the index statistics). This reframes a slow query less as "the database is being dumb" and more as "the chef doesn't have the information needed to make a better choice," which usually points you toward fixing statistics, indexes, or the query shape itself rather than fighting the optimizer.

A second useful model is to think of indexes as a trade of disk space and write latency for read latency, much like caching. Just as an application cache needs invalidation logic and consumes memory in exchange for faster reads, a database index consumes disk space and slows down writes in exchange for faster reads on the columns it covers. This framing makes it intuitive why you would not want to index every column "just in case" - nobody caches every possible computed value either, because the maintenance cost eventually outweighs the benefit.

The 80/20 of SQL Engineering

If you strip away the long list of specific tips, a small number of concepts account for most of the real-world pain and most of the real-world wins in SQL engineering. Understanding how indexes actually work - that they are typically B-trees ordered by column, that composite index column order matters, and that an index is only useful if the query's filter or sort can actually use its ordering - explains the majority of "why is this query slow" investigations. Learning to read an execution plan, even at a basic level of distinguishing a sequential scan from an index scan and noticing a wildly inaccurate row-count estimate, resolves most performance debugging sessions faster than guessing.

The second high-leverage concept is recognizing the N+1 pattern on sight, since it is by far the most common performance bug introduced through ORMs and is almost always fixable with either eager loading, a join, or a batched IN/ANY query. The third is understanding transactions and isolation well enough to know when a race condition is possible, because correctness bugs caused by concurrent writes are far more expensive to discover and fix after the fact than performance issues are. Mastering these three areas - indexing, query-shape awareness, and transactional correctness - covers a disproportionate share of the value in this entire field.

Key Takeaways

  • Always inspect EXPLAIN ANALYZE (or your engine's equivalent) before assuming a query is "fine," especially on tables expected to grow.
  • Watch for the N+1 pattern whenever code loops over a result set and issues further queries inside the loop; prefer joins or batched lookups.
  • Design indexes around actual query patterns, not hypothetically, and remember every index has a write-time cost.
  • Use constraints (CHECK, UNIQUE, foreign keys) to enforce invariants at the database level, not only in application code.
  • Choose isolation levels and locking strategy deliberately based on the correctness requirements of the specific workflow, rather than accepting the default everywhere.

Conclusion

SQL rewards the engineer who treats it as a system to reason about rather than a syntax to memorize. The language's declarative surface makes it easy to write something that "works" on a small dataset while hiding a query plan that will not survive contact with production-scale data. The patterns and anti-patterns covered here - covering indexes, keyset pagination, the N+1 problem, NULL semantics, and the normalization/denormalization trade-off - are not an exhaustive list, but they represent the recurring failure modes that show up across nearly every relational database project, regardless of the specific engine or ORM in use.

The underlying discipline that ties all of this together is curiosity about what actually happens when a query executes. Engineers who make a habit of checking execution plans, questioning default ORM behavior, and understanding the concurrency guarantees their transactions actually provide tend to build systems whose database layer scales gracefully, rather than one that requires a rewrite once real traffic arrives. SQL has stayed relevant for over fifty years because the underlying problem it solves has not gone away; the engineers who get the most out of it are the ones who respect that it is a system with real cost trade-offs, not a syntax to be used on autopilot.

References

  • Codd, E. F. (1970). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM.
  • Kleppmann, M. Designing Data-Intensive Applications. O'Reilly Media.
  • Karwin, B. SQL Antipatterns: Avoiding the Pitfalls of Database Programming. Pragmatic Bookshelf.
  • Winand, M. Use The Index, Luke! - A Guide to Database Performance for Developers. use-the-index-luke.com
  • PostgreSQL Global Development Group. PostgreSQL Documentation: Query Planning and EXPLAIN. postgresql.org/docs
  • PostgreSQL Global Development Group. PostgreSQL Documentation: Transaction Isolation. postgresql.org/docs
  • ISO/IEC 9075 - SQL Standard (maintained jointly by ISO and ANSI).
  • MySQL Documentation. EXPLAIN Output Format and Optimization. dev.mysql.com/doc