物化视图介绍
创建语法
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
[ (column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
AS query
[ WITH [ NO ] DATA ]
物化视图像视图一样使用，但是以一种类表的形式保留了结果。之间的主要区别是物化视图不能直接被更新，更新物化视图的话要用命令进行手工更新
REFRESH MATERIALIZED VIEW mymatview;
FDW介绍
FDW,即外部数据封装器,可以访问本地文件、异地的PG数据库或者其它类型的数据库,如ORACLE、MYSQL、MONGODB、REDIS等，但这些需要我们一个个去找来编译安装，PG本身自带的有FILE_FDW和POSTGRES_FDW。
其中使用POSTGRES_FDW时步骤如下
使用CREATE EXTENSION来安装 postgres_fdw 扩展。
使用CREATE SERVER创建外部服务器对象， 以表示你想连接的每一个远程数据库。指定除了 user 和 password 之外的连接信息作为该服务器对象的选项。
使用CREATE USER MAPPING， 为每个要允许访问外部服务器的数据库用户创建用户映射。指定远程用户名和口令作为用户映射的 user 和 password 选项。
使用CREATE FOREIGN TABLE或IMPORT FOREIGN SCHEMA， 为每一个你想访问的远程表创建一个外部表。外部表的列必须匹配被引用的远程表。但是，如果指定正确的远程名称作为外部表对象的选项，则可以使用与远程表不同的表和/或列名。
现在你只需要从一个外部表SELECT来访问存储在它的底层的远程表中的数据。也可以使用INSERT 、UPDATE 或 DELETE修改远程表（前提是你的用户映射指定的远程用户做这些事情的权限）。
应用示例
异地库192.168.43.176上创建测试表
create table test
(
id int,
info text
);
insert into test select generate_series(1,1),'test';
create unique index index_test_1 on test(id);
本地库192.168.43.175创建fdw及物化视图
创建extension
CREATE EXTENSION postgres_fdw;
创建server
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.43.176', port '6432', dbname 'postgres');
创建映射用户
CREATE USER MAPPING FOR postgres
SERVER foreign_server
OPTIONS (user 'postgres', password 'postgres');
创建foreign table
CREATE FOREIGN TABLE test_foreign (
id int,
info text
)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'test');
创建物化视图
CREATE MATERIALIZED VIEW IF NOT EXISTS mv_test as
select * from test_foreign
with no data;
执行全量更新
postgres=# refresh materialized view mv_test ;
REFRESH MATERIALIZED VIEW
postgres=#
postgres=# select * from mv_test ;
id | info
----+------
1 | test
(1 row)
异地库176上的test表更新及插入数据
postgres=# update test set info='176';
UPDATE 1
postgres=# insert into test select generate_series(2,2),'test';
INSERT 0 1
执行增量更新报错，物化视图需要有唯一索引
postgres=# refresh materialized view CONCURRENTLY mv_test ;
ERROR: cannot refresh materialized view "public.mv_test" concurrently
HINT: Create a unique index with no WHERE clause on one or more columns of the materialized view.
物化视图建索引再重试，增量更新成功
postgres=# create unique index index_mv_test_1 on mv_test(id);
CREATE INDEX
postgres=# refresh materialized view CONCURRENTLY mv_test ;
REFRESH MATERIALIZED VIEW
postgres=# select * from mv_test ;
id | info
----+------
1 | 176
2 | test
为什么我们不直接查询fdw外部表，而要多做一步用物化视图呢？因为查询物化视图比查询fdw外部表要快很多，fdw外部表每次都要远程去访问数据的，而物化视图是保存表数据到本地的。通过查询计划可以知道用物化视图查询的优势。
postgres=# explain select * from test_foreign ;
QUERY PLAN
------------------------------------------------------------------------
Foreign Scan on test_foreign (cost=100.00..150.95 rows=1365 width=36)
(1 row)
postgres=# explain select * from mv_test ;
QUERY PLAN
--------------------------------------------------------
Seq Scan on mv_test (cost=0.00..1.01 rows=1 width=36)
(1 row)
小结
物化视图的全量刷新会有锁，其它事务查询这视图时会等待。
物化视图增量刷新功能在9.4版本之后才有，需要有唯一索引，并且其它事务查询这视图时不会等待。当物化视图还未被填充数据时，这个选项不能被使用。
当数据量较大时，物化视图全量刷新比增量刷新速度快，CONCURRENTLY原理是通过索引关联、其它所有字段不相等的join查询更新的。
物化视图+FDW适合对数据库实时性不高的场景。
