Retention-Period Query Patterns for FSMA 204 Audit Logs

Selecting the audit records inside the two-year retention window sounds trivial — a WHERE event_timestamp >= cutoff — until the audit table holds hundreds of millions of rows across years of monthly partitions and that one predicate triggers a full scan on every export. An inspection export that takes forty minutes because the query reads every partition, including the ones entirely outside the window, is both an operational problem and a correctness risk: slow retention queries get “optimized” by hand into forms that quietly select the wrong boundary. This guide, part of the Structured Audit Log Export component, shows why the naive query full-scans and how to make the planner touch only the partitions that can contain matching rows.

The failure is easy to reproduce and easy to miss, because on a small table the naive query is fast and correct. It degrades only at production scale, and it degrades silently — the answer is still right, just expensive — until an inspector is waiting. Worse, the common attempts to speed it up (wrapping event_timestamp in a function, comparing against a local-time literal, or filtering on a derived column) defeat both partition pruning and the index at once, turning a slow-but-correct query into a fast-but-wrong one that selects the wrong window.

Root Cause: Predicates the Planner Cannot Prune On

Audit stores for Critical Tracking Events are almost always range-partitioned by time — one partition per month — because that matches how the Data Retention Policies engine ages records out. Partition pruning is the optimization that lets the planner skip any partition whose time range cannot overlap the query’s window. It works only when the WHERE predicate is sargable on the partition key: a bare comparison of the partition column against a constant, like event_timestamp >= '2024-07-16T00:00:00+00:00'.

Three things break pruning. First, wrapping the column in a function — WHERE date(event_timestamp) >= '2024-07-16' — hides the partition key from the planner, so it cannot reason about which partitions qualify and reads them all. Second, comparing against a non-constant or mistyped literal — a naive-datetime string, or now() - interval '2 years' computed per-row in some engines — can force a scan or, worse, shift the boundary by the server’s timezone offset. Third, filtering on a derived column that is not the partition key entirely bypasses pruning. Any of these turns a query that should read 24 monthly partitions into one that reads all of them, and the function-wrapping case can also silently move the two-year boundary by up to a day.

Minimal Reproducible Example

The table is range-partitioned by month on event_timestamp. The slow query wraps the column in date() and compares against a local-date string, which both defeats pruning and risks a boundary shift.

-- Partitioned audit store (one partition per month).
CREATE TABLE audit_events (
    audit_id        text        NOT NULL,
    event_timestamp timestamptz NOT NULL,
    actor_id        text        NOT NULL,
    action          text        NOT NULL,
    target_record_id text       NOT NULL,
    traceability_lot_code text  NOT NULL
) PARTITION BY RANGE (event_timestamp);

-- Slow + risky: date() hides the partition key, and '2024-07-16' is a
-- naive local date, so the planner scans every partition and the two-year
-- boundary can drift by the server's UTC offset.
SELECT audit_id, event_timestamp, actor_id, action
FROM audit_events
WHERE date(event_timestamp) >= '2024-07-16';

Driven from Python, the same mistake usually enters as a computed cutoff that is timezone-naive:

from datetime import datetime, timedelta

# BUG: naive local time — no tzinfo, so the DB coerces it against the
# session timezone and the window boundary shifts.
cutoff = datetime.now() - timedelta(days=730)
query = "SELECT audit_id FROM audit_events WHERE date(event_timestamp) >= %s"
# db.execute(query, (cutoff.date(),))  # full scan + wrong boundary

On a table with 36 monthly partitions, EXPLAIN on the slow query shows all 36 scanned. The result is correct only by luck of the server timezone matching UTC, and it reads roughly eighteen times the data it needs.

Fix Implementation

Compare the bare partition key against a timezone-aware UTC constant. The predicate stays sargable so the planner prunes, an index on event_timestamp handles the residual in-partition selection, and the cutoff is computed in UTC so the two-year boundary is exact.

-- Index the partition key so in-window selection is a range scan, not a seq scan.
CREATE INDEX idx_audit_events_ts ON audit_events (event_timestamp);

-- Fast + correct: bare column vs. a UTC constant. The planner prunes every
-- partition whose range ends before the cutoff and range-scans the rest.
SELECT audit_id, event_timestamp, actor_id, action
FROM audit_events
WHERE event_timestamp >= TIMESTAMPTZ '2024-07-16 00:00:00+00'
  AND event_timestamp <  TIMESTAMPTZ '2026-07-16 00:00:00+00';

from datetime import datetime, timedelta, timezone

def retention_window_bounds(now: datetime | None = None) -> tuple[datetime, datetime]:
    """Return the [cutoff, upper) bounds of the two-year window in UTC.

    Both bounds are timezone-aware so the partition-key comparison is exact
    and never shifted by the database session timezone.
    """
    now = (now or datetime.now(timezone.utc)).astimezone(timezone.utc)
    cutoff = now - timedelta(days=730)
    return cutoff, now

# Parameterized, sargable, half-open interval — pruning-friendly and exact.
lower, upper = retention_window_bounds()
query = (
    "SELECT audit_id, event_timestamp, actor_id, action "
    "FROM audit_events "
    "WHERE event_timestamp >= %s AND event_timestamp < %s"
)
# db.execute(query, (lower, upper))

Three changes fix it together. The predicate compares event_timestamp directly — no date() wrapper — so pruning is restored. The bounds are TIMESTAMPTZ constants (or timezone-aware Python datetime values) computed in UTC, so the boundary is exact regardless of the session timezone, the same UTC discipline the Data Retention Policies engine uses. And the half-open interval >= lower AND < upper avoids the off-by-one at both edges. The idx_audit_events_ts index turns the in-partition work into a range scan.

Verification Steps

Prove the fix two ways: the planner prunes, and the row count is unchanged from the correct baseline. First, EXPLAIN must show only the in-window partitions.

EXPLAIN (ANALYZE, BUFFERS)
SELECT audit_id FROM audit_events
WHERE event_timestamp >= TIMESTAMPTZ '2024-07-16 00:00:00+00'
  AND event_timestamp <  TIMESTAMPTZ '2026-07-16 00:00:00+00';
-- Expect: only the ~24 partitions overlapping the window appear in the plan,
-- each as an Index Scan on idx_audit_events_ts — not 36 Seq Scans.

Then confirm the fast query returns exactly the same rows as a correct-but-slow reference, so the speedup did not change the answer.

def assert_window_equivalence(db, lower, upper) -> None:
    fast = "SELECT count(*) FROM audit_events WHERE event_timestamp >= %s AND event_timestamp < %s"
    # Reference uses a sargable-but-unpruned form purely to cross-check the count.
    reference = (
        "SELECT count(*) FROM audit_events "
        "WHERE event_timestamp BETWEEN %s AND %s - interval '1 microsecond'"
    )
    fast_n = db.scalar(fast, (lower, upper))
    ref_n = db.scalar(reference, (lower, upper))
    assert fast_n == ref_n, f"window mismatch: fast={fast_n} ref={ref_n}"
    print(f"retention window verified: {fast_n} rows in [{lower}, {upper})")

If the counts match and EXPLAIN shows pruning, the query is both fast and correct. Capture the EXPLAIN output as part of the export manifest — it is the evidence that the retention window fed into the audit-log export was computed over the right partitions.

Related Edge Cases

• Records exactly on the cutoff instant. The half-open interval decides them deterministically — >= lower includes the cutoff microsecond, < upper excludes the upper bound. Pick the convention once and keep it, so two exports of the same window never disagree by one boundary row.
• A recall scope narrower than two years. When an FDA request scopes a specific date range, the same sargable pattern applies with tighter bounds; do not switch to a function-wrapped predicate for the narrower query or you lose pruning again. This feeds the FDA 24-Hour Response Automation directly.
• Late-arriving events written into an old partition. Backfilled audit rows can land in an already-scanned month; re-run the count check after any backfill so the window total stays reconciled, and verify the hash chain from the Append-Only Ledger & Hash Chaining guide still closes.

Frequently Asked Questions

Related

• Structured Audit Log Export for FDA Submission — the export component this retention query feeds
• Exporting audit logs to FDA submission templates — projecting the selected window onto the FDA columns
• Data Retention Policies — the two-year floor and UTC discipline this query enforces
• FDA 24-Hour Response Automation — the recall workflow that runs narrower-scoped versions of this query
• Append-Only Ledger & Hash Chaining — the integrity check to re-run after backfilling old partitions

Up: Structured Audit Log Export for FDA Submission — this guide details the retention-window query that selects the records the parent export renders.