One statement instead of ten thousand
Saving records in a loop is the slowest correct way to write data. The alternatives are faster and quietly skip things you may be relying on.
Importing ten thousand records by calling create! in a loop issues ten thousand
inserts, each a round trip, each with callbacks and validations. It works and it is slow
in a way that grows linearly with your success.
The bulk methods
insert_all writes many rows in a single statement. upsert_all does the same with
conflict handling — update on duplicate key rather than fail.
The speed difference is not incremental. One statement with ten thousand rows against ten thousand statements is a different order of magnitude, mostly because round trips dominate.
What they skip, and why that matters
They bypass Active Record entirely. No validations, no callbacks, no timestamps unless you supply them.
That is the source of the speed and the source of the danger. If your slug generation, normalisation or default-setting lives in a callback, bulk-inserted rows arrive without it — and you now have two shapes of data in one table, distinguishable only by how they were created.
The rule I follow: anything the data must always satisfy belongs in the database as a
constraint, not only in Ruby. A NOT NULL, a check constraint or a unique index holds
no matter which path wrote the row. A before_save holds only for the paths that went
through Active Record.
Bulk writes are a test of whether your invariants live in the database or merely in your models.
Batching, because one statement can be too big
A single statement with a million rows will exhaust memory, exceed parameter limits, or hold a lock long enough to matter.
Batches of a few thousand are usually the sweet spot: large enough that round trips stop dominating, small enough that each statement is quick and the transaction stays short.
Wrapping the whole import in one transaction sounds appealing and is usually wrong — a transaction open for minutes blocks vacuum, holds locks and turns any failure into a total rollback. Batch-sized transactions with resumability beat one heroic transaction.
Updates too
The same applies to updating. A loop calling update! per record is the same pattern.
update_all issues one statement for the whole scope — with the same caveat that it
skips callbacks and validations.
For backfills specifically, the shape that works is: select a batch of ids, update that batch, record progress, repeat. Resumable, interruptible, and it never holds a lock long enough to be noticed.