Toggle Menu

Indexing in Cassandra with Storage Attached Indexes (SAI)

Have you ever wondered why Cassandra requires the ALLOW FILTERING keyword for some queries? It’s because you’ve tried to query on a column that isn’t part of the partition key. While it’s not recommended to use ALLOW FILTERING in most cases for performance reasons, Cassandra’s secondary indexes provide a better approach. The Storage Attached Index (SAI) is a new secondary index implementation now available in Datastax Astra and Datastax Enterprise. SAI provides a filtering capability which is easier to use, more efficient, and simpler to maintain than Cassandra’s current indexing or add-on search solutions.

Get Started
Introduction-SAI

When to use SAI

Indexes allow you to query columns outside the Cassandra partition key without using the ALLOW FILTERING keyword or creating custom tables for each query pattern, as you would according to the classic best practices for Cassandra data modeling. You can create a table that is most natural for you, write to just that table, and query it any way you want. Your queries are not restricted by your primary key.

Next concept: Defining SAI indexes

Defining SAI indexes

After creating your database, a keyspace, and one or more tables, use CREATE CUSTOM INDEX ... USING 'StorageAttachedIndex' DDL commands to define one or more SAI indexes on the table that you wish to index.

Next concept: Querying your table
Defining SAI indexes
Querying your table

Querying your table

Once the index has been created, it is simply a matter of querying the table and specifying the SAI-indexed columns.

SAI is supported by DataStax Enterprise 6.8.3 and later (see the DSE release notes), and you can also give it a try on your free database in DataStax Astra in the skill building section below.

SAI is on the roadmap to be added to OSS Cassandra in the near future. See the Cassandra Enhancement Proposal (CEP-7) for more details.

Next: Skill Building

Skill Building

Try out Cassandra indexing with SAI for yourself - query tables based on non-primary key columns quickly and easily. Give it a try!

Using Storage-Attached Indexing (SAI) on Astra

DataStax Storage-Attached Indexing (SAI) lets you create one or multiple secondary indexes on the same database table, with each SAI index based on any column. SAI provides more functionality compared to Cassandra secondary indexes, is faster at writes compared to any Cassandra or DSE Search index, and uses significantly less disk space.

CONTRIBUTORS

DataStax Storage-Attached Indexing (SAI) lets you create one or multiple secondary indexes on the same database table, with each SAI index based on any column. SAI provides more functionality compared to Cassandra secondary indexes, is faster at writes compared to any Cassandra or DSE Search index, and uses significantly less disk space.

In this scenario, we'll learn how to:

  • Create an Astra database
  • Install and configure cqlsh
  • Create a table in Astra
  • Create SAI indexes on columns
  • Perform SELECT queries based on indexed columns

Astra is a great Cassandra as a Service offering, making setup easy for us. Let's get started!

Time to Complete

10 minutes

Difficulty

Beginner

Share

More Resources

Hands-on learning, articles, and documentation for SAI

SAI quick start
SAI

SAI Quick Start

Follow this short tutorial to get started quickly with using indexes on DSE or Astra.

See the docs
What is SAI
SAI

What is SAI?

Storage-Attached Indexing is a highly-scalable, globally-distributed index for Apache Cassandra®.

See the docs
Blog
SAI

Better Cassandra Indexes for a Better Data Model: Introducing Storage-Attached Indexing

The future of indexing in Apache Cassandra is here.

Read More