Storage-Attached Indexing (SAI) is a highly-scalable, globally-distributed index for Cassandra, DataStax DataStax Astra DB, and DataStax Enterprise databases. SAI combines a storage-attached architecture with a number of highly optimized on-disk index structures.

Supported databases:

Compared with most indexing environments, SAI configuration and related settings are much simpler. Key points:

Oftentimes, developers ask: "How can I query additional fields outside of the CQL partition key?"

SAI makes it possible to define multiple indexes on the same database table. Each SAI index can be based on any column in the table. Exception: There is no need to define an SAI index based on the partition key when it’s comprised of only one column; in this case, SAI issues an invalid query message. You can also define an SAI index using a single column in the table’s composite partition key. A composite partition key means that the partition is based on two or more columns. In this case with an SAI index, you would specify just one of the columns that comprises the composite partition key.

SAI indexes and queries are entirely CQL-based. The features, by design, are intentionally simple and easy to use.

SAI has limits that may or may not affect a cluster.

Yes — SAI supports collections of type map, list, and set.

For queries on tables with SAI indexes:

Use the WITH OPTIONS clause to indicate how SAI should handle case sensitivity and special characters in the index. For example, given a string column lastname:

CREATE CUSTOM INDEX lastname_sai_idx ON cycling.cyclist_semi_pro (lastname)
  USING 'StorageAttachedIndex' WITH OPTIONS =
  {'case_sensitive': 'false', 'normalize': 'true', 'ascii': 'true'};

No. There is a 1-to-1 mapping of an SAI index to a column. However, you can create a separate index on each column in a given table, up to the configured maximums — see this related FAQ. Also, SAI can use multiple defined indexes within a single read query.

SAI follows the Threads Per Core (TPC) memory model for DSE. The SAI memory footprint is divided between the JVM heap and the Chunk Cache. The heap stores memtable indexes, and the chunk cache stores recently accessed on-disk index components as well as other SSTable components. SAI provides metrics for both the heap and the chunk cache. For each index, SAI also provides metrics for determining the size in bytes of memory used by the on-disk data structure, as well as disk usage. Refer to Index group metrics. SAI also provides Table state metrics that give you visibility into the disk usage, the percentage of disk usage of the base table, the index builds in progress, and related metrics. See Table state metrics. These metrics and many others are accessible via DSE OpsCenter. Also, DataStax provides preconfigured Grafana dashboards.

There is no limit on the number of index columns that can be used in a single query. The sai_indexes_per_table_failure_threshold setting in cassandra.yaml controls the maximum number of SAI indexes allowed in a single table (10, by default). However, querying against multiple indexed columns incurs a cost that is related to the increased number of index components processed. When evaluating multiple indexed columns in a query, SAI performs a workflow (1: Traverse. 2: Merged. 3: Intersect) that ultimately coalesces data from multiple memtables and SSTables. DataStax performance testing has observed an (up to) 2x increase in latency and a 30% decrease in throughput when the number of index columns queried doubles. The relative impact on performance is largely dependent on the data model and distribution. DataStax recommends that you test specific use cases in your development environment.

The SAI write path is actually very simple. The indexes live with the data, both in memtables and SSTables. When a write is acknowledged to the client, the data is already indexed. This is a synchronous process. When the memtable is flushed, the indexes are flushed as well. See SAI write path and read path.

All DSE Search (Solr) index updates first perform a "read-before-write" procedure against the partition or row being indexed. As described above, the SAI write path is much simpler; basically, the indexes follow along with the data. DataStax testing has observed SAI write throughput perform 86% better than DSE Search (Solr), and perform 670% better with respect to latency. In general, DataStax testing observed SAI produce single-digit millisecond (ms) latencies for mutations, and single-digit to low-teen ms latencies for reads.

Column cardinality can affect read performance when it comes to range queries among replicas. The number of rows matching a value of a high-cardinality column, such as credit card numbers, is more likely to be isolated on very few nodes (or even isolated to one node), while the rows matching a value on a low-cardinality column are more likely to reside on numerous nodes. If a query does not specify a partition key, the Cassandra coordinator scans the token ring and group token ranges by endpoints (nodes). The coordinator then concurrently execute read commands for all participating endpoints. In the worst case where the indexed column has very high cardinality, an entire cluster scan may be required before finding a match. With low-cardinality columns, be aware that if your LIMIT is higher than the number of values in your targeted column, Cassandra has to search all replicas again before determining that the LIMIT cannot be satisfied. In this case, Cassandra returns only the number of matching results.

SAI applies post-filtering in numerous scenarios. For example, consider a simple table, and an SAI index on just one of the two non-PK columns:

CREATE KEYSPACE test WITH REPLICATION = {'class': 'SimpleStrategy', 'replication_factor': 1};

CREATE TABLE test.mytable (id int PRIMARY KEY,
       col1 text,
       col2 timestamp);

CREATE CUSTOM INDEX mytable_col1_idx ON test.mytable (col1) USING 'StorageAttachedIndex';

SELECT * FROM test.mytable WHERE col1 = 'hello world' and col2 < toTimestamp(now()) ALLOW FILTERING;

Yes. For example, consider a transaction_by_customer table where you have a primary key customer_id, plus static columns to contain each customer’s address, phone_number, and date_of_birth. Given a query such as:

SELECT * from transaction_by_customer where customer_id = 'xyz123';

 DSE Search (Solr) does not allow you to create search indexes on tables that contain static columns.
See https://docs.datastax.com/en/dse/6.8/dse-admin/datastax_enterprise/search/unsupportedSearchFeatures.html[Unsupported features for DSE Search].
SAI delivers the option and advantage of creating indexes based on static columns, while also achieving the benefit of conserving table space.

When you create an SAI index based on a string column, set the normalize option to true if you want SAI to perform Unicode normalization on the column data. SAI supports Normalization Form C (NFC) Unicode. When set to true, SAI normalizes the different versions of a given Unicode character to a single version, retaining all the marks and symbols in the index. For example, SAI would change the character Å (U+212B) to Å (U+00C5). See CREATE CUSTOM INDEX.

SAI validates the column name on which an index is being defined. SAI allows alphanumeric characters and underscores only. SAI returns InvalidRequestException if you try to define an index on a column name that contains other characters, and does not create the index.

SAI supports only the Murmur3Partitioner.

In your development environment, a general approach:

Send your feedback through the DataStax Community, via product.feedback@datastax.com, or both.