How to Quickly Delete a Large Amount of Data from a Massive Database Table with Tens of Millions of Records

How to Quickly Delete a Large Amount of Data from a Massive Table with Tens of Millions of Records？So, how can we tackle this problem more effectively? 

Let’s break it down from the following perspectives:

• What problems might occur if we delete a large amount of data all at once?

• Pre-deletion rehearsal: Evaluate the data volume, confirm the deletion plan, and check whether deletion conditions are indexed.

• Common strategies for bulk data deletion

• Post-deletion cleanup and follow-up actions

1. What Problems Might Arise from Deleting a Large Amount of Data All at Once?

When dealing with a large table containing tens of millions of records, deleting all data in one go can lead to serious issues such as table locking, transaction log bloating, CPU spikes, and replication lag between master and slave databases.

1.1 Table Locking That Freezes the Business

Problem: The delete operation can lock the table for a long time (especially if wrapped in a large transaction), blocking other read and write operations.

Consequence: Business interfaces time out, web pages freeze, and user experience drops off a cliff.

For example: Deleting 15 million rows takes 2 hours → during this time, users cannot place orders or run queries.

1.2 Transaction Log Explosion

Problem: The database must record all delete operations in the transaction log (for rollback purposes).

Consequence: Log files grow rapidly, potentially filling up the disk and crashing the database.

For instance: Deleting 100 million rows may generate up to 600GB of logs → the disk fills up entirely.

1.3. CPU Spikes and Performance Degradation

Problem: Deletion requires updating all indexes, checking constraints, and executing trigger logic.

Consequence: Heavy consumption of CPU and I/O resources leads to overall database slowdowns.

During deletion, CPU usage may spike to 100%, and normal query latency can jump from 5 ms to 10 seconds.

1.4. Master-Slave Replication Lag

Problem: Delete operations on the primary database need to be replicated to the secondary. Large transactions can cause significant replication delays.

Consequence: Data on the secondary database becomes inconsistent for an extended period, affecting services that rely on it (e.g., reporting, backups).

For example: If deletion on the primary takes 2 hours → the replication delay on the secondary may reach 4 hours, causing reporting data to be incorrect during that time.

1.5. Rollback Challenges

Problem: If the deletion fails midway or is manually canceled, the transaction must be rolled back.

Consequence: Rolling back can take even longer than the deletion itself, and in some cases, may fail and corrupt the data.

For instance: Interrupting a deletion of 60 million records after 1 hour → rollback could take 2 hours, resulting in prolonged business downtime.

2. Pre-Deletion Rehearsal

Before performing the deletion, it's important to do some preparation and confirmation work. This includes estimating the volume of data to be deleted, confirming the appropriate deletion strategy, checking whether deletion conditions are indexed, and ensuring data backups are in place.

2.1 Estimating Data Volume and Confirming the Strategy

Before deletion, assess how much data needs to be removed. The deletion method should match the scale of the data:

• If deleting the majority (e.g., over 90%): It's often faster to create a new table and drop the old one.

• If deleting a minority (e.g., less than 10%): It's more convenient to delete in batches.

2.2 Optimizing Deletion Conditions with Indexes

Make sure that the deletion condition uses indexed fields. This is crucial for performance. When the deletion query is based on indexed columns, the database can locate and remove rows much more efficiently.

If your deletion condition includes fields like dates or ID ranges, ensure those fields are indexed.

Indexes can significantly boost deletion performance, but be cautious—they can also introduce overhead if not handled properly during deletion.

2.3 Considering Data Backup

Before any large-scale deletion, ensure that you have a reliable backup. Deleting data from large tables inevitably carries the risk of data loss, and backups are your safety net.

Pro Tips:

Always perform a full backup before deleting.

If you’re deleting in batches, consider incremental backups between batches to minimize data loss risks.

3. Common Strategies for Deleting Large Volumes of Data

3.1 Batch Deletion

Why use a batch-based approach?

Let’s use a simple analogy:

Suppose you need to move 50,000 bricks to the rooftop, and you have an elevator. The elevator can carry a reasonable number of bricks at a time (let’s say up to 500). You could choose to carry one brick at a time, or move 500 at once. Which do you think would take longer?

Batch deletion works the same way: it avoids overly large single transactions by processing the data in manageable chunks.

DELETE FROM orders_tab WHERE condition LIMIT 2000; -- Delete 2,000 records at a time
Loop and execute until all records are deleted, with a short pause (e.g., 0.5 seconds) between each batch.
We can also disable auto-commit to reduce transaction overhead.

SET autocommit = 0;  -- Manually control transactions
-- Execute deletion here
COMMIT;

3.2 Partition Table Strategy (Should Plan Ahead)

If your use case involves frequent large-scale deletions, consider designing the table as a partitioned table. This allows you to delete entire partitions instead of removing data row by row, significantly speeding up the operation.

For example: partition data by time or range (e.g., in a log table).

-- Instantly delete an entire partition (completes in seconds)
ALTER TABLE table_name DROP PARTITION partition_name;

Advantages: No need to delete rows one by one — the system directly removes the physical files.
Requirement: The table must be pre-designed with a proper partitioning key.
3.3 Create a New Table and Drop the Old One 
If you're deleting more than 50% of the data or only need to keep a small portion, consider using the approach of creating a new table and dropping the old one. 
Steps: 
1）Create a new table — Insert only the data you want to keep:
CREATE TABLE new_table AS 
SELECT * FROM old_table WHERE retention_condition;

2）Rename tables — Quickly swap the new and old tables:
RENAME TABLE old_table TO old_table_backup, new_table TO old_table;

3）Drop the old table — After verifying the data is correct:
DROP TABLE old_table_backup;

Advantages: Extremely fast and almost no table locking. 
Disadvantages: You’ll need to recreate indexes, foreign keys, etc. (but they can be added ahead of time in the new table).
3.4 Use TRUNCATE Instead of DELETE (for Deleting All Table Data)

If you need to remove all data from a table, TRUNCATE is usually more efficient than DELETE because it doesn't delete rows one by one—instead, it directly frees the table's storage space.
-- Efficiently clears all data while keeping the table structure
TRUNCATE TABLE orders_tab;

3.5 Tips to Speed Up Deletion
Use specialized tools: For example, in MySQL, pt-archiver can automate batch deletions with minimal impact.
Operate during off-peak hours: Avoid deleting during business peak times to reduce conflicts.
Test on replica first: Simulate the deletion on a replica database to evaluate potential impact.
Prioritize cold data: Move old or infrequently accessed data to an archive table before deletion.
4. Post-Deletion Tasks
After the deletion, there are several follow-up tasks to perform—such as data validation, resource cleanup (freeing up physical space), and monitoring/logging.
4.1 Data Validation
1）Verify Deletion Scope
Make sure the targeted data has indeed been deleted. For example, if you deleted by time condition:
-- Confirm that all records before the specified date are gone
SELECT COUNT(*) FROM orders_tab WHERE create_time < '2024-01-02';

The result should be 0; otherwise, some records remain.
2) Check Integrity of Remaining Data
Randomly sample the remaining records to ensure no valid data was mistakenly deleted (e.g., records with status = 'active').
3) Validate Related Systems 
Check whether business features that rely on this table are functioning properly (e.g., reports, API endpoints). 
For example: Is the order query page throwing errors due to missing data?
4.2 Monitoring and Logging
1）Monitor Database Performance: 
Observe whether CPU, memory, and I/O usage return to normal levels after deletion.
Check the slow query log to ensure there are no performance issues caused by missing or ineffective indexes. 
2）Log the Operation: 
Record details such as the deletion time, amount of data deleted, and the operator. This is important for auditing and traceability.
4.3 Resource Cleanup (Reclaiming Physical Space) 
Reclaim disk space — some databases don’t automatically release space after deletion: 
-- Rebuilds the table and frees up space (use with caution, it locks the table!)
-- MySQL
OPTIMIZE TABLE orders_tab;  

-- PostgreSQL
VACUUM FULL orders_tab;