# 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 *`, no `LIMIT`, non-sargable
> predicates, the wrong index type, table-locking migrations, and no connection
> pooling, 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

Every mistake below is the same failure in a different costume.  Your coding agent
(Claude Code, Cursor, Copilot) reasons over your **code**. But whether a query is
fast, whether a migration is safe, and whether your connections will hold up are
properties of the **database and its runtime**, indexes, data volume, statistics,
lock state, connection limits.  The agent writes code that is idiomatic and locally
right, and the problem only surfaces when that code meets production.  Here is the
catalog.

## Query mistakes

- **N+1 queries.** Loading a list and firing one query per row for a related
  record, the most common ORM performance bug, and invisible in the source.
  → [How to find N+1 queries](https://www.dbgorilla.com/learn/postgres/how-to-find-n-plus-1-queries/)
- **Missing indexes.** Filtering or joining on an un-indexed 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](https://www.dbgorilla.com/learn/postgres/how-to-find-missing-indexes-in-postgresql/)
- **`SELECT *` and no `LIMIT`.** Moving every column and unbounded result sets, fine on 100 rows, a problem on 10 million.
  → [Why AI-generated SQL is slow](https://www.dbgorilla.com/learn/postgres/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](https://www.dbgorilla.com/learn/postgres/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?](https://www.dbgorilla.com/learn/postgres/which-postgresql-index-type-should-i-use/)

## Migration mistakes

- **Table-locking `ALTER`s.** Running a migration that takes `ACCESS EXCLUSIVE` (a
  rewriting type change, a volatile default) or that blocks behind a live query
  and freezes the table.
  → [Which ALTER TABLE statements lock a table](https://www.dbgorilla.com/learn/postgres/which-alter-table-statements-lock-a-postgres-table/)
- **Blocking index builds.** A plain `CREATE INDEX` that holds a write lock for
  the whole build, instead of `CREATE INDEX CONCURRENTLY`.
  → [How to add an index without downtime](https://www.dbgorilla.com/learn/postgres/how-to-add-a-postgres-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](https://www.dbgorilla.com/learn/postgres/how-to-add-a-not-null-column-safely-in-postgresql/)

## Operational mistakes

- **No connection pooling.** Opening a connection per request or serverless
  invocation until you hit `max_connections`, each connection is a whole backend
  process.
  → [Connection pooling and "too many connections"](https://www.dbgorilla.com/learn/postgres/postgresql-connection-pooling-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](https://www.dbgorilla.com/learn/postgres/how-to-fix-idle-in-transaction-connections/)
- **Never reading the plan.** Shipping SQL without checking `EXPLAIN`, so a
  `Seq Scan` or a row misestimate goes unnoticed.
  → [How to read an EXPLAIN ANALYZE plan](https://www.dbgorilla.com/learn/postgres/how-to-read-a-postgres-explain-analyze-plan/)

## How do I prevent all of this?

The fix is not "stop using AI to write SQL", the code is usually fine.  It is to
give whatever writes the SQL the database context it is missing, and to review
queries and migrations against the real schema, indexes, and data volume:

- Rank real load with [`pg_stat_statements`](https://www.dbgorilla.com/learn/postgres/how-to-identify-slow-queries-in-postgresql/)
  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.

## How DBGorilla helps

DBGorilla closes the gap behind all of these: it connects to your database
read-only and gives your AI coding agent (Claude Code, Cursor) the real context, indexes, row counts, `pg_stat_statements` load, query plans, lock and connection
state, so the agent can catch an N+1, a missing index, a locking migration, or a
connection storm the way a senior engineer would in review.  It surfaces and
explains through your agent; it does not touch production.
[Get started free →](https://app.dbgorilla.com/signup)

## FAQ

**Why do AI coding agents make so many database mistakes?**
Performance and safety live in the database, not the code.  The agent sees the
query but not the indexes, row counts, plans, locks, or limits, so its
locally-correct code only reveals its cost against real data.

**What is the most common AI database mistake?**
The N+1 query, a per-row query in a loop that reads clean in code.  Missing
indexes on filtered/joined columns are a close second.

**How do I stop it?**
Give whatever writes your SQL the database context it lacks, and review queries
and migrations against the real schema, not just for correctness.

## Related

- [Why AI-generated SQL is slow](https://www.dbgorilla.com/learn/postgres/why-ai-generated-sql-is-slow/)
- [How to find N+1 queries in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-find-n-plus-1-queries/)
- [How to identify slow queries in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-identify-slow-queries-in-postgresql/)
- [How to optimize ORM-generated queries](https://www.dbgorilla.com/learn/postgres/how-to-optimize-orm-generated-queries/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)