Materialized View - Incremental refresh methods

1) With Triggers
2) Using pg_ivm
3) WAL decoding method

1) With Triggers
`/* Create a new table, populated some data into it */`
CREATE TABLE test_t(id INT, name TEXT);
INSERT INTO test_t SELECT s AS id, s||'_'||substr(md5(random()::TEXT), 1, 6) AS name FROM generate_series(1,100) s;

`/* Created one more table populate some data into it */`
CREATE TABLE test_t_age(id INT, t_age INT);
INSERT INTO test_t_age SELECT s AS id, (random() * 100)::INT AS t_age FROM generate_series(1,100) s;

`/* Crete MATERIALIZED VIEW by joining two tables using id */`
CREATE MATERIALIZED VIEW mv_t_age AS SELECT * FROM test_t_age JOIN test_t USING(id);

`/* Create a function for refreshing MATERIALIZED VIEW */`
CREATE OR REPLACE FUNCTION refresh_t_age()
RETURNS TRIGGER AS
$fun$
BEGIN
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_t_age;

          /*
           * Here, I am going with CONCURRENTLY option, means we can refresh the
           * materialized view data withoud locking out concurent selects on
           * materialized view.
           */

RETURN NULL;
END;
$fun$ LANGUAGE PLPGSQL;

`/* We should have UNIQUE index on materialized view for using CONCURRENTLY refresh */`
haritest=# CREATE UNIQUE INDEX idx_unique_id ON public.mv_t_age(id);

`Trigger ON test_t`
CREATE OR REPLACE TRIGGER refresh_t_age
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON test_t
FOR EACH STATEMENT
EXECUTE PROCEDURE refresh_t_age();

`Trigger ON test_t_age`
CREATE OR REPLACE TRIGGER refresh_t_age
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON test_t_age
FOR EACH STATEMENT
EXECUTE PROCEDURE refresh_t_age();

haritest=# SELECT COUNT(1) FROM test_t;
count
───────
100
(1 row)

haritest=# SELECT COUNT(1) FROM test_t_age;
count
───────
100
(1 row)

haritest=# SELECT COUNT(1) FROM mv_t_age;
count
───────
100
(1 row)

i) Verify AFTER DELETE event
`Here, We are going to delete 90 records from test_t_age Table.`
haritest=# delete from test_t_age where id > 10;
DELETE 90
haritest=# SELECT COUNT(1) FROM mv_t_age;
count
───────
10
`Without manual refreshing, we can see the updated data.`

ii) Verify AFTER UPDATE event

            I am going to Update name for one person, it should reflect withoud doing manual refresh
                    Before Updating just verify the data in the materialized view.

haritest=# SELECT * FROM mv_t_age;
id │ t_age │ name
────┼───────┼───────────
1 │ 68 │ 1_64000e
2 │ 39 │ 2_9a8557
3 │ 76 │ 3_80c0ad
4 │ 26 │ 4_1c32a5
5 │ 72 │ 5_250fd5
6 │ 89 │ 6_d80b8f
7 │ 18 │ 7_3cdbf8
8 │ 11 │ 8_e5ad04
9 │ 53 │ 9_c277c4
10 │ 1 │ 10_11da9c
(10 rows)
haritest=# SELECT * FROM test_t where id = 1;
id │ name
────┼──────────
1 │ 1_64000e
haritest=# UPDATE test_t SET name = 'John' WHERE id = 1;
UPDATE 1
haritest=# SELECT * FROM mv_t_age;
id │ t_age │ name
────┼───────┼───────────
2 │ 39 │ 2_9a8557
3 │ 76 │ 3_80c0ad
4 │ 26 │ 4_1c32a5
5 │ 72 │ 5_250fd5
6 │ 89 │ 6_d80b8f
7 │ 18 │ 7_3cdbf8
8 │ 11 │ 8_e5ad04
9 │ 53 │ 9_c277c4
10 │ 1 │ 10_11da9c
1 │ 68 │ John `/* We can see the updated data here without manual refresh */`

iii) Verify AFTER INSERT event
`I am going to INSERT new record into test_t_age, it should reflect withoud doing manual refresh`
haritest=# SELECT * FROM test_t_age;
id │ t_age
────┼───────
1 │ 68
2 │ 39
3 │ 76
4 │ 26
5 │ 72
6 │ 89
7 │ 18
8 │ 11
9 │ 53
10 │ 1
haritest=# INSERT INTO test_t_age VALUES (11, 25);
INSERT 0 1
haritest=# SELECT * FROM test_t_age;
id │ t_age
────┼───────
1 │ 68
2 │ 39
3 │ 76
4 │ 26
5 │ 72
6 │ 89
7 │ 18
8 │ 11
9 │ 53
10 │ 1
11 │ 25
(11 rows)
haritest=# SELECT * FROM mv_t_age;
id │ t_age │ name
────┼───────┼───────────
2 │ 39 │ 2_9a8557
3 │ 76 │ 3_80c0ad
4 │ 26 │ 4_1c32a5
5 │ 72 │ 5_250fd5
6 │ 89 │ 6_d80b8f
7 │ 18 │ 7_3cdbf8
8 │ 11 │ 8_e5ad04
9 │ 53 │ 9_c277c4
10 │ 1 │ 10_11da9c
1 │ 68 │ John
11 │ 25 │ 11_489b07 `/* We can see the new record populating here without doing manual refresh */`
(11 rows)
iv) Verify AFTER TRUNCATE event

            Check the materialized view data after Truncating any one of the Table.
            I am going to TRUNCATE test_t table data.

haritest=# TRUNCATE test_t;
TRUNCATE TABLE
haritest=# SELECT COUNT(*) FROM test_t;
count
───────
0
(1 row)
haritest=# SELECT COUNT(*) FROM mv_t_age;
count
───────
0
(1 row)
`Here, we can see the refreshed data without doing manual refresh.`

References :
https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html
https://dba.stackexchange.com/questions/190552/trigger-to-update-a-materialized-view-once-after-relevant-transaction
https://www.netguru.com/blog/materialized-view-refresh-problem-and-how-to-solve-using-database-triggers-in-ror-based-application

2) Using pg_ivm:
pre requisites : Postgres version 14 or above, pg_ivm extension
`/* Create a new table, populated some data into it */`
CREATE TABLE test_t(id INT, name TEXT);
INSERT INTO test_t SELECT s AS id, s||'_'||substr(md5(random()::TEXT), 1, 6) AS name FROM generate_series(1,11) s;

`/* Created one more table populate some data into it */`
CREATE TABLE test_t_age(id INT, t_age INT);
INSERT INTO test_t_age SELECT s AS id, (random() * 100)::INT AS t_age FROM generate_series(1,11) s;

haritest=# select * from test_t;
id │ name
────┼───────────
1 │ 1_ef768e
2 │ 2_d4efd2
3 │ 3_cf7de2
4 │ 4_e6d89e
5 │ 5_1096af
6 │ 6_f99d14
7 │ 7_8c640d
8 │ 8_77f9da
9 │ 9_05f802
10 │ 10_e0f72c
11 │ 11_1a85a2
(11 rows)

haritest=# select * from test_t_age;
id │ t_age
────┼───────
1 │ 68
2 │ 39
3 │ 76
4 │ 26
5 │ 72
6 │ 89
7 │ 18
8 │ 11
9 │ 53
10 │ 1
11 │ 25
(11 rows)

haritest=# SELECT create_immv('mv_ivm', 'SELECT * FROM test_t_age JOIN test_t USING(id)');
Note : Returns Interger value that is no of rows returned by the query.
In our case return value is 11 .

i) Verify DELETE scenario:
haritest=# delete from test_t where id=11;
DELETE 1
haritest=# select * from mv_ivm;
id │ t_age │ name
────┼───────┼───────────
1 │ 68 │ 1_ef768e
2 │ 39 │ 2_d4efd2
3 │ 76 │ 3_cf7de2
4 │ 26 │ 4_e6d89e
5 │ 72 │ 5_1096af
6 │ 89 │ 6_f99d14
7 │ 18 │ 7_8c640d
8 │ 11 │ 8_77f9da
9 │ 53 │ 9_05f802
10 │ 1 │ 10_e0f72c
(10 rows)
Note : mv_ivm automaticly refreshed once data deleted from test_t.

ii) Verify UPDATE scenario :
haritest=# update test_t set name='test_name' where id = 10;
UPDATE 1
haritest=# select * from mv_ivm ;
id │ t_age │ name
────┼───────┼───────────
1 │ 68 │ 1_ef768e
2 │ 39 │ 2_d4efd2
3 │ 76 │ 3_cf7de2
4 │ 26 │ 4_e6d89e
5 │ 72 │ 5_1096af
6 │ 89 │ 6_f99d14
7 │ 18 │ 7_8c640d
8 │ 11 │ 8_77f9da
9 │ 53 │ 9_05f802
10 │ 25 │ test_name `/* Uodated data reflected here */ `
(10 rows)

iii) Verify INSERT scenario :
haritest=# insert into test_t values (11, 'XYZ');
INSERT 0 1
haritest=# select * from mv_ivm ;
id │ t_age │ name
────┼───────┼───────────
1 │ 68 │ 1_ef768e
2 │ 39 │ 2_d4efd2
3 │ 76 │ 3_cf7de2
4 │ 26 │ 4_e6d89e
5 │ 72 │ 5_1096af
6 │ 89 │ 6_f99d14
7 │ 18 │ 7_8c640d
8 │ 11 │ 8_77f9da
9 │ 53 │ 9_05f802
10 │ 25 │ test_name
11 │ 25 │ XYZ `/* New record reflected without refreshing the data */`
(11 rows)

iv) Verify TRUNCATE scenario :
haritest=# truncate test_t_age;
TRUNCATE TABLE
Time: 238.093 ms
haritest=# select * from mv_ivm ;
id │ t_age │ name
────┼───────┼──────
(0 rows)
`/* mv_ivm data got truncated without refreshing */`

`We can see the created view definition with get_immv_def `
haritest=# select get_immv_def('mv_ivm');
get_immv_def
──────────────────────────────
SELECT test_t_age.id, ↵
test_t_age.t_age, ↵
test_t.name ↵
FROM (test_t_age ↵
JOIN test_t USING (id))

haritest=# DROP TABLE mv_ivm;
`pg_ivm supports only inner join, won't support outer joins`
haritest=# SELECT create_immv('mv_ivm', 'SELECT * FROM test_t_age LEFT JOIN test_t USING(id)');
ERROR: OUTER JOIN is not supported on incrementally maintainable materialized view

`We can disable the incremental view maintainance by using refresh_immv command.`
haritest=# select refresh_immv('mv_ivm', false);
`mv_ivm disabled `

`Lets populate some data into test_t_age`
haritest=# INSERT INTO test_t_age SELECT s AS id, (random() * 100)::INT AS t_age FROM generate_series(1,11) s;
INSERT 0 11

`Here we are not seeing updated data, because mv_ivm got dissabled`
haritest=# select * from mv_ivm;
id │ t_age │ name
────┼───────┼──────
(0 rows)

`Let us enable the mv_ivm by with following command`
haritest=# select refresh_immv('mv_ivm', true);
refresh_immv
──────────────
11
(1 row)

`Data got reflected in mv_ivm`
haritest=# select * from mv_ivm;
id │ t_age │ name
────┼───────┼───────────
1 │ 34 │ 1_ef768e
2 │ 72 │ 2_d4efd2
3 │ 2 │ 3_cf7de2
4 │ 17 │ 4_e6d89e
5 │ 31 │ 5_1096af
6 │ 59 │ 6_f99d14
7 │ 29 │ 7_8c640d
8 │ 1 │ 8_77f9da
9 │ 60 │ 9_05f802
10 │ 34 │ test_name
11 │ 26 │ XYZ
(11 rows)

References :
https://github.com/sraoss/pg_ivm

MATERIALIZED VIEWS -> ORACLE VS POSTGRESQL :
Materialized View :
Materialized view is a database object, it contains the Query result.

Comparison :

ORACLE POSTGRESQL
BUILD IMMEDIATE WITH DATA
BUILD DEFERRED WITH NO DATA

REFRESH COMPLETE REFRESH
/* Discard existing data and rewrites */
REFRESH FAST Incremental Refresh (pg_ivm)
/* Oracle REFRESH FAST working only when Materilized /*We can achive with the pg_ivm, but pg_ivm has some limitations*/
/* view logs on enabled on base tables */

Refresh Strategy :
ON COMMIT ON COMMIT -- We can achive this by creating triggers on underlaying tables in the MV
ON DEMAND ON DEMAND -- Supports only manual refresh

We can perform DML on Materialized Views We cannot perform DML on Materialized Views

EX1 : CREATE MATERIALIZED VIEW mv_emp AS SELECT * FROM hr.employees;
CREATE MATERIALIZED VIEW mv_emp AS
SELECT * FROM hr.employees;

DBMS_MVIEW.REFRESH('mv_emp', 'cf'); REFRESH MATERIALIZED VIEW mv_emp;
/* cf -> Refresh configuration method */
/* c -> complete, f-> fast */

EX2:
CREATE MATERIALIZED VIEW /* Create a trigger that will initiate a refresh after */
mv_emp REFRESH FAST ON COMMIT /* every DML command on the underlying tables */
AS SELECT * FROM employees; CREATE OR REPLACE FUNCTION refresh_mv_emp()
RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$ BEGIN
REFRESH MATERIALIZED VIEW mv_emp;
RETURN NULL;
END $$;

CREATE TRIGGER refresh_mv_emp AFTER INSERT OR UPDATE
OR DELETE OR TRUNCATE ON employees FOR EACH STATEMENT
EXECUTE PROCEDURE refresh_mv_emp();

EX3:
CREATE MATERIALIZED VIEW LOG Not Supported
ON employees…
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW
mv_emp REFRESH FAST AS
SELECT * FROM employees;

References :
https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.special.matviews.html