Why is a multi-row INSERT ... VALUES faster than 50,000 single-row INSERT statements?

## Where the time actually goes Loading 50,000 rows with 50,000 statements spends most of its wall-clock time not inserting. Each statement is sent to the server, and the client waits for the response before sending the next. That wait is network latency, and it is paid 50,000 times. On a 1 ms link that is 50 seconds; over a slower network link it can be many minutes. Add per-statement parsing and, in autocommit mode, a durable commit per row. A multi-row `INSERT` collapses those per-statement costs: ```sql INSERT INTO measurements (sensor_id, taken_at, value) VALUES (1, TIMESTAMP '2024-05-01 10:00:00', 21.4), (1, TIMESTAMP '2024-05-01 10:01:00', 21.6), (2, TIMESTAMP '2024-05-01 10:00:00', 18.9); ``` One statement, one round trip, one parse, one execution, one commit. Fifty statements of 1,000 rows each replace 50,000 statements, and the round-trip bill drops by three orders of magnitude. ## What batching does not speed up The rows still have to be written, and every index on the table still has to be maintained for every row. Constraint checks still run for every row. If a load is slow because the table carries many indexes, batching the statements will not fix that — batching removes *statement* overhead, not *row* work. Being explicit about this distinction is what separates a candidate who has reasoned about it from one repeating advice. ## Choosing a batch size Bigger batches keep saving round trips, but with diminishing returns and rising costs: - **Statement limits.** Engines cap statement text size and the number of bind parameters a statement may carry. A 50,000-row batch with five columns is 250,000 parameters and will run into a limit somewhere. - **Memory.** The whole statement must be received, parsed and held server-side before execution. - **Failure blast radius.** A single SQL statement is atomic: if row 4,712 violates a unique or check constraint, the entire statement fails and none of its rows are inserted. With 1,000-row batches you retry 1,000 rows; with a 50,000-row batch you retry everything, and finding the offending row is harder. - **Transaction length.** Batches are usually committed per batch, which keeps each unit of work short. A few hundred to a few thousand rows per statement is the usual working range; the honest answer is to measure on your own connection and schema, because the optimum depends on latency and row width. ## The prepared-statement alternative Many client drivers can execute one prepared statement against many parameter sets in a single exchange with the server. That achieves the same round-trip saving with a single, unchanging statement text — which is often preferable, because a multi-row `VALUES` list produces a *different* statement text for every distinct row count. Both approaches are batching; they differ in how the rows travel, not in the principle. ## INSERT ... SELECT: batching without a client at all When the rows already exist inside the database, do not fetch and re-insert them. `INSERT INTO target (a, b) SELECT a, b FROM source WHERE ...` moves the data entirely server-side: no rows cross the network in either direction and there is exactly one statement, regardless of how many rows qualify. This is the fastest form of batching available, and it is what you should reach for whenever the source of the rows is a query rather than the application. ## Partial failure and retry Because each statement is atomic, a load built from batches needs to be re-runnable. Two habits make that easy: make each batch's rows identifiable (so a retry knows what to redo), and design the target so a repeated insert conflicts loudly rather than silently duplicating — a unique key on the natural identifier. Engines offer opt-in variants that skip or merge conflicting rows, but those are dialect-specific extensions; the portable base is "the statement inserts all its rows or none of them". ## The summary sentence Batching converts N statements into N/k statements. It removes latency and per-statement overhead, leaves per-row storage work untouched, and trades a larger blast radius on failure for the saving — which is why the batch size is a tuning decision rather than "as large as possible".