Why Your Database Triggers Fire More Often Than You Think: The Hidden Cost of Row-Level Triggers

Database triggers feel like magic when you add them. They appear to fire "automatically" in response to data changes, with no application code involvement. This is exactly what makes them dangerous to reason about. The trigger runs invisibly, outside the stack trace you are reading, and the only way to discover its cost is to look for it explicitly.

This post is about three things developers routinely get wrong about triggers: when they fire, what order they fire in, and why the cost is invisible in the tools you are already using.

BEFORE vs AFTER

A trigger is either BEFORE or AFTER, relative to the row modification it fires on. The difference is not just timing — it determines what you can do inside the trigger function.

A BEFORE trigger fires before the row is written. This means:

• In a BEFORE trigger function, NEW contains the proposed new row values.
• The trigger function can modify NEW and return the modified row, effectively changing what gets written to the table.
• The trigger function can return NULL to cancel the row modification entirely.

An AFTER trigger fires after the row is written and after the effects of the change are visible within the transaction. This means:

• The change has already happened. You cannot cancel it by returning NULL.
• If you need to propagate the change to another table, use an AFTER trigger.
• Statement-level AFTER triggers fire after all row modifications in the statement are complete.

The practical implication: use BEFORE for data validation and transformation; use AFTER for side effects like maintaining a summary table or logging to an audit trail.

Per-Row vs Per-Statement

This is where most developers get surprised.

A row-level trigger fires once for each row affected by the statement. An UPDATE touching 10,000 rows fires a row-level trigger 10,000 times. This is obvious when stated directly, but it is easy to forget when the trigger does something that looks cheap — like looking up a value, or updating a counter.

-- This fires ONCE PER ROW:
CREATE TRIGGER update_summary_row
AFTER UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_order_summary();

-- This fires ONCE PER STATEMENT:
CREATE TRIGGER update_summary_stmt
AFTER UPDATE ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION update_order_summary_bulk();

If you have a batch import that inserts 50,000 rows and a row-level AFTER INSERT trigger, that trigger function runs 50,000 times. If the trigger function does a SELECT or another INSERT or anything that touches disk, multiply that operation by 50,000. The import that "should take a few seconds" can take twenty minutes.

The fix depends on what the trigger does. If the trigger maintains a derived value (a count, a sum), consider using a statement-level trigger that recalculates the derived value once after all rows are processed. If the trigger inserts audit records, consider batching them with a statement-level trigger or moving to application-side audit logging where you can control the behavior of bulk operations explicitly.

Alphabetical Ordering

When multiple triggers exist on the same table for the same event and timing (for example, two BEFORE INSERT triggers), Postgres fires them in alphabetical order by trigger name. This is documented, but most developers do not know it until they encounter it in a system someone else built.

-- These fire in this order: a_validate, b_transform, c_audit
CREATE TRIGGER c_audit BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION audit_insert();
CREATE TRIGGER a_validate BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION validate_insert();
CREATE TRIGGER b_transform BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION transform_insert();

If your trigger logic depends on another trigger having run first — for example, if c_audit expects that b_transform has already cleaned up the data — you need to name your triggers to enforce the ordering you want. Or you need to put the logic in a single trigger function and call the sub-functions in order. The alphabetical ordering convention is not obvious, not self-documenting in the code, and easy to break when a new trigger is added later.

TRUNCATE Does Not Fire Row Triggers

This surprises people every time: TRUNCATE removes all rows in a table without firing per-row triggers. If you have an AFTER DELETE trigger that maintains a summary table, and someone runs TRUNCATE instead of DELETE FROM, your summary table will be wrong.

TRUNCATE does fire statement-level triggers if you have created a trigger on the TRUNCATE event:

CREATE TRIGGER on_truncate
AFTER TRUNCATE ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION handle_truncate();

But row-level triggers on INSERT, UPDATE, and DELETE are not involved. If your trigger-maintained invariants need to hold through TRUNCATE, you need either a statement-level TRUNCATE trigger or a policy of using DELETE FROM instead of TRUNCATE.

Recursion and Trigger Chains

A trigger can modify data, and that modification can fire another trigger. If that trigger modifies data in a way that fires the original trigger again, you have a trigger recursion loop. Postgres prevents infinite recursion by enforcing a trigger depth limit tied to max_stack_depth, but a trigger loop will fail with an obscure stack depth error rather than looping forever.

Recursion can also happen non-obviously across tables. Trigger A on table X inserts a row into table Y. Trigger B on table Y updates a row in table X. Trigger A fires again. This is a two-table recursion that is harder to spot than a self-referential loop.

The pg_trigger_depth() function returns the current trigger nesting depth. You can use it to guard against re-entry:

IF pg_trigger_depth() > 1 THEN
    RETURN NEW;
END IF;

The EXPLAIN Problem

This is the most practically important thing in this post: trigger cost is not visible in EXPLAIN or EXPLAIN ANALYZE.

When you run EXPLAIN ANALYZE on an UPDATE statement, you see the plan for the UPDATE — the scan, the filter, the estimated and actual rows. You do not see the trigger function executions. Those happen inside the execution engine, outside the query plan. If the UPDATE is slow and you are looking at the EXPLAIN output, you will not find the cause there.

The indirect signal is pg_stat_user_tables:

SELECT
  relname,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_tup_hot_upd
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'order_summary', 'audit_log');

If order_summary or audit_log shows modification counts that correlate with your orders table modifications, and if those counts are large, that is a signal that your triggers are doing real work on those tables. The pg_stat_statements view will capture the individual SQL statements inside the trigger function — look for queries that run very frequently and correlate with your main table operations.

The direct way to discover trigger cost is to temporarily disable triggers and measure the difference. In Postgres you can disable triggers on a specific table (requires superuser or table owner):

-- Temporarily disable all triggers on orders
ALTER TABLE orders DISABLE TRIGGER ALL;
-- ... run your operation and measure ...
ALTER TABLE orders ENABLE TRIGGER ALL;

The performance difference between the disabled and enabled runs is the trigger cost. This measurement is more reliable than any amount of EXPLAIN analysis.

Triggers are not free, and they do not show up where you expect them to. If a bulk operation is slower than it should be and the table has triggers, disable them temporarily and measure. The answer is usually right there.

Shipping something real? builds.anethoth.com is a public build ledger — post your dossier and show what you're working on.