Generate comments using existing data for performance testing

data.rake

namespace :generate do
  desc "generate N comments, typically for performance testing"
  task comments: [:connection] do
    n = ENV['N'].to_i
    abort 'specify the number of comments to generate: N=<count>' if n.zero?
    puts "Generating #{n} comments"
    ActiveRecord::Base.connection.execute <<~SQL
      INSERT INTO comments (submission_id, user_id, body, html_body, created_at, updated_at) (
        SELECT
          submission_id,
          user_id,
          body,
          html_body,
          now() - '1 year'::interval * random() AS created_at,
          now() AS updated_at
        FROM generate_series(1, #{n})
        JOIN (SELECT id submission_id, row_number() OVER (ORDER BY random()) AS submissions_row_number FROM submissions) random_submissions
          ON submissions_row_number = generate_series % (SELECT count(*) FROM submissions)
        JOIN (SELECT id user_id,       row_number() OVER (ORDER BY random()) AS users_row_number FROM users) random_users
          ON users_row_number       = generate_series % (SELECT count(*) FROM users)
        JOIN (SELECT body, html_body,  row_number() OVER (ORDER BY random()) AS comments_row_number FROM comments) random_comments
          ON comments_row_number    = generate_series % (SELECT count(*) FROM comments)
      )
    SQL
  end
end

What I haven’t figured out is a good way to use the same randomly generated date value in each row for both created_at and updated_at. For now, I’m just using now() for updated_at, which is sufficient. I could join another subselect containing another generate_series to generate a n rows of random dates, but that would add more time to this already-slow statement (~1 minute for 1 million rows).

Another interesting thing… specifying n creates n rows for small values of n. For larger numbers, however, the number of rows created becomes smaller. e.g.:

• n = 1000 => 979 records created
• n = 2000 => 1957 records created
• n = 1000000 => 977530 records created

generate_series(1, 10) generates 1 through 10 inclusive, so I'm not sure where things are not getting dropped.

Hey @nilbus! Sorry for taking so long to look at this. Busy week!

Your second question first. row_number() is 1-indexed, and %'s lowest return value is zero. So for fewer than n rows, where n is the number of records in the smallest foreign key table, you'll be good, since mod will never return zero. Once you loop back to zero, though, you won't find a corresponding zero coming from row_number(), so the (inner) JOIN will drop records.

Your first question is trickier... I think. One option is to run a second query afterwards: UPDATE comments SET updated_at = created_at which should sweep over all of them and do what you're hoping for. Pretty sure that wouldn't take on the order of a minute. Even at a million records, I'd guess a couple of seconds.

Yeah, that's the best idea I can come up with at the moment. Let me know if you have thought of something better, or if you try this, let me know how the runtime works out!