SAI Index Tables¶

The SAI (Storage-Attached Index) virtual tables provide introspection into index state, build progress, and per-SSTable index metadata. Available in Cassandra 5.0+.

Overview¶

SAI indexes are tightly integrated with SSTable storage. These virtual tables expose:

Index metadata and queryability status
Per-SSTable index information
Index segment details for debugging

Table	Purpose
`sai_column_indexes`	Index metadata and build status
`sai_sstable_indexes`	Per-SSTable index info
`sai_sstable_index_segments`	Detailed segment metadata

sai_column_indexes¶

Metadata about SAI indexes including build status.

Schema¶

VIRTUAL TABLE system_views.sai_column_indexes (
    keyspace_name text,
    index_name text,
    table_name text,
    column_name text,
    analyzer text,
    is_building boolean,
    is_queryable boolean,
    is_string boolean,
    PRIMARY KEY (keyspace_name, index_name)
) WITH CLUSTERING ORDER BY (index_name ASC)

Column	Type	Description
`keyspace_name`	text	Keyspace name
`index_name`	text	Index name
`table_name`	text	Base table name
`column_name`	text	Indexed column
`analyzer`	text	Text analyzer (for string indexes)
`is_building`	boolean	Index currently building
`is_queryable`	boolean	Index ready for queries
`is_string`	boolean	String-type index

Example Queries¶

-- All SAI indexes
SELECT keyspace_name, index_name, table_name, column_name,
       is_queryable, is_building
FROM system_views.sai_column_indexes;

-- Indexes currently building
SELECT keyspace_name, index_name, table_name, column_name
FROM system_views.sai_column_indexes
WHERE is_building = true;

-- Non-queryable indexes (building or failed)
SELECT keyspace_name, index_name, is_building
FROM system_views.sai_column_indexes
WHERE is_queryable = false;

-- String indexes (check analyzer column for configured analyzers)
SELECT keyspace_name, index_name, column_name, analyzer
FROM system_views.sai_column_indexes
WHERE is_string = true;

Index States¶

is_queryable	is_building	State
true	false	Ready - normal operation
false	true	Building - queries may return partial results
false	false	Failed or not yet started
true	true	Queryable but rebuild in progress

sai_sstable_indexes¶

Per-SSTable SAI index information.

Schema¶

VIRTUAL TABLE system_views.sai_sstable_indexes (
    keyspace_name text,
    index_name text,
    sstable_name text,
    table_name text,
    column_name text,
    cell_count bigint,
    min_row_id bigint,
    max_row_id bigint,
    start_token text,
    end_token text,
    format_version text,
    per_column_disk_size bigint,
    per_table_disk_size bigint,
    PRIMARY KEY (keyspace_name, index_name, sstable_name)
) WITH CLUSTERING ORDER BY (index_name ASC, sstable_name ASC)

Column	Type	Description
`keyspace_name`	text	Keyspace name
`index_name`	text	Index name
`sstable_name`	text	SSTable identifier
`table_name`	text	Base table
`column_name`	text	Indexed column
`cell_count`	bigint	Number of indexed cells
`min_row_id`	bigint	Minimum row ID in index
`max_row_id`	bigint	Maximum row ID in index
`start_token`	text	Start token range
`end_token`	text	End token range
`format_version`	text	Index format version
`per_column_disk_size`	bigint	Index size for this column
`per_table_disk_size`	bigint	Total index size for table

Example Queries¶

-- Index size per SSTable
SELECT index_name, sstable_name,
       per_column_disk_size / 1048576 AS index_size_mb,
       cell_count
FROM system_views.sai_sstable_indexes
WHERE keyspace_name = 'my_keyspace';

-- SSTables with large indexes (> 100 MB)
SELECT keyspace_name, index_name, sstable_name,
       per_column_disk_size / 1048576 AS size_mb
FROM system_views.sai_sstable_indexes
WHERE per_column_disk_size > 104857600;

sai_sstable_index_segments¶

Detailed segment information within SAI indexes. Useful for advanced debugging.

Schema¶

VIRTUAL TABLE system_views.sai_sstable_index_segments (
    keyspace_name text,
    index_name text,
    sstable_name text,
    segment_row_id_offset bigint,
    table_name text,
    column_name text,
    cell_count bigint,
    min_sstable_row_id bigint,
    max_sstable_row_id bigint,
    min_term text,
    max_term text,
    start_token text,
    end_token text,
    component_metadata frozen<map<text, frozen<map<text, text>>>>,
    PRIMARY KEY (keyspace_name, index_name, sstable_name, segment_row_id_offset)
) WITH CLUSTERING ORDER BY (index_name ASC, sstable_name ASC, segment_row_id_offset ASC)

Column	Type	Description
`segment_row_id_offset`	bigint	Segment offset within SSTable
`min_term`	text	Minimum indexed term
`max_term`	text	Maximum indexed term
`cell_count`	bigint	Cells in this segment
`component_metadata`	map	Detailed component info

Example Queries¶

-- Segment distribution for an index
SELECT sstable_name, segment_row_id_offset, cell_count,
       min_term, max_term
FROM system_views.sai_sstable_index_segments
WHERE keyspace_name = 'my_keyspace'
  AND index_name = 'my_index';

-- Term range analysis
SELECT index_name, min_term, max_term
FROM system_views.sai_sstable_index_segments
WHERE keyspace_name = 'my_keyspace';

Monitoring Use Cases¶

Index Build Monitoring¶

-- Monitor index build progress
SELECT keyspace_name, index_name, is_building, is_queryable
FROM system_views.sai_column_indexes
WHERE is_building = true;

-- Track build by watching SSTable coverage
SELECT index_name, sstable_name
FROM system_views.sai_sstable_indexes
WHERE keyspace_name = 'my_keyspace';

Count distinct SSTables per index in application.

Index Size Analysis¶

-- SAI index sizes by table
SELECT table_name,
       per_column_disk_size / 1048576 AS index_mb
FROM system_views.sai_sstable_indexes
WHERE keyspace_name = 'my_keyspace';

-- Table sizes for comparison
SELECT table_name, mebibytes AS table_mb
FROM system_views.disk_usage
WHERE keyspace_name = 'my_keyspace';

Sum index_mb per table in application and compare to table_mb to calculate overhead percentage.

Query Planning Insights¶

-- Term data for cardinality estimation
SELECT index_name, column_name, min_term
FROM system_views.sai_sstable_index_segments
WHERE keyspace_name = 'my_keyspace';

Count distinct terms per index/column in application for cardinality estimation.

Alerting Rules¶

Index Not Queryable¶

-- Alert: Index not ready for queries
SELECT keyspace_name, index_name, is_building
FROM system_views.sai_column_indexes
WHERE is_queryable = false;

Large Index Size¶

-- Get index sizes for alerting
SELECT keyspace_name, index_name, per_column_disk_size
FROM system_views.sai_sstable_indexes;

Sum sizes per index in application. Alert when total exceeds 10 GB (10737418240 bytes).

Troubleshooting¶

Index Build Stuck¶

Symptoms: - is_building = true for extended period - is_queryable = false

Investigation:

-- Check indexed SSTables
SELECT sstable_name
FROM system_views.sai_sstable_indexes
WHERE index_name = 'problematic_index';

Count results in application to see how many SSTables are indexed.

Resolution: - Check compaction status (builds happen during compaction) - Review logs for errors - Consider dropping and recreating the index

Partial Query Results¶

Symptoms: - Queries return fewer results than expected - is_queryable = true but is_building = true

Explanation: During index build, queries only search already-indexed SSTables.

-- Check build progress
SELECT index_name, is_queryable, is_building
FROM system_views.sai_column_indexes
WHERE keyspace_name = 'my_keyspace';

High Index Overhead¶

Symptoms: - Index size approaching or exceeding table size

Investigation:

-- Index to data ratio
SELECT index_name,
       per_column_disk_size / 1048576 AS index_mb
FROM system_views.sai_sstable_indexes
WHERE keyspace_name = 'my_keyspace';

Sum index_mb per index in application and compare to table size from disk_usage.

Resolution: - Review if index is needed - Consider more selective indexing - Evaluate alternative data models

Virtual Tables Overview - Introduction to virtual tables
SAI Indexing - SAI index creation and usage
Storage Tables - Disk usage monitoring

SAI Index Tables¶

Overview¶

sai_column_indexes¶

Schema¶

Example Queries¶

Index States¶

sai_sstable_indexes¶

Schema¶

Example Queries¶

sai_sstable_index_segments¶

Schema¶

Example Queries¶

Monitoring Use Cases¶

Index Build Monitoring¶

Index Size Analysis¶

Query Planning Insights¶

Alerting Rules¶

Index Not Queryable¶

Large Index Size¶

Troubleshooting¶

Index Build Stuck¶

Partial Query Results¶

High Index Overhead¶

Related Documentation¶