Cassandra

Apache Cassandra 6.0 Part 8 - Storage-Attached Indexing and Schema Constraints

Hayato Shimizu August 11, 2026 Updated September 7, 2026 17 min read

Storage-Attached Indexing and Schema Constraints

Suppose you keep a service catalogue in Cassandra and want to find services with a particular capability stored in a frozen collection. Cassandra 6.0 extends Storage-Attached Indexing so you can express that query through an index and also adds CQL constraints for checking writes. We’ll use a small catalogue to work through the queries and results before looking at their cost on a larger table.

An example with a handful of services makes the syntax easier to follow but the number of matching rows changes the work each query requires. Keep the real partitioning and data distribution in the test alongside write rates and tombstones so you can judge whether the index fits the application.

This post follows pre-release Cassandra 6.0 as reviewed against 6.0-alpha3 and the details may change before GA. Check the release notes and upgrade documentation for the version you’re testing before adopting the syntax.

Area	Cassandra 5.0 baseline	Cassandra 6.0 work to validate
Frozen collections	SAI treats a frozen collection as one serialized value for indexing purposes.	Value and element indexing can expose parts of a frozen collection to SAI query predicates.
Index planning	Cassandra chooses an available secondary-index path for a qualifying query.	CQL can select an index explicitly when more than one index path is available.
LIKE predicates	`LIKE` is constrained by the query implementation and index support.	Filtering support is expanded with validation around where wildcard forms can be used.
Constraints	Cassandra 5.0 has no constraint framework.	The 6.0 framework supports `NOT NULL`, scalar, regular-expression, and custom constraint definitions.

How SAI is attached to storage

SAI keeps an index with the memtables and SSTables containing the base table’s data on each node. Following that storage-local path helps explain both the work an index can avoid during reads and the work it adds to writes.

When an indexed value is written SAI records its term and primary-key information in an index associated with the current memtable. That index uses the memtable’s heap budget so more indexed columns can lead to earlier flushes and smaller SSTables. Flushing writes the attached index components with the base SSTable using a byte-ordered trie and postings for string terms or a KD-tree with postings for numeric terms. Both the SSTable and its attached index stay immutable until compaction replaces them.

Compaction rebuilds those SAI components while writing the merged SSTable so you’ll need to measure index cost beyond the query itself. Include memory and disk use alongside flush and compaction activity and follow index builds and rebuilds with the base table.

Stage	Base-table operation	SAI work
Mutation	Cassandra appends to the commit log and updates the active memtable.	The indexed value is associated with the primary key in the memtable index.
Memtable flush	Cassandra creates a new SSTable.	Terms and token-sorted row identifiers are written into attached index components.
Compaction	Cassandra reconciles overlapping SSTables into a replacement SSTable.	SAI buffers and writes the index entries for the merged output so they match the new SSTable.
Query planning	The coordinator receives CQL predicates.	It selects the index that most selectively narrows the search, then intersects indexed predicates when relevant.
Replica read	Matching keys are identified from SAI components.	Cassandra still materializes base-table rows and applies final filtering for conditions, tombstones, and wide-partition granularity.

After the index finds candidate rows Cassandra still reads the base data and checks tombstones and newer updates against the query conditions. Rows in a wide partition may also fail a condition even when the index identified that partition as a candidate. A selective index reduces the work but a predicate matching many partitions can still require a large distributed read.

Frozen collection indexing

CASSANDRA-18492 extends SAI to index values and elements inside frozen collections. Previously indexing a frozen collection meant indexing its complete serialized value which suited equality checks against the whole collection. The new targets let you find a row containing a particular element without requiring the remaining elements to match.

A frozen set makes this easier to follow because two services can share one capability while having different complete sets.

Stored value	Whole-collection view	Element-oriented view
`{'alerting', 'cassandra', 'operations'}`	One serialized collection value.	Individual terms such as `alerting`, `cassandra`, and `operations` are candidates for indexed lookup.
`{'cassandra', 'performance'}`	A different serialized collection value.	`cassandra` can match rows containing that element without requiring the full set to be identical.

The example creates an index on collection values and then looks for services whose capabilities include Cassandra. Cassandra 5.0 requires filtering for this CONTAINS query while 6.0 can use the attached index.

CREATE TABLE catalogue.service_profiles (
    service text PRIMARY KEY,
    owner_team text,
    tier text,
    region text,
    capabilities frozen<set<text>>,
    labels frozen<map<text, text>>
);

CREATE INDEX service_capabilities_sai
    ON catalogue.service_profiles (VALUES(capabilities))
    USING 'sai';

INSERT INTO catalogue.service_profiles
    (service, owner_team, tier, region, capabilities, labels)
VALUES (
    'payments-api',
    'payments',
    'critical',
    'eu-west-1',
    {'cassandra', 'kafka', 'pci'},
    {'region': 'eu-west-1', 'owner': 'payments'}
);

INSERT INTO catalogue.service_profiles
    (service, owner_team, tier, region, capabilities, labels)
VALUES (
    'analytics-api',
    'data',
    'important',
    'eu-west-1',
    {'cassandra', 'spark'},
    {'region': 'eu-west-1', 'owner': 'data'}
);

INSERT INTO catalogue.service_profiles
    (service, owner_team, tier, region, capabilities, labels)
VALUES (
    'edge-api',
    'platform',
    'critical',
    'us-east-1',
    {'kafka', 'rate-limiting'},
    {'region': 'us-east-1', 'owner': 'platform'}
);

INSERT INTO catalogue.service_profiles
    (service, owner_team, tier, region, capabilities, labels)
VALUES (
    'ledger-api',
    'payments',
    'critical',
    'eu-west-1',
    {'audit', 'kafka'},
    {'region': 'eu-west-1', 'owner': 'payments'}
);

SELECT service, owner_team, capabilities
FROM catalogue.service_profiles
WHERE capabilities CONTAINS 'cassandra';

The query returns payments-api and analytics-api because both capability sets contain cassandra even though their other elements differ. You can search for that shared element without supplying either service’s complete frozen set.

For a frozen map you can choose an index target based on whether the query searches keys or values or specific entries.

Required lookup	SAI target	CQL restriction
Exact complete map	FULL(labels)	labels = {‘region’: ‘eu-west-1’, ‘owner’: ‘payments’}
A map value anywhere in the map	VALUES(labels)	labels CONTAINS ‘payments’
Whether a map key exists	KEYS(labels)	labels CONTAINS KEY ‘region’
A particular map entry	ENTRIES(labels)	labels[‘region’] = ‘eu-west-1’

If the application needs services with a particular region label an entries index lets it match the key and value together.

CREATE INDEX service_region_label_sai
    ON catalogue.service_profiles (ENTRIES(labels))
    USING 'sai';

SELECT service, owner_team
FROM catalogue.service_profiles
WHERE labels['region'] = 'eu-west-1';

This query returns payments-api and analytics-api together with ledger-api because each has the requested region entry in its labels. For an attribute used by a core high-volume query you should also compare a dedicated column and access table before choosing to keep it in an indexed map.

A frozen collection is replaced as a whole when updated so replacing it also changes its indexed content as a whole. Test that write cost for a large or frequently updated collection alongside the element searches you want to support.

Use the following questions to decide which data and query cases belong in the test before creating the index.

How selective is the element or value in the real dataset?
How many rows can the predicate match when the table is largest?
How often is the frozen collection written or replaced?
Does the query replace a defined access table or try to discover arbitrary data across the ring?

Selectivity and result count determine how much base data Cassandra has to read while replacement frequency determines index-maintenance work. A status value present on most rows can still create a large candidate set with a correctly functioning index. Comparing that query with a defined access table helps you judge which design fits the application’s requirements.

Index selection and query behaviour

With CASSANDRA-18112 you can direct a query towards a particular available secondary index through CQL. That lets you compare index choices during tuning or while two indexes coexist in a schema migration. It also gives you a way to investigate a plan that selected an unexpected index from its cardinality estimates.

Choosing a less selective index can increase the candidate rows and partitions Cassandra has to read and filter. Once the application supplies that choice it needs to be tested again when the schema or data distribution changes.

SAI normally starts with the most selective indexed predicate and can intersect other indexed expressions joined by AND. Cassandra then reads the matching base-table data and evaluates any remaining conditions against it. An explicit index choice changes where that process starts while replica reads and tombstone handling still contribute to its cost.

Query shape	What to test	Failure pattern to avoid
One selective indexed value	Candidate partitions, replica reads, result size, and latency at expected concurrency.	Calling a query fast from a small test data set when its common production value matches millions of rows.
Two indexed predicates joined with `AND`	Individual and intersected selectivity, coordinator CPU, replica work, and paging behaviour.	Assuming two indexes automatically make a broad query selective without measuring the actual intersection.
Manual index selection	The chosen index against Cassandra’s default selection on the same data.	Leaving a hint in application code after the data distribution or available indexes change.
New index on an existing table	Build state, disk growth, index queryability, write rate, flush rate, and compaction impact.	Sending production traffic to an index before every relevant SSTable is built and queryable.

The syntax lets you specify included and excluded indexes with Cassandra required to use every included index or reject the query. Excluded indexes aren’t considered which can make ALLOW FILTERING necessary for a restriction that would otherwise use one.

CASSANDRA-20888 adds validation around those hints to check the choices a query supplies. Exclusion also lets you test filtering when only one applicable index exists without creating another index just for the comparison.

CREATE INDEX service_tier_sai
    ON catalogue.service_profiles (tier)
    USING 'sai';

CREATE INDEX service_region_sai
    ON catalogue.service_profiles (region)
    USING 'sai';

SELECT service, owner_team, region
FROM catalogue.service_profiles
WHERE tier = 'critical' AND region = 'eu-west-1'
ALLOW FILTERING
WITH included_indexes = {service_tier_sai}
AND excluded_indexes = {service_region_sai};

This query returns payments-api and ledger-api by using the tier index to find candidates before filtering their region. The region index is excluded so Cassandra has to check that condition against the returned base data. Compare both paths with representative data because a quick result on this small catalogue won’t establish the cost of a much broader tier in production.

Use system_views.sai_column_indexes for column-level index state and system_views.sai_sstable_indexes for the corresponding SSTable information. system_views.sai_sstable_index_segments supplies segment-level detail so these are the views to inspect instead of a nonexistent system_views.indexes table. Keep that state with table and host metrics throughout the test so you can investigate changes after the schema migration too.

LIKE filtering

CASSANDRA-17198 expands LIKE support with filtering and updates where those predicates are accepted. The pattern still has to be evaluated against candidate data so you’ll want to know how much data reaches that filtering step.

Use a partition key or clustering restriction to bound the candidates or an index whose selectivity you’ve measured. Then test the pattern against that candidate set because a leading wildcard can still require extensive filtering across matching data. A query needing broad pattern searches should be compared with a search index designed for that access.

Supported wildcard forms put % at either end of the term or at both ends. Alpha3 rejects an interior wildcard such as 'pay%ments' through the validation added in CASSANDRA-21068.

Pattern	Query shape
’payments%‘	Prefix match
’%payments’	Suffix match
’%payments%‘	Contains match
’payments’	Exact match

In the service catalogue the tier index narrows the candidate rows before Cassandra applies the owner-name prefix to those rows.

SELECT service, owner_team
FROM catalogue.service_profiles
WHERE tier = 'critical' AND owner_team LIKE 'pay%'
ALLOW FILTERING;

The query returns payments-api and ledger-api because their owners match the prefix within the critical-tier candidates. Its cost depends on how many rows belong to that tier so check the candidate count as the catalogue grows.

Try the patterns users actually submit and include a common pattern that matches a large share of the data. Compare rows examined and result count with p95 and p99 latency while testing paging and measuring coordinator and replica CPU. A query returning ten rows in development can require much more work once its pattern appears across a production dataset.

Schema constraints

CASSANDRA-20563 rewrites the constraint framework so function checks can take arguments without repeating the column specification. You can use NOT NULL and scalar comparisons alongside LENGTH and OCTET_LENGTH checks or validate content with REGEXP and JSON. Custom constraints use the server-side SPI and NOT NULL is prohibited on primary-key columns.

A constraint becomes part of the table schema and applies to every writer using that schema. It can reject an invalid value before storage but it can also reject a mutation that an older version of the application expected to succeed. Check the writer behaviour when planning the schema change so those rejections are handled deliberately.

In the example below built-in function checks omit the repeated column argument while scalar comparisons still identify the column being constrained.

Both versions of that constraint syntax belong to the unreleased 6.0 development line with CASSANDRA-20563 replacing the earlier repeated-column form. Cassandra 5.0 doesn’t provide that constraint framework so it isn’t a baseline for the old syntax.

CREATE TABLE catalogue.deployment_requests (
    request_id text PRIMARY KEY,
    service text CHECK NOT NULL,
    owner_team text CHECK LENGTH() <= 64,
    replica_count int CHECK replica_count >= 1 AND replica_count <= 12
);

INSERT INTO catalogue.deployment_requests
    (request_id, service, owner_team, replica_count)
VALUES ('deploy-1042', 'payments-api', 'payments', 3);

INSERT INTO catalogue.deployment_requests
    (request_id, service, owner_team, replica_count)
VALUES ('deploy-1043', null, 'payments', 0);

Cassandra accepts the first mutation because it satisfies the declared checks and rejects the second for violating them. The same checks apply whether an application or a script or an administrative tool submits the write.

Constraint use	Useful example	Required migration check
`NOT NULL`	A value that application code must always provide after a migration is complete.	Find historical clients and partial-update paths that currently omit the value.
Scalar bounds	A non-negative capacity, a bounded enum-like integer, or a valid numeric range.	Test all application languages and prepared-statement bindings for rejected values and error handling.
Regular expression	A compact identifier with a stable permitted format.	Test the regex against existing valid data and realistic input volume; do not use a costly expression on an uncontrolled field.
Custom SPI constraint	A domain rule that must be enforced by Cassandra for every write.	Review code distribution, classpath consistency, upgrade ordering, performance, failure mode, and rollback before enabling it.

Before adding a constraint to an existing table check whether its stored data would satisfy the new rule. Decide whether that data needs a backfill or can remain as it is and whether writers need a staged release before enforcement begins. Test the application’s response to rejected mutations so a new constraint doesn’t simply turn into a generic server error for its users.

Schema comments let you keep the reason for a table or partition choice close to its definition while security labels carry a classification with the schema. That information helps the next person maintaining the service understand the choices made when it was created. You’ll still need data-classification procedures and access reviews alongside approval for migrations that change those definitions.

Cassandra 5.0 and 6.0 validation

Test	Cassandra 5.0 baseline	Cassandra 6.0 comparison	Measurements
Frozen collection query	Whole-collection SAI capability or an explicitly maintained access table.	Value or element indexing on the representative frozen collection.	Index size, build time, write and flush rate, compaction cost, candidate count, p95/p99 latency, and result size.
Multiple-index query	Cassandra’s normal index selection.	Default selection and one controlled manual selection on the identical data.	Index chosen, partitions and rows examined, coordinator CPU, replica load, latency, and paging.
LIKE predicate	Existing query model without the new filtering path.	Realistic patterns with both selective and common forms.	Result counts, latency, scanned work, timeouts, and impact on other queries.
Constraint deployment	Application validation before a write reaches Cassandra.	Server-side constraint in a staging schema migration.	Accepted and rejected mutations, client error handling, write latency, compatibility of old clients, and rollback path.
Index lifecycle	Existing table and normal compaction behaviour.	Create, build, query, rebuild, and drop the index under traffic.	`system_views.sai_column_indexes`, `system_views.sai_sstable_indexes`, `system_views.sai_sstable_index_segments`, disk use, index queryability, writes, flushes, compaction, and query continuity.

For the comparison keep the CQL and schema changes with index state and table metrics in AxonOps. Compare read and write latency with SSTables touched and tombstones scanned while checking disk use and compaction. Logs and deployment events help you see whether a later change in performance followed the schema update or another operation.

Contributors

Mike Adamson proposed indexing frozen collection values and elements with Sunil Ramchandra Pawar assigned to the implementation. Maxwell Guo raised manual secondary-index selection with Caleb Rackliffe assigned to the work while Benjamin Lerer proposed the LIKE filtering expansion with Pranav Shenoy assigned. Stefan Miklosovic proposed and implemented the constraint-framework rewrite that changes how those checks are declared.

I’m grateful to those contributors and everyone testing the SAI read and write paths alongside schema compatibility. Tests of migrations and incomplete or corrupted index files are as necessary as the examples that run successfully. Review and documentation depend on further effort alongside CI and release work and reports from users whose workloads expose unexpected cases.

Series

Sources

All articles

Demo Sandbox