Common SQL Anti-Patterns and How to Avoid Them in PostgreSQL

calendar_today June 9, 2025 schedule 6 min read

Learning Objectives: By the end of this lesson, you will be able to:

Identify common SQL anti-patterns that harm query performance

Refactor problematic queries for better efficiency

Understand the impact of anti-patterns on the PostgreSQL query planner

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

What’s the most confusing or frustrating SQL query you’ve ever debugged? Have you ever been surprised by a slow query that looked simple?

Lesson Overview

Duration: 90 minutes
Prerequisites: Basic SQL, experience writing SELECT queries
Materials Needed: PostgreSQL database, SQL client, sample tables

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

Overview of common SQL anti-patterns
Why these patterns hurt performance
Refactoring examples and best practices
Hands-on query improvement exercises
Debugging and troubleshooting tips

Success Criteria:

By the end of this session, you should be able to spot and fix anti-patterns in your own SQL code.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

What is an Anti-Pattern?

An anti-pattern is a common but counterproductive way of solving a problem. In SQL, anti-patterns often lead to slow queries, high resource usage, or incorrect results.

Real-World Analogy: Like taking a shortcut that actually makes your commute longer due to traffic.

Why This Matters: Avoiding anti-patterns leads to faster, more reliable, and easier-to-maintain databases.

How PostgreSQL Handles Queries

The query planner tries to find the most efficient way to execute your SQL. Anti-patterns can confuse the planner or force it into inefficient strategies.

Key Terminology

Seq Scan (Sequential Scan): Scanning every row in a table
Index Scan: Using an index to find rows quickly
Predicate: The condition in a WHERE clause

4. Detailed Explanation and Examples (25-30 minutes)

Understanding Common Anti-Patterns

1. SELECT * (Select All Columns)

SELECT * FROM orders WHERE order_date > '2024-01-01';

Why it’s bad:

Fetches unnecessary data, increasing I/O
Breaks index-only scans
Makes code less maintainable

Better:

SELECT id, customer_id, order_date FROM orders WHERE order_date > '2024-01-01';

2. Functions on Indexed Columns in WHERE

SELECT * FROM users WHERE lower(email) = 'test@example.com';

Why it’s bad:

Prevents use of normal indexes
Forces a full table scan

Better:

Create an expression index:

CREATE INDEX idx_users_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'test@example.com';

3. OR on Different Columns

SELECT * FROM products WHERE category = 'Books' OR price < 10;

Why it’s bad:

Hard for planner to use indexes efficiently

Better:

Use UNION ALL if possible:

SELECT * FROM products WHERE category = 'Books'
UNION ALL
SELECT * FROM products WHERE price < 10 AND category <> 'Books';

4. NOT IN with NULLs

SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM customers WHERE region = 'West');

Why it’s bad:

If the subquery returns NULL, the result is always empty

Better:

Use NOT EXISTS:

SELECT * FROM orders o WHERE NOT EXISTS (
  SELECT 1 FROM customers c WHERE c.region = 'West' AND o.customer_id = c.id
);

5. Large IN Lists

SELECT * FROM orders WHERE id IN (1,2,3,4,5,6,7,8,9,10, ... 1000);

Why it’s bad:

Can be slow and hard to optimize

Better:

Use a temporary table or JOIN

5. Live Coding Demonstration (20-25 minutes)

Example 1: Refactoring SELECT *

-- Original
SELECT * FROM users WHERE created_at > '2024-01-01';
-- Refactored
SELECT id, name, created_at FROM users WHERE created_at > '2024-01-01';

Example 2: Fixing Functions on Indexed Columns

-- Original
SELECT * FROM users WHERE lower(username) = 'alice';
-- Refactored with index
CREATE INDEX idx_users_lower_username ON users (lower(username));
SELECT * FROM users WHERE lower(username) = 'alice';

Example 3: Replacing OR with UNION ALL

-- Original
SELECT * FROM sales WHERE region = 'East' OR amount > 1000;
-- Refactored
SELECT * FROM sales WHERE region = 'East'
UNION ALL
SELECT * FROM sales WHERE amount > 1000 AND region <> 'East';

Debugging Demonstration

-- Problem: NOT IN with NULLs
SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM customers WHERE status IS NULL);
-- Corrected
SELECT * FROM orders o WHERE NOT EXISTS (
  SELECT 1 FROM customers c WHERE c.status IS NULL AND o.customer_id = c.id
);

6. Hands-On Practice (30-40 minutes)

Exercise 1: Basic Practice (10 minutes)

Objective: Refactor a query that uses SELECT * and a function in WHERE.

Instructions:

Replace SELECT * with explicit columns
Create an index for the function in WHERE

Starter Code:

SELECT * FROM employees WHERE lower(last_name) = 'smith';

Solution:

CREATE INDEX idx_employees_lower_last_name ON employees (lower(last_name));
SELECT id, first_name, last_name FROM employees WHERE lower(last_name) = 'smith';

Exercise 2: Intermediate Challenge (15 minutes)

Objective: Rewrite a query using OR to use UNION ALL for better performance.

Requirements:

Avoid duplicate rows
Use explicit columns

Hints:

Use WHERE conditions to prevent overlap

Exercise 3: Creative Application (15 minutes)

Objective: Find all orders not placed by customers in a given region, avoiding NOT IN with NULLs.

Challenge:

Use NOT EXISTS
Show order id and customer id

Extension Ideas:

Add a filter for order date
Show customer name with a JOIN

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

Problem: Using SELECT * everywhere Cause: Habit or convenience Solution: Always specify needed columns
Problem: Ignoring NULLs in subqueries Cause: NOT IN returns no rows if NULL is present Solution: Use NOT EXISTS or filter out NULLs

Debugging Tips

Use EXPLAIN to see if indexes are used
Check for sequential scans on large tables
Test queries with and without problematic patterns

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

Always specify columns in SELECT
Use expression indexes for functions in WHERE
Prefer EXISTS/NOT EXISTS over IN/NOT IN with subqueries
Avoid large IN lists; use JOINs or temp tables

Next Steps in Your Learning Journey

Study more advanced query optimization
Learn about query plan analysis
Practice refactoring legacy SQL code

9. Summary and Review (5 minutes)

Key Takeaways

Anti-patterns can silently harm performance
Refactoring queries can yield big speedups
Use EXPLAIN and indexes to guide improvements

Quick Review Questions

Why is SELECT * discouraged in production queries?
How do functions in WHERE clauses affect index usage?
What is a safer alternative to NOT IN with subqueries?

For Instructors

Teaching Tips

Show before/after performance with EXPLAIN ANALYZE
Encourage students to refactor real queries
Discuss anti-patterns in code reviews

Assessment Ideas

Have students refactor a set of anti-pattern queries
Peer review for query clarity and performance
Quiz on anti-pattern identification

Adaptation Notes

For beginners: Focus on SELECT * and function/index issues
For advanced: Cover more subtle anti-patterns and planner behavior
Online: Use shared SQL sandboxes for practice
In-person: Group query refactoring challenges