Adding a column to a big Postgres table without taking our demo down
Our class project needed a new column on a table with a few million rows, the day before a demo. I learned which ALTER TABLE operations are instant, which ones lock the table, and how to backfill in batches.
The day before our software engineering demo, our professor asked if the course-review app could show whether each review was "verified" (written by someone actually enrolled in the class). Easy, I said. It's one column.
The
reviews table had a few million rows by then, because I'd loaded fake data for performance testing and never removed it. I was about to run the migration when a teammate asked, "won't that lock the table?" I did not know. So I went to find out before breaking the thing we were demoing in 20 hours.Adding the column. In PostgreSQL 11 and later,
ALTER TABLE ... ADD COLUMN with a constant default is fast even on big tables. Postgres stores the default in the catalog instead of rewriting every row. So this was effectively instant:```sql
ALTER TABLE reviews ADD COLUMN verified boolean NOT NULL DEFAULT false;
```
It still takes a brief lock, so it's worth running when nothing long is holding the table, but it doesn't rewrite millions of rows. Older Postgres versions would have rewritten the whole table. Something to remember if you're ever stuck on an old version.
Backfilling the real values. The default was
false, but some reviews needed to become true based on an enrollments table. My first instinct was one giant UPDATE ... FROM enrollments. That would update millions of rows in one transaction, hold row locks for a long time and create a lot of dead tuples at once. Instead I did it in batches:```sql
UPDATE reviews
SET verified = true
WHERE id IN (
SELECT r.id
FROM reviews r
JOIN enrollments e
ON e.user_id = r.user_id AND e.course_id = r.course_id
WHERE r.verified = false
LIMIT 5000
);
```
A small script ran that in a loop until it updated zero rows, with a short pause between batches. It took a few minutes and the app stayed responsive the whole time.
The part I almost forgot. The new "verified reviews only" filter needed an index, and a regular
CREATE INDEX blocks writes while it builds. CREATE INDEX CONCURRENTLY doesn't, at the cost of taking longer and not being allowed inside a transaction block, which our migration tool wraps everything in by default. I had to mark that one migration as non-transactional.The demo went fine. Nobody in the room knew anything about any of this, which I've decided is the highest compliment a database migration can get.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article