---
title: "Use a trigger when the old release must keep filling the new column | Proofline"
description: "Writing both columns from the new release covers only the new release's writes."
url: https://proofline.sh/learn/postgres-sync-trigger-old-writer/
---

[Learning center](/learn/)

# 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.

Schema conflicts The Proofline team November 22, 2026 3 min read

## 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
```

| id | status | state |
| --- | --- | --- |
| 1 | shipped | PAID |
| 2 | paid |  |

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
```

| id | status | state |
| --- | --- | --- |
| 1 | shipped | SHIPPED |
| 2 | cancelled | CANCELLED\_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](https://www.postgresql.org/docs/16/sql-createtrigger.html) 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:

| Trigger | Runs (ms) |
| --- | --- |
| Disabled | 284, 275, 324 |
| Enabled | 566, 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](/learn/postgres-which-release-is-connected/) 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](/learn/postgres-backfill-read-modify-write-race/) 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](/learn/when-to-stop-dual-writing/) 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](/free-code-review/).

[Start free with GitHub](https://proofline.sh/orgs/plan?offer=free_month)

## Related reading

-   ### [Why a data step in a schema migration misses rows](/learn/data-migration-in-schema-migration-file/)
    
    Breaking migrations The Proofline team November 22, 2026 3 min read
    
-   ### [Decide when to stop writing the old column](/learn/when-to-stop-dual-writing/)
    
    Schema conflicts The Proofline team November 22, 2026 3 min read
