What You Will Learn
- How to use ActiveRecord::Relation#delete_all - A fast way to delete a large batch of rows from a Database table
- How to use ActiveRecord::Batches::BatchEnumerator#in_batches - To prevent from locking your Database
Who This Is For
- Ruby on Rails developers who need to delete records more efficiently.
I am currently working on a Rails project that deals with importing and exporting large amounts of data from excel sheets. The user is able to import and save the data into the application’s Database and also delete the data.
Because some of these deletions can consist of hundreds of thousands of rows, the removal feature must be developed a little differently.
When a User imports/uploads new data into the application, a
is created, along with a new
DataSet record, for every single row from the
imported exec sheet.
DataSet has a
belongs_to relationship to
class DataSet < ApplicationRecord belongs_to :data_uploads end
DataUpload has a
has_many relationship to
class DataUpload < ApplicationRecord has_many :data_sets, dependent: :destroy end
dependent: :destroy. This means that when a
gets deleted/destroyed, all of the associated
DataSet records will be
destroyed as well.
This also loads every associated
DataSet model instance into memory as well.
This will bog down the server if there is a very large association of thousands
The way we solve this is to use the
method performs a
single SQL statement and efficiently deletes all of the records within the
Look at the example below.
upload = DataUpload.last data_sets = upload.data_sets data_sets.delete_all
- DO NOT use this method on more then 5,000 records in a Database table. It will lock the Database for the entire transaction.
#delete_alldoes not load the record, so any callbacks will not be fired. Make sure the application and/or model is not dependent on any pre/post delete processes.
Now we can implement the method in the in the
class DataUpload < ApplicationRecord has_many :data_sets, dependent: :delete_all end
BUT WAIT!! If you were a good reader and saw the first WARNING above you
will notice that if I left the code this way and a particular
instance had an association of more then 5,000
DataSet records… the
Database will lock for the entire transaction. To prevent from this we must
utilize one of the Ruby on Rails 5.0
If you are not use a Rails version that is >= 5.0 then check out the gem delete_in_batches
class DataUpload < ApplicationRecord has_many :data_sets before_destroy :destroy_data_sets private def destroy_data_sets data_sets.in_batches(of: 1000).delete_all end end
First we remove the previous callback
dependent: :delete_all, and replace it
before_destroy callback and pass in the new private method
You will notice that
#in_batches takes an option
:of set to
will limit the amount of records deleted in a single SQL transaction to 1,000.
Thus preventing the Database from locking.