PostgreSQLのバルクインサートをRubyからPrepared Statementで実行

タイトルの通りですが、PostgreSQLのバルクインサートをRubyからPrepared Statementで実装しました。

もっとスマートな方法があるんじゃないかと思いつつも、これ以上わからなかったので、きょうやった方法を記録しておきます。

概要

以下のような文字列をRuby側で組み立ててPrepared Statementとして実行します。

INSERT INTO sample1 (id, value) VALUES ($1, $2), ($3, $4), ($5, $6)

準備

PostgreSQL, Rubyのインストールは済ませているものとします。

PostgreSQLの中にサンプルのデータベースを作成します。次にサンプルのテーブルを今回は以下のようなカラム構成で作成します。

CREATE TABLE sample1 (id int, value text);

Rubyはpgというgemが必要です。今回はGemfileに

gem 'pg'

と記述し、

$ bundle install

を実行しました。bundleを使ったので以下でRubyを実行するには

$ bundle exec ruby test.rb

とします。

Rubyのソース

Rubyのソースコードは以下のように書きました。上のほうで定義しているパラメータは適切な値に設定します。

test.rb

require 'pg'

host = "localhost"
port = 5432
user = USERNAME
password = PASSWORD
dbname = "sample"

conn = PG::Connection.new({:host => host,
                           :port => port,
                           :user => user,
                           :password => password,
                           :dbname => dbname,
                          })

# サンプルデータ
inserting_data = [
  [10, "あいうえお"],
  [11, "かきくけこ"],
  [12, "さしすせそ"],
]

column_count = 2

# Prepared Statementの文字列組み立て
prefix_insert_sql = "INSERT INTO sample1 (id, value) VALUES "
prepared_stmt = prefix_insert_sql + ((0 ... inserting_data.size).map do |i|
  "(" +
  ((0 ... column_count).map do |j|
    "$%d" % [column_count * i + j + 1]
  end).join(", ") +
  ")"
end).join(", ")

# Prepared Statementに渡すデータの作成
prepared_stmt_data = inserting_data.flatten(1)

# デバッグ目的の出力
p prepared_stmt
p prepared_stmt_data

# Prepared Statementの準備
conn.prepare("insert", prepared_stmt)

# SQL実行
conn.exec_prepared("insert", prepared_stmt_data)

このこのRubyを実行すると、

$ bundle exec ruby test.rb
"INSERT INTO sample1 (id, value) VALUES ($1, $2), ($3, $4), ($5, $6)"
[10, "あいうえお", 11, "かきくけこ", 12, "さしすせそ"]

ただしくINSERTされているかは以下のSQLで確認できます。

sample=# SELECT COUNT(*) FROM sample1;
 count
-------
     3
(1 row)

Prepared StatementのSQLがデータ量に比例して長くなってしまうので、どれぐらいの大きさまで大丈夫なのか心配です。

サンプルデータを以下のように10万件にしてみたところ、

inserting_data = []
(0 ... 100000).each do |i|
  inserting_data += [[i + 20, "さ"]]
end

以下のようなエラーになりました。

test.rb:55:in `async_exec_prepared': number of parameters must be between 0 and 65535 (PG::UnableToSend)

Prepared Statementに埋め込むパラメータは65535個までという制限のようです。

問題点

データ量に応じて都度Prepared Statementを作らないといけないのが、ダサいと思います。もっといい方法はないのだろうか。