ALTER TABLE で構造を変更する

動いているテーブルに、列を 1 本足したい

テーブル設計は一度作って終わりにはなりません。「投稿に公開範囲を持たせたい」「使わなくなった列を消したい」という要求は、サービスが伸びるほど増えます。すでにデータが入っているテーブルの形を変える命令が ALTER TABLE で、アプリ開発の現場では「マイグレーション」と呼ばれます。

やっかいなのは、その間もテーブルが使われ続けていることです。走っている車のタイヤを、走ったまま替えるようなものだと思ってください。

いきなり NOT NULL を付けると失敗する

たとえば posts テーブルに公開範囲の列を足すとします。最初から NOT NULL を付けたくなりますが、すでに入っている行に何を入れるかが決まっていないので、この ALTER は失敗します。

そこで 3 回に分けます。

-- 1. まず NULL を許す形で足す。既存の行は NULL のまま
ALTER TABLE posts ADD COLUMN visibility VARCHAR(20);

-- 2. 既存の行を埋める
UPDATE posts SET visibility = 'public' WHERE visibility IS NULL;

-- 3. 埋め終わってから締める
ALTER TABLE posts ALTER COLUMN visibility SET NOT NULL;

1 と 3 の間もアプリは動き続けているので、新しく入る行にも値が入るよう、書き込み側を先に直しておきます。この順番を守ると、本番を止めずに列を足せます。

ADD COLUMN に DEFAULT を付けて一発で済ませる書き方もありますが、既存の全行にその値を書き込む処理が走ります。行が少ないうちは一瞬で終わり、多くなると 2 の UPDATE を一度に流したのと同じ重さになります。1 万行なら気にせず、100 万行なら分けて考える、くらいの感覚で構いません。

列を消すのは、足すより怖い

列を消した瞬間、その列を読んでいたクエリが一斉に落ちます。

ALTER TABLE posts DROP COLUMN legacy_flag;

打つ前に、アプリのコードと SQL を検索して参照がゼロであることを確かめます。「たぶん使っていない」で消すと、月次バッチだけが翌月に落ちる、という気づきにくい壊れ方をします。

列名の変更も同じ理由で危険です。

ALTER TABLE posts RENAME COLUMN body TO content;

これを一発で流すと、旧名で動いている古いアプリと新名を期待する新しいアプリが同時に走る瞬間に、片方が壊れます。実務では「新しい列を足す」「両方に書く」「読み先を新しい列へ移す」「旧列を消す」の 4 回に分けます。列を足すときの 3 段階と発想は同じで、切り替えの瞬間を作らないようにしています。

解説

ALTER TABLE は軽い操作に見えますが、内容によってはテーブル全行の検査や書き換えが走ります。1000 万行のテーブルに流して 30 分ロック、は珍しくありません。行数が多いテーブルでは、実行前に所要時間を見積もるところから始めます。