You need to update or delete roughly 200 million rows in a table that is serving live OLTP traffic. Compare doing it in a single transaction with committing in chunks, and describe how you would make the chunked version safe to re-run after a failure.

seniormust knowfreq 55%

answer

chunk = short lock hold, recyclable undo and log
keyset cursor, never OFFSET
cursor update inside the batch transaction
idempotent predicate so re-run is a no-op
throttle on replica lag; rollback of a giant txn is slow

basics

~20 s

One transaction means hours of held locks, huge undo and log growth, blocked version cleanup, replication lag, and a rollback as long as the run. Chunked commits keep each transaction short but lose all-or-nothing semantics, so make the work idempotent, resumable via a stored cursor, and throttled.

solid answer

~60 s

Single transaction: locks accumulate for the whole run and may escalate to table-wide contention, undo/rollback and write-ahead log grow to hold every changed row, the cleanup horizon is pinned for hours so the whole instance bloats, replicas fall behind, and if it fails at 95% the rollback can take as long as the run did.

Chunked commits: process a bounded batch — say 5,000 to 50,000 rows — commit, pause briefly, repeat. Each transaction is short, so locks are short-lived, undo and log space is recycled, cleanup keeps up, and replicas keep pace.

The price is atomicity: the table passes through partially-updated states, and a crash leaves the job half done. Make that safe by driving chunks off a keyset cursor on the primary key (never OFFSET), persisting progress in a control table inside the same transaction, writing the statement so re-applying it is a no-op, and adding a throttle that backs off on replication lag or lock waits. If readers must not see a half-done state, stage the change and expose it atomically instead of stretching the transaction.

**Single transaction:** locks accumulate for the whole run and may escalate to table-wide contention, undo/rollback and write-ahead log grow to hold every changed row, the cleanup horizon is pinned for hours so the whole instance bloats, replicas fall behind, and if it fails at 95% the rollback can take as long as the run did. **Chunked commits:** process a bounded batch — say 5,000 to 50,000 rows — commit, pause briefly, repeat. Each transaction is short, so locks are short-lived, undo and log space is recycled, cleanup keeps up, and replicas keep pace. The price is atomicity: the table passes through partially-updated states, and a crash leaves the job half done. Make that safe by driving chunks off a **keyset cursor** on the primary key (never OFFSET), persisting progress in a control table inside the same transaction, writing the statement so re-applying it is a no-op, and adding a throttle that backs off on replication lag or lock waits. If readers must not see a half-done state, stage the change and expose it atomically instead of stretching the transaction.

code

sql · 13 lines

sql

BEGIN;

UPDATE orders
   SET status = 'ARCHIVED'
 WHERE id >  (SELECT last_id FROM backfill_cursor WHERE job = 'archive_orders')
   AND id <= (SELECT last_id + 20000 FROM backfill_cursor WHERE job = 'archive_orders')
   AND status <> 'ARCHIVED';

UPDATE backfill_cursor
   SET last_id = last_id + 20000
 WHERE job = 'archive_orders';

COMMIT;

go deeper

for a junior

Know that a huge single transaction holds locks and undo for its whole duration and that batching into smaller commits is the normal approach.

for a middle

Name the concrete costs, describe the batch loop with a primary-key cursor, and explain why the statement must be idempotent.

for a senior

Add the persisted cursor committed with the data, throttling on replica lag and lock waits, a kill switch, batch-size tuning by commit interval, and rollback-cost reasoning.

for a principal

Frame the visibility question first — can readers see intermediate state — and choose between chunked in-place change, write-then-swap, generation markers, or partition operations based on that answer and on the maintenance window available.

Why one giant transaction is the wrong default

A single transaction covering 200 million rows is the textbook long transaction, and it hits every cost at once:

Lock accumulation. Every touched row stays locked until the final commit. Concurrent writers to those rows block. Engines that escalate row locks to page or table locks under pressure will do so, turning targeted contention into a table-wide stall.
Undo / rollback growth. Before-images for 200 million rows must remain available for the whole run. Undo tablespaces fill; in engines that keep old versions in the table, dead versions accumulate instead.
Write-ahead log pressure. The log cannot be recycled past the oldest transaction still needed for recovery, so log volume grows and archiving falls behind.
Pinned cleanup horizon. For the entire run, no other transaction's garbage anywhere in the instance can be reclaimed. Unrelated tables bloat.
Replication lag. A physical replica replays the same volume; a logical replica may not even see the change until commit, then must apply the entire batch at once, producing a lag spike measured in hours.
Catastrophic rollback. If it fails or is cancelled at 95%, the engine must undo everything. Rollback is real work — often as slow as or slower than the forward run — and it usually cannot be cancelled without a restart and recovery.

The chunked pattern

Process a bounded slice, commit, repeat:

Pick a stable ordering key, normally the primary key.
Select the next batch with a keyset predicate (WHERE id > :last_id ORDER BY id LIMIT :n). Never use OFFSET: it rescans skipped rows, so the job gets quadratically slower.
Apply the change for that batch.
Persist the new cursor position in the same transaction as the data change, so progress and effect commit or fail together.
Sleep briefly between batches to leave headroom for OLTP traffic.

Batch size is the tuning knob: large enough that per-transaction overhead is amortised, small enough that each transaction is short (a useful target is a commit every 100 ms to 1 s) and that locks are never held long. Start small, measure lock waits and replica lag, and increase carefully.

Making it safe to re-run

Chunking trades atomicity for boundedness, so recoverability must be engineered:

Idempotent statements. Write the update so re-applying it changes nothing: add a predicate such as AND status <> 'NEW' alongside the key range. Then an interrupted batch can simply be redone.
Durable cursor. A control row (job name, last key, rows done, state) updated inside the batch transaction gives exactly-once effect without exactly-once execution.
A single runner. Guard with a lock or lease so two workers cannot both advance the cursor. Parallel workers are possible over disjoint key ranges, but only if ranges are assigned, not discovered.
Throttling and backpressure. Before each batch, check replica lag and recent lock-wait time; if either is over threshold, sleep longer. This is what keeps a maintenance job from becoming an incident.
Kill switch. A flag an operator can flip to pause the job cleanly at a batch boundary.
Observability. Log rows processed, batch duration, current key position and estimated completion, so someone can answer "how far along is it" without querying the table.

The correctness question you must raise

Chunking means the table is observably half-updated for the duration. Ask whether any reader can tolerate that. Usually yes, because rows are independent. When not, the answer is not a longer transaction — it is a design that makes the switch atomic and cheap:

Write into a new column or new table in chunks, then flip readers over in one tiny transaction.
Add a generation marker and have readers filter on the active generation.
For deletes, chunk the delete and expose a filtered view until the job completes.

For deletes specifically, if you are removing most of the table, copying the surviving rows into a fresh table and swapping is often far cheaper than deleting hundreds of millions of rows, because it avoids generating a version per deleted row and leaves no bloat behind. And if the data is partitioned by the deletion criterion, dropping a partition beats any row-by-row approach outright.

What the interviewer is listening for

That you know transaction size is an operational parameter, not an accident; that you can name the concrete costs (locks, undo, log, cleanup horizon, replica lag, rollback time); that you reach for keyset pagination and a persisted cursor rather than OFFSET and hope; and that you explicitly consider whether intermediate states are visible and acceptable.

## Why one giant transaction is the wrong default A single transaction covering 200 million rows is the textbook long transaction, and it hits every cost at once: - **Lock accumulation.** Every touched row stays locked until the final commit. Concurrent writers to those rows block. Engines that escalate row locks to page or table locks under pressure will do so, turning targeted contention into a table-wide stall. - **Undo / rollback growth.** Before-images for 200 million rows must remain available for the whole run. Undo tablespaces fill; in engines that keep old versions in the table, dead versions accumulate instead. - **Write-ahead log pressure.** The log cannot be recycled past the oldest transaction still needed for recovery, so log volume grows and archiving falls behind. - **Pinned cleanup horizon.** For the entire run, no other transaction's garbage anywhere in the instance can be reclaimed. Unrelated tables bloat. - **Replication lag.** A physical replica replays the same volume; a logical replica may not even see the change until commit, then must apply the entire batch at once, producing a lag spike measured in hours. - **Catastrophic rollback.** If it fails or is cancelled at 95%, the engine must undo everything. Rollback is real work — often as slow as or slower than the forward run — and it usually cannot be cancelled without a restart and recovery. ## The chunked pattern Process a bounded slice, commit, repeat: 1. Pick a **stable ordering key**, normally the primary key. 2. Select the next batch with a **keyset predicate** (`WHERE id > :last_id ORDER BY id LIMIT :n`). Never use OFFSET: it rescans skipped rows, so the job gets quadratically slower. 3. Apply the change for that batch. 4. Persist the new cursor position **in the same transaction** as the data change, so progress and effect commit or fail together. 5. Sleep briefly between batches to leave headroom for OLTP traffic. Batch size is the tuning knob: large enough that per-transaction overhead is amortised, small enough that each transaction is short (a useful target is a commit every 100 ms to 1 s) and that locks are never held long. Start small, measure lock waits and replica lag, and increase carefully. ## Making it safe to re-run Chunking trades atomicity for boundedness, so recoverability must be engineered: - **Idempotent statements.** Write the update so re-applying it changes nothing: add a predicate such as `AND status <> 'NEW'` alongside the key range. Then an interrupted batch can simply be redone. - **Durable cursor.** A control row (job name, last key, rows done, state) updated inside the batch transaction gives exactly-once *effect* without exactly-once execution. - **A single runner.** Guard with a lock or lease so two workers cannot both advance the cursor. Parallel workers are possible over disjoint key ranges, but only if ranges are assigned, not discovered. - **Throttling and backpressure.** Before each batch, check replica lag and recent lock-wait time; if either is over threshold, sleep longer. This is what keeps a maintenance job from becoming an incident. - **Kill switch.** A flag an operator can flip to pause the job cleanly at a batch boundary. - **Observability.** Log rows processed, batch duration, current key position and estimated completion, so someone can answer "how far along is it" without querying the table. ## The correctness question you must raise Chunking means the table is observably half-updated for the duration. Ask whether any reader can tolerate that. Usually yes, because rows are independent. When not, the answer is not a longer transaction — it is a design that makes the switch atomic and cheap: - Write into a **new column or new table** in chunks, then flip readers over in one tiny transaction. - Add a **generation marker** and have readers filter on the active generation. - For deletes, chunk the delete and expose a filtered view until the job completes. For deletes specifically, if you are removing most of the table, copying the surviving rows into a fresh table and swapping is often far cheaper than deleting hundreds of millions of rows, because it avoids generating a version per deleted row and leaves no bloat behind. And if the data is partitioned by the deletion criterion, dropping a partition beats any row-by-row approach outright. ## What the interviewer is listening for That you know **transaction size is an operational parameter**, not an accident; that you can name the concrete costs (locks, undo, log, cleanup horizon, replica lag, rollback time); that you reach for keyset pagination and a persisted cursor rather than OFFSET and hope; and that you explicitly consider whether intermediate states are visible and acceptable.

Why is OFFSET-based chunking a trap for a 200-million-row job?
OFFSET makes the engine produce and discard all preceding rows on every batch, so batch N costs work proportional to N times the batch size and the job degrades quadratically; the last batches can take minutes each. It is also unstable, because concurrent inserts and deletes shift rows across batch boundaries so records get processed twice or skipped. A keyset predicate on the primary key seeks directly to the resume point and is stable under concurrent modification.
During the backfill, replica lag climbs to several minutes. What do you change?
Reduce batch size and increase the sleep between batches so the replica gets time to apply between commits. Better, make the runner read the lag before each batch and back off automatically until it is under threshold, which keeps the job self-regulating overnight. If lag persists even at small batch sizes, the replica is the bottleneck and the job should move to a low-traffic window rather than being pushed harder.
When would you not chunk at all?
When the change can be expressed as a metadata operation: dropping or detaching a partition, or a metadata-only column addition, is effectively instant and generates no per-row versions. Also when copying survivors into a new table and swapping is cheaper than modifying rows in place, which is common when the operation removes most of the table.

saying these in an interview costs you the question

Assuming a single transaction is safer because it is atomic, without pricing the rollback and lock hold
Chunking with LIMIT/OFFSET instead of a keyset cursor
Tracking progress only in memory, so a crash restarts from zero or double-applies
Committing per row, which trades one problem for enormous commit and log overhead
Ignoring replica lag and running the job at full speed against a live primary
Not asking whether readers can tolerate a partially-updated table

You need to update or delete roughly 200 million rows in a table that is serving live OLTP traffic. Compare doing it in a single transaction with committing in chunks, and describe how you would make the chunked version safe to re-run after a failure.

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Why one giant transaction is the wrong default

The chunked pattern

Making it safe to re-run

The correctness question you must raise

What the interviewer is listening for

▾common follow-ups3 questions

▾red flags6 flags

§ context

▾related questions4 questions

▾related topics3 topics

Why one giant transaction is the wrong default

The chunked pattern

Making it safe to re-run

The correctness question you must raise

What the interviewer is listening for

answer

go deeper

context