本文聚焦实操命令、管理操作、锁与并发刷新、定时、运维排查,适合直接上手落地,延续上一篇博客的内容。
一、前置说明
实体化视图(物化视图)会把 select 的查询结果持久化存储在磁盘,不是实时视图,数据是快照,必须手动/定时刷新;支持建索引,这是它最大优势。
区分:普通 view 只是存sql,无数据;物化视图是物理存储结果集。
二、创建实体化视图
1. 创建并立即填充数据(默认)
create materialized view mv_order_day_stat
as
select
date_trunc('day', o.create_time) as stat_date,
count(o.id) as order_count,
sum(o.amount) as total_amount
from t_order o
where o.status = 1
group by date_trunc('day', o.create_time);
执行后,pg立刻执行查询,把结果写入磁盘,创建完成即可查询。
2. 创建,但是不填充数据with no data
适合提前建好结构、后续低峰再加载数据:
create materialized view mv_order_day_stat
as
select
date_trunc('day', o.create_time) as stat_date,
count(o.id) as order_count,
sum(o.amount) as total_amount
from t_order o
where o.status = 1
group by date_trunc('day', o.create_time)
with no data;
此时直接查询会报错:materialized view xxx has not been populated,需要执行刷新。
3. 给物化视图建立索引(非常关键)
concurrently 并发刷新强制要求唯一索引,同时索引加速查询:
-- 普通b树索引 create index idx_mv_stat_date on mv_order_day_stat(stat_date); -- 唯一索引(并发刷新必备) create unique index uk_mv_stat_date on mv_order_day_stat(stat_date);
三、刷新实体化视图(最核心管理操作)
1. 普通刷新(简单,会锁表)
refresh materialized view mv_order_day_stat;
原理:清空全部数据,重新执行查询写入新数据
- 锁:
access exclusive,刷新期间不能读、不能写这个mv - 适合:离线报表、低峰期、小体量mv
2. 并发刷新concurrently(业务首选)
refresh materialized view concurrently mv_order_day_stat;
特点:
- 刷新过程不阻塞查询,业务可以正常读
- 前提:mv必须存在唯一索引,否则报错
- 代价:占用更多临时io、磁盘,速度比普通刷新慢
pg14+ 支持:refresh materialized view concurrently ... skip concurrently
3. 填充空的mv(with no data创建的)
refresh materialized view mv_order_day_stat;
四、查询、查看、修改、删除
1. 查询物化视图
语法和普通表完全一致:
select * from mv_order_day_stat where stat_date >= '2026-01-01';
2. 查看库中所有物化视图
select matviewname, schemaname, matviewowner from pg_matviews order by schemaname, matviewname;
3. 查看物化视图定义sql
select definition from pg_matviews where matviewname = 'mv_order_day_stat';
4. 修改物化视图
重点:pg没有 alter materialized view as 直接修改查询语句
两种方案:
- 重建(简单常用)
drop materialized view if exists mv_order_day_stat; -- 重新执行create materialized view
- 仅修改属性(不改变查询逻辑)
-- 设置是否填充数据(pg14+) alter materialized view mv_order_day_stat set (fillfactor=80);
5. 删除物化视图
drop materialized view if exists mv_order_day_stat;
连带索引一起删除,不需要单独删索引。
五、定时自动刷新(两种主流方案)
物化视图本身不带定时调度。
方案1:pg_cron 扩展(数据库内调度,推荐自建pg)
-- 每小时0分并发刷新
select cron.schedule(
'refresh-mv-order-stat',
'0 * * * *',
'refresh materialized view concurrently mv_order_day_stat;'
);
-- 查看定时任务
select * from cron.job;
云rds很多不支持pg_cron扩展。
方案2:外部调度
linux crontab、airflow、django定时任务、shell脚本,连接数据库执行刷新sql。
适合云数据库、不允许安装扩展的场景。
六、运维监控常用sql
1. 查看物化视图大小
select
matviewname,
pg_size_pretty(pg_total_relation_size(matviewname::regclass)) as total_size
from pg_matviews;
2. 查看正在执行的刷新任务
select pid, query, state, wait_event_type, wait_event from pg_stat_activity where query ilike 'refresh materialized view%';
3. 终止卡住的刷新会话
select pg_terminate_backend(pid);
七、常见限制与坑
concurrently必须有唯一索引,缺少直接报错- 基表ddl变更(删表、删字段),刷新mv会直接报错
- 普通刷新会加排他锁,大mv刷新会造成业务查询超时
- 物化视图不支持直接insert/update/delete,只能通过刷新更新
- 占用额外磁盘,是一份冗余数据,需要评估存储
- 刷新消耗cpu、io,避开业务高峰执行
八、最佳实践总结
- 对外给业务、bi使用的统计mv → 用
concurrently+ 唯一索引 - 后台离线、非实时报表、数据量不大 → 普通refresh
- 刷新尽量放在凌晨低峰;监控刷新耗时、锁、磁盘占用
- 不要把物化视图当成业务主表,它适合只读统计快照
- 数据需要强实时一致的场景,不要使用物化视图
以上就是在postgresql中创建和管理实体化视图的完整过程的详细内容,更多关于postgresql创建和管理实体化视图的资料请关注代码网其它相关文章!
发表评论