Lazy loading is the usual culprit
Fetching a list of records and then accessing a related object for each one issues a query per record. On twenty rows nobody notices; on a thousand the page takes twenty seconds.
We have seen a single Django page issuing over four hundred queries because a template accessed a related object inside a loop. No amount of caching fixes that properly.
Load relations explicitly
- Select related objects in the same query for one-to-one and foreign keys
- Prefetch for many-to-many and reverse relations
- Only fetch the fields you need on large models
- Watch out for relations accessed in templates, which are easy to miss
Count queries as a test
- Log query count per request during development
- Set a threshold that fails a test if exceeded
- Check every page that renders a list
- Re-check after any template change
A test that asserts a page issues no more than a set number of queries catches the regression that would otherwise appear months later as unexplained slowness.
Index what you filter on
| Index | When |
|---|---|
| Foreign keys | Usually created, but check |
| Filtered fields | Any field in a common filter |
| Ordering fields | Anything you sort large lists by |
| Composite indexes | Common multi-field filters |
| Nothing else | Each index costs on writes |
Paginate everything
A view that loads every record works fine in development with two hundred rows and fails in production with two hundred thousand. Paginate lists from the start.
The same applies to exports — stream them or process in batches rather than building the whole result in memory.