Apache Cassandra
Secondary Indexes & SASI Indexes
Understand when secondary indexes are appropriate in Cassandra and their significant performance limitations.
A secondary index lets you query a column that is not part of the primary key. It is the obvious answer to "I also need to look this up by email", and it is usually the wrong one.
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
The index is local to each node, and that is everything. Each node indexes only the rows it stores.
There is no cluster-wide index mapping a value to the node holding it.
2
So a query by an indexed column has no starting point. The coordinator must ask every node, wait for all of
them, and merge the replies:
3
coordinator -> node A "any rows with email = a@x.com?"
-> node B ...
-> node C ... 100 nodes = 100 requests
-> ... for one row4
A normal Cassandra read touches three replicas. This touches the cluster. Worse, it gets more expensive
as you add nodes — the opposite of how the rest of the database behaves.
5
When it is acceptable. A secondary index is reasonable when the indexed column has middling
cardinality and the query also gives the partition key:
6
SELECT * FROM users WHERE country = 'IN' AND status = 'ACTIVE'7
Here country is the partition key, so only that partition's replicas are contacted and the index is used
within them. No scatter, and the index is doing real work.
country
8
The two extremes both behave badly. Index a column with nearly unique values, such as email, and every
node is asked to find at most one row. Index a column with two values, such as a boolean, and the index
matches half the table on every node, so you have built an expensive way to read everything.
9
SASI, and what replaced it. SASI (SSTable Attached Secondary Index) added prefix, suffix and contains
matching plus better range queries. It is now effectively deprecated, having proved unreliable in
production.
10
Cassandra 5 ships SAI (Storage-Attached Index), which is genuinely better: lower disk overhead, indexes on
several columns, and a sane cost model. The scatter-gather problem remains, because it is inherent to a
local index, not an implementation flaw.
11
What production systems do instead. Write a second table keyed by the thing you want to look up:
12
CREATE TABLE users_by_email (
email text PRIMARY KEY,
user_id uuid
);13
Now finding a user by email is a single-partition read on three replicas, the same cost as any other read,
and it stays that cost at any cluster size. The price is that your code writes both tables. That is the
query-first trade, made explicitly rather than hidden behind an index that quietly does not scale.
14
What the interviewer is probing.1. "When is a secondary index appropriate?" Probing: the narrow fit. Stalls: "For any
query." Moves up: rarely — only for a low-cardinality column queried together with a partition
key; otherwise it fans out to every node.
15
2. "Why do they scale badly?" Probing: the mechanism. Stalls: "They are slow." Moves up:
the index is local to each node, so a query without the partition key must contact them all and
merge.
16
3. "What is the recommended alternative?" Probing: the modelling answer. Stalls: "SASI."
Moves up: a second table modelled for the query, or a materialised view, which keeps reads
partition-local.
17
4. "What does SASI add and what is its status?" Probing: honesty. Stalls: "It is the modern
option." Moves up: richer matching including prefix and range, but it is experimental and not
recommended for production.