How one product schema changed over four years, one diagram at a time. 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.
How the schema evolved
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.
What we would change next time
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.
- 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.
Would love a follow-up on how you test this in CI.
Agreed. Start simple and only reach for this when you feel the pain.
Would this still apply on shared hosting without shell access?
Clear, practical, no fluff. More of this, please.
Tried it on a side project over the weekend and it worked first time, which never happens.
This finally clicked for me after reading the docs three times.
We run this in production at a fairly large scale and can confirm the caveats are real.