🕌

デフォルト値付きのカラムを新規追加する時の話

に公開
PostgreSQL
DB
tech

既存のテーブルにデフォルト値付きのカラムを追加することになった。
前に先輩エンジニアからそういう時には注意が必要だよと教わったので、改めて調べることにした

※ちなみにPostgreSQLのお話

教わった注意点

デフォルト値付きのカラムを新規追加する時、
既存のレコード全てに対してデフォルト値の書き込みが発生するのでDB負荷が発生するよ
書き込み処理に時間がかかったり、対象テーブルでロックがかかったり...

回避策

DB負荷がかかるからカラム追加しないです。とはならないので...
大体こんな感じでDB負荷を回避する。

  1. デフォルト値なしでカラムを追加する
  2. 対象レコードを分割しつつ、値を更新していく
  3. デフォルト値を設定する

実際に調べてみた

前回と異なるプロジェクトに異動になり、またデフォルト値付きのカラムを追加することになったので
改めてDB負荷について調べてみた

結論

PostgreSQLのバージョンにより挙動が異なる!
PostgreSQL10以前と11以上で、デフォルト値付きのカラム追加の挙動が異なるらしい。
私が教わったのは、どうやらPostgreSQL10以前のバージョンにおける注意点だったっぽい

それぞれの違い

| 観点                         | PostgreSQL 10以前                                       | PostgreSQL 11以降                                      |
|------------------------------|----------------------------------------------------------|----------------------------------------------------------|
| デフォルト値の挿入方式       | 既存レコードに **物理的に書き込み**（テーブルrewrite）   | 既存レコードには **書き込まれず**, 仮想的に補完される     |
| 処理速度（ALTER TABLE）      | 遅い（全レコードの更新処理が走る）                      | 速い（システムカタログに記録されるだけ）                |
| テーブルロックの影響         | `ACCESS EXCLUSIVE` ロックが長時間発生                   | ロック時間は極小で済む                                  |
| SELECT時の値の見え方         | 実際に格納された値が返る                                | デフォルト値が **仮想的に補完されて返る**               |
| データの保持場所             | 各レコードに値が書き込まれている                        | `pg_attribute` に missing 値として保持される            |

PosgreSQL11以上では、既存レコードに対する更新が発生せずカラム追加処理が高速で行われる

処理時間の違い

PostgreSQL10と11でデフォルト値付きのカラムの追加処理時間を比較してみた。
条件：
カラム数：1
既存レコード数：10万件

PostgreSQL 処理時間
10 77.733 ms
11 3.439 ms

pg_attribute

pg_attributeってなんだろうっていうことでドキュメント確認
公式ドキュメント

pg_attributeカタログにはテーブルの列情報が格納されます。 データベース内のすべてのテーブルの各列に対し必ず１つのpg_attribute行があります。 （また、インデックスとpg_classに項目を持つすべてのオブジェクトに対しての属性記述があります。）

atthasmissing

この列は、行から列の値が完全に失われている場合に使われる値を持ちます。 これは、行が作られた後で非不安定(non-volatile)なDEFAULT値を持つ列が追加される際に起こります。 実際に使われる値はattmissingval列に格納されています。

attmissingval

この列は、行から列の値が完全に失われている場合に使われる値を持つ一要素配列を持ちます。 これは、行が作られた後で非不安定(non-volatile)なDEFAULT値を持つ列が追加される際に起こります。 この値はatthasmissingが真のときだけ使用されます。 値がなければその列はNULLになります。

実際にクエリを実行して確認してみた

# missing
SELECT attname, atthasmissing
FROM pg_attribute
WHERE attrelid = 'users'::regclass AND attname = 'is_active';

  attname  | atthasmissing
-----------+---------------
 is_active | t
 
 # 定数
 select attname, attmissingval from pg_attribute where attname = 'is_active';
  attname  | attmissingval
-----------+---------------
 is_active | {t}

これらの情報により、デフォルト値をカラム追加時に挿入しなくてもいい感じに動くようになってる

まとめ

今回はPostgreSQL11以上のバージョンだったので、DB負荷はそこまで意識しなくてもよいことがわかった。

Discussion