CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlmemoryactiverecord
apt
apt

Reputation: 137

Memory tradeoff on ActiveRecord mass create

running Psql 9, Ruby 2.4 and Rails 5.x

Memory wise, which code would be better?

object_with_huge_texts.each do |x|
  MyModel.create(text_col: x.huge_text)
end

versus

values = Array.new
object_with_huge_texts.each do |x|
  values.push("(" << x.huge_text << ")")
end
ActiveRecord::Base.connection.execute(
 "INSERT INTO my_model (text_col) VALUES '#{values.join(",")}'"
)

I understand that the second option will be 1 sql query vs n+1. But will the giant values array cause too big a memory bloat?

Upvotes: 0

Views: 222

Answers (1)

tadman
tadman

Reputation: 211540

It depends on how "huge" this data is. I've used servers with >1TB of memory and even a thrifty $5/mo. VPS still has >1GB in most cases, so it's all relative.

The first version benefits from garbage collection, as each model is created Ruby can discard the data, but there's additional overhead for the model itself.

The second version requires composing a potentially huge SQL string and smashing it in all at once. This could be problematic for two reasons: Your Ruby memory footprint might be too large, or your database might reject the query as being too big. The Postgres default "max query size" is typically 1GB.

If you're doing bulk loads on a regular basis and need it to be efficient you could try using a prepared statement with a placeholder value, then supply different values when executing. This scales quite well and performance is usually comparable to multi-insert style operations so long as there's not a lot of index pressure on the data.

Upvotes: 1

Related Questions