# Bulk COPY Insert large batches at high throughput with PostgreSQL binary COPY. ## 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`](/database/0.3.8/querying/writing/insert) 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](#trade-offs)). For small inserts, individual writes, or when you need generated keys propagated, use the regular insert path. ## Inserting a batch `BulkCopy.InsertMultipleCopyAsync` works with any generated `[Table]` entity. It returns the number of rows written. ```csharp 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: ```csharp public static Task InsertMultipleCopyAsync( IEnumerable rows, DbConnection connection, DbTransaction? transaction = null, InsertFields fields = InsertFields.Default, Expression>? 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](/database/0.3.8/querying/writing/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: ```plaintext 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](/database/0.3.8/dynamic-tables/declaring) (`[TableType]`) expose the same operation, bound to the runtime table name: ```csharp 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`](/database/0.3.8/querying/writing/insert) (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: ```csharp await using var tx = await conn.BeginTransactionAsync(); await BulkCopy.InsertMultipleCopyAsync(rows, conn, tx); await tx.CommitAsync(); // RollbackAsync() undoes the COPY ``` ## Performance See [Benchmarks](/database/0.3.8/performance/benchmarks) for COPY throughput against the parameterized multi-row insert, Dapper, and EF Core.