PostgreSQL自动更新物化视图

物化视图是从数据库中检索信息的便捷方法。与普通视图不同，物化视图可以保存查询结果并提供对数据的更快访问。例如，用户可以创建一个包含表中行数的简单物化视图：

 CREATE MATERIALIZED VIEW mv1 AS SELECT count(*) FROM pgbench_accounts;  

使用物化视图mv1获取行数远比直接访问pgbench_accounts中的行数要快得多。

test=# SELECT * from v1; count  -------- 100000(1 row)Time: 0.810 mstest=# SELECT count(*) FROM pgbench_accounts; count  -------- 100000(1 row)Time: 13.945 ms

但是，如果从pgbench_accounts中删除一行，则mv1中的行数仍保持不变。要反映基表的更改（在本例中为pgbench_accounts），您需要重新创建或刷新（这实际上是从头开始重新创建物化视图的内容），这可能需要很长时间。

为了解决这个问题，SRA OSS建议在现有的物化视图“增量物化视图维护”中添加一个新功能。像这样的。

• 添加新语法以允许在创建物化视图时逐步更新它。

• 自动安装触发器以附加到基表。

• 当UPDATE DELETE INSERT应用于基表时，触发器计算物化视图中行的差异并更新行。

这些应该以实时方式更新物化视图。开销是触发器调用的首要问题。因此，频繁更新表不是增量物化视图维护的最佳选择。以下是如何使用增量更新物化视图的演示。

首先创建逐步更新物化视图。

CREATE INCREMENTAL MATERIALIZED view mv2 AS SELECT count(*) FROM pgbench_accounts

该视图最初表示有100,000行。

SELECT * FROM mv2; count  -------- 100000(1 row)

从pgbench_accounts中删除一行。现在行数是99999.  

DELETE FROM pgbench_accounts WHERE aid = 10;DELETE 1SELECT count(*) FROM pgbench_accounts; count ------- 99999(1 row)

确保mv2报告的行数相同。

SELECT * FROM mv2; count ------- 99999(1 row)

它有效！

这是一个非常简单的物化视图。更复杂的查询，例如内部联接也可以。

CREATE INCREMENTAL MATERIALIZED VIEW mv3 AS SELECT a.aid, b.bid, t.tid FROM pgbench_accounts a INNER JOIN pgbench_branches b ON (a.bid = b.bid ) INNER JOIN pgbench_tellers t ON (b.bid = t.bid) WHERE a.aid BETWEEN 1 AND 5;SELECT 50SELECT * FROM mv3 LIMIT 5; aid | bid | tid-----+-----+-----   3 |   1 |  10   2 |   1 |   1   2 |   1 |   5   2 |   1 |   4   5 |   1 |   3 (5 rows)DELETE FROM pgbench_accounts WHERE aid = 2;DELETE 1SELECT * FROM mv3 LIMIT 5; aid | bid | tid-----+-----+-----   3 |   1 |  10   5 |   1 |   3   1 |   1 |   1   5 |   1 |   8   4 |   1 |   3
(5 rows)

实现其他类型的查询，例如外连接，自连接，也在我们的计划中，可能是针对PostgreSQL 13.  

本文翻译自：http://pgsqlpgpool.blogspot.com/2019/08/automatically-updating-materialized.html