Re: Implementing Incremental View Maintenance

From: Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
To: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Implementing Incremental View Maintenance
Date: 2020-11-24 09:21:14
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Wed, 11 Nov 2020 19:10:35 +0300
Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:

Thank you for reviewing this patch!

> The patch is not applied to the current master because makeFuncCall
> prototype is changed,
> I fixed it by adding COAERCE_CALL_EXPLICIT.

The rebased patch was submitted.

> Ooops! Now TPS are much lower:
> tps = 141.767347 (including connections establishing)
> Speed of updates is reduced more than 70 times!
> Looks like we loose parallelism because almost the same result I get
> with just one connection.

As you and Ishii-san mentioned in other posts, I think the reason would be a
table lock on the materialized view that is acquired during view maintenance.
I will explain more a bit in another post.

> 4. Finally let's create one more view (it is reasonable to expect that
> analytics will run many different queries and so need multiple views).
> create incremental materialized view teller_avgs as select
> t.tid,avg(abalance) from pgbench_accounts a join pgbench_tellers t on
> group by t.tid;
> It is great that not only simple aggregates like SUM are supported, but
> also AVG.
> But insertion speed now is reduced twice - 72TPS.

Yes, the current implementation takes twice time for updating a table time
when a new incrementally maintainable materialized view is defined on the
table because view maintenance is performed for each view.

> So good news is that incremental materialized views really work.
> And bad news is that maintenance overhead is too large which
> significantly restrict applicability of this approach.
> Certainly in case of dominated read-only workload such materialized
> views can significantly improve performance.
> But unfortunately my dream that them allow to combine OLAP+OLPT is not
> currently realized.

As you concluded, there is a large overhead on updating base tables in the
current implementation because it is immediate maintenance in which the view
is updated in the same sentence where its base table is modified. Therefore,
this is not suitable to OLTP workload where there are frequent updates of

For suppressing maintenance overhead in such workload, we have to implement
"deferred maintenance" which collects table change logs and updates the view
in another transaction afterward.

Yugo Nagata

Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Yugo NAGATA 2020-11-24 09:21:33 Re: Implementing Incremental View Maintenance
Previous Message Heikki Linnakangas 2020-11-24 08:54:39 Re: Deduplicate aggregates and transition functions in planner