PostgreSQL SQL レビュー & スタイルガイド

背景

ここに挙げる多くのルールは Bytebase の SQL レビューで設定できます。

PostgreSQL は非常に強力なデータベースですが、PostgreSQL をうまく使うにはバックエンドエンジニア、Ops/SRE、DBA の連携が欠かせません。

本記事では、PostgreSQL を使ううえでのベストプラクティスをまとめました。本番運用のハードルを下げる助けになれば幸いです。インターネット上にはいくつかの PostgreSQL SQL レビューガイドが存在しますが、本ガイドは次の点でユニークです。

実践的。教科書的なガイドではなく、実運用の経験に基づき、巨大なインターネット企業で採用されている内容。
網羅的。ガイドは以下をカバーします。

命名ルール

一般的な命名

必須

このルールはすべてのオブジェクト名 — データベース名、テーブル名、カラム名、関数名、ビュー名、シリアル名、エイリアスなど — に適用される。
オブジェクト名は小文字、アンダースコア、数字のみを使用する。先頭は小文字でなければならず、通常のテーブルは _ で始めてはならない。
オブジェクト名の長さは 63 文字を超えないこと、命名は snake_case に従う。
SQL 予約語の使用は禁止。予約語の一覧は SELECT pg_get_keywords(); で取得できる。
ドル記号 ($) は禁止、非英字も禁止、pg で始めない。
マイナーな単語や略語は避ける。

データベース命名

必須

データベース名はアプリケーションやサービスと一致させ、識別可能な英単語とする。
<biz>- で始め (<biz> は具体的なビジネスライン名)、シャーディングされたデータベースなら -shard で終える。
複数の区切りは - で連結する。例: <biz>-chat-shard、<biz>-payment など。区切りは合計 3 つを超えない。

ロール命名

必須

su データベースは 1 つだけ存在する: postgres。
ストリームレプリケーション用のユーザーは replication と命名する。
すべてのデータベースは標準で 3 つのベースロールを持つ: <biz>-read、<biz>-write、<biz>-usage — それぞれ全テーブルに対する読み取り、書き込み、関数実行権限を持つ。
本番ユーザー、ETL ユーザー、個人ユーザーは、対応するベースロールを継承して権限を得る。
より細かい権限が必要なら別ロールを用意する。これはビジネスごとに変わる。

スキーマ命名

必須

ビジネスは <*> をスキーマ名として使う (<*> はビジネス名)。スキーマ名は search_path の先頭に設定すること。
dba、monitor、trash は予約スキーマ名。
パーティションスキーマの命名規則は rel_<partition_total_num>_<partition_index>。
例外を除き、他のスキーマにオブジェクトを作らない。

テーブル命名

推奨

明瞭さが最優先。曖昧な略語や過度に長い名前は避け、一般的な命名規則に従う。
テーブル名は複数形の名詞を使うか、過去の慣例に合わせる。ただし複数形が不規則になる単語は避けるべき。
ビューは v_、マテリアライズドビューは mv_、一時テーブルは tmp_ を接頭辞にする。
継承テーブルやパーティションテーブルは親テーブル名を接頭辞にし、子テーブルの特徴 (ルール、パーティション範囲など) を接尾辞にする。

索引命名

推奨

可能であれば索引を作成するときに索引名を指定し、PostgreSQL のデフォルト命名規則と整合させる。再実行で重複索引を作らないため。
主キーの索引は _pkey で終わり、ユニーク索引は _key、EXCLUDED 制約用の索引は _excl、通常の索引は _idx で終える。

関数命名

推奨

動作種別を示すため、select、insert、delete、update、upsert のいずれかで始める。
重要なパラメータは関数名に組み込める。例: _by_ids、_by_user_ids を接尾辞にする。
オーバーロードを避け、同名関数は基本的に 1 つに保つ。
BIGINT/INTEGER/SMALLINT などの整数型でのオーバーロードは禁止。呼び出し時に曖昧になる。

カラム命名

推奨

予約システムカラム名 (oid、xmin、xmax、cmin、cmax、ctid など) を使わない。
主キーカラムは通常 id と命名するか、id を接尾辞にする。
作成時刻は通常 created_time、更新時刻は updated_time と命名する。
ブール値カラムには is_、has_ などの接頭辞を推奨する。
新規追加するカラム名は既存の命名規則と整合させる。

変数命名

推奨

ストアドプロシージャや関数内の変数は、位置パラメータではなく名前付きパラメータを使う。
パラメータ名がオブジェクト名と衝突する場合は、パラメータの後ろに _ を加える (例: user_id_)。

推奨

オブジェクトには可能な限りコメント (COMMENT) を付け、簡潔な 1 行コメントにする。
オブジェクトのスキーマや内容の意味が変わったら、必ずコメントを更新する。

設計ルール

文字セットは UTF8 必須

必須

UTF8 のみを許可する。

キャパシティプランニング

必須

テーブルのパーティション化を検討する目安は、単一テーブルで 1 億行超、またはサイズ 10GB 超。
データベースのシャーディングを検討する目安は、単一テーブルで 1TB 超、単一データベースで 2TB 超。

ストアドプロシージャを濫用しない

必須

ストアドプロシージャはトランザクションのカプセル化、同時実行衝突の削減、ネットワーク往復の削減、返却データの削減、少量のカスタムロジックの実行に向く。
複雑な計算や、退屈で頻繁な型変換やラッピングには向かない。

ストレージと計算を分離する

必須

WGS84 から他座標系への SQL 変換のような、計算負荷の高い不要なロジックをデータベースから外す。
例外: データ取得とフィルタリングに密接に紐づく計算ロジック (例: PostGIS の幾何計算) はデータベースで許容する。

主キーと識別カラム

必須

すべてのテーブルは識別カラムを持つこと。原則として主キーが必要で、最低限でも NOT NULL のユニーク制約は必要。
識別カラムはテーブル内の任意の行を一意に識別するために使われ、論理レプリケーションや多くのサードパーティツールがこれに依存する。

外部キー

必須

外部キーの使用は推奨せず、アプリケーション層で強制することを推奨する。外部キーを使う場合は、参照側に SET NULL、SET DEFAULT、CASCADE などの動作を必ず設定し、カスケード操作には注意する。

ワイドテーブルは慎重に使う

必須

15 カラムを超えるテーブルはワイドテーブルとみなし、縦分割を検討して同じ主キーで主テーブルと相互参照する。
MVCC の仕組み上、ワイドテーブルは書き込み増幅が顕著になるため、頻繁な更新は避ける。

デフォルト値

必須

デフォルト値を持つカラムには DEFAULT 句を必ず追加して値を指定する。
DEFAULT 句では関数を使って動的にデフォルト値を生成できる (例: 主キージェネレーター)。

ゼロと NULL

必須

カラムのセマンティクスがゼロと NULL を区別しないなら、NULL を許可せず、カラムに NOT NULL 制約を付ける。

一意性はデータベース制約で強制する

必須

一意性はデータベースが保証すべきであり、一意なカラムには UNIQUE 制約を付ける。
EXCLUDE 制約は一般化された UNIQUE 制約で、低頻度更新のシナリオでデータ整合性を担保するのに使える。

整数オーバーフローに注意

必須

SQL 標準は符号なし整数を定義しない。INTMAX を超えるが UINTMAX 以下の値は、より大きな型に格納する必要がある。
INT64MAX を超える値を BIGINT カラムに入れない。負数にオーバーフローする。
整数型を主キーにしてテーブルに挿入衝突が頻発する場合、オーバーフローに注意する。

タイムゾーンを統一する

必須

TIMESTAMP で時刻を保存し、UTC タイムゾーンを使う。
時刻型の入出力は ISO-8601 形式 (2006-01-02 15:04:05) で統一し、DMY と MDY の取り違えを防ぐ。
GMT/UTC 0 時間帯の標準時刻には TIMESTAMPTZ を使う。

不要な関数を片付ける

必須

使われなくなった関数は速やかにオフラインにし、新規関数との衝突を避ける。

主キーの型

推奨

主キーは通常整数を使う。BIGINT を推奨し、最大 64 バイトの文字列も許容する。
主キーは Serial で自動採番してよい。デフォルトの next_id() ジェネレーター関数の使用を推奨する。

適切なカラム型を選ぶ

推奨

一般的なテキスト型より、より具体的な型 (numeric、enum、ネットワークアドレス、金額、JSON、UUID など) を優先する。
適切な型を使えば、ストレージ、クエリ、索引、計算、保守性のすべてで効率が大きく向上する。

ENUM を使う

推奨

取り得る値が固定かつ小さい (十数個程度) カラムは、整数や文字列ではなく ENUM 型を使う。
ENUM には性能、ストレージ、保守性の利点がある。

適切な文字列型を選ぶ

推奨

PostgreSQL の文字列型には CHAR(n)、VARCHAR(n)、TEXT がある。
通常は VARCHAR または TEXT を推奨する。(n) 修飾子付きの型は文字列長をチェックするため微小なオーバーヘッドが生じる。文字列長に制限があり、過剰な長さのゴミデータの挿入を防ぎたい場合に VARCHAR(n) を使う。
CHAR(n) は避ける。SQL 標準に従い、未使用部分を空白で埋めるか過剰な文字を切り捨てるが、ストレージや性能の利点はない。

適切な数値型を選ぶ

推奨

通常の数値カラムには INTEGER を使う。判断に迷うときは主キーや数値カラムに BIGINT を使う。
例外を除き SMALLINT を使わない。性能とストレージの改善はわずかで、追加の問題を生む。
REAL は 4 バイト浮動小数点、FLOAT は 8 バイト浮動小数点。
FLOAT は地理座標のように末尾精度が問題にならない場面でのみ使い、浮動小数の等価比較は避ける。
NUMERIC は厳密な数値型に使い、精度と小数点位置に注意する。
通貨型には MONEY を使う。

関数の定義形式を統一する

推奨

シグネチャ (関数名と引数) は独立した行に、戻り値は次の行に、言語ラベルを最初に置く。
関数の揮発性ラベル (IMMUTABLE、STABLE、VOLATILE) を必ず付ける。
属性ラベル (RETURNS NULL ON NULL INPUT、PARALLEL SAFE、ROWS 1 など) も注釈し、バージョン互換性に留意する。

CREATE OR REPLACE FUNCTION
  nspname.myfunc(arg1_ TEXT, arg2_ INTEGER)
  RETURNS VOID
LANGUAGE SQL
STABLE
PARALLEL SAFE
ROWS 1
RETURNS NULL ON NULL INPUT
AS $function$
SELECT 1;
$function$;

拡張性を意識して設計する

推奨

テーブル設計時に将来の拡張ニーズを考慮し、1〜3 個の予備フィールドを残してもよい。
頻繁に変わり得る非キー列には JSON 型を使ってもよい。

適切な正規化レベルを選ぶ

推奨

マルチテーブルの結合を減らして性能を上げるため、適度な非正規化を許容する。

新しい PostgreSQL リリースを使う

推奨

新バージョンは性能向上、安定性強化、新機能をもたらす。
新機能を活用し、設計の複雑さを下げる。

トリガーは慎重に使う

推奨

トリガーはシステムの複雑性と保守コストを増やすため、推奨しない。

索引ルール

オンラインクエリは適合する索引を持つこと

必須

すべてのオンラインクエリはアクセスパターンに合わせて索引を張る。ごく少数の小さなテーブルを除き、フルテーブルスキャンは許可しない。
索引にはコストがあり、使われない索引は作らない。

巨大カラムへの索引は禁止

必須

索引対象カラムのサイズはページ容量の 1/3 (2KB) を超えないこと。原則として 64 文字を超えてはならない。
大きなフィールドに索引が必要なら、ハッシュ化して関数索引を作るか、別種類の索引 (GIN) を検討する。

NULL のソート規則を明示する

必須

NULL を許容するカラムをソートする場合、クエリと索引で NULLS FIRST か NULLS LAST を明示する。
DESC ソートのデフォルトは NULLS FIRST で、NULL がソート結果の先頭に来る。これは通常は期待動作ではない。
索引のソート条件はクエリと一致させる。例: CREATE INDEX ON tbl (id DESC NULLS LAST);。

最近傍検索には GiST 索引を使う

必須

伝統的な B-tree 索引は KNN 問題に向かない。GiST 索引を使うこと。

関数索引を使う

推奨

同じ行の他のカラムから推定できる冗長カラムは、関数索引で置き換えられる。
式をクエリ条件によく使う文には、式索引や関数索引で高速化できる。
典型例: 大きなフィールドにハッシュ関数索引を作る、左部分一致クエリが必要なテキストカラムに reverse 関数索引を作る。

部分索引を使う

推奨

クエリ条件が固定なら、部分索引で索引サイズを縮小しクエリ効率を上げられる。
索引対象カラムが取り得る値が限られる場合、複数の対応する部分索引を作ることもできる。

レンジ索引を使う

推奨

値がヒープテーブルの格納順と線形に関連するデータで、通常クエリがレンジクエリなら、BRIN 索引を推奨する。
時系列データは追加書き込みのみの典型例で、BRIN 索引が効率的。

複合索引のカーディナリティに注意

推奨

カーディナリティの高いカラムを先に置く。

クエリルール

読み書きを分離する

必須

原則として、書き込み要求はプライマリ、読み取り要求はレプリカへ。
例外: ライトアフターリードの整合性が必要な場合、または有意なレプリケーション遅延が検知された場合。

速い/遅いクエリを分離する

必須

本番で 1 ミリ秒未満のクエリを高速クエリ、1 秒超のクエリを低速クエリと呼ぶ。
低速クエリは必ずオフライン DB にルーティングし、対応するタイムアウトを設定する。
本番のオンラインクエリの実行時間は原則 1ms 以内に収める。
本番のオンラインクエリ実行時間が 10ms を超える場合、リリース前に最適化が必要。
オンラインクエリは 10ms 以下のタイムアウトを設定し、連鎖障害を防ぐ。
オンラインのプライマリ/レプリカで大量データの取得を許可しない。倉庫の ETL プログラムはオフラインリードレプリカからデータを引く。

タイムアウトを設定する

必須

すべての文に能動的なタイムアウトを設定し、超過したら能動的にリクエストを取り消して連鎖障害を防ぐ。
周期的に実行される文は、実行サイクルより短いタイムアウトを設定する。

レプリケーション遅延に注意

必須

アプリケーションはプライマリとレプリカ間のレプリケーション遅延を認識し、合理的な範囲を超える状況を適切に処理する。
通常 0.1ms オーダーの遅延が、極端な場合には数十分や数時間に達することがある。アプリケーションはプライマリから読む、再試行する、エラーを返すなどを選べる。

コネクションプールを使う

必須

アプリケーションはコネクションプール経由で DB にアクセスする。postgres の 5432 ではなく pgbouncer の 6432 に接続する。
コネクションプールと直接接続の違いに注意。一部機能 (Notify/Listen など) が動かないことや、接続汚染の問題があり得る。

コネクションプールから接続設定を変更しない

必須

公共のコネクションプールを使っているときに接続設定を変更してはならない。これには接続パラメータの変更、search_path の変更、ロールの変更、データベースの変更を含む。
やむを得ず変更した場合は、その接続を完全に破棄する。設定変更後の接続をプールに戻すと、接続汚染が拡散する。

接続をリトライする

必須

クエリは同時実行競合、管理者コマンドなどで kill され得る。アプリケーションはこれを認識し、必要に応じてリトライする。
DB から大量のエラーが報告される場合、アプリケーションはサーキットブレーカーで連鎖障害を避けてよい。ただしエラー種別を見分けることに注意。

再接続

必須

接続はさまざまな理由で中断され得るため、アプリケーションは再接続機構を持つこと。
SELECT 1 をハートビートとして使い、接続にメッセージが届くか定期的に確認し、生存を維持できる。

オンラインアプリでの DDL を禁止する

必須

アプリケーションコードに驚きを持ち込まない。

カラムを明示する

必須

SELECT * や RETURNING 句での * を避ける。具体的なカラムリストを使い、不要なカラムを返さない。ワイルドカードを使うクエリは、テーブル構造変更 (新カラム追加など) でカラム不一致エラーを起こしやすい。
例外: ストアドプロシージャがテーブル行型を返すときはワイルドカードを許可する。

フルテーブルスキャンを禁止する

必須

例外: 固定の小テーブル、極めて低頻度の操作、非常に小さなテーブル/結果集合 (100 行/100KB 以内)。
先頭フィルタ条件で !=、<> などの否定演算子を使わない。フルテーブルスキャンを招く。

トランザクション内の長時間待ちを禁止する

必須

トランザクション開始後は速やかにコミットまたはロールバックすること。10 分を超える IDLE IN transaction は強制 kill する。
アプリケーションは AutoCommit を有効にし、BEGIN の対応する ROLLBACK/COMMIT 無しの放置を避ける。
できるだけ標準ライブラリのトランザクションマネージャを使い、手動でトランザクションを制御しない。

カーソルは使用後に必ず閉じる

必須

行数のカウント

必須

count(*) は行数の標準構文。NULL の有無は関係ない。
count(col) は col 列の NULL 以外の行数を返す。NULL はカウントされない。
count(distinct col) は col 列の異なる値の数を返し、こちらも NULL を無視する。
count(col1, col2) は複数列をカウントする。すべてのカラムが空でも (NULL, NULL のような行も) カウントされる。
distinct (col1, col2) も同様に複数列をカウントする。

集約関数の NULL に注意

必須

count 以外のすべての集約関数は NULL を無視するため、入力値がすべて NULL だと結果は NULL になる。例外は count(col) で、この場合 0 を返す。
集約関数の戻り値の NULL が期待動作でない場合は、coalesce でデフォルト値を設定する。

NULL の扱いに注意

必須

Zero と NULL を明確に区別する。NULL の等価には IS NULL を、Zero の等価には通常の = を使う。
関数引数として NULL を渡す場合は型修飾子を付ける。さもないとオーバーロード関数のどれを呼ぶか特定できない。
NULL を含む比較の論理に注意: NULL を含む比較はすべて UNKNOWN を返す。Boolean 演算における UNKNOWN の挙動に注意する。
- or: 論理短絡により TRUE OR UNKNOWN は TRUE。
- and: 論理短絡により FALSE AND UNKNOWN は FALSE。
- その他、UNKNOWN が現れたら結果は UNKNOWN。
NULL を任意の値と比較した結果は NULL。例: NULL = NULL は TRUE/FALSE ではなく NULL を返す。
NULL と 非 NULL の等価比較には IS DISTINCT FROM を使い、比較結果が必ず 非 NULL になるようにする。
NULL と集約関数: 集約関数の入力がすべて NULL の場合、戻り値は NULL。

Serial のギャップに注意

必須

Serial 型を使うと、INSERT、UPSERT などの操作で番号が消費される。トランザクションが失敗しても消費はロールバックされない。

繰り返しクエリには Prepared Statement を使う

推奨

繰り返されるクエリには Prepared Statement を使い、SQL 解析の CPU オーバーヘッドを取り除く。
Prepared Statement は接続状態を変えるため、コネクションプールとの相互作用に注意。

適切な分離レベルを選ぶ

推奨

デフォルトの分離レベルは read-committed で、ほとんどの単純な読み書きトランザクションに向く。通常のトランザクションはニーズを満たす最も低い分離レベルを選ぶ。
トランザクションレベルの整合性スナップショットが必要な書き込みトランザクションには repeatable read を使う。
厳密な正しさが求められる書き込みトランザクションには serializable を使う。
RR と SR の同時実行衝突が起きたら、エラー種別に応じて積極的にリトライする。

存在チェックに count を使わない

推奨

カラムが条件を満たすか確認するには、count より SELECT 1 FROM tbl WHERE xxx LIMIT 1 の方が速い。
存在結果を Boolean に変換するには SELECT EXISTS(SELECT * FROM tbl WHERE xxx LIMIT 1) を使う。

`RETURNING` を使う

推奨

挿入、削除、更新後のデータを直ちに取得したい場合は RETURNING 句を使い、DB との往復回数を減らす。

ロジックを簡潔にする `UPSERT`

推奨

ビジネスに「挿入してダメなら更新」のシーケンスがある場合、UPSERT を検討する。

ホットスポットには Advisory Lock を使う

推奨

単一行への極めて高頻度な同時書き込み (スパイク) には、レコード ID を Advisory Lock でロックする。
高並列の競合をアプリ層で解決できるなら、DB 層で解決しない。

`IN` の最適化

推奨

性能上、IN 演算子の代わりに EXISTS 句を使う。
単一値の比較は、性能上 IN (1,2,3,4) ではなく = ANY(ARRAY[1,2,3,4]) を使う。
行値型の比較には依然として IN を使う。WHERE (a, b) = ANY(ARRAY[(1, 2), (2, 3)]) のような句は索引を使えない。= ANY (call_function()) のように関数生成の配列も同様。