Expand first and contract later

How to do it

To rename a column, add the new one and write to both, from the app or a trigger. Copy old rows across in batches, move reads to the new column, and drop the old one later. Add columns as nullable or with a constant default, and add NOT NULL once every row is filled. Never drop or rename in the same release that stops using a column.

Example

-- Release 1: expand
ALTER TABLE customers ADD COLUMN full_name text;
UPDATE customers SET full_name = name WHERE full_name IS NULL AND id BETWEEN $1 AND $2;
-- Release 2: code reads and writes full_name only
-- Release 3: contract
ALTER TABLE customers DROP COLUMN name;

How to check

Deploy the migration, then run the previous release's tests against the new schema. They pass.