本番の大量のデータを、止めずに書き換える (バックフィル) には、どうしますか

1 回で書き換えると何が起きるか

数千万行を 1 本の UPDATE で書き換えると、終わるまで 1 つの長いトランザクションになります。その間、書き換えた行のロックを持ち続けるので、同じ行を更新したい普通のリクエストが待たされます。変更の記録も一度に大量に出るので、複製の遅れが伸び、途中で失敗すると全部が巻き戻って、また最初からになります。

小さく分け、続きから流す

主キーの範囲で数百から数千行ずつに分け、1 つの塊を 1 つの短いトランザクションで流します。終わるたびに、最後に済んだ id を記録します。次の例は、7 行を 3 行ずつ流す動きを小さく書いた物です。

const rows = [];
for (let id = 1; id <= 7; id++) {
  rows.push({ id, name: "user" + id, fullName: null });
}

let lastId = 0;

function runBatch(size) {
  const batch = rows
    .filter((r) => r.id > lastId && r.fullName === null)
    .slice(0, size);
  for (const r of batch) {
    r.fullName = r.name.toUpperCase();
  }
  if (batch.length > 0) {
    lastId = batch[batch.length - 1].id;
  }
  return batch.length;
}

let count;
while ((count = runBatch(3)) > 0) {
  console.log("書いた " + count + " 件 続きは id " + lastId + " より後");
}
console.log("残り " + rows.filter((r) => r.fullName === null).length + " 件");

書いた 3 件 続きは id 3 より後
書いた 3 件 続きは id 6 より後
書いた 1 件 続きは id 7 より後
残り 0 件

ここで大事なのは 2 つです。1 つ目は、続きを「id がいくつより後」で探していることです。OFFSET で何件目からと数えると、先へ進むほど読み飛ばす行が増えて遅くなります。2 つ目は、「まだ値が入っていない行だけ」を対象にしていることです。途中で落ちて同じ塊をもう一度流しても、済んだ行は書き換えないので、結果は変わりません。

SQL で書くと、1 つの塊は次のような形です。

UPDATE users SET full_name = name
WHERE id > 30000 AND id <= 31000 AND full_name IS NULL;

様子を見ながら流す

塊と塊の間に少し間を空け、DB の CPU、ロックの待ち、複製の遅れを見ます。決めた線を超えたら自動で速さを落とすか止め、落ち着いたら続きから流します。利用者の少ない時間帯に寄せるのも手です。

書き換えはアプリの普段の保存の処理を通さず、専用の処理で行います。保存の処理には、メールを送る、外へ通知する、更新日時を変える、といった副作用が付いていることがあり、何千万回も動くと事故になります。

終わった後の確かめと戻し方

終わったら、対象の件数、まだ値が入っていない行の数、いくつかの行の新旧の値を照らします。流している間に普段の書き込みで作られた行に、値が入っているかも見ます。これはアプリの側で新しい行にも書くようにしておく必要があります。

戻せるように、古い列や古い値は、確かめが済むまで消さずに残します。上書きで元の値が消える書き換えなら、先に控え (バックアップ) を取ります。

トレードオフ

小さく分けて間を空けるほど安全ですが、終わるまでの時間は延びます。数時間で終わる量か、数日かかる量かを先に見積もり、急ぐ理由が無ければ安全な側に寄せます。行の少ない表なら、1 回で流して済むこともあります。本番に近い量のデータで先に流して、1 つの塊にかかる時間を測ってから決めます。

話す例は架空の例で構いません。「新しい列を埋めるために 1000 行ずつ流し、複製の遅れが伸びたら止まる仕組みを付け、途中で 1 回落ちたが続きから再開できた」のように話せると伝わります。