Re: Incrementally refreshed materialized view

We also tried to achieve incremental refresh of materialized view and our
solution doesn't solve all of the use cases.

Players:
1) WAL
2) Logical decoding
3) replication slots
4) custom background worker

Two kinds of approaches :
1. Deferred refresh (oracle type of creating log table for each base tables
with its PK and agg's columns old and new values)
a) Log table for each base table has to be created and this log table
will keep track of delta changes.
b) UDF is called to refresh the view incrementally - this will
run original materialized view query with the tracked delta PK's in their
where clause. so only rows that are modified/inserted will be touched.
c) Log table will keep track of changed rows from the data given by
replication slot which uses logical decoding to decode from WAL.
d) Shared memory is used to maintain the relationship between the
view and its base table. In case of restart they are pushed to maintenance
table.

2. RealTime refresh (update the view whenever we get any change-sets
related to that base tables)
a) Delta data from the replication slot will be applied to view by
checking the relationship between our delta data and the view definiton.
Here also shared memory and maintenance table are used.
b) Work completed only for materialized views having single table.

Main disadvantage :
1) Data inconsistency when master failure and also slave doesn't have
replication slot as of now. But 2ndquard guys try to create slots in slave
using some concepts of failover slots. But that doesn't come along with PG
:(.
2) Sum, count and avg are implemented for aggregates(single table) and for
other aggs full refresh comes to play a role.
3) Right join implementation requires more queries to run on the top of
MV's.

So we are on a long way to go and dono whether this is the right path.

Only deferred refresh was pushed to github.
https://github.com/harry-2016/MV_IncrementalRefresh

I wrote a post regarding that in medium.
https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-incremental-refresh-44d1ca742599