Common SQL Anti-Patterns That Slowly Destroy Your Database Performance

The codebase that performs fine until it doesn't

You inherit a backend. Queries are slow but not broken. The database is under constant, low-grade stress. You profile it and find no single catastrophic query — instead, dozens of queries each doing something slightly wrong: a WHERE clause that defeats an index, a SELECT * that transfers megabytes of unused data, a join that multiplies rows before aggregation. Each one is a few hundred milliseconds slower than it should be. Together, they saturate the database under production load.

This is what SQL anti-patterns look like in practice. Not explosions — erosion. Here is a consolidated list of the patterns I see most consistently in production codebases, and how to eliminate them.

Anti-pattern 1: SELECT * in application queries

Fetching all columns when only a few are needed adds unnecessary I/O, network transfer, and memory usage. On tables with wide rows (JSONB columns, TEXT fields, binary data), the overhead is significant.

-- Anti-pattern
SELECT * FROM products WHERE category_id = 5;

-- Fix: enumerate only what you need
SELECT id, name, price, stock_quantity FROM products WHERE category_id = 5;

The secondary benefit: explicit column lists catch schema changes at the query layer instead of silently returning unexpected data after a column is renamed or removed.

Anti-pattern 2: N+1 queries

Fetching a list of records and then querying for related data one row at a time:

-- Anti-pattern: 1 query for orders + N queries for users
SELECT * FROM orders WHERE status = 'pending';
-- For each order: SELECT * FROM users WHERE id = ?

-- Fix: JOIN or eager load in a single query
SELECT o.*, u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending';

At 1,000 pending orders, N+1 is 1,001 queries. The JOIN is 1 query. The difference in total latency is an order of magnitude.

Anti-pattern 3: Functions on indexed columns in WHERE

Any function applied to an indexed column in a WHERE clause defeats the index:

-- Anti-pattern: index on email is not used
WHERE LOWER(email) = 'user@example.com'
WHERE EXTRACT(YEAR FROM created_at) = 2024
WHERE LENGTH(description) > 100

-- Fix: rewrite to expose the bare column
WHERE email = 'user@example.com'  -- Normalize email at write time
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
-- Or use a functional index:
CREATE INDEX idx_lower_email ON users(LOWER(email));

Anti-pattern 4: Implicit type conversions in comparisons

Comparing a column to a value of a different type can cause an implicit cast that defeats indexes:

-- Anti-pattern: user_id is BIGINT, comparing to a string
WHERE user_id = '42'

-- Anti-pattern: date column compared to datetime literal (in some engines)
WHERE order_date = '2024-01-15 00:00:00'

-- Fix: match types explicitly
WHERE user_id = 42
WHERE order_date = '2024-01-15'::DATE

Type mismatches are common in ORMs that don't enforce parameter type binding. Audit your query logs for type cast warnings.

Anti-pattern 5: OFFSET pagination at large page numbers

-- Anti-pattern: reads and discards 100,000 rows to return 50
SELECT * FROM events ORDER BY created_at DESC LIMIT 50 OFFSET 100000;

-- Fix: keyset pagination
SELECT * FROM events
WHERE created_at < :last_seen_created_at
ORDER BY created_at DESC
LIMIT 50;

Offset pagination degrades linearly with page depth. Keyset pagination is constant-time regardless of position. The tradeoff: keyset pagination doesn't support random page access — you must navigate sequentially.

Anti-pattern 6: Storing delimited data in a single column

-- Anti-pattern: comma-separated IDs in a TEXT column
CREATE TABLE posts (
  id BIGINT PRIMARY KEY,
  tag_ids TEXT  -- '1,4,7,23'
);

-- Query requires string parsing — no index possible on individual IDs
WHERE tag_ids LIKE '%,4,%'

-- Fix: proper junction table
CREATE TABLE post_tags (
  post_id BIGINT REFERENCES posts(id),
  tag_id BIGINT REFERENCES tags(id),
  PRIMARY KEY (post_id, tag_id)
);
CREATE INDEX ON post_tags(tag_id);

Delimited values violate 1NF, cannot be indexed meaningfully, and require application-side parsing for every query.

Anti-pattern 7: Using OR with indexed columns unnecessarily

-- Anti-pattern: OR between different indexed columns forces a full scan in many planners
WHERE status = 'active' OR region = 'EU'

-- Fix: UNION of indexed lookups (if both columns are indexed separately)
SELECT * FROM users WHERE status = 'active'
UNION
SELECT * FROM users WHERE region = 'EU';

PostgreSQL has bitmap scan support that can combine index results for OR conditions, but it's not guaranteed. For OR on different columns, explicit UNION is more predictable.

Anti-pattern 8: Missing NOT NULL constraints

Allowing NULL in columns that should never be NULL defeats constraint enforcement and propagates NULL through downstream calculations silently.

-- Anti-pattern: allows NULL in columns with business meaning
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,           -- Can be NULL? What does an order without a user mean?
  total DECIMAL(10,2),      -- Can be NULL? What does a NULL total mean?
  created_at TIMESTAMPTZ    -- Can be NULL? A record with no creation time?
);

-- Fix: enforce NOT NULL at the database level
CREATE TABLE orders (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Constraints at the database level protect data integrity across all access paths — application code, direct SQL, imports, migrations. Application-only validation is insufficient.

Anti-pattern 9: Transactions that span user interaction or external calls

# Anti-pattern: transaction held open while waiting for user or external service
with db.transaction():
    cart = Cart.lock_for_checkout(user_id)
    payment_result = payment_gateway.charge(cart.total)  # 1-3 seconds
    Order.create(cart=cart, payment=payment_result)

The row locks acquired at the start of the transaction are held for the duration of the external call. Fix: narrow the transaction to only the database writes.

Anti-pattern 10: COUNT(*) to check existence

-- Anti-pattern: reads all matching rows to get a count, then discards it
SELECT COUNT(*) FROM subscriptions WHERE user_id = 42 AND status = 'active';
-- In application: if count > 0: ...

-- Fix: use EXISTS — stops at the first match
SELECT EXISTS (
  SELECT 1 FROM subscriptions WHERE user_id = 42 AND status = 'active'
);

With a covering index on (user_id, status), EXISTS is an index-only scan that stops after one entry. COUNT(*) must count all matching rows.

The audit

Run these ten checks against your codebase. Most mature production codebases have at least five of them. None requires a major refactor — each is a targeted fix. The aggregate improvement in query performance, database load, and data correctness from eliminating them is consistently larger than any single architectural change.