Indexes
The data stored in Cassandra-based databases is queried through primary and secondary indexes:
- Primary indexing
-
The main method for querying tables is primary indexing, which uses a table’s partition key. All CQL tables have a primary index because all tables have at least one partition key in the
PRIMARY KEYdefinition. By design, 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.Because effective querying should always result in a continuous slice of data being retrieved from the table, querying to match a non-primary key column is an anti-pattern. Non-primary keys don’t influence the sequence or storage of data, so querying for a particular value on a non-primary key column requires the database to scan all partitions. Scanning all partitions generally results in a prohibitive read latency, and it isn’t allowed by default. Options like
ALLOW FILTERINGcan be used to override this restriction, but they should be used with caution due to the aforementioned full partition scans.If you need to query by a non-primary key column frequently, a secondary index on that column enables more efficient queries without the need for full partition scans.
- 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 for specific table columns. Don’t create secondary indexes for every column; only index columns relevant to the queries expected by your data model.
Maintenance of indexes places additional load on the system, as updates to the indexed columns require corresponding updates to the indexes themselves.
Additionally, some columns aren’t suitable for indexing due to partitioning or the nature of the values they contain. Queries, with or without indexes, that involve many partitions are inherently inefficient.
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
booleancolumn with only two possible values is not useful for queries.Generally, you should treat secondary indexes 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.
- Secondary indexing for vector search
-
Similarity search on
vectorcolumns is an exception to the typical cardinality considerations for secondary indexes. 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. Use storage-attached indexing (SAI) to indexvectorcolumns and run similarity searches in CQL.
When a row in an indexed column changes, the corresponding secondary index entries are updated automatically through rebuild/reindex processes that run in the background without blocking reads or writes.
To perform a hot rebuild of an index, use the nodetool rebuild_index command.
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.
If you have experience with earlier Cassandra index types, SAI resolves write amplification and index size issues that occurred with those types.
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. For more information about creating and using 2i, see Create and use secondary indexes (2i).
Manual indexing with tables
Denormalized tables are a valid pattern in Cassandra-based databases for enabling queries that aren’t possible or efficient on another table. This approach can be more performant than secondary indexes for certain query patterns, but it requires additional effort.
These tables-as-indexes are created and maintained as regular tables (not index objects in the keyspace).
You must manually ensure that these tables are kept in sync with the original table.
For example, if you denormalize an age column into a table named by_age, then your client application must write to the original table and the denormalized table.
