Why architecture matters here
Secondary indexes matter because they're a powerful but frequently-misused Cassandra feature -- misusing them (especially the traditional 2i) causes serious performance and scalability problems, so understanding when and how to use them (versus denormalizing) is essential. Cassandra queries efficiently by partition key (routed to the right node) -- but querying by non-key columns requires secondary indexes, and the traditional 2i has subtle, dangerous performance characteristics (scatter-gather across all nodes for queries without the partition key; cardinality pitfalls at both extremes). Misusing 2i (e.g., a global query by a 2i-indexed column, scatter-gathering across the cluster) causes serious problems (slow queries, unscalable as the cluster grows -- the query cost growing with the node count). So knowing when 2i is appropriate (narrow, per-partition queries) versus when to denormalize (a query table for the access pattern) is essential to a performant, scalable Cassandra data model. For anyone using Cassandra (where the data model and query patterns are critical), understanding secondary indexes (their pitfalls, the denormalization alternative, the modern SAI) is essential, and misusing 2i is a common, costly Cassandra mistake.
The local-index-scatter-gather insight is the crucial understanding, and it's why 2i is dangerous for global queries. The traditional 2i is a local index -- each node indexes only its own data (the index is per-node, co-located with the data). This makes 2i efficient for a query that includes the partition key (the query is routed to the one node holding that partition, which uses its local index to find the matching rows -- a single-node query, efficient). But it makes 2i disastrous for a query without the partition key (a query by only the indexed column): since the index is local (per-node), and the query doesn't specify which node (no partition key), Cassandra must query all nodes (each checks its local index for matches) -- a scatter-gather across the entire cluster. This scatter-gather is expensive (every node involved -- the query cost proportional to the cluster size) and unscalable (as the cluster grows, the scatter-gather hits more nodes -- so the query gets slower, not faster, with scale -- the opposite of what you want). So 2i is efficient for per-partition queries (with the partition key -- single-node) but disastrous for global queries (without the partition key -- scatter-gather across all nodes). This is the crucial understanding: 2i's local nature makes it good for narrow, per-partition queries and bad for global queries (scatter-gather). Understanding the local-index-scatter-gather behavior (2i efficient with the partition key, disastrous without) is understanding when 2i is appropriate and why misusing it (global queries) is dangerous.
And the denormalize-instead principle is the Cassandra-idiomatic solution, reflecting Cassandra's query-first data modeling. Cassandra's data model philosophy is query-first: you design tables for your specific queries (each table structured so a query is an efficient partition-key lookup) -- rather than a normalized model queried flexibly (as in relational databases). So the idiomatic solution to querying by a non-key column is denormalization: create a separate query table keyed for that query (e.g., to query users by email, create a table partitioned by email -- so the query is a normal, efficient partition-key lookup on that table). This means maintaining the data in multiple tables (the original, plus the query tables -- each keyed for a query) -- writing to all of them (denormalized, duplicated) -- which Cassandra's cheap writes make acceptable. This denormalization (query tables per access pattern) avoids the 2i scatter-gather (each query is an efficient partition-key lookup on its purpose-built table) -- the idiomatic, scalable Cassandra approach. Materialized views can automate this (server-maintained query tables -- Cassandra maintaining a query table from the base table automatically) -- though with their own caveats. So the principle: for query patterns beyond the partition key, prefer denormalization (query tables) over 2i (except for narrow per-partition queries) -- reflecting Cassandra's query-first modeling. Understanding the denormalize-instead principle (query tables for access patterns, the idiomatic alternative to 2i) is understanding the Cassandra-idiomatic solution.
The architecture: every piece explained
Top row: the problem and index types. The problem: querying by non-key columns (Cassandra queries efficiently by partition key -- but querying other columns needs indexes). Secondary index (2i): the traditional index -- local, per-node (each node indexes its own data) -- efficient with the partition key, but scatter-gather without it. Scatter-gather: a query without the partition key hits all nodes (each checks its local index) -- expensive and unscalable (cost proportional to cluster size). SASI + SAI: richer index types -- SASI (SSTable-Attached Secondary Index -- more query capabilities) and SAI (Storage-Attached Index -- the modern, efficient default -- better performance and features) -- improvements over 2i.
Middle row: cardinality and alternatives. High cardinality: a column with many distinct values (e.g., email) -- the 2i index is selective but creates many tiny entries (inefficient for 2i). Low cardinality: a column with few distinct values (e.g., a status) -- the 2i index matches huge numbers of rows (inefficient -- little selectivity) -- so 2i is bad at both cardinality extremes. Denormalization: the idiomatic alternative -- a separate query table keyed for the query (so the query is an efficient partition-key lookup) -- avoiding the 2i issues. Materialized views: server-maintained query tables (Cassandra automatically maintaining a query table from the base table -- automating denormalization, with caveats).
Bottom rows: guidance and modern option. When 2i is OK: 2i is appropriate for narrow, per-partition queries (a query that includes the partition key plus the indexed column -- routed to one node, using its local index -- efficient) -- the case where 2i works well. SAI as modern default: SAI (Storage-Attached Index) is the modern index -- more efficient and capable than the old 2i (better storage, richer queries) -- the recommended index type on modern Cassandra (though the scatter-gather nature of global queries still applies -- SAI improves the index, not the fundamental distribution). The ops strip: index choice (choosing the index approach -- 2i/SAI for narrow per-partition queries, denormalization for global query patterns, SAI as the modern index -- matched to the query), cardinality (considering the column's cardinality -- 2i bad at both extremes -- part of the index-vs-denormalize decision), and query patterns (designing around the query patterns -- the query-first modeling -- query tables for the access patterns, indexes only where appropriate).
End-to-end flow
Trace 2i misuse and the denormalization fix. A table is partitioned by user_id, and the team needs to query users by email. They add a 2i on email. A query by email (without the user_id): since the 2i is local (per-node), and the query doesn't specify the partition (no user_id), Cassandra scatter-gathers across all nodes (each checks its local email index) -- expensive, and worse as the cluster grows (more nodes -- more scatter-gather). This is the misuse (a global query by a 2i column -- scatter-gather). The idiomatic fix: denormalize -- create a query table partitioned by email (users_by_email). Now a query by email is a normal, efficient partition-key lookup (routed to the one node holding that email's partition -- single-node, scalable). The team writes to both tables (the original users table and users_by_email -- denormalized, Cassandra's cheap writes making this acceptable). The denormalization (a query table keyed by email) replaced the 2i scatter-gather with an efficient partition-key lookup -- the idiomatic, scalable solution. The 2i misuse (global scatter-gather query) was fixed by denormalizing (a purpose-built query table).
The cardinality and when-2i-is-OK vignettes show the nuances. A cardinality case: the team considers a 2i on a status column (low cardinality -- 3 values). This would be inefficient (the index matching huge numbers of rows -- little selectivity -- a query by status returning a large fraction of the data, and the scatter-gather compounding it). They avoid the 2i (low-cardinality 2i is inefficient) -- using a query table or a different design. Conversely, a very-high-cardinality 2i (e.g., a unique-ish column) creates many tiny index entries (inefficient for 2i too) -- so 2i is bad at both extremes (part of the reason to prefer denormalization). A when-2i-is-OK case: the team has a query that includes the partition key plus a non-key column (e.g., 'within this user's data -- user_id specified -- find rows with a certain attribute'). Here a 2i (or SAI) is appropriate -- the query is routed to the one node (the partition key specified), which uses its local index to filter within that partition -- a single-node, efficient query. So 2i is OK for narrow, per-partition queries (with the partition key) -- the case where its local nature works well.
The SAI and query-pattern vignettes complete it. A SAI case: on modern Cassandra, the team uses SAI (Storage-Attached Index) instead of the old 2i -- SAI being more efficient and capable (better storage, richer queries -- e.g., range queries) -- the modern index default. (They still recognize that global queries -- without the partition key -- scatter-gather even with SAI -- so SAI improves the index but not the fundamental distribution -- denormalization still preferred for global query patterns.) A query-pattern case: the team designs their data model query-first (tables keyed for their queries -- query tables for the access patterns) -- using indexes (SAI) only for narrow per-partition queries, and denormalization (query tables) for global query patterns -- the query-first modeling avoiding 2i misuse. The consolidated discipline the team documents: understand 2i's local nature (efficient for per-partition queries -- with the partition key -- disastrous scatter-gather for global queries -- without), avoid 2i for global queries (scatter-gather across the cluster -- unscalable) and at cardinality extremes (bad at both high and low), prefer denormalization (query tables keyed for the access patterns -- the idiomatic, scalable solution -- Cassandra's cheap writes making it acceptable), consider materialized views (server-maintained query tables -- with caveats), use SAI as the modern index (more efficient than 2i) for narrow per-partition queries, and design the data model query-first (query tables for access patterns, indexes only where appropriate) -- because secondary indexes are a powerful but frequently-misused Cassandra feature (2i's local nature causing scatter-gather for global queries), and knowing when to use them (narrow per-partition queries, SAI) versus when to denormalize (global query patterns -- query tables) is essential to a performant, scalable Cassandra data model.