What are secondary indexes in Cassandra and when should you avoid them?
Learn how Cassandra secondary indexes work, why they scatter across nodes, which columns to avoid indexing, and when to denormalize for scalable queries.
Expected Interview Answer
A secondary index in Cassandra lets you query a table by a non-partition-key column that is not part of the primary key, by maintaining a hidden per-node index of that column's values. You should avoid it for high-cardinality columns, very low-cardinality columns, frequently updated columns, and any query that must scan across many nodes.
Unlike a relational index, a Cassandra secondary index is local to each node: it only indexes the data that node owns. A query filtered solely on a secondary index has no partition key, so the coordinator must fan out to every node in the cluster, gather partial results, and merge them — turning a supposedly fast lookup into a cluster-wide scatter-gather. For scalable access patterns you should instead denormalize into a second table keyed by the column you want to query, or use a purpose-built alternative like a SASI or storage-attached index (SAI) where appropriate.
- Enables ad-hoc queries on non-key columns without a new table
- Simple to create with a single CREATE INDEX statement
- Efficient when combined with a partition key to stay node-local
- Useful for low-to-moderate cardinality columns within a partition
- Avoids maintaining a separate denormalized table for rare queries
AI Mentor Explanation
Think of a secondary index as asking every scorer stationed at a different ground to flip through only their own local scorebook to find all sixes hit by left-handers. Each scorer knows only their matches, so the captain must radio all of them and stitch the answers together. That cluster-wide relay is exactly why filtering purely on a secondary index across many grounds is slow and unreliable at scale.
Step-by-Step Explanation
Step 1
Identify the query
Decide which non-key column you need to filter on and how often the query runs.
Step 2
Check cardinality
Avoid indexing extremely high-cardinality columns (like emails) or extremely low-cardinality ones (like booleans).
Step 3
Prefer partition-scoped queries
Only use a secondary index alongside a partition key so the lookup stays on one node.
Step 4
Consider a denormalized table
For scalable access, model a second table keyed by the column you want to query on.
Step 5
Evaluate SAI/SASI
For newer clusters, weigh storage-attached indexes that handle more patterns than legacy secondary indexes.
What Interviewer Expects
- Understanding that secondary indexes are per-node, not global
- Awareness of the scatter-gather cost without a partition key
- Cardinality guidance for good vs bad index columns
- Knowing denormalization is the idiomatic Cassandra alternative
- Familiarity with SASI/SAI as modern options
Common Mistakes
- Assuming a secondary index works like a relational B-tree index
- Indexing high-cardinality columns like unique IDs or emails
- Querying purely on a secondary index without a partition key
- Indexing frequently updated columns, causing tombstone buildup
- Using secondary indexes instead of proper query-first data modeling
Best Answer (HR Friendly)
“A secondary index lets Cassandra search a table by a column that isn't its main key, but each server only indexes its own data. That means a search on the index alone has to ask every server and combine answers, so it's best avoided for large-scale queries — you usually build a second table instead.”
Code Example
CREATE TABLE users (
user_id uuid PRIMARY KEY,
city text,
email text
);
-- Local, per-node index on city
CREATE INDEX ON users (city);
-- Cluster-wide scatter-gather (avoid at scale)
SELECT * FROM users WHERE city = 'London';
-- Better: a query-first table keyed by city
CREATE TABLE users_by_city (
city text,
user_id uuid,
email text,
PRIMARY KEY (city, user_id)
);Follow-up Questions
- How does a SASI index differ from a legacy secondary index?
- What is storage-attached indexing (SAI) in modern Cassandra?
- Why is denormalization preferred over indexes in Cassandra?
- How do tombstones affect indexes on frequently updated columns?
- When is it acceptable to use a secondary index with a partition key?
MCQ Practice
1. Why is a query filtered only on a secondary index slow at scale?
Secondary indexes are node-local, so a query without a partition key must scatter to all nodes and merge results.
2. Which column is the worst candidate for a secondary index?
Very high-cardinality columns like unique emails create huge, inefficient indexes and are poor secondary-index candidates.
3. What is the idiomatic Cassandra alternative to a secondary index?
Cassandra favors modeling a second table keyed by the query column so lookups stay partition-scoped and fast.
Flash Cards
Are Cassandra secondary indexes global or local? — Local to each node — each node indexes only the data it owns.
When does a secondary index query stay fast? — When it's combined with a partition key so the lookup stays on one node.
Worst column types to index? — Very high-cardinality (unique IDs) and very low-cardinality (booleans).
Preferred alternative? — A denormalized table keyed by the column you want to query on.