# 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](/learn/postgres/how-to-identify-slow-queries-in-postgresql/)**, 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](/learn/postgres/what-pg-stat-statements-tells-you/)**, what the view captures, what it cannot, and how to read it honestly. - **[How to read an EXPLAIN ANALYZE plan](/learn/postgres/how-to-read-a-postgres-explain-analyze-plan/)**, estimated versus actual rows, scan types, and the misestimate signal. - **[Why did my query suddenly get slow?](/learn/postgres/why-did-my-postgres-query-suddenly-get-slow/)**, plan flips, stale statistics, and getting the index back. - **[How to fix high CPU](/learn/postgres/how-to-fix-high-cpu-on-a-postgresql-database/)**, trace it to the query rather than the config knob. - **[How to optimize query performance](/learn/postgres/how-to-optimize-postgresql-query-performance/)**, sargability, anti-joins, keyset pagination, and the rewrites that quietly change your results. - **[Why are my queries spilling to disk?](/learn/postgres/why-are-my-queries-spilling-to-disk-work-mem/)**, reading temp-file volume and sizing `work_mem` from evidence rather than a formula. - **[Why do prepared statements get slow after the fifth execution?](/learn/postgres/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?](/learn/postgres/which-postgresql-index-type-should-i-use/)**, B-tree, GIN, GiST, BRIN, SP-GiST and Hash, and when each wins. - **[How to find missing indexes](/learn/postgres/how-to-find-missing-indexes-in-postgresql/)**, spot sequential scans on big tables, and rule out the indexes you must not drop. - **[How to add an index without downtime](/learn/postgres/how-to-add-a-postgres-index-without-downtime/)**, `CREATE INDEX CONCURRENTLY`, its caveats, and recovering from a failed build. - **[Do I still need the leading column?](/learn/postgres/do-i-still-need-the-leading-column-multicolumn-indexes/)**, multicolumn indexes and what PostgreSQL 18 skip scan changed. - **[Are UUID primary keys still bad?](/learn/postgres/are-uuid-primary-keys-still-bad-uuidv7/)**, 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](/learn/postgres/which-alter-table-statements-lock-a-postgres-table/)**, which forms rewrite or scan under `ACCESS EXCLUSIVE`, and how to avoid the stall. - **[How to add a NOT NULL column safely](/learn/postgres/how-to-add-a-not-null-column-safely-in-postgresql/)**, the metadata-only fast path, and what PostgreSQL 18 collapsed from four steps to two. - **[How to handle migrations safely](/learn/postgres/how-to-handle-database-migrations-safely/)**, lock levels, `lock_timeout`, and the expand-backfill-contract pattern. - **[What is table bloat and how do I fix it?](/learn/postgres/what-is-table-bloat-in-postgresql-and-how-to-fix-it/)**, MVCC dead tuples, `VACUUM` versus `VACUUM FULL`, and `pg_repack`. ### Locks, transactions and connections - **[Understanding lock contention](/learn/postgres/understanding-lock-contention-in-postgresql/)**, `pg_locks`, `pg_blocking_pids()`, lock modes and deadlocks. - **[How to fix idle in transaction connections](/learn/postgres/how-to-fix-idle-in-transaction-connections/)**, the open transaction that holds locks and blocks vacuum. - **[Connection pooling and "too many connections"](/learn/postgres/postgresql-connection-pooling-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?](/learn/postgres/why-is-autovacuum-not-keeping-up/)**, the three different causes, the xmin horizon, and wraparound. - **[My pg_wal directory filled the disk](/learn/postgres/pg-wal-directory-filling-up-disk/)**, what is pinning WAL, and why nothing in there is safe to delete by hand. - **[Configuration tuning for production](/learn/postgres/postgresql-configuration-tuning-for-production/)**, the settings worth changing, and the cache-hit folklore worth ignoring. - **[Database technical debt](/learn/postgres/database-technical-debt-how-to-identify-and-fix/)**, finding it in the catalog rather than from memory, and repaying it on a schedule. - **[Performance regression testing](/learn/postgres/database-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?](/learn/postgres/when-to-scale-up-vs-optimize-database-queries/)**, telling a hardware problem from a query problem, and what doubling the instance actually buys. ### AI-generated SQL - **[Why AI-generated SQL is slow](/learn/postgres/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](/learn/postgres/why-ai-generated-sql-looks-right-and-isnt/)**, a failure taxonomy built from real defects in this library. - **[Common mistakes AI coding agents make](/learn/postgres/common-postgresql-mistakes-ai-coding-agents-make/)**, the whole cluster in one checklist. - **[How to optimize ORM-generated queries](/learn/postgres/how-to-optimize-orm-generated-queries/)**, eager loading across Django, Rails, Prisma and Sequelize, plus foreign-key indexes. - **[How to find N+1 queries](/learn/postgres/how-to-find-n-plus-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?](/learn/postgres/is-it-safe-to-let-an-ai-agent-access-my-production-database/)**, the real risks, and which of them grants actually control. - **[How to set up a read-only role for an AI agent](/learn/postgres/how-to-set-up-a-read-only-postgres-role-for-an-ai-agent/)**, least privilege that survives a `SECURITY DEFINER` audit. - **[How do I get my agent to read EXPLAIN ANALYZE?](/learn/postgres/how-to-get-your-ai-agent-to-read-explain-analyze/)**, giving it the plan instead of the guess. - **[Why does my agent hallucinate columns that do not exist?](/learn/postgres/why-does-my-ai-agent-hallucinate-columns-that-dont-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](https://app.dbgorilla.com/signup)* ## MySQL - **[MySQL databases vs PostgreSQL schemas](/learn/mysql/mysql-databases-vs-postgres-schemas/)**, what maps to what, and why a cross-database join works in one and not the other.