In Active Record, why use find_each instead of Member.all.each to re-score a million loyalty members, and how does it page through the table?
answer
- bounded memory, many queries
- batch_size defaults to 1000
- ORDER BY id with id > last
- scoped order is ignored
- find_in_batches yields Arrays
basics
~20 sMember.all.each loads every row into memory before the loop starts; find_each loads 1,000 members at a time in primary-key order, continuing with WHERE id > the last id seen, so memory stays flat however large the table grows.
solid answer
~40 s`Member.all.each` materialises the whole relation, a million `Member` objects, before yielding the first one. `find_each` runs `ORDER BY id LIMIT 1000`, yields those records one by one, then asks for the next 1,000 with `id >` the last id it saw, until a batch comes back short. Memory is bounded by `batch_size` (default 1000), at the cost of one query per batch. It works on any relation, `Member.where(active: true).find_each`, but it forces its own order: a scoped `order` is ignored with a logged warning, or raises `ArgumentError` when `config.active_record.error_on_ignored_order` is true. `find_in_batches` uses the same walk and yields each batch as an `Array`, which suits bulk work such as building one API request per batch. Both skip the query cache, so finished batches are not kept around.
code
ruby · 12 lines# Loads every active member before the first iteration
Member.where(active: true).each { |m| m.update!(loyalty_score: LoyaltyScore.for(m)) }
# 1,000 members in memory at a time, walked by id
Member.where(active: true).find_each do |member|
member.update!(loyalty_score: LoyaltyScore.for(member))
end
# One Array per batch, for per-batch work
Member.where(active: true).find_in_batches(batch_size: 500) do |members|
RewardsApi.sync(members.map(&:reward_payload))
endgo deeper
Recall that all.each loads everything, that find_each loads 1,000 at a time by primary key, and that find_in_batches yields Arrays.
Explain the id > last_id walk, why a scoped order is ignored or raises, and why batches skip the query cache.
Show that batching bounds memory, not write count, plan resumption with start:, and know that batches see no single snapshot of a live table.
Decide when a backfill belongs in a resumable job, a batched SQL statement or a one-off script, weighing memory, run time and restart cost.
## The problem with all.each `Member.all.each` reads like a stream but is not one. Iterating an `ActiveRecord::Relation` **loads** it: Active Record runs one `SELECT`, instantiates every row as a model and keeps the whole `Array` until the loop ends. For a million loyalty members that is a million objects, each with its attribute set, alive at once. The process grows by hundreds of megabytes or more, the garbage collector works harder, and a container with a memory limit may be killed halfway through. ## How find_each walks the table `find_each` replaces one huge read with many small ones. By default it: 1. Runs the relation with `ORDER BY members.id ASC LIMIT 1000`. 2. Yields each of those records to the block, one at a time. 3. Remembers the **last id** of the batch and runs the next query with `members.id > last_id`. 4. Stops when a batch returns fewer rows than the batch size. ```sql SELECT members.* FROM members WHERE members.active = TRUE ORDER BY members.id ASC LIMIT 1000 SELECT members.* FROM members WHERE members.active = TRUE AND members.id > 1874 ORDER BY members.id ASC LIMIT 1000 ``` Because each query starts **after the last id** instead of using `OFFSET`, a late batch costs about as much as an early one when the cursor column is indexed. Rows from a finished batch are no longer referenced by Active Record and can be garbage-collected, and the batch queries **skip the query cache**, which would otherwise keep every batch in memory for the rest of the request. ## The rules that come with it - **Batch size**: `batch_size:` defaults to **1000**. Larger batches mean fewer queries and more memory per batch. - **Order is forced**: the walk needs its own `ORDER BY`. A relation that already has one, `Member.order(:name).find_each`, has that order **ignored**, and Rails logs "Scoped order is ignored, use :cursor with :order to configure custom order." With `config.active_record.error_on_ignored_order = true` (default `false`), or `error_on_ignore: true` on the call, it raises `ArgumentError` instead. - **Limits are honoured**: `Member.limit(5_000).find_each` stops after 5,000 rows. - **No block, an Enumerator**: `Member.find_each.with_index { |member, i| ... }` works. - **Not a database cursor**: Active Record runs ordinary `LIMIT` queries; it does not hold a server-side cursor or a transaction open across batches. ## find_each versus find_in_batches | Method | Yields | Use it for | |---|---|---| | `find_each` | one record at a time | per-record work: recalculating a score, sending a mailer | | `find_in_batches` | an `Array` of up to `batch_size` records | per-batch work: one bulk API call, one `upsert_all` per batch | | `Member.all.each` | one record at a time, from a fully loaded `Array` | small sets that fit comfortably in memory | The guide's advice holds: for a loop over a few hundred records the ordinary finders are simpler, and batching only pays off when the set would not fit in memory. ## Re-scoring a million members ```ruby Member.where(active: true).find_each(batch_size: 2_000) do |member| member.update!(loyalty_score: LoyaltyScore.for(member)) end ``` Each `update!` still runs validations and callbacks and writes one row, so this is a **memory** fix, not a speed fix: a million members still means a million `UPDATE`s. When the new score can be computed in SQL, `in_batches` with `update_all` writes a whole batch per statement. Associations read inside the block need `includes` on the relation, which is applied to each batch, or the loop turns into an N+1 per member. ## Inside the block - Each `update!` runs in its **own transaction**; find_each does not wrap the loop in one, so a crash at member 412,000 leaves the first 411,999 changes committed. - An exception raised in the block **stops the walk**. Rescue and record per-record failures if one bad member should not halt the run. - Keep the block's work proportional: a network call per member turns a memory problem into a run-time one, which is when `find_in_batches` and one call per batch pay off. ## What batching does not give you Batches are separate queries, so they do **not** see one consistent snapshot: rows inserted behind the cursor are missed, and rows inserted ahead of it are picked up. A job that dies halfway must be restarted with `start:` set to the last id it finished, or it begins again from the first row.
- Why does find_each continue with id > last_id instead of OFFSET?An `OFFSET` makes the database read and discard every earlier row, so batch 900 costs far more than batch 1. Continuing after the last id uses the primary-key index to jump straight to the next batch, and rows deleted between batches do not shift later rows out of view.
- How do you resume a find_each job that crashed after member 412,000?Pass `start: 412_001`; `start:` is inclusive, so starting at the last finished id would repeat that one row. Record the last processed id somewhere durable as the loop runs, or make the per-record work safe to repeat so an overlap does no harm.
Unloading a delivery truck one pallet at a time instead of tipping the whole load onto the shop floor: the floor never fills up, but you make many more trips to the truck.
- Member.all.each streams rows from the database one at a time.
- find_each keeps the scope's order(:name) and batches in name order.
- find_each pages with OFFSET, so late batches cost the same as early ones.
- find_each holds a server-side cursor and a transaction open across batches.
- find_each makes per-record updates fast because it batches the writes too.