大规模批量处理：PostgreSQL如何在性能上超越BigQuery的经验分享

照片由 Jan Antonin Kolar 拍摄，来自 Unsplash。

BigQuery 是 Google Cloud 提供的一种数据仓库，帮助用户管理和分析大量的数据。BigQuery 中的数据以列式存储，每一列可以存储在同一个或不同的机器上。查询数据时，根据扫描的字节数计费。存储和流式插入分别计费。

我们的日常工作包括处理大约5500万条记录，每条记录包含127个字段。所有这些数据都存储在BigQuery中，因为它可以几乎即时响应任何查询。尽管数据量如此庞大，但我们没有可以用来对表进行分区或聚簇的字段。

我们每隔一段时间就需要执行的一项任务是更新某些数据字段。这个更新操作只涉及少数几条记录。当涉及到更新时，BigQuery 并不是一个适合更新的数据仓库，因为每次更新都会带来较高的成本。为了解决这个问题，我们考虑了两种主要的解决方案：

• 代码优化工作
• 基础设施升级

代码优化更快也更简单，因为不需要任何数据移动。

由于我们只需要对少量记录进行更新操作，我们首先决定将表拆分为以下两张：clean_records 和 unclean_records。clean_records 是那些不需要更新的记录。对于 unclean_records，我们使用特定的批处理规则读取它们，在 pandas DataFrame 中实时执行更新，并将它们追加到 clean_records 表。由于 BigQuery 中没有执行任何更新，而追加操作免费，我们认为这些操作成本不高。

BigQuery 代码设计图 — 作者图

然而，我们发现，当我们读取这些unclean_records时，不得不执行一个SELECT * <project>.<dataset>.unclean_records操作，这产生的读取费用竟然比直接在表上执行UPDATE操作还要高，因为更新时只读取了127个字段中的6个。

我们探索了可以处理这种规模数据的数据库。我们发现PostgreSQL是个不错的选择来做这项操作。首先，我们将所有数据从BigQuery迁移到GCS的CSV文件中。之后，我们使用了优化的脚本来加载所有文件到PostgreSQL中。我们现在唯一的成本是数据库服务器的磁盘空间（300GB）和计算资源（4核，16GB内存）。

我们使用的是对原始表进行更新操作的源代码。这花费了很长时间，预计需要超过20天才能完成操作。然后我们将表拆分成了两个表——clean_records 和 unclean_records，仅对 unclean_records 表进行更新。然而，即使进行了这些更改，操作仍然预计需要超过10天才能完成。我们还为用于批次更新依据的ID字段添加了索引。

我们已经将Python代码优化到了极致，唯一能节省我们时间的方法就是调整PostgreSQL的配置参数。在我们深入讨论数据库配置优化之前，我们首先来了解一下PostgreSQL的一些内部运作机制。

注：文中“管道”一词比喻数据库的内部工作机制。

在 Postgres 中，每个数据库记录都会被分配一个唯一的 ID，称为 ctid。这个 ctid 用于确定元组在堆中的实际位置。我们还在 id 字段上建立了索引，并创建了一个 B 树，每个叶子节点都包含 (id, ctid) 这样的键值对。当我们运行一个带有 id 条件的查询时，它会扫描 B 树，确定对应行的 ctid，然后根据这些 ctid 或 ctids，从堆中获取并返回相应的元组。

Postgres的内部存储 — 作者绘制

当一个元组被更新时，Postgres 并不会就地更新，而是添加旧的行并更新必要的字段。在这一过程中，新的元组会有一个新的 ctid。旧的元组仍然存在于堆中，并且不会被删除。

ID-4的名称已更新 — 图片作者

当我们对上述表执行 SQL 查询 SELECT * 时，只会返回 ID 为 4 的一条记录。Postgres 有一个内部机制来确保在查询时返回最新的 ID 4 对象。

定期地，一个名为 auto-vacuum 的 PostgreSQL 进程会启动，它会找到已标记为删除的行（技术上称为 死行/死元组），从堆中删除它们，并移除与这些行相关的 ctid。auto-vacuum 进程还会更新为此表创建的任何索引的 B-树。

此外，PostgreSQL 还有一个 查询规划器 ，它根据 表统计信息 查找访问和检索数据的最佳路径。auto-analyze 的目的是在添加、修改或删除某些行时更新 表统计信息。此外，系统也会定期自动触发 auto-analyze 来更新表统计信息。

表的统计数据如下所示: 这些信息指表内的数据分布和特征。

默认启用了auto-vacuum（自动清理）和auto-analyze（自动分析）。不过，它们何时生效取决于以下两个设置。

autovacuum 真空比例因子 = 0.2  
autovacuum 分析比例因子 = 0.1

这意味着，在更新或删除的行数未达20%之前，auto-vacuum 过程不会运行，因此会将这些死元组保留在内存中。同样地，在有超过10%的行未被更新或删除之前，表统计信息也不会被更新。这表示这些死元组会存留在堆和 b-树中，表统计信息也不会被更新，这也意味着查询规划器无法制定优化的查询计划。

在我们的用例中，clean_records 表大约有 4700万条 记录，而 unclean_records 表大约有 760万条 记录。更新操作是在 unclean_records 表上进行的，但是由于默认的 autovacuum_vacuum_scale_factor = 0.2，这意味着当大约 150万条 记录被更新时，自动真空才会启动。同样地，自动分析过程不会触发，直到大约 76万条 记录被标记为死亡。这不仅阻止了 b-tree 索引的更新，还阻止了查询计划程序利用最新的表统计信息，从而使得 批量更新过程变慢了。

更新代码结构，作者供图

我们已将上述缩放比例更新为一个特定表格的以下值。

将表unclean_records的autovacuum_vacuum_scale_factor设置为0.1；
将表unclean_records的autovacuum_analyze_scale_factor设置为0.05；
-- 这些命令用于调整自动Vacuum和分析的比例因子，以优化表的维护。

我们也调整了一些其他配置，比如工作内存空间、共享缓存区和缓存，以便更高效地利用系统硬件资源。

或者使用聊天符号表示标题：👉结果

通过对 PostgreSQL 配置和 Python 脚本的更新，我们仅用不到一个小时就完成了整个批处理操作，几乎没花什么钱。

在我们的下游使用场景中，我们只需要根据不同的标准来查询数据，并且主要是只读。我们利用了 GCP 的 dataflow 模板将数据从 PostgreSQL 迁移到了 BigQuery。

从 PostgreSQL 切换到 BigQuery，我们将成本降低到了其原始成本的不到 1%，并且可以在 一天之内 就实现这些结果。通过将数据从 BigQuery（一种 OLAP 数据库）迁移到 PostgreSQL（一种 OLTP 数据库），我们实现了这一点。此外，通过调整 PostgreSQL 的配置，我们加快了批量操作的速度。

我们试了一下不同的方法，没有直接在 unclean_records 表中进行原地更新，而是选择从 unclean_record 表中读取数据，然后在 pandas 中进行修改操作，再追加到 clean_records 表中，最后删除 unclean_records 表中的这批数据。与我们的假设相反，这种方法比之前提到的方法更慢，因为 auto-vacuum 和 auto-analyzed 频繁触发。

在我们这篇博客文章即将结束时，我想花一点时间向这些不可或缺的伙伴们表达我诚挚的感激之情，特别是 Sachin Gupta，他在 PostgreSQL 优化方面给予了非常重要的指导。