Triggers in MySQL vs PostgreSQL: Same Keyword, Different Contract

The maintenance window was supposed to be ten minutes. We were updating a status flag on about 1.8 million rows of a payments table on MySQL 8.0, the kind of bulk UPDATE we had run a dozen times before. This time it took 41 minutes, held row locks long enough to queue customer-facing writes behind it, and produced a binlog nearly three times larger than the change itself. The culprit was not the statement. It was an audit trigger someone had added six weeks earlier, and nobody had re-measured the bulk path since.

The follow-up surprise came during the postmortem, when a colleague asked why the same trigger, in the PostgreSQL migration prototype, had produced wrong audit rows for exactly the statements where NEW and OLD values mattered most. The answer, as usual, was that CREATE TRIGGER parses almost identically on both engines and behaves differently in every dimension that matters: when it fires, what it can see, how it fails, and what happens on a replica.

This article is the comparison I now hand to anyone porting trigger logic between MySQL and PostgreSQL. I run both in production, I have been burned on both, and the honest summary is this: MySQL gives you one firing mode and a binlog that quietly encodes your trigger's side effects; PostgreSQL gives you four firing modes, real statement-level triggers, and a function call per row. Neither is simpler. They are different machines wearing the same SQL.

When does a trigger actually fire on each engine?

A MySQL trigger fires per row, always. The FOR EACH ROW clause is mandatory syntax, not an option, and there is no statement-level trigger in MySQL at all. If your UPDATE touches 1.8 million rows, the trigger body executes 1.8 million times. PostgreSQL defaults to FOR EACH STATEMENT and offers FOR EACH ROW, plus BEFORE, AFTER, and INSTEAD OF timing (the last for views), which means a single statement can fire zero, one, or millions of trigger invocations depending on how you declared it.

The practical consequence for the payments incident: the audit trigger was a BEFORE UPDATE row trigger that compared OLD and NEW and inserted into an audit table when the status changed. On MySQL, that body ran 1.8 million times even though only about 60,000 rows actually changed status, because MySQL fires the trigger for every row the statement updates, whether or not your logic cares. PostgreSQL has a WHEN clause that filters firing at the engine level, so the guard condition lives in the trigger definition rather than inside the body:

-- PostgreSQL: the WHEN clause keeps the trigger from firing at all
CREATE TRIGGER payments_audit
  BEFORE UPDATE ON payments
  FOR EACH ROW
  WHEN (OLD.status IS DISTINCT FROM NEW.status)
  EXECUTE FUNCTION audit_payment_status();

-- MySQL: no WHEN clause; the check must live inside the body,
-- and the body still runs for every touched row
CREATE TRIGGER payments_audit
  BEFORE UPDATE ON payments
  FOR EACH ROW
BEGIN
  IF NOT (OLD.status <=> NEW.status) THEN
    INSERT INTO payments_audit (payment_id, old_status, new_status, changed_at)
    VALUES (OLD.id, OLD.status, NEW.status, NOW());
  END IF;
END;

Note the MySQL null-safe equality operator <=>, because OLD.status = NEW.status is NULL, not false, when either side is NULL, and NULL in an IF condition takes the ELSE branch. I have seen audit tables silently miss rows for months because of a plain equals in that guard. The WHEN-clause gap is one of the quieter reasons MySQL trigger bodies grow defensive boilerplate, and one of the first things to delete when porting to PostgreSQL.

Statement-level triggers are the bigger structural difference. For a bulk operation, PostgreSQL can fire once per statement and hand you the entire set of changed rows through transition tables, which turns the audit insert into a single INSERT ... SELECT:

-- PostgreSQL 10+: one firing, one set-based audit insert
CREATE TRIGGER payments_audit_stmt
  AFTER UPDATE ON payments
  REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
  FOR EACH STATEMENT
  EXECUTE FUNCTION audit_payment_status_stmt();

-- inside the function:
INSERT INTO payments_audit (payment_id, old_status, new_status, changed_at)
SELECT n.id, o.status, n.status, statement_timestamp()
FROM new_rows n
JOIN old_rows o ON o.id = n.id
WHERE o.status IS DISTINCT FROM n.status;

The 41-minute MySQL window was a per-row problem: 1.8 million trigger invocations, each with its own audit-table insert attempt path, each extending the lock hold time. The PostgreSQL statement-level version of the same audit does the work set-at-a-time. There is no MySQL equivalent; the closest pattern is application-side batching, which is exactly what triggers were supposed to spare you.

What can the trigger body actually do?

MySQL trigger bodies are stored-program blocks with sharp restrictions: they cannot return a result set to the caller, cannot execute COMMIT or ROLLBACK, and the table the trigger is on is off-limits for writes from within its own triggers. Errors are raised with SIGNAL, which is how you implement "reject this write" in a BEFORE trigger:

-- MySQL: rejecting a write from a BEFORE trigger
CREATE TRIGGER payments_no_negative
  BEFORE INSERT ON payments
  FOR EACH ROW
BEGIN
  IF NEW.amount < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'payment amount cannot be negative';
  END IF;
END;

PostgreSQL triggers are calls to a trigger function, which is a full PL/pgSQL (or other language) function that returns the special trigger type. A BEFORE row trigger returns NEW to allow the row, possibly modified, or returns NULL to silently skip the row, which is a power MySQL does not have and a footgun when you forget the return statement and the trigger quietly swallows every write. Raising an error is RAISE EXCEPTION, and anything unhandled aborts the whole statement and transaction. The function-call indirection is also the honest performance line: per-row PostgreSQL triggers pay a function call per row, which is cheap but not free, and on million-row bulk paths it shows up in profiling exactly the way MySQL's per-row body did.

Two more differences that bite during ports. First, ordering: MySQL fires multiple triggers of the same timing and event in creation order and lets you control it explicitly with FOLLOWS and PRECEDES since 5.7; PostgreSQL fires same-timing triggers in alphabetical order by trigger name, which is documented, deterministic, and horrifying the first time you learn it. If order matters, name the triggers so the alphabet agrees with your intentions, or merge the logic into one function. Second, constraint triggers: PostgreSQL can declare a trigger as a constraint trigger and defer it to transaction commit, which enables cross-row checks that only make sense at the end; MySQL has nothing comparable, and the usual workaround is doing the check in application code, with the race conditions that implies.

What happens to triggers on a replica?

This is the section that changes operational behavior, and it is where the two engines diverge furthest. On MySQL with row-based replication, the default for years, the trigger fires on the primary and its effects are binlogged as row changes; the replica receives the already-computed audit rows and applies them without running the trigger. That is why our postmortem found correct audit rows on the replica despite a bug in the trigger body: the replica never ran the buggy code. It also means the binlog carries the full weight of your trigger's side effects, which was the other 2 a.m. discovery in the payments incident: the audit inserts tripled the binlog volume, and the replica's apply lag during the bulk window was a direct consequence.

PostgreSQL standbys replay WAL physically, so triggers never fire on a standby at all; whatever the primary computed is what lands. The data is always identical, but anything you built that assumed a trigger would fire on the read replica, such as a notification trigger or a local rollup, simply never happens there. And because the effects travel in the WAL regardless, the bulk-operation write amplification follows you to the standby's disk either way.

The operational playbook that falls out of all this is short. Keep triggers on hot write paths rare and measurable; the PostgreSQL trigger performance piece covers the measurement side on that engine. Prefer statement-level triggers with transition tables on PostgreSQL bulk paths, and accept per-row triggers on MySQL only when the row volume is small. And never let a trigger's cost be a surprise: the week someone adds one, re-run your bulk maintenance statements against production-scale data, because a trigger turns every O(rows) statement into O(rows times trigger body) and nobody's EXPLAIN warns you.

How MonPG watches trigger-heavy workloads

Trigger problems are invisible at the statement level: the UPDATE looks like an UPDATE in every query log, and the extra forty minutes live inside it. On PostgreSQL, the evidence that separates a slow statement from a slow statement plus trigger work is per-statement timing history, dead-tuple and write amplification on the audit table, and lock waits during bulk windows, and those are exactly the signals MonPG's PostgreSQL monitoring keeps history on, so the week a new trigger triples your update cost shows up as a trend break instead of a mystery.

MonPG monitors PostgreSQL today; MySQL support is on the roadmap, so it will not watch your InnoDB fleet. When MySQL support lands, the counters from this incident are the ones it will surface: binlog growth rate, replica apply lag during bulk windows, and the lock-wait evidence that a per-row trigger is extending lock hold time on a hot table. Until then, the MySQL monitoring page tracks where that work stands, and the discipline holds on both engines: triggers are code that runs inside your slowest statements, so measure them there.