Apache Cassandra
Secondary Indexes & SASI Indexes
Understand when secondary indexes are appropriate in Cassandra and their significant performance limitations.
A secondary index in Cassandra allows querying a table by a non-primary-key column without using ALLOW FILTERING. Under the hood, Cassandra maintains a hidden index table that maps indexed column values back to the primary keys of matching rows — but critically, this index is built and maintained locally per node, not globally across the cluster.
A secondary index is like asking every single branch of a nationwide store chain to check their own local inventory list for a product, rather than having one central inventory system you can query directly.
Key Concepts
1
This local-only nature is the biggest gotcha: a query using a secondary index must be broadcast to all nodes in the cluster (a scatter-gather operation), since the coordinator has no way to know which nodes contain matching rows without asking everyone. This makes secondary indexes fundamentally unsuitable for high-cardinality columns or large clusters, where the fan-out cost dominates.
all nodes
2
SASI (SSTable Attached Secondary Index) was introduced as an improved indexing implementation supporting more powerful query types, including prefix/suffix/contains text matching and range queries, with better performance characteristics than the original built-in secondary index implementation for certain workloads. However, SASI has since been effectively deprecated/experimental in many Cassandra distributions in favor of newer approaches like Storage-Attached Indexes (SAI) in Cassandra 5.x.
SASI (SSTable Attached Secondary Index)
3
In most production systems, secondary indexes are avoided in favor of maintaining a denormalized query table (per query-first modeling) that lets the query be answered via an efficient single-partition read instead.
denormalized query table