Bulk insert

Also known as: batch insert

On this page 7

A bulk insert is a database operation that loads many rows in a single statement or stream instead of issuing one INSERT per row. In database performance work, bulk inserts (also called batch inserts) are the standard way to load large datasets, because they remove per-row overhead such as network round trips, statement parsing, transaction commits and log flushes.

Every major engine offers dedicated bulk paths: COPY in PostgreSQL, LOAD DATA INFILE in MySQL, BULK INSERT and the bcp utility in SQL Server, and SQL*Loader direct-path loads in Oracle. Client drivers add batched parameter binding, which groups thousands of rows into one call.

How a bulk insert reduces overhead

A bulk insert is faster because the fixed cost of each operation is paid once per batch rather than once per row. A single-row INSERT over a network may take 0.5 to 2 ms, most of it spent on the round trip, parsing and an fsync at commit. Writing 1,000 rows in one batch spreads that fixed cost across the whole batch.

The table below breaks down where time goes in row-by-row loading and how bulk loading changes each cost.

Cost componentRow-by-row insertBulk insert
Network round tripOne per rowOne per batch or stream
Statement parsing and planningRepeated or cached per callOnce per batch
Commit and log flushOne per row in autocommitOne per batch or load
Lock acquisitionRepeated per rowAmortized, sometimes table-level
Write-ahead log volumeFull per rowReduced with minimal logging
Per-row costs removed by bulk loading

Some engines also offer minimal logging or direct-path writes, where new data pages are written directly and only page allocations are logged. This can cut log volume sharply, but it usually requires conditions such as an empty table, a table lock or a specific recovery model.

Bulk insert methods and when each applies

Bulk insert methods range from multi-row VALUES statements to native file-loading commands, and the right choice depends on where the data lives and how much of it there is. The table compares the common options.

MethodTypical speedBest use
Multi-row INSERT5 to 50 times row-by-rowApplication batches of hundreds of rows
Driver batchingSimilar to multi-row INSERTPrepared statements from code
COPY or LOAD DATAOften 10 to 100 times row-by-rowFiles or streams with millions of rows
Direct-path loadFastest availableData warehouse and initial loads
INSERT … SELECTServer-side, no networkMoving data between tables
Common bulk loading techniques

The ratios are indicative only. Actual gains depend on row width, indexes, storage latency and network distance. Over a high-latency link, batching alone can deliver a hundredfold improvement because round trips dominate; on a local socket with fast disks, the gain is smaller.

Choosing batch size and transaction scope

The best batch size is large enough to amortize overhead but small enough to limit lock time, memory use and rollback cost. Gains usually flatten somewhere between 1,000 and 10,000 rows per batch; beyond that, larger batches add risk without adding speed.

Engines also impose hard limits. SQL Server allows at most 1,000 rows in one VALUES list and 2,100 parameters per statement, while PostgreSQL caps a statement at 65,535 bind parameters. Wide rows therefore need smaller batches.

SituationRecommended approachReason
Online system with live trafficBatches of 500 to 5,000 rows, short transactionsLimits lock contention and replication lag
Nightly warehouse loadNative COPY or direct-path loadMaximum throughput, quiet period
Initial migration of a large tableLoad without secondary indexes, build them afterwardIndex builds are cheaper in one pass
Untrusted or messy source dataLoad into a staging table firstValidation and error handling stay isolated
Batch sizing decisions

Committing the entire load in one transaction is simple but risky: a failure near the end rolls back everything, and a huge transaction can bloat the log or the undo space. Committing per batch with a restart marker is the safer pattern for long loads.

Indexes, constraints and triggers during loading

Secondary structures are often the largest hidden cost of a bulk insert, because every index must be updated for every new row. On a table with five indexes, each row can trigger several random page writes, and the load turns from sequential to random I/O.

  • Drop or disable secondary indexes before very large loads and rebuild them afterward; a sorted rebuild is usually faster than incremental maintenance and leaves less index fragmentation.
  • Defer constraint checks or validate in a staging table, then enable foreign keys once the data is known to be clean.
  • Review triggers, because a row-level trigger silently converts a bulk operation back into per-row work.
  • Sort input by the clustered key so that pages fill sequentially instead of splitting.

After the load, refresh optimizer statistics. A table that grew from thousands to millions of rows will otherwise receive poor execution plans until automatic statistics catch up, and in PostgreSQL a manual ANALYZE and VACUUM help the planner and the visibility map.

Risks and common mistakes

Bulk inserts trade speed for coarser control, and most problems come from forgetting the side effects on the rest of the system.

SymptomLikely causeFix
Load slower than expectedAutocommit on, one commit per rowWrap batches in explicit transactions
Replicas fall far behindHuge single transaction or log burstThrottle and commit in smaller batches
Application queries blockTable lock held during loadLoad off-peak or into a staging table
Whole load fails on one bad rowNo error tolerance configuredUse reject files or a staging table
Bulk insert problems and fixes

A subtle mistake is building one giant SQL string by concatenating values. It defeats plan caching and can open the door to SQL injection. Parameterized batching or a native load command is both safer and faster.

Understanding it simply

Think of moving house. Carrying one book at a time from the old flat to the new one means a full trip for every book. Packing books into boxes and loading a van means far fewer trips, even though the books are the same.

A bulk insert is the boxes-and-van approach for data. Instead of sending each row on its own trip to the database, the program packs thousands of rows together. The database does the paperwork once per box, so large loads finish far faster.

Applied in practice

A logistics company imports about 20 million shipment events each night from partner files into a PostgreSQL reporting database. The original script inserted rows one at a time with autocommit and took close to six hours, often running into business hours.

The data team switched to COPY into an unlogged staging table, validated and deduplicated the rows with SQL, and then moved them into the main table with INSERT … SELECT in batches of 50,000 rows. Two secondary indexes on the main table were rebuilt after the load, and ANALYZE ran at the end. The nightly job now finishes in about 25 minutes, and replicas stay within a few seconds of the primary throughout.