---
title: "Keep a backfill from firing the table's update triggers | Proofline"
description: "A backfill is an UPDATE like any other, so every trigger on the table fires for every row it changes."
url: https://proofline.sh/learn/postgres-backfill-side-effect-triggers/
---

[Learning center](/learn/)

# Keep a backfill from firing the table's update triggers

A backfill is an UPDATE like any other, so every trigger on the table fires for every row it changes. On our 100,000-row orders table, a backfill of the new state column set a fresh updated\_at on every row and wrote 100,000 order.updated events for downstream consumers. Neither reflected a change any customer made. A WHEN clause on the event trigger and a transaction-scoped setting for the timestamp trigger kept both quiet.

Breaking migrations The Proofline team November 22, 2026 3 min read

## The lab

The table has two triggers that many applications carry: one sets `updated_at`, and one records an event for a change feed or outbox. We ran this on PostgreSQL 16.15.

```
CREATE FUNCTION touch_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at := now(); RETURN NEW; END $$;
CREATE TRIGGER orders_touch BEFORE UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

CREATE FUNCTION emit_order_changed() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN INSERT INTO order_events (order_id, kind) VALUES (NEW.id, 'order.updated'); RETURN NULL; END $$;
CREATE TRIGGER orders_changed AFTER UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION emit_order_changed();

UPDATE orders SET state = upper(status);   -- the backfill, 958 ms
```

| rows with a new `updated_at` | events written |
| --- | --- |
| 100,000 | 100,000 |

Each consequence lands somewhere different. A search index or warehouse that syncs by `updated_at` would reload the whole table. A consumer of `order.updated` might email customers, recompute invoices or call a partner. And the original `updated_at` values, which a report might use to find stale orders, are gone; restoring them needs a backup.

## Fire on the columns that mean a change

The event trigger only needs to fire when a column the consumers care about changes. A `WHEN` condition on the trigger skips it for everything else, and PostgreSQL does not even call the function. `CREATE OR REPLACE TRIGGER`, available since PostgreSQL 14, swaps the definition in place:

```
CREATE OR REPLACE TRIGGER orders_changed AFTER UPDATE ON orders
  FOR EACH ROW WHEN (OLD.status IS DISTINCT FROM NEW.status)
  EXECUTE FUNCTION emit_order_changed();
```

The timestamp trigger is harder, because the new release legitimately changes `state` and expects `updated_at` to move. Let the backfill say who it is with a setting that lasts only for its transaction:

```
CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF current_setting('app.backfill', true) = 'on' THEN RETURN NEW; END IF;
  NEW.updated_at := now();
  RETURN NEW;
END $$;

BEGIN;
SET LOCAL app.backfill = 'on';
UPDATE orders SET state = upper(status) WHERE id BETWEEN $1 AND $2;
COMMIT;
```

The backfill ran again with 0 rows touched and 0 events. An ordinary update afterwards, `SET status = 'shipped', state = 'SHIPPED' WHERE id = 8`, moved `updated_at` and wrote one event. `SET LOCAL` ends with the transaction, as the [SET documentation](https://www.postgresql.org/docs/16/sql-set.html) describes, so a pooled connection does not carry the flag into the next request. After the transaction, `current_setting('app.backfill', true)` returned an empty string, which the trigger treats as off.

## Other ways to skip triggers

`ALTER TABLE ... DISABLE TRIGGER` stops the trigger for every session, including the running release's real changes, and takes a lock on the table. `SET session_replication_role = replica` skips ordinary triggers only in the backfill's session, but it also skips the internal triggers that enforce foreign keys, so use it only where you have checked those separately.

## The rehearsal

Before the backfill, list the triggers on the table with `\d orders` or `pg_trigger`, and run one batch in staging. Pass when the event table, the outbox and the `updated_at` distribution are unchanged, and when an ordinary update still fires each trigger once. More backfill labs are in [moving data while two application releases share one database](/learn/data-migration-across-service-versions/).

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

-   ### [Use a trigger when the old release must keep filling the new column](/learn/postgres-sync-trigger-old-writer/)
    
    Schema conflicts The Proofline team November 22, 2026 3 min read
    
-   ### [Moving data while two application releases share one database](/learn/data-migration-across-service-versions/)
    
    Breaking migrations The Proofline team November 22, 2026 7 min read
