skip to content

Compare three ways of capturing row-level change history: database triggers, writes issued by the application itself, and log-based change data capture. What breaks with each?

level: seniorimportance: should knowfreq 36%

answer

  1. trigger = complete + same transaction, costs writes
  2. trigger lacks app user → session variable
  3. app-level = context, but bypassable
  4. CDC = free on write path, async, outside the DB
  5. CDC slot not consumed → WAL fills the disk

basics

~20 s

Triggers catch every writer and commit atomically with the change, but cost write throughput and lack application context. Application writes carry rich context but miss anyone bypassing the app. Log-based CDC is complete and free on the write path, but asynchronous and lands the history outside the database.

solid answer

~1 min

**Triggers.** A `BEFORE/AFTER INSERT/UPDATE/DELETE` trigger writes the history row inside the same transaction, so history and data commit or roll back together and *every* writer is covered — ad-hoc SQL, migrations, a DBA fixing data. Costs: per-row overhead on every write, invisible logic that surprises people, `TRUNCATE` and some bulk-load paths bypass triggers, and the trigger does not know your application user unless you pass it through a session variable at the start of the transaction. Triggers also fire only where the write happens — on a physical replica they never run. **Application-level writes.** Full context (user, request id, reason), easy to test, no hidden DDL. But any writer bypassing the application — another service, a migration, a manual fix — silently loses history, and multiple write paths drift over time. Writing history to a different store after the commit is a dual write and will lose records. **Log-based CDC** (logical decoding / binlog, e.g. via Debezium). Reads the write-ahead log, so it is complete and imposes almost no cost on transactions. But it is asynchronous, needs infrastructure, delivers to a stream rather than a joinable table, requires attention to schema evolution and to unchanged large column values, and again carries no application identity. Most teams pick triggers for compliance history in-database, CDC for feeding analytics.

code

sql · 15 lines
sql
CREATE FUNCTION audit_customers() RETURNS trigger AS $$
BEGIN
  INSERT INTO customers_history (customer_id, operation, changed_at, changed_by, name, email)
  VALUES (COALESCE(NEW.id, OLD.id),
          LEFT(TG_OP, 1),
          now(),
          NULLIF(current_setting('audit.user_id', true), '')::BIGINT,
          COALESCE(NEW.name, OLD.name),
          COALESCE(NEW.email, OLD.email));
  RETURN NULL;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER customers_audit
  AFTER INSERT OR UPDATE OR DELETE ON customers
  FOR EACH ROW EXECUTE FUNCTION audit_customers();

go deeper

for a junior

Know the three options exist and that a trigger fires inside the same transaction so history cannot be missed by any writer.

for a middle

Compare completeness, transactional safety and cost, and mention the session-variable trick for capturing the acting user in a trigger.

for a senior

Bring the operational edges: TRUNCATE and bulk-load bypasses, write-ahead log amplification and replication lag, CDC slot retention, before-image configuration, and the dual-write hazard.

for a principal

Assign mechanisms per purpose — compliance, support, analytics — and require verification: periodic reconstruction of rows from history compared against live data, with alerting on divergence.

## The requirement behind the choice Before comparing mechanisms, pin down what the history is for. *Compliance auditing* demands completeness and tamper resistance and must be queryable next to the data. *Debugging/support* wants context and readability and tolerates gaps. *Analytics and replication* want throughput and can tolerate latency. The mechanisms rank differently against each. ## Database triggers A trigger on the audited table writes a row into the history table for every insert, update and delete. **Strengths** - **Completeness.** It runs no matter who writes: the application, a second service, a data migration, a DBA typing SQL at 2am. This is the single strongest argument, and the reason auditors like it. - **Atomicity.** The history row is written in the same transaction. If the change rolls back, so does its record — no window in which a change exists without a trace, and none in which a trace exists without the change. - **No coordination.** Adding auditing to a table needs no application deploy. **Weaknesses** - **Write cost.** Every row modified pays for trigger invocation plus an extra insert. On a hot table this is a measurable throughput and latency hit, and it roughly doubles write-ahead log volume, which shows up as replication lag. - **Missing actor.** The database knows the connection user, typically one shared service account. To record the application user you must push it into the session (`SET LOCAL audit.user_id = …`) at transaction start and read it in the trigger. That works, but it re-introduces a discipline requirement: anyone writing outside the application leaves it null, and you must decide whether a null actor is acceptable or a failure. - **Bypass paths.** `TRUNCATE` does not fire row triggers (use a statement-level trigger or revoke the privilege). Some bulk-load utilities can skip triggers. Cascaded deletes from foreign keys *do* fire triggers, which occasionally surprises people in the other direction. - **Operational friction.** Trigger logic is invisible to developers reading application code, is awkward to version and test, can recurse, and interacts badly with statements that touch millions of rows. `MERGE`-style statements need care so that each branch is captured correctly. - **Locality.** Triggers fire where the write executes. On a physical replica nothing fires, so history must be produced on the primary. With logical replication, whether triggers fire on the subscriber depends on configuration — worth checking rather than assuming. ## Application-level writes The application, usually via an ORM interceptor or an explicit repository method, writes the history row alongside the change in the same transaction. **Strengths** - **Rich context.** User id, tenant, request/trace id, the business reason, the API endpoint — everything the database cannot see. - **Semantic granularity.** You can record a domain event ("order cancelled by customer") rather than a row diff, which is far more useful for support. - **Testable and reviewable** in the same language and repository as the rest of the code, with no hidden DDL. **Weaknesses** - **Incompleteness.** It is exactly as complete as your discipline. A migration script, a second service, an admin tool, or a manual fix writes without history — and nothing detects it. Over years, this is the failure mode that turns an audit trail into an unreliable one. - **Drift across write paths.** Bulk operations, upserts and native queries often bypass the ORM interceptor even inside the same application. - **Dual-write temptation.** Sending history to a separate service or queue after the commit means a crash between the two loses the record permanently. Keeping it in the same database and transaction is the only safe version; if it must leave the database, use the transactional outbox pattern. ## Log-based change data capture A connector reads the engine's write-ahead log (PostgreSQL logical decoding, MySQL binlog, SQL Server CDC) and emits a stream of row-level changes, typically into Kafka. **Strengths** - **Near-zero write-path cost.** Transactions are untouched; the work happens in a separate reader process. - **Completeness by construction.** Every committed change is in the log, whoever issued it. - **Ordering and transaction boundaries** are preserved, and the stream is naturally reusable for search indexes, caches and the warehouse — not just history. **Weaknesses** - **Asynchronous.** History exists only after the connector processes the log. If the connector is down, history lags; if its replication slot is not consumed, the database retains write-ahead log and can fill its disk — a real production hazard. - **Wrong shape for in-database queries.** The result is a stream or a table in another system, so "show this record's history" cannot be a simple join. Teams often stream it back into a history table, which adds a moving part. - **Operational surface.** Connectors, offsets, schema registry, replication slots, and a rerun/backfill story. - **Payload caveats.** Depending on the engine and settings, unchanged large values may be omitted from the change record, and "before" images require configuring the table's replica identity or equivalent. Schema changes must be handled explicitly. - **Still no actor.** The log records the change, not the human. The usual trick is to have the application write a marker row (or set a field) that the connector correlates with. ## Choosing, and combining A defensible default: **triggers (or engine-native system versioning) for the tables where history is a compliance requirement**, because completeness and transactional atomicity are non-negotiable there; **application-level events for the user-facing activity feed**, because context matters more than completeness; and **CDC for feeding analytics, search and caches**, because throughput matters and latency does not. These are not exclusive — many mature systems run all three for different purposes. Whatever you choose, verify it: periodically reconstruct a sample of rows from history and compare against the live values, and alert when the reconstruction diverges. An audit trail that has never been tested is an assumption, not a control.

  • How does a trigger learn which application user made the change?
    It cannot on its own — the database sees only the connection's role, usually a shared service account. The standard technique is for the application to set a session-scoped variable at the start of each transaction (for example `SET LOCAL audit.user_id = '42'`) which the trigger reads. It works, but anyone writing outside the application leaves it empty, so you must decide whether a missing actor is tolerated or treated as an error.
  • Why can a log-based CDC connector being offline threaten the database itself?
    The engine must retain write-ahead log segments that the connector's replication slot has not yet confirmed, so an unconsumed slot causes log retention to grow without bound and can fill the disk, which takes the database down. Monitoring slot lag and having a policy for dropping abandoned slots is mandatory operational hygiene for CDC deployments.
  • Why is writing history to a separate service after committing the data change unsafe?
    It is a dual write: the data change and the history record are in different failure domains, so a crash, timeout or deploy between the two loses the history permanently, silently and unrecoverably. The safe options are writing history in the same transaction (trigger or same-database insert), using a transactional outbox that a relay publishes, or deriving history from the database log.

saying these in an interview costs you the question

  • Assuming a TRUNCATE is captured by row-level triggers
  • Believing application-level auditing is complete when migrations and other services also write
  • Writing the audit record after the commit or in a separate transaction
  • Treating CDC as synchronous, or ignoring the replication-slot retention hazard
  • Expecting any of the three to record the application user automatically

context

resolving links…