Four things that prevent most problems
- Connection pooling — opening a connection per request does not scale
- Parameterised queries, always, without exception
- Explicit relation loading rather than lazy loading in loops
- Indexes on the columns you filter, join and sort by
Lazy loading inside a loop is the most common performance problem in any framework with an ORM. One query becomes four hundred and nobody notices until the data grows.
Query multiplication is the usual cause
Fetching a list and then accessing a related object per item issues one query per row. On ten rows nobody notices; on a thousand the page takes twenty seconds.
- Load relations explicitly with the parent query
- Log query count per request during development
- Set a threshold that fails a test if exceeded
- Check the count on any page that handles lists
Transactions where they matter
Anything that writes several related records should do so in a transaction, so a partial failure does not leave inconsistent data.
Keep transactions short. A transaction held open while calling an external API blocks other work and can exhaust the connection pool.
Migrations need discipline
| Practice | Why |
|---|---|
| Every schema change as a migration | Environments stay in step |
| Backwards compatible where possible | Code can roll back independently |
| Tested on a production-sized copy | Long-running migrations are discovered early |
| Reviewed before applying | Schema mistakes are expensive |
| Never edited after being applied | Environments diverge otherwise |
Watch the growth
Queries that were fast at ten thousand rows can be slow at a million. Applications frequently slow down with no change in usage, purely because data accumulated.
Monitor query duration over time, and archive data that is no longer needed in the working set.