PostgreSQL materialized view 增量刷新的扩展与手动 REFRESH

postgresql 15+ 原生不支持增量刷新物化视图，仅提供全量刷新机制；所谓增量需依赖外部扩展（如pg_matview）或手动实现，核心在于变更捕获、目标表UPSERT更新及刷新位点持久化。

PostgreSQL 15+ 原生不支持增量刷新 materialized view

PostgreSQL 直到 15 版本仍只提供 REFRESH MATERIALIZED VIEW 全量重算机制，底层会锁表、重建整个数据集。所谓“增量刷新”不是内建能力，必须靠外部扩展或手动模拟实现。

如果你看到文档或博客声称“PostgreSQL 支持增量物化视图”，大概率混淆了概念：要么指用触发器 + 普通表模拟，要么依赖第三方扩展（如 pg_cron + 自定义逻辑），要么误将分区表或物化视图 + 索引优化当成了增量。

• REFRESH MATERIALIZED VIEW CONCURRENTLY 只避免读锁，但仍是全量计算 + 差异比对，不是增量
• 每次刷新都执行原始 select 查询，哪怕只改了一行，代价也和第一次一样
• 没有类似 oracle 的 FAST REFRESH 或 SQL Server 的索引视图自动维护机制

pg_matview 扩展能做真正的增量刷新吗？

pg_matview（gitHub 上由 dbt Labs 维护的开源扩展）是目前最接近“增量物化视图”的方案，但它本质是**带元数据跟踪的普通表 + 触发器 + 调度辅助**，不是 PostgreSQL 内核级功能。

它通过以下方式模拟增量：

• 在源表上创建 AFTER INSERT/UPDATE/delete 触发器，记录变更到 matview_log 表
• 物化视图实际是一张普通表，由用户定义的 UPSERT SQL 更新（非 SELECT 重算）
• 调用 pg_matview.refresh() 时，只执行你写的那条 INSERT ... ON CONFLICT 或 MERGE 语句

注意：pg_matview 不自动推导增量逻辑——你必须手写符合业务语义的 upsert 语句。比如源表有 updated_at 字段，你就得在刷新 SQL 中加 WHERE updated_at > last_refresh_time；它不会帮你识别哪些行变了。

手动实现增量 REFRESH 的三个关键控制点

绕过扩展、纯 SQL + 应用协同也能做增量，但需同时管住三件事：

• 变更捕获边界：用时间戳（updated_at）、序列号（lsn）、或自增 ID（id > last_max_id）标记“上次刷到哪”
• 目标表更新方式：禁用 TRUNCATE + INSERT，改用 INSERT ... ON CONFLICT DO UPDATE 或分批 DELETE + INSERT
• 刷新状态持久化：把最后成功刷新的位点存进一张小表（如 matview_refresh_state），而不是靠变量或应用内存

示例片段（假设按时间增量）：

INSERT INTO my_mv (id, name, updated_at) SELECT id, name, updated_at FROM source_table WHERE updated_at > (SELECT last_updated FROM matview_refresh_state WHERE name = 'my_mv') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, updated_at = EXCLUDED.updated_at;  UPDATE matview_refresh_state  SET last_updated = (SELECT MAX(updated_at) FROM source_table) WHERE name = 'my_mv';

漏掉 UPDATE matview_refresh_state 这步，下次就会重复处理或跳过变更。

CONCURRENTLY 刷新为什么不能替代增量？

REFRESH MATERIALIZED VIEW CONCURRENTLY 常被误认为“轻量”，其实它只是把锁粒度从表级降到行级，并未减少计算量。

• 仍会完整执行原查询（可能扫描千万行），再逐行比对新旧结果
• 要求物化视图必须有唯一索引（否则报错 Error: cannot refresh materialized view "xxx" concurrently because it does not have a unique index）
• 并发刷新期间，新查询能看到部分更新后的数据，但旧数据残留时间不可控，不适合强一致性场景

真正要降负载，得砍查询本身：比如把 JOIN 5 张大表 拆成中间物化表 + 小范围增量合并，而不是依赖 CONCURRENTLY。

增量逻辑永远绕不开“你怎么定义‘变’”——数据库不知道你的业务语义，也不会猜你关心的是时间、状态还是主键。这点最容易被忽略，也最常导致数据对不上。