In our application we run a background processor called Sidekiq which allows us to run hundreds of idempotent jobs against the database in parallel.

We have one job which can add up to 3,000 inserts into a very large table (60 attributes, 7 indexes, 1.5 billion records). Currently we have this set up to do direct loads.

CSV data is put into an array (not written to file) and fed to STDIN like so:

  ActiveRecord::Base.connection_pool.with_connection do |conn|
    rc = conn.raw_connection
    rc.copy_data "COPY apps FROM STDIN WITH CSV HEADER" do
      rows.each do |line|
        rc.put_copy_data line

To generate the data for this direct load, we have to hit the table sequence once for each row. If 200 of these jobs are running simultaneously, and they can each have 3,000 records, that seems like a lot.

Is this strategy a bad idea, and all other things being ideal would we experience better throughput by doing individual inserts instead?

  • What do you mean with "hit the table sequence once for each row."? – a_horse_with_no_name Mar 14 '19 at 21:01
  • values << ActiveRecord::Base.connection.execute("select nextval('apps_id_seq'::regclass)").first['nextval'] – AKWF Mar 14 '19 at 21:07
  • And what is your question then? – a_horse_with_no_name Mar 14 '19 at 21:08
  • Is the direct load that we're doing better than doing individual insert statements? – AKWF Mar 14 '19 at 21:09
  • 1
    You can get 3000 sequence number with one query for each job by using something like ActiveRecord::Base.connection.execute("select nextval('apps_id_seq') from generate_series(1, 3000)").first['nextval'] – Sahap Asci Mar 14 '19 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.