Supercharge Your Rails App: Inserting 1 Million Records in 15s

In a world where database performance and efficiency are critical factors in application development, the swift import of a large number of records has become a necessity.

In a previous article, we extensively examined various methods for effectively creating 100,000 records with Ruby on Rails. As a quick recap, we achieved a record insertion time of less than 3 seconds using the insert_all method.

However, I received feedback mentioning an even more efficient method than insert_all. This method is called ActiveRecord-Copy. Based on the feedback I received, this method is expected to revolutionize my perspective on fast insertion.

Today, we are going to shift our focus to a larger scale and find the fastest method for inserting 1 million records into a database!

DataSet

For today's benchmark, we will start with the PostgreSQL database used in my previous article:

# db/schema.rb
create_table "accounts", force: :cascade do |t|
  t.string "first_name"
  t.string "last_name"
  t.string "phone"
  t.string "email"
  t.string "role"
end

As we aim to load 1 million records, using FactoryBot to load data for each benchmark execution would be too time-consuming. Therefore, I preloaded one million records into a file named accounts.csv to load the data at the beginning of the benchmark as follows:

accounts = []
CSV.foreach(Rails.root.join('accounts.csv'), headers: true) do |row|
  next if row['role'] == 'role'
  accounts << row.to_h
end

Now, we can feed these users to our various methods to kickstart the insertion process!

Introduction to ActiveRecord-Copy

ActiveRecord-Copy is a gem that enables the use of the PostgreSQL COPY command. Typically, the COPY command is used to import data from a .sql file into a local database using PostgreSQL directly.

ActiveRecord-Copy facilitates interfacing this command directly within Ruby. The use of COPY minimizes the overhead associated with creating and executing multiple SQL statements for each record, resulting in remarkable speed and efficiency.

To install it in your project:

bundle add activerecord-copy

Here is how I used ActiveRecord-Copy:

columns = users.first.keys + %w[created_at updated_at]
time = Time.now.getutc
Account.copy_from_client(columns) do |copy|
  accounts.each do |account|
    copy << (account.values + [time, time])
  end
end

When using ActiveRecord-Copy, you must specify the columns you will provide. You also need to provide the created_at and updated_at timestamps, which are not automatically set during the COPY process.

Benchmark

In this benchmark, we will compare insert_all, activerecord-import, and our new contender, activerecord-copy.

You can find the benchmark here.

Now, let's compare our three methods for creating 1 million records:

Performance Benchmark

For the pure performance benchmark, we will run the benchmark script ten times, and here are the results:

Here is the distribution of execution times over the ten trials:

We have an intriguing ranking!

🥇 ActiveRecord Copy (~5 times faster than ActiveRecord-Import)

🥈 InsertAll (~1.5 times faster than ActiveRecord-Import)

🥉 ActiveRecord-Import

ActiveRecord-Copy demonstrates significantly better performance than InsertAll and ActiveRecord-Import.

Error Handling Comparison

Beyond raw performance, error handling is a crucial aspect when inserting a large number of records. Let's compare how the three methods behave in terms of error handling.

For these tests, we will add two validations:

• A database validation

# db/migrate/20231101115631_add_unique_on_accounts_email.rb
class AddUniqueOnAccountsEmail < ActiveRecord::Migration[7.0]
  def change
    add_index :accounts, :email, unique: true
  end
end

• A validation in our ActiveRecord model

# app/models/account.rb
class Account < ApplicationRecord
  [ ... ]
  validates :first_name, presence: true, length: { maximum: 50 }
  [ ... ]
end

We will then create a script to introduce errors for each of the methods:

accounts = FactoryBot.attributes_for_list(:account, 100)

# We add an account with a first_name that is too long
accounts << FactoryBot.attributes_for(:account, first_name: 'a' * 51)

# We add a duplicate email in the array
accounts << accounts.first

Here are the behaviors observed when trying to insert records with the different methods:

ActiveRecord-Import is the only method that effectively handles model validations. This difference can partially explain why its processing time is significantly longer than the other methods.

I am also surprised that InsertAll does not create a record when the error originates from the database. Given that the method does not raise any errors, the expected behavior would have been database insertion.

Interpretation

After analyzing the benchmark results, we can draw the following conclusions regarding the performance and error handling of the three insertion methods:

1. InsertAll: The InsertAll method is highly efficient, taking around 48.71 seconds to insert 1 million records. It handles database errors but ignores model validation errors.

2. ActiveRecord-Import: This method offers reasonable performance, taking around 73.42 seconds to insert 1 million records. Importantly, this method actively handles database errors (PG::UniqueViolation) and model validation errors. This approach results in slower insertion but allows for comprehensive validation handling.

3. ActiveRecord-Copy: ActiveRecord-Copy stands out in terms of performance, inserting 1 million records in just 14.81 seconds. However, concerning error handling, it behaves similarly to InsertAll, as it handles database errors (triggering a PG::UniqueViolation in our tests) but does not address model validation errors.

Recommendations

Based on the benchmark results and your specific project priorities, here are my recommendations:

• Best Performance - ActiveRecord-Copy: If you seek the best performance for rapid insertion of large amounts of data, ActiveRecord-Copy is the top choice. It is approximately five times faster than ActiveRecord-Import. The only drawback is that it is exclusively available on PostgreSQL.
• Model Validation - ActiveRecord-Import: If handling model validation errors is essential for your project, ActiveRecord-Import is a viable option. It is important to note that this method offers extensive customization in its usage. I encourage you to consult the documentation, as it allows for advanced features like recursive model creation and skipping model validations for faster insertion.
• In the worst case - InsertAll: If you are not using PostgreSQL and do not require model validations, InsertAll is a solid choice. It provides good performance while handling database errors, even though it ignores model validation errors.

Conclusion

In the demanding world of application development, database performance plays a crucial role. Rapid data insertion is a common challenge, and our benchmark has highlighted significant differences between the three methods examined.

ActiveRecord-Copy stands out as the most efficient and memory-friendly method. However, ActiveRecord-Import is the only method that actively handles model validation errors.

Ultimately, the choice of the method depends on your priorities and project requirements. Each of these methods has its advantages and disadvantages, and the decision should be made based on your specific needs in terms of performance, memory usage, and error handling.

Personally, I am thrilled to have discovered ActiveRecord-Copy and look forward to applying it to my projects!

Learn More

• Repository link used in this article: https://github.com/just-the-v/fastest-way-to-insert-in-rails/tree/activerecord-copy
• PostgreSQL COPY documentation: https://docs.postgresql.fr/12/sql-copy.html
• ActiveRecord-Import documentation: https://github.com/zdennis/activerecord-import
• Video demonstration of COPY in PostgreSQL: https://www.scalingpostgres.com/tutorials/fast-postgresql-data-loading-using-ruby/
• ActiveRecord-Copy documentation: https://github.com/pganalyze/activerecord-copy
• Link to my article on how to efficiently insert 100k records: https://dev.to/justthev/performance-guide-to-create-100k-records-in-less-than-3s-using-ruby-on-rails-3k07