Copy page

DAL Library

Bulk Operations

Bulk Operations

Relevant source files

The following files were used as context for generating this wiki page:

• src/repository/repository.ts
• test/benchmark/bulk-benchmark.test.ts
• test/repository-bulk.test.ts

The Primebrick DAL provides specialized methods for high-volume data modifications. These methods are designed to bypass the overhead of single-row processing while maintaining data integrity, audit compliance, and PostgreSQL parameter limits.

Overview of Bulk Methods

Bulk operations in the Repository class are optimized for different use cases, ranging from simple batched inserts to complex temporary table-based updates. All bulk methods accept BulkOptions to control execution behavior.

BulkOptions

Option	Description
batchSize	Manual override for the number of rows per batch.
timeoutMs	Statement timeout for the operation.
actor	(Required for auditable entities) The user/system performing the operation.

Sources: src/types/types.ts:34-37, src/repository/repository.ts:148-150

---

1. addMany (Batched INSERT)

The addMany method performs high-speed inserts by grouping records into multi-row INSERT statements.

Implementation Details

• Auto-Batching: To avoid the PostgreSQL limit of 65,535 parameters per query, addMany calculates an optimal batch size using autoBatchSize(columnCount). This ensures that rowCount * columnCount never exceeds the limit src/repository/repository.ts:142-146, src/repository/repository.ts:476-485.
• Audit Stamping: Every row is automatically stamped with created_by, updated_by, and version = 1 if the entity is auditable src/repository/repository.ts:494-510.
• Result Hydration: It uses RETURNING * to return the fully hydrated entities, including database-generated IDs and defaults src/repository/repository.ts:517-522.

Sources: src/repository/repository.ts:466-531

---

2. upsertMany (Atomic Merge)

upsertMany handles "insert or update" logic at scale using the PostgreSQL INSERT ... ON CONFLICT syntax.

Key Features

• Conflict Handling: Requires a conflictTarget (usually the primary key or a unique column) to determine when to update instead of insert src/repository/repository.ts:544-550.
• Audit Awareness: If a conflict occurs, the system increments the version and updates the updated_by field, while preserving the original created_by value src/repository/repository.ts:577-585.
• Data Flow: Like addMany, it uses the autoBatchSize logic to prevent parameter overflow src/repository/repository.ts:557-560.

Sources: src/repository/repository.ts:533-617

---

3. updateMany (Temp Table Strategy)

Updating thousands of rows with different values for each row is inefficient using standard UPDATE statements. The DAL employs a Temporary Table Strategy to maximize performance.

The Update Workflow

1. Create Temp Table: A temporary table is created with the same structure as the target table but with ON COMMIT DROP to ensure cleanup src/repository/repository.ts:684-692.
2. Bulk Insert: All update data is inserted into this temporary table using the addMany logic src/repository/repository.ts:696-699.
3. Single Join Update: A single UPDATE ... FROM statement is executed, joining the target table with the temporary table on the matchBy column src/repository/repository.ts:701-724.

Diagram: UpdateMany Strategy

Code
sequenceDiagram
    participant R as Repository
    participant DB as PostgreSQL
    R->>DB: CREATE TEMP TABLE "tmp_..." ON COMMIT DROP
    Note over R,DB: Batched Insert into Temp Table
    loop for each batch
        R->>DB: INSERT INTO "tmp_..." VALUES (...)
    end
    Note over R,DB: Single Join Update
    R->>DB: UPDATE target_table SET ... FROM "tmp_..." WHERE target.id = "tmp_...".id
    DB-->>R: RETURNING *

Sources: src/repository/repository.ts:654-740

---

4. deleteMany (Soft-Delete)

deleteMany performs a bulk soft-delete by updating the deleted_at and deleted_by columns for a set of records.

Implementation

• Array-based WHERE: Instead of multiple queries, it uses the ANY($1::uuid[]) operator to update all matching rows in a single statement src/repository/repository.ts:641-645.
• Audit Stamping: It records the actor in the deleted_by column and sets the current timestamp in deleted_at src/repository/repository.ts:634-639.

Sources: src/repository/repository.ts:619-652

---

Technical Architecture

Data Flow: Natural Language to Code Entities

The following diagram maps the high-level bulk operations to the internal functions and PostgreSQL mechanics used in the Repository class.

Code
graph TD
    subgraph "Natural Language Space"
        B1["'Insert 10,000 rows'"]
        B2["'Update multiple rows with different data'"]
        B3["'Delete a list of IDs'"]
    end

    subgraph "Code Entity Space (Repository)"
        R_AM["Repository.addMany()"]
        R_UM["Repository.updateMany()"]
        R_DM["Repository.deleteMany()"]
        ABS["autoBatchSize()"]
        TT["TEMP TABLE Strategy"]
        ANY["ANY() Array Operator"]
    end

    subgraph "Database Space (PostgreSQL)"
        PG_INS["INSERT INTO ... VALUES (...), (...)"]
        PG_UP_FROM["UPDATE ... FROM temp_table"]
        PG_UP_ANY["UPDATE ... WHERE id = ANY(...)"]
    end

    B1 --> R_AM
    B2 --> R_UM
    B3 --> R_DM

    R_AM --> ABS
    ABS --> PG_INS

    R_UM --> TT
    TT --> PG_UP_FROM

    R_DM --> ANY
    ANY --> PG_UP_ANY

Sources: src/repository/repository.ts:142-146, src/repository/repository.ts:466-470, src/repository/repository.ts:619-623, src/repository/repository.ts:654-658

Performance Benchmarks

The bulk system is tested against various scales (100 to 1,000,000 rows). The updateMany strategy is specifically optimized to maintain high throughput even as row counts increase.

Operation	1,000 Rows	10,000 Rows	1,000,000 Rows (Opt-in)
addMany	~50ms	~300ms	~25s
updateMany	~80ms	~500ms	~45s

Sources: test/benchmark/bulk-benchmark.test.ts:97-101, test/benchmark/bulk-benchmark.test.ts:119-121

---