Finding Triggers That May Be Doing Hidden Work

Triggers can be easy to forget about.

A query may look simple from the application side, but behind the scenes a trigger might be firing additional SQL, reading other tables, evaluating JSONB expressions, or performing extra writes.

In YugabyteDB, that hidden work can matter.

Because YugabyteDB is distributed, trigger logic that scans tables, evaluates row-by-row filters, or performs additional writes may add CPU, RPCs, storage reads, and latency. This is especially important when the trigger function is missing supporting indexes or filtering on JSONB expressions.

Why Hidden Trigger Work Can Hurt Performance

If a trigger runs a query that is missing the right index, YugabyteDB may need to perform a full table scan and then apply filters row-by-row. That can consume a lot of CPU, especially when the table is large but the query only returns a small number of rows.

The cost can be even higher when the trigger filters on JSONB expressions, for example:

				
					WHERE payload->>'some_key' = 'some_value'

In that case, each scanned row may require YugabyteDB to extract values from the JSONB document, evaluate the expression, and then discard rows that do not match.

So the database is not just scanning rows. It is also doing JSONB expression work for each row it inspects.

Tip: When investigating unexpected CPU usage or write latency, do not only look at the SQL statement sent by the application. Also check whether triggers are firing and what SQL those trigger functions execute.

Show All User-Created Triggers

You can query the PostgreSQL/YSQL catalog to list user-created triggers:

				
					SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    t.tgname AS trigger_name,
    CASE t.tgenabled
        WHEN 'O' THEN 'enabled'
        WHEN 'D' THEN 'disabled'
        WHEN 'R' THEN 'replica'
        WHEN 'A' THEN 'always'
    END AS trigger_status,
    pg_get_triggerdef(t.oid, true) AS trigger_definition
FROM pg_trigger t
JOIN pg_class c
    ON c.oid = t.tgrelid
JOIN pg_namespace n
    ON n.oid = c.relnamespace
WHERE NOT t.tgisinternal
ORDER BY
    n.nspname,
    c.relname,
    t.tgname;

This excludes internal system triggers, such as those used for foreign key enforcement, and focuses on triggers created by the application or schema owner.

Include the Trigger Function

If you also want to see the trigger function name, use this version:

				
					SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    t.tgname AS trigger_name,
    p.proname AS function_name,
    CASE t.tgenabled
        WHEN 'O' THEN 'enabled'
        WHEN 'D' THEN 'disabled'
        WHEN 'R' THEN 'replica'
        WHEN 'A' THEN 'always'
    END AS trigger_status,
    pg_get_triggerdef(t.oid, true) AS trigger_definition
FROM pg_trigger t
JOIN pg_class c
    ON c.oid = t.tgrelid
JOIN pg_namespace n
    ON n.oid = c.relnamespace
JOIN pg_proc p
    ON p.oid = t.tgfoid
WHERE NOT t.tgisinternal
ORDER BY
    n.nspname,
    c.relname,
    t.tgname;

Show the Trigger Function Definition

Once you find a trigger function, you can inspect the actual function body with pg_get_functiondef.

For example:

				
					SELECT pg_get_functiondef('update_entity_is_latest'::regproc);

If there are multiple functions with the same name in different schemas, schema-qualify the function name:

				
					SELECT pg_get_functiondef('public.update_entity_is_latest'::regproc);

You can also view function details from ysqlsh using the \df+ meta-command:

				
					\df+ update_entity_is_latest

Or schema-qualify it:

				
					\df+ public.update_entity_is_latest

This is where the hidden work often becomes visible. You can review the function body and look for SQL that scans tables, filters on JSONB expressions, sorts rows, or updates additional rows behind the original application statement.

Example: A Trigger That Maintains a “Latest” Flag

Here is a simplified example of trigger logic that maintains an is_latest flag for rows that share the same entity identifier:

				
					CREATE OR REPLACE FUNCTION public.update_entity_is_latest()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE entity_table AS e
    SET is_latest = (e.id = latest.id),
        updated_at = NOW()
    FROM (
        SELECT id
        FROM entity_table
        WHERE entity_id = NEW.entity_id
        ORDER BY
            (payload->'version'->>'major')::int DESC,
            (payload->'version'->>'minor')::int DESC,
            (payload->'version'->>'patch')::int DESC,
            created_at DESC,
            id DESC
        LIMIT 1
    ) AS latest
    WHERE e.entity_id = NEW.entity_id
      AND e.is_latest IS DISTINCT FROM (e.id = latest.id);

    RETURN NULL;
END;
$function$;

At first glance, this looks reasonable. The trigger finds the newest version for an entity and updates the is_latest flag.

But there are a few important things happening here:

● The trigger runs additional SQL behind the original application write.
● The subquery filters by entity_id.
● The subquery sorts by version values extracted from a JSONB document.
● The outer UPDATE may update one or more rows with the same entity_id.

In YugabyteDB, that can add CPU, storage reads, RPCs, and distributed transaction overhead. If the supporting index is missing, the database may need to scan many rows, extract JSONB values row-by-row, sort the candidates, and then perform the update.

Important: The expensive part may not be the application statement itself. The expensive work may be inside the trigger function that fires because of that statement.

What To Look For

After you find the triggers, inspect the trigger functions and look for SQL that may be expensive in a distributed database.

Pattern	Why It Matters
`Seq Scan` inside trigger queries	May scan many rows and filter them one-by-one.
Missing indexes on trigger lookup columns	Can turn each row modification into additional expensive reads.
JSONB filters such as `payload->>'some_key'`	Requires JSONB value extraction and expression evaluation per scanned row.
JSONB expressions in `ORDER BY`	May require per-row expression evaluation and sorting if the right index is missing.
Triggers that update other rows	May add hidden distributed read/write work behind a simple application statement.
Queries touching many tablets	May add RPC overhead, storage reads, and latency across nodes.

Use EXPLAIN To Confirm

Once you identify the SQL being executed inside the trigger function, test the expensive parts directly with:

				
					EXPLAIN (ANALYZE, DIST)
SELECT ...

For trigger functions that run UPDATE statements, you can also test the update pattern in a lower environment.

Look for signs such as:

● Seq Scan
● Rows Removed by Filter
● Sort
● high storage read requests
● many RPCs
● large difference between rows scanned and rows returned

These are good indicators that the trigger query may need a better index, a query rewrite, or a schema adjustment.

How to Reduce the Hidden Trigger Cost

Option A: Add the Right Indexes and Reduce Per-Row Expression Work

One practical improvement is to make the trigger query cheaper by giving YugabyteDB an index that matches how the trigger looks up and orders rows.

In this example, the trigger needs to:

● Find rows for a specific entity_id.
● Determine the latest row by sorting on version values, created_at, and id.
● Update rows where the is_latest flag needs to change.

If the right index is missing, YugabyteDB may need to scan many rows for the entity, evaluate expressions, sort candidates, and then perform the update.

The JSONB expressions make this more expensive because the version values are extracted from the JSONB document at runtime:

				
					ORDER BY
    (payload->'version'->>'major')::int DESC,
    (payload->'version'->>'minor')::int DESC,
    (payload->'version'->>'patch')::int DESC

you can expose those values as stored generated columns:

				
					ALTER TABLE entity_table
ADD COLUMN version_major int
GENERATED ALWAYS AS ((payload->'version'->>'major')::int) STORED,
ADD COLUMN version_minor int
GENERATED ALWAYS AS ((payload->'version'->>'minor')::int) STORED,
ADD COLUMN version_patch int
GENERATED ALWAYS AS ((payload->'version'->>'patch')::int) STORED;

Then create an index that matches the trigger’s lookup and ordering pattern:

				
					CREATE INDEX idx_entity_latest_lookup
ON entity_table (
    entity_id HASH,
    version_major DESC,
    version_minor DESC,
    version_patch DESC,
    created_at DESC,
    id DESC
);

With this pattern, YugabyteDB has a much better chance of satisfying the lookup and ordering from the index, instead of repeatedly extracting JSONB values and sorting rows at runtime.

The trigger query can then order by normal columns:

				
					SELECT id
FROM entity_table
WHERE entity_id = NEW.entity_id
ORDER BY
    version_major DESC,
    version_minor DESC,
    version_patch DESC,
    created_at DESC,
    id DESC
LIMIT 1;

This does not remove the trigger overhead entirely, but it can reduce the computational cost of the subquery.

For simpler JSONB filters, an expression index can also help:

				
					CREATE INDEX idx_entity_payload_some_key
ON entity_table ((payload->>'some_key'));

For numeric comparisons, the expression and the query need to match:

				
					CREATE INDEX idx_entity_payload_priority
ON entity_table (((payload->>'priority')::int));

Indexing tip: For this type of pattern, try to align the index with both the filter and the ordering. In this example, the query filters by entity_id and then orders by version columns, created_at, and id. For frequently used JSONB values, stored generated columns can make the expression reusable and easier to index.

Option B: Move the Logic to the Application Write Path

For production workloads, it is also worth asking whether this logic should live in a trigger at all.

Triggers that update other rows can be convenient, but in a distributed SQL database they may hide extra reads, writes, RPCs, and transaction work from the application.

A more explicit pattern is to handle the “latest row” maintenance in the application’s write path:

				
					BEGIN;

UPDATE entity_table
SET is_latest = false
WHERE entity_id = $1
  AND is_latest = true;

INSERT INTO entity_table (..., entity_id, payload, is_latest, ...)
VALUES (..., $1, $2, true, ...);

COMMIT;

This makes the work visible and intentional. The application controls the transaction, the SQL is easier to test with EXPLAIN (ANALYZE, DIST), and the indexing strategy is clearer.

Production note: Triggers are not automatically bad, but triggers that query and update other rows should be reviewed carefully in YugabyteDB. They can hide distributed reads and writes behind what appears to be a simple application statement.

Final Takeaway

Triggers can hide a lot of database work.

If YugabyteDB is showing unexpected CPU usage, storage reads, or write latency, check whether triggers are involved.

A missing index inside trigger logic can cause repeated full table scans. JSONB filters and JSONB expressions in ORDER BY clauses can add even more per-row CPU overhead. Triggers that update other rows can also add hidden distributed transaction work.

Use the catalog queries above to find the triggers, inspect the trigger functions, and validate the expensive SQL with EXPLAIN (ANALYZE, DIST).

For common trigger access patterns, consider adding the right indexes, using stored generated columns for frequently accessed JSONB values, or moving the logic into the application write path where the work is explicit and easier to tune.

Have Fun!