Use Incremental Materialized Views

This document describes the use cases, usage methods, restrictions, and notes for incremental materialized views in SynxDB.

An incremental materialized view is a special form of a materialized view. When data in the base table changes (for example, through insert, update, or delete operations), an incremental materialized view does not need to recompute all the data in the entire view. Instead, it only updates the parts that have changed because the last refresh. This can save a significant amount of computational resources and time, leading to a notable performance improvement, especially when dealing with large datasets.

Comparison with regular views

Regular view: A regular view does not store data. Instead, it dynamically generates the result set based on its defining SQL statement every time it is queried. This means the view’s data must be recomputed for each query, which can be very time-consuming when dealing with large amounts of data.
Incremental materialized view: An incremental materialized view pre-computes and stores its result set, which reduces the computational load at query time. Furthermore, being ‘incremental’, it only needs to update the affected parts when the underlying base data changes, rather than recomputing the entire dataset.

Use cases

Accelerating queries with intermediate result sets.
Read-heavy, write-light scenarios.

Usage example

You can use the SQL command CREATE INCREMENTAL MATERIALIZED VIEW to create an incremental materialized view. The full syntax is as follows:

CREATE [INCREMENTAL] MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

If you specify WITH NO DATA, the incremental materialized view is created without data, and querying it reports materialized view "..." has not been populated. Run REFRESH MATERIALIZED VIEW once before using it.

The following example demonstrates how to create an incremental materialized view for tables in SynxDB.

Create tables t0 and t1.

CREATE TABLE t0 (a int) DISTRIBUTED BY (a);
CREATE TABLE t1 (a int) DISTRIBUTED BY (a);

Create an incremental materialized view m based on the two tables. Both statements below create a view named m, so run one or the other. Running both makes the second one fail with relation "m" already exists. The steps that follow use the simple view created by the first statement.
- Create a simple materialized view. The following statement creates an incremental materialized view named m. It selects all columns from table t0 and distributes the data based on the values in column a. This means the view m will store a snapshot of the data from table t0 and can be incrementally updated as the data in t0 changes.
```
CREATE INCREMENTAL MATERIALIZED VIEW m AS SELECT * FROM t0 DISTRIBUTED BY (a);
```
- Create a materialized view with a join operation. The following statement also creates an incremental materialized view named m, but this time by joining tables t0 and t1. It selects all values of t0.a, but only when the value of t0.a equals the value of t1.a. Similarly, the view’s data is distributed based on the values in column a.
```
CREATE INCREMENTAL MATERIALIZED VIEW m AS SELECT t0.a FROM t0, t1 WHERE t0.a = t1.a DISTRIBUTED BY (a);
```

Insert data into the tables. The RETURNING * clause returns the inserted rows after the insertion.

INSERT INTO t0 VALUES (5);
INSERT INTO t1 VALUES (5);
INSERT INTO t0 VALUES (8) RETURNING *;

View the structure of the materialized view m.

\d+ m

                                      Materialized view "public.m"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 a      | integer |           |          |         | plain   |             |              |
View definition:
 SELECT t0.a
   FROM t0;
Distributed by: (a)
Access method: heap
Incremental view maintenance: yes

The last line, Incremental view maintenance: yes, indicates that incremental maintenance is enabled for this view. A regular materialized view does not have this line.

View the data in view m. At this point t0 holds the rows 5 and 8, and the view is defined as SELECT * FROM t0, so both rows are in the view.
```
TABLE m;
```
```
 a
---
 8
 5
(2 rows)
```
Both INSERT and DELETE on the base table are reflected in the view without running REFRESH MATERIALIZED VIEW.

To debug the execution of statements related to incremental materialized views, you can set the system parameter debug_print_ivm to ON by executing SET debug_print_ivm = ON;. The details of this parameter are as follows:

Parameter	Description	Default	Required	Example
`debug_print_ivm`	Whether to enable IVM debugging.	`OFF`	No	`SET debug_print_ivm = ON;`

Query performance comparison with regular views

The following example shows a comparison of query performance between a regular view and an incremental materialized view when processing large datasets in SynxDB. The example uses the TPC-H Query 15 test dataset.

Example of using a regular view

Create a regular view revenue0.

CREATE VIEW revenue0 (supplier_no, total_revenue) AS
       SELECT
               l_suppkey,
               SUM(l_extendedprice * (1 - l_discount))
       FROM
               lineitem
       WHERE
               l_shipdate >= DATE '1996-01-01'
               AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' MONTH
       GROUP BY
               l_suppkey;

Select the details of suppliers and their maximum revenue from the supplier table and the revenue0 view.

SELECT
       s_suppkey,
       s_name,
       s_address,
       s_phone,
       total_revenue
FROM
       supplier,
       revenue0
WHERE
       s_suppkey = supplier_no
       AND total_revenue = (
               SELECT
                                MAX(total_revenue)
               FROM
                                revenue0
       )
ORDER BY s_suppkey;

s_suppkey |          s_name           |     s_address     |     s_phone     | total_revenue
-----------+---------------------------+-------------------+-----------------+---------------
   8449 | Supplier#000008449        | Wp34zim9qYFbVctdW | 20-469-856-8873 |  1772627.2087
(1 row)

Time: 3040.23 ms

In the query above, the result of the view revenue0 is computed in real time, and the query takes 3040.23 ms.

Example of using an incremental materialized view

You can create an incremental materialized view during the DDL phase to significantly reduce query time.

Create an incremental materialized view revenue0.

CREATE INCREMENTAL MATERIALIZED VIEW revenue0 (supplier_no, total_revenue) AS
       SELECT
               l_suppkey,
               SUM(l_extendedprice * (1 - l_discount))
       FROM
               lineitem
       WHERE
               l_shipdate >= DATE '1996-01-01'
               AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' MONTH
       GROUP BY
               l_suppkey;

Select the details of suppliers and their maximum revenue from the supplier table and the revenue0 view. The query takes 65 ms.

SELECT
       s_suppkey,
       s_name,
       s_address,
       s_phone,
       total_revenue
FROM
       supplier,
       revenue0
WHERE
       s_suppkey = supplier_no
       AND total_revenue = (
               SELECT
                                MAX(total_revenue)
               FROM
                                revenue0
       )
ORDER BY s_suppkey;

s_suppkey |          s_name           |     s_address     |     s_phone     | total_revenue
-----------+---------------------------+-------------------+-----------------+---------------
   8449 | Supplier#000008449        | Wp34zim9qYFbVctdW | 20-469-856-8873 |  1772627.2087
(1 row)

Time: 65.731 ms

TPC-H test result comparison

Using the TPC-H test, the execution times for insert and query statements with different data volumes are shown in the table below:

Data Volume	Regular Insert	Insert with Incremental MV	Query with Regular View	Query with Incremental MV
1 GB	2712 ms	3777 ms	813 ms	43 ms
5 GB	15681 ms	29363 ms	3040 ms	63 ms
10 GB	43011 ms	73188 ms	7057 ms	102 ms

As can be seen from the table above:

At all data volumes, query statements with incremental materialized views execute much faster than those with regular views, showing a significant improvement in query performance.
At all data volumes, insert statements with incremental materialized views are slower than regular insert statements. This is because after data is inserted into the table, the materialized view needs to be updated synchronously. Therefore, incremental materialized views are not suitable for write-heavy scenarios.

Usage restrictions and notes

Currently, incremental materialized views in SynxDB have the following restrictions.

Restrictions on the base table. These restrictions apply to the referenced base table. The storage of the incremental materialized view itself is not restricted, and you can specify ao_row, ao_column, or pax with USING:

Creating incremental materialized views for Append-Optimized (AO) tables is not supported.
Creating incremental materialized views for partitioned tables is not supported.
Creating incremental materialized views on PAX storage is not supported.
Creating an incremental materialized view on a view or a materialized view is not supported. This covers regular views, regular materialized views, and other incremental materialized views.

Restrictions on the view definition. The following are not supported when defining an incremental materialized view:

min and max functions, and custom aggregate functions. count, sum, and avg can be used.
Outer joins, that is, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
Window functions, HAVING clause
Subqueries, CTE queries
Set operations (UNION, INTERSECT, EXCEPT)
DISTINCT ON, ORDER BY, LIMIT, OFFSET. SELECT DISTINCT without ON can be used.

Restrictions on DML against the base table:

Once a base table has an incremental materialized view on it, you can no longer update its distribution key column. Running UPDATE reports UPDATE on distributed key column not allowed on relation with update triggers. Updating non-distribution-key columns is unaffected, and such changes are propagated to the view correctly. Dropping all incremental materialized views on that base table makes the distribution key column updatable again.

In addition, you should be aware of the following issues when using incremental materialized views in SynxDB:

Introducing incremental materialized views will slow down data insertion, deletion, and updates, and the first view accounts for most of the degradation. On a 4-segment cluster, inserting 1 million rows into a table takes 643 ms with no incremental materialized view, 3231 ms with one, and 3890 ms with three. The degradation is therefore not proportional to the number of views, but write cost rises sharply as soon as a base table has any incremental materialized view on it.
Using incremental materialized views generates temporary files to store the computed delta view, which might consume some storage space.
Depending on the view definition, materialized views might lead to increased storage costs.