PostgreSQL 聚合如何工作以及它如何启发我们的超函数设计

了解 PostgreSQL 聚合入门，了解 PostgreSQL 的实现如何在我们构建 TimescaleDB 超函数及其与高级 TimescaleDB 功能的集成时激发我们的灵感——以及这对开发人员意味着什么。在 Timescale，我们的目标是始终专注于开发人员体验，我们非常谨慎地设计我们的产品和 API，以使其对开发人员友好。我们相信，当我们的产品易于使用且可供广大开发人员使用时，我们将使他们能够解决各种不同的问题——从而构建解决大问题的解决方案。这种对开发人员体验的关注是我们在 TimescaleDB 设计的早期就决定在 PostgreSQL 之上构建的原因。我们当时相信，正如我们现在所做的那样，建立在世界上增长最快的数据库上将为我们的用户带来许多好处。或许这些优势中最大的一个是开发人员的生产力：开发人员可以使用他们熟悉和喜爱的工具和框架，并利用他们所有的 SQL 技能和专业知识。今天，有近 300 万个活跃的 TimescaleDB 数据库在各行各业运行任务关键型时间序列工作负载。时间序列数据很快就会出现，有时每秒生成数百万个数据点（阅读更多关于时间序列数据的信息）。由于信息量和速度如此之快，时间序列数据的查询和分析很复杂。我们将 TimescaleDB 构建为专门为时间序列构建的关系数据库，以降低复杂性，以便开发人员可以专注于他们的应用程序。因此，我们以开发人员的经验为核心构建，我们不断发布功能以进一步实现这一目标，包括连续聚合、用户定义的操作、信息视图，以及最近的 TimescaleDB 超函数：其中的一系列 SQL 函数TimescaleDB 使在 PostgreSQL 中操作和分析时间序列数据更容易，代码行更少。为了确保我们在规划新的超功能特性时始终专注于开发人员体验，我们建立了一套“设计约束”来指导我们的开发决策。遵守这些准则可确保我们的 API：

这在实践中是什么样子的？在这篇文章中，我解释了这些约束如何导致我们在 TimescaleDB 超函数中采用两步聚合，两步聚合如何与其他 TimescaleDB 功能交互，以及 PostgreSQL 的内部聚合 API 如何影响我们的实现。我们选择了这种设计模式，而不是更常见且看似更简单的一步聚合方法，在这种方法中，单个函数封装了内部聚合和外部访问器的行为：继续阅读有关为何快速聚合方法的更多信息当你开始做更复杂的事情（比如将函数组合成更高级的查询）以及在幕后，几乎所有的 PostgreSQL 聚合如何做一个两步聚合的版本时，它就会崩溃。在我们构建 TimescaleDB 超函数、连续聚合和其他高级功能时，您将了解 PostgreSQL 实现如何启发我们——以及这对开发人员意味着什么。如果您想立即开始使用超功能，请创建您的免费试用帐户并开始分析 🔥。 （TimescaleDB 超函数预安装在我们托管的云原生关系时间序列数据平台 Timescale Forge 实例上）。 5 或 6 年前，当我第一次开始学习 PostgreSQL 时（我是一名电化学家，处理大量电池数据，正如我在上一篇关于时间加权平均值的文章中提到的），我遇到了一些性能问题。我试图更好地了解数据库内部正在发生的事情以提高其性能——那时我发现了 Bruce Momjian 关于 PostgreSQL Internals Through Pictures 的演讲。布鲁斯以其富有洞察力的演讲（以及他对领结的偏爱）在社区中广为人知，他的会议对我来说是一个启示。从那时起，它们一直是我理解 PostgreSQL 工作原理的基础。他把事情解释得如此清楚，当我能想象发生的事情时，我总是学得最好，所以“通过图片”部分真的对我有帮助——并且一直困扰着我。所以接下来的一点是我试图通过图片解释一些 PostgreSQL 内部结构来引导 Bruce。系好领结，准备好学习

我们已经写过我们如何使用自定义函数和聚合来扩展 SQL，但是我们还没有确切地解释它们之间的区别。 SQL 中的聚合函数和“常规”函数之间的根本区别在于，聚合从一组相关行中生成单个结果，而常规函数为每一行生成一个结果：这并不是说函数可以'没有来自多列的输入；他们只需要来自同一行。另一种思考方式是函数通常作用于行，而聚合作用于列。为了说明这一点，让我们考虑一个包含两列的理论表 `foo`： 并且只有几个值，所以我们可以很容易地看到发生了什么：函数 best() 将为每个列生成 bar 和 baz 列中的最大值行：SELECT max(bar) as bar_max, max(baz) as baz_max FROM foo; bar_max | baz_max ---------+--------- 3 | 6

使用上述数据，下面是我们聚合某些内容时发生的情况的图片：聚合从多行获取输入并生成单个结果。这是它和函数之间的主要区别，但它是如何做到的呢？让我们看看它在幕后做了什么。在幕后，PostgreSQL 中的聚合逐行工作。但是，那么聚合如何知道前几行的任何信息呢？好吧，聚合存储一些关于它之前看到的行的状态，当数据库看到新行时，它会更新内部状态。对于我们一直在讨论的 max() 聚合，内部状态只是我们迄今为止收集到的最大值。当我们开始时，我们的内部状态是 NULL，因为我们还没有看到任何行：而且我们看到 bar (2.0) 的值大于我们当前的状态 (1.0)，所以我们更新状态：

我们将它与当前状态进行比较，取最大值，然后更新我们的状态：最后，我们没有更多的行要处理，所以我们输出我们的结果：总而言之，每一行进来，与我们当前的状态，然后状态会更新以反映新的最大值。然后下一行进来，我们重复这个过程，直到我们处理完所有行并输出结果。处理每一行并更新内部状态的函数有一个名称：状态转换函数（或简称为“转换函数”）。聚合的转换函数将当前状态和传入行的值作为参数并产生一个新的状态。它的定义是这样的，其中 current_value 表示来自传入行的值，current_state 表示在前几行上建立的当前聚合状态（如果我们还没有得到任何，则为 NULL），而 next_state 表示分析传入行后的输出：所以，max() 聚合有一个简单的状态，它只包含一个值（我们见过的最大的值）。但并非 PostgreSQL 中的所有聚合都具有如此简单的状态。为了计算它，我们将总和和计数存储为我们的内部状态，并在处理行时更新我们的状态：

但是，当我们准备输出 avg 的结果时，我们需要将 sum 除以计数：聚合内部还有另一个函数执行此计算：final 函数。一旦我们处理了所有的行，最终的函数就会获取状态并执行它需要的任何操作来产生结果。它是这样定义的，其中 final_state 表示转换函数在处理完所有行后的输出： 总结一下：当聚合扫描行时，它的转换函数更新其内部状态。一旦聚合扫描了所有行，它的最终函数就会产生一个结果，该结果返回给用户。这里要注意一件有趣的事情：转换函数被调用的次数比最终函数多很多：每行调用一次，而每组行调用一次最终函数。现在，在每次调用的基础上，转换函数本质上并不比最终函数更昂贵——但是因为通常进入聚合的行比出来的行多几个数量级，转换函数步骤成为最昂贵的部分迅速地。当您以高速率摄取大量时间序列数据时尤其如此；优化聚合转换函数调用对于提高性能很重要。因为转换函数是在每一行上运行的，所以一些有进取心的 PostgreSQL 开发者会问：如果我们将转换函数的计算并行化呢？

我们可以通过实例化转换函数的多个副本并将行的子集传递给每个实例来并行运行它。然后，每个并行聚合将在它看到的行子集上运行转换函数，产生多个（部分）状态，每个并行聚合一个。但是，由于我们需要聚合整个数据集，我们不能在每个并行聚合上单独运行最终函数，因为它们只有一些行。所以，现在我们已经有点麻烦了：我们有多个部分聚合状态，最终函数仅用于处理单个最终状态 - 就在我们将结果输出给用户之前。为了解决这个问题，我们需要一种新的函数，它接受两个部分状态并将它们组合成一个，这样最终的函数就可以完成它的工作。这（恰当地）称为组合函数。我们可以在并行化聚合时创建的所有部分状态上迭代运行组合函数。然后，在我们从所有并行聚合中获得组合状态后，我们运行最终函数并得到我们的结果。并行化和 combine 函数是降低调用聚合成本的一种方法，但这不是唯一的方法。另一种降低聚合成本的内置 PostgreSQL 优化出现在如下语句中：

PostgreSQL 将优化此语句以仅评估一次 `avg(bar)` 计算，然后使用该结果两次。而且，如果我们有具有相同转换函数但不同最终函数的不同聚合？ PostgreSQL 通过在所有行上调用转换函数（昂贵的部分）然后执行两个最终函数来进一步优化！漂亮整齐！现在，这不是 PostgreSQL 聚合所能做的全部，但这是一次非常好的游览，足以让我们到达今天需要去的地方。在 TimescaleDB 中，我们为聚合函数实现了两步聚合设计模式。这概括了 PostgreSQL 内部聚合 API，并通过我们的聚合、访问器和汇总函数将其公开给用户。 （换句话说，每个内部 PostgreSQL 函数在 TimescaleDB 超函数中都有一个等效的函数。）作为复习，当我们谈论两步聚合设计模式时，我们指的是以下约定，其中我们有一个内部聚合调用：内部聚合调用返回内部状态，就像 PostgreSQL 聚合中的转换函数一样。外部访问器调用获取内部状态并将结果返回给用户，就像 PostgreSQL 中的最终函数一样。

我们还为每个聚合定义了特殊的汇总函数，它们的工作方式与 PostgreSQL 组合函数非常相似。我们向用户公开两步聚合设计模式而不是将其作为内部结构有四个基本原因： 明确区分影响聚合和访问器的参数，使性能影响更容易理解和预测 启用易于理解的汇总，具有逻辑一致的结果，在连续聚合和窗口函数中（我们对连续聚合最常见的请求之一）随着需求的变化允许对连续聚合中的下采样数据进行更容易的回顾性分析，但数据已经消失了这有点理论化，所以让我们深入研究并解释每一个。 PostgreSQL 非常擅长优化语句（正如我们在本文前面看到的，通过图片🙌），但是你必须以它可以理解的方式给它一些东西。

例如，当我们谈到重复数据删除时，我们看到 PostgreSQL 可以“找出”一个语句在查询中何时出现不止一次（即 avg(bar)）并且只运行该语句一次以避免冗余工作：然而，如果我以稍微不同的方式编写方程式并将除法移动到括号内，以便表达式 avg(bar) 不会如此整齐地重复，那么 PostgreSQL 无法弄清楚如何优化它：它不知道除法是可交换的，或者这两个查询是等价的。这是数据库开发人员要解决的复杂问题，因此，作为 PostgreSQL 用户，您需要确保以数据库可以理解的方式编写查询。由数据库不理解的等效语句引起的性能问题是相等的（或者在您编写的特定情况下是相等的，但在一般情况下不是）可能是一些最棘手的 SQL 优化问题，需要用户弄清楚。因此，在我们设计 API 时，我们尽量让用户难以无意中编写低性能代码：换句话说，默认选项应该是高性能选项。接下来，将一个简单的表定义为：

让我们看一个例子，说明我们如何在百分位近似超函数中使用两步聚合来让 PostgreSQL 优化性能。 SELECT approx_percentile(0.1, percentile_agg(val)) as p10, approx_percentile(0.5, percentile_agg(val)) as p50, approx_percentile(0.9, percentile_agg(val)) as p90 FROM foo; SELECT approx_percentile(0.1, pct_agg) as p10, approx_percentile(0.5, pct_agg) as p50, approx_percentile(0.9, pct_agg) as p90 FROM (SELECT percentile_agg(val) as pct_agg FROM foo) pct;这种调用约定允许我们使用相同的聚合，以便在后台，PostgreSQL 可以对相同聚合的调用进行重复数据删除（因此速度更快）。 PostgreSQL 不能在这里对聚合调用进行重复数据删除，因为 approx_percentile 聚合中的额外参数随每次调用而变化：因此，即使所有这些函数都可以使用在所有行上构建的相同近似值，PostgreSQL 无法知道这一点。两步聚合方法使我们能够构建我们的调用，以便 PostgreSQL 可以优化我们的代码，并且它使开发人员能够了解什么时候会更昂贵，什么时候不会。具有不同输入的多个不同聚合将很昂贵，而同一聚合的多个访问器将便宜得多。我们还选择了两步聚合方法，因为我们的一些聚合本身可以采用多个参数或选项，并且它们的访问器也可以采用选项：

选择 approx_percentile(0.5, uddsketch(1000, 0.001, val)) 作为中值，--1000 桶，0.001 目标错误 approx_percentile(0.9, uddsketch(1000, 0.001, val)) 作为 p90,10udsketch(10udsketch(approx.5percentile,10dsketch) , val)) as less_accurate_median -- 修改聚合的项得到一个新的近似值FROM foo;这是 uddsketch 的一个例子，它是一种高级聚合方法，可以采用自己的参数进行百分位数近似。 -- 注意：这是我们决定不使用的 API 示例，它不起作用 SELECT approx_percentile(0.5, 1000, 0.001, val) as mediumFROM foo;很难理解哪个参数与功能的哪个部分相关。相反，两步方法非常干净地将访问器和聚合的参数分开，其中聚合函数在我们最终函数的输入中的括号中定义：通过明确哪个是哪个，用户可以知道如果他们改变聚合的输入，他们将获得更多（昂贵的）聚合节点，=而访问器的输入更改成本更低。所以，这些是我们公开 API 的前两个原因——以及它允许​​开发人员做什么。最后两个原因涉及连续聚合以及它们与超函数的关系，所以首先，快速回顾一下它们是什么。

TimescaleDB 包含一个称为连续聚合的功能，该功能旨在使对非常大的数据集的查询运行得更快。 TimescaleDB 连续聚合在后台连续增量地存储聚合查询的结果，所以当你运行查询时，只需要计算发生变化的数据，而不是整个数据集。在我们上面对 combine 函数的讨论中，我们介绍了如何在每一行上计算转换函数的昂贵工作，并将行拆分到多个并行聚合上以加快计算速度。 TimescaleDB 连续聚合做一些类似的事情，除了它们随着时间的推移而不是在同时运行的并行进程之间分散计算工作。连续聚合计算过去某个时间插入的行子集的转换函数，存储结果，然后在查询时，我们只需要计算我们没有的最近时间的一小部分原始数据' t 尚未计算。当我们设计 TimescaleDB 超函数时，我们希望它们在连续聚合中运行良好，甚至为用户开辟新的可能性。假设我从上面的简单表中创建了一个连续聚合，以 15 分钟为增量计算总和、平均值和百分位数（后者使用超函数）： CREATE MATERIALIZED VIEW foo_15_min_aggWITH (timescaledb.continuous)AS SELECT id, time_bucket(' 15 min'::interval, ts) 作为bucket, sum(val), avg(val), percentile_agg(val)FROM fooGROUP BY id, time_bucket('15 min'::interval, ts);然后，如果我回来并想将其重新聚合为小时或天，而不是 15 分钟的存储桶 - 或者需要跨所有 ID 聚合我的数据怎么办？我可以为哪些聚合体做到这一点，哪些不能？

我们想要通过两步聚合解决的问题之一是如何向用户传达什么时候可以重新聚合“可以”，什么时候不能。 （通过“好的”，我的意思是您将从重新聚合的数据中获得与直接在原始数据上运行聚合相同的结果。） SELECT sum(val) FROM tab;-- 等效于：SELECT sum( sum) FROM (SELECT id, sum(val) FROM tab GROUP BY id) s; SELECT avg(val) FROM tab;-- 不等同于：SELECT avg(avg) FROM (SELECT id, avg(val) FROM tab GROUP BY id) s;聚合返回内部聚合状态。 sum 的内部聚合状态是 (sum)，而average 的内部聚合状态是 (sum, count)。聚合的组合和转换函数是等效的。对于 sum()，状态和操作是相同的。对于 count()，状态是相同的，但是转换和组合函数对它们执行不同的操作。 sum() 的转换函数将传入值添加到状态，其组合函数将两个状态或总和相加。相反，count() 的转换函数为每个传入值增加状态，但它的组合函数将两个状态或计数总和相加。但是，您必须对每个聚合的内部结构有深入的（有时甚至是神秘的）知识，才能知道哪些符合上述标准——因此，哪些可以重新聚合。使用两步聚合方法，我们可以传达何时通过 e 重新聚合在逻辑上是一致的

......