Database Bulk Operations

Also Known As

TL;DR

Explanation

Bulk operations process many rows with a single database statement or a small number of batched statements, rather than executing one INSERT/UPDATE/DELETE per row in a loop. Each statement carries fixed costs: network round-trip latency, query parsing and planning, and per-statement transaction overhead. Doing 10,000 individual inserts pays those costs 10,000 times; a multi-row INSERT or a batched executemany pays them once or a handful of times.

The core techniques are: multi-row INSERT (INSERT INTO t (a,b) VALUES (1,2),(3,4),(5,6)), bulk UPDATE via a single statement with CASE or a join against a VALUES list or temp table, bulk DELETE with a WHERE id IN (...) or range predicate, and bulk UPSERT (INSERT ... ON CONFLICT / ON DUPLICATE KEY UPDATE). For very large loads, dedicated paths like COPY (PostgreSQL) or LOAD DATA INFILE (MySQL) are dramatically faster than INSERT.

Batching is not free of trade-offs. A single huge statement holds locks longer, grows the transaction log, and can blow past parameter limits (PostgreSQL caps bind parameters at 65535, so a wide multi-row insert needs chunking). The practical pattern is to chunk into batches of a few hundred to a few thousand rows, wrap each batch in its own transaction, and tune the batch size against lock contention and memory. Bulk DELETEs on hot tables should be ranged and paced to avoid long lock holds and replication lag.

Done well, bulk operations turn a multi-minute import or migration into seconds. Done naively - one query per row inside a request - they are a classic source of slow endpoints, connection exhaustion, and timeouts.

Common Misconception

Why It Matters

Common Mistakes

• Running one INSERT/UPDATE per row inside a loop instead of batching into multi-row statements.
• Building a single statement so large it exceeds the driver's bind-parameter limit or available memory.
• Committing every row in its own transaction, paying commit overhead per row instead of per batch.
• Bulk-deleting millions of rows in one statement, holding locks long enough to stall other writers and lag replicas.
• Concatenating values into SQL strings instead of using parameterised batches, opening an injection hole.

Avoid When

• Avoid one giant statement for millions of rows - chunk it to bound lock duration, log growth, and parameter count.
• Do not bulk-delete large ranges on hot tables in a single statement; range and pace deletes to limit lock holds and replication lag.
• Skip batching when only a handful of rows are involved - the added complexity is not worth it.

When To Use

• Use multi-row INSERT/UPSERT when importing, seeding, or syncing hundreds or more rows.
• Use a single set-based UPDATE/DELETE with a WHERE predicate instead of per-row statements in a loop.
• Use COPY or LOAD DATA INFILE for very large bulk loads where raw throughput matters most.

Code Examples

// One round-trip per row - slow and connection-hogging
$stmt = $pdo->prepare('INSERT INTO events (user_id, action) VALUES (?, ?)');
foreach ($events as $e) {
    $stmt->execute([$e['user_id'], $e['action']]); // N round-trips
}

// Chunked multi-row INSERT - one round-trip per batch
foreach (array_chunk($events, 500) as $chunk) {
    $placeholders = implode(',', array_fill(0, count($chunk), '(?, ?)'));
    $sql = "INSERT INTO events (user_id, action) VALUES $placeholders";
    $params = [];
    foreach ($chunk as $e) {
        $params[] = $e['user_id'];
        $params[] = $e['action'];
    }
    $pdo->beginTransaction();
    $pdo->prepare($sql)->execute($params);
    $pdo->commit();
}

Tags

Related Terms

References

• https://www.postgresql.org/docs/current/sql-insert.html
• https://dev.mysql.com/doc/refman/8.0/en/insert.html
• https://www.postgresql.org/docs/current/sql-copy.html