Almost every performance problem we are called in to fix is a database problem wearing a different costume. The application looks slow, the servers look busy, and underneath it is a handful of queries that were fine with ten thousand rows and are not fine with ten million.
Measure before you optimise
Enable slow query logging and look at the actual top offenders by total time, not by individual duration. A query taking 40ms called 900 times per request costs far more than a 2-second report someone runs weekly. Optimising the visibly slow query while ignoring the frequently slow one is the most common misdirection of effort.
Total time consumed beats individual duration. The expensive query is often the fast one, called often.
The N+1 is still the most common defect
Fetch a list, then loop and fetch a related record for each item. The ORM makes it invisible in the code and it works perfectly in development with twenty rows. In production with two thousand it is two thousand round trips. Eager loading fixes it; the harder part is spotting it, which is what query-count assertions in tests are for.
Indexes: the ones people miss
Single-column indexes on obvious lookups usually exist. What is typically missing is composite indexes matching the actual filter-and-sort combination, partial indexes for queries that always filter on the same condition, and covering indexes that let the database answer entirely from the index without touching the table.
Unbounded queries are a time bomb
A query with no limit works until the table grows. Every list endpoint needs pagination, and keyset pagination outperforms offset pagination dramatically at depth — OFFSET 100000 requires the database to walk 100,000 rows before discarding them. This is a design decision, not a tuning exercise.
Read the plan, do not guess
EXPLAIN ANALYZE tells you what the database actually did rather than what you assume. Sequential scans on large tables, nested loops over big row counts, and estimates wildly different from actual rows are the three signals worth learning to recognise. That last one usually means stale statistics, which is a one-command fix.
Know when to stop optimising
At some point the query is correct and the volume is genuinely large, and the answer is architectural: a read replica, a materialised view, a cache, or moving analytical work off the transactional database entirely. Recognising that boundary saves weeks of diminishing returns on index tuning.
