public inbox for [email protected]
help / color / mirror / Atom feedFrom: Yugo Nagata <[email protected]>
To: Yugo Nagata <[email protected]>
Cc: [email protected]
Subject: Re: Implementing Incremental View Maintenance
Date: Mon, 1 Apr 2019 12:11:22 +0900
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
On Thu, 27 Dec 2018 21:57:26 +0900
Yugo Nagata <[email protected]> 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 <[email protected]>
view thread (215+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Implementing Incremental View Maintenance
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox