What the database does under load

Most application performance problems are database problems, and most of those are one of a small number of things.

The N plus one query

orders = db.query("SELECT * FROM orders LIMIT 100")
for o in orders:
    o.customer = db.query("SELECT * FROM customers WHERE id = ?", o.customer_id)

101 queries. Each is fast, and the round trips dominate. This is the single most common performance bug in applications using an ORM, because the loop looks innocent and the queries are hidden behind attribute access.

The fix is one query with a join, or one follow up query with IN. Most ORMs have an eager loading option, and knowing it exists is the difference between 120ms and 4 seconds.

Missing indexes

A query filtering on an unindexed column reads every row. Fine on ten thousand, fatal on ten million.

EXPLAIN ANALYZE tells you which. A sequential scan on a large table returning few rows is the signature.

Indexes are not free: each one slows writes and takes space. Index what you filter, join and sort on, not everything.

Transactions held open

with transaction():
    order = create_order(data)
    response = payment_api.charge(...)   # 2 seconds, holding a connection
    order.mark_paid(response.id)

The transaction holds locks and a connection for the length of an external call. Under load the pool empties and every request queues behind a third party's latency.

Keep transactions short and do slow work outside them. If the payment must be recorded atomically with the order, record the intent first, charge outside, then update.

Locks and the ordering rule

Two transactions taking the same rows in different orders deadlock. The database aborts one with an error, which the application must be prepared to retry rather than surface as a 500.

Taking locks in a consistent order prevents it, which is the same rule as operating system locking.

Connection pools

Covered in the DBMS course, and worth repeating because the symptom is misleading: fast queries and slow requests usually means waiting for a connection, not a slow database.

The checks worth running

Measure before optimising

Slow query logs and per request query counts tell you where the time goes. Optimising the query that felt slow rather than the one that is slow is common and wasteful.