Right, let’s talk about making your triggers fast, or at least, not catastrophically slow. This isn’t optional. A badly written trigger isn’t just a minor performance hiccup; it’s a landmine waiting for the wrong query to step on it. Your database will feel like it’s running in molasses, and you’ll be left staring at pg_stat_activity wondering which of your life choices led you here. I’ve been there. Let’s not do that.

The core thing you must internalize is that a trigger executes for every row affected by the operation that fires it. Think about that. An UPDATE that touches 10 million rows? Your BEFORE UPDATE trigger function runs 10 million times. The overhead of a single function call is negligible. The overhead of 10 million function calls is your entire weekend. This is the fundamental difference between a row-level trigger (the default) and a statement-level trigger, and your choice here is the single biggest lever you have for performance.

The Row-Level Tax and How to Mitigate It

The row-level tax is the constant, per-row overhead of a trigger function. You can’t make it zero, but you can make it tiny. The first rule is to make your trigger function brutally efficient. Get in, do the absolute minimum required work, and get out.

A classic rookie mistake is to fire off a SELECT query inside a row-level trigger to look something up. This transforms your single UPDATE statement into one UPDATE plus one SELECT per row. The performance nosedive is immediate and horrifying.

Instead, use the NEW and OLD records. They’re free. They’re already in memory. Need to compare a value? Just do IF NEW.column <> OLD.column THEN.... Need to set a value based on another column? NEW.updated_at = NOW(); is all you need.

Here’s a decently optimized trigger function that avoids unnecessary work. Notice the IF check to see if the relevant data has even changed before doing the more expensive operation (like calling gen_random_uuid()).

CREATE OR REPLACE FUNCTION set_updated_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    -- Only bother if the 'important_data' column actually changed
    IF NEW.important_data IS DISTINCT FROM OLD.important_data THEN
        NEW.updated_at = NOW();
        NEW.modification_token = gen_random_uuid();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

The IS DISTINCT FROM operator is your friend here; it handles NULL values correctly where <> does not.

When to Use Statement-Level Triggers

Statement-level triggers are your performance savior for bulk operations. They fire once per statement, no matter how many rows it affects. Their use cases are more niche, but they’re incredibly powerful within those niches.

The classic use case is for auditing or logging where you care about the entire operation, not each individual row. Instead of writing 10 million log entries, you can write one summary. You don’t get direct access to NEW or OLD for each row, but you can use RETURNING clauses or query other auxiliary structures.

Imagine you want to log a count of updated rows for a specific bulk operation:

CREATE TABLE bulk_audit_log (
    operation_id bigserial,
    table_name text,
    rows_affected bigint,
    executed_at timestamptz DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION log_bulk_update()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO bulk_audit_log (table_name, rows_affected)
    VALUES (TG_TABLE_NAME, TG_ARGV[0]::bigint);
    RETURN NULL; -- It's an AFTER trigger, return value is ignored
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_update_statement_audit
    AFTER UPDATE ON my_big_table
    FOR EACH STATEMENT
    EXECUTE FUNCTION log_bulk_update(42); -- Wait, this is a problem...

Hold on. See the problem? I can’t just hardcode 42. I need the actual count. This exposes the main limitation: you often need to pre-calculate the data you need. You might do this by having the application pass a value, or by using a temporary sequence or table within the same transaction. It’s more architectural, but the performance payoff for massive tables is astronomical.

The Hidden Quicksand: Triggers and Locking

This one catches even experienced developers. A BEFORE trigger runs inside the locks acquired by the original statement. This is usually fine. But an AFTER trigger? It runs after the locks are held, and crucially, before the transaction commits. If your AFTER trigger does something slow—like calling an external web service, writing to a slow network filesystem, or even a complex, long-running query—it will hold those exclusive row locks on the modified data for the entire duration. This brings concurrent activity on those rows to a screeching halt, causing blocking and timeouts.

The best practice is simple: never, ever do anything in a trigger that might take a long or non-deterministic amount of time. Triggers must be fast and predictable. If you need to do heavy lifting based on a data change, use a trigger to put a job on a queue (like PG-based pg_q or an external system like RabbitMQ) and let a separate worker process handle it asynchronously, outside the original transaction and its precious locks.

The Conditional Execution Trap

You can add a WHEN condition to your trigger definition to prevent it from firing on irrelevant rows. This seems like a great optimization, and it is, but you have to understand what it’s doing.

CREATE TRIGGER only_on_specific_value
    BEFORE UPDATE ON my_table
    FOR EACH ROW
    WHEN (NEW.is_important = true) -- This condition is checked for every row
    EXECUTE FUNCTION my_trigger_function();

The WHEN clause is not a magic filter that only runs the function on some rows. The trigger is still fired for every row, but the condition is checked first. If the condition evaluates to false, the function call is skipped. This saves the function call overhead, which is great, but it doesn’t save the overhead of the trigger machinery itself checking the condition for every row. For a condition that’s very rarely true, it’s a huge win. For a condition that’s true half the time, you’re still paying a significant tax. Always benchmark.