[Proposal] Support Incremental View Maintenance (IVM) in Cloudberry Database

[avamingli]

Proposers

@avamingli
@yjhjstz
@my-ship-it

Proposal Status

Under Discussion

Abstract

Incremental View Maintenance (IVM) is a technique to maintain materialized views which computes and applies only the incremental changes to the materialized views rather than recomputing the contents as the current REFRESH command does. This feature is not implemented on PostgreSQL yet https://wiki.postgresql.org/wiki/Incremental_View_Maintenance.

IVM is more difficult and complex in CBDB, IVM in a distributed database need several key technologies supported:

1. Change data capture(CDC)
   1.1 Based on Triggers on distributed system, catch data changed(insert/update/delete) has not been implemented.
   1.2 Based on Rules, update a table also update IVM if exists(Need to dig, didn't support COPY).
   1.3 Based on DML logical replay (maybe enabled in utility mode on single segment, not been implemented in CBDB)
2. Complex expressions support
   Currently, built-in count sum, avg, min, and max are supported.
3. Distinct/duplicated rows in CBDB
4. Deferred View Maintenance
   When and how to auto refresh beside CDC.

Motivation

Will benefit a lot, as description in Snowflake Working with Materialized Views

A materialized view is a pre-computed data set derived from a query specification (the SELECT in the view definition) and stored for later use. Because the data is pre-computed, querying a materialized view is faster than executing a query against the base table of the view. This performance difference can be significant when a query is run frequently or is sufficiently complex. As a result, materialized views can speed up expensive aggregation, projection, and selection operations, especially those that run frequently and that run on large data sets.

Implementation

Draft branch https://github.com/cloudberrydb/cloudberrydb/tree/incremental_view_maintenance

Rollout/Adoption Plan

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

[avamingli]

We using IVM take2, port part v28 patches from https://www.postgresql.org/message-id/flat/20230601235909.0e1572c27e59112f9d0cbe86%40sraoss.co.jp

[tuhaihe]

Hi @avamingli, thanks for your proposal. The name of it is cp-1 and here is the URL: https://github.com/cloudberrydb/community/blob/main/proposals/cp-1/cp-1.md.

[yjhjstz]

finished by pr: #280