Minor anti-patterns: non-immutable functions, `ORDER BY` without `LIMIT`, massive `IN (...)`

There are "big" anti-patterns — OFFSET, COUNT(*), over-indexing — that we covered in capsules 02-04. There are also "minor" anti-patterns: individually they have little impact, but they add up when they appear together across many endpoints. And they share a characteristic: they're easy to recognize at first glance in a code review.

In this capsule we cover three:

1. Non-immutable functions in WHERE: WHERE created_at > NOW() - INTERVAL '1 day' gets evaluated per row or blocks the use of indexes.
2. ORDER BY without LIMIT: forces the planner to sort the whole resultset when it almost always indicates a bug.
3. IN (...) with thousands of values: degrades the plan to a Hash and blocks the planner's optimizations.

After this capsule you'll have the complete radar for anti-patterns. Capsules 07 and 08 close with the final integrative project.

Anti-pattern 1: non-immutable functions in WHERE

PostgreSQL classifies functions by volatility:

• IMMUTABLE: always returns the same result given the same inputs. E.g.: length('hello'), 'a' || 'b', 2 * 3.
• STABLE: returns the same result during the same query. E.g.: current_date, now(), catalog reads.
• VOLATILE: can return different results on each call. E.g.: random(), clock_timestamp().

The planner treats each category differently:

• IMMUTABLE functions can be pre-computed and used in index planning.
• STABLE functions are evaluated once per query — but PostgreSQL doesn't know the value at planning time, which limits the use of indexes with expressions.
• VOLATILE functions are evaluated per row — they block aggressive optimizations.

The typical problem

-- Anti-pattern: NOW() in WHERE
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '1 day';

NOW() is STABLE (it returns the same value during the query). This is NOT as bad as VOLATILE, but the problem is another one: if you have an expression index like:

CREATE INDEX idx_orders_recent ON orders ((created_at > '2026-05-08'::timestamptz));

It only applies to a fixed timestamp. The query WHERE created_at > NOW() - INTERVAL '1 day' doesn't use this index because NOW() - INTERVAL '1 day' is different every day.

Another problem: implicit timezone

NOW() returns a timestamp with the server's timezone. If your app is in another timezone, comparisons can be wrong. Volatile for debugging.

The refactor

Better: pass the computed value from the client.

# In the app
from datetime import datetime, timedelta, timezone

@router.get("/orders/recent")
async def get_recent_orders(db: AsyncSession = Depends(get_db)):
    one_day_ago = datetime.now(timezone.utc) - timedelta(days=1)

    result = await db.execute(
        select(Order).where(Order.created_at > one_day_ago)
    )
    return result.scalars().all()

Now the SQL query is:

SELECT * FROM orders WHERE created_at > '2026-05-07T14:32:11+00:00';

With a literal value, the planner can use indexes, exact statistics (based on the histogram), and plan better.

Another option: mark the function as IMMUTABLE when it applies.

-- If you have a custom function that's deterministic
CREATE OR REPLACE FUNCTION business_day_offset(start_date DATE, days INT)
RETURNS DATE AS $$
    -- logic
$$ LANGUAGE plpgsql IMMUTABLE;  -- Explicit mark

A function marked IMMUTABLE can be used in expression indexes.

Truly volatile functions

random() and clock_timestamp() are VOLATILE. In WHERE, they're evaluated per row:

-- Clear anti-pattern: it blocks the use of an index
SELECT * FROM users WHERE random() < 0.001;
-- Seq Scan mandatory, evaluates random() for each row

If you need a random sample, better:

-- TABLESAMPLE: native feature for sampling
SELECT * FROM users TABLESAMPLE BERNOULLI(0.1);  -- 0.1% sample
-- Or TABLESAMPLE SYSTEM(1) for a page-based sample

Anti-pattern 2: ORDER BY without LIMIT

-- Anti-pattern: ORDER BY all rows
SELECT * FROM orders ORDER BY created_at DESC;

Without LIMIT, PostgreSQL:

1. Traverses the table (Seq Scan or Index Scan).
2. Sorts all the rows.
3. Returns all of them.

If the table has 10M rows, sorting 10M rows in memory/disk is very expensive. And the client is almost always only going to use the first N.

Why it's an anti-pattern

When you see ORDER BY without LIMIT in backend SQL:

• The client will paginate in memory: fetching 10M rows into Python to show the first 20 wastes network, memory, and time.
• The client never uses everything: APIs almost never return 10M rows in one response.
• If you really need everything ordered: it's probably an export/report, not a typical API query.

The refactor

-- Listing API: ORDER BY + LIMIT
SELECT * FROM orders ORDER BY created_at DESC LIMIT 50;

-- Cursor pagination: ORDER BY + LIMIT + tiebreaker
SELECT * FROM orders
WHERE (created_at, id) < (X, Y)
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Full report: stream with a cursor
DECLARE rep_cursor CURSOR FOR
    SELECT * FROM orders ORDER BY created_at DESC;
FETCH 1000 FROM rep_cursor;
-- Process 1000 rows, FETCH another batch, etc.

When ORDER BY without LIMIT is legitimate

• Subqueries where the order is necessary for the aggregation or subsequent logic:

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM orders
) WHERE rn = 1;
-- Here ORDER BY is necessary for ROW_NUMBER, not for the final output

• CTE where another stage of the query consumes it ordered.
• COPY TO or structured exports where the final file really needs to be ordered.

In API contexts, it's almost always a bug.

Detection in code review

Grep the codebase:

grep -r "ORDER BY" src/ | grep -v "LIMIT"

(An approximation. SQL can have both on different lines. But it gives a hint of where to look.)

Anti-pattern 3: IN (...) with thousands of values

-- Anti-pattern: IN with many values
SELECT * FROM orders WHERE customer_id IN (1, 2, 3, ..., 50000);

When the IN list is small (<100), the planner uses the index efficiently. When it grows to thousands, two things happen:

1. Planning time grows: the planner has to evaluate each value.
2. The plan degrades: with 5000 values, the planner frequently chooses Hash or Seq Scan instead of Index Scan, because the overhead of 5000 individual lookups may not pay off.
3. Network overhead: the SQL query itself is kilobytes — more to parse, more to transmit if the client is far away.

The refactor: TEMP TABLE + JOIN

For large lists, better to use a temporary table and a JOIN:

-- Create a temp table with the IDs
CREATE TEMP TABLE filter_ids (id INTEGER PRIMARY KEY);

-- Insert (can be via COPY if there are many)
INSERT INTO filter_ids VALUES (1), (2), (3), ...;

-- Query with a JOIN
SELECT o.*
FROM orders o
JOIN filter_ids f ON o.customer_id = f.id;

PostgreSQL can plan a JOIN with Hash and Index Scan combined, much more efficient than a massive IN.

In SQLAlchemy 2.0

from sqlalchemy import insert, select
from sqlalchemy.dialects.postgresql import insert as pg_insert

async def get_orders_for_many_customers(
    db: AsyncSession,
    customer_ids: list[int],
):
    # If the list is small, use a normal IN
    if len(customer_ids) < 100:
        result = await db.execute(
            select(Order).where(Order.customer_id.in_(customer_ids))
        )
        return result.scalars().all()

    # Large list: use a TEMP TABLE
    await db.execute(text("""
        CREATE TEMP TABLE IF NOT EXISTS filter_customer_ids (
            id INTEGER PRIMARY KEY
        ) ON COMMIT DROP
    """))
    await db.execute(text("TRUNCATE filter_customer_ids"))

    # Bulk insert via COPY or multiple INSERT
    await db.execute(
        pg_insert(filter_table).values([{"id": cid} for cid in customer_ids])
    )

    # JOIN
    result = await db.execute(text("""
        SELECT o.*
        FROM orders o
        JOIN filter_customer_ids f ON o.customer_id = f.id
    """))
    return result.fetchall()

ON COMMIT DROP on the TEMP TABLE ensures it's cleaned up when the transaction ends.

Alternative with ANY

PostgreSQL also supports ANY with an array that can be more efficient than a massive IN:

result = await db.execute(
    select(Order).where(Order.customer_id == any_(customer_ids))
)
# Generates: WHERE customer_id = ANY(ARRAY[1, 2, 3, ...])

ANY with arrays is better than IN with many values in some cases. A similar trade-off — for massive lists, JOIN is still the answer.

When IN (...) with many values is acceptable

• Lists <100 values: the planner handles them well.
• Lists that come from subqueries: WHERE id IN (SELECT id FROM ...) is a different case — the planner sees the subquery and plans them together.
• A single time in migrations or ad-hoc scripts: it's not a production endpoint, a one-time cost is acceptable.

Bonus anti-pattern: LIKE '%pattern%' without a trigram index

-- Prefix search: uses an index if one exists
SELECT * FROM books WHERE title LIKE 'Harry%';

-- Suffix or substring search: forcibly a Seq Scan
SELECT * FROM books WHERE title LIKE '%Potter%';

Both LIKE 'X%' and LIKE '%X%' look the same but are fundamentally different:

• Prefix match ('X%'): can use a B-tree index. Efficient.
• Suffix match ('%X'): can't use a native B-tree index. Seq Scan.
• Contains match ('%X%'): can't use a native B-tree index. Seq Scan.

For suffix/contains/case-insensitive search, the solution is trigram indexes (the pg_trgm extension):

CREATE EXTENSION pg_trgm;

CREATE INDEX idx_books_title_trgm ON books USING gin (title gin_trgm_ops);

-- Now these queries use the index
SELECT * FROM books WHERE title LIKE '%Potter%';
SELECT * FROM books WHERE title ILIKE '%pot%';
SELECT * FROM books WHERE title % 'Potter';  -- similarity match

pg_trgm splits each string into trigrams (3-char windows) and indexes them. It allows similarity search and substrings with an index. It's mentioned in guide #14 (Advanced PostgreSQL).

Traps and common mistakes

1. Thinking that STABLE and VOLATILE don't matter in simple queries.

In simple queries, both work. The problem appears when you try to create expression indexes or when the query is complex enough for the planner to explore options.

2. Using ORDER BY id out of habit without needing it.

If you're going to process all the rows the same way regardless of order, ORDER BY adds cost for free. Only use it when the logic really requires order.

3. IN (...) with hardcoded values of 100+ elements in code.

A code review red flag. If you have a hardcoded list of 100+ IDs, those IDs should probably live in a table, not in code.

4. TEMP TABLE without ON COMMIT DROP.

Without this, the TEMP TABLE persists until the connection closes. Long-running connections accumulate TEMP tables. ON COMMIT DROP ensures cleanup.

5. Passing NOW() from the client when you should pass UTC.

If your client is in a different timezone than the server, NOW() can be ambiguous. Better: datetime.now(timezone.utc).

6. LIKE 'X%' with a function on the left side.

-- ❌ Blocks the index
WHERE LOWER(title) LIKE 'harry%';

-- ✅ If you need case-insensitive, an expression index or trigram
CREATE INDEX idx_books_title_lower ON books (LOWER(title));
WHERE LOWER(title) LIKE 'harry%';  -- now it DOES use the index

7. Using ORDER BY RANDOM() LIMIT 1 for a random sample.

-- Anti-pattern: O(n) Seq Scan + Sort
SELECT * FROM users ORDER BY RANDOM() LIMIT 1;

For an efficient random sample:

-- TABLESAMPLE: O(1) per page
SELECT * FROM users TABLESAMPLE SYSTEM(0.001) LIMIT 1;

-- Or sample by ID
SELECT * FROM users
WHERE id = (SELECT (random() * (SELECT MAX(id) FROM users))::INT);
-- (careful with gaps in IDs)

Exercise: detect and refactor

Setup: create a situation with the three anti-patterns.

DROP TABLE IF EXISTS user_activity;
CREATE TABLE user_activity (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    activity TEXT NOT NULL,
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_activity_user ON user_activity(user_id);
CREATE INDEX idx_activity_occurred ON user_activity(occurred_at);

INSERT INTO user_activity (user_id, activity, occurred_at)
SELECT
    (random() * 10000)::INT + 1,
    (ARRAY['click', 'view', 'submit'])[floor(random() * 3)::INT + 1],
    NOW() - (random() * INTERVAL '90 days')
FROM generate_series(1, 1000000);

ANALYZE user_activity;

Case 1: NOW() in WHERE

EXPLAIN ANALYZE
SELECT * FROM user_activity WHERE occurred_at > NOW() - INTERVAL '7 days';

Question: does it use the index? Time? How would you refactor it?

Case 2: ORDER BY without LIMIT

EXPLAIN ANALYZE
SELECT * FROM user_activity ORDER BY occurred_at DESC;

Question: how long does it take? If the frontend only shows 50, what would you change?

Case 3: IN with many values

-- Generate a list of 5000 user_ids
WITH big_list AS (
    SELECT array_agg(id) AS ids
    FROM generate_series(1, 5000) id
)
SELECT (SELECT ids FROM big_list);
-- Copy the result and use it in:

-- Anti-pattern
EXPLAIN ANALYZE
SELECT * FROM user_activity WHERE user_id IN (1, 2, 3, ...);  -- 5000 values

Question: which plan did it pick? How long does it take?

Refactor:

CREATE TEMP TABLE filter_ids (id INTEGER PRIMARY KEY) ON COMMIT DROP;
INSERT INTO filter_ids SELECT id FROM generate_series(1, 5000) id;

EXPLAIN ANALYZE
SELECT a.* FROM user_activity a
JOIN filter_ids f ON a.user_id = f.id;

Did it improve? By how much?

Bitmap Heap Scan on user_activity
  Recheck Cond: (occurred_at > (now() - '7 days'::interval))
  ->  Bitmap Index Scan on idx_activity_occurred
        Index Cond: (occurred_at > (now() - '7 days'::interval))

seven_days_ago = datetime.now(timezone.utc) - timedelta(days=7)
result = await db.execute(
    select(UserActivity).where(UserActivity.occurred_at > seven_days_ago)
)

Index Scan Backward using idx_activity_occurred on user_activity
  (cost=0.42..55403.99 rows=1000000 width=22)
  (actual time=0.003..335.77 rows=1000000 loops=1)

EXPLAIN ANALYZE SELECT * FROM user_activity ORDER BY occurred_at DESC LIMIT 50;

Limit  (cost=0.42..3.20 rows=50 width=22) (actual time=0.006..0.025 rows=50 loops=1)
  ->  Index Scan Backward using idx_activity_occurred on user_activity
        (cost=0.42..55403.99 rows=1000000 width=22) (actual time=0.006..0.022 rows=50 loops=1)

Bitmap Heap Scan on user_activity  (cost=...)
  Recheck Cond: (user_id = ANY ('{1,2,3,...,5000}'::integer[]))
  ->  Bitmap Index Scan on idx_activity_user

Hash Join  (cost=...)
  Hash Cond: (a.user_id = f.id)
  ->  Seq Scan on user_activity
  ->  Hash
        ->  Seq Scan on filter_ids

Summary and next step

What you learned in this capsule:

• Non-immutable functions in WHERE: NOW() is STABLE (manageable) but blocks expression indexes. RANDOM() is VOLATILE (worse). Passing the computed value from the client is preferable.
• ORDER BY without LIMIT: forces sorting everything. It almost always indicates a bug — the client only uses the first N rows.
• IN (...) with thousands of values: slow planning + a degraded plan. Use a TEMP TABLE + JOIN for large lists.
• Bonus: LIKE '%X%': requires a trigram index (pg_trgm) to avoid falling to a Seq Scan.

And in aggregate, what you learned in module 8 up to here (capsules 02-06):

• 4 "big" anti-patterns (OFFSET, COUNT, over-indexing, premature optimization).
• 4 "minor" anti-patterns in this capsule.
• The complete radar of patterns that appear in code review and produce problems in production.

Before moving on, you should be able to:

• Read a SQL query and detect the anti-patterns in under 30 seconds.
• Refactor each one with concrete code in SQLAlchemy 2.0.
• Justify when each anti-pattern is acceptable (they all have legitimate exceptions).
• Apply the trained eye in code reviews and deploys.

In the next capsule we start the final integrative project: the "Bookstore" API with five problems planted on purpose. You're going to see the complete setup (Docker Compose with PostgreSQL + FastAPI), the five problems you have to diagnose, and the integrative workflow that applies tools from all the modules of the guide. Capsule 07 is the setup; 08 is the execution and the portfolio-worthy BENCHMARKS.md.

Resources

1. PostgreSQL Docs — Function Volatility — the official reference for IMMUTABLE/STABLE/VOLATILE.
2. PostgreSQL Docs — pg_trgm — trigram indexes for LIKE.
3. PostgreSQL Docs — Temporary Tables — temp tables and ON COMMIT DROP.
4. Markus Winand — "WHERE clauses with functions" — deep analysis.
5. Crunchy Data — IN vs ANY — benchmarks of the two options.
6. PostgreSQL Wiki — Don't Do This — the official collection of anti-patterns.
7. PostgreSQL Docs — TABLESAMPLE — efficient sampling.

Capsule 06 of 08 — Module 8 — Database Performance & Query Tuning Guide