On Fri, 14 Sep 2018 at 16:26, Jeremy Finzel <finzelj@gmail.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.