Most application performance problems are database problems, and most database problems are one of five things. This chapter is those five things in enough depth that you can diagnose them rather than guess — and it is deliberately about the parts an application engineer actually touches, not about running a database.

An index is a sorted structure — almost always a B-tree — mapping column values to row locations. That single sentence explains all of its behaviour.

• The leftmost-prefix rule. An index on (a, b, c) serves queries filtering on a, on a and b, or on all three. It does nothing for a query filtering only on b. Column order in a composite index is a design decision, not a formatting one.
• Put equality before range. An index on (status, created_at) serves status = 'X' AND created_at > ... beautifully. Reverse the order and the range scan comes first, so the equality can only be applied as a filter over everything the range returned.
• A covering index avoids the table entirely. If every column the query needs is in the index, the database never dereferences a row. This is frequently a 10× improvement on its own and is the reason INCLUDE columns exist.
• Any function on the column kills the index. WHERE LOWER(email) = ... or WHERE DATE(created_at) = ... cannot use an ordinary index, because the index stores the raw values. Rewrite as a range, or build an expression index on exactly that expression.
• Leading wildcards kill it too. LIKE 'abc%' is a range scan; LIKE '%abc' is a full scan, because sorted order gives you no help.
• Indexes are not free. Every one must be maintained on every write, and they consume memory that would otherwise cache data. An unused index is a pure tax — and every serious database can tell you which of yours are unused.

EXPLAIN ANALYZE is the single most valuable database skill for an application engineer, and the thing to read is not the total time.

You fetch 100 orders, then in a loop fetch each order's customer. That is 101 queries. Each may take 1 ms in the database and 2 ms of round trip, so 300 ms of latency for work that is a single join.

It is caused by ORM lazy loading and it is invisible in code review, because the loop looks like ordinary object access. The fixes are eager loading, an explicit join, or a batched second query using WHERE id IN (...) — the last being a good pattern because it is two queries regardless of result size.

The durable fix is detection rather than vigilance: count queries per request and alert on the count. A request issuing 340 queries is a bug whether or not anyone has noticed it is slow yet, and a test that fails when a request's query count grows is one of the highest-value tests you can write.

Most engineers have heard of dirty reads and stop there. The two that matter in practice are further down.

• Lost update. Two transactions read a balance of 100, each adds 50, each writes 150. One update is gone. At READ COMMITTED this is entirely possible. The fixes are SELECT ... FOR UPDATE, an atomic UPDATE ... SET balance = balance + 50, or optimistic concurrency with a version column.
• Write skew. Two transactions read overlapping data, each verifies a constraint that still holds, each writes a different row, and the constraint is now violated. The canonical example: a rule that at least one doctor must remain on call; two doctors each see that two are on call, each removes themselves, both commit, nobody is on call. Snapshot isolation does not prevent this — only true serialisability does, or an explicit lock on the thing being constrained. It is the anomaly that produces the most baffling production data corruption, precisely because every individual transaction was correct.

Every connection to a database like PostgreSQL costs a process and memory on the server. A pool keeps a fixed number open and hands them out.

• More connections is not more throughput. Past the point where the database can genuinely do parallel work — broadly related to core count and disk parallelism — additional connections add context switching and lock contention and make everything slower. Small pools frequently outperform large ones, which is counter-intuitive enough that people rarely test it.
• Count the pool across your whole fleet. Twenty application instances with a pool of 50 each is a thousand connections. This is how a routine autoscaling event exhausts a database's connection limit and takes everything down.
• Pool exhaustion presents as application latency, not database latency. The database looks idle and healthy; requests are queuing for a connection that never arrives. Instrument wait time to acquire a connection as a first-class metric — without it this failure is genuinely hard to see.
• A long transaction holds its connection. An HTTP call inside a transaction is the classic disaster: a slow third party now holds a database connection for its entire timeout, and under load the pool drains. Never make a network call inside a transaction.