Postgresql has materialized views which are used to cache the query result which enhances query timing. But materialized view refresh given by postgresql will do complete refresh and this increases query waiting time.
But in 9.4, postgres added concurrent refresh of materialized views which in turn redirects queries to the old view, till the new view has been created.
This is the main cause to refresh materialized views incrementally. For incremental maintenance, there are two ways.
i)using trigger
ii)WAL and logical decoding
Using Triggers:
For each DML operation on base table, we have to use a trigger to update the associated views. As this would cause overhead for complex views, we used WAL and logical decoding.
What are WAL and Logical decoding.?
Write-A-head-Log keeps track of all the persistence changes done in database.
Logical decoding decodes those WAL to DML changes.
Using WAL and Logical Decoding:
WAL in postgreSQL is one of the precious features to do replication
and failover. But here, we have used WAL to update materialized views. There are two types of incremental refresh: realtime and deferred refresh.
RealTime refresh:
Whenever base table changes, we will automatically get that change set from WAL and we will update it to the corresponding view. Here one background process is attached to main postgresql process which receives the change set and apply them to the view.
Change set elimination, non-participating column skip and qual checks are made in decoding part where postgreSQL logical decoding module allows us to use our callbacks.
Deferred refresh:
Here log tables for each base table keep track of changed rows and while refresh, those log table would be replayed. So before creating view, it’s advisable to create respective log tables.
Primary key restriction: Materialized views must include PK of the participating table.
