[博客翻译]在100美元的预算上移动10亿Postgres行

由 发布于 data loading
 收藏

原文地址：https://blog.peerdb.io/moving-a-billion-postgres-rows-on-a-100-budget


受到1BR挑战的启发，我想探究将10亿行数据从Postgres迁移到Snowflake的成本。转移10亿行数据绝非易事。这一过程不仅涉及数据的传输，还需确保数据的完整性、错误恢复以及迁移后的一致性。

此任务的核心在于工具和技术的选择。我们将讨论开源工具的使用、自定义脚本、从Postgres读取数据的方法，以及Snowflake数据加载能力。诸如并行处理、高效读取Postgres的WAL日志、数据压缩以及在Snowflake上增量批量加载等关键方面将被着重强调。

我将列举并讨论一些为最小化计算、网络和仓库成本而实施的优化措施。此外，我还将突出显示作为此过程一部分所做的一些权衡。鉴于博客中涉及的大多数方法源自我在PeerDB的探索，旨在增强我们的产品——任务主要通过PeerDB完成。

我想明确指出，与成熟系统相比，这里存在一些功能差距，可能并不适用于所有用例。然而，它确实有效处理了最常见的用例，同时显著降低了成本。我还想提醒，可能在某些方面估算可能有误，我乐于根据反馈进行调整。

初始设置

4.png

初始数据加载x：我们将考虑在任务开始时表中已有3亿行数据，系统应处理所有行的初始加载。

插入、更新和删除（变更数据捕获）：剩余的7亿行将是插入、更新和删除的组合。包括对toast列的支持。

每秒变更1024行，持续约8天。

恢复能力：我们将每30分钟重启系统，以确保其健壮性，能够从灾难中恢复。

现在，让我们逐步介绍一个工程设计，以最小化成本和提高性能为目标，优化处理上述工作负载。

从Postgres到Snowflake的初始加载

首先，任何数据同步任务都要做的就是从源头加载初始数据集。这其中有一些挑战：

  • 如何高效从Postgres检索大量数据？
  • 如何以最小成本处理数据？
  • 如何高效将数据加载到Snowflake？

从Postgres高效检索数据

顺序读取Postgres中的表很慢。从Postgres读取3亿行数据需要很长时间。为了提高效率，我们需要并行化。我们发现了一个巧妙的方法，可以快速读取Postgres表中的数据片段，使用的是一个被称为TID Scan的隐藏宝石。基本上，它允许我们根据它们的元组ID（CTIDs）识别并选取存储在磁盘上的特定数据块，这些ID看起来像是（页面，元组）。这优化了IO利用率，对于高效读取大型表格非常有用。

以下是我们的做法：我们根据数据库页面将表分区，每个分区分配一个扫描任务。每个任务处理约50万行数据。因此，我们将表划分为CTID范围，每个分区大约有50万行，我们并行处理每个分区（一次16个分区）。

SELECT count(*) FROM public.challenge_1br; 

SELECT bucket, MIN(ctid) AS start, MAX(ctid) AS end 

FROM ( 

  SELECT NTILE(1000) OVER (ORDER BY ctid) AS bucket, ctid 

  FROM public.challenge_1br 

) subquery 

GROUP BY bucket ORDER BY start;

数据在传输中

在不超载系统的情况下处理数据非常重要。由于我们的预算有限，我们需要使用有效利用硬件的技术。我们将采用“数据集适合于RAM”这一系统设计范式。初始加载的3亿行听起来很多，但让我们看看如何让它适应我们的RAM。我们需要处理数据以确保数据类型正确映射到目的地。我们将把查询结果转换为Avro格式，以便更快地加载到仓库中，同时也支持其逻辑类型。

数据有多大？

让我们稍微绕道探索一下数据的大小。这是一个估算事物的好机会。基于与许多生产客户的互动，并与一些专家交谈，我们可以说，平均每个表约有15列。在我们的表中，假设每行大约为512字节。

num_rows = 300_000_000 

bytes_per_row = 512 

total_num_bytes = num_rows * bytes_per_row 

total_size_gb = total_num_bytes / 1_000_000_000 

num_rows_per_partition = 500_000 

mb_per_partition = num_rows_per_partition * bytes_per_row / 1_000_000 

num_partitions_in_parallel = 16 

required_memory = num_partitions_in_parallel * mb_per_partition

所需内存

根据以上粗略计算，我们可以看到，有4GB的RAM应该能够完成初始加载。我们将分配8GB的RAM以考虑其他组件。

高效地将数据加载到Snowflake

正如前面提到的，我们将把查询结果存储为磁盘上的Avro文件。我们进一步使用zstd压缩Avro文件，以进一步减少磁盘占用，并节省网络成本。我们将稍微偏离主题，讨论带宽成本。

带宽成本：它们可能会耗尽你的资金！

让我们看看网络成本，你会看到数字的变化。

成本的差异很有趣，因此最好将Postgres、我们的系统和Snowflake放在同一云提供商和同一地区。现在，让我们计算这个工作负载所需的网络成本。

计算网络成本

另一个需要注意的是仓库配置。

bytes_per_row = 512 

num_rows = 1_000_000_000 

total_data_size = 512GB 

compressed_data_size_GB = 256 

bandwidth_cost_per_10GB = $0.1 

network_costs_egress_from_postgres = $5 

network_costs_egress_from_system_to_snowflake = $2.56 

network_costs = $7.56