关于连续聚合

在现代应用程序中，数据通常增长非常快。这意味着将数据聚合为有用的摘要可能会变得非常缓慢。Timescale Cloud 连续聚合使数据聚合变得闪电般快速、准确且容易。

如果您非常频繁地收集数据，您可能希望将数据聚合到分钟或小时级别。例如，如果一个 IoT 设备每秒读取一次温度，您可能希望找到每小时的平均温度。每次运行此查询时，数据库都需要扫描整个表并重新计算平均值。

连续聚合是一种超表，随着新数据的添加或旧数据的修改，它会在后台自动刷新。您的数据集的更改会被跟踪，连续聚合背后的超表会在后台自动更新。

您无需手动刷新连续聚合，它们会在后台持续增量更新。连续聚合的维护负担也比常规 PostgreSQL 物化视图低得多，因为每次刷新时不需要从头开始创建整个视图。这意味着您可以专注于处理数据，而不是维护数据库。

由于连续聚合基于超表，您可以像查询其他表一样查询它们。这包括行式存储中的连续聚合、压缩到列式存储中，或分层到对象存储中。您甚至可以在连续聚合之上创建连续聚合——以实现更精细的聚合。

实时聚合使您能够将物化视图中的预聚合数据与最新的原始数据结合起来。这使您在每次查询时都能获得最新结果。

在 TimescaleDB v2.13 及更高版本中，实时聚合默认处于禁用状态。在早期版本中，实时聚合默认处于启用状态；当您创建连续聚合时，对该视图的查询会包含最新原始数据的结果。

有三种主要方法可以简化聚合：物化视图、连续聚合和实时聚合。

物化视图 是标准的 PostgreSQL 功能。它们用于缓存复杂查询的结果，以便您以后可以重复使用。物化视图不会定期更新，但您可以根据需要手动刷新它们。

连续聚合是 Timescale 独有的功能。它们的工作方式类似于物化视图，但随着新数据添加到数据库中，它们会在后台自动更新。连续聚合是持续增量更新的，这意味着与物化视图相比，它们的维护资源消耗更少。连续聚合基于超表，您可以像查询其他表一样查询它们。

实时聚合是 Timescale 独有的功能。它们与连续聚合相同，但会将最新的原始数据添加到先前聚合的数据中，以提供准确和最新的结果，而无需在写入数据时进行聚合。

您可以在另一个连续聚合之上创建连续聚合。这使您能够以不同的粒度汇总数据。例如，您可能有一个包含每秒数据的原始超表。在超表上创建一个连续聚合以计算每小时数据。要计算每日数据，请在您的每小时连续聚合之上创建一个连续聚合。

有关更多信息，请参阅关于连续聚合之上的连续聚合的文档。

连续聚合支持以下 JOIN 功能

TimescaleDB 中的 JOINS 必须满足以下条件

• 仅跟踪超表的更改，并在连续聚合刷新时更新它们。标准 PostgreSQL 表的更改不被跟踪。
• 您可以使用 INNER、LEFT 和 LATERAL 连接；不支持其他连接类型。
• 不支持在连续聚合的物化超表上进行连接。
• 分层连续聚合可以在带 JOIN 子句的连续聚合之上创建，但它们本身不能有 JOIN 子句。

给定以下模式

CREATE TABLE locations (
  id TEXT PRIMARY KEY,
  name TEXT
);

CREATE TABLE devices (
  id SERIAL PRIMARY KEY,
  location_id TEXT,
  name TEXT
);

CREATE TABLE conditions (
  "time" TIMESTAMPTZ,
  device_id INTEGER,
  temperature FLOAT8
) WITH (
  tsdb.hypertable,
  tsdb.partition_column='time'
);

复制

请参阅以下关于连续聚合的 JOIN 示例

• 使用 ON 子句的单个等值条件上的 INNER JOIN

  CREATE MATERIALIZED VIEW conditions_by_day WITH (timescaledb.continuous) AS
  SELECT time_bucket('1 day', time) AS bucket, devices.name, MIN(temperature), MAX(temperature)
  FROM conditions
  JOIN devices ON devices.id = conditions.device_id
  GROUP BY bucket, devices.name
  WITH NO DATA;

  复制

• 使用 ON 子句的单个等值条件上的 INNER JOIN，并在 WHERE 子句中添加了额外条件

  CREATE MATERIALIZED VIEW conditions_by_day WITH (timescaledb.continuous) AS
  SELECT time_bucket('1 day', time) AS bucket, devices.name, MIN(temperature), MAX(temperature)
  FROM conditions
  JOIN devices ON devices.id = conditions.device_id
  WHERE devices.location_id = 'location123'
  GROUP BY bucket, devices.name
  WITH NO DATA;

  复制

• 在 WHERE 子句中指定单个等值条件上的 INNER JOIN

  CREATE MATERIALIZED VIEW conditions_by_day WITH (timescaledb.continuous) AS
  SELECT time_bucket('1 day', time) AS bucket, devices.name, MIN(temperature), MAX(temperature)
  FROM conditions, devices
  WHERE devices.id = conditions.device_id
  GROUP BY bucket, devices.name
  WITH NO DATA;

  复制

• 在多个等值条件上的 INNER JOIN

  CREATE MATERIALIZED VIEW conditions_by_day WITH (timescaledb.continuous) AS
  SELECT time_bucket('1 day', time) AS bucket, devices.name, MIN(temperature), MAX(temperature)
  FROM conditions
  JOIN devices ON devices.id = conditions.device_id AND devices.location_id = 'location123'
  GROUP BY bucket, devices.name
  WITH NO DATA;

  复制

  TimescaleDB v2.16.x 及更高版本。

• 在 WHERE 子句中指定单个等值条件的 INNER JOIN 可以与 WHERE 子句中的其他条件结合使用

  CREATE MATERIALIZED VIEW conditions_by_day WITH (timescaledb.continuous) AS
  SELECT time_bucket('1 day', time) AS bucket, devices.name, MIN(temperature), MAX(temperature)
  FROM conditions, devices
  WHERE devices.id = conditions.device_id
  AND devices.location_id = 'location123'
  GROUP BY bucket, devices.name
  WITH NO DATA;

  复制

  TimescaleDB v2.16.x 及更高版本。

• 超表和多个 Postgres 表之间的 INNER JOIN

  CREATE MATERIALIZED VIEW conditions_by_day WITH (timescaledb.continuous) AS
  SELECT time_bucket('1 day', time) AS bucket, devices.name AS device, locations.name AS location, MIN(temperature), MAX(temperature)
  FROM conditions
  JOIN devices ON devices.id = conditions.device_id
  JOIN locations ON locations.id = devices.location_id
  GROUP BY bucket, devices.name, locations.name
  WITH NO DATA;

  复制

  TimescaleDB v2.16.x 及更高版本。

• 超表和 Postgres 表之间的 LEFT JOIN

  CREATE MATERIALIZED VIEW conditions_by_day WITH (timescaledb.continuous) AS
  SELECT time_bucket('1 day', time) AS bucket, devices.name, MIN(temperature), MAX(temperature)
  FROM conditions
  LEFT JOIN devices ON devices.id = conditions.device_id
  GROUP BY bucket, devices.name
  WITH NO DATA;

  复制

  TimescaleDB v2.16.x 及更高版本。

• 超表和子查询之间的 LATERAL JOIN

  CREATE MATERIALIZED VIEW conditions_by_day WITH (timescaledb.continuous) AS
  SELECT time_bucket('1 day', time) AS bucket, devices.name, MIN(temperature), MAX(temperature)
  FROM conditions, 
  LATERAL (SELECT * FROM devices WHERE devices.id = conditions.device_id) AS devices
  GROUP BY bucket, devices.name
  WITH NO DATA;

  复制

  TimescaleDB v2.16.x 及更高版本。

在 TimescaleDB 2.7 及更高版本中，连续聚合支持所有 PostgreSQL 聚合函数。这包括可并行化的聚合（如 SUM 和 AVG）和不可并行化的聚合（如 RANK）。

在 TimescaleDB 2.10.0 及更高版本中，FROM 子句支持 JOINS，但有一些限制。有关更多信息，请参阅JOIN 支持部分。

在 TimescaleDB 的旧版本中，连续聚合只支持可以由 PostgreSQL 并行化的聚合函数。您可以通过在连续聚合中聚合查询的其他部分，然后使用窗口函数查询聚合来解决这个问题。

下表总结了连续聚合中支持的聚合函数

函数、子句或功能	TimescaleDB 2.6 及更早版本	TimescaleDB 2.7、2.8 和 2.9	TimescaleDB 2.10 及更高版本
可并行化聚合函数	✅	✅	✅
不可并行化的 SQL 聚合	❌	✅	✅
ORDER BY	❌	✅	✅
有序集聚合	❌	✅	✅
假设集聚合	❌	✅	✅
聚合函数中的 DISTINCT	❌	✅	✅
聚合函数中的 FILTER	❌	✅	✅
FROM 子句支持 JOINS	❌	❌	✅

DISTINCT 在聚合函数中有效，但在查询定义中无效。例如，对于表

CREATE TABLE public.candle(
symbol_id uuid                     NOT NULL,
symbol    text                     NOT NULL,
"time"    timestamp with time zone NOT NULL,
open      double precision         NOT NULL,
high      double precision         NOT NULL,
low       double precision         NOT NULL,
close     double precision         NOT NULL,
volume    double precision         NOT NULL
);

复制

• 以下有效

  CREATE MATERIALIZED VIEW candles_start_end
  WITH (timescaledb.continuous) AS
  SELECT time_bucket('1 hour', "time"), COUNT(DISTINCT symbol), first(time, time) as first_candle, last(time, time) as last_candle
  FROM candle
  GROUP BY 1;

  复制
• 这无效

  CREATE MATERIALIZED VIEW candles_start_end
  WITH (timescaledb.continuous) AS
  SELECT DISTINCT ON (symbol)
  symbol,symbol_id, first(time, time) as first_candle, last(time, time) as last_candle
  FROM candle
  GROUP BY symbol_id;

  复制

如果您希望在 TimescaleDB 的更高版本中获得旧行为，请在创建连续聚合时将 timescaledb.finalized 参数设置为 false。

连续聚合包括

• 用于存储聚合数据的物化超表
• 物化引擎，用于将原始底层表中的数据聚合到物化超表
• 失效引擎，用于确定何时需要由于数据更改而重新物化数据
• 查询引擎，用于访问聚合数据

连续聚合从原始超表获取原始数据，对其进行聚合，并将聚合数据存储在物化超表中。当您查询连续聚合视图时，聚合数据会根据需要返回给您。

以上述温度示例，物化表如下所示

物化表存储为 Timescale 超表，以利用超表提供的扩展和查询优化。物化表包含查询中每个 group-by 子句的一个列，以及查询中每个聚合的一个 aggregate 列。

有关更多信息，请参阅物化超表。

物化引擎执行两个事务。第一个事务会阻塞所有 INSERT、UPDATE 和 DELETE 操作，确定要物化的时间范围，并更新失效阈值。第二个事务会解除其他事务的阻塞，并物化聚合。第一个事务非常快，大部分工作在第二个事务中完成，以确保工作不会干扰其他操作。

超表中数据的任何更改都可能使某些物化行失效。失效引擎会检查以确保系统不会被失效操作淹没。

幸运的是，时序数据意味着几乎所有的 INSERT 和 UPDATE 都带有近期时间戳，因此失效引擎不会物化所有数据，而是物化到称为物化阈值（materialization threshold）的特定时间点。设置此阈值是为了确保绝大多数 INSERT 操作都包含较新的时间戳。这些数据点从未被连续聚合物化，因此无需额外工作来通知连续聚合它们已被添加。当物化器下次运行时，它负责确定可以在不使连续聚合失效的情况下物化多少新数据。然后，它物化较新的数据并将物化阈值向前推进。这确保了阈值滞后于数据更改常见的时点，并且大多数 INSERT 操作不需要任何额外的写入。

当早于失效阈值的数据发生更改时，会记录更改行的最大和最小时间戳，这些值用于确定聚合表中哪些行需要重新计算。这种日志记录确实会产生一些写入负载，但由于阈值滞后于当前正在更改的数据区域，因此写入操作较小且不常见。