Skip to content
Prompt Words
History

Bulk write

Also called: bulk insert, batch insert, multi-row insert.

Saving many rows with one database statement or one transaction, instead of one INSERT and one commit per row. Each round trip and each commit has a fixed cost, so 500 single inserts spend most of their time waiting. One multi-row INSERT, or COPY, pays that cost once.

Save 500 contacts: one INSERT per row, or one INSERT with all 500 rows. Compare the round trips and commits.

One by one
Round trips–
Commits–
Time–
One batch
Round trips–
Commits–
Time–

Nothing saved yet. 500 contacts are waiting to be imported.

    Idle

    Say it in a prompt

    The contacts import saves 500 rows with one INSERT each. Validate every row first and report the bad ones by line number. Then insert the valid rows in batches of 500: one multi-row INSERT INTO contacts (name, email) VALUES (...), (...) per batch inside one transaction, or COPY for files over 10,000 rows. If a batch still fails, retry it in smaller batches to find the row that breaks it.

    Vague vs precise prompt

    Vague prompt

    the CSV import is slow, make it faster

    Typical resultRuns the import in a background job with a progress bar. It still sends one INSERT per row, so 500 rows still take 500 round trips and 500 commits.

    Precise prompt

    Validate the CSV rows first and report invalid ones by line number. Insert the valid rows in batches of 500 with one multi-row INSERT per batch inside one transaction (COPY for files over 10,000 rows). If a batch fails anyway, retry it in smaller batches to find the bad row.

    Typical result500 rows go in 1 round trip and 1 commit, about 40 ms instead of 2.5 s. Bad rows are reported by line number before anything is saved.

    Seen on

    • PostgreSQL documentation: Populating a Database: committing each insert separately makes PostgreSQL do a lot of work per row, so turn off autocommit and commit once, or use COPY to load all rows in one command.
    • MySQL Reference Manual: Optimizing INSERT Statements: an INSERT with many VALUES lists is considerably faster, many times in some cases, than separate single-row INSERTs.

    You might describe it as

    • load all the rows with one INSERT or COPY
    • the import takes forever because it saves one row at a time
    • one big insert instead of hundreds of small ones

    Not to be confused with

    • Batch endpoint

      A batch endpoint joins many HTTP calls into one; a bulk write joins many database writes into one statement.

    • N+1 query

      N+1 is many small reads; a bulk write fixes many small writes.