PostgreSQL 基礎講座 #2 データ型とテーブル設計 — 何として保存するか

インフラ PostgreSQL データベース
読了 5分

第 1 回の最初のテーブルに植えておいた習慣の理由を明かす番です。テーブル設計の半分はデータ型の選択で、ここでの間違いはサービスが大きくなった後に一番直すのが高くつきます。今回は実務で毎日ぶつかる選択、文字列・数値・時刻・主キー・制約を、判断基準とともに整理します。

文字列: text 1 つで足ります #

他の DB から来た方が最初に聞くのが「varchar(255) にすべきですか?」です。PostgreSQL の答えはシンプルです。text を使ってください。 PostgreSQL では text と varchar は内部的に同じもので、長さ制限のある varchar(n) だから速いということもありません。長さ制限がビジネスルールなら(ニックネーム 20 文字まで、など)、型ではなく CHECK 制約で表現したほうが意図が見えて変更も簡単です。

CHECKで長さ制限 
name text NOT NULL CHECK (char_length(name) <= 20)

数値: お金は必ず numeric #

整数はほとんど bigint で足ります(int で始めて 21 億を超えてマイグレーションする古典的な事故を避ける、安い保険です)。罠は小数です。float・double precision は二進浮動小数点なので、0.1 + 0.2 が正確に 0.3 になりません。 お金、数量、料率のように正確でなければならない値は必ず numeric を使います。numeric は十進の正確さを保証する代わりに演算が遅いですが、その差が問題になるワークロードはまれです。float の居場所は、科学計算や座標のように近似が許される場所だけです。

時刻: timestamptz が標準です #

時刻の保存はこの回で一番重要な節です。PostgreSQL には timestamp(タイムゾーンなし)と timestamptz(タイムゾーン認識)があり、実務の既定値は timestamptz です。 名前に反して、timestamptz はタイムゾーンを保存しません。入力を UTC に変換して保存し、読むときにセッションのタイムゾーンに変換して見せます。つまり「絶対時点」を保存する型です。一方 timestamp は変換なしで壁時計の数字をそのまま保存するので、サーバーとクライアントのタイムゾーンが混ざった瞬間、「この 9 時はどこの 9 時か」問題が始まります。

timestamptzカラム 
-- 絶対時点(イベント発生時刻)は timestamptz
created_at timestamptz NOT NULL DEFAULT now()

例外は「タイムゾーンと無関係な壁時計の値」だけです(店舗の開店時刻 09:00 のような)。日付だけ必要なら date、期間には interval が別にあります。

主キー: bigint IDENTITY が基本、UUID は要件があるとき #

PK 戦略は 2 つに分かれます。

項目bigint IDENTITYUUID
サイズ・インデックス8 バイト、小さく速い16 バイト、インデックスが大きい
生成場所DB(連番)どこでも(クライアント含む)
推測可能性連番が露出(件数を推測できる)不可
挿入の局所性良い(末尾に付く)v4 はランダムで悪い、v7 は時系列順で良い

既定値は bigint GENERATED ALWAYS AS IDENTITY です。 小さく、速く、シンプルです。UUID が要件になるのは、分散生成(DB に届く前に ID が必要)、外部に見せる ID の推測防止といった場合です。UUID を使うなら、v4 のランダム挿入がインデックスを散らかす問題を避けて時系列順に並ぶ uuidv7 を選ぶのが現在の定石で、PostgreSQL 18 からは uuidv7() 関数が内蔵されて拡張なしで使えます。内部結合用の bigint PK + 外部公開用の UUID カラムの併用も、実務でよくある折衷です。

制約: 整合性は DB 層で #

アプリケーション側の検証は迂回されます(管理コンソール、バッチ、後から繋がる 2 つ目のサービス)。最後の防衛線は DB 制約です。

制約付きordersテーブル 
CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     bigint NOT NULL REFERENCES users (id),
    status      text NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    amount_jpy  numeric NOT NULL CHECK (amount_jpy >= 0),
    created_at  timestamptz NOT NULL DEFAULT now()
);
  • NOT NULL を基本にして、「ないことがありうる」が本当の意味であるときだけ外します。NULL は 3 値論理(比較結果が true でも false でもなく unknown)を持ち込むので、少ないほどシンプルです。
  • CHECK は値のルールを、REFERENCES(外部キー)は関係の整合性を DB に強制させます。外部キーを性能を理由にためらう話は、ほとんどの規模では杞憂で、孤児レコードの後始末コストのほうがずっと高くつきます。
  • ステータス値は上のように text + CHECK で始めるのが無難です。enum 型もありますが、値の追加・削除の運用が面倒なので、値が本当に固定されたドメインだけに使います。配列(text[])は「子テーブルを作るほどではない複数値」(タグなど)に便利で、検索が必要になったら GIN インデックス(第 7 回の JSONB と同じ系統)が支えてくれます。

まとめ #

  • 文字列は text が基本です。長さのルールは varchar(n) ではなく CHECK で表現します。
  • お金・数量は numeric です。float は近似が許される場所専用です。整数は bigint で始めるのが安い保険です。
  • 時刻の既定値は timestamptz です。UTC で保存される絶対時点で、timestamp はタイムゾーン混乱の種です。
  • PK は bigint IDENTITY が基本、分散生成・推測防止の要件があれば UUID(v7)です。PostgreSQL 18 から uuidv7() が内蔵です。
  • 整合性の最後の防衛線は DB 制約です。NOT NULL 基本、CHECK と外部キーを惜しまないのがこの講座の設計方針です。
X