PostgreSQL物化视图刷新策略与查询性能优化实战

物化视图与普通视图的核心差异

PostgreSQL普通视图本质是存储的查询定义，每次查询时重新执行底层SQL。当底层表数据量大、关联复杂时，视图查询性能与直接写SQL无异。物化视图（Materialized View）将查询结果物理存储为一张表，查询时直接读取预计算结果，代价是数据不实时——需要手动或定时刷新。

适用场景：报表查询（T+1数据即可）、OLAP聚合、复杂多表JOIN预计算、降低高频查询对主表的压力。不适用场景：要求秒级数据新鲜度的在线业务。

创建物化视图与基础刷新操作

-- 创建物化视图：每日各品类销售汇总
CREATE MATERIALIZED VIEW mv_daily_category_sales AS
SELECT
    DATE(created_at) AS sale_date,
    category_id,
    c.name AS category_name,
    COUNT(*) AS order_count,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.price * oi.quantity) AS total_amount,
    AVG(oi.price * oi.quantity) AS avg_order_amount
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE o.status = 'completed'
GROUP BY DATE(created_at), category_id, c.name
WITH DATA;

-- 全量刷新（锁表，阻塞查询）
REFRESH MATERIALIZED VIEW mv_daily_category_sales;

-- 并发刷新（不锁表，需唯一索引）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_category_sales;

全量刷新过程会持有ACCESS EXCLUSIVE锁，期间所有SELECT被阻塞。生产环境应优先使用CONCURRENTLY，代价是需要物化视图上至少一个UNIQUE索引，且刷新耗时比全量更长（需要对比新旧数据差异）。

并发刷新机制与唯一索引要求

REFRESH MATERIALIZED VIEW CONCURRENTLY的工作流程：创建新的临时物化视图数据，与旧数据对比差异，增量更新新旧差异行，替换。全程不阻塞读操作，写操作仅在替换瞬间短暂持锁。

-- 必须先创建唯一索引才能使用CONCURRENTLY
CREATE UNIQUE INDEX idx_mv_daily_sales_date_cat
ON mv_daily_category_sales (sale_date, category_id);

-- 然后才能执行并发刷新
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_category_sales;

没有唯一索引时执行CONCURRENTLY会报错。唯一索引的列选择要能唯一标识物化视图的每一行，通常用GROUP BY的列组合。

定时刷新策略：pg_cron与外部调度对比

方案一：pg_cron扩展，在数据库内部定时执行刷新：

-- 安装pg_cron扩展（需修改postgresql.conf的shared_preload_libraries）
CREATE EXTENSION pg_cron;

-- 每天凌晨2点刷新
SELECT cron.schedule(
    'refresh_daily_sales',
    '0 2 * * *',
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_category_sales$$
);

-- 查看已调度任务
SELECT * FROM cron.job;

-- 删除调度
SELECT cron.unschedule('refresh_daily_sales');

方案二：外部调度（cron、Airflow、K8s CronJob），通过psql执行刷新SQL。优势是调度逻辑与应用部署统一管理，缺点是数据库凭据需要暴露给调度系统。

刷新频率选择：实时性要求高的场景每5-15分钟刷新一次；T+1报表每天一次；数据量超过10亿行的物化视图建议仅在低峰期全量刷新，高峰期用增量策略。

物化视图的索引设计与查询优化

物化视图本质是一张物理表，索引策略与普通表类似：

-- 查询条件常用的列建B-tree索引
CREATE INDEX idx_mv_sales_date
ON mv_daily_category_sales (sale_date);

-- 按品类+日期范围的复合查询
CREATE INDEX idx_mv_sales_cat_date
ON mv_daily_category_sales (category_id, sale_date DESC);

-- 金额排序查询
CREATE INDEX idx_mv_sales_amount
ON mv_daily_category_sales (total_amount DESC NULLS LAST);

索引过多会拖慢刷新速度（CONCURRENTLY刷新后索引需要同步更新）。建议只建查询高频使用的索引，控制在3-5个以内。EXPLAIN ANALYZE确认物化视图查询走了索引而非全表扫描。

增量刷新策略与分区物化视图方案

当物化视图数据量极大（数亿行），全量刷新耗时长、资源占用高。增量刷新的思路：只刷新源表有变更的部分。

-- 源表增加updated_at跟踪列
ALTER TABLE orders ADD COLUMN updated_at TIMESTAMP DEFAULT now();

-- 创建增量物化视图（只含最近7天数据）
CREATE MATERIALIZED VIEW mv_recent_category_sales AS
SELECT
    DATE(created_at) AS sale_date,
    category_id,
    c.name AS category_name,
    COUNT(*) AS order_count,
    SUM(oi.price * oi.quantity) AS total_amount
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE o.status = 'completed'
  AND o.created_at >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY DATE(created_at), category_id, c.name
WITH DATA;

分区方案：按月创建分区物化视图，mv_sales_202607、mv_sales_202608等，查询时UNION ALL所需月份。过期分区不再刷新，当前分区高频刷新。这比单一大物化视图的刷新性能提升数倍。

监控物化视图的数据新鲜度：在物化视图中添加last_refreshed列或单独维护一张刷新记录表，Grafana告警刷新延迟超过阈值。