Efficient Strategies for Deleting Massive Data from Large Database Tables

Introduction

Hello, I am Tianluo. This article shares a common interview question: How to quickly delete a massive amount of data from a table that contains tens of millions of rows .

1. Problems of Deleting a Large Table in One Shot

Deleting a tens‑million‑row table in a single statement may cause several serious issues:

Table lock : Long‑running lock blocks other queries and writes, leading to time‑outs and UI lag.

Transaction log explosion : The database must record every delete operation, which can fill the disk.

CPU and I/O spikes : Index updates, constraint checks, and trigger execution consume massive resources.

Master‑slave replication delay : Large delete transactions take time to replicate, causing stale data on replicas.

Rollback difficulty : If the delete fails or is cancelled, rollback may take longer than the original delete.

Examples:

Deleting 10 million rows may take 2 hours, during which users cannot place orders.

Deleting 100 million rows can cause log files to grow to 500 GB, filling the disk.

CPU can jump to 100 % and query latency can increase from 2 ms to 10 s.

Master‑slave delay of 3 hours may produce incorrect reports.

2. Pre‑Delete Rehearsal

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

2.1 Evaluate Data Volume and Choose a Plan

If you need to delete most of the data (e.g., >90 %), the "create‑new‑table + drop‑old‑table" method is faster. If you only need to delete a small fraction (e.g., <10 %), batch deletion is simpler.

2.2 Use Indexes to Optimize Delete Conditions

Make sure the columns used in the WHERE clause (such as date or ID range) have indexes. This speeds up row location and reduces the impact on other indexes.

2.3 Backup Data

Perform a full backup before a large‑scale delete. If you use batch deletion, consider incremental backups to minimise data‑loss risk.

3. Common Bulk‑Delete Solutions

3.1 Batch Deletion

Delete a limited number of rows in each transaction to avoid huge locks.

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

Loop the statement until the table is empty, optionally adding a short pause (e.g., 0.1 s) between batches. You can also turn off autocommit to reduce transaction overhead:

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

3.2 Partitioned Tables

If large deletions are frequent, design the table with partitions (e.g., by date). Deleting an entire partition is a seconds‑level operation:

ALTER TABLE table DROP PARTITION partition_name;

Advantages: no row‑by‑row delete, immediate space reclamation. Requirement: the table must be created with a partition key.

3.3 Create New Table and Drop Old Table

When more than 50 % of rows need to be removed, create a new table that contains only the data to keep, then swap the 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, almost no lock. Cons: indexes and foreign keys must be rebuilt on the new table.

3.4 Use TRUNCATE for Whole‑Table Deletion

TRUNCATE releases the table space directly and is usually faster than DELETE.

TRUNCATE TABLE tianluo_tab;

3.5 Additional Acceleration Techniques

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

Perform deletions during off‑peak hours.

Test the operation on a standby replica first.

Delete cold data after archiving it to another table.

4. Post‑Delete Processing

4.1 Data Validation

Confirm that the target rows have been removed and that remaining data integrity is intact.

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

Randomly sample the remaining rows to ensure no valid data was mistakenly deleted, and verify that dependent services (reports, APIs) still work.

4.2 Monitoring and Logging

Watch CPU, memory, and I/O after the delete to ensure they return to normal.

Check slow‑query logs for any performance regressions caused by missing indexes.

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

4.3 Resource Release (Free Physical Space)

Some databases do not automatically reclaim space. Run the appropriate command to compact the table:

-- MySQL (InnoDB)
OPTIMIZE TABLE tianluo_tab;  -- 重建表并释放空间（谨慎使用，会锁表！）
-- PostgreSQL
VACUUM FULL tianluo_tab;

Conclusion

If you found this article helpful, please give it a like, share, or follow.