Index types and use cases

The data stored in CQL tables is queried through primary and secondary indexes:

Primary indexing

The main method for querying tables is primary indexing, which uses a table’s defined partition key. This is any of the typical SELECT statements that you might use on a primary key column.

All tables support primary indexing queries because all tables have a partition key and a primary index.

The Cassandra storage engine uses the partition key to store rows of data, and the most efficient and fastest data lookups are matches on the partition key.

Secondary (auxiliary) indexing

To query non-primary key columns efficiently, you must create secondary indexes of those columns. Secondary indexing uses fast, efficient lookup of data that matches a given condition, but it requires storage overhead and deliberate creation and maintenance of the indexes.

Secondary indexes are optional and must be created explicitly. Don’t create secondary indexes for every column; only index columns relevant to the queries expected by your data model.

When compared to non-indexed tables, tables with secondary indexes typically experience significantly higher latency and lower throughput. In some cases, doubled latency and halved throughput. Often, system resources must be adjusted to provide additional capacity for the indexes.

Most types of secondary indexing work best when there is a moderate cardinality of the indexed values, meaning there are a variety of identical and unique values in the rows, but the rows aren’t excessively unique. The more unique values that exist in a particular column, the more overhead, on average, is required to query and maintain the index. For example, indexing a column where almost every value is different is typically inefficient and resource intensive. In contrast, a boolean column with only two possible values is not useful for queries.

In most cases, secondary indexes are like filters where you want the filter to be diverse enough to be useful, but not so specific that it isn’t reusable for different queries.

One exception to cardinality is vector (similarity) search using storage-attached indexing (SAI). In this case, all vector values are expected to be different because vector search compares them to a query vector to select results by degree of relevance.

Supported index types

There are several types of secondary indexing available, but they aren’t interchangeable. Furthermore, not all secondary index types are supported by every database platform.

Indexing support by product
Indexing type	Astra DB Serverless	Astra Managed Clusters	Apache Cassandra®	DataStax Enterprise (DSE)	Hyper-Converged Database (HCD)
Primary indexing (primary key)	Supported	Supported	Supported	Supported	Supported
Storage-attached indexing (SAI)	Supported	Supported	Supported (5.0)	Supported (6.8, 6.9)	Supported
Original secondary indexing (2i)	Not supported	Supported	Supported	Supported	Supported
SSTable-attached indexing (SASI)	Not supported	Not supported	Experimental, not recommended	Experimental (5.1, 6.8), not recommended	Not supported
DSE Search indexing	Not supported	Supported	Not supported	Supported	Not supported

Storage-attached indexing (SAI) (recommended)

SAI uses indexes for non-partition columns, and it attaches the indexing information to the SSTables that store the rows of data. SAI is designed to be a general indexing method that can be used for any type of query.

SAI is the recommended indexing method for most use cases. There are some exceptions for edge cases where 2i or DSE Search indexing are more appropriate, if supported by your database platform and required by your data model.

SAI resolves write amplification and index size issues that occur with 2i and SASI:

SASI showed that in-memory indexing and flushing indexes with SSTables was an efficient use of the write path. However, with SAI, the data is indexed when the mutation is acknowledged (fully committed to the table). This change and other optimizations vastly improved write performance with SAI. Even compared to 2i, SAI results in a 40 percent increase in throughput and over 200 percent better write latencies.
SAI dramatically improves index size, reducing storage requirements by a factor of 8 compared to SASI and a factor of 5 compared to 2i. This is because SAI uses two different types of indexing schemes based on the data type:
- Text: Inverted indexes are created with terms broken into a dictionary. The biggest improvement is from the use of trie-based indexing, which offers better compression and, ultimately, smaller index sizes.
- Numeric: Uses a data structure from Apache Lucene™ called block kd-trees that offers excellent range query performance. A separate row ID list is maintained to optimize for token-order queries.

HCD 2.0 introduces the following settings in cassandra.yaml to simplify secondary index creation with the CREATE INDEX command:

default_secondary_index: The default value is sai. A CREATE INDEX command that doesn’t specify an index type with a USING clause will default to SAI. DataStax recommends leaving this default since it is recommended for most workloads.
default_secondary_index_enabled: The default value is true, which enables default_secondary_index. If set to false, all CREATE INDEX commands require an explicit index type in the USING clause.

For more information about creating and using SAI, see SAI performance and use cases.

Secondary indexing (2i)

2i indexes, also known as index lookups, are the original built-in indexing method for Cassandra. These indexes are a local index, stored in an internal (hidden) table on each node of a cluster, separate from the table that contains the values being indexed.

Due to potential performance degradation, 2i is only recommended when used in conjunction with a partition key.

Don’t use 2i index in the following situations:

On high-cardinality columns to a query of a huge volume of records for a small number of results. See Problems using 2i for a high-cardinality column.
In tables that use a counter column.
On columns with frequently updated or deleted values. See Problems using 2i for a frequently updated or deleted column.
For broad queries on large partitions. See Problems using 2i to look for a row in a large partition unless narrowly queried.
Queries using non-equality or range operators.

Indexing is never free: The more you add, the more you impact write performance. In Cassandra databases, this manifests as write-amplification issues. When a mutation on an indexed column occurs, an indexing operation triggers reindexing of data in a separate index file.

More indexes on a table can dramatically increase disk activity during write operations. If a single node gets too many writes, I/O saturation can occur. This destabilizes individual nodes, creating cluster-wide performance issues.

For this reason, 2i should be used sparingly. Index size is fairly linear, but it can be difficult to plan for the amount of disk space needed in an active cluster for storing and reindexing indexes.

For more information about creating and using 2i, see Create and use secondary indexes (2i).

SSTable-attached indexing (SASI)

SASI isn’t supported in Hyper-Converged Database (HCD).

DSE Search indexing

DSE Search indexing isn’t supported in HCD.

Build and maintain secondary indexes

After you create an index on a table column, the index is built in the background automatically without blocking reads or writes.

Client-maintained tables as indexes must be created manually. For example, if an age column was indexed by creating a table named by_age, then your client application must populate the table with supporting data from other tables, such as a name table that uses id as the primary key.

To perform a hot rebuild of an index, use the nodetool rebuild_index command.