Post cover image
Image Source

February 26, 2026

Batch Inserts in SQL for Large Data Loads

Large data loads are where insert style stops being a small detail and starts driving throughput, lock time, and how much work the database…

By Alexander Obregon

9 min read

None

Large data loads are where insert style stops being a small detail and starts driving throughput, lock time, and how much work the database does to keep its log and indexes consistent. One row per statement works fine for light writes, but it tends to burn time on network round trips, statement parsing, and repeated commit overhead. Batched inserts change the workload by sending fewer statements that carry more rows, so you get more rows per second and less overhead per row.

I publish articles like this daily, and I have a SQL section on Substack where you'll find my whole series.

Why Single Row Inserts Slow Down at Scale

High volume loads change what matters. Most of the time no longer goes into the row data itself. Time shifts into repeated overhead around each statement, plus the storage and durability work the database has to do for every inserted row.

Network Round Trips and Parsing Costs

Extra network chatter is the first thing that starts to dominate. One row per INSERT means the client sends a request, the server parses it, plans it, executes it, then sends a response back. That full cycle repeats for every row. Even on a fast network, latency does not go away. Latency stacks up in a way that is hard to notice until you load a large number of rows.

Parsing is part of that repeated work too. SQL text has to be tokenized and parsed into an internal representation before execution can start. When the database sees the same insert statement shape over and over with different values, it still has to do statement level processing each time unless the client and driver use prepared statements and the server can reuse plans. Single row inserts can still be fast when prepared statements are used, yet they still carry per statement overhead and per round trip overhead.

Client side behavior gives away what is happening. App code loops, sends one INSERT, waits for the database to respond, then moves to the next row. The statement itself is fine, but throughput gets capped by the repeated request and response cycle, so the bottleneck ends up being round trip time and per statement work instead of disk bandwidth or CPU spent writing rows.

INSERT INTO sales_event (event_id, store_id, occurred_at, amount_cents)
VALUES (1001, 17, '2026-02-25 09:01:00', 1299);

INSERT INTO sales_event (event_id, store_id, occurred_at, amount_cents)
VALUES (1002, 17, '2026-02-25 09:01:03', 2599);

INSERT INTO sales_event (event_id, store_id, occurred_at, amount_cents)
VALUES (1003, 18, '2026-02-25 09:01:09', 799);

Prepared statements reduce parse work on repeated shapes because the database can reuse the prepared statement plan, but that does not remove the network and execution cycle per row. It is still one execute per row, one response per row, and one set of lock and log actions per row.

Transaction Overhead and Log Pressure

Autocommit mode turns single row inserts into a stream of tiny transactions. That detail matters because each commit has work around it, and durability pushes changes through a transaction log or write ahead log before the database can claim the transaction is committed. Lots of tiny commits produce lots of log flushes. That means the bottleneck can shift away from table storage and into log I/O. Some databases can group log flushes or amortize them under load, but the general direction stays the same. More commits mean more commit work.

Each inserted row changes table data and internal bookkeeping, and the database writes enough to the log so it can replay those changes after a crash. When the write volume rises, the log volume rises too. On PostgreSQL, high WAL generation can trigger checkpoint pressure if the system reaches its configured checkpoint and WAL sizing limits. That can surface as checkpoint warnings and reduced throughput during heavy write bursts.

Transaction boundaries get easier to see when you put autocommit next to an explicit BEGIN and COMMIT. Autocommit treats each statement as its own transaction, so a commit happens after every insert. Wrapping multiple inserts in one transaction keeps the work the same at the row level, but it moves the commit to the end of the group so you can see exactly where the database has to finish and durably record that unit of work.

BEGIN;

INSERT INTO sales_event (event_id, store_id, occurred_at, amount_cents)
VALUES (2001, 21, '2026-02-25 10:05:00', 499);

INSERT INTO sales_event (event_id, store_id, occurred_at, amount_cents)
VALUES (2002, 21, '2026-02-25 10:05:01', 899);

COMMIT;

That block still uses one row per statement, but it reduces commit work by committing once for the group. Log volume still exists, yet the expensive flush boundary happens less often.

Lock Time and Concurrency Impact

Locks are part of how databases keep concurrent transactions from stepping on each other. Inserts take locks to protect table and index structures while changes are applied. The details vary by database engine and isolation level, but two themes show up quickly when load volume climbs.

First theme is that single row inserts increase the lock acquire and release churn. Every statement takes locks, does its work, then releases locks at statement end or transaction end. When there are millions of statements, there are millions of lock cycles, and that consumes CPU and internal latch time inside the engine. The second theme is that larger transactions hold locks longer. When you group work into bigger transactions, you cut per transaction overhead, but you also keep locks held for longer intervals. That can block writers, and on engines like PostgreSQL it can still slow parts of the system through hot index pages, foreign key checks, and other lock waits, even though plain reads are not blocked by inserts under MVCC.

This is also where hot spots show up. Secondary indexes can become a shared contention point because inserts tend to land in similar index pages when the indexed values are clustered. Foreign key checks can add more reads and locks. Unique constraints can force lookups that contend under heavy insert concurrency.

Some of this can be observed with basic lock views when a load is running. In PostgreSQL, a quick check is pg_locks joined to pg_stat_activity to see lock types and who is waiting.

SELECT a.pid,
       a.state,
       a.wait_event_type,
       a.wait_event,
       l.locktype,
       l.mode,
       l.granted,
       a.query
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE a.datname = current_database()
ORDER BY a.pid;

This query does not fix contention, but it helps connect symptoms such as stalled throughput to lock waits rather than to parsing time or disk time.

Index Maintenance Costs During Heavy Loads

Every inserted row updates the base table plus every index on that table. That means index work scales with row count and index count. With a single index, one row becomes one extra index entry. With five indexes, one row becomes five extra index entries, plus constraint checks that may cause index lookups. Index maintenance has two costs that are easy to miss at first. One cost is CPU and memory churn inside the engine to navigate B tree pages, split pages when needed, and manage free space. Second cost is write amplification. Inserting one row into a table can generate multiple physical writes because index pages also change, and those page changes also need logging for durability.

PostgreSQL documentation explicitly recommends loading data first and then creating indexes after the load when building a new table, because building an index over existing rows is faster than updating it row by row during a large load. That point is specific to the new table case, but it explains why inserts slow down when indexes are present.

A basic way to make index overhead visible is to look at the definition count and think through what each row triggers. This example table has a primary key plus two secondary indexes:

CREATE TABLE sales_event (
  event_id      bigint PRIMARY KEY,
  store_id      int    NOT NULL,
  occurred_at   timestamp NOT NULL,
  amount_cents  int    NOT NULL
);

CREATE INDEX sales_event_store_id_idx ON sales_event (store_id);
CREATE INDEX sales_event_occurred_at_idx ON sales_event (occurred_at);

With that layout, every insert touches the table storage plus three index structures. When the row count gets large, that steady per row index work can become the main limiter, and statement overhead becomes the smaller part of the total.

Practical Batching Patterns for Bulk Loads

Batching is the middle ground between one row per statement and file based bulk import. It keeps the work inside normal SQL inserts, but it reduces how often the client and server have to do statement level overhead. Bulk loaders sit at the far end of the speed spectrum because they stream data in a form the database can ingest with less SQL statement handling.

Multi Row Inserts with Insert Select Pipelines

Sending more than one row in a single INSERT is the most common step up. The database still parses and executes SQL, but the request happens once for a group of rows, so the client pays fewer round trips and the server pays fewer statement setup costs per inserted row. Multi row inserts also make it easier to control batch size because the batch is visible right in the statement.

INSERT INTO sales_event (event_id, store_id, occurred_at, amount_cents)
VALUES
  (3001, 31, '2026-02-25 11:15:00', 1599),
  (3002, 31, '2026-02-25 11:15:02',  999),
  (3003, 32, '2026-02-25 11:15:05', 2199),
  (3004, 33, '2026-02-25 11:15:07',  499);

Statement size limits and driver limits do matter, so batching usually turns into a repeated cycle of building an INSERT for a chunk of rows, sending it, then moving to the next chunk. The database side work still happens per row, like index updates and constraint checks, but the outer shell work around the statement gets amortized across the batch.

Parameterization stays important once batches get large. Instead of embedding literal values, most apps send placeholders and bind values through the driver so the database can treat the statement shape as stable while values change. The exact placeholder style differs by database and driver, but the idea is the same.

INSERT INTO sales_event (event_id, store_id, occurred_at, amount_cents)
VALUES
  (?, ?, ?, ?),
  (?, ?, ?, ?),
  (?, ?, ?, ?);

Insert select pipelines are another way to batch, and they work well when the data already lives in the database. A common flow is loading into a staging table first, then moving into the final table with a single statement that pulls rows from staging. That avoids app to database row shipping for the second step, and it lets the database perform the read and write in one internal operation.

INSERT INTO final_orders (order_id, customer_id, created_at, total_cents)
SELECT order_id, customer_id, created_at, total_cents
FROM staging_orders
WHERE created_at >= '2026-02-01';

Filtering in the SELECT can also turn the move into a controlled batch process by selecting a window of rows at a time, which keeps each write transaction bounded without changing the shape of the SQL.

Bulk Loaders That Bypass Most SQL Statement Overhead

File based bulk import tools exist because batched inserts still carry SQL statement overhead. They still parse a statement, bind values, execute row inserts, and handle per statement bookkeeping. Bulk loaders stream data from a file or stream into the table in a way that reduces the amount of repeated SQL handling, so throughput is usually higher when loading millions of rows.

PostgreSQL COPY can read from standard input or from a server-side file. COPY FROM '/path/file.csv' reads a file on the database server host and is restricted to superusers or roles such as pg_read_server_files, so it is common to load through standard input in client tools.

COPY sales_event (event_id, store_id, occurred_at, amount_cents)
FROM STDIN
WITH (FORMAT csv, HEADER true);

SQL Server has two common bulk load paths, bcp and BULK INSERT. bcp is a command line utility that moves large volumes of data between SQL Server tables and data files, so it fits naturally into import and export workflows. BULK INSERT does the file load inside T-SQL, so it fits well into deployment scripts or ETL steps that already run inside SQL Server.

BULK INSERT dbo.SalesEvent
FROM 'C:\import\sales_event.csv'
WITH (
  FIRSTROW = 2,
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\r\n',
  TABLOCK
);

Logging behavior is one reason SQL Server bulk import can run much faster than row by row inserts. Under the full recovery model, bulk imports are fully logged, which means the transaction log has to record a detailed history of the changes. Under the simple or bulk logged recovery models, bulk import operations can qualify for minimal logging in certain cases, so the log work is lighter and large loads are less likely to run into log growth pressure. Table level locking can be part of meeting the minimal logging requirements, which is why you often see TABLOCK paired with BULK INSERT.

MySQL LOAD DATA reads rows from a text file into a table at very high speed, and the file can be read from the server host or client host depending on the LOCAL modifier. that model is similar to PostgreSQL COPY in the sense that the database ingests a stream of delimited rows rather than receiving a separate INSERT statement for each row.

LOAD DATA INFILE '/var/lib/mysql-files/sales_event.csv'
INTO TABLE sales_event
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(event_id, store_id, occurred_at, amount_cents);

Each of these bulk loaders is still inserting rows, updating indexes, and honoring constraints, but they reduce overhead around statement parsing and per statement execution. That is why they tend to beat even well batched INSERT statements for large file based imports when the database engine and deployment environment allow them.

Conclusion

Batch inserts speed up large loads by reducing how often the client and server repeat statement level work and commit work. Fewer round trips and fewer statement executions per row frees up time for what the database still has to do for every row, which is writing data pages, updating indexes, taking the right locks, and recording durability changes in the log. When the load shifts into the millions of rows, file based bulk tools go further by streaming data into the engine with far less SQL statement handling, which is why commands like PostgreSQL COPY, SQL Server bcp and BULK INSERT, and MySQL LOAD DATA INFILE tend to win when that import style fits the workflow.

  1. PostgreSQL COPY Command
  2. PostgreSQL Populating a Database
  3. SQL Server BULK INSERT
  4. SQL Server bcp Utility
  5. SQL Server Prerequisites for Minimal Logging in Bulk Import
  6. MySQL LOAD DATA Statement
None