Loading Data in Bulk

Batch the rows, wrap the batches in transactions, and use prepared statements. A multi-row INSERT with a few hundred rows per statement, inside an explicit transaction, is the practical sweet spot for a transactional database.

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  source VARCHAR(40) NOT NULL,
  payload VARCHAR(200) NOT NULL,
  received_at TEXT NOT NULL DEFAULT (datetime('now'))
);

BEGIN;

INSERT INTO events (source, payload) VALUES
  ('web', 'signup'),
  ('web', 'login'),
  ('app', 'purchase'),
  ('app', 'purchase');

INSERT INTO events (source, payload) VALUES
  ('batch', 'import-1'),
  ('batch', 'import-2');

COMMIT;

SELECT COUNT(*) AS total_events FROM events;
SELECT source, COUNT(*) AS events FROM events GROUP BY source ORDER BY source;

Six events were loaded, and the second query shows two from each source: app, batch and web.

Why batching wins is about the number of round trips and the number of commits. Six separate INSERT statements in autocommit mode means six transactions and six durable commits. Two statements inside one transaction means one commit and, over a network connection, one round trip. The saving scales with the batch size, which is why the right answer to "how do I load a million rows" is never a loop of single inserts.

Batch size is a trade-off rather than a maximum. Larger batches amortise the per-statement overhead better, but a statement that is too large takes longer to plan, can exceed the maximum packet size on a network connection, and makes a failed batch lose more work. Between a hundred and a thousand rows per batch is the usual range, and the right number for your system is worth measuring rather than guessing.

Prepared statements matter more than people expect. A prepared statement is parsed and planned once and then executed with different parameter values, and it keeps values out of the SQL text entirely, which removes the injection risk. If you build the SQL by concatenating values, the statement text changes on every iteration and the plan cache never hits — so string concatenation costs you both safety and speed.

And for a really large load, use the engine's bulk-load path instead of INSERT. PostgreSQL's COPY, MySQL's LOAD DATA INFILE and SQLite's .import command bypass much of the per-row processing and are dramatically faster. The trade-off is that they usually disable triggers and require the data to be well-formed, so they are a data-loading tool rather than an application write path.

There are three strategies and they sit on a spectrum between speed and safety.

Row-at-a-time INSERT in autocommit. The simplest and the slowest. Every row is its own transaction with its own commit and its own round trip. It is also the safest in one narrow sense: each row either lands or does not, so a failure halfway through leaves a partial load that you can resume from. That property is why it survives in some migration scripts — a row-at-a-time load that fails is resumable.

Batched multi-row INSERT inside a transaction. The balanced option. One statement per batch, one commit per batch, prepared statements for speed and safety. A failed batch rolls back as a unit, so you lose at most one batch of work and you know exactly where to resume. This is the default choice for application code and for migration scripts.

The engine's bulk-load path. COPY in PostgreSQL, LOAD DATA INFILE in MySQL, .import in SQLite. It is the fastest by a wide margin because it bypasses the normal row-processing path, fires fewer triggers and often skips constraint checks during the load. The cost is control: the data usually has to be clean before it goes in, triggers and foreign keys may be disabled for the duration, and the load is not transactional in the same way, so a failure can leave a half-loaded table.

There is a fourth option worth mentioning, and it is the right one for very large volumes: load into a staging table with no constraints, validate and transform the data with SQL, and then move it into the real table with a single INSERT ... SELECT. That gives you a place to check the data before it becomes visible, and it turns the transformation into set-based work rather than application-code work. The downside is the disk space and the time for the copy.

Whatever the strategy, the surrounding discipline is the same: disable unique indexes and non-essential triggers before the load if the engine allows it, load in batches, commit per batch, and validate the row counts and checksums afterwards against the source. And in every case, keep the load out of the hours when the table is being read by real traffic, or accept that the lock it takes will show up as latency for those users.