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
- The column in the
INis not the partition key, and it has an SAI index. Checksystem_schema.indexesor the table definition. If there is no SAI index, this is a different query problem. - Count the values in the
INfrom the application log or the prepared statement. A long list on a non-partition column is the pattern. nodetool tablestatson the table. A high SSTable count multiplies the index work. Each SSTable has its own SAI files.- If you need to see which replica fails, run the query at
CONSISTENCY LOCAL_ONEagainst 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. - On DSE,
dse_perf.node_slow_log(whenskip_writing_to_dbis 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.
- Supply the partition key when the application has it. The same SAI lookup restricted to one partition is a different and cheaper query.
- Break a large
INinto smaller batches from the client and run them concurrently, if the application can assemble the results. A list of 10 is already a candidate. A list of hundreds is the same problem at higher cost. - If access by that clustering column is the dominant pattern, the table is missing a query-shaped partition key. Denormalize. An SAI index on a clustering column is a convenience for occasional lookups, not a substitute for a partition key.
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
- Rebuild the SAI index.
INDEX_NOT_AVAILABLEis the rebuild case. This error means the index was served and the work failed. Rebuilding does not change the query. - Raise consistency to "make it succeed." More replicas doing the same expensive work fails more often, not less.
- Retry the same statement in a loop. Each retry repeats the index lookups on every SSTable.
- Drop the SAI index to stop the error without a plan for the queries that legitimately use it. The large
INis the problem. The index is what makes the smaller lookups possible. - Treat a small
INon an SAI column as always wrong. A short list, especially with a partition key, is a normal use. The failure mode is the list getting long while the SSTable count is high.
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.