SAI FAQ

Use this FAQ to find answers to common questions and get help with Storage-Attached Indexing (SAI).

What is SAI?

Storage-Attached Indexing (SAI) is a highly-scalable, globally-distributed index for the Cassandra database. SAI combines:

• the storage-attached architecture of open source SSTable Attached Secondary Indexes (SASI)
• a number of highly optimized on-disk index structures

  Which databases are supported?

  Cassandra 5.0 is the only supported database currently. CREATE CUSTOM INDEX ... USING 'StorageAttachedIndex' to define one or more SAI indexes on the table. For Cassandra databases, use cqlsh. The same CREATE CUSTOM INDEX ... USING 'StorageAttachedIndex' command is available for both. See SAI quickstart.

  What configuration settings should I use with SAI?

  Compared with most indexing environments, SAI configuration and related settings are much simpler. Key points:
• --XX:MaxDirectMemorySize, leaving approximately 15-20% of memory for the OS and other in-memory structures.
• file_cache_size_in_mb to 75% of that value.

• Heavy mixed read/write workloads may want to:

• range_request_timeout_in_ms

• write_request_timeout_in_ms
• memtable_flush_writers value is set too low, writes may stall. If this occurs in your environment, increase memtable_flush_writers. Configure SAI indexes.

  What computing challenges does SAI solve?

  Oftentimes, developers ask: “How can I query additional fields outside of the Apache Cassandra partition key?” SAI implements efficient indexes based on a table’s columns, such as parts of a composite partition key. Before SAI, you could index clustering keys, but you could not index parts of a composite partition. The development of SAI was inspired by SASI to achieve the goal of efficient and simpler filtering via the creation of secondary indexes. SAI also makes data modeling easier because you do not need to create custom tables just to cater to particular query patterns. You can create a table that is most natural for you, write to just that table, and query it any way you want.

  What are the advantages of using SAI?

  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. For developers, SAI removes several previous pain points, including the need to duplicate denormalized data to query non-PrimaryKey columns. For operators, SAI has several advantages, including the use of significantly less disk space for indexing; fewer failure points; easier uptime due to the simplified architecture of SAI; and fewer copies of data to secure.

  Is SAI a complete search solution?

  SAI is not an enterprise search engine. While it does provide some of the same functionality, SAI is not a complete replacement for text-based search. At its core, SAI is a filtering engine, and simplifies data modeling and client applications that would otherwise rely heavily on maintaining multiple query-specific tables.

  How does schema management compare between SAI and text-based search?

  SAI is an index, not a search engine. Unlike the text-based search, SAI has no need for schema management. SAI configuration is simpler and is tuned with existing database parameters, such as in cassandra.yaml. With SAI, there is no commit log to accept writes during bootstrap; SAI does not need to wait for bootstrap to read the database configuration. With SAI, schema/indexing options reside in the index metadata, which is handled by native database schema management.

  How do I use SAI features?

  Storage-Attached Indexing has queries are entirely CQL-based. The features, by design, are intentionally simple and easy to use. At a high level, SAI indexes are:
• CREATE CUSTOM INDEX ... USING 'StorageAttachedIndex' commands and DROP INDEX commands. Start in SAI quickstart.
• nodetool.
• Monitor SAI indexes.

  On which column in a database table can I base an SAI index?

  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. keys, values, and entries as map types. SAI also supports list and set collections.

  DROP and recreate an SAI index on the same column, does that block any read operations? And is there a way to check the indexing status?

  DROP / recreate an SAI index, you are not blocked from entering queries that do not use the index. However, you cannot use that SAI index (based on the same column) until it has finished building and is queryable. To determine the current state of a given index, query the system_views.indexes virtual table. Example:

  SELECT is_queryable,is_building FROM system_views.indexes WHERE keyspace_name='keyspace'       AND table_name='table' AND index_name='index';

  Virtual tables and Virtual tables for SAI indexes and SSTables.

  What are the write and read paths used by SAI indexes?

  SAI write path and read path.

  What on-disk index formats does SAI support?

  SAI supports two on-disk index formats, optimized for:

• strings.

• trie data structure, in conjunction with postings (term/row pairs) lists. The trie is heap friendly, providing string prefix compression for terms, and can match any query that can be expressed as a deterministic finite automaton. The feature minimizes on-disk footprint and supports simple token skipping.

• numeric and non-literal types.

• timestamp, date, UUID) are indexed on-disk using k-dimensional tree, a balanced structure that provides fast lookups across one or more dimensions, and compression for both values and postings.

  What is the disk footprint overhead for SAI indexes?

  unindexed data. The SAI disk usage is largely dependent on the underlying data model and the number of columns indexed.

  What are the supported column data types for SAI indexing?

  ASCII, BIGINT, DATE, DECIMAL, DOUBLE, FLOAT, INET, INT, SMALLINT, TEXT, TIME, TIMESTAMP, TIMEUUID, TINYINT, UUID, VARCHAR, VARINT.

  Does SAI support indexes on a collection column?

  map, list, and set. See the following topics:

• Using collections with SAI

  What are the supported query operators?

  For queries on tables with SAI indexes:
• =, <, >, ⇐, >=, AND, OR, IN
• =, CONTAINS, CONTAINS KEY, AND, OR, IN The unsupported query operators are:
• LIKE Examples:

  SELECT * FROM cycling.cyclist_semi_pro WHERE registration > '2010-01-01' AND registration < '2015-12-31' LIMIT 10;

  SELECT * FROM audit WHERE text_map CONTAINS KEY 'Giovani';

  CREATE CUSTOM INDEX command for SAI, what options are available?

  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'};

  Does SAI support composite indexing: meaning, a single index on multiple columns?

  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. Also, SAI can use multiple defined indexes within a single read query.

  How can I view SAI memory usage metrics?

  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.

  AND clauses can I add?

  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. match streaming and post filtering example.

  Are SAI write operations asynchronous, or does SAI wait before acknowledging the write to the user?

  synchronous process. When the memtable is flushed, the indexes are flushed as well. See SAI write path and read path. The on-disk index components are broken down into per-SSTable index files and per-column index files. The column indexes do not store the primary keys or tokens; instead, they store compressible row IDs. The per-SSTable index files link the row IDs from the column-indexes to their backing SSTables. This SAI design allows all column indexes within a single SSTable to share per-SSTable index files, which further helps reduce the disk footprint.

  What are the guidelines regarding column cardinality with SAI indexes?

  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.

  What are the circumstances under which SAI applies post filtering?

  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';

  Given a query such as the following:

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

  col1) first, then applies post-filtering on col2. (Use the ALLOW FILTERING clause with caution.) In this scenario, no additional replica roundtrips are needed; the post filtering on col2 is carried out on the replicas themselves. related FAQ about AND queries.

  static column?

  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 like:

  SELECT * from transaction_by_customer where customer_id = 'xyz123';

  transaction_by_customer rows, because you defined those three static fields, this query runs against a table that uses significantly less disk space, as compared to an environment where writes had inserted the per-customer values (address, phone_number, date_of_birth) in every row.

  For indexed strings, how does SAI handle Unicode characters in the column data?

  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.

  Can the index’s column name have special characters?

  InvalidRequestException if you try to define an index on a column name that contains other characters, and does not create the index.

  What partitioner does SAI support?

  Murmur3Partitioner.