Hot take: an ORM is great for CRUD and quietly terrible for anything that smells like reporting. Yeah, I said it. I lost a week to this last month, so here are the receipts.
Context: a Django app (admin panels, invoices, the boring kind), and finance wants a usage report every month. Three years, no complaints. Then our customer count passed 40,000 and the endpoint started timing out.
1. The N+1 I refused to believe
First I blamed the database: threw indexes at it, bumped the instance size (that never works, I do it anyway), nothing moved. Then query logging showed 4,000 lines printing for a single request. The ORM was lazy-loading one related row per invoice. I’d skipped the eager version two years earlier because it “looked heavy.” (You see where this is going.)
2. Filtering in Python because it felt easier
Same endpoint, deeper down: I’d added a filter that pulled 50,000 rows into memory and kept 300 with a list comprehension. In SQL that’s one WHERE clause. In my code it was a loop that called a helper hitting the database once per row. Still with me? Not my finest hour.
3. The bulk update that wasn’t
The one that scared me: a job I wrote flipped a status flag on a few thousand accounts through the ORM, one query per account. A deploy restarted the worker halfway through, it resumed at the wrong offset, and some accounts got flagged twice, others never did. One UPDATE with a WHERE would have been atomic and boring.
4. I couldn’t read my own query plan
By then the report took 38 seconds, so I ran EXPLAIN ANALYZE, except the ORM had aliased every join to t4 and t7 and the plan read like a ransom note. A senior asked me what t7 was. Rebuilding the query by hand showed the real problem: it had joined a relation I never touched in the template.
5. What I actually do now
Here’s the thing. I first threatened the ORM with deletion, which lasted a day before I remembered how much I hate hand-writing INSERTs. Now we split: ORM for CRUD and migrations, plain SQL for reports and anything on a schedule. Dynamic filters go through a small query builder, not string concatenation. (I tried concatenation once, in staging, and got lucky.)
38 seconds down to 1.9. Same data, same indexes, one honest query. The raw SQL I’d avoided for three years turned out to be the cheap option.
What I’d do different
- Turn on query logging on day one, not after the pager goes off.
- If a loop touches the database, it should probably not be a loop.
- If the query plan is full of t7 aliases, fix the query before buying hardware.
I half expected a rerun of my missing index saga. It wasn’t. The indexes were fine. I was the problem.
External resources
- PostgreSQL’s SQL tutorial
- Use The Index, Luke, best free material on query performance
- freeCodeCamp’s SQL course if it’s been a while
So yeah. Three years of trusting the ORM, one timeout, and a report that finishes before my coffee does. The ORM still does my CRUD and it’s fine at that. We just don’t discuss reports in front of it.