Choose SAI vs Denormalized Tables vs External Search Based on Workload and Scale

Learning outcomes

AtlasMart now has three candidate architectures for product discovery: a query-specific Cassandra table, SAI on the source table, or an external search engine. The wrong decision is to choose by fashion—“indexes are easier,” “denormalize everything,” or “search engine for all filtering.” This lesson makes the choice from workload invariants, scale, consistency, relevance features, operational ownership, and measured cost.

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. Start from the business invariant, not the technology

Suppose AtlasMart has three workloads. The storefront category page needs predictable single-digit-millisecond service at high QPS and knows the category/bucket in advance. Support staff need selective combinations of brand, price, rating, and status with modest QPS and bounded LIMIT. Public product search needs typo tolerance, stemming, relevance ranking, facets, synonym handling, highlighted matches, and perhaps cross-domain documents. These are different products, so one storage path should not be forced to serve all three.

2. Compare the same AtlasMart requirement three ways

# 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);

-- Storefront: category+bucket is known before the read.CONSISTENCY LOCAL_QUORUM;SELECT product_id,brand,status,price_cents,ratingFROM atlasmart_sai.products_by_category_bucketWHERE category='laptop' AND bucket=0;

CREATE INDEX IF NOT EXISTS product_brand_sai ON atlasmart_sai.product_catalog (brand) USING 'sai';CREATE INDEX IF NOT EXISTS product_price_sai ON atlasmart_sai.product_catalog (price_cents) USING 'sai';CREATE INDEX IF NOT EXISTS product_rating_sai ON atlasmart_sai.product_catalog (rating) USING 'sai';TRACING ON;SELECT product_id,category,brand,price_cents,ratingFROM atlasmart_sai.product_catalogWHERE brand='Northstar' AND price_cents < 140000 AND rating >= 4LIMIT 20;TRACING OFF;

Option C—external search—is architectural in the mandatory lab. You do not need a paid service or another multi-gigabyte container stack to learn the decision boundary. Write the required feature contract: tokenizer/language handling, relevance metric, typo/fuzzy semantics, facets/aggregations, authorization filtering, freshness SLA, ingest/replay source, deletion propagation, backup/reindex time, and consistency behavior. If those capabilities are mandatory, SAI's filtering engine should not be sold as a drop-in replacement. A free local OpenSearch/Elasticsearch/Solr experiment can be added later, but it is not required to complete this Cassandra lesson.

3. Wrong comparison: only median latency on eight rows

A technology decision must compare representative data and operating cost. For the query table, count write fanout and reconciliation effort. For SAI, capture index disk bytes, build/queryable state, SSTable indexes hit, trace fanout, compaction/streaming behavior, and write cost. For external search, include ingest delay, duplicate infrastructure, shard/index design, reindex time, authorization model, network egress, backup, and on-call ownership. Median latency on a tiny warm dataset hides all of these.

DESCRIBE TABLE atlasmart_sai.product_catalog;SELECT index_name,column_name,cell_count,indexed_sstable_count,is_building,is_queryable,       per_column_disk_size,per_table_disk_sizeFROM system_views.indexesWHERE keyspace_name='atlasmart_sai';SELECT index_name,sstable_name,cell_count,per_column_disk_size,per_table_disk_size,start_token,end_tokenFROM system_views.sstable_indexesWHERE keyspace_name='atlasmart_sai';

docker exec atlasmart-cass-1 nodetool statusdocker exec atlasmart-cass-1 nodetool tablestats atlasmart_sai.product_catalogdocker exec atlasmart-cass-1 nodetool compactionstatsdocker exec atlasmart-cass-1 nodetool netstats

4. Migration and rollback must be designed before cutover

When changing SAI options, current documentation generally requires dropping/recreating the index, which means build state becomes part of the deployment. Non-indexed reads continue, but queries requiring that index must wait until it is queryable. This is why index rollout needs an application feature flag and status check rather than a blind schema migration.

5. Production decision record

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. Chapter 19 extends SAI into vector search. The next lesson begins with the fixed-dimension CQL vector type, embedding provenance, and similarity/schema constraints before any ANN query is trusted.

Summary and next bridge

SAI belongs between query-first Cassandra modeling and full external search—not above both. Use primary-key/query tables for deterministic locality, SAI for measured storage-attached filtering, and a search platform when product requirements exceed SAI's filtering contract. Chapter 19 now builds on SAI for vector approximate-nearest-neighbor search, where index quality must be judged by recall as well as latency.

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
• Vector search concepts