You split a long batch job into chunks so a crash costs one chunk instead of the whole night, and the job now takes many times longer. If the real cost is a scan that every chunk repeats, chunking didn’t divide the work. It multiplied it.
Table of Contents
The instinct, and the trap
A long job feels risky, so you reach for chunking: split the work into pieces, process each, checkpoint as you go. If it dies, you’ve lost one chunk, not the whole run. Most of the time that instinct is right.
It pays off only if a chunk is genuinely cheaper than the whole. That holds when cost scales with the items you’re processing. It fails badly when the dominant cost is a fixed scan that every chunk has to repeat. Then one scan becomes N scans, and you’ve made the job slower and harder to follow in the name of saving it.
Chunking divides per-item work. It multiplies fixed work. Know which one dominates before you split.
So the question before chunking is not “how do I split the items?” but “what actually costs the time: the items, or the scan underneath them?”
A worked example
Picture a nightly export that attaches product details to a batch of records before writing them out, for a catalog spread across several shops. The worry is legitimate: the job writes all of its output at the very end, so a crash an hour in produces nothing. Chunking to save partial output looks obvious. Split the records into groups, process each group, write as you go.
Read the query first, though. The collector does something like this:
SELECT ...
FROM items i
JOIN item_details d USING (item_id)
WHERE i.store_id = ANY (:stores);
Code language: SQL (Structured Query Language) (sql)It scans every item in the shops involved and matches what it needs in code, however many target records you asked for. The number of output records never touches the size of that scan, and EXPLAIN on the statement shows it: the plan reads the whole table, and the result count never enters its cost.
Measuring, rather than assuming, settles it. In one real run of a job shaped like this:
- 150 records took ~26 minutes.
- 2,930 records took ~38 minutes.
Nineteen times the records, and barely more time. The cost is the scan underneath (hundreds of thousands of rows), and that scan is fixed. Next to it, the per-record work is noise.
Now hold the chunking plan up against those numbers. Splitting by records into ~20 chunks of 150 means 20 full scans. Twenty scans at ~26 minutes each is roughly nine hours (projected), to make durable a job that runs in ~38 minutes whole. The “safety” feature would have been a 14× slowdown.
Why the instinct misfires
The chunking instinct is trained on per-item work: parse a million rows, call an API per record, resize a folder of images. There, a chunk really is a fraction of the job, and checkpointing between chunks is close to free. For that shape of job, the training is correct.
A scan-bound job breaks the assumption silently, because nothing about the code looks different. You still have “items” to loop over. What’s hidden is that the expensive thing sits outside the loop: it’s the one query that fills it, and that query costs the same whether you ask for a hundred items or all of them. The loop you can see is cheap. The scan you forgot to account for is the bill.
If a job’s runtime barely grows when you feed it 19× the items, the items aren’t what it’s spending on.
The fix: chunk along the scan, not the output
If you do need to chunk for resumability, split along the column that partitions the scanned data, so each chunk reads a disjoint slice of it. The output axis re-reads the same data every time.
In the example that column is store. The scan already filters by store_id, so processing one shop at a time reads each shop’s items exactly once. Together the chunks perform the same total scan as the combined run, and each shop’s output is written the moment its slice finishes. You get durable partial output at shop granularity, almost for free:
- Chunk by record (the output): N chunks → N× the scan.
- Chunk by store (the scan’s own partition key): N chunks → 1× the scan, with N checkpoints.
Same resumability, opposite cost. All that changes is picking the split the scan was already divided along, instead of the one the output happened to suggest. It’s the habit behind profiling before you blame the obvious suspect: the slow part is rarely the part you were looking at.
Sometimes the right answer is no chunking at all. In that same run, the whole multi-phase job finished in ~74 minutes start to finish, because every phase was scan-bound. Nine projected hours had been on the table for a checkpoint the job turned out not to need.
Estimating a scan-bound job
The same fixed-scan shape wrecks estimates, in the opposite direction. Measure a small sample (“150 records in 26 minutes”) and the natural extrapolation is linear: thousands of records must take hours. For a scan-bound job that’s badly wrong, because the sample already paid almost the entire fixed cost. A better estimate comes from measuring the scan once and treating the item count as a rounding error on top of it.
A scan-bound job barely grows with item count, so a small sample over-predicts the whole rather than under-predicting it.
It’s the cost-model mistake behind billing by the wrong unit: the number that drives the total isn’t the number you were counting. Find the unit the cost actually scales with (here, scans, not records) and both your estimate and your chunking strategy follow from it.
The short version
- Chunking helps only when a chunk does less work than the whole.
- Test for a fixed scan first. Run a small sample and a large one. If runtime barely moves (150 records in ~26 min, 2,930 in ~38 min), the items aren’t the cost.
- Chunk along the scanned data’s partition key (store, date range, shard key) so the chunks together read it once, with a checkpoint per chunk. Never chunk along the output, which re-scans every time.
- Estimate from the fixed scan, not a linear extrapolation of a sample, or you’ll predict hours for a job that takes minutes.
Frequently Asked Questions
How do I tell if my job is scan-bound before I chunk it?
Run it over a small sample and over a much larger one, and compare wall-clock time. If 19× the items costs barely more time, the cost is a fixed scan, and chunking by item count will multiply that scan. If time scales roughly with item count, the job is per-item and ordinary chunking is safe. EXPLAIN on the main query confirms it: a sequential scan of a large table whose cost ignores your batch size.
Isn’t chunking still worth it just for resumability, even if it’s slower?
Only if “slower” means a few percent. In the measured case, chunking by the output axis would have turned a ~38-minute job into a projected ~9 hours, and nobody should trade a 14× slowdown for a checkpoint. Chunk along the scan’s own partition key instead and you get the resumability and the single scan.
What if my job has no natural partition key?
Then chunking can’t avoid the re-scan, so weigh the alternatives. Make the job write its output incrementally as it goes, so a crash keeps what was finished. Or cut the scan itself, with an index or a narrower WHERE clause, so a repeat costs less. Often the simplest answer is to run it whole and rerun on failure, since one rerun of a 38-minute job is still cheaper than twenty scans.
