TopolearnSAI makes secondary access patterns practical, but only if you understand how index queries execute across the cluster.
Cassandra 5.0's Storage-Attached Indexing (SAI) indexes regular columns, clustering columns, collections and vectors. Each index is built alongside each SSTable and memtable on every node, so it shares the data's lifecycle and is much more efficient than the legacy secondary indexes.
Create one with CREATE INDEX ON t (col) USING 'sai';. Then you can filter on that column: equality, ranges on numeric and date types, CONTAINS on collections, and combinations of several indexed columns with AND. Text indexes can be made case-insensitive with options like 'case_sensitive': false.
How a query executes matters:
- with the partition key restricted, an SAI query reads one partition: fast and cheap,
- without it, the coordinator must ask nodes across the whole token range, so latency grows with cluster size. Fine for occasional or admin queries with selective predicates and a
LIMIT, not for the hottest request path.
For vector search, index a vector<float, n> column and query with ORDER BY embedding ANN OF [ ... ] LIMIT k, optionally with other SAI filters.
Indexes cost write work and disk space on every node. If a non-key access pattern is central and high-volume, a dedicated denormalized table is still the most predictable design; SAI fills the gaps for additional, less frequent queries.
Example
CREATE INDEX IF NOT EXISTS orders_status ON shop.orders_by_user (status) USING 'sai';
CREATE INDEX IF NOT EXISTS orders_total ON shop.orders_by_user (total) USING 'sai';
-- cheap: partition key + index
SELECT order_id, total FROM shop.orders_by_user
WHERE user_id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 AND status = 'open';
-- cluster-wide: fine for selective, occasional queries with a LIMIT
SELECT user_id, order_id FROM shop.orders_by_user
WHERE status = 'open' AND total > 500 LIMIT 50;
-- vector similarity search (5.0)
CREATE TABLE shop.docs (id int PRIMARY KEY, body text, embedding vector<float, 3>);
CREATE INDEX ON shop.docs (embedding) USING 'sai';
SELECT id, body FROM shop.docs ORDER BY embedding ANN OF [0.1, 0.8, 0.3] LIMIT 3;Remember
- SAI (5.0) indexes columns per SSTable and memtable on each node
- Combine indexed columns with AND, ranges and CONTAINS
- Restrict the partition key when you can; otherwise the query fans out
- Vector search: ORDER BY col ANN OF [...] LIMIT k
Watch out for
- Putting a cluster-wide SAI query without partition key on the hottest request path.