Create SAI Indexes for Textual / Numeric Columns and Combine Multiple Predicates

Learning outcomes

AtlasMart wants an operational catalog filter that combines brand, status, price band, and rating without maintaining a new table for every support-screen combination. This is a good SAI candidate only if the predicates are supported, the result sets are bounded, and the workload does not quietly become free-form relational search. This lesson tests the supported CQL surface instead of memorizing an index syntax.

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. Supported does not mean “arbitrary SQL WHERE clause”

Current Cassandra 5.0 SAI supports indexes on most CQL columns, including many primary-key, collection, static, text, numeric, temporal, and UUID-like types. Important restrictions remain: counter columns and non-frozen user-defined types are not SAI-indexable, and a table whose partition key is a single column does not need/allow an SAI index on that partition-key column. Numeric indexes support equality/ranges; text indexes support equality and collection-specific containment patterns. LIKE is not a supported SAI operator. Current SAI concepts also describe richer logical behavior such as IN/OR in some contexts; because operator support can evolve, this lab uses the conservative command-reference surface—equality, numeric range, collection containment when explicitly indexed, and AND—and asks you to re-check your target patch before relying on newer expressions.

2. Create a practical index set, not an index set for every column

# 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'WITH OPTIONS = {'case_sensitive':'false','normalize':'true','ascii':'true'};CREATE INDEX IF NOT EXISTS product_status_saiON atlasmart_sai.product_catalog (status) USING 'sai';CREATE INDEX IF NOT EXISTS product_price_saiON atlasmart_sai.product_catalog (price_cents) USING 'sai';CREATE INDEX IF NOT EXISTS product_rating_saiON atlasmart_sai.product_catalog (rating) USING 'sai';DESCRIBE TABLE atlasmart_sai.product_catalog;SELECT index_name,column_name,analyzer,is_building,is_queryable,       indexed_sstable_count,per_column_disk_size,per_table_disk_sizeFROM system_views.indexesWHERE keyspace_name='atlasmart_sai';

The brand index uses case/normalization options so equality comparisons can normalize text according to the configured analyzer. That still does not produce stemming, relevance ranking, fuzzy typo tolerance, synonym expansion, phrase search, or highlighting. If those are product requirements, evaluate an external search engine rather than layering many assumptions onto SAI.

3. Combine indexed predicates and inspect actual candidate work

CONSISTENCY LOCAL_QUORUM;TRACING ON;SELECT product_id,category,brand,status,price_cents,ratingFROM atlasmart_sai.product_catalogWHERE brand='northstar'  AND status='ACTIVE'  AND price_cents >= 10000  AND price_cents <= 140000LIMIT 20;TRACING OFF;SELECT product_id,category,brand,price_cents,ratingFROM atlasmart_sai.product_catalogWHERE rating >= 4 AND price_cents < 100000LIMIT 20;

Record the final row count and the trace's index/segment/partition/post-filter counts. Multiple indexed clauses are not free: current documentation notes that processing more indexed columns increases component work, and some additional expressions can be applied through post-filtering after initial indexed candidate generation. Therefore “four indexes narrowed the result to two rows” is incomplete without the trace and dataset cardinality.

4. Deliberately rejected queries teach the boundary

-- LIKE is not an SAI query operator in the current command reference.SELECT product_id,brand FROM atlasmart_sai.product_catalogWHERE brand LIKE 'North%';-- product_id is the single-column partition key: direct lookup is already the primary index.CREATE INDEX product_id_saiON atlasmart_sai.product_catalog (product_id) USING 'sai';-- Counter columns cannot be SAI-indexed. Use a disposable schema probe.CREATE TABLE IF NOT EXISTS atlasmart_sai.counter_probe (  k text PRIMARY KEY,  c counter);CREATE INDEX counter_probe_saiON atlasmart_sai.counter_probe (c) USING 'sai';

Do not “repair” an unsupported expression by adding ALLOW FILTERING until the data model and boundedness are understood. ALLOW FILTERING is permission for server filtering, not a feature that converts unsupported index semantics into a scalable query. The safer repairs are: direct primary-key routing, a query-specific denormalized table, a supported SAI predicate, or an external search system whose feature contract matches the product requirement.

5. Verification and cleanup

• Four intended indexes are queryable; rejected probe indexes were not created.
• The combined query returns the expected logical subset from the small fixture.
• You captured trace evidence rather than inferring selectivity from only the final row count.
• You can distinguish analyzer normalization from full-text relevance search.
• You did not use unsupported queries as evidence that SAI is broken; they are contract boundaries.

DROP TABLE IF EXISTS atlasmart_sai.counter_probe;SELECT index_name,column_name,is_queryableFROM system_views.indexesWHERE keyspace_name='atlasmart_sai';

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 4 scales the fixture and measures selectivity, per-index disk bytes, SSTable-index fanout, guardrails, write/read latency, and one-node failure behavior.

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 Index Selectivity, Range Queries, Disk/Write Cost, Query Limits, and Operational Monitoring.

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