Index Selectivity, Range Queries, Disk / Write Cost, Query Limits, and Operational Monitoring

Learning outcomes

AtlasMart's small demo queries all look fast. That proves almost nothing about production. A low-selectivity status filter, many SSTables, a large cluster, and extra indexes can turn a simple CQL predicate into large distributed work. This lesson creates a larger deterministic fixture and records the signals that should appear in a real SAI capacity review.

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. Build a deterministic cardinality/selectivity fixture

The following PowerShell generates 2,000 products. status='ACTIVE' matches roughly 90% (deliberately low selectivity), one brand matches 20%, and price/rating predicates narrow further. The dataset is intentionally modest enough for a laptop, but large enough to produce real SSTables and nonzero index disk accounting after flush. Do not call it a performance benchmark of Cassandra itself.

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

$csv = Join-Path $PWD 'atlasmart-sai-products.csv'"product_id,category,brand,status,price_cents,rating,warehouse_region,updated_at" | Set-Content $csv$brands = @('Northstar','Contoso','Fabrikam','AdventureWorks','WideWorld')$cats = @('laptop','monitor','keyboard','mouse','storage')for ($i=1; $i -le 2000; $i++) {  $id = "00000000-0000-0000-0001-{0:D12}" -f $i  $category = $cats[$i % $cats.Count]  $brand = $brands[$i % $brands.Count]  $status = if (($i % 10) -eq 0) {'DISCONTINUED'} else {'ACTIVE'}  $price = 1000 + (($i * 137) % 200000)  $rating = 1 + ($i % 5)  $region = if (($i % 2) -eq 0) {'eu-west'} else {'us-east'}  "$id,$category,$brand,$status,$price,$rating,$region,2026-09-08T06:00:00Z" | Add-Content $csv}docker cp $csv atlasmart-cass-1:/tmp/atlasmart-sai-products.csvdocker exec atlasmart-cass-1 cqlsh -e "COPY atlasmart_sai.product_catalog (product_id,category,brand,status,price_cents,rating,warehouse_region,updated_at) FROM '/tmp/atlasmart-sai-products.csv' WITH HEADER=TRUE;"Remove-Item $csv

CREATE INDEX IF NOT EXISTS product_brand_sai ON atlasmart_sai.product_catalog (brand) USING 'sai';CREATE INDEX IF NOT EXISTS product_status_sai ON atlasmart_sai.product_catalog (status) 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';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';

2. Measure selectivity and SSTable-index fanout

for n in 1 2 3; do docker exec atlasmart-cass-$n nodetool flush atlasmart_sai product_catalog; donedocker exec atlasmart-cass-1 cqlsh -e "SELECT index_name,column_name,cell_count,indexed_sstable_count,per_column_disk_size,per_table_disk_size FROM system_views.indexes WHERE keyspace_name='atlasmart_sai';"docker exec atlasmart-cass-1 cqlsh -e "SELECT keyspace_name,index_name,sstable_name,cell_count,per_column_disk_size,per_table_disk_size FROM system_views.sstable_indexes WHERE keyspace_name='atlasmart_sai';"

TRACING ON;-- Deliberately low-selectivity: most rows are ACTIVE.SELECT product_id,brand,status FROM atlasmart_sai.product_catalogWHERE status='ACTIVE' LIMIT 100;TRACING OFF;TRACING ON;-- More selective intersection.SELECT product_id,brand,status,price_cents,ratingFROM atlasmart_sai.product_catalogWHERE brand='Northstar'  AND price_cents >= 120000  AND rating >= 4LIMIT 50;TRACING OFF;

For each trace, record requested LIMIT, returned rows, Memtable/SSTable indexes accessed, segments, partitions, and post-filtered rows when shown. A LIMIT can stop later rounds after enough rows are found, but it does not guarantee the first ranges searched contain enough matches. Data distribution changes fanout. This is why production testing needs representative cardinality and skew rather than only result count.

3. Before/after latency: measure, but do not overclaim

A proper p95/p99 benchmark should use a persistent driver session, warmup, controlled concurrency, a fixed data snapshot, and separate tables/workloads for writes with and without indexes. Spawning cqlsh for every request mostly measures process startup. The following PowerShell smoke harness is therefore only for validating the measurement workflow. For production decisions, repeat the same protocol with Java Driver 4.19.3 or your production driver and report distributions.

$samples = @()1..40 | ForEach-Object {  $sw = [System.Diagnostics.Stopwatch]::StartNew()  docker exec atlasmart-cass-1 cqlsh -e "CONSISTENCY LOCAL_QUORUM; SELECT product_id FROM atlasmart_sai.product_catalog WHERE brand='Northstar' AND price_cents >= 120000 LIMIT 20;" | Out-Null  $sw.Stop(); $samples += $sw.ElapsedMilliseconds}$ordered = $samples | Sort-Object$p50 = $ordered[[math]::Floor(($ordered.Count-1)*0.50)]$p95 = $ordered[[math]::Floor(($ordered.Count-1)*0.95)]$p99 = $ordered[[math]::Floor(($ordered.Count-1)*0.99)]"cqlsh smoke only: p50=$p50 ms p95=$p95 ms p99=$p99 ms"

For write cost, create an identical no-index control table, use one prepared INSERT shape and a persistent driver session, then run the same row set/concurrency against both tables. Report p50/p95/p99, error/retry counts, CPU, compaction state, and bytes written. Because SAI indexing is synchronous, an indexed table should be expected to consume additional write CPU/disk work; the magnitude is workload-dependent and must be measured rather than copied from a benchmark article.

CREATE TABLE IF NOT EXISTS atlasmart_sai.product_catalog_no_index (    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'};-- Use the same prepared INSERT payload/concurrency against product_catalog_no_index-- and product_catalog. Do not batch unrelated partitions merely to speed the test.

4. Guardrails and failure behavior are part of capacity

Current Cassandra configuration includes SAI-specific limits such as an index-count failure threshold and SSTable-indexes-per-query warning/failure thresholds. The exact defaults/settings are version-sensitive; inspect the active configuration before changing anything. A warning such as “too many SSTable indexes touched” is evidence of read amplification, not a request to blindly raise the threshold.

docker exec atlasmart-cass-1 sh -lc "grep -n -E 'sai_.*(threshold|indexes)' /etc/cassandra/cassandra.yaml || true"docker exec atlasmart-cass-1 nodetool tablestats atlasmart_sai.product_catalogdocker exec atlasmart-cass-1 nodetool compactionstats

docker pause atlasmart-cass-3# From a live cqlsh session, run the SAI query at LOCAL_QUORUM and then ALL.# LOCAL_QUORUM can usually continue with two RF=3 replicas; ALL cannot.docker exec atlasmart-cass-1 cqlsh -e "CONSISTENCY LOCAL_QUORUM; SELECT product_id FROM atlasmart_sai.product_catalog WHERE brand='Northstar' LIMIT 10;"docker exec atlasmart-cass-1 cqlsh -e "CONSISTENCY ALL; SELECT product_id FROM atlasmart_sai.product_catalog WHERE brand='Northstar' LIMIT 10;" || truedocker unpause atlasmart-cass-3docker exec atlasmart-cass-1 nodetool status

5. Acceptance checklist

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 5 turns all of this evidence into a workload decision: SAI versus a denormalized Cassandra query table versus a dedicated external search platform.

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 Choose SAI vs Denormalized Tables vs External Search Based on Workload and Scale.

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