Common PostgreSQL mistakes AI coding agents make
Quick answer: AI coding agents repeat a predictable set of PostgreSQL mistakes: N+1 queries, missing indexes,
SELECT *, noLIMIT, non-sargable predicates, the wrong index type, table-locking migrations, and no connection pooling. They make these mistakes because they write locally correct code against a database they cannot see. The indexes, row counts, plans, locks, and limits all live outside the source.
The one root cause
Section titled “The one root cause”Every mistake below has the same cause. A coding agent reasons over the code. But query speed, migration safety, and connection capacity depend on the database and its runtime: indexes, data volume, statistics, lock state, and connection limits. The agent writes code that is idiomatic and locally correct, and the problem surfaces only when that code meets production.
Query mistakes
Section titled “Query mistakes”- N+1 queries. Loading a list and firing one query per row for a related record. It is the most common ORM performance bug, and it is invisible in the source. → How to find N+1 queries
- Missing indexes. Filtering or joining on an unindexed column, which becomes a sequential scan on a big table. Foreign-key columns are the usual miss. PostgreSQL does not index them for you. → How to find missing indexes
SELECT *and noLIMIT. Moving every column and returning unbounded result sets is fine on 100 rows and a problem on 10 million. → Why AI-generated SQL is slow- Non-sargable predicates. Wrapping an indexed column in a function
(
lower(email) = $1,created_at::date = ...) so the planner cannot use the index and falls back to a scan. → Why AI-generated SQL is slow - The wrong index type. Defaulting to B-tree for a
jsonb @>filter, which needs GIN, or aLIKE '%term%'search, which needs a trigram index from thepg_trgmextension (USING gin (col gin_trgm_ops)). Plain GIN has no operator class fortext. → Which index type should I use?
Migration mistakes
Section titled “Migration mistakes”- Table-locking
ALTERs. Running a migration that takesACCESS EXCLUSIVE(a rewriting type change, a volatile default) or blocks behind a live query and freezes the table. → Which ALTER TABLE statements lock a table - Blocking index builds. A plain
CREATE INDEXthat holds a write lock for the whole build instead of usingCREATE INDEX CONCURRENTLY. → How to add an index without downtime - Unsafe
NOT NULLcolumns. A backfill in one giantUPDATE, or aSET NOT NULLthat scans a huge table, instead of the staged safe pattern. → How to add a NOT NULL column safely
Operational mistakes
Section titled “Operational mistakes”- No connection pooling. Opening a connection per request or serverless
invocation until reaching
max_connections. Each connection is a whole backend process. → Connection pooling and “too many connections” - Long or idle transactions. Holding a transaction open across a network call, which keeps locks and blocks vacuum cleanup. → How to fix idle in transaction connections
- Never reading the plan. Shipping SQL without checking
EXPLAIN, so aSeq Scanor a row misestimate goes unnoticed. → How to read an EXPLAIN ANALYZE plan
How do I prevent all of this?
Section titled “How do I prevent all of this?”Stopping AI from writing SQL is not the fix, since the code is usually fine. Instead, give the system that writes the SQL the missing database context. Review queries and migrations against the real schema, indexes, and data volume:
- Rank real load with
pg_stat_statementsand pull plans for the top offenders. - Index what is scanned; drop what is unused.
- Treat every migration as a lock question before a correctness question.
- Pool connections and keep transactions short.
Why do AI coding agents make so many database mistakes?
Because performance and safety live in the database, not the code. An AI agent sees the schema and the query but not the indexes, row counts, data distribution, query plans, lock state, or connection limits. It writes code that is locally correct and reproduces patterns from its training data, and the problems only appear against real production data.
What is the most common AI database mistake?
The N+1 query: loading a list and then querying once per row for a related record. It reads perfectly in code and is the single most common ORM performance bug. Missing indexes on filtered or joined columns are a close second.
How do I stop AI-generated code from causing database problems?
Give whatever writes your SQL the database context it lacks (indexes, row counts, plans and lock state), and review new queries and migrations against the real schema, not just for correctness. With that context an agent can catch the same issues a senior reviewer would.