PostgreSQLにおいてALTER文実行前に留意すべき点

はじめに

PostgreSQLデータベースのスキーマ変更やインデックス追加を行う際は、ALTER文実行によるテーブルロックとその影響について事前考慮しておく必要があります。

2022年7月現在、MySQL 5.6以降では後述するオンラインDDLによってALTER文実行時でも多くの場合で内部ロックを獲得せずにスキーマ変更などが行えますが、PostegreSQL 14においては強いロックを獲得します。

ゆえに、マイグレーション時にテーブルレベルロックが獲得されることでアプリケーションが実行するクエリの結果が長時間返せず、ユーザーからアプリケーションが停止して見えたり、サーバーがタイムアウトしてしまう可能性があります。

本記事では、上記問題にできる限り対応すべく、PostgreSQLにおけるALTER文実行前に事前注意しておくべき点を列挙します。

オンラインDDLとはなにか

MySQL 5.1以前において、ALTER文実行時はテーブルの共有ロック（他のセッションからの参照を許可し変更をブロックするロック）を獲得していました。

ロックした上で古いテーブルのデータを新しいテーブルにコピーし、最終的に置き換えて古いテーブルを破棄する方法であり、テーブルのレコードが大きいほどテーブル変更をブロックする時間も長くなる点がネックでした（一応、MySQL5.5などではFast Index Creationという機能が追加されており、DDL実行時にテーブルコピーせずにセカンダリインデックスを変更できるようになったものの、依然変更時はテーブルロックを獲得していました）。

MySQL 5.6以降はオンラインDDL操作が可能になり、多くのDDL実行時にテーブルコピーおよびテーブルレベルロックが不要になりました。

https://dev.mysql.com/doc/refman/5.6/ja/innodb-create-index-overview.htmlの「表 14.5 DDL 操作のオンラインステータスのサマリー」におけるインプレース?列を参照すれば、一部のDDL（カラムのデータ型変更など）を除いてDDL実行時にテーブルロックが不要になったことがわかります。

問題は、PostgreSQLにこのオンラインDDLに該当する機能が存在しないことです。PostgreSQLにおいてはほとんどのALTER文実行時に、共有ロックよりも強いロックであるACCESS EXCLUSIVEロックという占有ロックを獲得して他セッションによるテーブル参照すらブロックしてしまいます。

PostgreSQL 14のロック種別については、PostgreSQL: Documentation: 14: 13.3. Explicit Lockingを参照してください。

ALTER文実行前に留意すること

PostgreSQLで実行可能なALTER文によって定義変更できる対象がいくつもあります。そのうち、ここでは比較的DDL操作機会が多いと思われる、テーブルのカラム定義更新とインデックス更新時における留意事項と回避策についてまとめました。

常に以下の回避策を採用する必要はありませんが、更新対象となるテーブルのレコード件数が多い場合は検討をおすすめします。

カラム定義更新

DEFAULT制約のあるカラムを追加する

DEFAULT制約を指定した場合、カラム追加時に既存データすべてに対して更新を行うためテーブルロックの時間が長くなってしまいます。この場合、以下のようにDDLを分割することでロックする時間を短縮できます。

-- まずDEFAULT制約なしでカラム追加
ALTER TABLE [テーブル名] ADD COLUMN [カラム名] [データ型];

-- DEFAULT制約を追加
ALTER TABLE [テーブル名] ALTER COLUMN [カラム名] SET DEFAULT [デフォルト値];

NOT NULL制約のあるカラムを追加する

下記のようにNOT NULL制約を付与してALTER COLUMNを実行してしまうとテーブルがフルスキャンされ、既存レコードに対してデフォルト値が設定可能かチェックしてしまいます（NOT NULL制約を付与するには既存レコードにデフォルト値を設定する必要があります）。

ALTER TABLE [テーブル名] ALTER COLUMN [カラム名] SET NOT NULL;

これを回避する方法として、まず既存テーブルをコピーして新規テーブルを作成し、新規テーブルのカラムに対してNOT NULL制約を追加します。その後、既存テーブルを削除して新規テーブルをリネームします。

この方法であればACCESS EXCLUSIVEロックを獲得せずにNOT NULL制約があるカラムを追加できますが、いかにデータ整合性を保証するか事前検討する必要があり、これは別種の課題をもたらします。

別の方法として、NOT NULL制約でなくCHECK CONSTRAINT制約を用いる方法もあります。PostgreSQL 14のドキュメントでは以下のように説明されています。

A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit not-null constraint is more efficient.

つまりNOT NULL制約とCHECK CONSTRAINT制約は機能的に同一なようです（パフォーマンス観点など、一部差異は存在するので事前認識はしておいた方が良いです）。ゆえに、下記のようにDDLを分割実行し実質NOT NULL制約をカラムに付与できます。

-- 新しい制約をテーブルに追加する
-- NOT VALIDオプションを付与することで、既存レコードが制約を満たすかどうか判定するためのフルスキャンをスキップする
ALTER TABLE [テーブル名] ADD CONSTRAINT [制約名] CHECK ([カラム名] IS NOT NULL) NOT VALID;

-- 制約を有効化する
ALTER TABLE [テーブル名] VALIDATE CONSTRAINT [制約名];

インデックス更新

CONCURRENTLYオプションをつけることでロックを獲得せずインデックスを追加できますが、実行時間は長くなってしまいます。

おわりに

ビジネス向けかつ日本国内向けのサービスであればJSTの深夜帯などダウンタイムにデータベースのマイグレーションを実行できそうですが、24/365恒常的にアクセス量が安定しているサービスの場合、いかに短いロックでDDLを完了できるかがシビアな問題になるでしょう。

上記の方法を実験するため、本番と同等のデータ量を保存したDBを用意した開発環境を利用するのも良い方法だと思います。

参考文献

漢(オトコ)のコンピュータ道: ALTER TABLEを上手に使いこなそう。

PostgreSQLで安全にテーブル定義を変更する - LCL Engineers' Blog

Safe Operations For High Volume PostgreSQL

PostgreSQLのNOT NULL制約のロックを最小化して高速化する（翻訳）｜TechRacho by BPS株式会社

gRPC関連の読み物

はじめに

gRPCをゼロから学びたい人向けのドキュメント、ブックリストを備忘録的に記載しました。

適宜更新するかもしれません。

紹介一覧

公式

https://grpc.io/

基本。

スターティングgRPC

www.amazon.co.jp もともと同人誌として発行されていた書籍の商業版。

gRPCの各RPC方式を実装を交えて紹介していく形式で、gRPCサーバやクライアントの実装例がGoやRailsで掲載されています。Goでバックエンドを書いている方に特におすすめです。

RESTと比較したgRPCのpros/consも整理されていて勉強になると思います。

WEB+DB PRESS Vol.110

www.amazon.co.jp スターティングgRPCを通読する時間がない場合、入門用の読み物として先にこちらの特集を一読することをおすすめします。

具体的なプロダクトのgRPCサーバを実装していく過程が、Goの実装例を併記して紹介されています。

スターティングgRPCでは実装（呼び出し）例はあるものの説明が省略されているgRPCのGraceful shutdownに言及が（少しだけど）あったりして良いです。

gRPC Internal - gRPC の設計と内部実装から見えてくる世界

www.wantedly.com スターティングgRPCを一読した方は、gRPCの設計原則についての解説や、C-coreの説明あたりから読み進めると良いと思います。HTTP/2の勉強にもなります。

データ指向アプリケーションデザイン

www.amazon.co.jp 本書でも少しgRPCに触れています。直接的にgRPCに関連する内容としては、「4.2.2 サービス経由でのデータフロー：RESTとRPC」の一読をおすすめします。

gRPCに固有の課題ではないものの、それ以上に8章「分散システムの問題」で論じられるタイムアウトの問題などが興味深く読めるかと思います。同様の内容、つまりローカル（関数）呼び出しとは異なるリモート呼び出しの複雑さについては、Sam Newman『マイクロサービスアーキテクチャ』の4章「統合」にも記述がありますので、ぜひ併読してみてください。

gRPC: Up and Running

www.amazon.co.jp 2022年7月時点で英語でしか読めないのですが、分量も多くないので比較的読みやすい英書です。

gRPC以外の通信プロトコルについての解説も多く、Chapter7ではDockerやKubernetesへのデプロイについて記載があり、流通書籍の中では実用面において白眉だと思います。

一方、2020年に刊行された書籍であり実装例が古くなってしまっているため、その点は差し引いて読んでください。

おわりに

どれか一冊選ぶなら、とりあえずスターティングgRPCを読んでおけばいいかな……。

google_project_iam_bindingでtfファイルをstraightforwardに書く

概要

TerraformでGCP IAM設定をする際、google_project_iam_bindingの利用には注意が必要だ。

bindingリソースの挙動を把握していないと、新規にロールを有効化した際に既存のbindingリソースから意図せずロールを剥奪してしまう可能性があるからだ。

では、google_project_iam_bindingは使わないほうが良いのかーというと、別にそうでもないという話。

Terraform で GCP IAM 設定

google_project_iam_bindingは、任意のロールとその権限を有するアカウントとの対応関係を1:1でマッピングする。そのため、誤って同一ロールに対してbindingリソースを複数作成すると、新しく作られたリソースが優先され、古いリソースからはロールが剥奪されてしまう。

一方、google_project_iam_memberはその対応関係を1:nでマッピングできる。ゆえに、memberリソースを追加しても既存のリソースに影響を及ぼさず、複数のmemberリソースすべてに対してロールが有効化される。

この点は下記の記事に詳しい。 zenn.dev

google_project_iam_bindingを使うメリット

上記のような認識から、なんとなくgoogle_project_iam_bindingは指定せず、google_project_iam_memberを使えばいいじゃん……と思わないだろうか。

だが、bindingを指定することで整理されたtfが書ける。というのも、memberとbindingではロールを指定する方法が若干異なるためだ。

memberはひとつのリソースに対して、下記のようにメールアドレスが1件ずつしか指定できない。だから、同一のロールを複数のアカウントに紐付けたい場合は、memberリソースを繰り返し指定する必要があり、冗長な書き方を強いられる。

resource "google_project_iam_member" "project" {
  project = "hoge-project-id"
  role    = "roles/editor"
  member  = "user:taro@example.com"
}

1件ずつロールとアカウントの紐付けを管理し、かつ管理外の設定（他のmemberリソース）と共存できる……というのがgoogle_project_iam_memberのpolicyなので、意味のある冗長さではあるのだが。

一方、bindingはメールアドレスをstringのスライスで書くため、下記のように複数行にわたって指定できる。

resource "google_project_iam_binding" "project" {
  project = "hoge-project-id"
  role    = "roles/editor"

  members = [
    "user:taro@example.com",
    "user:hanako@example.com",
  ]
}

同一のロールを複数のアカウントに紐付けたい場合、memberよりもbindingを使うほうがstraightforwardなtfファイルが書ける。 bindingリソースのAuthoritativeな挙動を認識していれば、単純に書く分にはbindingを使うで良いと思う。

参考

memberやbindingにはそれぞれメリデメがあるので、悩んだら下記記事など参考にすると良さそう。

scrapbox.io