Delta Materialized View Refreshes?

From: Jeremy Finzel <finzelj(at)gmail(dot)com>
To: PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org>
Subject: Delta Materialized View Refreshes?
Date: 2018-09-14 18:58:58
Message-ID: CAMa1XUi7YkHptJS==rJNZkEuOWVvPKnw9SFL=AABm8f2Aawnvw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

To me the biggest disadvantage of using a materialized view instead of a
table delta load for an ETL process, for example, is that the materialized
view is very inefficient in terms of write overhead, for of course it
rewrites the whole set of data in the table, meaning heap, indexes, WAL and
everything that goes with it.

Couldn't this be coded to only update changed records? As an example, we
have a materialized view definition that holds summarized data on customer
records, and it only changes when new customers are added. It would be so
nice to only write that change.

I recognize that it may take longer, but for our environment as I'm sure is
true of many others, we are often much more excited about reducing write
churn than read churn.

If in fact it could be option, I am curious if we could leverage the work
being done on the merge functionality to do a "merge" from the materialized
view definition into the actual persistent object.

If people really like the current functionality, then at least it could be
an option like REFRESH MATERIALIZED VIEW DELTA or something - I dunno I am
more interested in just having the feature!

Many thanks for any thoughts on this subject.
Jeremy

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jeremy Finzel 2018-09-14 19:05:36 Logical replication to partitioned subscriber
Previous Message Robert Haas 2018-09-14 18:57:47 Re: stat() on Windows might cause error if target file is larger than 4GB