PostgreSQL 9.3 materialized view usage

Materialized views are a feature built in Oracle for a long time, while PostgreSQL also developed functional code very early in the way similar to create table ...., it's just that it hasn't been built in. Version 9.3 Finally uses this as a built-in function. Next we will share the materialized view of the latest version.

Currently, ipvs9.3 has four installation packages on the official website: 9.3.0 (stable version), 9.3.0 beta1, 9.3.0betal, and 9.3.0rc (release candidate version). Therefore, we can download the stable version 9.3.0 stable.

: Http://www.postgresql.org/ftp/source/v9.3.0/
Installation omitted.

I. Syntax

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

Ii. Description
Storage_parameter is a storage parameter, such as a fill factor (fillfactor). tablespace can specify a tablespace. The key is the subsequent as query with [no] data, which is described in the following example.

Iii. Example

1. Create a basic table

[postgres@primary ~]$ psqlpsql (9.3.0)Type "help" for help.postgres=# create table test_kenyon(id int,vname text);                                                 CREATE TABLEpostgres=# insert into test_kenyon select generate_series(1,20),'kenyon good boy'||generate_series(1,20);INSERT 0 20postgres=# select * from test_kenyon ; id |       vname       ----+-------------------  1 | kenyon good boy1  2 | kenyon good boy2  3 | kenyon good boy3  4 | kenyon good boy4  5 | kenyon good boy5  6 | kenyon good boy6  7 | kenyon good boy7  8 | kenyon good boy8  9 | kenyon good boy9 10 | kenyon good boy10 11 | kenyon good boy11 12 | kenyon good boy12 13 | kenyon good boy13 14 | kenyon good boy14 15 | kenyon good boy15 16 | kenyon good boy16 17 | kenyon good boy17 18 | kenyon good boy18 19 | kenyon good boy19 20 | kenyon good boy20(20 rows)

2. Create a Materialized View

Postgres = # create materialized view mv_test_kenyon as select * from test_kenyon where id> 10; SELECT 10 S = # select * from mv_test_kenyon; id | vname ---- + Route 11 | kenyon good boy11 12 | kenyon good boy12 13 | kenyon good boy13 14 | kenyon good boy14 15 | kenyon good boy15 16 | kenyon good boy16 17 | kenyon good boy17 18 | kenyon good boy18 19 | kenyon good boy19 20 | kenyon good boy20 (10 rows) postgres = # \ d + List of relations Schema | Name | Type | Owner | Size | Description -------- + ---------------- + bytes + ---------- + ------- + ------------- public | mv_test_kenyon | materialized view | S | 16 kB | public | test_kenyon | table | postgres | 16 kB | (2 rows) postgres = # \ d mv_test_kenyonMaterialized view "public. mv_test_kenyon "Column | Type | Modifiers -------- + --------- + ----------- id | integer | vname | text | -- size: the size of the empty table is 8 KB by default. Here, the value is 16 kb) it indicates that the data is stored, corresponding physical files are available, and the table-like structure is available.

3. materialized view update

S = # insert into test_kenyon values (21, 'Bad boy '); INSERT 0 1 S = # insert into test_kenyon values (22, 'Bad bo2 '); INSERT 0 1 postgres = # select * from test_kenyon where id> 20; id | vname ---- + ---------- 21 | bad boy 22 | bad boy2 (2 rows) postgres = # select * from mv_test_kenyon where id> 20; id | vname ---- + ------- (0 rows) -- The materialized view data is not refreshed. -- refresh the MATERIALIZED view data S = # REFRESH materialized view mv_test_kenyon; refresh MATERIALIZED VIEWpostgres = # select * from mv_test_kenyon where id> 20; id | vname ---- + ---------- 21 | bad boy 22 | bad boy2 (2 rows) -- use with no data to refresh ipvs = # insert into test_kenyon values (32, 'Bad boy3 '); INSERT 0 1 postgres = # select * from mv_test_kenyon where id> 20; id | vname ---- + ---------- 21 | bad boy 22 | bad boy2 (2 rows) postgres = # refresh materialized view mv_test_kenyon with no data; refresh materialized VIEWpostgres =#\ d + List of relations Schema | Name | Type | Owner | Size | Description -------- + ---------------- + bytes + ---------- + ------------ + ------------- public | updated | materialized view | postgres | 8192 bytes | public | test_kenyon | table | postgres | 16 kB | (2 rows) postgres = # select * from mv_test_kenyon; ERROR: materialized view "mv_test_kenyon" has not been populatedHINT: Use the refresh materialized view command.

When REFRESH with no data is used, the data in the materialized view is cleared and the materialized view is unavailable. to continue using the refresh materialized view view_name, you must use the refresh materialized view view_name.

4. Delete the Materialized View

Postgres = # drop materialized view mv_test_kenyon; drop materialized VIEWpostgres = # -- if there are other constraints on the materialized view, cascade must be added for cascading deletion.

Iv. application scenarios and advantages and disadvantages
You can write complex SQL statements as views for calling, and increase data security.
In addition, materialized views and common views usually scan less data than directly scanning data. With the support of indexes, these views are more efficient and consume less network resources, especially cross-database and cross-server queries.
Compared with a common view, data needs to be refreshed from time to obtain the most real-time data.

V. Summary
1. The materialized view is currently fully refreshed and does not support incremental refresh.
2. the refresh parameter with data is a full update of the Materialized View content and the default parameter. with no data clears the Materialized View content, releases the space occupied by the materialized view, and makes the materialized view unavailable.

Vi. Reference:
Http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html http://wiki.postgresql.org/wiki/Materialized_Views

PostgreSQL deletes duplicate data rows in a table

Establishment of PostgreSQL database connection pool PgBouncer

Compiling PostgreSQL on Windows

Notes on PostgreSQL backup

PostgreSQL details: click here
PostgreSQL: click here