AnalyticDB:Real-time materialized views

Synchronous mode

The synchronous mode of real-time materialized views in AnalyticDB for PostgreSQL uses STATEMENT consistency. When a statement on the base table succeeds, the data in the real-time materialized view is updated synchronously. The update logic is as follows:

• The database kernel first updates the base table, then updates the materialized view. If the base table update fails, the materialized view is not changed.

• If the materialized view update fails, the base table update also fails. The base table data remains unchanged, and the statement returns a failure.

If you use an explicit transaction (such as BEGIN + COMMIT), the materialized view update is also part of the same transaction after the base table update succeeds:

• If the Isolation levels is set to READ-COMMITTED, the updates to the materialized view are not visible to other transactions until the transaction is committed.

• If the transaction is rolled back, both the base table and the materialized view are rolled back.

Version limits

In the following versions, real-time materialized views use synchronous mode by default.

• AnalyticDB for PostgreSQL instances with a kernel version earlier than v7.0.6.9 (excluding v7.0.6.9).

• AnalyticDB for PostgreSQL instances with a kernel version earlier than v6.6.2.5 (excluding v6.6.2.5).

To switch the default mode of real-time materialized views, submit a ticket.

Asynchronous mode

In asynchronous mode, write operations on the base table are completed first, and the transaction can be committed normally. The data in the real-time materialized view is not immediately updated. After the transaction is committed, the database kernel automatically schedules incremental updates for the real-time materialized view in the background. Concurrent write transactions are handled by the database. During the update process, the database may batch updates. For all concurrent writes to the base table, the database kernel updates the real-time materialized view to ensure eventual data consistency. Even with concurrent writes in asynchronous mode, the database kernel guarantees that the data in the real-time materialized view is eventually consistent with the base table. Since updates are typically completed within seconds under normal system load, asynchronous mode meets the requirements of most real-time data warehousing scenarios.

Version limits

In the following versions, newly created real-time materialized views use asynchronous mode by default.

• AnalyticDB for PostgreSQL instances with a kernel version of v7.0.6.9 or later.

• AnalyticDB for PostgreSQL instances with a kernel version of v6.6.2.5 or later.

To switch the default mode of real-time materialized views, submit a ticket.

Limitations

AnalyticDB for PostgreSQL restricts the query statements that can be used to create real-time materialized views. You can only create real-time materialized views with the following query statements.

• Queries can include most filtering and projection operations, as well as most PostgreSQL built-in functions and UDFs.

• Queries can include most aggregate functions and window functions.

• If the query contains JOIN, both INNER JOIN and OUTER JOIN (LEFT, RIGHT, FULL) are supported.

• If the query contains OUTER JOIN, the JOIN condition only supports AND connections. OR connections are not supported, and the columns on both sides of the equijoin condition cannot come from the same table.

• Only simple queries, FROM subqueries, and UNION ALL statements are supported. Correlated subqueries are not supported.

After creating a real-time materialized view on the base table, DDL operations on the base table are restricted as follows.

• You can only execute DROP TABLE on the base table with the CASCADE option.

• ALTER TABLE cannot delete or modify columns referenced by the materialized view.

Use cases

We recommend that you use real-time materialized views in scenarios with the following characteristics.

• Based on the incremental update and nested cascading capabilities of real-time materialized views, you can easily build real-time ETL pipelines without the need for an external scheduling system. You can create real-time materialized views on upstream base tables, and then create downstream cascaded real-time materialized views to produce real-time ETL results, such as real-time wide tables and real-time aggregations, to accelerate query analysis.

• Real-time materialized views can significantly accelerate query results, especially when the query result contains only a small number of rows or columns relative to the base table, or when obtaining the query result requires extensive computation, including:

  • Highly selective filter conditions.

  • Highly concentrated aggregate functions.

  • Semi-structured data analysis.

  • Aggregation operations that take a long time to compute.

• The base table of the view contains a large amount of data, and the incremental update volume is much smaller than the total data volume.

Real-time materialized views are suitable for all scenarios where standard materialized views are applicable. Compared with standard materialized views, real-time materialized views provide high data consistency. When the base table changes, real-time materialized views are updated with low performance overhead (incremental). In contrast, standard materialized views require a manual full refresh every time the base table changes to ensure data consistency, which often incurs significant performance overhead. Therefore, when the base table has a certain degree of changes or even requires streaming imports, real-time materialized views have a significant advantage over standard materialized views.

Cost of real-time materialized views

Real-time materialized views are similar to indexes maintained in real time. While they significantly optimize query performance, they also have some impact on write performance. The key factors that affect the write performance of real-time materialized views are:

• The complexity of the query statement and the number of cascading layers. A single-layer real-time materialized view on a single table with a simple JOIN can support tens of thousands to hundreds of thousands of row writes per second on instances with different configurations. Complex JOIN and multi-layer nested scenarios consume more instance computing resources, and write performance decreases proportionally.

• Real-time materialized views that contain JOIN may experience write amplification. For example, in a real-time materialized view that joins a fact table with 1 billion rows and a dimension table with 10,000 rows, the large fact table usually achieves high write performance. However, changes to the smaller dimension table can cause a proportional decrease in write performance due to write amplification during incremental computation.

• The size and resources of the AnalyticDB for PostgreSQL instance. Real-time materialized views use instance resources during incremental computation and writes. The instance size and resources affect the write efficiency. When the real-time write performance does not meet your business requirements, you can increase computing resources for better write performance.

Create and delete real-time materialized views

• Use the CREATE INCREMENTAL MATERIALIZED VIEW command to create a real-time materialized view named mv. Example:

  CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base WHERE id > 40;

• Use the DROP MATERIALIZED VIEW command to delete the materialized view mv. Example:

  DROP MATERIALIZED VIEW mv;

Examples

1. Create a base table.

   CREATE TABLE test (a int, b int) USING HEAP DISTRIBUTED BY (a);

2. Create a real-time materialized view.

   CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM TEST WHERE b > 40 DISTRIBUTED BY (a);

3. Insert data into the base table.

   INSERT INTO test VALUES (1, 30), (2, 40), (3, 50), (4, 60);

4. Query the base table.

   SELECT * FROM test;

   The result is as follows:

    a | b
   ---+----
    1 | 30
    2 | 40
    3 | 50
    4 | 60
   (4 rows)

5. Query the materialized view.

   SELECT * FROM mv;

   The materialized view is updated. The result is as follows:

    a | b
   ---+----
    3 | 50
    4 | 60
   (2 rows)