Cassandra Runbook

NOTE 08 · SAI INDEX

ReadFailure from a large IN on an SAI column

A ReadFailure on a query with a large IN against an SAI-indexed clustering column is usually the query doing too much work, not an index that is down. Rebuilding the index does not fix it.

What you are looking at

The application reports a ReadFailureException at LOCAL_ONE or higher: some number required, zero or few responded, several failed. The query has no partition key, or a very wide one, and a clustering column (or other non-partition column) with an SAI index in a large IN list. Ten to twenty values is already enough to be expensive when the table has many SSTables. The application log often includes the list and a count.

This is a distributed index query. On every replica that participates, SAI looks up each value in the IN list in the on-disk index, unions the postings from the memtable and from each SSTable's index, then streams and post-filters. Cost scales with the number of values, the number of SSTables, and the number of replicas contacted. Some replicas fail the request under that work. You get a ReadFailure (replicas actively failed), not a timeout and not INDEX_NOT_AVAILABLE.

Confirm it

  1. The column in the IN is not the partition key, and it has an SAI index. Check system_schema.indexes or the table definition. If there is no SAI index, this is a different query problem.
  2. Count the values in the IN from the application log or the prepared statement. A long list on a non-partition column is the pattern.
  3. nodetool tablestats on the table. A high SSTable count multiplies the index work. Each SSTable has its own SAI files.
  4. If you need to see which replica fails, run the query at CONSISTENCY LOCAL_ONE against individual nodes, the same way you would for an index-not-available check. A node that fails here is overloaded by the query, not missing its index.
  5. On DSE, dse_perf.node_slow_log (when skip_writing_to_db is false) can show the statement and how long it ran. The slow log is confirmation, not the first check.

What to change

The fix is in the query or the data model, not in a nodetool command on the index.

Compaction that reduces the SSTable count lowers the cost of the index lookup. It does not make a large IN without a partition key a good query. Treat compaction as relief, not the design fix.

Done when

The application no longer sends the wide IN against the SAI column, or it sends it with a partition key, or it sends it in small batches. The ReadFailure for that statement stops. The index is still present and still used for narrower queries. You did not rebuild it.

Do not

SAI query cost and the exact slow-log table differ by version. The pattern is the same: a ReadFailure on a large IN against an indexed non-partition column, with the index present and the replicas failing under the work. This is a decision aid, not a procedure for a specific cluster.

Back to notes