/DB

Bulk COPY

Insert large batches at high throughput with PostgreSQL binary COPY.

updated 2 Sept 20264 min readv0.3.8View as Markdown

Overview

For large inserts, BulkCopy.InsertMultipleCopyAsync streams rows to PostgreSQL using the binary COPY ... FROM STDIN (FORMAT BINARY) protocol. It is substantially faster than the parameterized InsertMultipleAsync path for big batches and is not bound by the 65,535-parameter limit, so a batch of any size is a single COPY operation rather than several chunked INSERT commands.

NEW in 0.3.3

Use it when you are loading many rows and don't need database-generated values back (see Trade-offs). For small inserts, individual writes, or when you need generated keys propagated, use the regular insert path.

Inserting a batch

BulkCopy.InsertMultipleCopyAsync<T> works with any generated [Table] entity. It returns the number of rows written.

using Socigy.OpenSource.DB.Core.Bulk;

var rows = Enumerable.Range(0, 100_000)
    .Select(i => new LogEntry { Id = Guid.NewGuid(), Message = $"event {i}", Level = "info" })
    .ToList();

await using var conn = connectionFactory.Create();
await conn.OpenAsync();

ulong written = await BulkCopy.InsertMultipleCopyAsync(rows, conn);
// written == 100000

The signature:

public static Task<ulong> InsertMultipleCopyAsync<T>(
    IEnumerable<T> rows,
    DbConnection connection,
    DbTransaction? transaction = null,
    InsertFields fields = InsertFields.Default,
    Expression<Func<T, object?[]>>? keep = null,
    CancellationToken cancellationToken = default)
    where T : class, IDbTable, IInsertPlanProvider;

The fields parameter (an InsertFields enum from Socigy.OpenSource.DB.Core.CommandBuilders, new in 0.3.5) selects which columns you write: InsertFields.IncludeAutoIncrement also writes auto-increment columns yourself (for example, when you are supplying your own identity values), and InsertFields.ServerDefaults omits both auto-increment and [Default] columns so the server fills them. Because transaction comes before fields, call with the fields: named argument: await BulkCopy.InsertMultipleCopyAsync(rows, conn, fields: InsertFields.ServerDefaults). The keep selector writes specific [Default] columns from your own values while the server fills the rest: keep: r => new object?[] { r.Id }.

InsertFields.ServerDefaultsWhenUnset (new in 0.3.8) omits a [Default] column only where the row still holds its CLR type default, so a value you assigned is written — see INSERT → Letting the server fill columns. It costs something here that it does not cost on a single-row insert:

  • ServerDefaults resolves the written columns once and runs the whole batch as a single COPY.
  • ServerDefaultsWhenUnset depends on each row's values, so rows are grouped by the set of columns they omit and one COPY runs per distinct group.

A batch whose rows agree — the usual case, and the only one for a batch of freshly built rows — produces a single group and is exactly as fast. A batch that disagrees costs one COPY per shape, and that split is logged at Information naming the columns responsible, so a slower batch is never a mystery:

info: Bulk insert into 'outbox' split 5000 rows into 3 batches: with ServerDefaultsWhenUnset the written
      columns depend on each row's values, and these rows disagree about [occurred_at, seq]. Name those
      columns in keep, or set them on every row, to get back to a single batch.
TIP
If you see that log line on a hot path, either name the varying columns in keep (so they are always written) or set them on every row before the call. Both restore the single-plan behaviour.

Dynamic tables

Dynamic tables ([TableType]) expose the same operation, bound to the runtime table name:

ulong written = await AuditEntry
    .WithTableName("audit_2026_06")
    .WithConnection(conn)
    .InsertMultipleCopyAsync(rows);

How it works

The values written by COPY come from the same per-column pipeline as a normal insert. Each column's value is produced exactly as it would be for InsertMultipleAsync, so special columns are handled identically and there is no risk of the two paths diverging:

  • [Encrypted] columns are written as their bytea ciphertext.
  • [JsonColumn] / [RawJsonColumn] columns are written as jsonb.
  • Value-convertor columns have their convertor applied before writing.
  • NULL values are written as SQL NULL.

Trade-offs

Binary COPY trades a few conveniences for throughput. Keep these in mind:

  • No RETURNING. COPY cannot return database-generated values. Auto-increment and DEFAULT columns are still filled by the database, but those values are not written back to your in-memory instances. When you need generated keys propagated, use InsertMultipleAsync (which supports value propagation) instead.
  • Strict types, DateTime kind matters. COPY does not perform the implicit casts that a parameterized INSERT does. A DateTime written to a timestamp without time zone column must have Kind = Unspecified (or Local); a Kind = Utc value belongs in a timestamp with time zone column. Mismatches throw at write time. The parameterized path masks this by letting the driver infer and the database implicitly cast.
  • All or nothing per call. A COPY is a single streamed operation; a failure aborts the whole batch.

Transactions

COPY participates in the connection's current transaction. Begin a transaction on the same connection (or pass it via transaction) and the rows are committed or rolled back with it:

await using var tx = await conn.BeginTransactionAsync();
await BulkCopy.InsertMultipleCopyAsync(rows, conn, tx);
await tx.CommitAsync();   // RollbackAsync() undoes the COPY

Performance

See Benchmarks for COPY throughput against the parameterized multi-row insert, Dapper, and EF Core.