A narrated walk through EXPLAIN ANALYZE output for a slow report query. Databases are usually the last place teams look when an application slows down, and the first place the problem turns out to be. This post is a practical walk through how we find and fix those problems.
Sample footage: Big Buck Bunny, an open movie by the Blender Foundation, released under a Creative Commons licence.
Look at the plan first
Every slow query investigation starts with EXPLAIN ANALYZE. It shows what the planner expected, what actually happened, and where the two diverged, which is almost always where the fix is.
- Read the query plan before adding an index.
- Backfill in batches, never in one transaction.
- Drop indexes nobody uses.
- Test migrations against a copy of production data.
Schema changes on a busy table need care. We add the new column, backfill in batches, switch reads, switch writes, and only then drop the old column, with a deploy between each step.
None of this is new, and that is the point. The techniques that last are usually the boring ones that are documented well and supported by the platform itself.
As always, the examples are simplified to fit on a page. The production version has more error handling and logging, but the shape of the code is the same.
Questions, corrections, and war stories are welcome in the comments. Several of our best follow-up posts started as a reader pointing out something we had missed.
We will revisit this in six months with fresh numbers. If the results change, we will update the post and note what changed at the end.
If you try this on your own project, start with one page or one service rather than the whole codebase. A small experiment gives you real numbers to argue with, and it is easy to roll back if the idea does not fit.
Adding manpower to a late software project makes it later.
Fred Brooks, The Mythical Man-Month, 1975
Any thoughts on how this interacts with server-side rendering?