多层嵌套对象递归插入PostgreSQL速度慢，请问有更高效的实现方案吗？

我有一个名为Rows的大型数据集，格式为包含多层深度嵌套对象的数组，每个row都可带有subrows数组，子行和父行的属性结构完全一致。如果将该数据集“扁平化”为单层对象，最终会得到5000万到1亿条左右的对象。

我的目标是将每个条目作为单独记录插入PostgreSQL数据库实体中，需要注意的是：插入subRow时必须将对应parentRow的自动生成整数主键作为parentId字段关联写入；parentId为可选字段，无父级的行不需要填充该字段，以此完整保留原始的层级关系。

以下是数据样例：

{
  "modelId": 1,
  "subRows": [
    {
      "visualId": "01.01.10",
      "elementForgeDbIds":[222904, 222905 ,222906 ,222912, 222913],
      "description": "parent 1",
      "subRows": [
        {
          "visualId": "01.01.10",
          "elementForgeDbIds": [222923, 222926,222927 ,222931],
          "description": "child 1 of parent 1",
          "subRows": [
            {
              "visualId": "01.01.10",
              "elementForgeDbIds": [1, 2 ,3 ,4, 5, 6],
              "description": "child 1 of child 1",
              "classificationFormula": [],
              "subRows": [
                {
                  "visualId": "01.01.10",
                  "elementForgeDbIds": [1, 2 ,3 ,4, 5, 6],
                  "description": "terminal 1",
                  "classificationFormula": [],
                  "subRows": []
                }
              ]
            },
            // 省略其余同结构样例数据
          ]
        }
      ]
    }
  ]
}

以下是目前实现的递归方案：

class RowService {

  private terminalRows: IRow[] = []
  private subRows: Partial<Row>[] = []
  private modelId: string

  constructor(private rowRepository: Repository<RowEntity>)
  
  
  async createRows(payload: CreateBoqRowDTO) {
      const { modelId, subRows: rows } = payload
      this.modelId = modelId
      /** 处理父行 */
      for (const row of rows) {
         const { subRows, parentId, classificationFormula, elementForgeDbIds } = row
         if (subRows && Array.isArray(subRows) && subRows.length) {
            /** 为当前父行设置model Id */
            row.modelId = modelId
            /** 保存当前父行获取其Id，后续将作为其子行的parentId */
            const { id: currentParentId } = await this.saveRow(row)
            this.insertedParentRow++

            /** 将当前行的子行推入类成员subRows中 */
            this.subRows.push(
               ...subRows.map((row) => {
                  row.parentId = currentParentId
                  return row
               }),
            )
         } else {
            /** 处理（保存）终端节点 */
            row.modelId = modelId
            row.parentId = parentId
            this.terminalRows.push(row)
            await this.saveRow(row)
            this.insertedTerminalRow++
         }
      }

      /** 处理子行 */
      if (this.subRows.length) {
         /** 取出subRows的第一条行，用于下一轮递归处理 */
         const currentRow = this.subRows.shift()
         /** 组装原方法入参，传入当前待处理行 */
         const payload = {
            modelId: this.modelId,
            subRows: [currentRow],
         }

         /** 此处执行递归调用 */
         return await this.createRows(payload)
      }

      await this.rowRepo.save(this.terminalRows)
   }

   async saveRow(row): Promise<Row> {
      const { parentId, visualId, description, grouping, classificationFormula, modelId } = row
      const newRow: any = this.rowRepo.create({
         modelId,
         parentId,
         visualId,
         description,
         classificationFormula: { ...classificationFormula },
      })
      return await this.rowRepo.save(newRow)
   }

}

这套实现可以正确保留数据的层级关系，但执行速度极慢，请问有没有更优的解决思路？

优化方案 #

原有实现的核心性能瓶颈 #

1. 逐行插入数据库，5000万~1亿次单条IO请求，网络交互和数据库事务提交开销占了绝大多数执行时间
2. 递归处理逻辑每次仅处理1条子行，完全没有利用批量处理的能力，且数组shift操作时间复杂度为O(n)，数据量越大数组操作开销越高
3. 没有显式使用事务，每次单条插入都会触发数据库自动提交，磁盘刷写开销极高

推荐优化方案 #

方案一：层级批量插入（无需修改表结构）

• 第一步：对原始嵌套数据做广度优先遍历，按层级分组所有行，遍历过程中给每个节点分配一个自增的临时ID，子节点同时记录对应父节点的临时ID
• 第二步：从顶层（无父节点的行）开始批量插入，使用PostgreSQL的RETURNING id, temp_id语句，一次性拿到当前批次插入后生成的真实主键和临时ID的映射关系
• 第三步：下一层的所有行将父临时ID替换为映射后的真实父ID，再批量插入，重复该过程直到所有层级插入完成
• 批大小建议设置为1000~10000，平衡内存占用和插入效率

方案二：预分配主键全量批量插入（性能最优）

• 第一步：遍历整个嵌套树，统计总记录数N，同时给每个节点分配临时ID、记录父临时ID，将所有数据扁平化为一维数组
• 第二步：直接从PostgreSQL的主键序列一次性获取N个连续的主键ID：SELECT nextval('your_table_id_seq') FROM generate_series(1, N)
• 第三步：给扁平化数组的每个节点分配真实ID，同时根据父临时ID映射替换为对应的真实父ID
• 第四步：分批次批量插入所有记录，单批次可设置为1万~10万条，插入效率相比原方案可提升数百到上千倍

额外优化建议 #

• 插入前临时关闭目标表的非必要索引、外键约束，插入完成后再重建，可大幅减少插入过程中索引维护的开销
• 整个导入过程使用显式事务，避免自动提交的磁盘刷写开销
• 避免使用递归遍历，改用迭代实现的广度/深度优先遍历，防止层级过深导致栈溢出，同时遍历性能更高

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