受到1BR挑战的启发,我想探究将10亿行数据从Postgres迁移到Snowflake的成本。转移10亿行数据绝非易事。这一过程不仅涉及数据的传输,还需确保数据的完整性、错误恢复以及迁移后的一致性。
此任务的核心在于工具和技术的选择。我们将讨论开源工具的使用、自定义脚本、从Postgres读取数据的方法,以及Snowflake数据加载能力。诸如并行处理、高效读取Postgres的WAL日志、数据压缩以及在Snowflake上增量批量加载等关键方面将被着重强调。
我将列举并讨论一些为最小化计算、网络和仓库成本而实施的优化措施。此外,我还将突出显示作为此过程一部分所做的一些权衡。鉴于博客中涉及的大多数方法源自我在PeerDB的探索,旨在增强我们的产品——任务主要通过PeerDB完成。
我想明确指出,与成熟系统相比,这里存在一些功能差距,可能并不适用于所有用例。然而,它确实有效处理了最常见的用例,同时显著降低了成本。我还想提醒,可能在某些方面估算可能有误,我乐于根据反馈进行调整。
初始设置
初始数据加载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