Learning center

Use a trigger when the old release must keep filling the new column

Writing both columns from the new release covers only the new release's writes. The release it replaces keeps writing the old column alone until its last pod stops, and again after a rollback. A BEFORE trigger that fills the new column from the old one covers both. In our lab it added about 1.2 microseconds per inserted row.

What application dual writes leave out#

Release 2026.11.4 writes status and state. Release 2026.11.3 writes only status. Both ran against the same orders table on PostgreSQL 16.15:

INSERT INTO orders (status, state) VALUES ('paid', 'PAID');   -- 2026.11.4
INSERT INTO orders (status) VALUES ('paid');                  -- 2026.11.3
UPDATE orders SET status = 'shipped' WHERE id = 1;            -- 2026.11.3
idstatusstate
1shippedPAID
2paid

Order 1 shows the worse result. A reader of state sees a shipped order as paid, and a null check does not find it. Every old-release write produces one of these two rows until the old release is gone, and a rollback to 2026.11.3 resumes producing them.

The trigger#

CREATE FUNCTION orders_fill_state() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' AND NEW.state IS NULL
     OR TG_OP = 'UPDATE' AND NEW.status IS DISTINCT FROM OLD.status
                         AND NEW.state IS NOT DISTINCT FROM OLD.state THEN
    NEW.state := upper(NEW.status);
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER orders_fill_state BEFORE INSERT OR UPDATE OF status ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_fill_state();

The conditions decide who wrote the row. An insert with no state came from the old release. An update that changed status but left state alone came from the old release too. An update that set both came from the new release, and the trigger leaves its state untouched, even when that value is one the old column cannot express. We emptied the table and replayed the same writes with the trigger in place, plus one new-release update:

TRUNCATE orders RESTART IDENTITY;
INSERT INTO orders (status, state) VALUES ('paid', 'PAID');                      -- 2026.11.4
INSERT INTO orders (status) VALUES ('paid');                                     -- 2026.11.3
UPDATE orders SET status = 'shipped' WHERE id = 1;                               -- 2026.11.3
UPDATE orders SET status = 'cancelled', state = 'CANCELLED_BY_CUSTOMER' WHERE id = 2;   -- 2026.11.4
idstatusstate
1shippedSHIPPED
2cancelledCANCELLED_BY_CUSTOMER

A trigger that always ran NEW.state := upper(NEW.status) would have turned order 2 into CANCELLED. UPDATE OF status limits the trigger to statements that name status in their SET list, as the CREATE TRIGGER documentation describes, so an update of another column does not call it at all.

What it costs#

We inserted 200,000 rows in one statement, three times with the trigger disabled and three times with it enabled:

TriggerRuns (ms)
Disabled284, 275, 324
Enabled566, 521, 535

That is about 245 ms more per 200,000 rows on our machine, or roughly 1.2 µs per row. A row-at-a-time API pays it once per request. A bulk loader pays it per row and may notice.

Which to choose#

Use the trigger when any release that writes only the old column can still run: during the rollout, while it is the rollback target, and while a job or another service writes the table. Tag PostgreSQL connections with the release that opened them finds those writers. Application dual writes are enough only when every writer already writes both columns, and then the trigger is redundant.

The trigger does not fill rows that existed before it was created; the backfill does. It also does not protect a backfill job from its own stale reads, which compute the backfilled value in SQL, not in the job covers.

The compatibility test#

In staging, run each write path of the previous release and each of the new release, then compare. Pass when state IS DISTINCT FROM upper(status) matches only rows whose state the new release set to a value with no old equivalent. Drop the trigger only after every writer of status alone has moved to state or stopped, jobs and other services included, as decide when to stop writing the old column describes.

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