DBGorilla Learn

Practical database engineering. Query plans, indexes, locks, capacity, and the AI-generated SQL traps that keep showing up. Every article answers one question, answers it in the first paragraph, and shows the SQL.

Every SQL statement here runs against a real server before it ships: PostgreSQL 15, 16, 17 and 18, and MySQL 8.4. Nothing is published on the strength of looking correct.

PostgreSQL

35 articles, grouped by the problem you arrived with.

Finding what is slow

• How to identify slow queries, rank real load with pg_stat_statements, sorted by total time rather than mean.
• What pg_stat_statements tells you, and what it does not, what the view captures, what it cannot, and how to read it honestly.
• How to read an EXPLAIN ANALYZE plan, estimated versus actual rows, scan types, and the misestimate signal.
• Why did my query suddenly get slow?, plan flips, stale statistics, and getting the index back.
• How to fix high CPU, trace it to the query rather than the config knob.
• How to optimize query performance, sargability, anti-joins, keyset pagination, and the rewrites that quietly change your results.
• Why are my queries spilling to disk?, reading temp-file volume and sizing work_mem from evidence rather than a formula.
• Why do prepared statements get slow after the fifth execution?, the custom-to-generic plan switch and plan_cache_mode.

Indexing

• Which index type should I use?, B-tree, GIN, GiST, BRIN, SP-GiST and Hash, and when each wins.
• How to find missing indexes, spot sequential scans on big tables, and rule out the indexes you must not drop.
• How to add an index without downtime, CREATE INDEX CONCURRENTLY, its caveats, and recovering from a failed build.
• Do I still need the leading column?, multicolumn indexes and what PostgreSQL 18 skip scan changed.
• Are UUID primary keys still bad?, what UUIDv7 fixes, what it does not, and why a retrofit is not a type change.

Schema changes and migrations

• Which ALTER TABLE statements lock a table, which forms rewrite or scan under ACCESS EXCLUSIVE, and how to avoid the stall.
• How to add a NOT NULL column safely, the metadata-only fast path, and what PostgreSQL 18 collapsed from four steps to two.
• How to handle migrations safely, lock levels, lock_timeout, and the expand-backfill-contract pattern.
• What is table bloat and how do I fix it?, MVCC dead tuples, VACUUM versus VACUUM FULL, and pg_repack.

Locks, transactions and connections

• Understanding lock contention, pg_locks, pg_blocking_pids(), lock modes and deadlocks.
• How to fix idle in transaction connections, the open transaction that holds locks and blocks vacuum.
• Connection pooling and “too many connections”, why each connection is a process, and why pooling beats raising the limit.

Keeping it healthy

• Why is autovacuum not keeping up?, the three different causes, the xmin horizon, and wraparound.
• My pg_wal directory filled the disk, what is pinning WAL, and why nothing in there is safe to delete by hand.
• Configuration tuning for production, the settings worth changing, and the cache-hit folklore worth ignoring.
• Database technical debt, finding it in the catalog rather than from memory, and repaying it on a schedule.
• Performance regression testing, catching slowdowns before production, weighted by how often the query actually runs.

Capacity and cost

• When should I scale up instead of optimizing?, telling a hardware problem from a query problem, and what doubling the instance actually buys.

AI-generated SQL

• Why AI-generated SQL is slow, the database-blindness gap: N+1s, missing indexes, non-sargable predicates.
• Why AI-generated SQL looks right and is not, a failure taxonomy built from real defects in this library.
• Common mistakes AI coding agents make, the whole cluster in one checklist.
• How to optimize ORM-generated queries, eager loading across Django, Rails, Prisma and Sequelize, plus foreign-key indexes.
• How to find N+1 queries, the most common ORM performance bug: spot it, confirm it, fix it.

Giving an agent database access

• Is it safe to let an AI agent access production?, the real risks, and which of them grants actually control.
• How to set up a read-only role for an AI agent, least privilege that survives a SECURITY DEFINER audit.
• How do I get my agent to read EXPLAIN ANALYZE?, giving it the plan instead of the guess.
• Why does my agent hallucinate columns that do not exist?, why the schema in your repo is not the schema in your database.

---

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) your real database context, so it stops guessing about the one system it cannot see. Get started free

MySQL

• MySQL databases vs PostgreSQL schemas, what maps to what, and why a cross-database join works in one and not the other.