How do you delete or update millions of rows safely in production?

Summary

In small batches (for example 1,000 to 10,000 rows) by primary-key ranges, each in its own short transaction, with a pause between batches and monitoring of replication lag. Never in one giant statement.

Longer Answer

A single DELETE FROM logs WHERE created_at < '2025-01-01' on 50 million rows holds locks for a long time, bloats undo, writes an enormous binlog event, stalls replicas (the event is applied as one unit) and cannot be stopped cleanly. Batching:

DELETE FROM logs WHERE created_at < '2025-01-01' ORDER BY id LIMIT 5000;
-- repeat until 0 rows affected, sleeping briefly between batches

Walking by primary-key ranges (WHERE id BETWEEN ? AND ?) avoids rescanning. Tools like pt-archiver do this, and for recurring retention, partitioning with DROP PARTITION is better still.

Key Points

• Batch by PK range, short transactions.
• Throttle and watch replica lag.
• pt-archiver for automation.
• Partitioning for recurring retention.