Chunking Address Batches for Optimal Throughput

TL;DR: Chunk size is bounded from below by per-chunk overhead, from above by memory, and from the side by straggler tolerance — so pick a size in the broad plateau between them and create several times more chunks than workers. Commit per chunk and the job becomes restartable for free. The surrounding pipeline is optimizing batch geocoding throughput.

Three Bounds, One Plateau

Chunk size is not a single optimum but a range, and the range is defined by three constraints that push in different directions.

What bounds a chunk size from each side Three constraints on a size axis. Per-chunk overhead makes very small chunks wasteful. Memory makes very large chunks impossible. Straggler tolerance makes large chunks costly at the end of a run. Between them lies a broad plateau where any value performs comparably. workable plateau 1 000 – 50 000 rows on most pipelines per-chunk overhead serialisation, scheduling, transaction cost per chunk memory + stragglers a large chunk holds more rows and delays the whole run at the tail smaller chunks larger chunks Anywhere in the shaded band performs within a few percent; the edges are where the trouble is.

The plateau is wide, which is the useful news: precision is unnecessary and the common mistake is landing outside the band entirely — a chunk of ten, or a chunk that is the whole file.

The Streaming Implementation

from __future__ import annotations

from typing import Iterable, Iterator, TypeVar

import pandas as pd

T = TypeVar("T")


def chunked(rows: Iterable[T], size: int) -> Iterator[list[T]]:
    """Yield fixed-size lists from any iterable, without materialising it."""
    buf: list[T] = []
    for row in rows:
        buf.append(row)
        if len(buf) >= size:
            yield buf
            buf = []
    if buf:
        yield buf


def stream_csv(path: str, size: int = 20_000) -> Iterator[pd.DataFrame]:
    """Read a large CSV in bounded frames, preserving string dtypes."""
    yield from pd.read_csv(path, chunksize=size, dtype=str, keep_default_na=False)

dtype=str and keep_default_na=False are not incidental. Address data contains postcodes with leading zeros and literal values such as NA (a real Canadian street abbreviation and a US state code fragment), both of which pandas will happily convert into something else given the chance.

More Chunks Than Workers

The scheduling property that matters at the end of a run is how many units remain when workers start to idle. With as many chunks as workers, one slow chunk leaves everyone else waiting; with several times more, the tail shrinks to a single chunk.

Straggler cost with few chunks and with many Two schedules across four workers. With one chunk each, a slow chunk leaves three workers idle for most of its duration. With four smaller chunks each, work is redistributed and the idle tail is reduced to the length of one small chunk. 4 chunks, 4 workers 3 workers idle 16 chunks, 4 workers tail is one short chunk

Four to eight chunks per worker is the usual rule of thumb, and it interacts with the size bounds above: if that ratio forces a chunk below the overhead floor, the answer is fewer workers rather than smaller chunks.

Chunk Boundaries Make a Job Restartable

Committing results and recording progress at each chunk boundary converts a fragile long-running job into a resumable one, at almost no cost.

The mechanism is a single row per run recording the last completed chunk offset. On restart, the job seeks past it and continues. What that buys is significant: a run that fails at eighty percent resumes rather than repeating, and — critically for a metered pipeline — it does not re-pay for the provider calls the first eighty percent already made.

It also changes the operational posture around deployments. A job that can be stopped at a chunk boundary and resumed can be interrupted deliberately, which means a batch no longer blocks a release, and a release no longer risks a batch.

The one requirement is that writes must be idempotent, since the last chunk before a crash may have partially committed. The upsert pattern described in Postgres materialized view geocode cache provides exactly that property, which is why the two designs fit together so naturally.

Chunking Interacts With Deduplication

There is one subtlety worth stating explicitly: deduplication is only as effective as the window it can see. Collapsing duplicates within a chunk catches far fewer than collapsing across the whole run, because a customer’s two orders are unlikely to land in the same twenty-thousand-row slice.

The resolution is to deduplicate on the canonical key across the run rather than per chunk — which is possible without holding the file in memory, because the cache lookup does the same job. A second occurrence of an address in a later chunk finds the result the first occurrence wrote, so the provider is called once regardless of how the rows were divided.

That is worth knowing when tuning: chunk size affects memory and scheduling, and it should not affect the number of provider calls at all. If it does, the deduplication is happening inside the chunk rather than against shared state, and the cost of a smaller chunk is higher than it looks.

Memory Stays Flat, Whatever the Input

Chunked memory against input size Two lines plotted against input size. The load-everything approach rises linearly and crosses the container limit. The chunked approach stays flat at a few hundred megabytes regardless of whether the input is fifty thousand rows or fifty million. container limit load the whole file chunked stream 50 K rows 50 M rows

Edge Cases and Failure Modes

Chunk size below the overhead floor. With chunks of a hundred rows, the per-chunk transaction and scheduling cost can exceed the work. The symptom is CPU time in the driver rather than in the workers.

A chunk larger than a transaction should be. Very large commits hold locks and inflate rollback cost. Ten to fifty thousand rows per transaction is comfortable for most databases.

Row-order dependence. A chunked job must produce the same result regardless of how rows are divided. Any logic that depends on the previous row — a running total, a de-dup against the immediately preceding record — breaks at chunk boundaries.

Unbounded task creation inside a chunk. Chunking the read does not bound concurrency; creating a coroutine per row within a chunk of fifty thousand is still fifty thousand objects. The semaphore bounds execution, not allocation.

Choosing the Number in Practice

Rather than searching for an optimum, pick a value from the three constraints and verify it once.

Start from memory: divide the memory you are willing to give one worker by the per-row cost, including the response payloads held while a chunk is in flight. On a typical geocoding worker that lands somewhere between twenty and a hundred thousand rows, which is already inside the plateau.

Then check the chunk count. Divide the expected input size by the candidate chunk size and compare against the worker count; if the result is fewer than about four chunks per worker, reduce the chunk size until it is not. For a small input that may mean chunks well below the memory bound, which is fine — the memory bound is a ceiling, not a target.

Finally, sanity-check the transaction. A chunk is also a commit, and a commit of half a million rows is a different kind of problem from a commit of twenty thousand. Where the two concerns disagree, split them: process in large chunks and commit in smaller batches inside them.

Verify by running one chunk and recording peak memory and duration. Those two numbers confirm the arithmetic in a couple of minutes, and they are worth writing into a comment beside the constant so the next person can check whether the reasoning still holds.

Integration Note

Chunking is the outermost loop of the pipeline described in optimizing batch geocoding throughput: read a chunk, deduplicate onto canonical keys, resolve the cache, dispatch the misses, write back, record the offset. Every stage inside it operates on a bounded amount of data, which is what makes the memory profile flat and the runtime predictable regardless of whether the input is fifty thousand rows or fifty million.

Recording the chunk size alongside the run metrics is worth the extra column too: when a future run behaves differently, knowing whether the chunking changed is the first question and the cheapest one to answer.

Chunking and Observability

A chunked job is also a job with natural reporting boundaries, and that turns out to be worth as much as the memory property.

Emitting a progress line per chunk — offset, rows, duration, cache hit rate — gives a run a readable shape without any additional instrumentation. A batch that is slowing down shows it as rising per-chunk duration long before the total runtime becomes alarming, and a chunk that behaves unlike its neighbours is immediately identifiable rather than being averaged into the whole.

Those same lines are what make a long-running job diagnosable while it is still running. Without them the only observable is “started” and “finished”, and the question “is it stuck or merely slow?” has no answer short of attaching a debugger to a production process.