Materialized views
|
Materialized views are considered experimental and not recommended for production use. |
A materialized view (MV) is a pseudo-table built from data in another table with a new primary key and new properties. Queries are optimized by the primary key definition.
Typically, you create a table for a query, and then create a new table with the same data if a different query is needed. Client applications write to all tables where data is duplicated. With MVs, all writes go to the one base table, and the associated MVs are updated automatically.
MVs are defined at the schema level. Each MV has a different primary key, which enables query patterns that aren’t possible with the base table’s primary key. Additionally, MVs can contain the same data as the base table or a subset of that data, but they cannot contain data that isn’t present in the base table.
Propagation of updates to materialized views
The following workflow illustrates how the database propagates updates from the base table to its materialized views:
-
The coordinator node receives an update from a client for the base table and forwards it to the replica nodes.
If the
cassandra.mv_enable_coordinator_batchlogproperty is enabled, the coordinator writes abatchlogto aQUORUMof batchlog nodes before forwarding the write request to the replicas. Thebatchlogcontains records of the base table writes. This configuration provides better protection against a coordinator failing in the middle of a request, but slows the view write operation considerably. -
Upon receiving an update from the coordinator for the base table, each replica node completes the following tasks:
-
Generate view updates for each materialized view of the base table.
A local read on the relevant base table row determines if a previous view row must be removed or modified.
While generating the view update, a local lock is acquired on the base table partition to ensure that the view updates are serialized. This lock is released after updates to the view are propagated to the replicas and the base updates are applied locally.
-
Deterministically compute the paired view replica for each view update to distribute view replication work among base replicas.
If the base replica is also a view replica, the base replica chooses itself as the paired view replica, and then applies the view update synchronously. Otherwise, the update is written synchronously to the local
batchlogfor durability, and sent asynchronously to the remote paired view replica. For more information, see Consistency considerations. -
Acknowledge the write to the coordinator node.
-
After receiving acknowledgement of all asynchronous paired view writes, remove the local
batchlog.If any asynchronous writes aren’t acknowledged, the replica will replay the
batchlogat a later time to attempt to propagate the unacknowledged view updates. If a replica is down duringbatchlogreplay, one hint is written for each mutation.
-
-
After the coordinator node receives acknowledgement from all nodes (based on consistency level), the coordinator node returns a successful write response to the client.
Performance considerations
Materialized views allow fast lookup of data using the normal read path. However, materialized views do not have the same write performance as normal table writes because the database performs an additional read-before-write operation to update each materialized view. This additional work is required to ensure that all correct state changes to a given row are applied to materialized views, especially regarding concurrent updates. By using materialized views, performance is traded for data correctness.
To complete an update, the database performs a data consistency check on each replica. A write to the source table incurs latency (approximately 10 percent for each materialized view), and the performance of deletes on the source table also suffers. If a MV has a different partition key, writing data requires network communication with other nodes that are responsible for corresponding token range.
If a delete on the source table affects two or more contiguous rows, this delete is tagged with one tombstone. However, these same rows may not be contiguous in materialized views derived from the source table. If they are not, the database creates additional tombstones in the materialized views.
When creating a materialized view on a table with existing rows, the entire materialized view must be built.
This can take some time, depending on the amount of data.
You can check the status of this job with the nodetool viewbuildstatus command.
With respect to storage, MV data is stored in each view, similar to an index or table. Each MV increases the total size of the data stored. This can be significant when there are many MVs or the base table is large.
Consistency considerations
As described in Propagation of updates to materialized views, each base table replica writes the view updates locally or writes a local batchlog before returning the base table write.
If the base table replica cannot update a remote view during the write operation, the replica retries the update during batchlog replay.
This mechanism ensures that all changes to each base table replica are reflected in the views, unless data loss occurs in the base table replica.
The write operation for the view replica is asynchronous to ensure availability is not compromised.
A consequence is that a read operation for a view might not immediately see a successful write to the base table until the write operation is propagated by the base replicas.
Under normal conditions, data is quickly made available in the views.
Use the ViewWriteMetrics metric to track the view propagation time.
Scenario that can result in base-view inconsistency
In an ordinary CQL table, when a row is successfully written to consistency level replicas, data loss can occur if those replicas become permanently unavailable before the update is propagated to the remaining replicas. The following example illustrates this scenario.
-
Write to a table with a replication factor of three (RF=3) and a consistency level of ONE.
-
The base replica is also the coordinator node.
-
The coordinator responds to the client that the write was successful.
-
The machine hosting the coordinator node dies.
In the case of materialized views, the previous example carries additional implications. If the base table (coordinator node) successfully wrote the view update to another node, the row will exist only in the view but not in the base table, creating an orphaned view row.
Another scenario that can create an orphaned view row is when the base table row loses all replicas without repair between failures. If a view row loses its replicas, the base table row will not have its corresponding view row.
To avoid those situations, write to base tables with materialized views using consistency levels greater than ONE (such as LOCAL_QUORUM).
Alternatively, use the -Dmv_enable_coordinator_batchlog=true option to provide better protection against a coordinator failing in the middle of a request.
However, be aware that using the -Dmv_enable_coordinator_batchlog=true option increases view write latency considerably.
Known limitations for materialized views
The following limitations are known for materialized views:
- Desynchronized MVs require manual rebuilding
-
It is possible for a MV to become desynchronized from the base table and miss writes. There is no way to automatically detect and fix permanent inconsistency between the base and the view (CASSANDRA-10346). If a table is desynchronized, you must rebuild it with
nodetool rebuild_view. - Limitations on repair operations
-
Incremental repair is not supported on base tables with materialized views (CASSANDRA-12888).
Additionally, regular repair operations don’t apply to MVs. You must use specific MV operations like
nodetool rebuild_viewto repair the MVs.For maintenance recommendations, see Materialized view maintenance.
- Not recommended for production
-
MVs are considered experimental and not recommended for production use. If no other approach (secondary indexes or tables-as-indexes) supports your use case, materialized views might be considered as an option.
Before using materialized views, review the limitations, consistency considerations, and performance impacts. Then, thoroughly test MVs against your application requirements to determine if they are suitable for your environment. After materialized views are deployed, regular maintenance repairs are required to ensure that base tables and views are consistent.
When comparing MVs and manually denormalized tables, consider the level of effort required to ensure consistency between views or tables in the event of a failure or disaster recovery situation. With MVs, concurrent updates require additional processes and effort, such as row locking, view repair, and paired view replication. In practice, no guarantees are lost when using built-in materialized views versus manually denormalized tables.
In cases where consistency is less important or data is written once and never changed, duplicate writes to multiple tables are more performant than MVs.
Materialized view maintenance
Follow these guidelines to ensure materialized view health and consistency:
-
Run anti-entropy repair on base tables with materialized views at least every
gc_grace_secondsto ensure tombstones are propagated to materialized views and avoid data resurrection. -
Run
nodetool rebuild_viewperiodically to detect and fix silent data corruption in materialized views. You can run this operation less frequently than repairs on base tables.
