Inserting Multiple Rows Efficiently

Insert them in batches with a multi-row VALUES clause, and wrap each batch in a transaction. A multi-row INSERT is a single statement, so the engine parses and plans it once, and the transaction means the batch commits once instead of once per row.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  sku VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL
);

BEGIN;

INSERT INTO products (id, sku, name, unit_price) VALUES
  (1, 'KB-01',  'Mechanical Keyboard', 79.50),
  (2, 'HUB-02', 'USB-C Hub',           32.00),
  (3, 'MON-03', '27-inch Monitor',    249.99),
  (4, 'DSK-04', 'Standing Desk',      419.00);

COMMIT;

SELECT id, sku, unit_price FROM products ORDER BY id;

Four rows come back. One statement inserted all four, inside one transaction.

Why batching wins is about the number of round trips. Inserting four rows one at a time means four statements, each with its own parse, plan, execute and commit — four log flushes and four network round trips if you are on a network connection. One four-row INSERT inside one transaction means one parse, one plan, one log flush and one round trip. The saving grows with the batch size, and the usual sweet spot for a transactional database is somewhere between a hundred and a thousand rows per batch, where the statement is still small enough to plan quickly but large enough to amortise the overhead.

For a much larger load, the bulk-load path is better than INSERT altogether. Every major engine has one: PostgreSQL's COPY, MySQL's LOAD DATA INFILE, and SQLite's .import dot command or the sqlite3 shell's own import. These bypass the normal row-processing path, do not fire per-row triggers in the same way, and can be an order of magnitude faster than INSERT.

Two practical notes. Keep the batch size bounded so that a failed batch does not lose too much work and so the statement does not blow past the maximum packet size on a network connection. And if you are inserting from application code, use prepared statements with parameter placeholders rather than building the SQL by concatenation — that is both safe against injection and much faster, because the statement is parsed once and reused.

The cost is not the row. It is everything that happens around the row. Each single-row INSERT is a separate statement, and in autocommit mode each one is also a separate transaction — which means a separate durable commit, a log flush and a round trip to the client. Ten thousand rows in a loop is ten thousand commits.

On a networked connection the latency dominates entirely. If each round trip costs a millisecond, ten thousand individual inserts spend ten seconds waiting for the network, and the database spends most of that time idle. Batching a thousand rows per statement cuts the round trips by a factor of a thousand. Wrapping the batch in a transaction removes the per-statement commit cost as well.

There is a second, quieter cost: parsing and planning. Each statement is parsed, optimised and prepared before it runs. A loop that rebuilds the same SQL text every time forces that work every time; a prepared statement reuses the plan. And if the application concatenates values into the SQL string, the statement text changes on every iteration, which defeats prepared statements entirely and also opens you up to SQL injection.

What to do, in order. Turn off autocommit and wrap the load in one transaction, or in batches of a few thousand rows if the load is large enough that one transaction would hold locks for too long. Use a multi-row INSERT or the engine's bulk-load command. Use prepared statements with real parameter placeholders. And if the data is being copied from another table, use INSERT ... SELECT rather than pulling it into the application and pushing it back row by row — that keeps the whole thing inside the database, where the data never has to be serialised at all.

One thing to watch when you disable autocommit: a long transaction holds locks and produces a long undo log, and in PostgreSQL it blocks vacuum. For a load that takes minutes, batch the transaction rather than opening one for the whole job.