Every backend team has one. It is a scheduled job that recalculates something for every record in a big table: a risk score, an eligibility flag, a classification, a renewal date. It began as a simple loop. It worked beautifully when the table had 50,000 rows. Today the table has 10 million rows, the run takes hours, it occasionally dies halfway, and nobody wants to touch it.

We recently rebuilt exactly this kind of job, a nightly process that recomputes a derived status for every customer record. The rebuild turned a run that moved millions of messages and made thousands of cross-service calls into one that does a few hundred rows of real work and a handful of SQL statements per page.

The interesting part is that the speedup did not come from clever tricks. It came from a small set of patterns that apply to almost any batch scheduler. This article walks through the whole process in order, with the reasoning behind each step, so you can apply it to your own jobs.

The process at a glance

1. Measure the problem with real numbers.
2. Decide whether the job should do less work at all.
3. Fix how the job reads data.
4. Fix how the job writes data.
5. Shorten and bound every transaction.
6. Make failures local instead of contagious.
7. Add safe concurrency, checkpoints and locking.
8. Make re-runs and retries harmless.
9. Bound memory.
10. Configure, observe and protect sensitive data.
11. Test and verify on real infrastructure.

The order matters. Steps 1 and 2 usually deliver more than all the others combined, and they cost nothing to try on paper.

Step 1: Size the problem with numbers

Before you change a line of code, answer a short list of questions using the code and the database, not intuition. Engineers are consistently wrong about where a batch job spends its time.

Question

How to find the answer

How many rows does each run scan?

Table size and the query’s WHERE clause

How many rows actually change per run?

The business rule, plus a count from a real run

How many SQL statements does each row cost?

Count entity loads, eager associations, per-page COUNT queries, flushes and re-reads

How many external calls does each row cost?

HTTP or RPC calls, message-broker sends, decryption, file access

What is the longest transaction?

Find any transaction annotation that spans a loop

How many connections does the job hold at peak?

Workers, prefetchers and any nested transactions, compared with the pool size

Does the job need every row, or only rows that can change?

Is there a date or an event that predicts when a row’s result changes?

In our case the answers were embarrassing:

• The job scanned 10 million rows to change a few hundred.
• Each row cost about 13 SQL statements, mostly because loading a fully mapped entity triggered around nine separate SELECTs through eager associations.
• The job produced one message on a broker per row, and a separate service paged through the data over HTTP, about 20,000 calls per run.
• Worse, the HTTP client had a fallback that returned an empty page when the call failed. A failed run therefore still reported SUCCESS, with most rows quietly missing.

Write your own table down. If it shows the job is already cheap, stop here and leave it alone. Optimisation has a maintenance cost, and a cheap job does not deserve one.

Step 2: Do less work, not the same work faster

The biggest win in the entire project had nothing to do with SQL tuning. It came from asking one question: can we predict when a row’s result will next change?

In our case the derived status depended on a person’s age, and age only crosses a category boundary on a known date. A customer who is 31 today will not change category for years. Scanning that row every night is pure waste.

Store a “next due” date

If you can compute when a row’s result will next change, store that date on the row. The daily run then reads only the rows whose date has arrived. A job that touched 10 million rows a day now touched a few hundred.

The rules that make it safe

A stored “next due” date is a cache, and caches go stale. These rules keep it honest:

• Use a far-future sentinel for “never changes again.” For example 9999-12-31. Rows in their final state drop out of every future delta run permanently.
• Keep NULL to mean “not computed yet.” New rows and edited rows then get picked up automatically, because they have no date yet.
• Clear the date whenever the source data changes. If a user edits the field the result depends on, null out the stored date in the same code path, ideally inside the entity’s setter, so no caller can forget.
• Run a weekly full reconciliation. A full sweep is your safety net against drift, missed events, bugs and manual data fixes made directly in the database.
• Run an event-triggered full sweep when configuration changes. If thresholds or rules come from configuration, every stored date was computed under the old configuration. When a config input changes, recompute everything once.

Three run modes fall out of this naturally: a cheap daily DELTA run, a weekly FULL run, and an on-demand FULL run after a configuration change. They share all the code below and differ only in which query selects the rows.

Compute “today” exactly once

A small trap hides inside date-driven jobs. If the job reads the current date in several places, a run that crosses midnight will classify a row using day D and then evaluate follow-up rules using day D+1, and the two will disagree.

The fix is simple: compute the run date once at the start, store it in a run context object, and pass it to every date calculation, including calls into other services. Overload your helper methods to accept the date explicitly rather than reading the clock.

Run the job next to its data

The old design fanned work out across service boundaries: one broker message per row, and an HTTP client paging through the data from another service. Every row paid for serialisation, a network hop and a retry policy, and the failure mode (the empty fallback page) was dangerous.

The rebuilt job runs inside the service that owns the data, reading its own table directly. The only thing left in the scheduling service is a configuration row that says “trigger this job at this time.” Moving the work to the data eliminated millions of messages and thousands of HTTP calls in one stroke.

Step 3: Fix how the job reads data

Use keyset pagination, not OFFSET

Paging with Page<> or OFFSET looks convenient and ships in every framework. It is also a quiet disaster at scale:

• It runs a COUNT(*) on every page, which on a large table is itself expensive.
• OFFSET costs more with every page, so a full run is O(N²). Page 9,000 must walk and discard 9,000 pages’ worth of rows before returning anything.
• If rows are inserted, deleted or updated while you scan, OFFSET skips some rows and repeats others. This is worst when the job updates the very column its query filters on.

Keyset pagination fixes all three. Instead of “skip N rows,” you say “give me the next rows after the last id I saw”:

-- first page: no cursor yet
SELECT id, encrypted_dob, next_review_date
FROM customers
WHERE status = 'ACTIVE'
ORDER BY id
FETCH FIRST :n ROWS ONLY;

-- every page after that
SELECT id, encrypted_dob, next_review_date
FROM customers
WHERE status = 'ACTIVE' AND id > :lastId
ORDER BY id
FETCH FIRST :n ROWS ONLY;

Write these as two separate queries, not one query with (:lastId IS NULL OR id > :lastId). The OR form often defeats the optimiser and produces a poor plan. Two simple queries give the database one stable, index-friendly plan each.

The cursor also becomes your checkpoint. “Resume from id X” is meaningful and stable even when data changes underneath you, whereas “resume from page 4,200” is not.

For the delta run, the same shape applies with an extra predicate (next_review_date <= :today OR next_review_date IS NULL), and an index on that column turns the daily query into a range scan.

Read a narrow projection, not the entity

Loading a fully mapped entity is the most expensive thing an ORM does. With eager associations, a single findById can issue around nine SELECTs, and the entity then becomes managed state that the persistence context must track and dirty-check.

For reads, ask only for the columns you need and map them into a plain value type. In Java that is a record filled by a constructor expression in the query:

record ScanRow(UUID id, byte[] encryptedDob, LocalDate nextReview) {}

One row, no associations, no managed state, no proxies. Prefer records over interface projections on the hot path, because interface projections are proxies and cost more per row.

Load wider data only for rows that need it

Often most rows in a page need nothing beyond the narrow projection, and a few need more: a join, an extra decrypted field. Do not make everyone pay for the few. Decide per row from the narrow data, collect the ids that need more, and fetch the wider data for just those ids with a single IN query.

Kill the N+1 with one IN query per page

If each row needs related rows (the latest flag, the latest status), do not query inside the loop. Collect the page’s ids, run one IN query for all related rows, then reduce in memory to the latest row per key. A page of 1,000 rows costs one extra query instead of 1,000.

Respect the IN-list limit

Some databases cap the number of elements in an IN list at 1,000 and fail hard when you exceed it. Clamp your page size to 1,000, and throw an explicit error if a caller asks for more. A clear error at startup beats a mysterious failure mid-run.

Set a fetch size

Database drivers fetch a small number of rows per network round trip by default, often 10. A 1,000-row page then takes about 100 round trips. Setting the fetch size to match your page size (via a query hint or the statement setting) collapses that to a handful. It is one line of configuration and one of the cheapest wins available.

Check cheaply before loading expensively

Some rows need an expensive step: a wide load, or decryption of several fields. Before paying for it, ask whether you can rule the row out using data you already have.

In our full sweeps after a configuration change, most customers could not possibly be affected by the new thresholds. A cheap pre-check on the narrow projection avoided millions of wide loads and tens of millions of decrypt operations.

There is one rule that makes this optimisation safe and one test that enforces it:

• The pre-check must call the same helper methods as the full evaluation, so the two cannot disagree by construction.
• Write a parity test asserting preCheck(row) == (fullEvaluation(row) != null) across a large grid of boundary cases. Without it, the pre-check and the real logic will drift apart the first time someone edits one of them, and rows will silently stop being processed.

Decrypt only what you use, once per row

If sensitive fields are encrypted at rest, decrypt only the fields the decision actually needs, and decrypt each at most once per row. Decryption is CPU-heavy and often involves a key service. In our job the main sweep decrypted a single field, and the multi-field decrypt ran only for the small number of rows that genuinely needed the wider object.

Step 4: Fix how the job writes data

Skip unchanged rows completely

Compare the new result with the state you already fetched. If nothing changed, do nothing: zero SQL statements, zero audit events, zero downstream triggers. In a daily job where almost everything is unchanged, this single rule removes the vast majority of write work.

Write in bulk

Stage every change for the page in memory, then write it in one go. With an ORM, that means one saveAll with JDBC batching switched on (batch size around 50, with inserts and updates ordered so statements can be grouped). Two details trip people up:

• Batching silently stops working with database-generated IDENTITY ids. Use application-generated UUIDs.
• For plain bookkeeping columns (such as stamping a review date on thousands of rows), skip the ORM and use batchUpdate with a single parameterised statement.

Write managed entities, not detached ones

If you built an object elsewhere and call save on it, the ORM treats it as detached and runs a merge, which issues a SELECT for every row first to see whether it exists. Instead, re-read the rows inside the write transaction. They become managed, the dirty check updates them in place, and the extra SELECTs disappear. The re-read is one IN query for the whole page.

Keep bookkeeping consistent with the data it describes

This is the subtlest rule in the write path. A stamp such as “processed” or “next due” must commit together with the data write it describes, or be written only after that write succeeds.

The failure case: the stamp commits, the data write fails. The row now says “done until next quarter” while holding stale data, and the daily run will never look at it again. Always make the stamp share the data write’s fate.

Step 5: Keep transactions short and bounded

This is where batch jobs most often go wrong, usually by accident.

Never wrap the whole run in a transaction

A transaction that spans a loop over millions of rows has three terrible properties. It pins a database connection for the entire run. It makes the persistence context grow without bound, which is a slow memory leak that ends in an out-of-memory error. And it holds row locks for hours, blocking online users.

Check for this explicitly: a class-level transactional annotation on a job, a launcher or a service loop is the classic form of this bug.

Put only writes inside the transaction

The pattern that works is a three-phase page:

1. Read the page outside any transaction.
2. Compute: decrypt, evaluate rules, call any remote service, all still outside any transaction.
3. Write inside one short programmatic transaction that contains only the re-read, the bulk save, outbox inserts and the bookkeeping stamps.

A connection is then held only for the milliseconds of the write, not for decryption or network calls.

Give every transaction a timeout

Every transaction should have a timeout shorter than your connection pool’s leak-detection threshold. If the pool reports a suspected leak at 60 seconds, give the page transaction something like 30 seconds and a single-row fallback transaction around 5.

TransactionTemplate pageTx = new TransactionTemplate(txManager);
pageTx.setTimeout(30);   // pool leak detection fires at 60s

TransactionTemplate rowTx = new TransactionTemplate(txManager);
rowTx.setTimeout(5);     // single-row fallback path

The reasoning is that a stuck transaction should roll back and release its connection on its own. The checkpoint logic then retries the page. Without a timeout, the connection stays pinned and the pool begins logging “apparent connection leak” warnings, usually at 3 a.m.

Know what joins your transaction

Any method you call inside the page transaction with default propagation (REQUIRED) becomes part of it. That is exactly right for an outbox insert, which should commit or roll back with the data. It is exactly wrong for anything slow: an HTTP call, a broker send, heavy computation. Keep those outside.

Side effects that must survive get their own transaction

Some side effects should not be undone if the main write rolls back, for example recording that a follow-up review was triggered. Run those outside the page transaction, each in a short REQUIRES_NEW transaction. Do the programmatic form so the try/catch sits outside the transaction, which lets you catch a constraint violation that only appears at flush time.

Avoid nested REQUIRES_NEW inside the page transaction

Each nested REQUIRES_NEW takes a second connection while the first is still held. With four workers, each holding one connection and waiting for another, you can exhaust a pool of 20 faster than you would think, and in the worst case deadlock on the pool itself.

Step 6: Make failures local

Batching introduces a new risk: one poisoned row can sink 1,000 good ones. A good batch job must make sure a bad row fails only itself.

Two-level writes with per-row fallback

1. Try the batched page write inside the short page transaction.
2. If it throws, roll back and redo the same work one row at a time, each row in its own short transaction with the tight timeout.
3. Merge counters only from transactions that actually committed, so the rolled-back batch attempt is not counted twice.

The common case stays fast, and the rare bad page degrades gracefully into slow but correct processing. Only the genuinely bad row ends up failing.

A failed row must stay eligible

We found this bug in our own rebuild, and it is easy to miss. In the fallback path, a row whose write failed still received a future “next due” date, so the daily run dropped it until the weekly full sweep. The row was effectively lost for days.

The rule: any row that failed must be stamped “due today” (or left unstamped) so tomorrow’s run picks it up again. Failure must never advance the schedule.

Isolate each row’s parse, decrypt and arithmetic

Wrap each row’s parsing, decryption and date arithmetic in its own error handling. Count failures by type (invalid value, decrypt failure, missing data) so you can see what is going wrong. Date arithmetic deserves special care: adding a period to a date near the maximum representable date throws an exception, and one such row should not abort a page. Never log decrypted values, not even in error messages.

Add a failure-ratio circuit breaker

Bad data is normal. A broken dependency is not, and the two need different handling. Split failures into two kinds:

• Data failures: a malformed value, a missing field. These are per-row, expected, and counted but never fatal.
• Systemic failures: database write errors, decrypt failures, downstream errors. These suggest something is broken.

If systemic failures on a page exceed a threshold (we used 20%), abort the run with a resumable checkpoint rather than ploughing on. Without a breaker, a database outage produces a run that “completes” having processed nothing, and reports SUCCESS.

Fail fast on bad configuration

Validate every configuration value once, at the start of the run, and throw a non-retriable exception if something is missing or invalid. A missing threshold should stop the run immediately with a clear message, not surface as a NullPointerException at row 4 million, and a retry loop should never keep retrying something that cannot succeed.

Step 7: Concurrency, checkpoints and locking

A sliding window of pages

Parallelism without chaos comes from a small, fixed structure:

• A fixed pool of worker threads (we used 4).
• A window of in-flight pages (we used 3), so workers always have something to do.
• A separate single-thread executor that prefetches the next page while workers are busy.

Budget your connections explicitly: workers, plus one for the prefetcher, plus the short side-effect transactions, kept well under the pool size. Then confirm the arithmetic against reality, not assumptions.

One small but important detail: thread-local context such as a security or request context does not follow work onto pool threads. Set it inside each worker task and clear it in a finally block, otherwise every row logs a warning about a missing context.

Checkpoint at the low watermark

Pages finish out of order. If you record “the last page that completed” as your checkpoint and then crash, you may have recorded page 12 while page 10 never finished, and on resume you will skip page 10 forever.

The fix is to drain results in strict FIFO order. Always wait on the front page of the window, and record only the front page’s cursor as the checkpoint. This is the low watermark: everything at or before the checkpoint is guaranteed finished. On resume, the run restarts from that cursor and at worst repeats some completed pages, which is harmless because re-running a page is a no-op (see Step 8).

A durable lease with owner fencing

Each run should hold a durable lease keyed by a unique run id, recording which instance owns it. Three operations matter: heartbeat, complete and release. All three must filter on the owner, so an instance that has lost the lease cannot affect the new owner’s progress.

The heartbeat must return a boolean, and the caller must stop the run when it returns false. We found exactly this bug in an older scheduler: a run that lost its lease kept running alongside the new owner, and two instances wrote the same rows concurrently, a zombie dual-writer that is very hard to debug after the fact.

Renew the distributed lock on every heartbeat

If a distributed lock stops two runs overlapping, it needs an expiry (so a crashed pod does not hold it forever) and it needs renewal (so a healthy long run does not lose it). Renew on every heartbeat using an atomic compare-and-extend script, so you only extend a lock you still own. Two supporting rules:

• Every job that writes the same rows must share one lock key. In our case, the daily, weekly and configuration-change sweeps all contend on the same key.
• Decide deliberately what happens if the lock service is down. Failing open (running anyway) was our existing convention, because the lease and idempotent writes protect correctness.

Keep the run off the message-listener thread

If a message from a broker triggers the job, never run the job on the listener thread. It blocks consumption, trips poll-timeout rebalances, and causes redelivery storms. Instead, hand the run to a launcher: a single-thread executor with a SynchronousQueue, which gives exactly one run per pod and rejects a second trigger instead of queueing it.

While the run is in progress, publish a RUNNING heartbeat every few minutes so an upstream watchdog (ours gave up after 30 minutes of silence) does not declare the run dead and send it to a dead-letter queue.

Step 8: Make re-runs and retries harmless

At-least-once execution is the reality of distributed batch work. Pages get retried, runs get resumed, triggers get delivered twice. Design so that none of it hurts.

• Re-running a page is a no-op. A second pass over the same page must produce no writes, no audit events and no downstream triggers. This follows from skipping unchanged rows, and it is the property that makes checkpoint resume safe.
• Dedupe downstream triggers at the database. Put a unique index on the trigger table’s natural key. Even if application logic ever double-fires, the second insert fails harmlessly.
• Order side effects so a failure leaves the retry signal visible. In our case, a downstream review trigger is evaluated before the flag is written. If the page then fails, the stale flag still differs from the new result, so the next run re-detects the change and fires the trigger. Had we written the flag first and failed before the trigger, the difference would be gone and the trigger lost.
• Keep audit events small. Emit one event per changed row containing ids and changed values only. Never serialise the whole object graph into an audit record: it is slow, huge and often contains data that should not be there.
• Mind the outbox queue. If audit events flow through an outbox with a bounded callback queue, keep workers multiplied by page size within that queue’s capacity. Overflow can be designed to be safe (rows stay PENDING and a relay resends them), but it delays delivery, so size it deliberately.

Step 9: Bound memory

A batch job’s memory should be a constant, not a function of table size. Make it so:

• Hold at most (window + 1) pages of small records at any time.
• Use LongAdder or similar counters rather than lists of per-row results.
• Keep no per-row result lists across pages.
• Close the persistence context at every page commit so entities do not accumulate.
• Build objects cheaply: records instead of proxies, and convert reusable values such as thresholds into Period objects once per run rather than once per row.

If you can compute the maximum heap the job needs from its settings (window, page size, record size), you have done this right.

Step 10: Configuration, observability and sensitive data

Put every knob in configuration, with a safe fallback

Worker count, window size, page size, transaction timeouts, failure-ratio threshold: all of them belong in configuration with sensible defaults. Guard each value so that zero or missing means “use the default.” This matters because tests often construct the job outside the dependency-injection container, where injected fields are zero.

Make the job observable

• Counters and timers per run mode.
• One summary INFO line every N pages, with scanned, changed, unchanged and failed counts.
• Per-row logging only at DEBUG.
• Final counts returned to whatever scheduled the job, so “SUCCESS” comes with numbers.

A job that reports only “done” is a job you will not trust the first time it is wrong.

Treat sensitive data deliberately

Decrypt in memory only and never log decrypted values. Be careful with derived columns: a stored “next due” date that is computed from a date of birth effectively points back to the original, so it deserves the same scrutiny as the source. Exclude such columns from audit trails and serialisation, keep them out of search indexes, and get explicit sign-off from whoever owns compliance before shipping them.

Migrations and code quality

• Let your migration tool own indexes and schema, using idempotent guards so a re-run is safe. Do not let the ORM create indexes implicitly.
• Check the next free migration version across unmerged branches too, because most migration tools refuse out-of-order versions.
• Keep methods small. Batch code tends to produce one enormous method, and static analysis gates on cognitive complexity will punish it. Extract constants for repeated literals and delete unused code.

Step 11: Test and verify

Batch jobs fail in odd places, so test at four levels.

Rule tests. The pure date and threshold logic: boundary values at minus one, exact and plus one day; February 29 in non-leap years; month-and-day thresholds; the sentinel value.

Page-processor tests.

• An unchanged row makes zero writes and zero downstream calls.
• A first-time row inserts but does not fire a trigger.
• A real transition fires exactly once, in the right order.
• A bad row is isolated and the rest of the page commits.
• A failed row stays eligible for tomorrow.
• Transaction timeouts are asserted by capturing the transaction definition passed to the transaction manager.

Job-level tests. The low-watermark checkpoint, resume from a saved cursor, the failure-ratio breaker, lock contention, a lost lease stopping the run, and bad configuration being reported as non-retriable.

Parity tests for every pre-check, as described in Step 3.

Verification on real infrastructure, which unit tests cannot replace:

• Count SQL statements per page with the ORM’s statistics. Expect a handful, not thousands.
• Run the database’s explain-plan on the delta query and confirm an index range scan.
• Load-test on at least a million rows.
• Confirm there are no connection-pool leak warnings.
• Confirm no backlog is building in the outbox.
• Run the job twice and confirm the second run performs zero writes.

A reusable checklist

Copy this into your own planning document and tick each line, writing “N/A” plus a reason when something does not apply.

Workload

• Measured scanned rows, changed rows, statements per row, external calls per row.
• Considered a stored “next due” date, with sentinel and NULL semantics.
• Run date computed once and passed everywhere.
• Job runs next to its data.

Reading

• Keyset pagination with separate first and after queries.
• Narrow record projections.
• One IN query per page for related rows; page size at or below the IN limit.
• Fetch size set.
• Cheap pre-check with a parity test.

Writing

• Unchanged rows skipped completely.
• Bulk writes with managed entities.
• Bookkeeping stamps commit with their data.

Transactions

• No run-wide transaction.
• Only writes inside transactions; timeouts below the leak threshold.
• No slow calls or nested REQUIRES_NEW inside the page transaction.

Failures

• Batched write with per-row fallback.
• Failed rows stay eligible.
• Failure-ratio circuit breaker; config validated up front.

Concurrency

• Worker pool, window and prefetch within the connection budget.
• Low-watermark checkpoint in FIFO order.
• Owner-fenced lease; stop on lost heartbeat; renewed distributed lock.
• Run off the listener thread, with upstream heartbeats.

Safety

• Re-runs are no-ops; unique indexes on triggers.
• Side effects ordered for at-least-once delivery.
• Small audit events; bounded memory.
• Configurable, observable, no sensitive values in logs.

The short version

If you take only five ideas away:

1. Store a “next due” date so daily runs read only rows that can actually change.
2. Use keyset pagination and narrow projections, with one query per page for related data.
3. Keep transactions short, per page, writes only, with timeouts below your pool’s leak threshold.
4. Batch the writes, fall back to one row at a time, and never let a failed row look finished.
5. Fence your leases and checkpoint at the low watermark, so crashes and concurrency cannot corrupt progress.

The job we rebuilt went from millions of messages and thousands of cross-service calls per run to a few hundred rows of real work and a handful of SQL statements per page. None of it relied on exotic technology. It came from asking, at every step, the same question: does this need to happen at all?