delete_in_batches

:fire: The fastest way to delete 100k+ rows with ActiveRecord

Note: This gem is not needed in Rails 5+. Instead, you can do:

Tweet.where(user_id: 1).in_batches(of: 10000).delete_all

Build Status

Slow

Tweet.where(user_id: 1).delete_all
# DELETE FROM tweets WHERE user_id = 1

The database performs the delete in a transaction - either all the records are deleted (query completes) or none are, due to multiversion concurrency control.

Fast

Tweet.where(user_id: 1).delete_in_batches
# DELETE FROM tweets WHERE id IN (SELECT id FROM tweets WHERE user_id = 1 LIMIT 10000)
# DELETE FROM tweets WHERE id IN (SELECT id FROM tweets WHERE user_id = 1 LIMIT 10000)
# ...

Important: Be sure to test your query before running it in production

Change the batch size

Tweet.where(user_id: 1).delete_in_batches(batch_size: 50000) # defaults to 10000

Show progress

Tweet.where(user_id: 1).delete_in_batches do
  puts "Another batch deleted"
end

Works with associations

user.tweets.delete_in_batches

To delete all rows in a table, TRUNCATE is fastest.

ActiveRecord::Base.connection.execute("TRUNCATE tweets")

Installation

Add this line to your application’s Gemfile:

gem "delete_in_batches"

And then execute:

bundle

Contributing

Everyone is encouraged to help improve this project. Here are a few ways you can help: