Renaming a column without downtime: expand, migrate, contract

Renaming a column in Postgres is instant. ALTER TABLE customers RENAME COLUMN full_name TO display_name updates the catalog and returns in a millisecond. It is also one of the easiest ways to cause an outage, because for the minutes it takes to roll out new application code, the old pods are still running queries against a column that no longer exists.

The safe version takes three deploys and about a week. That sounds like a lot until you have explained a 12 minute partial outage to your support team. This is the procedure we follow, using a real rename from last month as the example.

The shape of it

The pattern is called expand and contract, and the idea is that the database must work with both the old and the new application code at every step.

1. Expand. Add the new column. Keep both columns in sync with a trigger.
2. Migrate. Backfill existing rows. Deploy code that reads and writes the new column.
3. Contract. Once no running code mentions the old column, drop it.

Every step is backwards compatible with the one before it, so any deploy can be rolled back without touching the database.

Step one: expand

SET lock_timeout = '3s';

ALTER TABLE customers ADD COLUMN display_name text;

CREATE FUNCTION customers_sync_name() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.display_name := COALESCE(NEW.display_name, NEW.full_name);
    NEW.full_name    := COALESCE(NEW.full_name, NEW.display_name);
  ELSIF NEW.full_name IS DISTINCT FROM OLD.full_name THEN
    NEW.display_name := NEW.full_name;
  ELSIF NEW.display_name IS DISTINCT FROM OLD.display_name THEN
    NEW.full_name := NEW.display_name;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER customers_sync_name
  BEFORE INSERT OR UPDATE ON customers
  FOR EACH ROW EXECUTE FUNCTION customers_sync_name();

The trigger handles both directions. Old code updates full_name, and the trigger copies it across. New code updates display_name, and the trigger copies it back. The IS DISTINCT FROM comparisons treat NULLs sensibly, which plain != does not.

Step two: backfill, then switch the code

Existing rows still have NULL in display_name. Updating 9 million rows in one statement would hold row locks for minutes and generate a burst of WAL, so we backfill in batches from a script that loops until a batch updates zero rows:

UPDATE customers
SET display_name = full_name
WHERE id IN (
  SELECT id
  FROM customers
  WHERE display_name IS NULL AND full_name IS NOT NULL
  ORDER BY id
  LIMIT 5000
);

-- step three, a week later, once no deployed code mentions full_name
SET lock_timeout = '3s';
DROP TRIGGER customers_sync_name ON customers;
DROP FUNCTION customers_sync_name();
ALTER TABLE customers DROP COLUMN full_name;

Each batch takes around 40 ms. We sleep 100 ms between them to let replicas keep up, and the whole backfill finished in under three minutes.

Once it is done, we deploy the application change. In our codebase the query layer was the only place that knew the column name:

 export async function findCustomer(db: Db, id: string) {
   const { rows } = await db.query(
-    'SELECT id, email, full_name FROM customers WHERE id = $1',
+    'SELECT id, email, display_name FROM customers WHERE id = $1',
     [id],
   );
-  return rows[0] && { ...rows[0], name: rows[0].full_name };
+  return rows[0] && { ...rows[0], name: rows[0].display_name };
 }

Step three: contract

The final migration, shown at the bottom of the SQL block above, waits until we are sure no running code reads the old column. “Sure” means three checks: grep the codebase, check that every service has been deployed since the switch, and look in pg_stat_statements for any query text mentioning full_name. Background workers and a forgotten cron job are the usual offenders.

Things that make this easier

If the new column needs to be NOT NULL, do not add the constraint directly on a large table, because Postgres scans every row while holding a lock. Add CHECK (display_name IS NOT NULL) NOT VALID, then run VALIDATE CONSTRAINT separately, which only takes a weaker lock.

And write the three migrations as three separate pull requests on the same day. It is much easier to remember step three when the pull request is already open and waiting for a date.