The fastest way to delete 100k+ rows with ActiveRecord
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
lib
test
.gitignore
.travis.yml
Gemfile
LICENSE.txt
README.md
Rakefile
delete_in_batches.gemspec

README.md

delete_in_batches

🔥 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: