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

The Cassandra 6.0 line extends both Storage-Attached Indexing and CQL schema validation. These are useful additions, although neither turns Cassandra into a database where a query can be designed after the data is written. Partitioning, expected result size, write rate, data distribution, cardinality, tombstones, and the volume of data touched by an index still determine whether a request is a good fit for the cluster.

The work is most useful when it removes a specific limitation from an otherwise sound model. It is least useful when it is used to defer modelling decisions that should have been made before an application depends on a high-volume query.

Cassandra 6.0 is pre-release software. This post reflects 6.0-alpha3 and must be checked again against the release notes and upgrade documentation for the exact version being deployed.

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 is not a separate global search service. It maintains an index alongside the memtables and SSTables that contain the base table’s data. That storage-local design is important when judging what an index can and cannot do for a query.

When Cassandra receives a mutation for an indexed column, SAI adds the indexed term and the row’s primary-key information to an in-memory index associated with the current memtable. The incremental memory use counts against the memtable’s heap budget, so adding indexed columns can cause earlier flushes and smaller SSTables. When the memtable is flushed, SAI writes the attached index components with the SSTable. String terms are stored in an on-disk byte-ordered trie with postings, while numeric terms use a KD-tree structure with postings. The base SSTable and its attached index are then immutable until a future compaction replaces them.

Compaction has to rebuild the SAI components as it writes the merged SSTable. This means an index is not an isolated query feature. It has a write cost, a flush cost, a compaction cost, disk components, memory accounting, build and rebuild activity, and operational state that should be monitored with the 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.

The final filtering stage is a necessary detail. An index can identify partitions or rows that may match, but Cassandra still has to account for tombstones, newer updates, and rows within a wide partition that do not satisfy every condition. Indexing reduces the candidate set. It does not make an unbounded result set inexpensive, and it does not erase the cost of reading a large number of matching partitions.

Frozen collection indexing

CASSANDRA-18492 extends SAI to index values and elements inside frozen collections. Before this work, a frozen collection was indexed as the serialized value of the whole collection. That is useful when the application needs equality or a lookup on the complete stored collection, but it is not useful when the application needs to find rows that contain one particular element or value.

The distinction is easiest to see with a frozen set.

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 following table and index use the new value target. Cassandra 5.0 requires filtering for the CONTAINS query shown here, while Cassandra 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. The predicate does not require the complete frozen set to equal a supplied set. It finds the rows whose frozen collection contains the requested value.

Frozen maps have distinct index targets for distinct query shapes.

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’

For example, an application that needs an exact region label can use an entries index rather than searching every value in the map.

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

That query returns payments-api, analytics-api, and ledger-api. It does not mean that every metadata field should be placed in a frozen map and indexed. A dedicated column and access table remain the clearer model when an attribute is a core, high-volume access path.

The feature is useful for a query requirement that genuinely needs to search a structured frozen value. It does not make a large or frequently rewritten collection free to index. Frozen collections are updated as a whole, and a mutation that replaces the collection changes the indexed content as a whole as well.

Before adding the index, answer four questions.

How selective is the element or value in the real data set?
How many rows can one predicate match at peak?
How often is the frozen collection written or replaced?
Is the query replacing a clearly defined access table, or is it trying to discover arbitrary data across the ring?

The first two questions control read cost. The third controls index-maintenance cost. The fourth determines whether this belongs in a Cassandra table design at all. A status value present on most rows, for example, can produce a very large candidate set even when the index itself is functioning correctly.

Index selection and query behaviour

CASSANDRA-18112 adds manual secondary-index selection at the CQL level. It gives a team a way to direct a query towards a specific available index instead of relying entirely on Cassandra’s estimated cardinality choice. This is useful during controlled tuning, a schema migration where two paths coexist, or an investigation into a plan that is choosing an unexpected index.

It is not an index-performance override. Selecting an index with poor selectivity can cause more work by expanding the set of candidate rows and partitions that must be read and filtered. It can also turn a schema change into application behaviour that needs to be maintained, tested, and reconsidered when data distribution changes.

SAI’s normal coordinator behaviour is to identify the most selective indexed predicate, use it to narrow the search, and intersect additional indexed expressions connected with AND. Cassandra then reads the matching base-table data and applies remaining conditions. Explicit index selection changes the entry point into that process; it does not make the rest of the distributed read, replica work, tombstone handling, or result-size cost disappear.

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 supplies an included set and an excluded set. Cassandra must use every included index or reject the query. An excluded index is not considered, which can make ALLOW FILTERING necessary for its restriction.

CASSANDRA-20888 hardens validation around index hints. Excluding an index also has a useful narrow case when a table has only one applicable index and an operator wants to test the unhinted filtering path without adding another index simply for 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};

The data above returns payments-api and ledger-api. The hint makes Cassandra enter through the tier index and post-filter the region restriction because the region index was excluded. This is useful when comparing the two paths on representative data or while replacing an index implementation. It should not be used to force a broad path into production because it happens to be faster against a small test data set.

The SAI virtual tables system_views.sai_column_indexes, system_views.sai_sstable_indexes, and system_views.sai_sstable_index_segments expose the index state needed during this work. They show per-column, per-SSTable, and segment-level index information rather than one generic system_views.indexes view. That operational state should be collected with ordinary table and host metrics rather than treated as a one-time schema migration check.

LIKE filtering

CASSANDRA-17198 expands support for LIKE predicates used with filtering and updates validation around their placement. A pattern predicate still has to be evaluated against data, and a broad pattern can cause a large amount of work even when Cassandra accepts the syntax.

Use LIKE where the candidate data set is already bounded by a partition key, clustering restriction, or an index whose selectivity has been measured. Treat it with the same caution as any other filtering condition when it could require scanning a broad portion of the data. A leading wildcard is not a substitute for a search index designed around the pattern being requested.

The supported wildcard forms place % at one or both boundaries of the term. An interior wildcard such as ‘pay%ments’ is rejected in alpha3 by CASSANDRA-21068.

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

The service catalogue makes the filtering case concrete. The tier index limits the candidate rows, and Cassandra applies the prefix pattern to the owners of those rows.

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

This returns payments-api and ledger-api. The index on tier does not make the owner_team pattern cheap by itself. The query remains suitable only while the critical-tier candidate set is demonstrably bounded.

The correct test includes patterns that users actually issue, not only one convenient prefix. Compare result count, rows examined, p95 and p99 latency, paging, coordinator CPU, replica CPU, and the behaviour of a low-selectivity pattern. A query that returns ten rows in development can behave very differently when its common pattern reaches a large fraction of a production data set.

Constraints and schema intent

CASSANDRA-20563 rewrites the constraint framework so a definition no longer has to repeat the column specification. It also enables arguments for constraint functions. The resulting CQL can express NOT NULL, type-specific scalar checks, LENGTH, OCTET_LENGTH, REGEXP, JSON, and custom constraints through the server-side SPI. NOT NULL cannot be applied to a primary-key column.

Constraints belong to the table schema, so they are propagated as schema and become part of the database contract for every writer. That is valuable when a value must be rejected before it is stored, rather than discovered later by an application job or during an incident. It also means a constraint is a write-path change and should be deployed as carefully as any other change that can cause a previously valid mutation to fail.

The revised syntax removes the repeated column argument from built-in function constraints. Scalar comparisons still name the column they constrain.

The repeated-column constraint syntax existed during 6.0-line development before the CASSANDRA-20563 rewrite. It is not a Cassandra 5.0 baseline.

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

The first mutation is accepted. Cassandra rejects the second because it violates the declared constraints. This protects the stored contract regardless of which application, script, or administrative tool submits the mutation.

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.

An existing table may contain data that would not satisfy a new constraint. The migration plan should decide whether old data remains acceptable, needs a backfill, or requires a staged application release before the constraint is enforced. Application code also needs a deliberate response for rejected mutations. Returning a generic 500 error to an end user because a server-side constraint was introduced is not a completed schema migration.

Schema comments and security labels are smaller improvements, although they are useful when a table has a long operational life. A comment can retain the reason for a table, partition choice, or access path close to the schema. A security label can carry a classification signal with the schema. Neither replaces a data-classification program, access review, or migration approval, but both make it harder for that intent to be lost when the original authors are no longer maintaining the service.

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.

AxonOps should retain the CQL/schema change with the index state, table metrics, read and write latency, SSTables touched, tombstones, disk use, logs, and deployment events. That makes it possible to see whether an apparent query improvement has shifted cost into write pressure or compaction, and whether a later latency change followed the schema change or a different operational event.

Contributors

Mike Adamson proposed frozen collection value and element indexing, and Sunil Ramchandra Pawar is assigned to the implementation. Maxwell Guo raised manual secondary-index selection, with Caleb Rackliffe assigned to the work. Benjamin Lerer proposed the filtering expansion for LIKE, with Pranav Shenoy assigned. Stefan Miklosovic proposed and implemented the constraint-framework rewrite.

These CQL features also rely on people who maintain SAI’s read and write path, build compatibility tests, test schema migrations and corrupted or incomplete index files, review changes, improve documentation, operate CI, prepare releases, and report the workloads that expose edge cases. I am grateful to everyone involved in that work.

Demo Sandbox

Thank You from AxonOps: Current London 2026 Recap