如何在Rails中填充新表格？

Question

我正在创建一个新表，需要根据用户帐户（超过几万个）的数据进行回填，使用以下一次性rake任务。

我决定为每2000个用户创建一个大的INSERT字符串，并执行该查询。

以下是代码的大致样子：

task :backfill_my_new_table => :environment do
    inserts = []
    User.find_each do |user|
        tuple = # form the tuple based on user and user associations like (1, 'foo', 'bar', NULL)
        inserts << tuple
    end

    # At this point, the inserts array is of size at least 20,000
    conn = ActiveRecord::Base.connection
    inserts.each_slice(2000) do |slice|
        sql = "INSERT INTO my_new_table (ref_id, column_a, column_b, column_c) VALUES #{inserts.join(", ")}"
        conn.execute(sql)
    end
end

所以我在想，有没有更好的方法来做这个？我采取的方法有什么缺点？我应该如何改善它？如果我不切片 inserts 数组，而是简单地执行一个带有几十万个 VALUES 元组的单个 INSERT，那么有什么缺点呢？

谢谢！

- boo-urns

---

为什么不使用包含在事务中的MyNewTable方法来加速插入操作？此外，当前实现会让您面临SQL注入的风险。 - Philip Hallstrom

哦，我错过了你一次性进行多个插入的操作。如果您将普通插入操作包装在每1000个事务中，那确实会更快（但不确定速度提升有多少）。 - Philip Hallstrom

Answer 1

根据您使用的PG版本而定，但在大多数批量加载数据到表中的情况下，这是足够的检查清单：

• 尽可能使用COPY而不是INSERT；
• 如果使用多个INSERT，请禁用自动提交并将所有INSERT包装在单个事务中，即BEGIN; INSERT ...; INSERT ...; COMMIT;
• 禁用目标表上的索引和检查/约束条件；
• 禁用表触发器；
• alter table使其成为未记录（自PG 9.5以来，在数据导入后不要忘记打开日志记录），或增加max_wal_size，以便WAL不会被淹没。

对于PG来说，2k切片插入在一个事务中是完全可以的，即使涉及到一些非常复杂的触发器/检查，2万行也不算什么大问题。值得一读的是PG手册中有关批量加载的部分。

更新：还有depesz的一篇有点旧但很棒的文章，摘录如下：

因此，如果您想尽可能快地插入数据，请使用copy（或更好的pgbulkload）。如果由于某种原因无法使用copy，则使用多行插入（8.2中新增！）。然后，如果可以，请将它们捆绑在事务中，并使用准备事务，但通常情况下，它们并没有给您带来太多好处。

- Evgeniy Chekan