touch: true, dependent: :destroy, $!

09 February 2016 on rails. 5 minutes

A little while ago our team was faced with an issue: certain DELETE requests were taking extremely long to process, to the tune of over 1 minute! This post explores what can happen when you have dependent: :destroy in a has_many relationship

For this example I’ve extracted any domain specific implementation to a hypothetical application which manages a library:

• A library has many books.
• A book belongs to 1 library.
• A book belongs to 1 donor (the person who donated the book)
• When we update a book, we should update the updated_at timestamp for the donor.
• When we destroy a library, we should destroy all of the books in the library.

In this example we have a library with around 2 000 books. One day I decide to delete the library. What happens in the background?

2.1.4 :019 > Library.find(6).destroy Library Load (0.2ms) SELECT `libraries`.* FROM `libraries` WHERE `libraries`.`id` = 6 LIMIT 1 (0.1ms) BEGIN Book Load (1.3ms) SELECT `books`.* FROM `books` WHERE `books`.`library_id` = 6 SQL (0.4ms) DELETE FROM `books` WHERE `books`.`id` = 5001 Donor Load (0.2ms) SELECT `donors`.* FROM `donors` WHERE `donors`.`id` = 1 LIMIT 1 SQL (0.2ms) UPDATE `donors` SET `donors`.`updated_at` = '2017-03-10 05:04:22' WHERE `donors`.`id` = 1 # And so on, for each of the 2000 books...

In total, this would require 4 db calls per book, plus 1 to load the Library, and 1 to delete it - a total of 8002 db calls for our example!

This is because of the dependent: :destroy from library to book - which will load and execute all callbacks for each associated book individually.

Solution

How we ended up fixing this was by reaffirming our hatred of callbacks, and by moving our deletion logic to a service. This way you can perform your business logic without letting Rails load every associated record and do the logic one-by-one. This can be done by switching out destroy for delete_all on has_many associations, and moving any other callback logic into the service. In this example, it may look something like:

Models:

class Book < ActiveRecord::Base belongs_to :library belongs_to :donor
end class Library < ActiveRecord::Base has_many :books, dependent: :delete_all
end class Donor < ActiveRecord::Base has_many :books
end

Service

class LibraryDeletionService def destroy_library(library) Library.transaction do # Process the associations in whatever size batches is appropriate. Default 1000. library.books.find_in_batches do |batch| donor_ids = batch.map(&:donor_id).uniq Donor.where(id: donor_ids).update_all(updated_at: Time.now) end library.destroy end end
end

SQL when destroying a library now:

2.1.4 :013 > LibraryDeletionService.new.destroy_library(Library.last) Library Load (0.2ms) SELECT `libraries`.* FROM `libraries` ORDER BY `libraries`.`id` DESC LIMIT 1 (0.1ms) BEGIN Book Load (1.0ms) SELECT `books`.* FROM `books` WHERE `books`.`library_id` = 9 ORDER BY `books`.`id` ASC LIMIT 1000 SQL (0.2ms) UPDATE `donors` SET `donors`.`updated_at` = '2017-03-10 05:24:19' WHERE `donors`.`id` IN (/SOME BATCH OF DONORS/) Book Load (0.2ms) SELECT `books`.* FROM `books` WHERE `books`.`library_id` = 9 AND (`books`.`id` > 9000) ORDER BY `books`.`id` ASC LIMIT 1000 SQL (1.7ms) DELETE FROM `books` WHERE `books`.`library_id` = 9 SQL (0.2ms) DELETE FROM `libraries` WHERE `libraries`.`id` = 9 (0.7ms) COMMIT

Much better! We’re performing our business logic (updating the timestamp on donor) using manageable batches of books. This will cut down the number of db calls significantly, and since we’re performing these operations on (what should be) primary keys, it will be pretty fast.

Hopefully this can be useful to someone on their next rails project.

Cheers