Guides
Database performance guides
Short guides to MySQL and PostgreSQL performance that stand on their own. Looking for how to use dbtempo itself? See the documentation.
PostgreSQL
Create an index in PostgreSQL: when it helps
How to create a PostgreSQL index without blocking writes, which column goes first, and when an index will not be used or will cost more than it saves.
7 min
PostgreSQL index types: B-tree, GIN, partial
Which PostgreSQL index type to use for which query: B-tree, hash, GIN, GiST, BRIN, and the partial, expression and covering variations.
7 min
Read a PostgreSQL EXPLAIN plan and find the slow step
Read a PostgreSQL EXPLAIN plan from the inside out, spot a Seq Scan or Sort, and get the plan analyzed in paste mode.
6 min
EXPLAIN ANALYZE in PostgreSQL, and when not to run it
What EXPLAIN ANALYZE adds to a PostgreSQL plan (real times and row counts), why it runs your query, and how to use it safely.
7 min
Find long-running queries in PostgreSQL
See which PostgreSQL queries are running right now and for how long, tell a busy query from a stuck one, and cancel one safely.
6 min
Find your slowest PostgreSQL queries with pg_stat_statements
Turn on pg_stat_statements, rank queries by total time, and read the numbers right. A query run a million times often costs more than the single slowest run.
7 min
Test a PostgreSQL index before you build it with HypoPG
Use the HypoPG extension to ask the planner whether a new index would be used, without building it, locking the table or waiting for it to finish.
6 min
Find what is blocking a query in PostgreSQL
List the PostgreSQL sessions waiting on a lock with pg_blocking_pids, find the session they wait for, and decide whether to wait, cancel or end it.
6 min
Find idle-in-transaction sessions in PostgreSQL
Find PostgreSQL sessions that sit idle inside an open transaction, why they hold locks and block cleanup, and how to end them and stop them recurring.
5 min
Read a PostgreSQL deadlock error
Read the PostgreSQL deadlock detected error line by line, see why two transactions ended up waiting on each other, and change the code so it stops.
6 min
Prisma connection pool exhaustion: find the cause
Why a Prisma app runs out of database connections: pool size against max_connections, transactions that hold a connection, and a write loop inside $transaction.
6 min
MySQL and MariaDB
Create, show and drop indexes in MySQL
The MySQL statements to create, list and drop an index, how column order works, and how to find indexes nothing uses.
6 min
Why MySQL ignores your index
Why a MySQL query skips an index you created (a function on the column, a type mismatch, the wrong column order) and how to check each with EXPLAIN.
6 min
Read a MySQL EXPLAIN plan and find the slow step
Read the columns of MySQL EXPLAIN output, spot a full table scan or filesort, and get the plan analyzed in paste mode.
6 min
MySQL slow query log: turn it on, read it
Turn on the MySQL slow query log, choose a sensible threshold, read an entry, and find the queries that cost the most.
6 min
Find InnoDB lock waits in MySQL and MariaDB
See which InnoDB transaction blocks another in MySQL and MariaDB, what error 1205 and error 1213 mean, and where to read the latest deadlock report.
6 min