Efficient Strategies for Deleting Massive Data from Large Tables

1. Issues with Direct Bulk Deletion

Deleting all rows from a table containing tens of millions of records in a single operation can cause table locks, exponential log growth, CPU spikes, and master‑slave replication delays.

1.1 Table‑Locking and Business Freeze

Problem: The delete operation holds a long‑running lock, blocking other queries and writes.

Consequence: Service timeouts and severe user‑experience degradation.

Example: Deleting 10 million rows takes 2 hours, during which users cannot place orders or run queries.

1.2 Transaction Log Explosion

Problem: The database records every delete in the transaction log for potential rollback.

Consequence: Log files can swell to hundreds of gigabytes, exhausting disk space and possibly crashing the database.

Deleting 100 million rows may generate a 500 GB log file, filling the disk.

1.3 CPU and Performance Degradation

Problem: Each delete updates indexes, checks constraints, and fires triggers.

Consequence: CPU and I/O become saturated, slowing overall database response.

During a massive delete, CPU can hit 100 % and query latency can jump from 2 ms to 10 seconds.

1.4 Replication Lag

Problem: Deletions on the primary must be replicated to slaves; large transactions cause delay.

Consequence: Slave data becomes stale, affecting reporting and backup processes.

Two‑hour primary delete → three‑hour slave lag, leading to incorrect reports.

1.5 Rollback Difficulty

Problem: If a delete fails or is cancelled, the transaction must be rolled back.

Consequence: Rollback can take longer than the delete itself, or even fail, corrupting data.

Interrupting a 1‑hour delete of 50 million rows may require a 2‑hour rollback, extending downtime.

2. Pre‑Deletion Planning

Before deleting, evaluate data volume, confirm the chosen method, ensure delete conditions are indexed, and back up the data.

2.1 Evaluate Data Volume and Choose a Strategy

Delete >90 % of rows: use the "create new table + drop old table" method.

Delete <10 % of rows: use batch deletes.

2.2 Use Indexes for Delete Conditions

Make sure the WHERE clause fields (e.g., date, ID range) have appropriate indexes to speed up row location.

2.3 Backup Data

Perform a full backup before large deletions; for batch deletions, consider incremental backups to reduce data‑loss risk.

Full backup before deletion.

Incremental backup when using batch deletes.

3. Common Large‑Scale Deletion Techniques

3.1 Batch Deletion

Delete in small chunks to avoid oversized transactions.

delete from tianluo_tab where 条件 LIMIT 1000; -- 每次删1000条

Loop until the table is empty, optionally pausing briefly (e.g., 0.1 s) between batches.

Disable autocommit to reduce transaction overhead:

SET autocommit=0; -- 手动控制事务
-- 执行删除...
COMMIT;

3.2 Partitioned Tables

If frequent massive deletions are required, design the table with partitions (e.g., by date) and drop whole partitions:

ALTER TABLE table DROP PARTITION partition_name;

Advantages: No row‑by‑row delete; physical files are removed instantly.

Requirements: Table must be pre‑partitioned on a suitable key.

3.3 Create New Table and Drop Old Table

When >50 % of rows are to be removed, create a new table containing only the rows to keep, then swap tables.

CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 保留条件;

RENAME TABLE old_table TO old_table_backup, new_table TO old_table;

DROP TABLE old_table_backup;

Pros: Extremely fast, minimal locking.

Cons: Requires rebuilding indexes, foreign keys, etc., on the new table.

3.4 TRUNCATE Instead of DELETE

To empty an entire table, TRUNCATE is usually more efficient because it does not delete rows one by one.

TRUNCATE TABLE tianluo_tab;

3.5 Additional Acceleration Tips

Use tools like pt‑archiver for automated low‑impact batch deletes.

Run deletions during off‑peak hours.

Test the operation on a replica first.

Archive cold data to a separate table before deletion.

4. Post‑Deletion Tasks

4.1 Data Validation

Confirm that the intended rows are gone and that remaining data is intact.

-- 确认目标数据已删除（如按时间条件删除）
SELECT COUNT(*) FROM tianluo_tab WHERE create_time < '2025-05-02';
-- 结果应为0，否则说明有残留

4.2 Monitoring and Logging

Watch CPU, memory, and I/O to ensure they return to normal levels.

Check slow‑query logs for any new performance issues.

Record operation time, data volume, and operator for audit purposes.

4.3 Resource Release (Freeing Physical Space)

Some databases do not automatically reclaim space after massive deletes.

-- MySQL (InnoDB)
OPTIMIZE TABLE tianluo_tab;  -- Rebuild table and free space (use with caution, it locks the table!)
-- PostgreSQL
VACUUM FULL tianluo_tab;

5. Conclusion

If you found this guide helpful, please give it a like, share, and comment.