Several tables in a system need change history. How would you decide how much temporal machinery each table gets, and how do you keep the history from becoming the largest and slowest part of the database?

## Start from the requirement, table by table "Add auditing" as a blanket policy produces a database where most of the bytes, most of the write-ahead log and most of the operational pain come from data nobody has ever queried. The first job is to separate the requirements, which are genuinely different: - **Regulatory / compliance history.** Somebody outside the company may demand proof of what changed and when. Requires completeness, tamper resistance, actor identity, and a defined retention period. Typically a small set of tables: money movements, entitlements and permissions, pricing, consent records, personal-data changes. - **Operational forensics.** Support and engineers need to reconstruct "what happened to this account". Wants context and readability, tolerates gaps, and can live with a short retention window. - **Product features.** Undo, version history in the UI, "who edited this document". This is a functional requirement with its own product semantics — it may need diffs and authorship rather than raw row images. - **Analytics.** Slowly-changing dimensions and trend analysis. This belongs in the warehouse, fed by change data capture, and should not shape the OLTP schema. Each maps to a different mechanism and a different retention. A table can, legitimately, be in none of these categories — `updated_at` and backups are a complete answer for most reference data. ## The cost model to state out loud History is a write amplifier. A trigger-based history table means every update writes a second row, touches that row's index, and doubles the write-ahead log generated — which propagates as replication lag, backup size and storage cost. On a table taking thousands of writes per second, that is a capacity decision, not a detail. Two levers reduce it before any storage tuning: **audit fewer tables**, and **audit fewer columns** (nobody needs the history of `last_seen_at`). Second, history grows monotonically while live data does not. A table with a 5% daily churn produces more history rows than live rows within a month. Estimate at 12 and 36 months during design; the number usually changes the design. ## Keeping history off the hot path - **Separate tables, always.** Mixing current and historical rows in one table means every ordinary query filters against a table that is mostly history, and index depth grows for everyone. Engine-native system versioning that stores history in a separate table has the same shape. - **Partition by time.** Monthly range partitions on `changed_at` make retention a partition drop instead of a mass delete — instant, no dead rows, no bloat, no long transaction. This single decision is the difference between a purge that runs in seconds and one that runs for days. - **Index minimally.** `(entity_id, changed_at DESC)` covers the overwhelmingly dominant read. Add `(changed_by, changed_at)` only if cross-entity forensics is a real workflow. Every extra index is a tax on the write path you were already worried about. - **Compress and tier.** Older partitions compress well (repetitive rows) and can move to cheaper storage or out of the OLTP database entirely — an archive database or object storage with a documented retrieval path. - **Do not couple to live schema.** No foreign keys into live tables (the entity may be deleted, and the constraint would block or cascade). No unique constraints. If the live schema changes frequently, store the payload as JSON so a column addition does not require a migration of a billion-row history table. - **Protect it.** Revoke `UPDATE` and `DELETE` on history from the application role so the trail cannot be rewritten by application bugs or by an attacker with application credentials. Retention jobs run under a separate, restricted role. ## Retention and its conflicts Every history table needs a retention period with a written justification, and a legal-hold mechanism that suspends deletion for specific entities. Retention conflicts with privacy obligations in a specific, awkward way: history tables are exactly where the *old* values of personal data live, so an erasure request must reach them too. Options are anonymizing the affected columns in history rows, deleting those history rows and recording that an erasure occurred, or encrypting personal fields per subject so destroying the key neutralises every retained copy. Decide this at design time, because retrofitting erasure into an immutable, tamper-protected, partitioned history table is genuinely hard. ## Verification An audit trail nobody has tested is an assumption. Build a reconciliation job: pick a sample of entities, reconstruct the current row by replaying history, and compare against the live table. Divergence means a write path is bypassing capture — a bulk load, a migration, a `TRUNCATE`, an ORM path that skips the interceptor. Alert on it. Also test the read path you promised: if the requirement is "reconstruct any row as of any date", make that a query someone runs regularly, not a capability discovered to be broken during an audit. ## How to present the decision The strong answer is a policy plus numbers: which tables get which mechanism and why, the projected history volume at 12 and 36 months, the retention and archival tiers, who may read and modify the history, how erasure interacts with it, and what signal would cause you to change the design — history exceeding some share of total storage, write latency regressing past a target, or a new obligation arriving. That is a principal-level answer; "we put triggers on everything" is not.