Wrapping Bulk INSERTs in a Transaction

Transaction-Wrapped Bulk Inserts

Wrapping your bulk INSERT statements in a transaction provides two benefits: atomicity (all-or-nothing) and performance (reduced commit overhead).

Basic Transaction Wrapper

BEGIN;

INSERT INTO "events" ("id", "type", "timestamp")
VALUES
  (1, 'login', '2024-01-01 09:00:00'),
  (2, 'click', '2024-01-01 09:01:00'),
  -- ... more rows ...
  (100, 'logout', '2024-01-01 10:00:00');

INSERT INTO "events" ("id", "type", "timestamp")
VALUES
  (101, 'login', '2024-01-01 11:00:00'),
  -- ... next batch ...
  (200, 'purchase', '2024-01-01 12:00:00');

COMMIT;

SQL Server Variant

SQL Server uses BEGIN TRANSACTION instead of BEGIN:

BEGIN TRANSACTION;

INSERT INTO [events] ([id], [type], [timestamp])
VALUES
  (1, 'login', '2024-01-01 09:00:00');

COMMIT;

Why Transactions Matter

Without transaction: Each INSERT statement is auto-committed individually.

• If batch 5 of 10 fails, batches 1-4 are already committed
• Each auto-commit triggers a disk sync (slow on HDDs)

With transaction: All batches succeed or all are rolled back.

• Atomic: partial imports are impossible
• Faster: one disk sync at COMMIT instead of N syncs

Performance Impact

Scenario	SQLite (1000 rows)	PostgreSQL (1000 rows)
No transaction	~2 seconds	~500ms
With transaction	~50ms	~100ms

The speedup is most dramatic in SQLite, where each auto-commit forces a filesystem sync.

Best Practices

1. Keep transactions short (< 10,000 rows) to avoid lock contention
2. Add error handling in your script: BEGIN; INSERT ...; INSERT ...; COMMIT; with rollback on failure
3. For very large imports, split into multiple transactions of 5,000-10,000 rows each