从物化视图到连续聚合：通过实时分析增强 PostgreSQL

使用户能够访问实时数据分析是许多现代应用程序的关键功能。想象一下您自己正在使用您最喜欢的SaaS平台 - 可能有一个直观的仪表板显示实时数据和历史见解。您可以与该平台进行交互，创建自定义报告，探索详细的指标，并可视化跨越数周或数月的趋势。

作为用户，您当然不希望这个平台变慢。这意味着支持这些产品的数据库必须能够快速运行大量数据的查询，包括复杂的分析查询。

尽管PostgreSQL 是当今开发人员最喜爱的数据库，它并不以快速查询大量数据而闻名。但不用担心： Postgres的工具箱中总是有一个工具。最好的之一是物化视图。

什么是 PostgreSQL 物化视图？

基于物化技术，PostgreSQL 物化视图预先计算常用的查询并将结果存储为表。与每次引用视图时运行基础查询的标准 PostgreSQL 视图不同，物化视图将源查询的结果保留在数据库中。这样做的好处是，您的数据库不必每次运行查询时都执行该查询：结果已经可以在磁盘上访问 - 您将更快地获得查询响应。

对于计算资源密集型的查询，这是优化查询响应的绝佳方法。例如，可能涉及处理大量数据、聚合或多个联接的查询。

使用物化视图非常简单。要创建视图，您可以使用CREATE MATERIALIZED VIEW语句和您选择的查询。

创建物化视图后，您可以将其作为常规PostgreSQL表进行查询：

 CREATE MATERIALIZED VIEW customer_orders AS SELECT customer_id, COUNT(*) as total_orders FROM orders GROUP BY customer_id;

 -- Query the materialized view SELECT * FROM customer_orders;

该物化视图将很快变得过时，直到您刷新它：即使您向基表添加新数据（或更新或删除数据），物化视图也不会自动包含这些更改；它是创建时的快照。要更新物化视图，您需要运行REFRESH MATERIALIZED VIEW 。

 REFRESH MATERIALIZED VIEW customer_orders;

最后一点（如何处理刷新）是物化视图的致命弱点，我们将在下一节中讨论。

实时分析怎么样？ PostgreSQL 物化视图的限制

正如我们所说，PostgreSQL 物化视图是一个强大的工具，可以加速频繁运行的查询，特别是当这些查询涉及大量数据时。但物化视图有一个不太理想的方面：为了使物化视图保持最新，必须刷新它们。

这个问题造成了三个重要的限制：

刷新效率低且计算量大

刷新物化视图时，将在整个数据集上重新计算查询。在后台，当您运行刷新时，旧的物化数据将被删除，然后替换为新的、重新物化的数据。实施增量刷新（仅更新更改的数据）将使聚合过程更加高效，但很难在一致的关系数据库中正确实现。有时可以通过额外的脚本来解决问题，但它们远非直截了当，特别是对于复杂的查询或延迟数据到达的情况。

刷新不会自动运行

正如前面提到的，物化视图不会自动合并最新数据。必须通过运行REFRESH MATERIALIZED VIEW来刷新它们。在生产环境中运行手动刷新是不可行的：更现实的设置是自动刷新。

不幸的是，物化视图没有内置的自动刷新功能，因此在 PostgreSQL 中为物化视图创建自动刷新计划需要某种调度程序。这可以通过扩展在数据库内处理，也可以通过 cron 等调度程序在数据库外处理。然而，它是受管理的，因为刷新成本昂贵且需要很长时间。很容易陷入无法足够快地刷新视图的情况。

物化视图不显示最新结果

物化视图的静态性质的结果是，在查询时，它们将错过自上次刷新以来添加或更改的数据（即使刷新按计划发生）。如果您的计划窗口设置为一小时，那么您的总计将最多为一小时加上执行更新的实际时间。但当今的许多应用程序都意味着不断摄取数据流，并且通常这些应用程序必须向用户提供最新结果，以确保他们在查询视图时检索准确的信息。

遗憾的是，物化的观点受到了这些限制的限制。如果您正在根据实时数据集构建 SaaS 平台，并且新数据频繁出现，是否应该完全丢弃物化视图？

答案是不。在 Timescale 中，我们构建了一个解决方案，可以有效增强物化视图，使其更适合现代应用程序：连续聚合。

满足连续聚合：具有自动刷新功能的物化视图以进行实时分析

想象一下这样一个世界：物化视图不仅仅是静态快照，而是动态且高效地更新。您将获得所需的查询性能改进，而无需担心其他任何事情。嗯，看起来我们描述了 Timescale 的连续聚合。

连续聚合（通过 TimescaleDB 扩展可用于所有 PostgreSQL 数据库，通过 Timescale 平台可在 AWS 中使用）是通过高效、自动刷新功能和实时元素增强的物化视图。它们的外观和感觉几乎与物化视图完全相同，但允许以下功能：

• 通过刷新策略自动刷新
• 更高效的刷新过程：当刷新运行时，它只会触及自上次刷新以来更改的数据
• 最新结果，扩展了可以利用物化视图的用例（例如实时分析、实时仪表板、报告等）

使刷新自动化并节省资源

创建连续聚合与创建物化视图非常相似（也可以作为常规 PostgreSQL 表进行查询）：

 CREATE MATERIALIZED VIEW hourly_sales WITH (timescaledb.continuous) AS SELECT time_bucket(INTERVAL '1 hour', sale_time) as hour, product_id, SUM(units_sold) as total_units_sold FROM sales_data GROUP BY hour, product_id;

但与物化视图不同的是，创建刷新策略非常简单。您可以轻松定义数据库内的刷新间隔，确保连续聚合自动定期更新。

下面的示例设置刷新策略以每 30 分钟更新一次连续聚合。 end_offset参数定义了数据刷新的时间范围， schedule_interval设置了连续聚合的刷新频率：

 -- Setting up a refresh policy SELECT add_continuous_aggregate_policy('hourly_sales', end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '30 minutes');

当此刷新策略生效时，该过程将比我们使用普通物化视图更有效。与运行REFRESH MATERIALIZED VIEW不同，刷新连续聚合时，Timescale 不会删除所有旧数据并根据其重新计算聚合：引擎仅针对最近的刷新周期（例如 30 分钟）运行查询并添加它到具体化。

类似地，识别在最后一个时期执行的UPDATE和DELETE ，重新计算涉及它们的块（分区）。 （基于 Timescale 的连续聚合超表，它们是自动分区的 PostgreSQL 表。这是一个巨大的优势，允许引擎在数据更改时仅重新计算特定分区而不是整个表。）

显示实时分析的最新结果

但是，连续聚合如何解决查看最新结果的问题呢？如果上次刷新后添加了新数据，并且我查询连续聚合，会发生什么情况？

为了允许此功能，我们添加了实时聚合功能到连续聚合。 当启用实时聚合时，并且您查询连续聚合，您将看到的结果将结合两部分：

• 底层物化视图中的物化数据，已在上次刷新中更新。
• 最新的、尚未具体化的原始数据，仍然专门存在于您的基表（或者准确地说是超表）中。

此功能将物化视图从静态快照转换为动态实体，确保存储的数据不仅仅是历史反映，而且是底层数据集的最新表示。

使用连续聚合：示例

即使这一切听起来不错，（希望）通过一个例子可以更好地结合在一起。

想象一下交通机构和拼车公司使用的平台。该平台包含一个仪表板，公司可以在其中查看其车队状态的概述，包括一个包含关键指标最新状态的表格和两个可视化效果，显示指标在特定日期和一周内的表现。

为了支持这个应用程序，我们首先要有一个超级表，其中不断插入有关游乐设施的数据。超级表可能看起来像这样：

 CREATE TABLE rides ( ride_id SERIAL PRIMARY KEY, vehicle_id INT, start_time TIMESTAMPTZ NOT NULL, end_time TIMESTAMPTZ NOT NULL, distance FLOAT NOT NULL, price_paid FLOAT NOT NULL ); SELECT create_hypertable('rides', 'start_time');

超级表速度非常快且可扩展性非常好——即使该表有数十亿行，它也将保持高性能。

为了通过提供实时概览来为表格提供支持，我们将使用连续聚合来按 30 分钟存储数据。这将使该过程保持快速和响应：

 -- Create continuous aggregate for live overview CREATE MATERIALIZED VIEW live_dashboard WITH (timescaledb.continuous, timescaledb.materialized_only=false)) AS SELECT vehicle_id, time_bucket(INTERVAL '30 minute', start_time) as minute, COUNT(ride_id) as number_of_rides, AVG(price_paid) as average_price FROM rides GROUP BY vehicle_id, minute;

 -- Set up a refresh policy SELECT add_continuous_aggregate_policy('live_dashboard', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '15 minute');

在前面的代码中， end_offset参数确保聚合不会立即尝试刷新最新数据，从而允许一些缓冲时间来适应数据到达中的任何滞后。将end_offset设置为10 minutes意味着聚合将刷新至少 10 分钟前的数据，确保不会由于数据流入的微小延迟而错过更新。在实际用例中，您可以根据在数据管道中观察到的平均延迟来调整此值。

为了支持提供每日视图的可视化，我们将创建第二个连续聚合。在此图表中，数据按小时显示，因此我们不需要像上一个那样每分钟的粒度：

 -- Create continuous aggregate for daily overview CREATE MATERIALIZED VIEW hourly_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 hour', start_time) as hour, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 day' GROUP BY vehicle_id, hour;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('hourly_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL `1 hour`);

最后，为了提供每周视图的图表，我们将创建另一个连续聚合，这次按天聚合数据：

 -- Create continuous aggregate to power chart with weekly overview CREATE MATERIALIZED VIEW daily_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 day', start_time) as day, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 week' GROUP BY vehicle_id, day;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('daily_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '1 day);

PS 为了使定义连续聚合的体验更加高效， Timescale引入了分层连续聚合在 TimescaleDB 2.9 中。一旦熟悉了连续聚合，您就可以开始在其他连续聚合之上创建它们 - 例如，在前面的示例中，您还可以在按分钟聚合之上定义每小时聚合。

结论

即使 PostgreSQL 最初并不是为需要处理大型实时数据集的应用程序构建的，你猜怎么着——这些类型的工作负载现在无处不在。然而，PostgreSQL 附带了有助于完成此任务的功能。物化视图是最强大的视图之一，因为它们允许预先计算查询结果并将其存储在磁盘上以便快速检索。

然而，物化视图具有三个重要的局限性。首先，触发刷新的计算效率非常低。其次，即使设置这些自动刷新也不是一个无缝的过程。第三，物化视图不显示最新结果，因为它们排除了自上次刷新以来添加或修改的数据。

这些限制使得物化视图成为许多现代应用程序不切实际的解决方案。为了解决这个问题，我们构建了连续聚合。这些是 PostgreSQL 物化视图，您可以在其中轻松定义刷新策略，以便刷新自动发生。这些刷新也是增量式的，因此效率更高。最后，连续聚合允许您将已具体化的数据与自上次刷新以来添加和修改的原始数据组合起来，确保您只能获得最新的结果。

如果您在硬件上运行 PostgreSQL，则可以通过以下方式访问连续聚合安装 TimescaleDB 扩展。如果您使用 AWS，请查看 Timescale 平台。前 30 天免费。

由卡洛塔·索托和马特·阿耶撰写。