立即开始为您的 PostgreSQL 加速。
作者：Carlota Soto、Mat Arye 和 Doug Ortiz
使用户能够访问实时数据分析是许多现代应用程序的关键功能。想象一下，您正在使用自己喜欢的 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 物化视图是加速频繁运行的查询的强大工具，尤其是当这些查询处理大量数据时。但物化视图有一个不太理想的方面：为了使物化视图保持最新，它们必须刷新。这个单一问题造成了三个重要的局限性：
刷新物化视图时，会在整个数据集上重新计算查询。在幕后，当您运行刷新时，旧的物化数据实际上会被删除，然后用新的重新物化数据替换。实现 增量刷新（只更新更改的数据）将使聚合过程更有效率，但在一致的关系数据库中正确实现非常困难。有时可以使用额外的脚本进行变通，但这远非易事，尤其是对于复杂的查询或如果数据延迟到达。
如前所述，物化视图不会自动合并最新数据。它们必须通过运行 REFRESH MATERIALIZED VIEW 来刷新。在生产环境中手动运行刷新不可行：更现实的设置是自动执行刷新。
不幸的是，物化视图没有内置的自动刷新功能，因此在 PostgreSQL 中为物化视图创建自动刷新计划需要某种计划程序。这可以在数据库内使用扩展来处理，也可以在数据库外使用计划程序（如 cron）来处理。但是，它是被管理的，因为刷新很昂贵且需要很长时间。很容易陷入无法足够快地刷新视图的情况。
物化视图静态特性的一个后果是，在查询时，它们会错过自上次刷新（即使刷新是按计划进行的）以来添加或更改的数据。如果您的计划窗口设置为一小时，那么您的聚合将最多落后一小时加上实际执行更新的时间。但如今，许多应用程序都意味着数据流是持续不断的，而且通常情况下，这些应用程序必须向用户提供最新的结果，以确保他们在查询视图时检索到准确的信息。
很遗憾物化视图受到这些限制的约束。如果您正在从实时数据集中构建一个 SaaS 平台，而新数据频繁地涌入，是否应该完全放弃物化视图？
答案是否定的。在 Timescale 中，我们构建了一个解决方案，有效地增强了物化视图，使其更适合现代应用程序：持续聚合。
想象一个世界，在这个世界中，物化视图不再只是静态的快照，而是动态且高效地更新。您将获得所需的查询性能提升，而无需担心其他任何事情。好吧，这似乎是我们描述的 Timescale 的持续聚合。
持续聚合（通过 TimescaleDB 扩展提供给所有 PostgreSQL 数据库，并且在 AWS 中通过 Timescale 平台提供）是物化视图，增强了高效的自动刷新功能和实时元素。它们看起来和感觉几乎与物化视图完全相同，但允许以下功能
通过刷新策略实现自动刷新
更有效的刷新过程：刷新运行时，它只会触及自上次刷新以来更改的数据
最新的结果，扩展了可以利用物化视图的用例（如实时分析、实时仪表板、报表等）
创建持续聚合与创建物化视图非常相似（它也可以像普通 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 分钟 表示聚合将刷新至少 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);附注：为了使定义连续聚合的过程更加高效，Timescale 在 TimescaleDB 2.9 中引入了分层连续聚合。熟悉连续聚合后，您就可以开始在其他连续聚合的基础上创建它们 - 例如，在前面的示例中，您也可以在按分钟聚合的基础上定义按小时的聚合。
虽然自己动手解决问题对大多数人来说很有吸引力，但当涉及到利用 PostgreSQL 触发器实现连续聚合时，各种不可预见和未计入的挑战将很快浮出水面。
一些很快想到的问题是性能开销、并发问题、维护复杂性、触发器级联、测试和调试、资源消耗以及查询性能。以上列表并非详尽无遗，但应该能让您了解如果您决定使用 PostgreSQL 触发器实现实时分析解决方案，将会面临的挑战。
让我们深入探讨上述每个挑战。
性能开销：默认情况下，触发器会对每个数据修改操作产生额外的处理时间；多个触发器会增加处理时间和资源消耗。
并发问题：触发器在与它们关联的 DML（数据操作语言）操作相同的交易上下文中运行。仅此一项就可能导致死锁和争用，更不用说多个交易同时尝试更新相同数据的争用情况了。
数据一致性：维护源表和聚合表之间的数据一致性至关重要。如果触发器在更新或聚合过程中失败或遇到错误，会导致不一致，这将难以跟踪和调试。
维护复杂性：触发器管理会增加数据库模式和维护任务的复杂性，需要确保触发器创建、维护和更新都正确、高效且可靠。
触发器级联：当触发器“触发”其他触发器时，通常会导致性能和并发问题。
测试和调试：彻底的测试和调试将不仅必要，而且是强制性的。此过程无法涵盖所有场景，这意味着将出现意外情况和边缘情况。
资源消耗：数据库管理员将需要大量参与，因为需要监控资源、CPU、内存和磁盘空间。
查询性能：聚合表的設計和索引需要仔细考虑，以确保高效的查询执行。
最终，一个类似的比较问题可能是：我应该买一辆车，还是应该自己造一辆？
这听起来像个好主意吗？
即使 PostgreSQL 最初并非为需要处理大量实时数据集的应用程序而构建，但事实是，这些类型的负载现在无处不在。但是，PostgreSQL 提供了有助于完成此任务的功能。物化视图是最强大的功能之一，因为它允许预先计算查询结果并将它们存储在磁盘上以便快速检索。
但是，物化视图有三个重要的局限性。首先，触发刷新非常低效。其次，即使设置这些自动刷新也不是一个无缝的过程。第三，物化视图不会显示最新结果，因为它们不包括自上次刷新以来添加或修改的数据。
这些局限性使物化视图对于许多现代应用程序而言成为不切实际的解决方案。为了解决这个问题，我们构建了连续聚合。这些是 PostgreSQL 物化视图，您可以在其中轻松定义刷新策略，以便刷新自动发生。这些刷新也是增量的，因此效率更高。最后，连续聚合允许您将物化数据与自上次刷新以来添加和修改的原始数据相结合，确保您只能获得最新结果。
“我不得不几次向同事解释，当他们询问数据库处理实时事件的能力时，他们说：‘别担心，Timescale 会处理好。’知道使用连续聚合进行扩展时，只需花一个小时设置就可以，我们以后就不用再担心了，这确实让人放心。”
Björn Olafur Johannsson，AVO 软件工程师
如果您在自己的硬件上运行 PostgreSQL，可以通过 安装 TimescaleDB 扩展 来访问连续聚合。 如果您使用的是 AWS，请查看 Timescale 平台。前 30 天免费。