Apache Cassandra 5.0 Expert

Querying with SAI:

-- Equality query (uses SAI index)
SELECT * FROM my_table WHERE email = '[email protected]';

-- Range query on numeric column
SELECT * FROM my_table WHERE age > 25 AND age < 65;

-- Combined partition key + SAI filter
SELECT * FROM my_table WHERE partition_id = 'abc' AND status = 'active';

-- Multi-column SAI queries (AND only; no OR)
SELECT * FROM my_table WHERE email = '[email protected]' AND age > 25;

-- Collection contains
SELECT * FROM my_table WHERE tags CONTAINS 'urgent';

-- Map entry query
SELECT * FROM my_table WHERE properties['color'] = 'red';

SAI vs Legacy Secondary Index:

SAI internals:

• SAI data is stored in per-SSTable index files alongside the data
• When an SSTable is compacted, its SAI index is automatically rebuilt
• No separate compaction or repair needed for SAI indexes
• Uses trie-based structures for text indexes and BKD trees for numeric indexes
• Memory-mapped for efficient access
• Bloom filter equivalent built into the index structure

SAI limitations:

• No support for OR queries (only AND)
• No full-text search (not a replacement for Solr/Elasticsearch)
• Scatter-gather without partition key still requires all nodes (but faster than 2i)
• Cannot index counter columns
• Cannot index columns used in the primary key

Unified Compaction Strategy (UCS)

UCS replaces STCS, LCS, and TWCS with a single configurable strategy:

-- Basic UCS (default behavior similar to STCS)
ALTER TABLE my_table WITH compaction = {
    'class': 'UnifiedCompactionStrategy'
};

-- UCS mimicking LCS behavior
ALTER TABLE my_table WITH compaction = {
    'class': 'UnifiedCompactionStrategy',
    'scaling_parameters': 'L4'     -- Leveled with fanout 4
};

-- UCS mimicking STCS behavior
ALTER TABLE my_table WITH compaction = {
    'class': 'UnifiedCompactionStrategy',
    'scaling_parameters': 'T4'     -- Tiered with min_threshold 4
};

-- UCS mimicking TWCS behavior
ALTER TABLE my_table WITH compaction = {
    'class': 'UnifiedCompactionStrategy',
    'scaling_parameters': 'T4',
    'target_sstable_size': '1GiB',
    'base_shard_count': 4,
    'expired_sstable_check_frequency_seconds': 600
};

-- UCS with time-window sharding
ALTER TABLE my_table WITH compaction = {
    'class': 'UnifiedCompactionStrategy',
    'scaling_parameters': 'T4, L8, L8, L8',    -- tier first level, level rest
    'target_sstable_size': '256MiB'
};

UCS scaling parameters explained:

• T<N> = Tiered (like STCS): triggers compaction when N SSTables of similar size exist
• L<N> = Leveled (like LCS): organizes into levels with fanout N
• N<N> = No compaction for that level (data just accumulates)
• Multiple values (comma-separated) define behavior at each level: 'T4, L10, L10'
  • Level 0 (memtable flushes): Tiered with threshold 4
  • Level 1+: Leveled with fanout 10

UCS advantages over legacy strategies:

• Single strategy to learn and configure
• Smooth transition between tiered and leveled behavior
• Better handling of mixed workloads
• Automatic sharding for parallelism
• Configurable per-level behavior
• Improved compaction prioritization

UCS configuration reference:

Trie-Based Storage Engine

Cassandra 5.0 introduces trie-based data structures that replace the traditional B-tree-like partition index:

Trie Memtable:

# cassandra.yaml