The 5 most common Postgres SQL mistakes

Most performance problems in a Postgres application come from one of a short list of avoidable SQL patterns. The list has not changed much in a decade. What follows are the five that show up most often in real codebases, each with the specific symptom it produces in EXPLAIN ANALYZE, the fix, and the reason the fix works. All of these apply to any Postgres, managed or self-hosted.

1. Filtering on an unindexed column

The most common and the most damaging. A query like SELECT * FROM sessions WHERE user_id = $1 on a 50-million-row table runs a sequential scan if user_id is not indexed, reads every page of the table, and takes seconds per call. At low traffic the performance hit is invisible. At high traffic it saturates CPU.

The symptom in EXPLAIN is a Seq Scan node with a large Rows Removed by Filter count on a table that has more than a few thousand rows.

EXPLAIN ANALYZE SELECT * FROM sessions WHERE user_id = 12345;

 Seq Scan on sessions
   Filter: (user_id = 12345)
   Rows Removed by Filter: 49,999,988
   (actual time=2104.2..2847.1 rows=12 loops=1)

The fix is an index:

CREATE INDEX CONCURRENTLY sessions_user_id_idx ON sessions (user_id);

Always CONCURRENTLY in production. A plain CREATE INDEX holds a lock that blocks writes for the duration of the build, which on a large table is long enough to cause an outage. Verify after that the new plan is an Index Scan, not a Seq Scan.

2. SELECT * when you need three fields

SELECT * is fine during development. In production it costs real money. Every column in the table is serialized out of the heap, shipped over the network, and deserialized in the application. For a table with a TOAST'd JSONB column, a wide text column, or a row-level array, SELECT * can move a hundred kilobytes per row when the caller only needed thirty bytes.

The harder cost is that SELECT * prevents index-only scans. An index-only scan returns rows directly from the index without touching the heap, which is the fastest plan Postgres can produce for a read-heavy hot path. Index-only scans require that every column the query returns is in the index. SELECT * guarantees at least one column is not, so the planner has to fall back to a regular index scan that visits the heap for every row.

The fix is explicit. List the columns you actually need. If there are three, write three. If the query is a hot path, consider a covering index:

CREATE INDEX CONCURRENTLY sessions_user_id_covering_idx
  ON sessions (user_id) INCLUDE (created_at, expires_at);

Combined with SELECT user_id, created_at, expires_at FROM sessions WHERE user_id = $1, this can produce an Index Only Scan plan. Ten times less work than a heap lookup.

3. Deep OFFSET pagination

Pagination with LIMIT 20 OFFSET N is fine for the first few pages and catastrophic past about N=10,000. The reason: Postgres has to produce the first N rows to throw them away, and the only way to do that is to read them. OFFSET 100000 means reading a hundred thousand rows and discarding them, every page.

A cold version of OFFSET 100000 on a million-row table takes seconds. Users rarely page that far, but web crawlers and abusive scripts do, and a single bad actor can saturate the database by paginating to the end of a large table.

The fix is keyset pagination. Instead of "give me rows 100,001 through 100,020 by count," ask "give me the next 20 rows after this specific ID." The query references the primary key (or any unique index), which Postgres can seek to directly:

-- First page:
SELECT id, title, created_at FROM posts
ORDER BY id DESC LIMIT 20;

-- Next page, where $last_id is the id of the last row on the previous page:
SELECT id, title, created_at FROM posts
WHERE id < $last_id
ORDER BY id DESC LIMIT 20;

Every page takes the same constant time regardless of depth, because every page is an index seek followed by twenty sequential reads.

4. ORM-generated N+1 cascade

An ORM that helpfully auto-loads related objects is a performance footgun in disguise. A list of 20 projects with related owner and latest-task information, fetched through project.owner.name and project.latest_task.title in application code, expands into 1 parent query plus 40 child queries, one round trip each.

The symptom: no single query is slow, but the page is. The signature in pg_stat_statements is a few simple SELECTs with call counts in the hundreds of thousands and mean_exec_time under a millisecond. EXPLAIN ANALYZE cannot see this, because the planner works on one query at a time and has no visibility into the loop that issued them.

The fix is one query that asks for everything at once. JOIN handles the one-to-one relationships. LATERAL handles the "most recent task per project" pattern that JOIN alone cannot express cleanly:

SELECT p.id, p.name, o.full_name AS owner_name, latest.title AS latest_task
FROM projects p
JOIN users o ON o.id = p.owner_id
LEFT JOIN LATERAL (
  SELECT title FROM tasks t
  WHERE t.project_id = p.id
  ORDER BY t.created_at DESC LIMIT 1
) AS latest ON true
WHERE p.user_id = $1;

Most ORMs have an escape hatch for this: includes, preload, joinedload, or a raw-SQL method. Use it.

5. Casting a column inside the WHERE clause

A WHERE clause like WHERE lower(email) = $1 or WHERE created_at::date = '2025-01-15' cannot use an index on email or created_at. The planner treats the expression as a function applied to every row, which forces a sequential scan or a much less selective index choice. The query is correct, and slow.

Two fixes. If the cast is incidental, move it to the parameter side of the comparison:

-- Slow, cannot use index on created_at:
SELECT * FROM orders WHERE created_at::date = '2025-01-15';

-- Fast, can use index on created_at:
SELECT * FROM orders WHERE created_at >= '2025-01-15' AND created_at < '2025-01-16';

If the cast is essential (case-insensitive email lookup, for example), create an expression index that matches the exact expression in the query:

CREATE INDEX CONCURRENTLY users_lower_email_idx ON users (lower(email));

-- Now this query uses the index:
SELECT * FROM users WHERE lower(email) = 'ada@example.com';

Expression indexes are real indexes, with real maintenance cost on writes. Only create one for a case-insensitive or function-applied lookup that actually runs on a hot path.

What a pre-merge check catches

Three of these five are deterministically detectable at PR time from the SQL alone. A check that runs against the repo and the live schema can flag:

• Mistake 1 by comparing filtered columns in the DDL against the existing index list on the target table.
• Mistake 2 by grepping for SELECT * in migration files and new application code.
• Mistake 5 by parsing WHERE clauses and detecting function calls or casts on columns that match an existing index's base column but not its expression.

That is what Datapace does before merge. Datapace is the context layer and security gateway for production databases: it knows what's there, what it means, and how it connects, and it uses that context to flag these patterns before they ship. Honest limits: the check cannot detect Mistakes 3 and 4 from SQL alone, because those live in application call patterns. pg_stat_statements call-count regression after deploy is how Datapace catches those, and the attribution to a commit is probabilistic, based on timing. If you want to see how it works on your database, get in touch.

Sources

1. PostgreSQL documentation, Indexes
2. PostgreSQL documentation, Expression indexes
3. PostgreSQL documentation, Index-only scans
4. Markus Winand, Keyset pagination