PostgreSQL 之 本地物化视图

作者：瀚高PG实验室 （Highgo PG Lab）- 波罗
创建本地表

postgres=# create table tbl(id int primary key, info text, crt_time timestamp);
CREATE TABLE
postgres=# 
insert into tbl select generate_series(1,100000),md5(random()::text),clock_timestamp();
INSERT 0 100000

创建物化视图

postgres=# create materialized view mv_tbl as select * from tbl where id<1000 with no data;
SELECT 0
postgres=# refresh materialized view mv_tbl;
REFRESH MATERIALIZED VIEW
postgres=# select * from pg_matviews;
 schemaname | matviewname | matviewowner | tablespace | hasindexes | ispopulated |        definition        
------------+-------------+--------------+------------+------------+-------------+--------------------------
 public     | mv_tbl      | postgres     |            | f          | t           |  SELECT tbl.id,         +
            |             |              |            |            |             |     tbl.info,           +
            |             |              |            |            |             |     tbl.crt_time        +
            |             |              |            |            |             |    FROM tbl             +
            |             |              |            |            |             |   WHERE (tbl.id < 1000);
(1 row)

创建唯一索引

postgres=# create unique index idx_mv_tbl on mv_tbl(id);
CREATE INDEX
postgres=# select * from pg_matviews;
 schemaname | matviewname | matviewowner | tablespace | hasindexes | ispopulated |        definition        
------------+-------------+--------------+------------+------------+-------------+--------------------------
 public     | mv_tbl      | postgres     |            | t          | t           |  SELECT tbl.id,         +
            |             |              |            |            |             |     tbl.info,           +
            |             |              |            |            |             |     tbl.crt_time        +
            |             |              |            |            |             |    FROM tbl             +
            |             |              |            |            |             |   WHERE (tbl.id < 1000);
(1 row)

做一次增量刷新

postgres=# refresh materialized view concurrently mv_tbl with data;
REFRESH MATERIALIZED VIEW

重新建一个物化视图

postgres=# create materialized view mv_tbl1 as select * from tbl where id<1000 with no data;
SELECT 0

创建唯一索引

postgres=# create unique index idx_mv_tbl1_id on mv_tbl1(id);
CREATE INDEX
Time: 11.361 ms

全量刷新物化视图

postgres=# refresh materialized view mv_tbl1;
REFRESH MATERIALIZED VIEW
Time: 28.121 ms

加并行

postgres=# refresh materialized view concurrently mv_tbl1;
REFRESH MATERIALIZED VIEW
Time: 80.138 ms

若在事务中开启全量刷新物化视图，在另一session中查询物化视图则处于等待状态

postgres=# begin;
BEGIN
Time: 0.773 ms
postgres=# refresh materialized view mv_tbl1;
REFRESH MATERIALIZED VIEW
Time: 44.889 ms

postgres=# select * from mv_tbl1;

当结束刷新后，查询才会继续进行

postgres=# end;
COMMIT

postgres=# select * from mv_tbl1;
 id  |               info                                         |          crt_time          
-----+--------------------------------------------------+---------------------------------------
   1 | 1904d219d7bfaf3e95f7ec33cdac8539 | 2018-01-02 09:14:05.637828
   2 | 821734ef05ef0b839981b6578ec86fe3 | 2018-01-02 09:14:05.638461
   3 | b79fad166b7d819fba4efaf125c862a5 | 2018-01-02 09:14:05.638489
   4 | f773248466b39d06b92b67b618c9a000 | 2018-01-02 09:14:05.6385
   5 | 319ebc21e9d6c5a49e0371026f665972 | 2018-01-02 09:14:05.63851
   6 | 83496b5e1af7089f51b002ba27db5417 | 2018-01-02 09:14:05.63852
   7 | cb5aa7e6dc171fdca06161eda243d83c | 2018-01-02 09:14:05.638529
   8 | eed5bfad7f3de95aa9babfdf1fcdbe12 | 2018-01-02 09:14:05.638538
   9 | bc2fa983bdfec0df33342af44a83770e | 2018-01-02 09:14:05.638547
  10 | 7a4ac6ff9de6aeb1faacb155313b2efe | 2018-01-02 09:14:05.638557
  11 | 8644619688113805b4b657d57dbda6d0 | 2018-01-02 09:14:05.638566
  12 | 1fdd3a48b20cc12dfa789fc2600f38a1 | 2018-01-02 09:14:05.638575
  13 | 5949a59276b0eae19b28292e6067b05c | 2018-01-02 09:14:05.638746
......

若在事务中开启增量刷新物化视图，在另一session中查询物化视图则不会受到阻拦

postgres=# begin;
BEGIN
Time: 1.080 ms
postgres=# refresh materialized view concurrently mv_tbl1;
REFRESH MATERIALIZED VIEW
Time: 37.132 ms

postgres=# select * from mv_tbl1 limit 1;
 id |               info               |          crt_time          
----+----------------------------------+----------------------------
  1 | 1904d219d7bfaf3e95f7ec33cdac8539 | 2018-01-02 09:14:05.637828
(1 row)