Learning center

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.

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_atevents written
100,000100,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 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.

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