Synchronous incremental update of materialized views for PostgreSQL

A solution for synchronous incremental maintenance of materialized views based on SQL recursive query

“…There are incremental (differential) update [10][11][12][13][14][15] and complete (fully refresh) update according to the ways of maintenance process. Fully refresh update clears the MV table content, then re-executes the MV query and fills the result into the MV table.…”

“…Obviously, there is nothing interesting in completely new updates of MV although sometimes it's effective. On the contrary, a large number of publications are devoted to incremental update of MVs [10][11][12][13][14][15][21][22][23][24][25].…”

“…Therefore, the incremental update algorithm must separately perform "delete event" procedure and "insert event" procedure. Sometimes, this division is not necessary [15]. It means that update events are manipulated in only one operation instead of two steps like above.…”

“…For SPJ views, the work [15] suggested adding at least one key of each base table into the MV, so that we can do insert, update and delete directly in MV based on the set of key values corresponding to changed records in the base table.…”

“…Most of incremental update algorithms of MV using queries with aggregate functions [12,15] (sum, count, avg, min, max...) are based on the ones of SPJ MV. The approach has its own reason.…”