Learning center

Use a generated column as the first step of a column change

When the new column is a pure function of the old one, a stored generated column fills existing rows and follows the old release's writes, with no backfill job. It rewrites the table once, and the new release cannot write to it. When the new release needs to write the column, DROP EXPRESSION turns it into an ordinary column in about a millisecond. Install the sync trigger in the same transaction, or the old release's next writes leave it stale.

Add it#

The orders table has 1,000,000 rows. We ran this on PostgreSQL 16.15:

SELECT pg_relation_filenode('orders');   -- 16777
ALTER TABLE orders ADD COLUMN state text GENERATED ALWAYS AS (upper(status)) STORED;
-- Time: 911 ms
SELECT pg_relation_filenode('orders');   -- 16786

The file node changed, so PostgreSQL rewrote the table to store the computed values, holding an ACCESS EXCLUSIVE lock for the 911 ms. Every query on orders waited that long. At production size the wait grows with the table, so rehearse it on a copy; why a fast migration blocked every query shows the queue it forms.

From then on, the old release needs no changes:

INSERT INTO orders (status) VALUES ('paid');          -- 2026.11.3
UPDATE orders SET status = 'shipped' WHERE id = 1;     -- 2026.11.3
idstatusstate
1shippedSHIPPED
1000001paidPAID

What it cannot do#

The new release cannot write the column:

ERROR:  cannot insert a non-DEFAULT value into column "state"
ERROR:  column "state" can only be updated to DEFAULT

The expression can use only the same row and immutable functions. A lookup in another table fails with cannot use subquery in column generation expression, and now() fails with generation expression is not immutable. The generated columns chapter lists the restrictions. A conversion that depends on a mapping table, the session's time zone or application code needs a trigger instead.

Hand the column to the new release#

When the release that writes state is ready, convert the column:

ALTER TABLE orders ALTER COLUMN state DROP EXPRESSION;
-- Time: 1.4 ms, file node unchanged

The stored values stay, and the column becomes writable. It also stops following status:

UPDATE orders SET status = 'cancelled' WHERE id = 3;   -- 2026.11.3, after the drop
UPDATE orders SET state = 'SHIPPED' WHERE id = 4;       -- 2026.11.4
idstatusstate
3cancelledPAID
4paidSHIPPED

Order 3 shows the gap: the old release is still running, and its cancellation no longer reaches state. We repeated the step with the sync trigger from use a trigger when the old release must keep filling the new column created in the same transaction:

BEGIN;
ALTER TABLE orders ALTER COLUMN state DROP EXPRESSION;
CREATE TRIGGER orders_fill_state BEFORE INSERT OR UPDATE OF status ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_fill_state();
COMMIT;

Order 3 then read cancelled, CANCELLED, order 4 kept the new release's SHIPPED, and a new old-release insert got PAID.

The rollback limit#

Before DROP EXPRESSION, rolling back means dropping the column. After it, state holds values the new release wrote, and the trigger keeps the old release's writes flowing, so the rollback limits are those of any sync trigger. Rehearse the ADD COLUMN rewrite at production size, and run the old release's write paths after the DROP EXPRESSION migration. Pass when state IS DISTINCT FROM upper(status) matches only rows the new release set deliberately. The other steps are in moving data while two application releases share one database.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading