如何快速将S3存储桶中千万级数据导入Postgres数据库

当前流程核心瓶颈在于小分片串行读取、逐行/小批量插入导致的数据库写入开销过高，以及关联表主键回查、索引实时更新的冗余损耗，可按以下层级优化：

预处理阶段优化 #

• 取消100MB分片落地S3的流程，直接通过S3范围流式读取源文件，内存中完成结构化解析，省掉分片存储、反复拉取的IO开销
• 预解析阶段直接按24张表的一对多关联关系拆分数据集：如果父表用自增主键，提前给所有父表记录预分配连续的主键段，子表直接绑定预分配的父表主键，完全避免插入父表后再回查主键关联子表的额外查询开销
• 去重、校验等逻辑全部放在预处理阶段完成，不要放到数据库层通过触发器、约束实现，数据库仅做批量写入动作

数据库写入优化 #

• 导入前删除所有表的非主键索引、触发器、外键约束，全量导入完成后统一重建，单批次索引重建的开销比逐行更新索引低90%以上
• 用COPY FROM命令代替普通INSERT语句批量写入，该命令是Postgres原生最快的写入方式，跳过很多SQL解析、事务校验逻辑，性能是普通INSERT的10~100倍
• 调整事务粒度：按单表10~20万条为一个事务批次提交，避免过小的事务导致频繁提交刷盘，也避免过大的事务导致WAL日志暴涨
• 导入期间临时调整数据库参数：maintenance_work_mem调至1GB以上，wal_buffers调至64MB，checkpoint_timeout调至1小时，临时关闭fsync和synchronous_commit（全量导入完成后务必改回原配置，避免故障丢数据），最大化写入吞吐量

并行提速方案 #

• 预处理、入库流程全异步并行：父表全量导入完成后，无依赖关系的表可并行写入，存在一对多关联的子表可按父表主键范围拆分为多个分片并行写入
• 有多台空闲资源的情况下，可将源文件按行拆分为多个独立大分片，每个分片预分配独立的主键段后并行解析、并行入库，性能可接近线性提升

事务一致性保障 #

• 取消逐行/逐片的事务校验逻辑，改为批次级校验：每个批次写入前生成源数据的校验和，写入完成后对比库内数据的校验和，不一致直接回滚整个批次重试
• 全量导入完成后统一做跨表关联一致性校验，比边写入边校验的开销低一个数量级

按这套方案执行，单台服务器处理1000万条关联数据的耗时可以控制在几小时以内，远低于当前的一周耗时。

内容的提问来源于stack exchange，提问作者Bhumi Patel