Re: Delta Materialized View Refreshes?

On Fri, 14 Sep 2018 at 16:26, Jeremy Finzel <finzelj(at)gmail(dot)com> wrote:

>
>> Could you apply something similar using triggers?
>> One question would be how PG would identify changes to existing rows -
>> using the replication facilities to essentially replicate into the view?
>> This would be quite tricky I reckon. Otherwise a change to the underlying
>> table may not propagate correctly to the MV.
>>
>
> That's not what I had in mind. I only mean when REFRESH MATERIALIZED VIEW
> is run, it gathers the results of the view in memory, then instead of
> essentially "wiping and reloading" the table, it would only write the
> differences. So if 90% of the rows would be the same as before the
> refresh, we only update 10% of the rows.
>

On a related note, I've mused about allowing a WHERE clause on REFRESH
MATERIALIZED VIEW. To start with, I imagine limiting it to refer to the
columns of a primary key (which implies that primary key constraints would
have to be allowed). As long as this is done, I think it's pretty clear
what the semantics would have to be, at least as to the new view contents:
the equivalent of DELETE with the WHERE clause, followed by INSERT of the
view expression with the same WHERE clause applied.