How to Use Storage-Attached Indexes (SAI) for Faster Queries in Apache Cassandra

Storage-Attached Indexes (SAI) store index data directly alongside SSTable files, enabling high-performance secondary indexing for equality, range, full-text, and vector similarity queries without the overhead of traditional index tables.

Storage-Attached Indexing (SAI) is Apache Cassandra’s modern secondary-index framework designed to accelerate queries on non-primary-key columns. Unlike legacy secondary indexes that maintain separate tables, SAI writes per-column index files during SSTable flush and compaction operations, eliminating network hops and reducing read amplification. This guide demonstrates how to implement SAI based on the actual source code implementation in the apache/cassandra repository.

Understanding Storage-Attached Index Architecture

SAI represents a fundamental shift in how Cassandra handles secondary indexing. Rather than storing index entries in separate "index tables," SAI maintains on-disk co-location with SSTable files. In src/java/org/apache/cassandra/index/sai/disk/IndexDescriptor.java, the system tracks per-column index files that are written during flush and compaction operations.

This architecture delivers several performance benefits:

• Column-aware storage formats: Text and byte columns utilize trie structures implemented in src/java/org/apache/cassandra/index/sai/disk/v1/trie/TrieTermsDictionaryWriter.java, while numeric vectors use specialized vector postings managed by src/java/org/apache/cassandra/index/sai/disk/v1/vector/VectorPostingsWriter.java.

• Pluggable text analysis: The src/java/org/apache/cassandra/index/sai/analyzer/NonTokenizingAnalyzer.java provides configurable filter pipelines for case sensitivity, Unicode normalization, and ASCII conversion.

• Live query routing: The src/java/org/apache/cassandra/index/sai/view/IndexViewManager.java builds in-memory views of on-disk indexes, allowing the coordinator to evaluate predicates without loading full SSTables into memory.

During write operations, the StorageAttachedIndexBuilder (located in src/java/org/apache/cassandra/index/sai/StorageAttachedIndexBuilder.java) constructs index entries, while StorageAttachedIndexWriter (in src/java/org/apache/cassandra/index/sai/disk/StorageAttachedIndexWriter.java) handles the physical serialization using codec utilities from src/java/org/apache/cassandra/index/sai/disk/v1/SAICodecUtils.java.

Creating Storage-Attached Indexes

To implement SAI in your Cassandra cluster, use the CREATE CUSTOM INDEX syntax with the 'sai' identifier.

Step 1: Create the Keyspace and Table

Begin with a standard CQL keyspace and table definition:

CREATE KEYSPACE cycling WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};

CREATE TABLE cycling.cyclist_semi_pro (
    id uuid PRIMARY KEY,
    name text,
    age int,
    country text,
    comment_vector vector<float, 128>
);

Step 2: Define SAI Indexes

Create indexes using the USING 'sai' clause. You can customize text indexes with analyzer options and vector indexes with similarity functions:

CREATE CUSTOM INDEX IF NOT EXISTS name_sai_idx
  ON cycling.cyclist_semi_pro (name)
  USING 'sai'
  WITH OPTIONS = {
    'case_sensitive' : false,
    'normalize' : true
  };

CREATE CUSTOM INDEX IF NOT EXISTS age_sai_idx
  ON cycling.cyclist_semi_pro (age)
  USING 'sai';

CREATE CUSTOM INDEX IF NOT EXISTS comment_vector_sai_idx
  ON cycling.cyclist_semi_pro (comment_vector)
  USING 'sai'
  WITH OPTIONS = { 'similarity_function' : 'DOT_PRODUCT' };

The StorageAttachedIndex class (in src/java/org/apache/cassandra/index/sai/StorageAttachedIndex.java) processes these definitions and manages the index lifecycle.

Step 3: Load Data

Insert data normally; SAI indexes are materialized automatically during SSTable writes:

INSERT INTO cycling.cyclist_semi_pro (id, name, age, country, comment_vector)
VALUES (uuid(), 'Chris Froome', 31, 'GB', [0.12, 0.34, 0.56, 0.78]);

Querying with Storage-Attached Indexes

Once indexes are built, the QueryContext class (in src/java/org/apache/cassandra/index/sai/QueryContext.java) helps the coordinator route queries efficiently.

Equality and Range Queries

Scalar columns support standard comparison operators:

SELECT * FROM cycling.cyclist_semi_pro WHERE age <= 23;

Full-Text Search

Text indexes support case-insensitive containment searches using the analyzer configuration:

SELECT * FROM cycling.cyclist_semi_pro WHERE name CONTAINS 'froome';

Vector Similarity Search

For vector columns, use Approximate Nearest Neighbor (ANN) queries with the similarity function defined during index creation:

SELECT id, name FROM cycling.cyclist_semi_pro
ORDER BY comment_vector ANN OF [0.10, 0.20, 0.30, 0.40] LIMIT 5;

This leverages the vector postings structure in src/java/org/apache/cassandra/index/sai/disk/v1/vector/VectorPostings.java to perform k-NN searches efficiently.

Index Maintenance

Remove indexes when no longer needed:

DROP INDEX IF EXISTS cycling.age_sai_idx;

Summary

• Storage-Attached Indexes store index data directly with SSTable files rather than in separate tables, reducing read path latency.
• Create indexes using CREATE CUSTOM INDEX ... USING 'sai' with optional WITH OPTIONS for text analyzers or vector similarity functions.
• Support for multiple query types includes equality, range, full-text CONTAINS, and vector ANN (Approximate Nearest Neighbor) searches.
• Automatic maintenance occurs during SSTable flush and compaction via StorageAttachedIndexBuilder and StorageAttachedIndexWriter.
• Query routing uses in-memory views managed by IndexViewManager to evaluate predicates without full table scans.

Frequently Asked Questions

What is the difference between SAI and traditional secondary indexes in Cassandra?

Traditional secondary indexes create separate tables that require network hops to coordinator nodes, while SAI stores index files co-located with SSTable data. According to the source code in IndexDescriptor.java, SAI writes per-column index files during compaction, eliminating the need for distributed index tables and reducing read amplification.

Can I create multiple SAI indexes on different columns of the same table?

Yes, you can create multiple Storage-Attached Indexes on different columns within a single table. Each index operates independently with its own on-disk structure managed by StorageAttachedIndexBuilder, allowing you to query by name, age, or vector similarity simultaneously without performance degradation from shared index tables.

How does SAI handle vector similarity search?

SAI supports vector indexing through specialized postings lists implemented in VectorPostingsWriter.java and VectorPostings.java. When you create an index on a vector column with a similarity function (DOT_PRODUCT, COSINE, or EUCLIDEAN), SAI builds an index structure that supports k-NN queries using the ANN OF syntax, enabling efficient similarity searches on high-dimensional embeddings.

Does enabling SAI impact write performance?

SAI introduces minimal write overhead because index updates occur during SSTable flush and compaction rather than at insert time. The StorageAttachedIndexWriter serializes index entries using optimized codecs from SAICodecUtils.java, making the write path efficient compared to traditional secondary indexes that require immediate distributed updates.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}