バックフィル SQL の確認事項

INSERT INTO new_table (a, b)
SELECT old_table.a, old_table.b
FROM old_table
INNER JOIN parent_table
ON old_table.parent_table_id = parent_table_id
LEFT JOIN new_table
ON parent_table.id = new_table.parent_table_id
WHERE new_table.parent_table_id IS NULL

のような SQL でバックフィルする際、どんなチェックをすれば安心して本番 DB で実行できるか、メモっておきました。

トランザクション張ってないか？

Rails の migration で実行する場合は、INSERT 先テーブルにロックがかかっちゃうから、strong migrations に従って disable_ddl_transaction! でトランザクション外に実行すること。

実行計画はどんな感じか？

SELECT 文だけ切り取って頭に EXPLAIN 付けて実行してみること。

SELECT old_table.a, old_table.b
FROM old_table
INNER JOIN parent_table
ON old_table.parent_table_id = parent_table_id
LEFT JOIN new_table
ON parent_table.id = new_table.parent_table_id
WHERE new_table.parent_table_id IS NULL

返ってくる実行計画は例えばこういう感じで、

 Aggregate  (cost=23.93..23.93 rows=1 width=4)
   ->  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)
         Index Cond: (i < 10)

最初の cost=23.93..23.93 の上限 23.93 が Total Cost で、チューニングは基本この Total Cost を減らしていきたい。試してみるチューニング案としては例えば

条件を JOIN ... ON から WHERE に移すとか
SUBSELECT を減らすとか
Seq Scan が一番重い処理で、減らせるなら減らす

INSERT 先テーブル 🆚 INSERT 元テーブル 🆚 SELECT 文のレコード数が一致してるか？

※ ユーザ操作で INSERT 先テーブル と INSERT 元テーブル 両方にも書き込んでる現行システムが前提。

SELECT 文のレコード数 + INSERT 先テーブルのレコード数 = INSERT 元テーブルのレコード数

つまり

移行予定のレコード数 + 移行済みのレコード数 = 総レコード数学

SELECT COUNT(*) '移行予定のレコード数'
FROM old_table
INNER JOIN parent_table
ON old_table.parent_table_id = parent_table_id
LEFT JOIN new_table
ON parent_table.id = new_table.parent_table_id
WHERE new_table.parent_table_id IS NULL

UNION

SELECT COUNT(id) '移行済みのレコード数' FROM new_table

UNION

SELECT COUNT(id) '総レコード数' FROM old_table

SQL 文にはよるが、レコード数が一致してれば、JOIN がおかしくないと思っちゃっていいでしょう。

※ staging など全環境の DB に対して実行すること！

UNIQUE インデックスに引っ掛からないか？

INSERT 先テーブルに UNIQUE インデックスが張ってある場合は、データの中に重複するデータがないことを確認。

SELECT
  COUNT(*),
  COUNT(DISTINCT old_table.a),
  COUNT(DISTINCT (old_table.a, old_table.b))
FROM old_table
INNER JOIN parent_table
ON old_table.parent_table_id = parent_table_id
LEFT JOIN new_table
ON parent_table.id = new_table.parent_table_id
WHERE new_table.parent_table_id IS NULL

COUNT が一致してれば、重複してないと思っちゃっていいでしょう。

NOT NULL 制約に引っ掛からないか？

INSERT 先テーブルに NOT NULL 制約がかかってる場合は、データの中にヌルがないことを確認。

SELECT COUNT(old_table.a IS NULL OR NULL)
FROM old_table
INNER JOIN parent_table
ON old_table.parent_table_id = parent_table_id
LEFT JOIN new_table
ON parent_table.id = new_table.parent_table_id
WHERE new_table.parent_table_id IS NULL

ゼロであれば、ヌルがないと思っちゃっていいでしょう。

FK に引っ掛からないか？

INSERT 先テーブルに FK が張ってある場合は、関連テーブルに該当レコードがちゃんと存在することを確認。

SELECT old_table.a, old_table.b
FROM old_table
INNER JOIN parent_table
ON old_table.parent_table_id = parent_table_id
LEFT JOIN new_table
ON parent_table.id = new_table.parent_table_id
WHERE new_table.parent_table_id IS NULL

INNER JOIN parent_table してるのがそれ。

本番 DB のダンプに対して実行しても落ちないか？

とりあえず念のため

エラーが発生しないか？
実行時間どのぐらいかかるか？

rastam on rails

東京在住のマレーシア人 Rubyist