postgresql-物理化视图

PostgreSQL 9.3开始支持物化视图，9.4又增加了非阻塞的CONCURRENTLY选项，但REFRESH时却不支持类似START WITH ... NEXT ...的定时刷新选项。

如何实现定时刷新物化视图？主要是以下几种：
借助操作系统，如Linux/Unix的crontab或Windows的定时任务；
借助插件pgAgent；
使用触发器，一般为语句级(... FOR EACH STATEMENT ...)。
近日接触到\watch命令，发现了一种新的刷新物化视图的方法。 \watch 10  --没隔10s执行一次前面的命令，但需要psql开着

应用场景和优劣势：
可以将复杂的SQL写成视图来调用，并可增大数据的安全性
另外物化视图与普通视图比因为直接扫描数据，通常扫描的数据更少，在有索引的支持下，效率更高，网络消耗也更少，特别是跨DB，跨服务器的查询
与普通视图相比的劣势是数据需要不定时地刷新才能获取到最实时的数据
刷新参数with data是全量更新(replace)物化视图内容,且是默认参数；with no data会清除物化视图内容，释放物化视图所占的空间，并使物化视图不可用

异地物化视图（基于外部表）案例：

首先在192.168.100.200上创建extension

postgres=# create extension postgres_fdw;
CREATE EXTENSION

在200上创建server

postgres=# create server pg154 foreign data wrapper postgres_fdw options(hostaddr '192.168.100.154', dbname 'postgres', port '5432');
CREATE SERVER

创建user mapping

postgres=# create user mapping for postgres server pg154 options(user 'postgres', password 'postgres');
CREATE USER MAPPING

在另一台机器（pg v9.3.1 IP192.168.100.154）创建测试表基表

postgres=# create table userinfo(id int primary key, info text, crt_time timestamp);
CREATE TABLE

插入测试记录

postgres=# insert into userinfo select generate_series(1,100000), md5(random()::text), clock_timestamp();
INSERT 0 100000

在100.200创建外部表

postgres=# create foreign table ft_userinfo(id int, info text, crt_time timestamp) server pg154 options(schema_name 'public', table_name 'userinfo');
CREATE FOREIGN TABLE

查询：

postgres=# select * from ft_userinfo limit 2;
 id |               info               |          crt_time          
----+----------------------------------+----------------------------
  1 | 846b0023e543879ff827efb0f15ecbd4 | 2018-01-02 10:21:42.203501
  2 | 95a04631dae89fce67441e2d2af4e88f | 2018-01-02 10:21:42.204296
(2 rows)

创建物化视图

postgres=# create materialized view mv_userinfo as select * from ft_userinfo with no data;
SELECT 0

全量刷新物化视图

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

创建唯一索引

postgres=# create unique index idx_mv_userinfo_id on mv_userinfo(id);
CREATE INDEX

增量刷新物化视图
postgres=# refresh materialized view concurrently mv_userinfo with data;
REFRESH MATERIALIZED VIEW

postgres=# \d tbl
                 Table "public.tbl"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 id       | integer                     | not null
 info     | text                        | 
 crt_time | timestamp without time zone | 
Indexes:
    "tbl_pkey" PRIMARY KEY, btree (id)

postgres=# alter table tbl drop constraint tbl_pkey;
ALTER TABLE

此时也是可以增量刷新物化视图
postgres=# refresh materialized view concurrently mv_tbl1 ;

REFRESH MATERIALIZED VIEW
1
增量刷新能否刷新判断的是物化视图上有没有唯一索引，并不关注基表上有没有唯一索引

物化视图不允许refresh、vacuum、analyze以及select以外的操作。
在pg_rewrite中限制了：hasForUpdate false
 

参考：https://blog.csdn.net/pg_hgdb/article/details/80475846