超大型XML导入PostgreSQL时重复城市关联数据高效入库方案问询

现有方案的缺陷 #

• 哈希碰撞风险：即便使用高强度哈希算法，仍存在极小概率的不同城市名生成相同哈希值的情况，一旦发生会导致person和city的关联完全错误，且这类问题排查难度极高。
• 不必要的IO开销：向city表写入1亿条重复数据会产生数GB级的无效写入，后续全表扫描去重也会额外消耗大量磁盘IO和CPU，整体导入效率并没有做到最优。
• 脏数据风险：若XML中同一个城市的人口、面积等附属字段存在不一致，去重时直接取首行的逻辑会引入无法追溯的脏数据。

更优实现方案 #

由于唯一城市总量仅1万条，完全可以通过内存缓存的方式完全规避导入过程中的数据库查询操作，推荐方案如下：

步骤1：前置准备 #

• 使用流式XML解析器（如SAX、StAX），禁止加载整个XML文件到内存，避免OOM。
• 内存中创建哈希字典，键为城市名，值为分配的city_id，同时维护城市数据的批量插入队列、person数据的批量插入队列。
• 创建无约束、无索引的city和person空表。

步骤2：流式解析写入 #

每解析完成一条person记录执行以下逻辑：

1. 取出关联的城市名，查询内存字典：
   • 存在：直接获取对应的city_id
   • 不存在：手动分配自增的city_id，写入内存字典，同时将完整城市数据加入城市插入队列
2. 将person字段+对应city_id加入person插入队列
3. 当队列长度达到预设阈值（建议1000~10000条）时，批量执行INSERT语句写入数据库，单次批量写入的性能远高于单条插入。

步骤3：收尾与约束添加 #

• 全部XML解析完成后，将队列中剩余的未写入数据全部刷入数据库。
• 为city表添加city_id主键约束，为person表添加city_id外键约束，按需创建其他索引即可。

该方案全程无导入过程中的数据库查询操作，内存占用仅需几MB，导入性能相比原方案可提升数十倍。

替代工具化方案 #

如果不想自行实现缓存逻辑，也可以用以下流程：

1. 流式解析XML输出两个纯文本文件：person.csv（包含姓名、生日、城市名），city.csv（包含城市名、人口、面积）
2. 用shell命令对city.csv去重：sort -u city.csv > unique_city.csv
3. 给unique_city.csv新增自增city_id列，用PostgreSQL原生COPY命令批量导入city表
4. 导入person数据时关联city表补全city_id，再批量写入person表

导入性能优化建议 #

• 导入阶段关闭自动提交，使用大事务批量提交。
• 导入完成后再创建索引、约束，避免边写入边维护索引的开销。
• 临时调大PostgreSQL的maintenance_work_mem、work_mem参数，可大幅加快约束和索引的创建速度。

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