You are asked to enable comprehensive database auditing on a high-throughput transactional system without wrecking its latency, its disk budget, or your log-ingest bill. How do you decide what to capture and how long to keep it?

## Frame the problem correctly The failure mode this question probes is "turn everything on and see what happens", which ends in one of two places: an incident where the audit volume degrades or halts the database, or a quiet decision at 3 a.m. to disable auditing that nobody re-enables. The senior move is to derive scope from **control objectives** — the specific questions the organisation must be able to answer, and the regulation or contract that requires them — and to treat the volume budget as a first-class design constraint. Useful control objectives sound like: *who changed the schema*, *who granted themselves access*, *what did the break-glass account do during that window*, *which accounts read patient records last quarter*. Each maps to a narrow audit rule. "Log all SQL" maps to none of them and costs the most. ## Where the money actually goes Five cost centres, and candidates usually name only the first: 1. **Local disk and IO on the database host** — audit writes compete with the workload for the same devices unless separated. 2. **CPU and latency on the hot path** — formatting and emitting a record per statement is not free, and synchronous emission adds directly to statement latency. 3. **Network and collector capacity** — shipping gigabytes per hour off-box. 4. **Downstream ingest and retention pricing** — SIEM and log platforms typically charge per GB ingested, and this is very often the largest line item by an order of magnitude. An audit policy is a procurement decision. 5. **Investigation cost** — a trail so voluminous that queries over it take hours is a trail nobody uses. Signal density is a design goal, not a nicety. **Estimate before enabling.** Take statements per second in each candidate class, multiply by realistic record size (statement text dominates, and audit records of a few hundred bytes to a few kilobytes are typical), and produce bytes/hour and dollars/month per rule. Do it on a replica or with a short window on a canary instance. Bringing a number to the design review is what distinguishes this answer. ## Reduce at the source, in this order 1. **Scope by object.** Audit `patients`, `payments`, `credentials` — not every table. Volume then tracks sensitive access, not total traffic. 2. **Scope by principal.** Capture *everything* superuser, DBA, break-glass and human sessions do, while leaving the application's pooled login on a narrow policy. Human sessions are a rounding error in volume and the bulk of the risk. 3. **Prefer cheap classes.** Logins, DDL and privilege changes are effectively free and answer a disproportionate share of real questions. 4. **Fix the architecture the audit exposes.** If read auditing on one column produces enormous volume, the finding is usually that a hot path reads data it does not need; removing the column from the default projection or routing bulk consumers to a derived dataset reduces both risk and log volume at once. 5. **Redact where allowed.** Recording parameterised statements without literal values shrinks records and avoids duplicating sensitive values into the audit store — but reduces forensic detail, so the data-protection owner decides, not the DBA. What you must **not** do is sample. Statistical sampling is legitimate for performance telemetry and meaningless for security evidence: the question is always about one specific access, and a trail that captured one request in ten cannot answer it. If volume is unaffordable at full fidelity, narrow the scope, do not thin the capture. ## Protect the database from its own auditing Audit output must not share a device with data or transaction-log files; a full audit volume otherwise stalls or halts the database. Ship records off-host quickly, keep only a bounded local spool sized to absorb a plausible collector outage, and alert on spool depth well before it fills. Decide explicitly what happens when the spool does fill — refuse in-scope work, or continue unaudited — and record that decision with the control owner rather than discovering it during an incident. Prefer asynchronous emission unless the compliance regime demands synchronous durability of the record, since synchronous audit writes put the sink's latency directly into transaction latency. ## Retention as tiers, with an end date Retention is not one number: - **Hot** — days to a few months, indexed and searchable, sized for incident response and routine investigations. - **Warm/cold** — compressed archive in object storage for the regulated period (commonly one to seven years), retrievable in hours, ideally with write-once retention locks. - **Deletion** — an actual, exercised expiry. Privacy regimes oblige deletion as well as retention, and because audit records frequently contain personal data, an indefinitely retained audit store becomes its own compliance liability. Operationally, partition any in-database audit tables by time and drop partitions rather than issuing mass DELETEs: dropping a partition is a metadata operation, whereas deleting hundreds of millions of rows generates transaction-log traffic, bloat and vacuum load on the system you were trying to protect. Verify restores from cold storage periodically — archives that have never been read back are an assumption, not a control. ## How to pitch it Lead with "scope follows the control objectives", give the volume estimate method with the ingest bill named as the dominant cost, list the reduction levers in order, refuse sampling explicitly, and finish with tiered retention plus partition-drop purging and the fail-open/fail-closed decision.