CockroachDB

For CockroachDB, the underlying implementation is leveraging CockroachDbBulkCopy, a native binary COPY-based bulk writer from RepoDb.Connector.CockroachDb.

For CockroachDB, the underlying implementation is leveraging CockroachDbBulkCopy, a native binary COPY-based bulk writer from RepoDb.Connector.CockroachDb.

For BulkInsert, the entities/rows are written straight to the target table — unless CockroachDbBulkImportIdentityBehavior.ReturnIdentity is requested, in which case a staging table is used instead so the generated identity values can be read back via a RETURNING clause on the same statement that loads the real table.

For BulkDelete, BulkDeleteByKey, BulkMerge and BulkUpdate, a pseudo (staging) table is always created first. The library writes to it via CockroachDbBulkCopy internally, then cascades the changes to the original table using the correct SQL statement.

The data is brought together from the client application into the database server (at one-go). It then gets processed together at the same time.

The other bulk operations can be optimized further by targeting the underlying table indexes (via qualifiers). Pass a list of Field objects when calling the operations — an index on the pseudo table’s qualifier columns is also created automatically to speed up the matching statements.

Pseudo Table Type

The CockroachDbBulkImportPseudoTableType enum lets you choose between a session-private temporary table (Memory), an ordinary heap table (Physical), or let the library decide based on row count (Auto, the default — Physical at 5,000 rows or more, otherwise Memory).

Backing a call with Memory first runs SET experimental_enable_temp_tables = 'on'; — CockroachDB only supports temporary tables experimentally and otherwise rejects CREATE TEMP TABLE with a XCEXF error. Every bulk call also creates its own pseudo table and drops it once the call completes, rather than reusing one across calls.

Supported Objects

Below are the following objects supported by the bulk operations.

• System.DataTable
• System.Data.Common.DbDataReader
• IEnumerable<T>
• ExpandoObject
• IDictionary<string, object>

Operation SQL Statements

Once all the data is in the staging (pseudo) table, the correct SQL statement is used to cascade the changes towards the original table.

BulkInsert writes directly into the target table and skips the staging table entirely — unless identityBehavior is set to ReturnIdentity, in which case a staging table is used first (see above).

For BulkInsert

> INSERT INTO "OriginalTable" (Field1, Field2, ...)
> SELECT Field1, Field2, ... FROM "PseudoTempTable" ORDER BY "__RepoDbBulkRowOrder__"
> RETURNING "Identity" AS "Result";

For BulkDelete / BulkDeleteByKey

> DELETE FROM "OriginalTable"
> USING "PseudoTempTable" AS S
> WHERE ("OriginalTable".QualifierField1 = S.QualifierField1 AND "OriginalTable".QualifierField2 = S.QualifierField2);

For BulkMerge

Without identityBehavior: ReturnIdentity, matched rows are updated and unmatched rows are inserted via two statements:

> UPDATE "OriginalTable" SET Field3 = S.Field3, Field4 = S.Field4
> FROM "PseudoTempTable" S WHERE (QualifierField1 = S.QualifierField1);
>
> INSERT INTO "OriginalTable" (Field1, Field2, ...)
> SELECT Field1, Field2, ... FROM "PseudoTempTable" S
> WHERE NOT EXISTS (SELECT 1 FROM "OriginalTable" WHERE QualifierField1 = S.QualifierField1)
> ORDER BY S."__RepoDbBulkRowOrder__";

With identityBehavior: ReturnIdentity, a single INSERT ... ON CONFLICT DO UPDATE ... RETURNING performs the whole upsert and reads back every identity value in one round trip — see Identity Setting Alignment below.

The identity column, if any, is always left out of the INSERT column list — a brand-new row’s identity property is typically an unset default (e.g. 0), not a real value meant to be inserted as-is.

For BulkUpdate

> UPDATE "OriginalTable" SET Field3 = S.Field3, Field4 = S.Field4
> FROM "PseudoTempTable" S WHERE (QualifierField1 = S.QualifierField1);

Unlike BulkMerge, staged rows with no matching target row are left as-is, not inserted.

Special Arguments

The arguments below are available on most operations.

Identity Setting Alignment

When identityBehavior is set to ReturnIdentity, an __RepoDbBulkRowOrder__ identity column is added to the pseudo table (via ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS IDENTITY) to track each entity’s position in the source IEnumerable.

For BulkInsert, the library runs a single INSERT INTO ... SELECT ... FROM "PseudoTempTable" ORDER BY "__RepoDbBulkRowOrder__" RETURNING identity statement, then assigns the returned identities back to the entities in that order.

For BulkMerge, the identity for rows that will be inserted is pre-assigned before the upsert runs: UPDATE "PseudoTempTable" SET identity = nextval(pg_get_serial_sequence(target, identity_column)) WHERE NOT EXISTS (SELECT 1 FROM target WHERE <qualifiers>). The pseudo table’s rows now carry a real identity value for every row that doesn’t already exist in the target. A single INSERT ... OVERRIDING SYSTEM VALUE ... ON CONFLICT (qualifiers) DO UPDATE ... RETURNING identity then performs the upsert and returns every row’s identity — the pre-assigned one for new rows, the existing one for updated rows — in one round trip. This is more efficient than the multi-statement (snapshot, then update, then insert) approach some other providers require for the same feature.

Type Coercion

CockroachDbBulkCopy writes rows through Npgsql’s binary COPY without explicit types, so the wire type is inferred from each CLR value. CockroachDB’s INT/INTEGER is a 64-bit INT8, so a plain .NET int property sent as a 4-byte INT4 datum is rejected with 42601: read binary tuple: decode datum as INT8. To avoid this, every mapped value is coerced to the destination column’s actual CLR type before it reaches CockroachDbBulkCopy, so Npgsql emits the width the server expects.

BatchSize

All the provided operations have a batchSize argument that lets you override the number of rows wired-up to the server per batch. By default it is null, meaning all items are sent together in one-go.

Use this argument if you wish to optimize the operation based on certain situations.

• Network Latency
• Infrastructure
• No. of Columns
• Type of Data

Async Methods

All the provided synchronous operations have an equivalent asynchronous (Async) counterpart.

BulkDelete

using (var connection = new CockroachDbConnection(connectionString))
{
    var people = connection.Query<Person>(e => e.IsActive == false);
    var deletedRows = connection.BulkDelete<Person>(people);
}

Or with qualifiers.

using (var connection = new CockroachDbConnection(connectionString))
{
    var deletedRows = connection.BulkDelete<Person>(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}

BulkDeleteByKey

using (var connection = new CockroachDbConnection(connectionString))
{
    var primaryKeys = new object[] { 10045, 10046, 10047 };
    var deletedRows = connection.BulkDeleteByKey("Person", primaryKeys);
}

BulkInsert

using (var connection = new CockroachDbConnection(connectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people);
}

To return the newly generated identity values:

using (var connection = new CockroachDbConnection(connectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people,
        identityBehavior: CockroachDbBulkImportIdentityBehavior.ReturnIdentity);
}

BulkMerge

using (var connection = new CockroachDbConnection(connectionString))
{
    var people = GetPeople(10000);
    var mergedRows = connection.BulkMerge(people);
}

Or with qualifiers.

using (var connection = new CockroachDbConnection(connectionString))
{
    var mergedRows = connection.BulkMerge(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}

BulkUpdate

using (var connection = new CockroachDbConnection(connectionString))
{
    var people = GetPeople(10000);
    var updatedRows = connection.BulkUpdate(people);
}

Or with qualifiers.

using (var connection = new CockroachDbConnection(connectionString))
{
    var updatedRows = connection.BulkUpdate(people,
        qualifiers: e => new { e.LastName, e.DateOfBirth });
}