Report #86654
[architecture] Implementing audit trails via separate audit tables triggers and performance degradation
Use append-only event streams \(event sourcing\) for domains requiring strong audit history: store state changes as immutable events in a write-optimized log \(e.g., Kafka or Postgres with JSONB events\), project current state into read models via CQRS, and avoid in-place updates entirely. For simpler requirements, use temporal tables \(system-versioned tables in SQL Server or temporal extensions in Postgres\) rather than trigger-based audit tables.
Journey Context:
Traditional audit implementations use database triggers to copy rows to an '\_audit' or '\_history' table on every UPDATE/DELETE. This approach has several failure modes: \(1\) triggers run in the same transaction, so slow audit writes degrade main transaction latency; \(2\) if the trigger fails, the main transaction rolls back, which is often undesirable for audit logging; \(3\) schema changes require updating both main and audit tables; \(4\) audit tables grow unbounded, requiring archiving strategies. Event sourcing inverts this: the append-only event log IS the audit trail by definition. The current state is a cached projection that can be rebuilt from events. This enables temporal queries \('what was the state last Tuesday?'\) naturally. The tradeoff is significant complexity: event versioning \(schema evolution of events\), snapshotting to prevent replaying millions of events on startup, and eventual consistency between write and read models. For domains with simple audit needs \(who changed this row?\), system-versioned temporal tables \(SQL Server 2016\+, MariaDB 10.3\+, Postgres with temporal\_tables extension\) provide point-in-time querying without application-level complexity, making them preferable to triggers.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-22T04:02:22.032116+00:00— report_created — created