Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 30 ยท Cassandra in Practice

Secondary indexes, SAI and materialized views

Why Cassandra secondary indexes are risky, what Storage-Attached Indexes change, why materialized views are experimental, and when to denormalize instead.

In Cassandra a query must name the partition it wants. That rule is what makes reads fast, and it is also what frustrates newcomers who ask: "How do I find users by email when the table is keyed by user id?" Cassandra offers three answers: a secondary index, a materialized view, or a second table that you maintain yourself. They look similar in a tutorial and behave very differently under load.

All CQL here is read-only; the real database cannot run on this site. The JavaScript stand-ins count how many nodes a query has to touch, which is the number that decides whether a lookup scales. They are models, not Cassandra's code.

The query that is not allowed

SQL
CREATE TABLE app.users (
  user_id  uuid PRIMARY KEY,
  email    text,
  country  text,
  age      int
);

SELECT * FROM app.users WHERE email = '[email protected]';
-- InvalidRequest: Cannot execute this query as it might involve data
-- filtering and thus may have unpredictable performance.

Cassandra refuses because email is not part of the primary key. It does not know which partition holds the row, so it would have to ask every node. You can force it with ALLOW FILTERING:

SQL
SELECT * FROM app.users WHERE email = '[email protected]' ALLOW FILTERING;

ALLOW FILTERING means "scan and discard". It is acceptable on a tiny table or inside one known partition, and a production incident on a large table. If a query needs it, the table is usually shaped wrong for that question.

Classic secondary indexes (2i)

A secondary index lets you filter on a non-key column:

SQL
CREATE INDEX users_by_email ON app.users (email);
SELECT * FROM app.users WHERE email = '[email protected]';

The key design fact is that the index is local. Each node indexes only the data it stores. A query that does not name the partition key therefore goes to many nodes, each of which looks in its own local index, and the coordinator merges the answers. This is called scatter-gather.

flowchart TD
    Q["SELECT ... WHERE email = ?"] --> Co["Coordinator"]
    Co --> A["Node A: local index"]
    Co --> B["Node B: local index"]
    Co --> C["Node C: local index"]
    Co --> D["Node D: local index"]

A query without the partition key fans out to nodes; adding nodes does not make that single query cheaper.

Compare that with a primary-key lookup, which asks only the replicas that own the partition.

JS Nodes touched: partition lookup vs index scatter-gather

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The key lookup stays at three nodes however large the cluster grows. The index lookup grows with the cluster, so it gets slower in total work as you scale out. Classic indexes also behave badly on two kinds of column:

  • High cardinality (a unique email per row): a scan over the whole cluster to find one row.
  • Very low cardinality (a boolean flag, or a column where most rows share one value): the index entry for that value becomes a huge partition.

Frequently updated or deleted columns are a third problem, because every change writes index entries and tombstones. The rule of thumb for 2i is: use it only together with a partition key, on a column of moderate cardinality, or not at all.

Storage-Attached Indexes (SAI)

Newer Cassandra versions (5.0 and later) include Storage-Attached Indexes, a redesigned secondary index. An SAI index is stored alongside each SSTable, so it follows flushes and compactions instead of living in a separate hidden table:

SQL
CREATE INDEX users_email_sai ON app.users (email) USING 'sai';
CREATE INDEX users_age_sai   ON app.users (age)   USING 'sai';

SELECT * FROM app.users WHERE age >= 30 AND age < 40;

Compared with classic 2i, SAI is more efficient on disk, supports range queries on numbers and several conditions combined, and in recent versions adds text analysis and vector search. It is still a local, per-node index, though, so the scatter-gather shape in the diagram above is unchanged. SAI makes a necessary index much better; it does not turn Cassandra into a relational database. Syntax and features differ between Cassandra versions and between DataStax products, so check the documentation of the version you run.

Materialized views

A materialized view is a table that Cassandra maintains for you from a base table, with a different primary key:

SQL
CREATE MATERIALIZED VIEW app.users_by_email AS
  SELECT * FROM app.users
  WHERE  email IS NOT NULL AND user_id IS NOT NULL
  PRIMARY KEY (email, user_id);

Writing to app.users now also updates the view, and reading by email is a normal partition lookup in the view. That sounds ideal, and it is the reason views exist, but they carry a warning that you should take seriously: in current Cassandra releases materialized views are still labelled experimental, they have had consistency bugs, repairing them is awkward, and they must be explicitly enabled in recent versions (materialized_views_enabled). Many teams therefore avoid them in production.

The reliable answer: denormalize

The approach Cassandra is designed around is a second table that your application writes next to the first one:

SQL
CREATE TABLE app.users_by_email (
  email    text PRIMARY KEY,
  user_id  uuid
);

BEGIN BATCH
  INSERT INTO app.users       (user_id, email, country) VALUES (?, ?, ?);
  INSERT INTO app.users_by_email (email, user_id)       VALUES (?, ?);
APPLY BATCH;

A logged batch (see Lightweight transactions, batches and counters) keeps the two tables in step. This is exactly the "one table per query" rule from Query-first data modeling.

JS A view versus a hand-maintained table: the stale-row trap

Press Run (or Ctrl + Enter) to see the console output here.

No output.

This is the hidden cost of denormalizing: when a key column changes, you must delete the old lookup row yourself (a materialized view does this for you, which is its attraction). In real Cassandra you would read the old email first, then delete and insert in one batch.

Choosing

Need Prefer
Lookup by a column, on a big table, high traffic A second table keyed by that column
Filter inside one known partition Clustering columns, or SAI/2i scoped by partition key
Range or multi-column filter, moderate traffic SAI (5.0+)
Automatic maintenance, and you accept the risks Materialized view (experimental)

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does a classic secondary index query without the partition key scale poorly?

    Secondary indexes are local to each node, so a query that does not name a partition fans out across the cluster.
  2. 2. What does ALLOW FILTERING tell Cassandra?

    It overrides the safety check. On large tables it can read huge amounts of data to return a few rows.
  3. 3. What is the main advantage of SAI over a classic secondary index?

    SAI is a better local index. It is still per node, so it improves efficiency and flexibility but not the scatter-gather shape.
  4. 4. Which approach do most Cassandra teams trust for 'find by another column' on a large table?

    A query table gives a single-partition lookup. You pay with extra writes and the job of removing stale rows when a key changes.

Summary

  • Cassandra rejects queries on non-key columns because it would have to ask every node; ALLOW FILTERING only hides that cost.
  • Classic secondary indexes are local per node, so queries without the partition key are scatter-gather. Avoid them on high- or very low-cardinality columns.
  • SAI (Cassandra 5.0+) is a more capable local index with range queries, but it is not a substitute for query-first design.
  • Materialized views automate the second table but are marked experimental and need care.
  • Denormalized tables are the dependable choice; update them together and delete stale rows when keys change.
  • The stand-ins only count touched nodes and show the stale-row trap; the CQL was not run here.

Next: Multi-datacenter setups and security.