PostgreSQL に大きめのデータを高速にインポートしたい

C# .NET Core PostgreSQL

.NET Core のアプリケーションで、ある程度まとまった量の外部データを PostgreSQL へ取り込むのに EntityFramework でいけるかなと考えていたのですが想定よりデータ量が多く、運用を考えるとあまり実用的な速度がでなかったので Npgsql の Binary COPY を使ってみました(まあこの用途で ORM 使うなよという話しではありますが…)。

COPY | Npgsql Documentation

100万件のデータを愚直に Add メソッドで追加するパターンと、Binary COPY で追加するパターンの処理時間を各3回ずつ計測しました。

※ 今回は下記のライブラリの実装を参考にさせていただきました。

Model

データアクセスに使う Entity と Context です。Bar テーブルに Id、 Title 、 Body 列があります。これは両パターンで共通です。

public class FooDbContext : DbContext
{
    public DbSet<Bar> Bars { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql("/* connection string */");
    }
}

public class Bar
{
    [Key] public int Id { get; set; }
    public string Title { get; set; }
    public string Body { get; set; }
}

Add メソッドのパターン

internal class Program
{
    private static async Task Main(string[] args)
    {
        var bars = new List<Bar>();
        for (var i = 0; i < 1000000; i++)       // ダミーデータを生成
        {
            bars.Add(new Bar
            {
                Title = Guid.NewGuid().ToString(),
                Body = Guid.NewGuid().ToString()
            });
        }

        var context = new FooDbContext();

        var sw = new System.Diagnostics.Stopwatch();
        sw.Start();

        using (var transaction = context.Database.BeginTransaction())
        {
            foreach (var bar in bars)
            {
                await context.Bars.AddAsync(bar);
            }

            await context.SaveChangesAsync();
            transaction.Commit();
        }

        sw.Stop();

        var ts = sw.Elapsed;
        Console.WriteLine($"{ts.Hours}:{ts.Minutes}:{ts.Seconds}.{ts.Milliseconds}");
    }
}

実行結果

  • 1回目
    • 0:4:12.771
  • 2回目
    • 0:4:10.858
  • 3回目
    • 0:4:11.68

4分以上かかりました。流石に遅いですね(通常このデータ量でこの方法はとらないかと思いますが)。

Binary COPY のパターン

internal class Program
{
    private static async Task Main(string[] args)
    {
        var bars = new List<Bar>();
        for (var i = 0; i < 1000000; i++)       // ダミーデータを生成
        {
            bars.Add(new Bar
            {
                Title = Guid.NewGuid().ToString(),
                Body = Guid.NewGuid().ToString()
            });
        }

        var context = new FooDbContext();

        var sw = new System.Diagnostics.Stopwatch();
        sw.Start();

        using (var transaction = context.Database.BeginTransaction())
        {
            context.Database.ExecuteSqlCommand(
                $@"CREATE TEMP TABLE foo_temp_input(title text, body text) ON COMMIT DROP");

            var connection = (NpgsqlConnection) context.Database.GetDbConnection();

            using (var importer = connection
                .BeginBinaryImport($@"COPY foo_temp_input (title, body) FROM STDIN (FORMAT BINARY)")
            )
            {
                foreach (var bar in bars)
                {
                    importer.StartRow();
                    importer.Write(bar.Title);
                    importer.Write(bar.Body);
                }

                importer.Complete();
            }

            context.Database.ExecuteSqlCommand(
                $@"INSERT INTO ""Bars"" (""Title"", ""Body"") SELECT title, body FROM foo_temp_input");

            transaction.Commit();
        }

        sw.Stop();

        var ts = sw.Elapsed;
        Console.WriteLine($"{ts.Hours}:{ts.Minutes}:{ts.Seconds}.{ts.Milliseconds}");
    }
}

実行結果

  • 1回目
    • 0:0:13.993
  • 2回目
    • 0:0:14.760
  • 3回目
    • 0:0:14.705

14秒前後になりました。Add メソッドの場合と比べるととてもはやくなりました。

少し補足

ドキュメントを読むと Binary COPY の場合、最後に Complete() メソッドを呼ばないと writer が破棄されたタイミングでロールバックされるようです。また Cancel() メソッドで処理の取り消しが可能なようです。なので try/catch でエラー時に Cancel() メソッドを呼び出すと良さそうです(すいません未検証なのでこのような言い回しをしています……)。

……という仕様のようなので今回の Binary COPY のパターンだとトランザクションは不要です。サンプルのコードで敢えてトランザクションを使っているのはデータ更新対象のテーブルが複数あった場合の対応です。Binary COPY で一旦一時テーブルにインポートし、その後目的のテーブルに insert しています。複数テーブルに対して更新する場合この方法でトランザクションが有効になります。