Optimizing Data Storage in Ruby on Rails: A Seamless Migration from Database to AWS S3

Our application has a table dedicated to storing SENT email messages. This feature enables us to present a record of sent items to users and track changes to email templates. Currently, we store all this data in our database, which accounts for the largest share of our data storage.

I recently realized that this data, which is seldom accessed, occupies a significant amount of storage and contributes to database slowdowns. It became clear that a more efficient solution was required.

After exploring multiple options, I landed on a straightforward resolution: migrate the data from the database to an S3 storage system.

Below is the execution process of this solution.

Our Existing Email Storage Class

Our application initially had a class like this:

# Table name: mail_queue_items
#
# id :integer
# body :text(16777215) <- this is where all the data goes!
# completed_at :datetime
# delivery_code :string(255)
# error :string(255)
# mail_to :string(255)
# msg_reference :string(255)
# run_at :datetime
# status :string(255)
# subject_line :string(255)
# tracked :boolean
# tracked_at :datetime
# created_at :datetime
# updated_at :datetime
class MailQueueItem < ApplicationRecord
end

The data (the body column) is stored in a compressed, encoded format in our database:

sig { params(data: String).void }
  def body=(data)
    if data[/^HTML/]
      super(CompressedColumn.new.dump(data))
    else
      super(data)
    end
  end

  sig { returns(String) }
  def body
    CompressedColumn.new.load(read_attribute(:body))
  end

Storing in S3

This process turned out to be simpler than anticipated. Since we were already using S3 (like everyone else on the planet!), the transition was pretty straightforward.

1. Create a new bucket

For our needs, we didn’t require any complex setup. So we just created a new bucket using the AWS console. It’s worth noting that you might want to enable versioning for added safety in case of accidental overwrites.