Trigger-based CDC

What do triggers buy in fidelity, and what do they cost the source on every write?

Where no readable log exists, the only remaining place to observe a mutation is inside the transaction that performs it. So you install a trigger: a procedure the database runs automatically on each row change. The trigger writes a record to a shadow table (a table that holds the change history).

-- A trigger that records every change to orders in a shadow table
CREATE TABLE orders_changes (
    change_id  bigserial PRIMARY KEY,
    op         char(1),            -- I, U or D
    order_id   bigint,
    old_row    jsonb,
    new_row    jsonb,
    changed_at timestamptz DEFAULT now()
);

CREATE FUNCTION capture_orders() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO orders_changes(op, order_id, new_row)
    VALUES ('I', NEW.order_id, to_jsonb(NEW));
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO orders_changes(op, order_id, old_row, new_row)
    VALUES ('U', NEW.order_id, to_jsonb(OLD), to_jsonb(NEW));
  ELSE
    INSERT INTO orders_changes(op, order_id, old_row)
    VALUES ('D', OLD.order_id, to_jsonb(OLD));
  END IF;
  RETURN NULL;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER orders_cdc AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION capture_orders();

The benefit: capture is transactionally consistent for free. The change record commits or aborts together with the change, so you can never observe a change that did not happen.

The cost: the write amplification lands on the OLTP write path, synchronously. Typical overhead is 2–5× per statement, and worse for bulk operations, which now run row by row. You have also put a second writer inside every transaction: the shadow table's hot rows, indexes and locks now join the source's contention surface. (A "hot key" is one row that many transactions fight over; the shadow table's newest rows become exactly that.)

The trade is stark: log-based pays in retention coupling, trigger-based pays in source latency. Both pay; neither escapes. This is the conserved-cost rule again: the cost moved, it was not removed.

In one line: triggers buy transactional fidelity with the source's own write latency.