postgresql 物化视图
使用场景
1、将复杂查询视图化，提高检索的能力，作用于实时性不是非常高，例如非秒级实时场景，分钟级以上皆可，因为同步上存在延迟
2、远程视图本地化，用于有些查询需要消耗非常高的远程IO，导致查询时间非常缓慢的场景，可通过物化视图实现本地查询的效果，快速返回结果
3、可以当作是一张独立的表，可以与其他表进行关联查询，减小查询io，快速返回结果
4、可独立构建特定查询字段的索引，以此达到快速检索的目的
创建物化视图
CREATE MATERIALIZED VIEW ten_month_goods AS
select GSID,NO,TYPE,STATUS,GOODS_NAME,GOODS_BIG_TYPE_ID_INT,GOODS_TYPE_NAME,START_PROVINCE,START_CITY,START_DISTRICT,START_LOCATION,END_PROVINCE,END_CITY,END_DISTRICT,END_LOCATION,RELEASE_TIME,CONTACT,USE_VC_TYPE,USE_VC_LENGTH
from tra_goods_source
where 1=1
-- 从第10月开始截至到现在的数据
and release_time>=( now() - interval '10 month')全量刷新物化视图
-- 全量刷新视图，但是这个时候，视图会被锁住，导致所有查询都无法正常查询,全量刷新，速度较快,400万条数据大概耗时大概在 30s左右
REFRESH MATERIALIZED VIEW ten_month_goods;增量刷新物化视图
-- 唯一索引构建，必须至少有一个唯一索引，才能使用增量物化视图
CREATE UNIQUE INDEX uni_ten_month_goods
on ten_month_goods (gsid);
-- 根据唯一索引刷新视图,但是速度非常慢,7倍左右的时间消耗，400万条数据大概耗时大概在 150s左右
REFRESH MATERIALIZED VIEW CONCURRENTLY ten_month_goods;删除物化视图
-- 删除物化视图
drop materialized view ten_month_goods ;自动刷新视图方式
-- 构建触发器(不推荐)
create or replace function tri_ten_month_goods_func() returns trigger as
$$
declare
begin
refresh materialized view concurrently ten_month_goods with data;
return null;
end;
$$ language plpgsql;
-- 表插入、更新、删除时都会触发刷新视图,但是如果刷新速度很慢的话，会使CURD的操作很久才能正常响应完成，不推荐
create trigger tri_tra_goods_source
after insert or update or delete on tra_goods_source
for each statement
execute procedure tri_ten_month_goods_func();
-- 发现非常慢的情况下请删除触发器
DROP TRIGGER if EXISTS tri_tra_goods_source
ON tra_goods_source;
定时刷新视图(推荐)
-- 安装 pg_cron 组件
git clone https://github.com/citusdata/pg_cron.git
cd pg_cron
source /home/postgres/.bash_profile
make
make install
-- =====================================
-- 修改pg数据库启动配置
-- 启动时加载 cron 组件
shared_preload_libraries = 'pg_cron'
-- cron 数据库名
cron.database_name = 'mes'
-- ==============================
-- 数据库启用插件
create extension pg_cron;
-- 删除插件的指令
DROP EXTENSION pg_cron;
-- 使用cron命令格式
SELECT cron.schedule('<定时计划>', '<定时任务>')
-- 每个小时的23分执行指定脚本。
SELECT cron.schedule('23 * * * *', 'select 1;')；
-- 查看任务列表
SELECT * FROM cron.job;
