Now in private preview: Cluster Key Index support for Azure Cosmos DB Cassandra API

Iria Osara

Join the private preview for cluster key index in Azure Cosmos DB Cassandra API. To enable on your account contact the Azure Cosmos DB Cassandra API team at cassandra-preview@microsoft.com

Benefits of indexes on clustering keys 

• Indexing cluster key helps improve performance even as data grows, unlike without index that degrades performance linearly as data grows.
• Faster queries with properly indexed cluster keys, throughput cost (RU consumption) is also reduced. 
• Improved efficiency in aggregation and range query when filtering on an indexed cluster key or data with large partitions. 

Cluster Key Index use cases 

We will be creating a table, inserting sample data and running some sample queries to show how cluster key indexing works within Azure Cosmos DB Cassandra API. 

CREATE TABLE uprofile.democluster(id int, name text, state text, level int, primary key ((id), name, state)); 

INSERT INTO uprofile.democluster(id, name, state, level) VALUES (103, 'Sara Nancy', 'active', 2);

1. Filter on non-leading cluster key

   SELECT * FROM uprofile.democluster WHERE state='active' AND name='Sara Nancy';

   These types of queries are not allowed without index on name since the clause is not restricted without the preceding cluster key. With index on name, the query becomes valid,  and the performance is also consistent.

2. Filter on leading cluster key

   SELECT * FROM uprofile.democluster where name='Sara Nancy';

   Note that without index on name the query will require you to ALLOW FILTERING which will affect performance.  

   CREATE INDEX ON uprofile.democluster (name);

   Indexing cluster key will help achieve consistent latency and RU/s consumption.  

3. Filter on cluster key while selecting with aggregation function

   SELECT MIN(level) FROM uprofile.democluster WHERE name='Sara Nancy';

   Without an index on ‘name’, this query requires ALLOW FILTERING and is inefficient. 

   SELECT MIN(level) FROM uprofile.democluster WHERE id=103 AND name='Sara Nancy';

   Without a partition key query there is no need for ALLOW FILTERING,  but the query is inefficient resulting in increased latency and costly throughput (RU) consumption.

   Having an index on ‘name’, the aggregation query becomes efficient. You can index the partition key ‘id’, to achieve the best performance for your query and reduce RU. 

Get started 

If you’re interested in trying out cluster key index within Azure Cosmos DB Cassandra API, contact us at cassandra-preview@microsoft.com for instructions to configure and setup your Cassandra API account.  We are excited to have you try this new capability in our API for Cassandra in Azure Cosmos DB! 

Discover additional features and capabilities about Azure Cosmos DB and get started for free.

Stay up to date on the latest Azure Cosmos DB news and features by following us on Twitter @AzureCosmosDB.

Iria Osara Program Manager, Azure Cosmos DB

Follow