Learn AI Create the Future — with Pranay
Main site

Batching and Concurrency

Offline batch scoring

Offline batch scoring runs a model over a huge pile of rows on a schedule, with nobody waiting for an answer, which makes it far simpler and cheaper than serving requests live.

Read these first

On this page 8
  1. The short answer
  2. The analogy you have already lived
  3. Why it exists
  4. How it works
  5. A real example you have seen
  6. The honest part
  7. Remember this
  8. What to learn next

One lesson, three depths. Pick the one that fits you today — you can switch any time.

Beginner — No maths. Plain English.

The short answer

Offline batch scoring means running your model over a huge pile of rows at once, usually overnight, with nobody waiting for a single answer.

The analogy you have already lived

You have done a big laundry load. You stuff the machine full and run one cycle, instead of washing each shirt separately, one at a time, all day. One load takes longer than one shirt would. But washing everything together, once, uses far less water, time and effort than repeating the whole process for every single item.

Batch scoring is that same choice, applied to a model instead of a washing machine.

Why it exists

Model serving answers one question the instant it is asked, because someone is waiting right then. That is the right shape for a fraud check during a payment.

Plenty of real work has no one waiting at all. Score every customer for tomorrow's offer emails. Re-rank every product in a catalogue overnight. Flag every transaction from yesterday for a morning review. Nobody is standing at a counter for any of these. The only requirement is that the whole pile gets done by some deadline, often the next morning.

Building a live, always-on service for work like this is unnecessary work. A scheduled job that runs once a day, scores everything, and writes the results down is simpler and cheaper. There is nothing to keep on call at 2 a.m.

How it works

Online serving (someone is waiting right now):
   one request  -->  model  -->  one answer, in milliseconds

Offline batch scoring (nobody is waiting):
   ALL the rows  -->  model, in large chunks  -->  ALL the answers,
                                                     written to a table,
                                                     read whenever needed

The key difference is not the model. It is when the answer is needed. Batch scoring trades "instantly" for "by the next scheduled run," and in exchange gets to process everything together, far more efficiently.

A real example you have seen

The personalised recommendations on a shopping app's home screen were very likely computed overnight for every user, not calculated fresh the second you opened the app. The recommendation was ready and waiting before you ever asked for it.

The honest part

Batch scoring has a hidden cost: staleness. The scores are only as fresh as the last run. If someone's situation changes right after the nightly job runs, the system will not know until tomorrow. Whether that is acceptable depends entirely on what the score is used for — fine for a marketing email, not fine for a fraud check.

Remember this

  • Batch scoring works on a whole pile of rows at once, on a schedule, with nobody waiting.
  • It is simpler and cheaper than live serving whenever a delayed answer is genuinely acceptable.
  • The trade-off is staleness — the answer is only as current as the last time the job ran.

What to learn next

Developer — Code and libraries.

Setup

No installation needed — this uses Python's built-in sqlite3 module only.

The mistake, and the fix, in one script

offline_batch_scoring.py
import sqlite3
import time


def build_database(path, n_rows):
    conn = sqlite3.connect(path)
    conn.execute("DROP TABLE IF EXISTS customers")
    conn.execute("""
        CREATE TABLE customers (
            id INTEGER PRIMARY KEY,
            income REAL, years REAL, age REAL,
            score REAL
        )
    """)
    rows = [(i, 10 + (i % 70), (i % 10) + 0.5, 20 + (i % 45), None) for i in range(n_rows)]
    conn.executemany("INSERT INTO customers VALUES (?, ?, ?, ?, ?)", rows)
    conn.commit()
    conn.close()


def score(income, years, age):
    """Stands in for a real model's .predict() -- pure arithmetic, deterministic."""
    return round(0.01 * income + 0.05 * years - 0.002 * age, 4)


def score_row_by_row(path):
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    start = time.perf_counter()
    for row_id, income, years, age in conn.execute("SELECT id, income, years, age FROM customers"):
        cur.execute("UPDATE customers SET score = ? WHERE id = ?", (score(income, years, age), row_id))
        conn.commit()   # one disk write confirmed per row -- the expensive part
    elapsed = time.perf_counter() - start
    conn.close()
    return elapsed


def score_in_chunks(path, chunk_size=500):
    conn = sqlite3.connect(path)
    cur = conn.cursor()
    read_cur = conn.execute("SELECT id, income, years, age FROM customers")
    start = time.perf_counter()
    while True:
        chunk = read_cur.fetchmany(chunk_size)   # bounded memory: never load the whole table
        if not chunk:
            break
        updates = [(score(income, years, age), row_id) for row_id, income, years, age in chunk]
        cur.executemany("UPDATE customers SET score = ? WHERE id = ?", updates)
        conn.commit()   # one disk write confirmed per chunk, not per row
    elapsed = time.perf_counter() - start
    conn.close()
    return elapsed


if __name__ == "__main__":
    N_ROWS = 1200

    build_database("scoring_a.sqlite", N_ROWS)
    row_by_row_time = score_row_by_row("scoring_a.sqlite")

    build_database("scoring_b.sqlite", N_ROWS)
    chunked_time = score_in_chunks("scoring_b.sqlite", chunk_size=500)

    print(f"{N_ROWS} rows, row-by-row commit: {row_by_row_time:6.2f}s "
          f"({N_ROWS/row_by_row_time:8.0f} rows/s)")
    print(f"{N_ROWS} rows, 500-row chunks:    {chunked_time:6.2f}s "
          f"({N_ROWS/chunked_time:8.0f} rows/s)")
    print(f"speedup: {row_by_row_time/chunked_time:.0f}x")
Output
1200 rows, row-by-row commit:   2.25s (     534 rows/s)
1200 rows, 500-row chunks:      0.01s ( 150036 rows/s)
speedup: 281x

These are real timings from this machine's disk and this exact script. The row-by-row number is heavily dependent on your storage hardware, since it is dominated by how fast your disk confirms a write. An SSD, a spinning disk, and a network drive all behave very differently here. The pattern — chunked commits being dramatically faster than one commit per row — holds everywhere. The specific 281x will not.

Line-by-line walkthrough

score_row_by_row calls conn.commit() inside the loop, once per row. Every commit() on SQLite waits for the write to be confirmed safe on disk before returning — genuinely useful for correctness, genuinely expensive to pay 1200 separate times.

score_in_chunks uses fetchmany(chunk_size) instead of loading every row into memory at once, and calls executemany plus a single commit() per chunk of 500 — one disk confirmation for 500 rows instead of 500 separate ones.

Common mistakes

Loading the entire table into memory before scoring. conn.execute(...).fetchall() on a table with tens of millions of rows can exhaust memory outright. fetchmany() in a loop keeps memory bounded no matter how large the table is.

Committing after every row out of habit. This is the single biggest cost in the row-by-row version above. It is easy to write by accident, by adapting online-serving code — which commits per request for good reason — into an offline script, where the same habit is pure waste.

Never checking how stale the last run was. A batch job that silently fails to run for three days is worse than one that never existed, because downstream code keeps reading old scores with no warning. Log the run's completion time somewhere it gets checked.

Recomputing everything every run, when only some rows changed. For very large tables, scoring only the rows that changed since the last run — using an updated_at column — can turn a multi-hour job into a multi-minute one.

Try it yourself

Change chunk_size from 500 to 10 and rerun. Throughput should drop noticeably — you are paying the commit cost far more often again, though not as often as the fully row-by-row version.

What to learn next

Researcher — Mathematics and papers.

Where the time actually goes

The dominant cost in score_row_by_row is not the scoring function — it is fsync-class disk synchronisation triggered by each commit(), which by default in SQLite waits for the write-ahead log to be durably flushed. This is a correctness guarantee (durability, the D in ACID), not overhead to be casually removed — but the guarantee only needs to apply at whatever batch granularity a failure would be acceptable to redo, not at every single row.

SQLite's WAL (write-ahead log) mode (PRAGMA journal_mode=WAL) additionally allows concurrent readers during a write and, combined with chunked commits, is a common and larger further improvement for batch workloads specifically. PRAGMA synchronous=NORMAL in WAL mode trades a small, bounded durability window (survives a process crash, not necessarily an OS crash at the exact wrong instant) for a further reduction in per-commit cost — a decision that should be made deliberately, with the failure mode understood, not applied blindly.

Scaling beyond one machine

For a single machine and a table that fits in reasonable disk space, chunked SQLite as shown scales into the tens of millions of rows without difficulty. Beyond that, or when the scoring function itself is the bottleneck rather than I/O, the same chunking principle reappears at larger scale:

  • Spark / Dask — partition the data, score each partition in parallel across a cluster, write results back partitioned.
  • Vectorised scoring — replacing the per-row Python loop with a single call over a NumPy or pandas array is typically the largest available speedup once I/O is no longer the bottleneck, often 10–100× depending on the model, because it moves the per-row overhead into compiled code called once instead of Python called per row.
  • Columnar output formats (Parquet) instead of row-oriented ones (a SQL table) for very large result sets, because downstream analytics readers scan columns, not rows.

Batch scoring as a feature-freshness problem

The staleness trade-off described in the beginner section is formally a feature freshness and point-in-time correctness problem once the scored features themselves come from a data pipeline with its own update schedule — covered in depth in the feature-pipelines material this site is building out; the short version is that a batch score is only as correct as the most stale input feature it depends on.

References

  • SQLite documentation, Write-Ahead Logging — sqlite.org/wal.html
  • SQLite documentation, PRAGMA synchronous — sqlite.org/pragma.html#pragma_synchronous
  • Zaharia et al., Apache Spark: A Unified Engine for Big Data Processing, CACM 2016 — the standard reference for partitioned, distributed batch processing at scale.

What to learn next

Related terms

What to learn next

These follow on from what you just read.

  • Latency, Load Testing and Capacity

    Latency budgets

    A latency budget splits your total allowed response time across every step of a request, so you know exactly which step is eating the most time before it becomes a problem.

  • Latency, Load Testing and Capacity

    Tail latency and percentiles

    The average response time can look perfectly healthy while a real slice of your users wait far longer, which is why percentiles like p95 and p99 matter more than the mean.

  • Latency, Load Testing and Capacity

    Load testing a model endpoint

    Load testing sends real, concurrent traffic at a service before real users do, so you find its breaking point on your own schedule instead of during a launch.