Skip to content

Common PostgreSQL mistakes AI coding agents make

Last updated

View as Markdown

Quick answer: AI coding agents repeat a predictable set of PostgreSQL mistakes: N+1 queries, missing indexes, SELECT *, no LIMIT, 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.

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.

  • 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 no LIMIT. 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 a LIKE '%term%' search, which needs a trigram index from the pg_trgm extension (USING gin (col gin_trgm_ops)). Plain GIN has no operator class for text. → Which index type should I use?
  • Table-locking ALTERs. Running a migration that takes ACCESS 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 INDEX that holds a write lock for the whole build instead of using CREATE INDEX CONCURRENTLY. → How to add an index without downtime
  • Unsafe NOT NULL columns. A backfill in one giant UPDATE, or a SET NOT NULL that scans a huge table, instead of the staged safe pattern. → How to add a NOT NULL column safely

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_statements and 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.