Why does trigger-based change capture add write cost and risk to the source database?
answer
- it runs inside the user's transaction
- one business write becomes two
- if the capture fails, the write fails
- truncate leaves no trace
- the audit table needs its own operations
basics
~20 sRow triggers fire inside the application's own transaction and write an extra audit row per change, so every write becomes two. Commit latency, lock duration, log volume and failure surface all grow, and the audit table needs its own drain and purge.
solid answer
~60 sTrigger-based capture attaches `AFTER INSERT/UPDATE/DELETE` row triggers to each captured table; the trigger writes the old and new values into a shadow audit table, and a reader drains that table. It is the classic answer when the engine's change log is unreachable, and unlike polling it does capture deletes and every intermediate version. The cost is that the trigger runs *inside the writing transaction*. Every business write becomes at least two writes, the transaction holds its locks for longer, commit latency rises, and log and storage volume grow on the source. Worse, the failure surface is shared: if the trigger raises — audit table full, constraint violation, a column the trigger no longer recognises after a schema change — the user's transaction fails with it. The audit table is then a hot table of its own that must be drained and purged continuously, or it becomes the largest object in the database. And triggers can be bypassed: row triggers do not fire for `TRUNCATE`, and some bulk-load paths skip them, so the capture has silent holes exactly where the biggest changes happen.
code
sql · 10 linesCREATE FUNCTION orders_capture() RETURNS trigger AS $$
BEGIN
INSERT INTO orders_audit(op, old_row, new_row)
VALUES (left(TG_OP,1), to_jsonb(OLD), to_jsonb(NEW));
RETURN NULL;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER orders_capture_trg
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION orders_capture();go deeper
Know the shape: AFTER row triggers write old and new values into a shadow table that a reader drains. Remember it captures deletes, unlike polling the table.
Explain that the trigger executes inside the writing transaction, so writes double and commit latency and lock hold times grow, and that a trigger error aborts the user's transaction.
Bring the operational reality: audit-table growth and bloat, purge policy, TRUNCATE and bulk paths bypassing row triggers, and the schema-change coupling that makes every migration a two-part edit.
Decide when the technique is acceptable at all — which sources cannot expose a change log, what write-path risk the business will accept, and whether negotiating log access is cheaper than owning triggers across many tables.
## The mechanism Before logical decoding became widely available, and still today on engines or hosting arrangements where the change log is off limits, teams capture changes with database triggers. Each captured table gets row-level `AFTER` triggers for insert, update and delete, and each trigger inserts a record describing the change into a shadow table: ```sql CREATE TABLE orders_audit ( audit_id bigserial PRIMARY KEY, op char(1) NOT NULL, changed_at timestamptz NOT NULL DEFAULT now(), old_row jsonb, new_row jsonb ); ``` A reader then polls `orders_audit` by `audit_id` and deletes or marks what it has consumed. Note the shape: the *capture* is trigger-based, but the *delivery* is still a poll — of a table that, unlike the base table, is append-only and monotonic, which is why the poll is safe here. ## What it genuinely gives you It is not a bad technique, and it beats polling the base table on the two things polling cannot do: - **Deletes are captured**, because a delete fires a trigger. - **Every intermediate version is captured**, because every statement fires a trigger. - **Old and new values** are both available, since the trigger sees them. - It works with nothing but ordinary DDL privileges — no replication role, no server configuration, no restart. ## What it costs the source **Write amplification inside the user transaction.** The trigger body executes as part of the transaction that fired it. One `UPDATE` of one row becomes an update plus an insert, plus index maintenance on the audit table, plus the write-ahead logging for both. Storage and log volume for the captured tables roughly double, and often more when the audit row stores whole before/after images. **Longer transactions, more contention.** Everything the trigger does happens while the business transaction still holds its locks. Commit latency rises, lock hold times rise, and on a hot path that increases the deadlock and lock-wait rate for unrelated statements. **Shared failure surface.** This is the risk that distinguishes triggers from every other capture approach. A polling extract that fails does not affect the source; a stalled log reader does not fail user transactions (it retains log, which is a different problem). A trigger that raises an error *aborts the user's transaction*. A full disk on the audit tablespace, a constraint violation, a type mismatch introduced by a schema change, or a bug in the trigger body all become production write outages for the application. **An audit table that must be operated.** It grows at the rate of all captured writes combined. It needs a reader that keeps up, a purge policy, and vacuum or purge attention of its own; in multi-version engines it is a classic bloat generator because it is written and deleted continuously. ## Where it silently misses changes Row triggers are not universal interception: - **`TRUNCATE` does not fire row-level triggers.** A truncate empties the table and the capture stream shows nothing, which is the worst possible silent divergence. - **Some bulk-load and utility paths bypass triggers** depending on engine and options. - **Changes applied by replication on a replica** commonly do not fire triggers there, so you cannot simply move the capture off the primary to save it load. ## Keeping it in sync with schema The trigger encodes the table's shape. Add a column and, unless the trigger serialises the whole row generically (which is why `jsonb`/`hstore` capture is popular), the new column is silently absent from the capture. Every source schema change now has a second edit that a migration author must remember, and forgetting is undetectable until someone downstream notices a missing field. ## When to choose it anyway Choose it when the change log is genuinely unavailable and the pipeline needs deletes or intermediate versions — a hosted engine with no logical replication, an internal policy against replication roles, or a legacy engine. Choose it for a *narrow* set of tables with a modest write rate, budget for the audit-table operations, and put the risk in writing: the trigger is now on the critical path of the application's writes. If the change log can be opened up instead, that is almost always the better trade, because it moves the capture off the transaction path entirely.
- What does trigger-based capture give you that polling a base table does not?Deletes and intermediate versions. A delete fires a trigger, so it appears in the audit table; every individual statement fires one, so no update collapses. It also gives both old and new values, which a state poll never has. Those are exactly the gaps polling cannot close.
- How would you reduce the blast radius if you must run trigger-based capture on a hot table?Keep triggers on the fewest tables possible, keep the trigger body trivial (serialise the row, insert, return), give the audit table its own tablespace and free-space monitoring, drain and purge it continuously, and alert on audit-table growth. Accept that a trigger error still fails user writes and rehearse disabling the trigger.
- Why is polling the audit table acceptable when polling the base table is not?Because the audit table is append-only with a monotonic surrogate key. Rows are never updated or hard-deleted out from under the reader before it consumes them, so absence-versus-no-change ambiguity does not arise and the marker cannot go backwards.
A trigger is a carbon copy the clerk must fill in before the sale can complete; the replication log is the till roll that prints anyway.
saying these in an interview costs you the question
- Saying triggers are free because they are just a small insert
- Assuming a trigger fires on TRUNCATE
- Believing a failed capture cannot break the application's writes
- Forgetting the audit table needs draining and purging
- Not updating triggers when the source table gains a column