Primary-Key Access vs Secondary Indexes: Why Partition Modeling Still Comes First

Learning outcomes

AtlasMart's storefront already serves the high-QPS route “show laptops in category X” from a bounded query table, but support engineers also need occasional filters such as “active Northstar products” or “items in a price band.” Replacing every query table with secondary indexes would make the schema look convenient while hiding distributed fanout and tail-latency cost. This lesson starts with the decision boundary: use the partition key when the application knows the partition; use SAI when a measured filtering use case justifies a distributed secondary path.

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. Primary-key lookup and secondary discovery solve different problems

A query such as WHERE category='laptop' AND bucket=0 against products_by_category_bucket names one Cassandra partition directly. The driver/coordinator can map that partition key to a token and its replicas. By contrast, WHERE brand='Northstar' against a table whose partition key is product_id does not identify one partition. SAI supplies a secondary filtering structure on each relevant node/SSTable, and the coordinator performs a distributed range-style search to discover matching partition keys before materializing rows.

The fact that SAI is integrated with Cassandra storage does not remove the distributed nature of the query. Selectivity matters: a predicate matching 5 rows is a different workload from a predicate matching 80% of a billion-row table, even when both have an index.

2. Build the smallest observable SAI example

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

-- Direct primary-key read: one known partition.TRACING ON;SELECT product_id,brand,status,price_centsFROM atlasmart_sai.products_by_category_bucketWHERE category='laptop' AND bucket=0;TRACING OFF;-- Create SAI on an already-populated non-primary-key column.CREATE INDEX IF NOT EXISTS product_brand_saiON atlasmart_sai.product_catalog (brand) USING 'sai';DESCRIBE TABLE atlasmart_sai.product_catalog;SELECT keyspace_name,index_name,column_name,indexed_sstable_count,       is_building,is_queryable,per_column_disk_size,per_table_disk_sizeFROM system_views.indexesWHERE keyspace_name='atlasmart_sai' AND index_name='product_brand_sai';-- Once is_queryable=true, trace the secondary-index query.TRACING ON;SELECT product_id,category,brand,status,price_centsFROM atlasmart_sai.product_catalogWHERE brand='Northstar' LIMIT 20;TRACING OFF;

On this tiny fixture, index construction may finish before you can observe is_building=true. That does not invalidate the mechanism. The stable evidence is that the schema contains an SAI index, the local virtual-table row eventually reports is_queryable=true, and the traced indexed query reports SAI/index work rather than a direct single-partition lookup. Virtual tables are local-node views, so repeat the status query on multiple nodes when diagnosing a cluster.

3. Boundary case: availability is still CL/topology behavior

SAI does not create a separate globally consistent search database. Each replica maintains index state alongside its Cassandra data; the query still obeys Cassandra replication, consistency, failure detection, and replica eligibility. Pause one disposable replica and compare the same query at LOCAL_QUORUM and ALL. With RF=3 in one DC, LOCAL_QUORUM needs two replicas and can usually continue with one node unavailable; ALL cannot. Exact error text and range scheduling are runtime-dependent.

docker pause atlasmart-cass-3docker exec atlasmart-cass-1 nodetool status# Run the CQL block below from a live node, then restore node 3.# Do not change host firewall rules or system clocks.docker unpause atlasmart-cass-3docker exec atlasmart-cass-1 nodetool status

CONSISTENCY LOCAL_QUORUM;SELECT product_id,category,brand FROM atlasmart_sai.product_catalogWHERE brand='Northstar' LIMIT 20;CONSISTENCY ALL;SELECT product_id,category,brand FROM atlasmart_sai.product_catalogWHERE brand='Northstar' LIMIT 20;CONSISTENCY LOCAL_QUORUM;

4. Verification and reset

• All three nodes return UN after the failure drill.
• product_brand_sai is queryable on the nodes inspected.
• You captured separate traces for a direct partition-key query and an SAI query.
• You recorded the SAI result limit and result cardinality rather than describing the index as “fast.”
• You can state that successful LOCAL_QUORUM SAI results do not imply ALL replicas were available or globally linearized.

CONSISTENCY LOCAL_QUORUM;SELECT keyspace_name,index_name,is_building,is_queryableFROM system_views.indexesWHERE keyspace_name='atlasmart_sai' AND index_name='product_brand_sai';-- Keep atlasmart_sai for Lessons 2–5.

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 2 opens the SAI storage path: Memtable indexes, per-SSTable shared/per-column components, synchronous indexing, build state, distributed range execution, and zero-copy streaming.

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 SAI Architecture: Index Components Attached to SSTables and Distributed Query Execution.

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