Why architecture matters here
The architecture matters because the alternative — denormalizing into a second table per query pattern — is powerful but expensive and rigid, and SAI changes the trade-off. The Cassandra-idiomatic way to query by a non-key column is to maintain a whole separate table keyed by that column, written to on every base-table write. This works and scales, but it multiplies write amplification, storage, and application complexity for every new access pattern, and it forces you to know all your query patterns up front. SAI lets you add an index to an existing column in place, at a fraction of the storage cost of a duplicate table, and query it without maintaining a parallel write path in the application. For access patterns that are secondary — occasional lookups, admin queries, filters on top of a partition-scoped query — this is a far better fit than denormalizing everything.
The second forcing function is that SAI had to fix the specific failures of the older secondary indexes to be trustworthy. The legacy implementations stored an index per column with heavy on-disk overhead, coupled poorly to compaction, and performed badly on high-cardinality columns and range queries, which is why experienced operators learned to avoid them. SAI's storage-attached design directly targets those problems: by sharing a single index file structure across multiple indexed columns of a table and by tying the index lifecycle to the SSTable lifecycle, it slashes the disk footprint and keeps the index consistent with the data through compaction automatically. The result is an index you can actually reach for by default rather than one hedged with warnings, which is the whole point of shipping a new engine.
The third reason is that SAI is a local, distributed index, and understanding that shapes every query decision. Each replica builds indexes only over the SSTables it stores, so there is no global index that can point a query straight at the nodes holding matches. A query that does not restrict the partition key must therefore be sent to enough replicas to cover the whole token range — potentially every node — each of which scans its local indexes and returns matches, which the coordinator merges. This is fine when the predicate is selective (few matches, cheap merge) or when the query also restricts the partition or token range (limited fan-out), and it is a scalability trap when the query is a broad filter across the whole cluster. The architecture makes secondary indexing possible; the query shape determines whether it is cheap.
The fourth architectural payoff is that SAI unifies several kinds of indexing under one engine, including numeric range queries and vector search. The same storage-attached mechanism that indexes a text column for equality can index a numeric column with an ordered structure that supports range predicates efficiently, and, in modern Cassandra, index a vector column so that approximate-nearest-neighbor similarity search runs against embeddings stored in the table. This consolidation matters because it means one index type, one operational model, and one set of tuning knobs cover equality, range, and similarity, rather than a zoo of special-purpose indexes. For teams adding AI features — storing embeddings next to their operational data and querying by similarity — SAI is what makes Cassandra a viable vector store without a separate system.
A fifth consideration is the write-path cost, which SAI keeps low by piggybacking on work Cassandra already does. Because the index is built at flush time — when the memtable is written to a new immutable SSTable — and merged at compaction time, the indexing work rides along with I/O the storage engine was going to perform anyway, rather than imposing a separate synchronous index update on every write. The live write path stays fast: a write lands in the memtable and commit log as usual, and the indexing happens asynchronously when that data is flushed. The trade-off is index build lag — freshly written rows are indexed only once they flush, so there is a brief window where a row exists but is not yet visible to an index scan of on-disk data (though it is still visible via the memtable). This lazy, storage-attached building is what keeps SAI's write overhead modest compared to an index that must be updated synchronously on every mutation.
The architecture: every piece explained
Top row: how the index comes into being. A write follows the normal write path — into the memtable and commit log — with no synchronous index update. When the memtable fills, the SSTable flush writes an immutable data file, and it is here that the SAI index build happens: for each indexed column, Cassandra writes per-SSTable index components alongside the data file. Those index components differ by type — a term dictionary plus postings lists for text columns (which value appears in which rows), and ordered numeric structures for numbers so range predicates can be answered without scanning every value. The index is thus a sidecar to each SSTable, created at the same moment and sharing its immutability.
Middle row: how a query uses the index. A query coordinator receives a filter on an indexed column and, because SAI is a local index, fans the query out to the replicas covering the needed token range. On each replica, a local index scan consults the per-SSTable index components to find the row keys matching the predicate within each of that replica's SSTables. The coordinator then performs merge + primary read: it combines the matching keys across SSTables and replicas, resolves them to full rows by reading the base data, and returns the result. Meanwhile compaction — the background process that merges SSTables — merges their attached indexes alongside the data, so the index count shrinks as data files are consolidated and stays consistent with the base rows.
Bottom row: the constraints you operate against. Restricted query shape is the standing rule: SAI answers filters efficiently only when the predicate is selective or the query also bounds the partition/token range, because an unselective full-cluster filter still has to touch many nodes. Fan-out cost is the concrete hazard — a query with no partition restriction hits every replica set, so its cost scales with cluster size, not with the number of matches. The ops strip names the health signals: the size the indexes add on disk, the build lag between write and index visibility, the query fan-out (how many nodes a typical indexed query touches), the false-positive rate of any probabilistic filtering, and the resulting read latency.
End-to-end flow
Follow a user table partitioned by user id that also needs lookup by email. An SAI index is created on the email column. A write inserting a new user lands in the memtable and commit log and returns immediately — no index work yet. Later the memtable flushes to an SSTable, and at that moment SAI writes the email index component for that SSTable: a term-to-row-keys mapping that says which row keys in this file have which email. The new user is now indexed on disk. Between the write and the flush the row was queryable through the memtable but not yet present in any on-disk index — the brief build-lag window that lazy indexing implies.
Now a query arrives: find the user with a given email. Because the query filters on email and does not name a partition key, the coordinator must fan out across the token ring. It sends the predicate to the replicas covering the ring; each replica runs a local index scan, consulting the email index component of each of its SSTables to find matching row keys. Because email is highly selective — one user per email — each replica returns at most a handful of keys. The coordinator merges these, resolves the winning key to the full row by reading the base data, and returns the user. The fan-out touched many nodes, but each did trivial work and returned almost nothing, so the query is fast. This is the good case: an unselective-partition query saved by a highly selective predicate.
Contrast the bad case. Someone runs a query filtering on a low-cardinality column — say status = 'active', where a large fraction of all users match — with no partition restriction. The coordinator still fans out to the whole ring, but now every replica's local index scan returns a huge number of matching keys, and the coordinator must merge and read full rows for a large fraction of the entire dataset scattered across every node. The query is slow, expensive, and scales with cluster size; SAI made it possible to express, but it is exactly the shape SAI cannot make cheap. The right design would restrict the partition (status within a known user segment) or accept that a full-table analytical filter belongs in an analytics system, not an online index query.
Finally, watch compaction keep the index healthy. Over time the table accumulates many SSTables, each with its own attached email index, and a query must consult all of them per replica — more files, more scans. Compaction merges SSTables into fewer, larger files, and crucially it merges their attached SAI indexes at the same time, so the number of per-SSTable indexes a query must scan falls as compaction proceeds, and obsolete entries (from overwritten or deleted rows) are dropped. The index thus stays compact and consistent with the base data through the same background process that maintains the data itself — no separate index maintenance, no drift between index and rows. The operational cycle is: write lands, flush builds the index, queries fan out and scan locally, and compaction keeps the whole thing consolidated.