Debugging a Slow Postgres Migration: What the Query Plan Told Us

We had a migration that should have taken four minutes. It took forty.

The first mistake was looking at CPU. The database was idle. The second mistake was looking at locks - plenty of them, but short. What actually mattered was in the query plan: a sequential scan on a table that had grown past the point where our index helped.

What we found

The migration added a column with a default and backfilled it in one statement. On a table with 40M rows that meant a full rewrite while holding a lock.

ALTER TABLE events ADD COLUMN source text DEFAULT 'legacy';

Postgres 11+ makes adding a column with a constant default cheap, but the backfill was ours to write, and we wrote it as a single UPDATE.

What worked

Three changes, in order of impact:

1. Add the column as nullable, with no default.
2. Backfill in batches of 10k with a short sleep between.
3. Set the default afterwards, in a separate transaction.

The migration went from 40 minutes and a lock queue to 6 minutes with no visible stall.

The part I keep coming back to

The query plan gave us the answer in about ten seconds. We spent two hours on dashboards before we looked at it. Observability is not a substitute for reading the plan.