当前位置: 代码网 > it编程>数据库>MsSqlserver > 在PostgreSQL中创建和管理实体化视图的完整过程

在PostgreSQL中创建和管理实体化视图的完整过程

2026年11月17日 • MsSqlserver •我要评论
本文聚焦实操命令、管理操作、锁与并发刷新、定时、运维排查,适合直接上手落地,延续上一篇博客的内容。一、前置说明实体化视图(物化视图)会把 select 的查询结果持久化存储在磁盘,不是实时视图,数据是

本文聚焦实操命令、管理操作、锁与并发刷新、定时、运维排查,适合直接上手落地,延续上一篇博客的内容。

一、前置说明

实体化视图(物化视图)会把 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 直接修改查询语句
两种方案:

  1. 重建(简单常用)
drop materialized view if exists mv_order_day_stat;
-- 重新执行create materialized view
  1. 仅修改属性(不改变查询逻辑)
-- 设置是否填充数据(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);

七、常见限制与坑

  1. concurrently 必须有唯一索引,缺少直接报错
  2. 基表ddl变更(删表、删字段),刷新mv会直接报错
  3. 普通刷新会加排他锁,大mv刷新会造成业务查询超时
  4. 物化视图不支持直接insert/update/delete,只能通过刷新更新
  5. 占用额外磁盘,是一份冗余数据,需要评估存储
  6. 刷新消耗cpu、io,避开业务高峰执行

八、最佳实践总结

  1. 对外给业务、bi使用的统计mv → 用 concurrently + 唯一索引
  2. 后台离线、非实时报表、数据量不大 → 普通refresh
  3. 刷新尽量放在凌晨低峰;监控刷新耗时、锁、磁盘占用
  4. 不要把物化视图当成业务主表,它适合只读统计快照
  5. 数据需要强实时一致的场景,不要使用物化视图

以上就是在postgresql中创建和管理实体化视图的完整过程的详细内容,更多关于postgresql创建和管理实体化视图的资料请关注代码网其它相关文章!

赞 (0)

相关文章:

版权声明:本文内容由互联网用户贡献,该文观点仅代表作者本人。本站仅提供信息存储服务,不拥有所有权,不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 2386932994@qq.com 举报,一经查实将立刻删除。

发表评论

验证码:
Copyright © 2017-2026  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com