Migrate a Postgres Column with Zero Downtime
Add a new column, backfill it in batches, swap reads and writes in code, then drop the old column in a follow-up deploy without locking the table.
Task
Rename a column in a large Postgres table without taking the application offline or holding a long table lock.
When to use this
The users table has 80 million rows and a column named usr_name that needs to become username. A direct rename locks the table for minutes, which takes the login flow down. The team needs a migration that does not block.
Prerequisites
- Postgres 14 or later with statement-level lock timeouts
- A deploy pipeline that can ship two releases in sequence
- A way to run a batch backfill script with a throttle
Steps
1. Add the new column nullable and create a trigger that keeps it in sync with the old column for new writes.
ALTER TABLE users ADD COLUMN username text;
CREATE FUNCTION sync_username() RETURNS trigger AS $$
BEGIN NEW.username := NEW.usr_name; RETURN NEW; END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_sync_username BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_username();
Expected output: New and updated rows have both columns populated; existing rows still have username null.
2. Backfill the existing rows in small batches with a lock timeout so the script never blocks writers.
SET lock_timeout = "2s";
UPDATE users SET username = usr_name
WHERE id BETWEEN $start AND $end AND username IS NULL;
Expected output: Each batch updates a bounded number of rows and either succeeds or skips when the lock is unavailable, leaving no long lock.
3. Ship a release that reads and writes username, then drop the old column in a follow-up deploy.
ALTER TABLE users DROP COLUMN usr_name;Expected output: The application keeps serving throughout, and the final deploy removes the old column with only a brief access exclusive lock on the table.
Troubleshooting
Problem
The backfill batch hits the lock timeout repeatedly
Solution
Reduce the batch size and add a sleep between batches; a long-running transaction elsewhere is holding the lock, so find it with pg_stat_activity.
Problem
The trigger doubles write latency
Solution
Keep the trigger body trivial and drop it as soon as the backfill and the code switch are complete; it exists only for the transition window.
Limitations
- The migration requires two deploys, so the old and new columns coexist for one release cycle.
- A failure between steps leaves the trigger in place; include a cleanup step in the runbook.
Expand, backfill, contract
A safe column rename is three steps: add the new column, backfill it, then drop the old one. Each step is a separate deploy, so no single change holds a long lock.
ALTER TABLE users ADD COLUMN username text;
CREATE TRIGGER users_sync_username BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_username();Backfill in batches
Update a bounded range of rows at a time with a lock timeout. If a batch cannot acquire the lock, it skips and retries; writers always proceed. Track progress with a where username is null count so you know when the backfill is done.
SET lock_timeout = '2s';
UPDATE users SET username = usr_name
WHERE id BETWEEN $start AND $end AND username IS NULL;Expected outcome
The application serves traffic throughout, no step holds a long table lock, and the final deploy drops the old column in a brief access exclusive lock. The trigger exists only for the transition and is removed in the same deploy that drops the column.