A materialized view is a stored table-like copy of a base table, arranged with a different primary key. It gives ScyllaDB an efficient access path for a query that the base table cannot serve directly, trading extra disk storage for more query flexibility. section 7.3
Use a view when a new query pattern would otherwise require filtering many rows. For example, article summaries may need lookup by date, author, and score. Manually denormalized tables can support those queries, but application writes must keep every copy synchronized. A materialized view moves that replication work into ScyllaDB. section 7.2.3 section 7.3.2
Create a materialized view from three pieces:
- Choose a base table that already contains the fields the new query needs. section 7.3.1
- Define a new primary key: include the base table primary-key fields and the new partition key used by the query. section 7.3
- Write a SELECT clause for the stored columns and a WHERE clause that restricts every view primary-key field with at least IS NOT NULL. section 7.3.1
- Add no more than one base-table non-primary-key column to the view primary key. section 7.3.1
Listing 7.1 article_summaries_by_score materialized view
cqlsh:reviews> CREATE MATERIALIZED VIEW #1
article_summaries_by_score_view #2
AS
SELECT score, id, title, author_name, image #3
FROM article_summaries_by_date #4
WHERE score IS NOT NULL #5
AND date_bucket IS NOT NULL
AND id IS NOT NULL
PRIMARY KEY(score, date_bucket, id); #6
This CQL definition rebuilds score-based article summaries from a date-based base table. Score becomes the partition key, while date_bucket and id remain in the key so rows can be located correctly. section 7.3.1
Querying articles by score
After creating article_summaries_by_score_view, query it as you would a regular table: SELECT * FROM article_summaries_by_score_view WHERE score = 97. Rows present when the view is created are copied into it; later base-table inserts are automatically replicated asynchronously, usually almost immediately unless the cluster is overloaded. section 7.3.2
Treat changes to a view partition key carefully. ScyllaDB must move the row to a potentially different node, delete the old value, and lock during the operation to avoid concurrent-update races. The lock protects against data loss but increases the update cost. section 7.3.2
Indexes are related but behave differently. An index is implemented as a materialized view, yet queries still target the base table; ScyllaDB uses the index to find matching primary keys and then reads the matching base-table rows. This avoids a full scan but can require two read stages. section 7.3.3 section 7.4
