Bulk CUD Operation #
Almost every application has large-scale data operation needs: importing thousands of rows from an Excel file, syncing data from external APIs, updating the status of hundreds of thousands of orders at once, or periodically cleaning up old data. The most naive way to handle this is a loop in the application layer — one INSERT or UPDATE per row, repeated thousands of times. On small datasets this doesn’t feel problematic. In production with hundreds of thousands of rows, this can take a full minute, hold locks on the modified rows, cause replication lag affecting read replicas, and disturb the OLTP queries running concurrently. Bulk CUD is about reducing per-operation overhead by sending more work in one statement or one transaction — but in a way that doesn’t break system availability. This article covers separate strategies for bulk INSERT, UPDATE, and DELETE, complete with Go implementations and partial failure handling.
Why Per-Row Operations Are Very Expensive #
Every standalone CUD operation bears the same fixed cost, regardless of how little data is modified:
The per-operation cost of a single INSERT:
──────────────────────────────────────────────────────────────
1. Parse and validate the SQL
2. Acquire row locks
3. Write to the data page (may need to allocate a new page)
4. Update ALL relevant indexes (B-Tree rebalancing)
5. Write to the WAL/redo log (with fsync if durability = full)
6. Update MVCC metadata (PostgreSQL)
7. Commit → fsync the WAL to disk
8. Release locks
9. Return an acknowledgement to the client (network roundtrip)
──────────────────────────────────────────────────────────────
For 10,000 one-by-one INSERTs:
Steps 1-9 repeated 10,000 times
WAL fsync: 10,000 times → can be 10-30 seconds of pure I/O
For 10,000 INSERTs in one multi-row statement:
Steps 1-9 happen ONCE
WAL fsync: 1 time
Total: hundreds of milliseconds
──────────────────────────────────────────────────────────────
This difference can reach 100× or more depending on the innodb_flush_log_at_trx_commit configuration and data size.
Bulk CREATE: Multi-Row INSERTs #
Multi-Row Syntax and the Difference #
-- ANTI-PATTERN: one INSERT per row — N roundtrips, N commits, N WAL fsyncs
INSERT INTO products (name, price, stock) VALUES ('Laptop', 9500000, 10);
INSERT INTO products (name, price, stock) VALUES ('Mouse', 150000, 50);
INSERT INTO products (name, price, stock) VALUES ('Keyboard', 350000, 30);
-- ... repeated 10,000 times
-- CORRECT: multi-row INSERT — 1 roundtrip, 1 commit, 1 WAL fsync
INSERT INTO products (name, price, stock) VALUES
('Laptop', 9500000, 10),
('Mouse', 150000, 50),
('Keyboard', 350000, 30),
-- ... up to hundreds or thousands of rows per statement
('Monitor', 4500000, 15);
INSERT with ON DUPLICATE KEY for Upserts #
When importing data that may already exist (for example, syncing from another system), use ON DUPLICATE KEY UPDATE to combine insert and update in one operation:
-- Upsert: insert if not present, update if present
INSERT INTO products (sku, name, price, stock, updated_at)
VALUES
('SKU-001', 'Laptop Pro', 9500000, 10, NOW()),
('SKU-002', 'Wireless Mouse', 150000, 50, NOW()),
('SKU-003', 'Mechanical Keyboard', 350000, 30, NOW())
ON DUPLICATE KEY UPDATE
name = VALUES(name),
price = VALUES(price),
stock = VALUES(stock),
updated_at = VALUES(updated_at);
-- If the sku already exists: update those columns
-- If the sku doesn't exist: insert a new row
-- All in one roundtrip
Bulk INSERT Implementation in Go with Chunking #
For large datasets (> 10,000 rows), don’t send everything in one statement — this can make the query too long and hold locks too long. Chunk the data into smaller batches:
type Product struct {
SKU string
Name string
Price int64
Stock int
}
// BulkUpsertProducts upserts in batches to avoid
// prolonged locks and oversized queries
func BulkUpsertProducts(ctx context.Context, db *sql.DB, products []Product) error {
const batchSize = 500 // tune based on row size and memory
for i := 0; i < len(products); i += batchSize {
end := i + batchSize
if end > len(products) {
end = len(products)
}
batch := products[i:end]
if err := upsertBatch(ctx, db, batch); err != nil {
return fmt.Errorf("batch %d-%d failed: %w", i, end, err)
}
// Throttle between batches: let the database and replicas breathe
// Adjust based on system load
if end < len(products) {
time.Sleep(10 * time.Millisecond)
}
}
return nil
}
func upsertBatch(ctx context.Context, db *sql.DB, products []Product) error {
// Build the multi-row query dynamically
valueStrings := make([]string, len(products))
valueArgs := make([]interface{}, 0, len(products)*4)
for i, p := range products {
valueStrings[i] = "(?, ?, ?, ?)"
valueArgs = append(valueArgs, p.SKU, p.Name, p.Price, p.Stock)
}
query := fmt.Sprintf(`
INSERT INTO products (sku, name, price, stock)
VALUES %s
ON DUPLICATE KEY UPDATE
name = VALUES(name),
price = VALUES(price),
stock = VALUES(stock)
`, strings.Join(valueStrings, ","))
_, err := db.ExecContext(ctx, query, valueArgs...)
return err
}
LOAD DATA INFILE for Very Large Volumes #
For importing CSV files with millions of rows, LOAD DATA INFILE (MySQL) or COPY FROM (PostgreSQL) is the fastest way because it bypasses much of the SQL parsing overhead:
-- MySQL: import from a CSV file directly into the table
LOAD DATA LOCAL INFILE '/tmp/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS -- skip the header
(sku, name, price, stock);
-- For PostgreSQL:
COPY products (sku, name, price, stock)
FROM '/tmp/products.csv'
WITH (FORMAT CSV, HEADER true, DELIMITER ',');
Import speed comparison for 1 million rows:
──────────────────────────────────────────────────────────────
One-by-one INSERTs: ~30 minutes
Multi-row INSERT (500/batch): ~2 minutes
LOAD DATA INFILE / COPY: ~15 seconds
──────────────────────────────────────────────────────────────
LOAD DATA/COPY is far faster because:
- Minimal parsing (binary/CSV format, not SQL)
- Index updates can be deferred and done once at the end
- No per-row roundtrips
Bulk UPDATE: Strategy by Scenario #
Bulk UPDATE has the most diverse cases. The strategy differs depending on whether all rows are updated with the same value, different values per row, or only rows matching a certain condition.
Updating the Same Value for Many Rows #
-- ANTI-PATTERN: one-by-one updates
UPDATE orders SET status = 'expired' WHERE id = 1001;
UPDATE orders SET status = 'expired' WHERE id = 1002;
UPDATE orders SET status = 'expired' WHERE id = 1003;
-- ... N queries for N orders
-- CORRECT: WHERE IN — one query, one lock acquisition
UPDATE orders
SET status = 'expired', updated_at = NOW()
WHERE id IN (1001, 1002, 1003, ...)
AND status = 'pending'; -- a safety guard condition
Updating Different Values per Row with CASE #
-- ANTI-PATTERN: per-row updates with different values
UPDATE products SET price = 9500000 WHERE id = 1;
UPDATE products SET price = 150000 WHERE id = 2;
UPDATE products SET price = 350000 WHERE id = 3;
-- CORRECT: CASE WHEN for mass updates with different values
UPDATE products
SET price = CASE id
WHEN 1 THEN 9500000
WHEN 2 THEN 150000
WHEN 3 THEN 350000
ELSE price -- ← important: don't change other rows
END,
updated_at = NOW()
WHERE id IN (1, 2, 3); -- ← bound the scope with WHERE
Go Implementation for Bulk UPDATE with Different Values #
type PriceUpdate struct {
ProductID int64
NewPrice int64
}
func BulkUpdatePrices(ctx context.Context, db *sql.DB, updates []PriceUpdate) error {
if len(updates) == 0 {
return nil
}
const batchSize = 300
for i := 0; i < len(updates); i += batchSize {
end := i + batchSize
if end > len(updates) {
end = len(updates)
}
batch := updates[i:end]
if err := updatePricesBatch(ctx, db, batch); err != nil {
return fmt.Errorf("price update batch %d-%d failed: %w", i, end, err)
}
time.Sleep(5 * time.Millisecond) // throttle
}
return nil
}
func updatePricesBatch(ctx context.Context, db *sql.DB, updates []PriceUpdate) error {
// Build the CASE WHEN dynamically
caseExpr := strings.Builder{}
caseExpr.WriteString("CASE id ")
ids := make([]interface{}, 0, len(updates))
args := make([]interface{}, 0, len(updates)+len(updates))
for _, u := range updates {
caseExpr.WriteString("WHEN ? THEN ? ")
args = append(args, u.ProductID, u.NewPrice)
ids = append(ids, u.ProductID)
}
caseExpr.WriteString("ELSE price END")
// Build the IN clause placeholders
placeholders := strings.Repeat("?,", len(ids)-1) + "?"
allArgs := append(args, ids...)
query := fmt.Sprintf(`
UPDATE products
SET price = %s, updated_at = NOW()
WHERE id IN (%s)
`, caseExpr.String(), placeholders)
result, err := db.ExecContext(ctx, query, allArgs...)
if err != nil {
return err
}
affected, _ := result.RowsAffected()
if affected != int64(len(updates)) {
// Some IDs weren't found — log a warning but don't error
// Depending on the business requirement
log.Warn("some products not found during bulk price update",
"expected", len(updates), "actual", affected)
}
return nil
}
Bulk UPDATE with Temporary Tables (for Very Large Amounts) #
For updates with different values per row in very large quantities (> 50,000 rows), CASE WHEN can get too long. The alternative is using a temporary table:
-- Step 1: create and fill a temporary table
CREATE TEMPORARY TABLE temp_price_updates (
product_id BIGINT NOT NULL,
new_price BIGINT NOT NULL,
PRIMARY KEY (product_id)
);
INSERT INTO temp_price_updates (product_id, new_price) VALUES
(1, 9500000), (2, 150000), (3, 350000), ...;
-- Step 2: UPDATE via a JOIN to the temporary table
UPDATE products p
JOIN temp_price_updates t ON p.id = t.product_id
SET p.price = t.new_price, p.updated_at = NOW();
-- Step 3: clean up the temporary table (optional, automatic when the session ends)
DROP TEMPORARY TABLE IF EXISTS temp_price_updates;
Bulk DELETE: Safe and Reversible #
Bulk DELETE is the riskiest operation because it can’t be undone. A good strategy must consider data safety, index impact, and rollback capability.
Soft Delete as a Safety Net #
-- ANTI-PATTERN: direct hard delete — can't be undone
DELETE FROM orders WHERE created_at < '2024-01-01';
-- CORRECT — Step 1: soft delete first (mark as deleted)
UPDATE orders
SET deleted_at = NOW()
WHERE created_at < '2024-01-01'
AND deleted_at IS NULL;
-- Verify the count to be hard-deleted before doing it
SELECT COUNT(*) FROM orders
WHERE deleted_at IS NOT NULL
AND deleted_at < NOW() - INTERVAL 7 DAY;
-- Step 2: archive to a historical table before the hard delete
INSERT INTO orders_archive
SELECT * FROM orders
WHERE deleted_at IS NOT NULL
AND deleted_at < NOW() - INTERVAL 7 DAY;
-- Step 3: hard delete after verification and archiving
DELETE FROM orders
WHERE deleted_at IS NOT NULL
AND deleted_at < NOW() - INTERVAL 7 DAY;
Chunked DELETE to Avoid Long Locks #
A large DELETE in one transaction can hold locks on many rows at once, blocking other queries that need to access those rows. Chunked DELETE splits the operation into small batches:
// ChunkedDelete removes old data in small batches to minimize locks
func ChunkedDeleteOldOrders(ctx context.Context, db *sql.DB, olderThan time.Time) (int64, error) {
const chunkSize = 1000
var totalDeleted int64
for {
// Delete a maximum of chunkSize rows per iteration
result, err := db.ExecContext(ctx, `
DELETE FROM orders
WHERE created_at < ?
AND status IN ('completed', 'cancelled')
AND deleted_at IS NOT NULL
LIMIT ?
`, olderThan, chunkSize)
if err != nil {
return totalDeleted, fmt.Errorf("chunk delete failed: %w", err)
}
affected, _ := result.RowsAffected()
totalDeleted += affected
if affected == 0 {
break // Nothing left to delete
}
// Log progress for long operations
log.Info("delete progress", "deleted_so_far", totalDeleted)
// Pause between chunks — let the database and replicas breathe
select {
case <-ctx.Done():
return totalDeleted, ctx.Err() // Respect cancellation
case <-time.After(50 * time.Millisecond):
}
}
return totalDeleted, nil
}
Never runDELETE FROM tablewithout aWHEREclause orLIMITon a large production table. One unbounded DELETE query can hold locks on the entire table for minutes to hours, blocking all read and write operations. Always use chunked DELETEs withLIMITand pauses between batches.
Bulk CUD’s Impact on the System: What to Monitor #
Replication Lag #
Bulk CUD produces large volumes in the binlog/WAL that must be replicated to read replicas. Without throttling, replicas can fall far behind the primary:
The impact of a 1-million-row bulk INSERT on replication:
──────────────────────────────────────────────────────────────
Primary:
INSERT finishes in 30 seconds
Binlog size: ~500 MB
Read replica:
Must replay 500 MB of binlog operations
Can take 2-5 minutes
During that time: replica queries read old data
Impact on the application:
A cache invalidated but read from the replica
→ Gets old data → stale data issue
──────────────────────────────────────────────────────────────
Monitor replication lag before and during bulk operations:
-- MySQL: check replication lag on the replica
SHOW SLAVE STATUS\G
-- Look at: Seconds_Behind_Master
-- PostgreSQL: check from the primary
SELECT
client_addr,
state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn,
(sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;
Index Maintenance During Bulk Writes #
Every inserted or updated row needs index updates on all indexes of that table. For tables with many indexes, this can become a bottleneck:
-- See how many indexes exist on the target table
SHOW INDEX FROM orders;
-- If there are 8 indexes: every INSERT updates 8 B-Trees
-- For large bulk imports in a maintenance window:
-- Step 1: drop the non-primary indexes
ALTER TABLE products DROP INDEX idx_products_name;
ALTER TABLE products DROP INDEX idx_products_category_price;
-- Step 2: insert the data (far faster without index maintenance)
INSERT INTO products ...; -- or LOAD DATA INFILE
-- Step 3: rebuild the indexes after the insert finishes
ALTER TABLE products ADD INDEX idx_products_name (name);
ALTER TABLE products ADD INDEX idx_products_category_price (category_id, price);
-- Note: this step only fits a maintenance window
-- Don't do it during active traffic because the table temporarily has no indexes
Stale Table Statistics after Bulk DELETE #
After deleting many rows, the statistics used by the query planner can become inaccurate, causing the planner to choose the wrong execution plan:
-- After a bulk DELETE, update the statistics:
-- MySQL
ANALYZE TABLE orders;
-- PostgreSQL
ANALYZE orders;
VACUUM ANALYZE orders; -- also cleans up dead tuples
-- Monitor the effect:
EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2026-01-01';
-- Check whether the rows estimate is now more accurate
Idempotency in Bulk CUD #
Bulk operations run repeatedly (retries after failures, jobs running twice) must produce the same state — no duplication or inconsistent states.
-- ANTI-PATTERN: non-idempotent INSERT
INSERT INTO order_logs (order_id, status, created_at)
VALUES (?, 'processed', NOW());
-- If run twice: two identical rows in order_logs
-- CORRECT: idempotent with ON DUPLICATE KEY DO NOTHING
INSERT INTO order_logs (order_id, status, processed_at)
VALUES (?, 'processed', NOW())
ON DUPLICATE KEY UPDATE processed_at = processed_at;
-- If it already exists: no-op
-- Requires a UNIQUE constraint on (order_id, status)
-- Or with PostgreSQL:
INSERT INTO order_logs (order_id, status, processed_at)
VALUES (?, 'processed', NOW())
ON CONFLICT (order_id, status) DO NOTHING;
// Idempotent pattern for retryable bulk jobs:
type OrderProcessResult struct {
OrderID int64
Processed bool
Error error
}
func ProcessOrdersBatch(ctx context.Context, db *sql.DB, orderIDs []int64) []OrderProcessResult {
results := make([]OrderProcessResult, len(orderIDs))
// Check which ones were processed before
processed, _ := getAlreadyProcessed(ctx, db, orderIDs)
processedSet := make(map[int64]bool)
for _, id := range processed {
processedSet[id] = true
}
// Filter to only the unprocessed ones
var toProcess []int64
for _, id := range orderIDs {
if !processedSet[id] {
toProcess = append(toProcess, id)
}
}
// Process only the unprocessed ones
if len(toProcess) > 0 {
_ = bulkMarkProcessed(ctx, db, toProcess)
}
// Build the result — idempotent: already-processed ones count as success
for i, id := range orderIDs {
results[i] = OrderProcessResult{
OrderID: id,
Processed: true, // both new and already-existing ones
}
}
return results
}
Anti-Patterns to Avoid #
-- ✗ Anti-pattern 1: one-by-one INSERTs in a loop — N roundtrips, N WAL fsyncs
-- (in Go)
for _, product := range products {
db.ExecContext(ctx, "INSERT INTO products VALUES (?, ?, ?)",
product.Name, product.Price, product.Stock)
}
-- ✓ Solution: multi-row INSERTs with 500-row batches
-- ✗ Anti-pattern 2: UPDATEs without a LIMIT or scope-limiting WHERE
UPDATE orders SET status = 'reviewed';
-- This updates EVERY order, holding locks on the entire table
-- ✓ Solution: UPDATE ... WHERE ... LIMIT N with chunking
-- ✗ Anti-pattern 3: bulk DELETEs without archiving
DELETE FROM logs WHERE created_at < '2025-01-01';
-- Data permanently lost, no recovery
-- ✓ Solution: soft delete → archive to a history table → hard delete
-- ✗ Anti-pattern 4: bulk CUD during peak traffic hours
-- A nightly job scheduled at 19:00 when users are most active
-- ✓ Solution: schedule during off-peak hours (02:00-05:00), or use throttling
-- ✗ Anti-pattern 5: one transaction for the entire large batch
tx.Begin()
for i := 0; i < 1000000; i++ {
tx.Exec("INSERT ...") // 1 million rows in one transaction
}
tx.Commit()
-- Locks held for the entire 1 million INSERTs
-- ✓ Solution: one transaction per batch (500-1000 rows)
-- ✗ Anti-pattern 6: bulk operations without replication lag monitoring
-- The job finishes, but the read replica is still 5 minutes behind
-- Replica queries return old data
-- ✓ Solution: monitor replication lag, add pauses if lag exceeds a threshold
Safe Bulk CUD Checklist #
PREPARATION BEFORE THE OPERATION:
□ Is there a recent backup (or snapshot) of the data to be modified?
□ Is the operation scheduled outside peak traffic hours?
□ Is there a rollback mechanism if the operation fails midway?
□ Is the batch size determined (recommendation: 500-1000 for INSERT, 100-500 for DELETE)?
□ Is there throttling between batches to keep replication lag in check?
FOR BULK INSERT:
□ Using multi-row INSERTs, not one INSERT per row?
□ ON DUPLICATE KEY / ON CONFLICT used for idempotent upserts?
□ Batch size not too large (max ~1000 rows per statement)?
FOR BULK UPDATE:
□ Does the WHERE clause clearly bound the scope (not updating every row)?
□ Using IN clauses or CASE WHEN, not per-row loops?
□ Only the truly changed columns updated?
FOR BULK DELETE:
□ Is there a soft delete before the hard delete?
□ Is there archiving before the hard delete for data that must be kept?
□ Using chunked DELETEs with LIMIT, not LIMIT-less DELETEs?
AFTER THE OPERATION:
□ Table statistics updated after a large DELETE (ANALYZE)?
□ Replication lag back to normal?
□ Is the query planner still using the right execution plan (EXPLAIN)?
Summary #
- One-by-one INSERTs = N WAL fsyncs — every single INSERT needs a log flush to disk for durability. Multi-row INSERTs do this once for hundreds of rows. The throughput difference can be 100× or more.
- The right batch size is the key — too small (10 rows) is inefficient, too large (100,000 rows) holds locks too long. 500-1000 rows per batch is a good starting point for most cases.
- Throttling between batches protects replication lag — 10-50ms pauses between batches give replicas time to catch up, preventing read replicas from falling far behind and serving stale data.
- Bulk DELETEs must go through soft delete + archive — direct hard deletes can’t be undone. Soft delete first, verify, archive if needed, then hard delete with chunking.
- CASE WHEN for different values per row — more efficient than per-row loops because it’s one roundtrip and one lock acquisition. For very large amounts (>50,000), use a temporary table + UPDATE JOIN.
- Idempotency is mandatory for bulk jobs — retryable jobs must produce the same state even when run twice. Use ON DUPLICATE KEY DO NOTHING or ON CONFLICT DO NOTHING.
- Monitor replication lag during and after the operation — large bulk CUDs can leave read replicas minutes to hours behind, causing replica queries to return old data.
- Run ANALYZE after large bulk DELETEs — query planner statistics can become inaccurate after many rows are deleted, causing the planner to choose the wrong execution plan.