Safely Deleting Massive Tables: Avoid Long Transactions with Batch SQL and Insert‑Instead Strategies

When you need to delete a huge amount of data—e.g., all rows from 2021 in a table named yes that holds 500 million rows—the instinctive SQL is:

delete from yes where create_date > "2020-12-31" and create_date < "2022-01-01";

Running this statement directly creates a long transaction that locks millions of rows, blocks concurrent DML, can cause master‑slave replication delay, and may even lead to service outages if the transaction runs for hours and then rolls back.

Why Long Transactions Are Problematic

A long‑running delete holds locks until the transaction finishes, preventing other operations from accessing the locked rows. This can block business threads, cause cascading delays, and increase the risk of a system‑wide avalanche.

Additionally, the master may take a long time to finish, delaying replication to slaves and creating data inconsistency windows.

If the transaction runs into an error after several hours, the entire operation rolls back, wasting time and requiring a restart.

Therefore, we must avoid long transactions.

How to Split the Delete into Smaller Batches

Using the same example SQL, a naive split by date might look like:

delete from yes where create_date > "2020-12-31" and create_date < "2021-02-01";

delete from yes where create_date >= "2021-02-01" and create_date < "2021-03-01";

However, if create_date is not indexed, each batch will still trigger a full table scan.

To leverage an index, we can use the primary key range. First obtain the minimum and maximum id values:

SELECT MIN(id) FROM yes;

SELECT MAX(id) FROM yes;

Assume the range is 233,333,333 – 666,666,666. Then delete in incremental id windows while keeping the original date filter:

delete from yes where (id >= 233333333 and id < 233433333) and create_date > "2020-12-31" and create_date < "2022-01-01";

delete from yes where (id >= 233433333 and id < 233533333) and create_date > "2020-12-31" and create_date < "2022-01-01";

repeat until the max id

delete from yes where (id >= 666566666 and id <= 666666666) and create_date > "2020-12-31" and create_date < "2022-01-01";

Each batch touches a small, indexed range, so the transaction is short, locks are released quickly, and failures affect only the current batch.

These batches can also be executed in parallel to further speed up the process, provided the system can tolerate the temporary lock contention.

When Deleting Is Too Expensive: Insert‑Instead

If you need to remove 480 million rows from a 500 million‑row table, consider keeping the 20 million rows you need and inserting them into a new table instead of deleting the rest.

Create a new table yes_temp .

Copy the desired rows: INSERT INTO yes_temp SELECT * FROM yes WHERE create_date BETWEEN ...;

Rename the original table: RENAME TABLE yes TO yes_old;

Rename the temporary table: RENAME TABLE yes_temp TO yes;

This “swap‑table” technique avoids massive deletes altogether and can reduce a 2‑hour operation to under a minute.

Tools like pt-online-schema-change automate similar workflows, but the core idea remains the same: use indexed ranges or a copy‑instead‑delete strategy to keep transactions short and system impact minimal.

Conclusion

Understanding the risks of large‑scale deletes and applying batch‑or‑copy strategies is essential for developers, especially in environments without dedicated DBAs. By breaking down the work into indexed chunks or by rebuilding the table with only the needed data, you can maintain database performance and reliability.