扩展 PostgreSQL：我们如何驯服 100 亿条每日记录和 350 TB 以上的数据

今年早些时候，我们启动了公司最大的测试努力曾经。建造见解是一个允许我们的用户在 Timescale 平台中分析其查询性能的工具，我们试图收集所有客户数据库的查询统计信息，包括与每个查询相关的时间、CPU、内存使用情况和磁盘 I/O。我们需要一个能够处理大量数据、具有强大分析能力（尤其是随着时间的推移进行分析）、并且不会成为运营负担的数据库。

自然地，我们选择了Timescale，这是我们以TimescaleDB为核心的成熟云平台。我们习惯于使用PostgreSQL ，并且我们构建了 TimescaleDB 来使 PostgreSQL 更快、更具可扩展性——还有什么比以我们自己的例子为生更好的呢？

描述这个测试实验的最简单方法是使用有助于量化其规模的数字。为了构建 Insights，我们需要在持续运行的生产数据库中收集查询信息。我们快速收集了超过1 万亿条有关平台上单个（经过清理的）查询的记录。

现在 Insights 已投入生产，我们每天摄取超过100 亿条新记录。单个 Timescale 服务提供的数据集每天增长约 3 TB ，目前总计超过350 TB ，并且相同的数据库服务为我们所有客户的实时仪表板提供支持。

这篇博文提供了构建 Insights 过程的幕后一瞥。要以这种规模进行运营，意味着要突破单个 Timescale 服务的极限，不仅要扩展 PostgreSQL，还要扩展我们开发人员的同理心。我们发现 Timescale 足以胜任这项任务，但也有我们需要改进的地方！

扩展 PostgreSQL 以分析整个队列的查询

我们刚刚在 Timescale 平台上推出了内部测试工作的最终产品 Insights。该数据库可观察性工具使用户可以更好地了解他们的查询的执行情况。 Insights 将各种指标（包括查询延迟、CPU、内存和 I/O）与并发执行的查询关联起来，帮助用户找到可能导致问题的低性能查询。一旦您确定有问题的查询，Insights 就会为您提供更精细的视图，例如特定时间段内的共享缓冲区和缓存命中率等方面，以帮助您找出问题。

为了实现 Insights，我们必须戴上数据库管理员的帽子🤠，并解决一些技术挑战，将 PostgreSQL 扩展到数 TB 的数据。我们希望使用 Timescale 服务作为我们的中央数据库，托管在我们的平台上，没有“特殊”基础设施。这意味着以下内容：

• 我们必须建立一个能够每天将数十亿条记录摄取到单个 Timescale 服务中的管道。 Timescale 可以处理高摄取率，并且它会定期为我们的客户执行此操作，但在生产查询负载下的这种规模水平总是令人惊讶。

  
  

• 我们的客户必须能够灵活地查询该数据库，以支持 Insights 提供的所有分析，我们不想让他们等待几分钟才能得到响应！

  
  

• 由于每天我们都会添加多个 TB，因此我们需要在单个 Timescale 服务中存储数百个 TB。较旧的数据（即超过几周的数据）需要可访问，但查询速度不一定很快。

我们如何构建它：通过（巨大）数据库支持实时分析

收集和写入大量数据

从数据收集方面，我们利用了 Timescale 平台的架构。 Timescale 在 Kubernetes (k8s) 上运行，我们有多个k8s集群在不同的地理区域运行。这些集群具有保存一项或多项客户数据库服务的节点。为了收集所有这些数据库的查询执行情况，我们从该数据库冒泡到区域级别，然后使用区域编写器将批量记录存储在为 Insights 提供支持的 Timescale 数据库服务中。

请原谅避免一些低级血腥细节的手部动作，但从广义上讲，事情就是这样运作的：整个车队运行的每个数据库都会在每次查询后创建一个记录（为了隐私和安全而进行清理），包括查询本身和我们关心的统计数据。

这些记录在节点级别收集，用标签标记以使其与来自哪个数据库服务相关联，并批量发送给区域写入器。根据需要复制区域写入器服务以处理每个区域中的负载。每个写入器从每个集群中的节点收集批次，并生成更大的批次。

然后，首先使用“COPY”将这些大批量写入临时表（无预写日志=快速）。然后，该临时表中的条目将用于更新必要的表（见下文）。临时表允许我们使用“COPY”而不必担心重复，这是通过后续操作从临时表中删除记录来处理的。

总结一下：

• 我们在多个地理区域拥有 k8s 集群。这些区域内有运行一个或多个客户数据库的节点。
• 收集来自客户数据库的经过净化的查询统计数据并进行批处理（在区域内）。
• 一旦有足够数量的事件，这些事件就会发送到我们的集中式 Timescale 数据库服务。

让我们深入了解一下为 Insights 提供支持的数据库。我们在“现成的”Timescale 服务中运行 Insights 高可用性副本，它也用于读取缩放（稍后会详细介绍）。通过“现成的”，我们的意思是我们正在使用客户可用（或即将可用😉）的所有相同功能。没有特殊的黑魔法，因为我们还控制基础设施。

支持 Insights 的数据库有相当多的部分，但我们将尽力突出显示最重要的部分。

首先，我们有两个常规 PostgreSQL 表作为“参考表”。这些表包含信息数据库元数据和查询字符串元数据。这是他们的（伪）模式：

数据库元数据

 Table "insights.cloud_db" Column | Type | Collation | Nullable | Default ---------------+--------------------------+-----------+----------+-------------------------------------- id | bigint | | not null | nextval('cloud_db_id_seq'::regclass) service_id | text | | not null | project_id | text | | not null | created | timestamp with time zone | | not null | now() Indexes: "cloud_db_pkey" PRIMARY KEY, btree (id) "cloud_db_project_id_service_id_key" UNIQUE CONSTRAINT, btree (project_id, service_id)

查询元数据

 Table "insights.queries" Column | Type | Collation | Nullable | Default ---------------+--------------------------+-----------+----------+-------------------------------------- hash | text | | not null | normalized_query | text | | not null | created | timestamp with time zone | | not null | now() Indexes: "queries_pkey" PRIMARY KEY, btree (hash)

每当新数据库开始对其运行查询时，它都会被添加到“insights.cloud_db”中。每当运行新的规范化查询时，它都会添加到“insights.queries”中。

（什么是规范化查询？这是一个所有常量都被占位符替换的查询：第一个为 $1，第二个为 $2，依此类推，因此我们只能看到查询的“形状”，而不是其值.)

到目前为止，我们只是使用常规的 Postgres，没有使用 Timescale 秘方。但数据库中的其他重要对象是 TimescaleDB 所独有的，有助于将 PostgreSQL 扩展到另一个水平。这就是神奇发生的地方：超表和连续聚合。

• Hypertables是 Timescale 的自动分区表。它们在摄取数据时自动按维度对数据进行分区，从而更容易将 PostgreSQL 表扩展到大规模。超级表是 Timescale 的构建块。我们将查询统计指标存储在一个巨大的超表中，稍后我们将看到。

  
  

• 连续聚合是 Timescale 的 PostgreSQL 物化视图的改进版本，允许增量和自动物化，事实证明这在构建 Insights 时非常有用。

让我们介绍一下如何使用这些功能在用户端实现快速分析查询。

支持（快速）实时分析

正如我们所说，我们使用一个大型超表来存储有关每个查询执行的信息。这个超级表是我们的主表，其中包含经过净化的原始指标。它看起来有点像下面这样，并配置为使用其时间戳列（ created ）在摄取数据时自动对数据进行分区。

 Table "insights.records" Column | Type | Collation | Nullable | Default -----------------------------+--------------------------+-----------+----------+--------- cloud_db_id | bigint | | not null | query_hash | text | | | created | timestamp with time zone | | not null | total_time | bigint | | | rows | bigint | | | ...

现在，我们必须允许来自用户端的快速查询，但这个表很大。为了加快速度，我们严重依赖连续聚合（使用分层连续聚合，准确地说）。

连续聚合对于提供实时、面向用户的分析（例如 Insights）的产品非常有意义。为了向用户提供可操作的信息，我们需要聚合指标：我们不会向用户显示他们运行的每个查询的日志以及旁边的统计信息 - 一些数据库每秒执行数千个查询，因此找到它将是一场噩梦任何有用的东西。相反，我们为用户群提供服务。

因此，我们不妨利用我们不向用户显示原始个人记录的事实并保留结果物化的为了快速响应。对于客户来说，这意味着在使用 Insights 时需要在精度上做出一些小牺牲（即，他们无法获得低至秒的精确时间范围），但这是一个非常合理的价格。

我们本来可以使用 PostgreSQL 物化视图，但 Timescale 的连续聚合有几个对我们特别有用的优点。我们经常刷新视图，连续聚合具有自动刷新的内置策略，并且它们是增量刷新的。

我们每五分钟刷新一次视图，因此连续聚合不是每五分钟重新生成整个物化信息，而是通过跟踪原始表中的更改来增量更新视图。以我们目前的运营规模，我们无法每五分钟从上到下扫描一次主超表，因此连续聚合的这种功能对我们来说是一个基本的“解锁”。

在这些为幕后洞察提供支持的连续聚合中，我们还将大多数有趣的统计数据聚合到一个UDD草图，这是我们收集的一部分机能亢进在时间尺度上。 UDDSketch 专门从其处理的数据中捕获近似的统计度量。这使我们能够呈现中位数、95%（或 P95）等基本指标，以及延迟或行数等更多指标。

尽管如此，在某个时刻，数据库开始做大量工作来插入所有这些原始记录，然后将它们具体化以供服务。我们在摄入和维持的量上遇到了一些限制。

为了进一步将摄取率提高到我们需要的水平，我们将 UDDSketch 生成从数据库卸载到区域编写器。现在，我们仍然将一些记录存储为“原始”记录，但我们还将其余记录推送到存储在数据库中的预先生成的草图中：

 Table "insights.sketches" Column | Type | Collation | Nullable | Default -----------------------------+--------------------------+-----------+----------+--------- cloud_db_id | bigint | | not null | query_hash | text | | | created | timestamp with time zone | | not null | total_time_dist | uddsketch | | | rows_dist | uddsketch | | | ...

UDDSketchs 最好的部分是可以很容易地连续“滚动”草图以支持更大的时间范围。使用这样的汇总，无论是在构建分层连续聚合时还是在查询时，覆盖较窄时间范围的草图都可以聚合为覆盖较宽时间范围的草图。

我们用来确保快速摄取和查询的另一个工具是只读副本。在我们的案例中，使用复制对于高可用性和性能至关重要，因为 Insights 为 Timescale 平台提供了一项面向客户的主要功能。

我们的主数据库实例非常忙于批量工作、写入数据、具体化连续聚合、运行压缩等等。 （稍后将详细介绍压缩。）为了减轻部分负载，我们让副本服务客户从 Insights 控制台读取请求。

在单个 Timescale 实例中存储数百 TB

最后，我们需要将数百个 TB 轻松地放入单个 Timescale 服务中。 Insights 数据库正在快速扩展：我们开始时约为 100 TB，现在已超过 350 TB（并且还在增加）。

为了有效地存储这么多数据，我们启用了Timescale的柱状压缩在我们的超表和所有连续聚合中（是的，您也可以压缩连续聚合，因为它们本质上是子超表！）。我们还提高了压缩率——毕竟，团队对优化压缩了解很多。

我们的主超级表上的压缩率高达 20 倍以上。

管理非常大的超表时的另一个重大胜利是压缩数据的模式可变性。我们在上一节中描述了我们的近似模式，但正如您可以想象的，我们经常更改它以添加更多统计信息等 - 能够直接在压缩的超表中执行此操作非常有用。

我们也是 Timescale 数据分层的大量用户。该功能于今年早些时候进入抢先体验阶段（等待 GA 消息很快 🔥），并允许我们通过 Timescale 数据库保持数百 TB 的访问。数据分层也被证明非常高效：我们在这里也看到了惊人的压缩率，130 TB 缩小为高度资源高效的 5 TB。

使用 Timescale 扩展 350 TB 以上 PostgreSQL 数据库：经验教训

构建 Insights 的过程向我们展示了我们的产品实际上可以走多远，但最好的事情是站在客户的立场上走几英里。我们了解了很多有关使用 Timescale 扩展 PostgreSQL 的用户体验，并且作为产品背后的工程师，我们在待办事项列表中添加了一些内容。

让我们回顾一下这一切：好的，马马虎虎的。

时间尺度亮点

• 请原谅我们的不谦虚，但我们有时对我们的产品感到非常自豪。每天将数百亿条记录提取到一个已经有数百 TB 大小的 PostgreSQL 数据库中是不容小觑的。当数据库开始加速时，我们花了几周的时间对其进行调整，但现在它就可以正常工作，无需照顾或持续监控。 （注意，这和不受监控不同，它肯定是被监控的！）

  
  

• 我们的探险家视图在此过程中至关重要，它提供了宝贵的见解并允许轻松更改配置以优化性能，例如在优化块（分区）大小时。

  
  

• 压缩对我们来说非常有效。正如我们在上一节中分享的那样，使用简单的“segmentby”选项，我们获得了令人印象深刻的压缩率（20 倍！）。对于我们来说，制定和调整政策的经验并不难——当然，我们建立了这个功能……可以说我们有一点优势。另外，将新列无缝添加到压缩数据中的能力进一步增强了我们数据库的灵活性和适应性。我们使用此功能时没有出现任何复杂情况。

  
  

• 连续聚合简化了构建不同时间段的逻辑，简化了数据分析和处理。我们使用了大量的分层连续聚合。

  
  

• Timecale 超函数中包含的近似算法简化了我们的实施并极大地扩展了我们的分析。轻松汇总草图的能力也是在面向客户的 Insights 仪表板中有效支持不同时间范围和时间段粒度的关键。

  
  

• Timescale 数据库通过数据分层提供的“无限”温存储对于扩展到数百 TB 至关重要，并且有足够的增长空间。我们当前的__ 数据分层政策__在热存储中保留三周的记录。

  
  

最后，我们使用创建自定义作业的功能来增强可观察性（例如监控作业历史记录）并实施实验性刷新策略。

改进的机会（我们注意到了）

在告诉你所有伟大的事情之后，是时候承认那些不太伟大的事情了。没有什么是完美的，包括 Timescale。我们在实施管道时遇到了一些挑战，我们并不是将这些视为不满：

Timescale 平台中的数据库可观察性可以得到改善，特别是围绕作业和连续聚合物化的性能。

TimescaleDB 主要提供基于快照的视图，这使得了解一段时间内的性能和趋势变得具有挑战性。例如，没有现成的“作业历史”表可用。早期，我们注意到连续聚合的增量实现似乎需要越来越长的时间，最终导致发现错误，但我们无法确认或量化范围。

正如我们之前提到的，定义自定义作业并在 Timescale 作业框架内运行它们的能力确实使我们能够创建一个“足够好”的版本。我们会不断查询想要监控的视图，并将任何更改插入到超表中。目前，这适用于 Insights，但我们也在努力将其中一些功能转变为内置功能，因为我们认为，一旦您将 Timescale 扩展至一切都很快的程度，它们就至关重要。

当基础数据很大时，连续聚合可能很难正确执行。

创建连续聚合时使用“__WITHNODATA”选项__ 是一个救星。明智地对待刷新策略的偏移量也很重要，这样增量刷新的数据量就不会意外地变得太大。

即使您遵循此建议，您仍然可能会得到一个连续聚合，其刷新时间比您尝试实现的数据量要长，例如，需要 30 分钟才能实现 15 分钟的数据。发生这种情况的原因是，有时连续聚合底层任务太大，无法放入内存并溢出到磁盘。

我们遇到了这个问题，由于我们发现（现已修复）一个差一的错误，该问题导致查询计划中包含额外的块，即使它们最终不会为物化提供任何数据，该问题变得更加严重。发现这个错误实际上是一个“dogfoodception”的情况：我们在构建 Insights 时发现了这个性能问题🤯。我们在 Insights 中看到的时间信息表明这里出了问题，我们通过使用 EXPLAIN 并查看计划发现了这个问题。所以我们可以告诉你它有效！

为了使实现速度更快，我们最终创建了一个自定义增量刷新策略来限制刷新增量的大小。我们正在研究是否可以将其正确推广回 TimescaleDB。

变革很难规模化。

一旦数据达到一定大小，TimescaleDB 中的某些 DDL（架构修改）操作可能会花费比理想情况更多的时间。我们已经通过多种方式体验到了这一点。

例如，向大型超表添加新索引成为一种计时练习。由于 TimescaleDB 目前不支持将“CONCURRENTLY”与“CREATE INDEX”一起使用，因此下一个最佳选择是使用其内置方法一次创建一个块索引。在我们的例子中，我们必须在创建新块后立即启动它，因此“活动”块上的锁定是最小的。也就是说，当块是新的时创建索引意味着它（几乎）是空的，因此可以快速完成并且不会阻止新的插入。

另一种难以改变的方式是更新连续聚合以添加新指标（列）。连续聚合当前不支持“ALTER”。因此，当我们想要向用户公开一个新指标时，我们创建一个全新的连续聚合“版本”，即，对于连续聚合“foo”，我们将有“foo_v2”、“foo_v3”等。这是不太理想，但目前正在工作。

最后，大规模改变压缩设置非常困难。事实上，现在对我们来说实际上是不可能的，因为它需要解压缩所有压缩块，更改设置，然后重新压缩它们，这在我们当前的规模下是不可行的。

我们继续与同事集思广益，为所有这些问题找到可行的解决方案。不仅适合我们，也适合所有 Timescale 用户。

包起来

这是相当多的信息，要全部放在一篇文章中。但如果你需要一个打包袋以后再享受也没关系！

建立洞察力对于我们的团队来说是一次深刻的经历。我们亲眼目睹了 Timescale 能走多远，达到令人印象深刻的规模数字。我们在这个过程中遇到的痛点给了我们如此多的客户同理心——这就是内部测试的美妙之处。

明年，我希望写另一篇博客文章，介绍我们如何监控另一个数量级的数据库，以及我们如何继续改善大规模使用 Timescale 的体验。

回头见！ 👋