Postgres Statement-Level vs Row-Level Triggers: Two Different Tools for Two Different Jobs

Postgres triggers come in two granularities: FOR EACH ROW and FOR EACH STATEMENT. The two run at different times relative to the statement, see different things in their special variables, and carry costs that diverge by orders of magnitude depending on how much data the statement touches. The choice is consequential and is not symmetric—picking the wrong one in either direction tends to be expensive.

Row-level triggers are the default mental model when people learn about triggers. They run once per affected row and have access to the OLD and NEW pseudo-records representing the row before and after the change. Statement-level triggers run once per statement regardless of row count and instead have access to the transition tables introduced in Postgres 10, which give the trigger function a result set containing all affected rows. The difference is more than a matter of how often the trigger fires; the two variants are designed for fundamentally different jobs.

What row-level triggers are for

Row-level triggers are the right answer when the trigger's logic genuinely needs to act on each row individually—when the side effect or constraint check is per-row in nature and there is no benefit to seeing the rows in batch. Audit logging that captures before-and-after snapshots is the canonical case: each row needs its own audit entry, and there is no efficiency gain from processing rows together.

The cost of row-level triggers is that the trigger function executes once per row. For a single-row INSERT or UPDATE this is invisible. For a bulk operation affecting a million rows, a trigger that does anything non-trivial multiplies its cost by a million. The pattern that bites teams is bulk operations against tables with row-level triggers that were sized for OLTP workloads; the bulk operation runs orders of magnitude slower than the equivalent operation on an untriggered table, often without the team realizing why.

What statement-level triggers are for

Statement-level triggers run once per statement regardless of how many rows the statement affected. The right job for a statement-level trigger is operations that are inherently batch-friendly—anything that processes the set of affected rows together more efficiently than processing them one at a time.

The transition tables make this possible. A statement-level trigger can declare REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows in its definition, and the trigger function then sees these as readable result sets containing all rows affected by the statement. A bulk-update audit that wants to capture the operation as a single event with a count and a small sample of changed rows is dramatically cheaper as a statement-level trigger than as a row-level trigger that fires a million times.

Statement-level triggers are also the right answer for constraints that span multiple rows of the same operation. If a constraint says "no INSERT can leave the table with more than N rows of a particular type," that constraint cannot be expressed as a row-level CHECK constraint, and a row-level trigger would have to run a count query for every inserted row. A statement-level trigger that runs one count query after the entire INSERT completes is both correct and efficient.

The combination pattern

It is sometimes useful to combine both granularities on the same table. A BEFORE row-level trigger for per-row validation paired with an AFTER statement-level trigger for batch logging is a clean separation of concerns: the per-row work happens where it must, and the batch work happens once per statement. The two triggers do different jobs and stay out of each other's way.

The opposite combination—two triggers doing similar work at different granularities—is almost always a sign of confusion and tends to produce double counting or inconsistent state. The granularity is not a tuning knob; it is a structural choice about what the trigger is for.

The OLD/NEW gotcha

Statement-level triggers do not have access to OLD and NEW as scalar pseudo-records the way row-level triggers do. This sometimes surprises developers who try to port a row-level trigger to statement-level for performance reasons. The transition tables give access to the same data, but in result-set form, which means the trigger function must use SQL or PL/pgSQL loops to iterate—and if the natural expression is per-row iteration, the gain over the row-level trigger may be small.

The transition tables are also empty for some operations. DELETE has only old_rows. INSERT has only new_rows. UPDATE has both. The trigger function must handle the appropriate combination, and a single trigger fired for INSERT OR UPDATE OR DELETE has to inspect TG_OP to know which tables are populated.

Timing and ordering

Both granularities support BEFORE, AFTER, and INSTEAD OF timing. BEFORE row-level triggers can modify the row by assigning to NEW; AFTER triggers cannot. Statement-level triggers run before or after the entire statement and cannot modify individual rows. INSTEAD OF triggers are valid only on views and are most commonly row-level.

Multiple triggers on the same event fire in alphabetical order by trigger name. This is one of the few ordering decisions in Postgres that depends on user-controlled metadata rather than on the order of definition, and it bites teams who define triggers in a particular order expecting them to fire in that order. The fix is naming discipline that encodes order in the prefix, or—better—keeping the number of triggers per table small enough that ordering does not matter.

What triggers do not solve

Both granularities of trigger run inside the same transaction as the statement that fired them. A trigger that raises an exception aborts the entire statement and any uncommitted work. A trigger that calls out to an external service blocks the transaction on the external service's response time. A trigger that does substantial work on every write makes every write substantially more expensive.

The transaction-scope property is what makes triggers correct for constraint enforcement and audit logging. It is also what makes them wrong for anything that should be asynchronous. The right pattern for asynchronous work is the transactional outbox: the trigger writes to an outbox table inside the transaction, and a separate worker reads from the outbox and performs the asynchronous work. The trigger remains fast and correct; the slow or external work happens outside the critical path.

Our use across the four products

Our SQLite-based products use the SQLite trigger mechanism, which supports row-level triggers but not the FOR EACH STATEMENT form with transition tables. The asymmetry is not as costly as it sounds because the SQLite workloads we run are OLTP-shaped: most statements touch one row or a small number of rows, and the row-level granularity is appropriate. The Postgres migration plan would gain the FOR EACH STATEMENT capability, which would be useful primarily for batch operations that we currently keep simple to avoid the row-level trigger cost.

The deeper observation about triggers is that the granularity question is one of the cleaner cases in database operations where reading the manual carefully and thinking about what the trigger is for produces a substantially better outcome than reaching for the default. Triggers in general have a reputation for being problematic in production systems, and a significant fraction of that reputation comes from row-level triggers being used for batch-friendly work where statement-level triggers would have been an order of magnitude faster. The mechanism is fine; the granularity choice is what makes triggers either an asset or a liability.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.