The Cost of Row by Row
Inserting one row at a time pays a fixed cost per statement: parsing, a round trip, and often a separate transaction commit. For a million rows these fixed costs dominate and the load crawls.
Batching and Bulk Loaders
Batching groups many rows into one statement or one transaction, amortizing the fixed overhead. Bulk loaders go further with a dedicated copy path that streams rows past the normal statement engine.
To speed a large initial load:
- Wrap many inserts in one transaction so there is one commit, not many.
- Use a multi row insert or the database copy command.
- Drop or disable indexes and constraints during load, then rebuild them once.
The Tradeoff
Bigger batches lower overhead but raise memory use and lock duration, and a failure rolls back the whole batch. Pick a batch size that balances throughput against retry cost.
Key idea
Batching and bulk loading amortize per statement overhead so large loads run far faster, with batch size trading throughput against memory and retry cost.