Dual-Writing with Database Triggers

A schema migration needs the new customer_addresses table to stay in sync with the address columns on customers while code moves over, and the writers include three services, a nightly import and the support team’s admin tool. Changing all of them to write twice is slow and error-prone; setting up change data capture for two tables in the same database feels like heavy machinery. A trigger sits in the middle: it runs inside the same transaction as every write to the source table, from every writer, and applies the corresponding change to the target. Used carefully, triggers are the simplest correct way to dual-write within one database. Used carelessly, they add latency to every write, create infinite loops when sync runs in both directions, and turn a bug in the sync into failed user transactions. This guide covers the careful version. It belongs to Dual-Write Synchronization.

Symptom / Error Signatures

You are in trigger territory when application dual-writes are missing writers — reconciliation finds rows changed by an import job or a manual UPDATE that never reached the target. Trigger problems have their own signatures:

ERROR:  stack depth limit exceeded
CONTEXT:  PL/pgSQL function customers_sync() ... PL/pgSQL function customer_addresses_sync() ...

That is two sync triggers calling each other forever. Others: user writes failing with an error raised inside the trigger (null value in column ... of relation "customer_addresses"), write latency rising on the source table after the trigger is added, and deadlocks (40P01) when two transactions update source rows in different orders and their triggers lock target rows in the opposite order.

Root Cause Analysis

A row-level trigger fires for each affected row, inside the statement’s transaction. That gives it two powerful properties — it sees every write from every client, and its effects commit or roll back atomically with the write — and three costs. Every write now does extra work, so latency rises by the cost of the target write. Any error in the trigger fails the user’s statement. And a trigger’s write is itself a write, which fires the target’s triggers — so bidirectional sync loops unless guarded.

In MySQL, triggers are also row-level and transactional with InnoDB, but a table may have limited triggers per event and timing in older versions, and triggers cannot be combined with some online schema change tools — gh-ost does not support tables with triggers, while pt-online-schema-change supports them in recent versions with specific options. Check before choosing triggers on MySQL tables you may later alter online.

Immediate Mitigation

1. If sync triggers are looping, break the loop now. Disable the reverse trigger (or both) — ALTER TABLE ... DISABLE TRIGGER takes a lock like other DDL, so use a timeout.

-- PostgreSQL · migration role · stops the reverse direction immediately
SET lock_timeout = '3s';
ALTER TABLE customer_addresses DISABLE TRIGGER customer_addresses_sync;
-- ROLLBACK PATH: ALTER TABLE customer_addresses ENABLE TRIGGER customer_addresses_sync;

2. Add a depth guard to every sync function. pg_trigger_depth() returns 1 for a trigger fired directly by a client statement and more for triggers fired by other triggers.

-- PostgreSQL · migration role · forward sync with a loop guard and an indexed upsert
CREATE OR REPLACE FUNCTION customers_address_sync() RETURNS trigger AS $$
BEGIN
  IF pg_trigger_depth() > 1 THEN
    RETURN NEW;                       -- change came from the reverse sync; do not echo it back
  END IF;
  INSERT INTO customer_addresses (customer_id, street, city, postcode, country)
  VALUES (NEW.id, NEW.street, NEW.city, NEW.postcode, NEW.country)
  ON CONFLICT (customer_id) DO UPDATE
    SET street = EXCLUDED.street, city = EXCLUDED.city,
        postcode = EXCLUDED.postcode, country = EXCLUDED.country;
  RETURN NEW;
END $$ LANGUAGE plpgsql;
SET lock_timeout = '3s';
CREATE OR REPLACE TRIGGER customers_address_sync
  AFTER INSERT OR UPDATE OF street, city, postcode, country ON customers
  FOR EACH ROW EXECUTE FUNCTION customers_address_sync();
-- ROLLBACK PATH: DROP TRIGGER customers_address_sync ON customers; DROP FUNCTION customers_address_sync();

CREATE OR REPLACE TRIGGER requires PostgreSQL 14; on older versions drop and create in one transaction.

3. Make sure the target lookup is indexed. The upsert relies on a unique index or primary key on customer_addresses.customer_id; without it, ON CONFLICT cannot work and any lookup would scan.

Permanent Fix / Long-Term Pattern

Use triggers as a temporary bridge with a defined lifecycle: create the forward trigger before the backfill, backfill existing rows, reconcile, switch reads, then either flip direction (drop the forward trigger, create a reverse one) when writes move to the new table, or drop the trigger entirely at the end of the contract phase. Keep sync functions small, idempotent (upserts, not inserts), guarded against loops, and covered by tests that write through every path.

Handle deletes explicitly — an AFTER DELETE trigger or ON DELETE CASCADE on the target — and decide what happens to rows whose sync fails: during migration, failing the user’s write is usually the right choice because it surfaces bugs immediately; logging and continuing risks silent divergence and must be paired with reconciliation, as in reconciling divergence between dual-written tables. For synchronisation across databases, triggers are the wrong tool; use log-based replication, described in using change data capture instead of application dual-writes. A full worked example of the trigger lifecycle appears in splitting a wide table into two.

Verification Checklist

• Every sync function has a loop guard and uses idempotent upserts.
• Triggers fire only on the relevant columns (UPDATE OF ...) and on inserts and deletes as needed.
• The target has a unique index supporting the upsert, and write latency on the source stayed within budget.
• Reconciliation between the tables shows zero differences while the trigger is active.
• The plan names the release in which each trigger is created and dropped.

Frequently Asked Questions

Do triggers slow down writes? Yes, by the cost of the extra work they do. An indexed upsert into another table typically adds a fraction of a millisecond per row; bulk updates of many rows pay that per row, so large batch jobs slow down proportionally.

How do I stop two sync triggers from calling each other forever? Guard each function with IF pg_trigger_depth() > 1 THEN RETURN NEW; END IF; so that a change made by one trigger does not fire the other’s sync. Alternatively, set a session variable in one direction and check it in the other.

Should a failing sync fail the user’s transaction? During a migration, usually yes: it surfaces bugs immediately and keeps both tables consistent. Swallowing errors requires a separate reconciliation process to find and repair divergence.

Can I use triggers on MySQL tables I will later change with gh-ost? gh-ost does not support tables with triggers. If you expect to alter the table online during the migration, prefer pt-online-schema-change (which supports existing triggers in recent versions) or application-level or CDC-based sync.