Bulk INSERTs in Active Record

rubyonrails-core
1

Hey!

I’m thinking about adding bulk inserts to Active Record. My preliminary research indicates that:

  1. Bulk inserts haven’t been raised in an issue (I browsed through all issues, open and closed, with the word “insert”).

  2. Same for PRs labeled with “activerecord”.

  3. I found GitHub - zdennis/activerecord-import: A library for bulk insertion of data into your database using ActiveRecord. (maintained) and ar-extensions/ar-extensions at master · zdennis/ar-extensions · GitHub (unmaintained).

  4. I searched this group for “bulk insert”, “mass insert”, “insert multiple” and “insert many” but it seems the issue hasn’t been discussed.

The scope of work is:

  1. Designing an ActiveRecord API for the feature.

  2. Adding support for bulk INSERTs to arel.

  3. Implementing the API from 1 using the functionality from 2.

  4. Enhancing the documentation.

My question is: are bulk inserts something we’d like to see in ActiveRecord?

Best regards

2

Hey!

I’d love to receive some feedback and guidance. The most important thing for me is: is this something we’d like to have in Active Record? If so, I’d be able to start working on this next week. If not, I’d love to contribute in another way.

Best regards

3

Hi Greg.

I am interested in bulk inserts as active Rails user.

The use cases for that are somewhat rare, but when you need for example import some dictionary from 15 GB XML file to your DB, that will take literally days to complete, and one of the bottlenecks (except XML parsing) will be a lot of INSERTS with waiting for completion of each statement. Bulk INSERTs may really help there.

Sorry, I’m just a user and can’t help you with any guidance in ActiveRecord (really I need such a guidance too in my own work).

With best regards, Andrey Novikov.

4

Sounds useful, but likely a better fit for a standalone gem… at least to start.

5

Sound useful to me too but I agree it should be a gem.

6

Hi,

If you are looking for performance, you should use the database interface directly like COPY for simple case like text, CSV or I think JSON.

Furthermore, you are talking to use a gem for this feature but I discovered that gem https://github.com/jamis/bulk_insert which provides the bulk import. I don’t know if that gem does what you are looking for.

Best regards.

7

I use the active_record_import gem.

It works well and is fairly mature and still maintained. I don’t believe it implements COPY, as that tend to be very DBMS-specific in syntax, but the way it optimizes use of INSERTs is much faster than anything you can do with regular active_record. You can also work with (import) raw columns and arrays of values instead of instantiating active_record model objects, which can save a ton of memory when dealing with many records.