Describe a safe batching strategy for deleting 50 million old rows without locking the table for hours.

Problem Statement

Explanation

Delete in small, time-bounded chunks that follow an index, such as primary key or date. Use a WHERE with a range or a keyset cursor to grab, say, 5–20k rows per batch. Commit between batches to release locks and let checkpoints advance. Add supporting indexes, disable unnecessary triggers, and run during low traffic. Monitor fragmentation; if your engine supports it, use partitioning and drop or detach whole partitions for instant archival cleanup.

Code Solution

SolutionRead Only

WHILE 1=1 LOOP
  DELETE FROM events WHERE ts < :cutoff
  ORDER BY ts
  LIMIT 10000;
  EXIT WHEN ROW_COUNT()=0;
  COMMIT;
END LOOP;

Practice Sets

This question appears in the following practice sets:

Query Plans & Performance Tuning

Next Question

Master Interviews
Anywhere, Anytime

Describe a safe batching strategy for deleting 50 million old rows without locking the table for hours.

Problem Statement

Explanation

Code Solution

Practice Sets

Related Questions

Which SQL statement retrieves all columns and rows from a table named employees?

Which clause filters rows before aggregation and affects which rows are grouped?

What is the default sort order when ORDER BY is used without ASC or DESC?

Which construct is commonly used to paginate results in SQL engines like Postgres and MySQL?

Which predicate correctly checks for missing values in standard SQL?

More from SQL & Databases

Master Interviews Anywhere, Anytime

Describe a safe batching strategy for deleting 50 million old rows without locking the table for hours.

Problem Statement

Explanation

Code Solution

Practice Sets

Related Questions

Which SQL statement retrieves all columns and rows from a table named employees?

Which clause filters rows before aggregation and affects which rows are grouped?

What is the default sort order when ORDER BY is used without ASC or DESC?

Which construct is commonly used to paginate results in SQL engines like Postgres and MySQL?

Which predicate correctly checks for missing values in standard SQL?

More from SQL & Databases

Master Interviews
Anywhere, Anytime