Re: Implementing Incremental View Maintenance

On Thu, 27 Dec 2018 21:57:26 +0900
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:

> Hi,
>
> I would like to implement Incremental View Maintenance (IVM) on PostgreSQL.

I am now working on an initial patch for implementing IVM on PostgreSQL.
This enables materialized views to be updated incrementally after one
of their base tables is modified.

At the first patch, I want to start from very simple features.

Firstly, this will handle simple definition views which includes only
selection, projection, and join. Standard aggregations (count, sum, avg,
min, max) are not planned to be implemented in the first patch, but these
are commonly used in materialized views, so I'll implement them later on.
Views which include sub-query, outer-join, CTE, and window functions are also
out of scope of the first patch. Also, views including self-join or views
including other views in their definition is not considered well, either.
I need more investigation on these type of views although I found some papers
explaining how to handle sub-quries and outer-joins.

Next, this will handle materialized views with no duplicates in their
tuples. I am thinking of implementing an algorithm to handle duplicates
called "counting-algorithm" afterward, but I'll start from this
no-duplicates assumption in the first patch for simplicity.

In the first patch, I will implement only "immediate maintenance", that is, materialized views are updated immediately in a transaction where a base
table is modified. On other hand, in "deferred maintenance", materialized
views are updated after the transaction, for example, by the user command
like REFRESH. Although I plan to implement both eventually, I'll start from "immediate" because this seems to need smaller code than "deferred". For
implementing "deferred", it is need to implement a mechanism to maintain logs
for recording changes and an algorithm to compute the delta to be applied to
materialized views are necessary.

I plan to implement the immediate maintenance using AFTER triggers created
automatically on a materialized view's base tables. In AFTER trigger using
transition table features, changes occurs on base tables is recorded ephemeral relations. We can compute the delta to be applied to materialized views by
using these ephemeral relations and the view definition query, then update
the view by applying this delta.

--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>