Review audit_log secondary-index strategy on Cassandra/Scylla (low-cardinality hotspots) #805

Activity

1. lakhansamani

   added 2 commits that reference this issue on Oct 14, 2026

   refactor(storage): drive audit-log scan hint from the indexed-column set

   ...

   49c5188

   refactor(storage): drive audit-log scan hint from the indexed-column …

   ...

   3aaa1f8
2. lakhansamani commented on Oct 14, 2026

   

   lakhansamani

   on Oct 14, 2026

   ContributorAuthor

   More actions

   Partially resolved by #812. Recording what shipped and what this issue still tracks.

   Shipped

   The coupling is gone. The ALLOW FILTERING decision now derives from an explicit auditLogIndexedColumns set rather than len(clauses) > 1. This was the blocker named in the third bullet above: a hint-less equality on an unindexed column is rejected by Cassandra, not merely slow, so no index could be removed while the decision was positional. Dropping or adding an index is now a one-line edit to that set, guarded by TestAuditLogIndexedColumnsMatchCreatedIndexes, which reads provider.go and fails in both directions (set entry with no index; index with no set entry).

   created_at's index is dropped — answer to the first question: no, nothing used it. A Cassandra 2i serves equality only, and both queries this table issues against the column are ranges (ListAuditLogs bounds, and DeleteAuditLogsBefore's created_at < ? ALLOW FILTERING). Pure write amplification on the highest-write table. Non-destructive: existing deployments keep theirs until dropped by hand, CQL in a comment at the removal site.

   Correction to the issue's closing line. "Whatever is decided should be applied to both backends together" does not hold for created_at. Couchbase's AuditLogCreatedAtIndex is load-bearing — N1QL serves range predicates from a GSI, every ListAuditLogs query there carries ORDER BY created_at DESC, and the delete is created_at < $before. Dropping it there would regress the exact path it exists for. The parity rule is comparable query cost across backends, not identical index lists; here identical lists would produce divergent cost.

   Still open — the second question

   Whether the action (~60 distinct values) and resource_type (~17) low-cardinality MV hotspots are acceptable, or should ride the scan path.

   Deliberately not guessed at. The hotspot is a plausible inference from how Scylla implements a global secondary index (MV partitioned by the indexed value), but the inputs needed to decide are MV partition sizes and write amplification measured on a real cluster with real audit volume — not reasoning from distinct-value counts. Note also that both indexes predate #802; resource_type was added in the same shape as the pre-existing action one, so whatever is true of one is likely true of both.

   With the set in place, acting on it later is: remove the column from auditLogIndexedColumns, remove its CREATE INDEX, remove it from cassandraIndexProbes (added in #810). The hint logic then routes it to a scan automatically, and the test fails if any of the three is forgotten.

   Measurement worth taking first

   -- per-partition row counts in the index view, largest first
   SELECT * FROM system_distributed.view_build_status;
   nodetool tablehistograms authorizer.authorizer_audit_log_action_index;

   A partition-size distribution on a deployment with real login volume answers this; nothing short of it does.
3. lakhansamani commented on Oct 15, 2026

   

   lakhansamani

   on Oct 15, 2026

   ContributorAuthor

   More actions

   Closing. Two of the three questions are answered and shipped in #812 (released in 2.4.2); the third is split out to #819 because it needs cluster measurement rather than code review, and leaving it here obscured how much was already settled.

   Answered

   "Is created_at's index used by anything? If not, drop it." — No, and dropped. A Cassandra secondary index serves equality only, and every query this table issues against created_at is a range: the ListAuditLogs bounds and DeleteAuditLogsBefore's created_at < ? ALLOW FILTERING. The index could never be read while still costing a write on every audit row, on the highest-write table in the schema. Verified on main: four CREATE INDEX statements remain (actor_id, action, resource_type, resource_id), and created_at is not among them.

   Existing deployments keep theirs — the removal was non-destructive, with the DROP INDEX CQL in a comment at the removal site and an operator note in the 2.4.2 changelog.

   The coupling — "any index removal has to adjust that logic in the same change" — is gone, and this was the part that actually blocked everything else. The ALLOW FILTERING decision is no longer positional (len(clauses) > 1); it derives from an explicit auditLogIndexedColumns set. That mattered more than it reads: a hint-less equality against a column with no index is rejected by Cassandra, not merely slow, so under the old logic dropping any index would have turned a working filter into an error. TestAuditLogIndexedColumnsMatchCreatedIndexes now reads provider.go and fails in both directions, so the set and the created indexes cannot drift apart again.

   One correction to the issue as written: it closes with "whatever is decided should be applied to both backends together." That turned out to be wrong for created_at. Couchbase's AuditLogCreatedAtIndex is load-bearing — N1QL serves range predicates from a GSI, and every ListAuditLogs there carries ORDER BY created_at DESC plus a created_at < delete. Dropping it would have regressed the precise path it exists for. The AGENTS.md parity rule is about comparable query cost, not identical index lists; here identical lists would have produced divergent cost.

   Moved to #819

   "Is action's hotspot acceptable, or should action/resource_type ride the ALLOW FILTERING scan path?"

   Still open, deliberately. The hotspot is a sound inference from Scylla implementing global secondary indexes as materialized views partitioned by the indexed value — but it is an inference, and the input needed to decide is MV partition-size distribution on a cluster with real login volume, not distinct-value counts. Guessing is costly either way: dropping makes action (what the dashboard sends most) a scan, keeping it lands write amplification on the table least able to absorb it.

   #812 made acting on it cheap when the number arrives: three coordinated edits, with a test that fails if any is forgotten. #819 carries the specific nodetool tablehistograms commands and the exact edit list.
4. lakhansamani

   closed this as completedon Oct 15, 2026