Cassandra SASI Indexes

SASI stands for SSTable Attached Secondary Index. It is an advanced indexing mechanism that supports range queries, prefix searches, and substring matching on non-primary-key columns — capabilities that standard secondary indexes do not provide. SASI stores the index data inside the SSTable files themselves rather than in a separate index table.

What SASI Adds Over Standard Indexes

Feature              Standard Index    SASI Index
──────────────────────────────────────────────────────────────
Equality (=)         Yes               Yes
Range (>, <, >=, <=) No                Yes
LIKE prefix (abc%)   No                Yes (PREFIX mode)
LIKE suffix (%abc)   No                Yes (CONTAINS mode)
LIKE contains (%abc%)No                Yes (CONTAINS mode)
Case-insensitive     No                Yes (with analyzer)
Text tokenization    No                Yes (with analyzer)

Creating a SASI Index

CREATE CUSTOM INDEX ON table_name (column_name)
  USING 'org.apache.cassandra.index.sasi.SASIIndex'
  WITH OPTIONS = {
    'mode': 'mode_name'
  };

SASI Modes

PREFIX Mode

PREFIX mode indexes the beginning of each value. It supports exact match and prefix LIKE queries (value%). This is the most storage-efficient mode.

CREATE CUSTOM INDEX idx_customer_name ON customers (last_name)
  USING 'org.apache.cassandra.index.sasi.SASIIndex'
  WITH OPTIONS = {'mode': 'PREFIX'};

-- Prefix search:
SELECT * FROM customers WHERE last_name LIKE 'Smi%';

-- Exact match:
SELECT * FROM customers WHERE last_name = 'Smith';

CONTAINS Mode

CONTAINS mode indexes every suffix of each value, which enables LIKE queries at any position: prefix (abc%), suffix (%abc), and contains (%abc%). It uses more storage than PREFIX mode.

CREATE CUSTOM INDEX idx_product_desc ON products (description)
  USING 'org.apache.cassandra.index.sasi.SASIIndex'
  WITH OPTIONS = {'mode': 'CONTAINS'};

-- Substring search:
SELECT * FROM products WHERE description LIKE '%wireless%';

-- Suffix search:
SELECT * FROM products WHERE description LIKE '%mouse';

SPARSE Mode

SPARSE mode is designed for numeric or timestamp columns with high cardinality. It supports range queries efficiently with lower storage overhead than CONTAINS mode.

CREATE CUSTOM INDEX idx_order_total ON orders (total)
  USING 'org.apache.cassandra.index.sasi.SASIIndex'
  WITH OPTIONS = {'mode': 'SPARSE'};

-- Range query on a non-primary-key numeric column:
SELECT * FROM orders
WHERE customer_id = [uuid]
  AND total >= 100.00
  AND total <= 500.00;

Text Analyzer for Full-Text Search

SASI supports analyzers that tokenize and normalize text before indexing. This enables case-insensitive search and multi-word search across individual tokens in a string.

NonTokenizingAnalyzer (case-insensitive)

CREATE CUSTOM INDEX idx_city ON customers (city)
  USING 'org.apache.cassandra.index.sasi.SASIIndex'
  WITH OPTIONS = {
    'mode': 'PREFIX',
    'analyzer_class':
      'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
    'case_sensitive': 'false'
  };

-- Case-insensitive search:
SELECT * FROM customers WHERE city LIKE 'lond%';
-- Matches 'London', 'LONDON', 'london'

StandardAnalyzer (tokenized text)

CREATE CUSTOM INDEX idx_bio ON profiles (bio)
  USING 'org.apache.cassandra.index.sasi.SASIIndex'
  WITH OPTIONS = {
    'mode': 'CONTAINS',
    'analyzer_class':
      'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
    'analyzed': 'true',
    'tokenization_enable_stemming': 'true',
    'tokenization_locale': 'en'
  };

-- Find profiles where bio contains the word "engineer":
SELECT * FROM profiles WHERE bio LIKE '%engineer%';

SASI Range Queries on Timestamps

CREATE TABLE events (
  user_id    UUID,
  event_time TIMESTAMP,
  event_type TEXT,
  PRIMARY KEY (user_id, event_time)
);

CREATE CUSTOM INDEX idx_event_time ON events (event_time)
  USING 'org.apache.cassandra.index.sasi.SASIIndex'
  WITH OPTIONS = {'mode': 'SPARSE'};

-- Query events in a time range without clustering column filter:
SELECT * FROM events
WHERE event_time >= '2024-06-01'
  AND event_time <  '2024-07-01'
ALLOW FILTERING;

Dropping a SASI Index

DROP INDEX idx_customer_name;

SASI vs Standard Index vs Materialized View

Feature              Standard Index  SASI Index       Materialized View
─────────────────────────────────────────────────────────────────────────
Equality queries     Yes             Yes              Yes
Range queries        No              Yes              Yes (if in PK)
LIKE / prefix search No              Yes              No
Full-text search     No              Yes (analyzer)   No
Write overhead       Low             Medium           High
Storage overhead     Medium          Medium-High      High (full copy)

SASI Limitations

Limitation                          Notes
──────────────────────────────────────────────────────────────────
Experimental status                 Marked experimental in some
                                    Cassandra versions; test before
                                    production use
All-node fan-out                    Like standard indexes, queries
                                    go to every node
Not suited for high write volume    Each write updates the SASI index
                                    embedded in the SSTable
LIKE with leading wildcard is slow  %value% performs full scan per node
No aggregate support                Cannot use COUNT, AVG inside SASI

When to Use SASI

Good SASI Use Cases
──────────────────────────────────────────────────────────────────
Name prefix search (autocomplete): last_name LIKE 'Smi%'
Case-insensitive city search: city LIKE 'london%' (case_sensitive=false)
Price range queries on non-PK column: price >= 50 AND price <= 200
Keyword search in product descriptions: description LIKE '%wireless%'
Date range filtering without clustering column

Summary

SASI indexes extend Cassandra's querying capabilities with range operators, LIKE pattern matching, and text analysis. Use PREFIX mode for autocomplete-style prefix queries, CONTAINS mode for substring search, and SPARSE mode for numeric or timestamp range queries. Add an analyzer for case-insensitive or tokenized full-text search. SASI is more powerful than standard secondary indexes but carries higher storage overhead and should be used thoughtfully on write-heavy tables.