刷新 PostgreSQL 材料化视图的性能

Question

我正在探索使用物化视图创建去规范化视图，以避免在读取性能方面连接多个表。API将从物化视图中读取数据以提供给客户端。

我正在使用亚马逊Aurora Postgres（版本11）。

我在物化视图（MV）上使用唯一索引，以便我可以使用“并发刷新”选项。

然而，我注意到当源表中的一部分行更新时，并且我尝试刷新视图时，速度非常慢。事实上比首次填充视图要慢。例如：第一次填充MV需要约30分钟，刷新需要超过一小时。更新不到1％的行。生成MV所涉及的主要三个表共有大约1800万、2700万和4000万行。

物化视图刷新的及时性很重要，以便数据不会变得太陈旧。

如果有可能，我可以避免使用自定义表来存储去规范化的数据，而不是使用物化视图，但我必须实现逻辑来刷新数据。所以计划避免这种情况。

有什么可以做以加快物化视图的刷新过程吗？

如果您需要更多细节，请告诉我。

谢谢 Kiran

- StudentForever

Answer 1

即使只有很少的更改，刷新物化视图也很慢，因为每次刷新视图时都会运行定义查询。

使用CONCURRENTLY使操作变得更慢，因为它不是对物化视图内容的全面替换，而是对现有数据的修改。

也许您可以创建一个非规范化表，每当基础表被修改时，通过触发器进行更新。

- Laurenz Albe

---

感谢提供的信息，我猜您是在指语句触发器。(https://www.postgresql.org/docs/11/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-TRANSITION-EXAMPLE)。我会探索这个选项。 - StudentForever

可以是行级触发器或语句级触发器，带有转换表。 - Laurenz Albe

Answer 2

你可以创建第二个物化视图并更新它（不同时），然后在一个事务中交换表名。我其实不知道为什么PostgreSQL没有以这种方式实现CONCURRENTLY。

- Matthew Painter

---

这样做是为了避免死元组。 - Roman M

只有在没有其他[物化]视图从您要替换的视图中进行选择时，此方法才有效。依赖视图是指对象而不是对象名称。因此，当您交换名称时，依赖视图不会引用新交换的视图。您还需要查找和替换所有依赖视图。 - Brad Martsberger

Answer 3

也许我来晚了一点，但如果您使用的是postgres 13或更高版本，则可以尝试此扩展程序：https://github.com/sraoss/pg_ivm

它们有一些限制，但承诺重建时间更快。

以下是pganalyze提供的更多信息：https://pganalyze.com/blog/5mins-postgres-15-beta1-incremental-materialized-views-pg-ivm（与项目作者相反，他们希望您使用v14或更高版本）

- hakamairi