A data import inserts one row per statement. Each one is a network round trip, a parse, a plan, an execution, and an index update. A million of those takes hours, and the database is barely working — it is waiting on the network.
The same load, done properly, finishes in minutes.
What changed in 2026
- Bulk copy paths became better known. Using the dedicated bulk-load interface rather than batched inserts spread as standard practice.
- Staging table patterns matured. Loading raw and then transforming inside the database became the default ETL shape.
- Index management during load got documented. The drop-and-rebuild decision became better understood.
- Unlogged and minimally-logged options got attention. Reducing write-ahead log volume during initial loads became a recognised technique.
Where the time goes
| Cost |
Row-by-row |
Batched |
Bulk copy |
| Network round trips |
One per row |
One per batch |
One stream |
| Statement parsing |
Per row |
Per batch |
None |
| Index maintenance |
Per row |
Per row |
Per row |
| Transaction overhead |
Per row, if autocommit |
Per batch |
Per batch |
| Log volume |
High |
High |
Lower in some modes |
Round trips dominate at the start. A batch of a thousand rows in one statement eliminates 999 round trips, and that single change frequently accounts for most of the available improvement.
Index maintenance becomes the limiting factor once round trips are handled, because every index on the table is updated for every row regardless of how the rows arrive.
Batch the statements and the commits
Two separate decisions people conflate.
Statement batching puts many rows in one insert. Reduces round trips and parsing.
Transaction batching commits every N rows rather than per row or once at the end.
Both matter. Autocommit per row means a durable write per row, which is the other reason row-by-row loading is slow — see write-ahead logging.
The opposite extreme — one transaction for the entire load — has its own problems: a long-running transaction blocking cleanup, an enormous rollback if it fails at 90%, and no progress visibility — see MVCC explained.
Committing every few thousand rows is the usual balance. Failures lose one batch rather than everything, and transactions stay short.
Index management
For a large load into an existing table, index maintenance per row can exceed the insert cost.
Dropping indexes before the load and rebuilding after is frequently much faster, because building an index in bulk is far more efficient than maintaining it incrementally.
That is only safe when the table is not serving queries during the load, since it is unindexed meanwhile. It also means the rebuild is a substantial operation of its own, so the saving must exceed it — generally true for a load that is large relative to existing table size.
For loading into an empty table, create indexes after loading rather than before. Same reasoning, no downside.
Constraints follow similar logic, and dropping them requires confidence the data is clean — see check constraints.
Look for per-row work
Before optimising the insert path, check what fires per row.
Triggers are the usual culprit. A trigger doing anything non-trivial per row multiplies across a million rows, and it is frequently the dominant cost while everyone optimises the insert.
Foreign key checks require a lookup per row on the referenced table, which needs an index on the referencing column or each check scans.
Generated columns are computed per row, which is cheap individually and not free at scale.
The pattern that avoids all of this: load raw into a staging table with no indexes, constraints, or triggers, then transform and move into the real table with set-based statements. The database processes the transformation as a bulk operation rather than row by row, and it is usually dramatically faster than doing the work during ingestion — see COPY bulk loading.
Common mistakes
- Row-by-row inserts. Round trips dominate.
- Autocommit per row. A durable write per row.
- One transaction for the whole load. Long transaction, huge rollback risk.
- Maintaining indexes during a large load. Drop and rebuild instead.
- Ignoring triggers. Frequently the real cost.
- Missing indexes on foreign key columns. Every check scans.
- Transforming during ingestion. Stage raw, transform in bulk.
FAQ
What batch size should I use?
Large enough to amortise round trips, small enough that a failure does not lose much and transactions stay short. Thousands rather than tens or millions; measure on your setup.
Should I disable constraints?
Only with confidence the data is clean, and re-enabling requires validating existing rows anyway. Frequently not worth it unless the load is very large.
What about parallel loading?
Effective when the target supports concurrent writes without contention. Parallel loads into the same indexes can contend, so measure rather than assuming linear improvement.
Does this apply to updates too?
The same principles — batch, watch triggers, mind transaction size. Updates additionally create dead row versions, so a large update has cleanup consequences a load does not.
Where to go next
For the dedicated bulk path, read COPY bulk loading. For the transaction-size considerations, MVCC explained, and for index rebuild costs, index bloat.