The expand-and-contract migration pattern with the exact SQL we ran. 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.
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.
-- Step 1: add the new column
ALTER TABLE customers ADD COLUMN display_name text;
-- Step 2: backfill in batches
UPDATE customers
SET display_name = full_name
WHERE id IN (
SELECT id FROM customers
WHERE display_name IS NULL
LIMIT 5000
);
-- Step 3 (after the app reads display_name): drop the old column
ALTER TABLE customers DROP COLUMN full_name;
The migration we ran
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.
- 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.
Keeping it fast
Indexes are not free. Each one slows writes and takes memory, so we review unused indexes every quarter and drop the ones the statistics say nobody reads.
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.
We have been running this approach for several months now. The initial setup took about a day, and the ongoing cost is close to zero once the team knows where to look.
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.
Before you ship this
ALTER TABLE takes a lock. On large tables, set a short lock_timeout so a stuck migration fails fast instead of blocking every query behind it.
Any fool can write code that a computer can understand. Good programmers write code that humans can understand.
Martin Fowler, Refactoring, 1999
Wrapping up
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.
Counterpoint: for small teams the simpler approach in the first section is usually enough.
Glad it helped!
Fair point. For a monorepo I would probably split the configuration per package.
How does this hold up on older Safari versions? That is where we always get burned.
We tried something similar in 2023 and rolled it back. Curious whether the tooling has caught up since.
Solid write-up. The warning about caching bit us hard last year.
How does this hold up on older Safari versions? That is where we always get burned.
This finally clicked for me after reading the docs three times.