[500秒→7秒]PostgreSQLCopyHelperを使って大量のレコードを高速で一括挿入する

PostgreSQL（Npgsql）にて、大量のINSERTを効率的に行いたい。

環境

• PostgreSQL 12
• EF Core 3.1
• ASP.NET Core 3.1

障害内容

2000件以上の新規レコード生成処理が500秒近くかかってしまう。

調査結果

処理を見ると次のように、EF Coreを使ってINSERTしていた。

private void InsertList(List<DetailModel1> list)
{
  // モデルのリストからエンティティクラスを生成してDbContextにAddする
  foreach( var item in list )
  {
    _dbContext.Add( new table1 {
      id = item.id,
      name = item.name,
      price = item.price,
      desc = item.desc,
    });
    
  }
  
  // DBに反映
  _dbContext.SaveChanges();
}

2000件以上の新規レコードをDbContextに突っ込んでSaveChanges()している。つまり2000回のINSERT文がDBに向けて発行される。そりゃ遅いはずである。

外部の業者が書いたコードであるが、コードレビューで見逃してしまっていた。

対処

PostgreSQLにはcopy関数という、一括挿入命令がある。これを用いてみる。
その為の外部ライブラリとして、PostgreSQLCopyHelperというものがオープンソースで公開されている。

NuGetにも存在し、ダウンロード数も200万件を超えているようだ。

これを使って置き換えた例が次のものになる。

private void CopyList(List<DetailModel1> list)
{
  // テーブル列とモデルプロパティのマッピング定義
  var copyHelper = new PostgreSQLCopyHelper.PostgreSQLCopyHelper<table1>("public", "table1")
    .Map("id", x => x.id, NpgsqlTypes.NpgsqlDbType.Varchar)
    .Map("name", x => x.name, NpgsqlTypes.NpgsqlDbType.Varchar)
    .Map("price", x => x.price, NpgsqlTypes.NpgsqlDbType.Number)
    .Map("desc", x => x.desc, NpgsqlTypes.NpgsqlDbType.Varchar)
  ;
  
  try
  {
    // 接続の生成
    using var conn = new NpgsqlConnection(connectionString); // _dbContextのDB接続文字列と同一のもの
    // 接続をOpen
    conn.Open();

    // モデルのリストからエンティティのリストに変換    
    var records = list.Select( item => new table1 {
      id = item.id,
      name = item.name,
      price = item.price,
      desc = item.desc,
    });
    
    // copy
    copyHelper.SaveAll(conn, records);
    
  }
  catch (Exception ex)
  {
    // ログ出力などを行う
    throw;
  }
}

copyHelperは、テーブルの列名と、モデルクラスのプロパティ名を対応付けるマッパー設定が必要である。
この辺は工夫すればリフレクションで自動化できそうだ。

結果

7秒で処理が完了した。びっくり。

注意事項

とりあえずのメモであり、この結果は未検証な為、注意されたい。

尚、copyはトランザクションに含むことができるが、失敗時には当然ながら全てロールバックされる為、DBへの負担は高い。