A backfill that should have taken two minutes was still running after an hour. The UPDATE was simple, the table had good indexes, and there was no lock contention. The query just crawled, row by row.
The table had four triggers. An audit trigger, a denormalization trigger that updated a summary table, a search-vector trigger, and a notify trigger. None of them mattered for a single-row UI edit. All of them fired a million times during the backfill, and one of them did a slow lookup each time.
Triggers are the easiest way to add behavior and the easiest way to add invisible cost. They run inside your transaction, on your write path, and they do not show up in the query you wrote.
Per-row triggers run once per row, every time
A FOR EACH ROW trigger fires for every affected row. That is fine when writes are one row at a time. It is brutal during bulk operations, because a single statement that touches a million rows fires the trigger function a million times, each invocation carrying its own overhead.
If the trigger body does anything non-trivial, a secondary query, a function call, an external-looking lookup, you pay that cost per row. The statement you wrote looks cheap; the work it triggers does not.
Find out what is actually attached
Before optimizing, know what fires. It is common to inherit a table with triggers nobody remembers adding. List them, read their function bodies, and decide which ones must run on a bulk path at all.
-- Every trigger on a table, with its timing and function
SELECT tgname,
CASE WHEN tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END AS level,
CASE WHEN tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END AS timing,
pg_get_triggerdef(oid) AS definition
FROM pg_trigger
WHERE tgrelid = 'orders'::regclass
AND NOT tgisinternal;Prefer statement-level triggers with transition tables
Since PostgreSQL 10, statement-level triggers can access transition tables, the full set of changed rows as OLD TABLE and NEW TABLE. This lets you do set-based work once per statement instead of per row. A denormalization or audit task that was a per-row loop becomes a single INSERT ... SELECT against the transition table.
This is frequently a 10x to 100x improvement for bulk writes, because you replace a million function invocations with one statement that processes a million rows in a set operation.
-- One invocation per statement, set-based work over all changed rows
CREATE OR REPLACE FUNCTION audit_orders() RETURNS trigger AS $$
BEGIN
INSERT INTO orders_audit (order_id, new_status, changed_at)
SELECT id, status, now() FROM new_rows;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER orders_audit_stmt
AFTER UPDATE ON orders
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT EXECUTE FUNCTION audit_orders();Watch for trigger cascades and serialization
Triggers that write to other tables can fire those tables' triggers, which fire more. A single insert can fan out into a tree of work you never see in the original statement. Map the cascade before you trust the cost.
Worse, a denormalization trigger that updates a shared summary row turns concurrent inserts into a queue: every transaction wants to update the same counter row, so they serialize on that row lock. Your write throughput is now bounded by one hot row, not by the table.
Disable triggers for trusted bulk operations
For a controlled backfill or migration where you can reason about correctness, temporarily disabling triggers and doing the equivalent work in bulk afterward is legitimate and fast. Do it inside a transaction, scoped to the session, and re-enable explicitly.
Be careful: disabling triggers also disables foreign-key enforcement triggers if you use the blunt form, so prefer disabling specific user triggers by name, and re-validate constraints afterward if there is any doubt.
BEGIN;
ALTER TABLE orders DISABLE TRIGGER orders_search_vector;
-- bulk update here, then do the search-vector work set-based
UPDATE orders SET search_vector = to_tsvector('english', title)
WHERE search_vector IS NULL;
ALTER TABLE orders ENABLE TRIGGER orders_search_vector;
COMMIT;Keep trigger functions honest
The cheapest trigger does the least. Inside a per-row trigger, avoid lookups that could be joins, avoid anything that touches a remote or unindexed table, and exit early when nothing relevant changed. A BEFORE UPDATE trigger that returns immediately when the watched columns are unchanged saves enormous work on wide updates.
- Per-row triggers multiply by row count; assume bulk paths will hit them.
- Move set-based work to statement-level triggers with transition tables.
- Map cascades; a hot summary row silently serializes concurrent writes.
- Disable specific user triggers for trusted backfills, then re-enable.
- Short-circuit when watched columns did not change.
The practical standard
The best PostgreSQL performance work is boring in the right way. Name the failure mode, capture the before plan or metric, make one change, and compare the exact same signal afterward. Anything else is just a more confident guess.