Measure audit_log action/resource_type MV partition distribution before deciding on the index hotspot #819

Split out of #805, which is now closed — its other two questions are answered and shipped in #812. This is the one that remains, and it is the one that cannot be answered by reading code.

The question

audit_log carries four secondary indexes on Cassandra/ScyllaDB after #812:

ScyllaDB implements a global secondary index as a materialized view partitioned by the indexed value. For a low-cardinality column that means a small number of very large partitions — the classic index hotspot — and audit_log is among the highest-write tables in the system (every login, token issuance and admin action).

action is the worst case rather than resource_type: user.login_success collects a row for every successful login on the deployment, all in one MV partition.

Why this is still open rather than acted on

It is a plausible inference from how Scylla implements secondary indexes, not a measurement. Deciding from distinct-value counts alone would be guessing, and the cost of guessing wrong is real in both directions:

• Drop the indexes and every single-column audit filter becomes a scan — and action is what the dashboard's Audit Logs page sends most.
• Keep them and, if the hotspot is real, the write amplification lands on the table least able to absorb it.

Both indexes also predate #812 in shape: resource_type was added in #802 in the same shape as the pre-existing action one, so whatever holds for one likely holds for both.

What would settle it

MV partition-size distribution on a deployment with real login volume:

SELECT * FROM system_distributed.view_build_status;

nodetool tablehistograms authorizer.authorizer_audit_log_action_index
nodetool tablehistograms authorizer.authorizer_audit_log_resource_type_index

The number that matters is the partition-size distribution for the action index — specifically whether the largest partitions approach the practical Scylla limit, and how fast they grow relative to audit volume. Anything short of that is reasoning from first principles about someone else's cluster.

If the answer turns out to be "drop them"

#812 made that cheap on purpose. It is now three coordinated edits, and a test fails if any is forgotten:

1. remove the column from auditLogIndexedColumns (internal/storage/db/cassandradb/audit_log.go)
2. remove its CREATE INDEX (provider.go)
3. remove it from cassandraIndexProbes (provider.go, added in fix(storage): make cassandra index readiness probe actually wait #810)

buildAuditLogWhere then routes that filter to the ALLOW FILTERING scan path automatically, because the hint decision is derived from the indexed-column set rather than the clause count. TestAuditLogIndexedColumnsMatchCreatedIndexes reads provider.go and fails in both directions, so a set entry with no index behind it — which Cassandra would reject at query time, not merely run slowly — cannot ship.

Existing deployments keep any index that stops being created, so a removal is non-destructive and needs an operator DROP INDEX note in the changelog, as #812 did for created_at.

Also worth deciding at the same time

Whether Couchbase follows. For created_at the answer was no — N1QL serves range predicates from a GSI and every ListAuditLogs there carries ORDER BY created_at DESC, so dropping it would have regressed the exact path it exists for. The parity rule in AGENTS.md is about comparable query cost across backends, not identical index lists, and for low-cardinality equality columns the two engines may well diverge again.