AuroraDB PostgreSQL

For Aurora PostgreSQL, the underlying implementation is leveraging AuroraDbBulkCopy, a native binary COPY-based bulk writer from RepoDb.Connector.AuroraDb.Npgsql.

For Aurora PostgreSQL, the underlying implementation is leveraging AuroraDbBulkCopy, a native binary COPY-based bulk writer from RepoDb.Connector.AuroraDb.Npgsql.

For BulkInsert, the entities/rows are written straight to the target table — unless AuroraDbBulkImportIdentityBehavior.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 AuroraDbBulkCopy 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 AuroraDbBulkImportPseudoTableType 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).

The pseudo table is named deterministically from {pseudoTableType}{tableName}{Operation} (for example PhysicalPersonMerge), created via CREATE [TEMP] TABLE ... AS SELECT ... WHERE (1 = 0) (copying the target table’s column shape without its rows), then given its own __RepoDbBulkRowOrder__ identity column via ALTER TABLE. It is dropped once the call completes — every bulk call creates and drops its own pseudo table rather than reusing one across calls.

Aurora PostgreSQL supports temporary tables natively, so requesting Memory needs no special session flag (unlike CockroachDB, which requires SET experimental_enable_temp_tables = 'on'; first).

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 ... OVERRIDING SYSTEM VALUE ... 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

AuroraDbBulkCopy writes rows through Npgsql’s binary COPY without explicit types, so the wire type is inferred from each CLR value. The binary format requires every datum to have the exact width of its destination column — for example, a plain .NET int property sent as a 4-byte INT4 datum into a BIGINT column is rejected. To avoid this, every mapped value is coerced to the destination column’s actual CLR type before it reaches AuroraDbBulkCopy, 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 AuroraDbConnection(connectionString))
{
    var people = connection.Query<Person>(e => e.IsActive == false);
    var deletedRows = connection.BulkDelete<Person>(people);
}

Or with qualifiers.

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

BulkDeleteByKey

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

BulkInsert

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

To return the newly generated identity values:

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

BulkMerge

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

Or with qualifiers.

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

BulkUpdate

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

Or with qualifiers.

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