SAI Architecture: Index Components Attached to SSTables and Distributed Query Execution

Learning outcomes

AtlasMart's support query works, but operators now need to answer harder questions: where does the index live, when is a write considered indexed, what does compaction do to index files, how does an index build differ from normal writes, and what happens during streaming? SAI is easiest to operate when its lifecycle is understood as part of Cassandra storage rather than as a sidecar search cluster.

Core terms for this chapter

Apache Cassandra is a peer-to-peer distributed database. CQL is the Cassandra Query Language. A partition key determines a partition and is hashed into a token; token ownership and the keyspace replication strategy determine the replicas storing that partition. A request's coordinator is the Cassandra node that accepts that client request and coordinates work with replicas. A primary-key access path identifies partitions directly from the primary key and is Cassandra's most efficient lookup path. A secondary index provides an additional way to find rows by non-primary-key values. Storage-Attached Indexing (SAI) is Cassandra 5.0's storage-integrated secondary-index capability: it indexes Memtables in memory and attaches on-disk index components to SSTables. An SSTable is an immutable on-disk Cassandra storage file set; a Memtable is the mutable in-memory write buffer before flush. Selectivity describes how narrowly a predicate reduces candidate rows; a predicate matching nearly every row is low-selectivity. Fanout is the amount of token-range/replica work a distributed query must touch. Build state records whether an index is still being constructed and whether it is queryable. Streaming moves SSTable components between nodes during operations such as bootstrap, repair, rebuild, or decommission. SAI is a filtering engine, not a general relational join engine or full-text relevance/search platform.

1. SAI is attached to the Cassandra write and storage lifecycle

On a replica, SAI indexes the in-memory Memtable representation as writes arrive. When the Memtable flushes, SAI writes index components associated with the resulting SSTable. Current Cassandra documentation states that the SAI write path is synchronous: when a replica acknowledges a write, that replica's data is already represented in the SAI indexing path. This is very different from an asynchronously refreshed external search service. It does not, however, mean every replica has acknowledged—regular Cassandra CL still defines the request guarantee.

On disk, SAI shares some per-SSTable index structures across all SAI columns on the table and stores additional per-column components. For text, current SAI uses trie/postings structures; for numeric and other non-literal types it uses k-dimensional-tree-style structures. Application code should treat these as implementation/storage evidence, not stable file APIs.

2. Force an SSTable and inspect SAI metadata safely

# Verify the existing course cluster first.docker exec atlasmart-cass-1 nodetool versiondocker exec atlasmart-cass-1 nodetool statusdocker exec atlasmart-cass-1 java -version# If the shared course cluster does not exist, recreate the same local topology.docker network inspect atlasmart-cassandra >/dev/null 2>&1 || docker network create atlasmart-cassandradocker volume create atlasmart-cass-1-datadocker volume create atlasmart-cass-2-datadocker volume create atlasmart-cass-3-datadocker inspect atlasmart-cass-1 >/dev/null 2>&1 || docker run -d --name atlasmart-cass-1 --hostname atlasmart-cass-1 --network atlasmart-cassandra -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack1 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_NUM_TOKENS=16 -v atlasmart-cass-1-data:/var/lib/cassandra cassandra:5.0.9# Wait until node 1 is UN before starting peers.docker exec atlasmart-cass-1 nodetool statusdocker inspect atlasmart-cass-2 >/dev/null 2>&1 || docker run -d --name atlasmart-cass-2 --hostname atlasmart-cass-2 --network atlasmart-cassandra -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack2 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_NUM_TOKENS=16 -e CASSANDRA_SEEDS=atlasmart-cass-1 -v atlasmart-cass-2-data:/var/lib/cassandra cassandra:5.0.9docker inspect atlasmart-cass-3 >/dev/null 2>&1 || docker run -d --name atlasmart-cass-3 --hostname atlasmart-cass-3 --network atlasmart-cassandra -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack3 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_NUM_TOKENS=16 -e CASSANDRA_SEEDS=atlasmart-cass-1 -v atlasmart-cass-3-data:/var/lib/cassandra cassandra:5.0.9# Continue only when all three nodes are UN in dc1.docker exec atlasmart-cass-1 nodetool statusdocker exec -it atlasmart-cass-1 cqlsh

CREATE KEYSPACE IF NOT EXISTS atlasmart_saiWITH replication = {'class':'NetworkTopologyStrategy','dc1':3};CREATE TABLE IF NOT EXISTS atlasmart_sai.product_catalog (    product_id uuid PRIMARY KEY,    category text,    brand text,    status text,    price_cents int,    rating int,    warehouse_region text,    updated_at timestamp) WITH compaction = {'class':'UnifiedCompactionStrategy'};CREATE TABLE IF NOT EXISTS atlasmart_sai.products_by_category_bucket (    category text,    bucket tinyint,    product_id uuid,    brand text,    status text,    price_cents int,    rating int,    PRIMARY KEY ((category,bucket),product_id)) WITH compaction = {'class':'UnifiedCompactionStrategy'};CONSISTENCY LOCAL_QUORUM;

INSERT INTO atlasmart_sai.product_catalog (product_id,category,brand,status,price_cents,rating,warehouse_region,updated_at) VALUES (00000000-0000-0000-0000-000000001801,'laptop','Northstar','ACTIVE',129900,5,'eu-west',toTimestamp(now()));INSERT INTO atlasmart_sai.product_catalog (product_id,category,brand,status,price_cents,rating,warehouse_region,updated_at) VALUES (00000000-0000-0000-0000-000000001802,'laptop','Northstar','ACTIVE',89900,4,'eu-west',toTimestamp(now()));INSERT INTO atlasmart_sai.product_catalog (product_id,category,brand,status,price_cents,rating,warehouse_region,updated_at) VALUES (00000000-0000-0000-0000-000000001803,'laptop','Contoso','ACTIVE',149900,5,'us-east',toTimestamp(now()));INSERT INTO atlasmart_sai.product_catalog (product_id,category,brand,status,price_cents,rating,warehouse_region,updated_at) VALUES (00000000-0000-0000-0000-000000001804,'monitor','Northstar','ACTIVE',39900,4,'eu-west',toTimestamp(now()));INSERT INTO atlasmart_sai.product_catalog (product_id,category,brand,status,price_cents,rating,warehouse_region,updated_at) VALUES (00000000-0000-0000-0000-000000001805,'monitor','Fabrikam','DISCONTINUED',29900,3,'us-east',toTimestamp(now()));INSERT INTO atlasmart_sai.product_catalog (product_id,category,brand,status,price_cents,rating,warehouse_region,updated_at) VALUES (00000000-0000-0000-0000-000000001806,'keyboard','Fabrikam','ACTIVE',9900,4,'eu-west',toTimestamp(now()));INSERT INTO atlasmart_sai.product_catalog (product_id,category,brand,status,price_cents,rating,warehouse_region,updated_at) VALUES (00000000-0000-0000-0000-000000001807,'keyboard','Northstar','ACTIVE',15900,5,'us-east',toTimestamp(now()));INSERT INTO atlasmart_sai.product_catalog (product_id,category,brand,status,price_cents,rating,warehouse_region,updated_at) VALUES (00000000-0000-0000-0000-000000001808,'mouse','Contoso','ACTIVE',4900,4,'eu-west',toTimestamp(now()));INSERT INTO atlasmart_sai.products_by_category_bucket (category,bucket,product_id,brand,status,price_cents,rating) VALUES ('laptop',0,00000000-0000-0000-0000-000000001801,'Northstar','ACTIVE',129900,5);INSERT INTO atlasmart_sai.products_by_category_bucket (category,bucket,product_id,brand,status,price_cents,rating) VALUES ('laptop',0,00000000-0000-0000-0000-000000001802,'Northstar','ACTIVE',89900,4);INSERT INTO atlasmart_sai.products_by_category_bucket (category,bucket,product_id,brand,status,price_cents,rating) VALUES ('laptop',0,00000000-0000-0000-0000-000000001803,'Contoso','ACTIVE',149900,5);

CREATE INDEX IF NOT EXISTS product_brand_saiON atlasmart_sai.product_catalog (brand) USING 'sai';CREATE INDEX IF NOT EXISTS product_price_saiON atlasmart_sai.product_catalog (price_cents) USING 'sai';SELECT keyspace_name,index_name,column_name,indexed_sstable_count,       is_building,is_queryable,cell_count,per_column_disk_size,per_table_disk_sizeFROM system_views.indexesWHERE keyspace_name='atlasmart_sai';

docker exec atlasmart-cass-1 nodetool flush atlasmart_sai product_catalog# Virtual tables are local to each node; inspect more than one.for n in 1 2 3; do  echo "=== node $n ==="  docker exec atlasmart-cass-$n cqlsh -e "SELECT keyspace_name,index_name,indexed_sstable_count,is_building,is_queryable,per_column_disk_size,per_table_disk_size FROM system_views.indexes WHERE keyspace_name='atlasmart_sai';"done# Read-only inventory: filenames are implementation detail, so do not script production logic around them.docker exec atlasmart-cass-1 sh -lc "find /var/lib/cassandra/data/atlasmart_sai -maxdepth 4 -type f | sort | head -120"

After a flush, indexed_sstable_count and disk-size fields should become nonzero once relevant data exists. The exact byte counts, component names, SSTable format, and compaction timing depend on runtime state. The key proof is that SAI's disk accounting is attached to SSTable/index metadata on each node rather than a separate remote service.

3. Read path: union, intersection, then row materialization

For one indexed predicate, SAI merges matching token-ordered results from relevant Memtable and SSTable indexes. With multiple indexed predicates joined by AND, SAI intersects indexed result streams. The resulting partition keys still cause Cassandra partition reads; rows can then be post-filtered for row granularity, tombstones, or expressions not satisfied directly by index structures. This is why “an index exists” is not equivalent to “only matching rows are touched.”

TRACING ON;SELECT product_id,category,brand,price_centsFROM atlasmart_sai.product_catalogWHERE brand='Northstar' AND price_cents >= 5000 AND price_cents <= 130000LIMIT 20;TRACING OFF;

Current SAI tracing can report how many Memtable indexes, SSTable indexes, segments, partitions, and post-filtered rows were involved. Capture those numbers. They are far more useful than a screenshot of the final result set because they explain why two predicates with the same result count can have different cost.

4. Build/rebuild and streaming are operational states

Creating an index on an already-populated table triggers index construction for existing data. On a tiny lab this may finish almost instantly. To make the lifecycle observable without inventing a slow build, inspect system_views.indexes before/after creation and optionally use nodetool rebuild_index on one disposable node. During a rebuild, queries that require an index are only safe once the relevant local index is queryable. In a cluster, nodes gossip local index status so coordinators can avoid replicas whose required index is not queryable.

# Optional lab: rebuild one index on one disposable node.docker exec atlasmart-cass-1 nodetool rebuild_index atlasmart_sai product_catalog product_brand_sai# Observe local state after/during the rebuild. On a tiny dataset it may already be complete.docker exec atlasmart-cass-1 cqlsh -e "SELECT keyspace_name,index_name,is_building,is_queryable,indexed_sstable_count FROM system_views.indexes WHERE keyspace_name='atlasmart_sai' AND index_name='product_brand_sai';"docker exec atlasmart-cass-1 nodetool netstats

SAI is compatible with zero-copy streaming: when Cassandra can stream entire SSTables, their SAI components travel with them rather than requiring a separate external reindex on the destination. Whether a specific operation uses zero-copy depends on SSTable eligibility, encryption, compaction/storage state, and Cassandra configuration. Do not infer zero-copy from “streaming happened”; inspect the operation and current configuration.

5. Verification

• Both brand and price indexes are queryable on inspected nodes.
• After flush, you recorded indexed SSTable count and disk bytes.
• You captured a trace for an AND query and identified index/segment/post-filter evidence if present.
• You did not modify any SAI component files directly.
• You can distinguish index build from normal synchronous indexing of new writes.

Production judgment

Adopt SAI only after the primary-key model and business query have been written down. Record expected cardinality/selectivity, result limit, partitions/token ranges touched, replica/DC scope, RF/CL, p50/p95/p99 latency, index build state, SSTables/indexes touched per query, SAI disk bytes, base-table disk bytes, write amplification, compaction strategy/backlog, tombstones/TTL, streaming and repair behavior, node/index failure behavior, driver timeout/retry/idempotency policy, and whether authorization is enforced before or as part of retrieval. SAI adds synchronous write work and on-disk components; it does not make a low-selectivity cluster-wide predicate free. More SAI indexes also mean more components and more write/disk cost, so index count is a workload decision rather than a schema decoration.

For security, remember that index components may contain user-derived values and must be included in the data-at-rest threat model. For managed Cassandra services, verify exact SAI version, allowed index options, query guardrails, observability surfaces, backup/restore behavior, encryption, and billing model instead of assuming open-source defaults. Do not claim SAI provides cross-row relational constraints, global linearizability, arbitrary joins, relevance scoring, fuzzy full-text semantics, or authorization isolation. Lesson 3 now turns the architecture into concrete text/numeric indexes and deliberately probes supported and unsupported predicate boundaries.

Summary and next step

This lesson’s concepts, evidence path, failure boundaries, and production judgment should now be explicit enough to verify rather than assume. Re-run the check-your-understanding prompts and preserve any lab evidence you need before changing or cleaning up the environment.

Next, continue to Create SAI Indexes for Textual/Numeric Columns and Combine Multiple Predicates.

Authoritative references

These are the version-sensitive source of truth for this lesson. Re-check them when regenerating the course because SAI capabilities, guardrails, virtual tables, and query semantics can evolve.

• Apache Cassandra downloads / current GA baseline
• Indexing concepts: primary index, SAI, legacy 2i
• SAI concepts and distributed execution
• SAI read and write paths
• Working with SAI
• CREATE INDEX reference
• SAI monitoring and metrics
• SAI virtual tables
• SAI configuration and guardrails
• SAI FAQ and query-operator boundaries
• nodetool rebuild_index
• Cassandra streaming and zero-copy