【PostgreSQL技巧】PostgreSQL中的物化视图与汇总表比较

多年来，物化视图一直是Postgres期待已久的功能。他们最终到达了Postgres 9.3，尽管当时很有限。在Postgres 9.3中，当刷新实例化视图时，它将在刷新时在表上保持锁定。如果您的工作量是非常繁忙的工作时间，则可以工作，但是如果您要为最终用户提供动力，那么这将是一个大问题。在Postgres 9.4中，我们看到了Postgres实现了同时刷新实例化视图的功能。现在，我们已经完全烘焙了物化视图的支持，但即使如此，我们仍然看到它们可能并不总是正确的方法。

什么是视图view？

对于那些不是数据库专家的人，我们将做一点备份。要了解什么是实体化视图，我们首先来看一个标准视图。视图是已定义的查询，您可以像表一样对其进行查询。当您具有通常用于某些标准报表/构建块的复杂数据模型时，视图特别有用。稍后我们将介绍一个实例化视图。

视图非常适合简化复杂SQL的复制/粘贴。缺点是每次执行视图时都会重新计算结果。对于大型数据集，这可能会导致扫描大量数据，使缓存无效，并且通常速度较慢。输入实例化视图

物化你的视图

让我们从一个可能包含大量原始数据的示例架构开始。在这种情况下，一个非常基本的网络分析工具会记录综合浏览量，发生时间和用户的会话ID。

CREATE TABLE pageviews ( id bigserial, page text, occurred_at timestamptz, session_id bigint );

基于这些原始数据，有很多不同的视图可能非常普遍。而且，如果我们有一个实时仪表板，我们将为它提供动力，因为它可能花费很长时间来查询原始数据，因此很快变得不可行。相反，我们可以对物化视图进行一些汇总：

CREATE MATERIALIZED VIEW rollups AS SELECT date_trunc('day') as day, page, count(*) as views FROM pageviews GROUP BY date_trunc('day'), page;

对于每天至少浏览一次的页面，这将为我们每天提供1条记录。

对于每天晚上批处理的事情，可以处理前一天的事情。但是对于面对客户的事情，您可能不希望等到一天结束后再提供有关网页浏览量如何进行分析的信息。当然，您可以定期刷新一次：

refresh materialized view rollups;

这种刷新方式的缺点是每次刷新时都会重新计算当天的总数，这实际上是在进行不必要的处理。

为了可扩展性增量汇总

另一种方法是使用upsert，它使我们能够增量汇总数据而不必重新处理所有基础数据。Upsert本质上是创建或更新。为此，我们将创建一个表而不是物化视图，然后在其上施加唯一约束：

CREATE TABLE ( day as timestamptz, page text, count as bigint, constraint unq_page_per_day unique (day, page) );

现在开始汇总，我们将执行以下操作：

INSERT INTO rollups SELECT date_trunc('day') as day, page, count(*) as views FROM pageviews GROUP BY date_trunc('day'), page;

这基本上与我们的物化视图相同。但是由于我们的独特限制，当遇到已经插入的记录时，插入会出错。为了完成这项工作，我们将调整查询以完成两件事。一项我们将只处理新记录，另一项我们将使用upsert语法。

为了处理新记录，我们将保留上次停止记录的记录，仅处理新记录。我们在本文中概述了一组方便使用的函数/表。使用适当的函数和表格来跟踪我们上次中断的位置，现在我们将查询更新为仅汇总自上次处理后的数据。然后，我们将其与upsert结合在一起。upsert将尝试插入当天/页面的任何新记录，如果已经看到这些值，则将增加它们：

INSERT INTO rollups SELECT day, page, count(*) as views FROM pageviews WHERE event_id > e GROUP BY day, page ON CONFLICT (day, page) DO UPDATE SET views = views + EXCLUDED.views;

物化视图与汇总表哪个正确？

物化视图是一种非常简单直接的方法。它们的易用性使它们成为快速简便的事情的理想选择。但是，对于具有较大活动负载的较大数据集和数据库，仅处理上一次汇总的净新数据可以更有效地利用资源。哪种方法最合适取决于时间和系统资源。尽管如您所见，汇总方法仅需要一点点努力，并且可以进一步扩展。

原文：https://www.citusdata.com/blog/2018/10/31/materialized-views-vs-rollup-tables/

本文：http://jiagoushi.pro/node/928

讨论：请加入知识星球或者微信圈子【首席架构师圈】

微信公众号

如果喜欢仙翁的分享，请关注微信公众号【首席架构师智库】

如果想进一步讨论，请加仙翁小号【intelligenttimes】，注明你希望加入的群：架构，云计算，大数据，数据科学，物联网，人工智能，安全，全栈开发，DevOps，数字化，产品转型。

如果想和志趣相投的同好交流，请关注仙翁的微信圈子【首席架构师圈】。