How do you delete millions of rows safely?

SQL changing data safely · Freshers and experienced

Model answer

Delete in batches (DELETE ... LIMIT or by id ranges) with short transactions to avoid long locks and a huge transaction log. Consider partitioning and dropping old partitions, or TRUNCATE when removing everything. Run off-peak and monitor replication lag.

Learn this topic

INSERT, UPDATE and DELETE: Change Data Safely in the course SQL for Beginners: Learn Databases Step by Step.

Sign in to add your own answer.

More questions

• How do you insert, update and delete rows?
• How can you make UPDATE and DELETE safer?
• What is the difference between INSERT ... SELECT and INSERT ... VALUES?
• What is an upsert?
• How do you add a column or change a table?
• What are default values and auto-increment/identity columns?