Change a Postgres Table Without Downtime: Expand, Migrate, Contract

Add columns, backfill, NOT NULL and indexes on a live table, tested on PostgreSQL 18 with a real lock queue

Figure 1: Add the new shape next to the old one, move the data, then remove the old shape.

Many teams still change a busy table with one big migration. Add the column, fill it, make it NOT NULL, drop the old one, all in a single deploy.

On a busy table, one of those steps takes a lock at the wrong moment and every request touching the table hangs.

I will be sharing the pattern I’ve been using in multiple projects for this: expand, migrate, contract. I ran every step against a real PostgreSQL 18.6 in Docker with 200,000 rows, and the output in this article is from those runs.

This article expects you to have minimum knowledge of SQL and Node.js, but nothing is really complicated here.

The example

We have users.name and want first_name and last_name instead. The app is running the whole time, and old and new versions of the code run side by side during a deploy.