Prepare to use materialized views
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 original 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 steps illustrate how Apache Cassandra® and DataStax Enterprise (DSE) propagate updates from a original table to its materialized views:
-
The coordinator node receives an update from a client for the original table and forwards it to the configured replica nodes.
-
When the
cassandra.mv_enable_coordinator_batchlogproperty is enabled, the coordinator will write a batchlog to QUORUM nodes containing the original table write before forwarding them to the replicas. 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 original table, each replica node completes the following tasks:
-
Generate view updates for each materialized view of the original table.
-
A local read is completed in the original table row to determine if a previous view row must be removed or modified.
-
A local lock is acquired on the original table partition when generating the view update to ensure that the view updates are serialized. This lock is released after updates to the view are propagated to the replicas and base updates are applied locally.
-
-
After generating view updates, deterministically compute its paired view replica for each view update, so that the view replication work is distributed among base replicas.
-
If the base replica is also a view replica, the base replica chooses itself as the paired view replica, and applies the view update synchronously.
-
Otherwise, the update is written synchronously to the local batchlog for durability, and sent asynchronously to the remote paired view replica.
-
-
Acknowledge the write to the coordinator node.
-
After receiving an acknowledgement of all asynchronous paired view writes, remove the local batchlog. Otherwise, replay the batchlog at a later time to propagate the view update to the replica. If a replica is down during batchlog replay, one hint is written for each mutation.
-
-
After receiving an acknowledgement from all nodes (based on consistency level), the coordinator node returns a successfully 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
Each original table replica writes the view updates locally (when it is also a view replica), or writes a local batchlog before returning the original table write (as described in 2.b). If the original 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 original table replica are reflected in the views, unless data loss occurs in the original 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 original 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 original table (coordinator node) successfully wrote the view update to another node, the row will exist only in the view but not in the original table, creating an orphaned view row.
Another scenario that can create an orphaned view row is when a original table row loses all replicas without repair between failures. If a view row loses its replicas, the original table row will not have its corresponding view row.
To avoid those situations, write to original 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.
|
Using the |
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 original 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. - 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 original 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.
