"PostgreSQL" materialized view

The materialized view appears after PostgreSQL 9.3. Materialized views are made up of real data, which is fundamentally different from the general view.

1. The materialized view creation syntax is as follows:

--Create grammar
CREATE MATERIALIZED VIEW table_name
     [(column_name [, ...])]
     [WITH (storage_parameter [= value] [, ...])]
     [TABLESPACE tablespace_name]
     AS query
     [WITH [NO] DATA]

--Refresh syntax
REFRESH MATERIALIZED VIEW [CONCURRENTLY] name
     [WITH [NO] DATA] 

2. Create a materialized view diagram example

--Create test table
test = # CREATE TABLE TEST01 (ID INT);
CREATE TABLE
test = # INSERT INTO TEST01 SELECT GENERATE_SERIES (1,1000000);
INSERT 0 1000000

--Create a materialized view
test = # CREATE MATERIALIZED VIEW V_TEST01 AS SELECT * FROM TEST01;
SELECT 1000000
test = #
test = # INSERT INTO TEST01 VALUES (1000001);
INSERT 0 1
test = # \ timing
Timing is on.

--Refresh without CONCURRENTLY parameter
test = # REFRESH MATERIALIZED VIEW V_TEST01;
REFRESH MATERIALIZED VIEW
Time: 1888.860 ms
test = # INSERT INTO TEST01 VALUES (1000002);
INSERT 0 1
test = # CREATE UNIQUE INDEX INX_ID ON V_TEST01 (ID);
CREATE INDEX

--Add CONCURRENTLY parameter refresh
test = # refresh materialized view concurrently v_test01;
REFRESH MATERIALIZED VIEW
Time: 100476.707 ms

It can be seen that after adding the CONCRRENTLY parameter, the refresh time increases significantly. But the update with the CONCRRENTLY parameter will not block the query operation. 

The end!

2017-08-21

"PostgreSQL" materialized view