中級 PostgreSQL 完全攻略

① 正規化と非正規化の判断

基本は 3NF (第 3 正規形) まで正規化し、必要に応じて意図的に崩します。

② データ型の選び方

③ 制約の使い分け

-- 予約時間の重複禁止 (EXCLUDE)
CREATE EXTENSION btree_gist;

CREATE TABLE reservations (
  room_id int,
  during  tstzrange,
  EXCLUDE USING gist (
    room_id WITH =,
    during WITH &&
  )
);

INSERT INTO reservations VALUES (1, '[2026-03-15 10:00, 12:00)');
INSERT INTO reservations VALUES (1, '[2026-03-15 11:00, 13:00)');
-- ERROR: conflicting key value violates exclusion constraint

④ 主キー (PK) の選び方 — サロゲートキー vs ナチュラルキー

主キー (Primary Key) の選び方には、大きく分けて 2 つの方針があります。サロゲートキー (代理キー) は、業務的な意味を持たない人工的な ID をシステムが自動採番して PK に使う方針です。 一方のナチュラルキー (自然キー) は、メールアドレスや ISBN、社員番号など、業務上もともと一意性を持つ値をそのまま PK に使う方針を指します。

実装面では、サロゲートキーをさらに 連番 (bigint identity) と UUID の 2 種類のどちらで実現するかという選択があり、合わせると現実的な選択肢は次の 3 つになります。PK は後から変えるのが最も難しいテーブル設計要素なので、10 年後の運用を見据えて選びます。

⑤ 監査用カラムの標準パターン

監査用カラム (audit columns) とは、ビジネスデータ本体 (商品名・金額・住所など) とは別に、「このレコードはいつ・誰によって作られ、いつ・誰が最後に変更したのか」というメタ情報を記録するためのカラム群を指します。 ビジネス的には何の価値も生まない、純粋な記録のためのカラムです。

ではなぜわざわざ毎回入れるのか。ここをきちんと押さえていないと、以下のような場面で後から大きな代償を払うことになるからです。

つまり監査用カラムは「普段は使わないが、いざという時に救命具になる」存在です。 そして救命具は事故が起きてから付けても手遅れ。後付けでは過去データに値が入らず、最も知りたい「事故前の状態」が永遠に分からないままになります。だから新規テーブル作成時に必ず最初から入れておく、というのが業界の鉄則です。

標準は次の 4 点セットです。created_at (作成日時) /updated_at (最終更新日時) /created_by (作成ユーザー ID) /updated_by (最終更新ユーザー ID)。 このうち updated_at はアプリ側で更新し忘れるのが最も多いミスなので、DB トリガーで自動的に now() を書き込む構成が定石です。created_by / updated_by はアプリのセッション情報からアプリ側で入れます (誰がログインしているかは DB は知らないので)。

-- どのテーブルにも入れたい 4 つ
CREATE TABLE orders (
  id            bigint generated by default as identity primary key,
  ...

  -- 監査カラム (4 点セット)
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  created_by    bigint REFERENCES users(id),
  updated_by    bigint REFERENCES users(id)
);

-- updated_at の自動更新トリガー
CREATE OR REPLACE FUNCTION trg_update_timestamp()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER orders_update_ts
  BEFORE UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION trg_update_timestamp();

⑥ Soft Delete の罠と対策

Soft Delete (論理削除) とは、ユーザーが「削除」操作をした時に DELETE 文で実際に行を消すのではなく、deleted_at という日時カラムに削除した時刻を入れて「削除されたことにする」設計パターンです。物理的にはレコードはテーブルに残り続け、アプリは「deleted_at IS NULL の行だけを生きている行として扱う」という暗黙のルールで動きます。

ではなぜわざわざ消さないのか。「DELETE すれば良いじゃん」と思うかもしれませんが、本番システムでは消したくない事情が次々に出てきます:

ここまでは Soft Delete を採用する理由の話です。ただし「deleted_at を足せばそれで終わり」では本番で必ず事故が起きます。 以下の3 つの罠を知らずに導入すると、サービス停止級のトラブルにつながります:

以下は 3 つの罠それぞれに対する具体的な対策コードです。

ALTER TABLE users ADD COLUMN deleted_at timestamptz;

-- ❌ 全クエリに WHERE deleted_at IS NULL が必要 → アプリで漏れがち
SELECT * FROM users WHERE deleted_at IS NULL;

-- ✓ partial index で「生きてる行」だけ高速に
CREATE INDEX idx_users_active ON users (email)
  WHERE deleted_at IS NULL;

-- ✓ VIEW で「生存」のみ見せて WHERE 書き忘れを防ぐ
CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL;

-- ✓ UNIQUE 制約は partial で
CREATE UNIQUE INDEX users_email_active
  ON users (email)
  WHERE deleted_at IS NULL;
-- → 削除済みユーザーと同じ email で再登録可能になる

⑦ JSONB vs カラム

PostgreSQL の JSONB 型は、1 つのカラムの中にスキーマレスな JSON を丸ごと格納できる特殊な型です。 通常のカラム (text / int / timestamptz) は「1 カラム 1 値」が原則ですが、JSONB は「1 カラムに任意の構造化データ (オブジェクト・配列・ネスト) を入れられる」点が決定的に違います。MongoDB のようなドキュメント DB の世界観を、RDB の中に部分的に取り込める機能と言えます。

ではなぜこの設計判断をテーマにするのか。設計時にもっとも悩ましい「カラムにすべきか、JSONB にまとめるべきか」は、判断を誤ると後からほぼ取り返しがつかない領域だからです。

つまりこの判断は「便利だから JSONB」「型安全だからカラム」のような二択ではなく、そのデータが「将来どう使われるか」で決まります。 判断の決定打になるのは次の 3 つの問いです:

1 つでも YES があればカラムに切り出すべきです。全部 NO のもの (ログ詳細・ユーザー固有設定・解析用メタデータ) だけが JSONB の出番。整理すると次の使い分けになります:

⑧ ENUM vs Lookup Table

DB 設計をしていると、「固定された数種類の値しか取らないカラム」が頻繁に出てきます。たとえば注文ステータス (pending / shipped / delivered / cancelled)、支払方法、ユーザー権限、優先度などです。これら「有限の選択肢から 1 つを選ぶ型」を、テーブル設計上どう表現するか — それがこの章のテーマです。

ではなぜわざわざ章を立てて議論するのか。「普通に text で持って、アプリで弾けばいいじゃないか」と思うかもしれません。しかしそれだと本番で必ず想定外の値が入ります。SQL バッチ・データ移行・別アプリからの書き込み・手動 UPDATE など、アプリのバリデーションを通らない経路が必ず存在し、気付くと status カラムに 'Pending' と 'pending ' と 'PENDING' が混在し、集計クエリが崩壊します。だから DB レベルで「決められた値しか入らない」と物理的に保証する必要があるのです。

ところがその「保証する手段」が PostgreSQL には3 通りあり、どれもメリットとデメリットがあります。それぞれの特性を理解せず安易に選ぶと、「本番で値の追加ができない」「削除した値が永遠に残る」「並び順が表示できない」といった運用ハマりに直面します。以下が 3 つの選択肢です:

結論を一言で言うと、「値が今後変わるか」「業務メタデータが必要か」で決まります。 ① 値がコードレベルの定数 (例: 性別・血液型・国コード) → ENUM、 ② 業務側で値の追加削除や並び順の変更が起きる (例: プラン名・タグ・カテゴリ) → Lookup Table、 ③ その中間で、ラベル等のメタデータは要らないが将来追加はあり得る → CHECK 制約、というのが現場の判断軸です。

-- 方式 A: ENUM 型
CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered', 'cancelled');
ALTER TABLE orders ADD COLUMN status order_status;
-- ◯ 型安全・サイズ小
-- ✗ 値追加が ALTER TYPE ADD VALUE (DDL)、削除は更に面倒

-- 方式 B: Lookup テーブル
CREATE TABLE order_statuses (
  code text PRIMARY KEY,
  label text NOT NULL,
  sort_order int NOT NULL
);
ALTER TABLE orders ADD COLUMN status text REFERENCES order_statuses(code);
-- ◯ 追加・削除・並び順管理が楽
-- ✗ JOIN が要る

-- 方式 C: CHECK 制約 + text
ALTER TABLE orders ADD COLUMN status text NOT NULL
  CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'));
-- ◯ 中間的。値追加は ALTER TABLE で柔軟
-- ✗ メタデータが取れない

-- 使い分け: 値が安定なら ENUM、変動が頻繁なら Lookup Table

⑨ 履歴管理パターン

通常のテーブルは「現在の状態」だけを保持します。たとえば users テーブルの 1 行は、そのユーザーの「いまのメールアドレス・いまの名前・いまの料金プラン」だけが入っており、過去にどんな値だったかは分かりません。これに対して履歴管理とは、テーブルに「いつから、いつまで、どんな値だったか」を保持させる設計のことを言います。

ではなぜわざわざ章を立てるのか。「普通は現在の値だけで十分でしょ」と思うかもしれません。しかし業務システムでは、想像以上の頻度で「過去の値」が必要になります:

ここで困るのが、PostgreSQL には 「ON / OFF にすれば自動で履歴を保持してくれる」標準機能が存在しないことです (SQL Server の Temporal Table のような機能は無い)。代わりに、設計者が用途に応じて以下の 4 つのパターンから選んで自前で組む必要があります。 どれが正解という話ではなく、「何の履歴を、どれくらい遡りたいか」「書き込み性能をどこまで犠牲にできるか」「監査の厳しさ」で使い分けます。複数を組み合わせるケースも普通にあります (例: 重要テーブルは Shadow Table + 全テーブル共通の Audit Log)。

⑩ 命名規則

まず命名規則とは、テーブル名・カラム名・インデックス名・制約名などをチーム全体で統一されたルールに沿って付ける取り決めのことです。 技術的には「User」でも「users」でも「user_table」でも動くので、最初は些細な問題に見えます。

ではなぜわざわざ規則を決める必要があるのか。理由は 2 つあります:

以下は業務システムで最低限合わせておきたい慣例です。プロジェクトごとに細かい差はあれど、概ね業界共通の最大公約数となっています。これと異なるルールを採用する場合でも、「なぜ違うのか」を説明できる状態にしておくことが重要です。

⑪ スキーマ進化

まずスキーマ進化 (schema evolution) とは、本番稼働中のサービスを止めずにテーブル定義 (カラム追加・型変更・制約追加・インデックス追加など) を変えていく手順や考え方のことです。 開発初期のように「夜中にメンテ時間を取って ALTER TABLE 流して終わり」という運用ができる時期はとうに過ぎ、24/7 で動き続けるサービスでも安全に DB スキーマを変えていく必要があります。これを実現するための知識と手順がスキーマ進化です。

ではなぜわざわざ章を立てるのか。「ALTER TABLE ... ADD COLUMN ... を打てば終わりじゃないの?」と思うかもしれませんが、本番では無邪気に打つと数時間単位でサイトが落ちるからです:

逆に言うと、PostgreSQL の現代バージョン (11+) では、メタデータ更新だけで瞬時に終わる ALTERと全行スキャンする ALTER の境界がしっかり整理されており、書き方を知っていればほとんどの変更を瞬時かつ無停止で適用できます。 以下は本番で頻出するパターンを、「ロックを最小限に抑える書き方」と、対比のための「絶対やってはいけない書き方」のセットで示したものです。これらを知っているかどうかで、深夜メンテが必要なケースと不要なケースが分かれます。

-- ❌ 危険: 全行に書き込みが走る
ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'pending';

-- ✓ PG 11+: NULL 可で DEFAULT なしなら即座 (メタデータのみ更新)
ALTER TABLE orders ADD COLUMN status text;

-- ✓ NOT NULL を後から付ける場合
-- Step 1: NULL 可で追加
ALTER TABLE orders ADD COLUMN status text;
-- Step 2: バックフィル (バッチで)
UPDATE orders SET status = 'pending' WHERE status IS NULL;
-- Step 3: NOT NULL 化
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

-- ✓ FK の追加は NOT VALID で先に飛ばす
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
-- 既存行の検査を後で
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

⑫ スキーマ (Schema) — 名前空間としての活用

PostgreSQL のスキーマは MySQL でいう DB のような名前空間で、1 つの DB 内に複数の論理グループを作れます。マルチテナント・モジュール分離・拡張機能の隔離に使います。

-- スキーマ作成
CREATE SCHEMA tenant_a;
CREATE SCHEMA tenant_b;
CREATE SCHEMA audit;

-- スキーマ内にテーブル作成
CREATE TABLE tenant_a.orders (...);
CREATE TABLE tenant_b.orders (...);

-- search_path で参照解決の順序を指定
SET search_path TO tenant_a, public;
SELECT * FROM orders;  -- → tenant_a.orders を参照

-- 用途:
-- ① マルチテナント (テナント毎にスキーマ分離)
-- ② アプリケーション層分離 (auth / billing / analytics スキーマ)
-- ③ 拡張機能を別スキーマに隔離 (pg_stat_statements 等)
-- ④ pg_dump --schema=xxx で部分バックアップ

⑬ Tablespace — 物理ディスクの使い分け

PostgreSQL のテーブルスペースは、テーブルやインデックスを別のディスクに配置する機能。ホットデータを SSD、コールドデータを HDD など、I/O 特性で使い分けられます。

-- 作成
CREATE TABLESPACE fast_ssd LOCATION '/mnt/nvme/pg';
CREATE TABLESPACE cold_hdd LOCATION '/mnt/hdd/pg';

-- テーブルやインデックス毎に指定
CREATE TABLE hot_orders (...) TABLESPACE fast_ssd;
CREATE INDEX idx_archive ON archive_logs (id) TABLESPACE cold_hdd;

-- DB のデフォルト変更
ALTER DATABASE warehouse SET TABLESPACE fast_ssd;

-- 既存テーブルを移動 (重いので注意)
ALTER TABLE old_logs SET TABLESPACE cold_hdd;

-- 用途:
-- ① 頻繁アクセス用 SSD と長期保管用 HDD の使い分け
-- ② WAL を別ディスクに分離 (initdb --waldir で初期化時に指定)
-- ③ 大きなインデックスだけ別ボリュームへ

⑭ テーブル継承 (Inheritance) — PG 独自の親子関係

PostgreSQL はテーブル継承をサポートしており、子テーブルが親のカラムを引き継げます。 パーティショニングの旧実装の基盤でしたが、現在は宣言的パーティショニング (PG 10+) を使うのが主流。継承自体は共通列を持つ複数テーブルのまとめ管理として使えます。

-- 親テーブル
CREATE TABLE entities (
  id bigserial PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- 子テーブル (entities の列を引き継ぐ)
CREATE TABLE users (
  name text NOT NULL,
  email text UNIQUE
) INHERITS (entities);

-- entities への SELECT は users も含めて返る
SELECT * FROM entities;        -- users の行も含まれる
SELECT * FROM ONLY entities;   -- 親のみ

-- 注意点:
-- ・FK や UNIQUE は親子間で共有されない
-- ・パーティショニングには現代の宣言的構文を使うべき
-- ・現在は監査・ロギングテーブルの共通カラム集約くらい

⑮ カスタム型 — Composite / Domain / Enum / Range

PostgreSQL は独自データ型を作れるのが強力。よく使う 4 種を紹介します。

-- ① Composite Type (構造体)
CREATE TYPE address AS (
  street text,
  city text,
  postal_code text,
  country text
);
CREATE TABLE customers (
  id bigserial PRIMARY KEY,
  shipping address,
  billing address
);

-- ② Domain (制約付きの型エイリアス)
CREATE DOMAIN positive_int AS int CHECK (VALUE > 0);
CREATE DOMAIN email_address AS text
  CHECK (VALUE ~* '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$');

CREATE TABLE products (
  quantity positive_int,
  contact_email email_address
);

-- ③ Enum (Section ⑧ 参照)
CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered');

-- ④ Range Type (範囲)
CREATE TABLE reservations (
  room_id int,
  during tstzrange,                              -- timestamptz の範囲
  price_range int4range                          -- int の範囲
);
SELECT * FROM reservations
  WHERE during @> '2026-03-15 14:00:00'::timestamptz;

⑯ Generated Column (生成列、PG 12+)

他のカラムから自動計算される列。アプリ側の計算ロジックを DB に寄せられます。

CREATE TABLE products (
  id bigserial PRIMARY KEY,
  price numeric NOT NULL,
  tax_rate numeric NOT NULL DEFAULT 0.10,

  -- STORED: 物理的に保存される (index 可)
  price_with_tax numeric GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);

-- 全文検索用 tsvector も生成列の典型
CREATE TABLE articles (
  title text,
  body text,
  search_vector tsvector
    GENERATED ALWAYS AS (
      to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))
    ) STORED
);
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- 注意: VIRTUAL (計算のみで保存しない) は将来の PG で対応予定

⑰ Identity Column (PG 10+) vs serial

serial は古い書き方。PG 10 以降は SQL 標準のidentity columnを使うのが現代的です。

-- ❌ 古い書き方
CREATE TABLE orders (
  id serial PRIMARY KEY    -- 内部で pg_get_serial_sequence による sequence 生成
);

-- ✓ 現代的な書き方 (推奨)
CREATE TABLE orders (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
);

-- ALWAYS: 明示的 INSERT を禁止
CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
-- INSERT INTO orders (id, ...) VALUES (5, ...);  -- エラー
-- INSERT INTO orders DEFAULT VALUES;             -- OK

-- BY DEFAULT: 明示的 INSERT も許可 (移行時に便利)
CREATE TABLE orders (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
);

-- メリット: ① SQL 標準、② DEFAULT 値の検査が厳密、
-- ③ pg_dump で serial の sequence 復元の落とし穴回避

⑱ Unlogged Table / Temporary Table

通常テーブルとは違う 2 種類の特殊テーブル。正しく使うと性能が桁違いに上がります。

-- ① UNLOGGED TABLE: WAL を書かない (高速、ただしクラッシュで消える)
CREATE UNLOGGED TABLE realtime_metrics (
  metric_name text,
  value numeric,
  ts timestamptz
);
-- 用途: 一時集計バッファ / セッションキャッシュ / 失っても良いログ
-- 性能: INSERT が通常テーブルの 2〜5 倍速い
-- 注意: クラッシュやレプリカ移行で TRUNCATE される

-- ② TEMPORARY TABLE: セッション終了で自動削除
CREATE TEMP TABLE session_cart (
  product_id bigint,
  quantity int
);
-- 用途: バッチ処理の中間テーブル / 大規模 JOIN の前処理
-- スコープ: 同一セッション内のみ可視
-- 切断時に自動 DROP

-- ON COMMIT 動作
CREATE TEMP TABLE work (...) ON COMMIT DROP;       -- COMMIT で消える
CREATE TEMP TABLE work (...) ON COMMIT DELETE ROWS; -- COMMIT で行だけ消える
CREATE TEMP TABLE work (...) ON COMMIT PRESERVE ROWS; -- 残る (デフォルト)

⑲ Materialized View (マテリアライズドビュー)

通常の VIEW は「クエリのエイリアス」ですが、マテビューは結果を物理的に保存するビュー。重い集計を事前計算しておけます。

-- 作成 (この時点で計算される)
CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) AS day,
       count(*) AS orders,
       sum(amount) AS revenue
FROM orders
GROUP BY 1;

-- index も張れる
CREATE INDEX idx_daily_sales_day ON daily_sales (day);

-- 再計算 (テーブル全体ロック)
REFRESH MATERIALIZED VIEW daily_sales;

-- 無停止再計算 (UNIQUE index が必要)
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;

-- 用途:
-- ① ダッシュボード用集計
-- ② 重い JOIN の事前計算
-- ③ 全文検索インデックス
-- ④ 統計レポート

-- 注意: 自動更新されない → cron / pg_cron で REFRESH スケジュール

⑳ Foreign Data Wrapper (FDW) — 外部 DB を仮想テーブル化

他の PostgreSQL・MySQL・S3 ファイル・REST API などをあたかも PG のテーブルのように JOIN・SELECT できる機能。

-- postgres_fdw (PG → PG)
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_pg FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'analytics.example.com', dbname 'warehouse');
CREATE USER MAPPING FOR app_user SERVER remote_pg
  OPTIONS (user 'reader', password 'xxx');

-- リモートテーブルを仮想化
CREATE FOREIGN TABLE remote_orders (
  id bigint, amount numeric, created_at timestamptz
) SERVER remote_pg OPTIONS (schema_name 'public', table_name 'orders');

-- 普通の SQL で使える
SELECT u.name, count(o.id)
FROM users u
JOIN remote_orders o ON o.user_id = u.id
GROUP BY u.name;

-- 他の FDW:
-- file_fdw     : CSV/TSV ファイル
-- mysql_fdw    : MySQL
-- mongo_fdw    : MongoDB
-- redis_fdw    : Redis
-- multicorn    : Python で自作 FDW

㉑ HOT update を活かす設計

HOT (Heap-Only Tuple) update は MVCC のコストを抑える PG 独自の最適化 (Section 13 で詳述)。「更新対象カラムにインデックスがない」「ページ内に空きがある」時に発動し、index 更新を省略。 これを設計でどう活かすか:

-- ① 頻繁に UPDATE するカラムには index を張らない
--    (status 等)
-- ② fillfactor を 80〜90 に下げて HOT 余地を確保
CREATE TABLE orders (
  id bigint generated by default as identity PRIMARY KEY,
  user_id bigint NOT NULL,
  status text,
  updated_at timestamptz
) WITH (fillfactor = 85);

-- 既存テーブルにも適用可
ALTER TABLE orders SET (fillfactor = 85);

-- ③ HOT update が効いているか確認
SELECT n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd,0), 1) AS hot_ratio
FROM pg_stat_user_tables WHERE relname = 'orders';
-- → hot_ratio が 70% 以上なら効いている

㉒ カラム順序とアライメント — 実は性能に効く

PostgreSQL のタプル (1 行) はカラム順そのままでバイナリ化されます。データ型ごとにアライメント (4/8 byte 境界揃え) があるため、カラム順序を変えるだけで行サイズが小さくなることがあります。

-- ❌ 非効率: アライメントで隙間ができる
CREATE TABLE bad (
  a bool,        --  1 byte + 7 byte padding
  b bigint,      --  8 byte
  c bool,        --  1 byte + 7 byte padding
  d bigint,      --  8 byte
  e text         -- 可変長
);
-- → 32 byte + text

-- ✓ 効率的: 大きい型を先に、小さい型をまとめる
CREATE TABLE good (
  b bigint,      --  8 byte
  d bigint,      --  8 byte
  a bool,        --  1 byte
  c bool,        --  1 byte (+ 6 byte padding)
  e text         -- 可変長
);
-- → 24 byte + text。1 行 8 byte 節約

-- ルール:
-- ① bigint/timestamptz/double (8 byte) を先に
-- ② int/float (4 byte) を次に
-- ③ smallint (2 byte) を次に
-- ④ bool/char (1 byte) をまとめる
-- ⑤ 可変長 (text/bytea/jsonb) は最後

-- 数百万〜数億行なら効果絶大

㉓ pgvector — ベクトル列で AI 時代の設計

埋め込みベクトル (LLM の出力) を保存・近傍検索する拡張。RAG・推薦・類似検索のインフラとして急速に普及中。

CREATE EXTENSION vector;

CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  body text,
  embedding vector(1536)   -- OpenAI text-embedding-3-small の次元
);

-- HNSW index (近似最近傍、高速)
CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops);

-- 類似検索 (コサイン距離)
SELECT id, body, embedding <=> $1 AS distance
FROM documents
ORDER BY embedding <=> $1
LIMIT 10;

-- 距離演算子:
--   <->  L2 距離 (ユークリッド)
--   <=>  コサイン距離
--   <#>  内積 (符号反転)

8KB ページの中身

タプルヘッダ (23 byte)

HeapTupleHeaderData {
  t_xmin       : XID    // 作成したトランザクション ID
  t_xmax       : XID    // 削除/更新したトランザクション ID (0 なら有効)
  t_cid        : CID    // コマンド ID (同一トランザクション内の順序)
  t_ctid       : ItemId // 自分自身か、UPDATE 後の新タプルへのポインタ
  t_infomask   : flags  // 各種フラグ (NULL を持つか、HOT か、等)
  t_infomask2  : flags
  t_hoff       : uint8  // ヘッダ長
  // ↓ ここから実データ (カラムの値が並ぶ)
}

TOAST

1 つのタプルは原則として8KB 以下でなければなりません (ページに収まらないため)。 しかし TEXT や BYTEA など、可変長のカラムが大きくなると問題になります。 これを解決するのが TOAST (The Oversized-Attribute Storage Technique) です。

そもそも並行制御の何が難しいのか — 4 つの典型的な不整合

MVCC の話に入る前に、「そもそもなぜ並行制御という仕組みが必要なのか」を確認します。複数のユーザーやアプリが同じデータを同時に読み書きする時、何も対策しないと様々な不整合が起き、データベースの信頼性が崩れます。代表的なものを 4 つ挙げます。

こうした問題を防ぐ仕組みが並行制御 (Concurrency Control) です。世のデータベースには大きく分けて 2 つのアプローチがあります。

ロックベース vs MVCC — 2 つのアプローチ

並行制御の方式は、大きく次の 2 つに分類できます。PostgreSQL は後者 (MVCC) を採用しています。

MVCC の最大の利点は「SELECT がブロックされない」こと。長時間の集計クエリやレポート生成が走っている間も、他のトランザクションは普通に UPDATE / DELETE できます。これが PostgreSQL の高い同時実行性能の源泉です。代償としてゴミが溜まる問題は、Section 13 (VACUUM) でカバーします。

UPDATE が起きると何が起きるか

タプルヘッダに刻まれる情報 — xmin / xmax / ctid 詳細

MVCC を支えているのは、すべてのタプル (= 行) の先頭 23 バイトに記録されるヘッダ情報 (tuple header) です。アプリケーションから見える列 (id, name, ...) のすぐ前に、PostgreSQL が内部用に管理しているこのヘッダが格納されています。MVCC に関係する主要なフィールドは次の通りです。

通常の SELECT では普通の列だけが返りますが、明示的に指定すれば SQL でこれらを覗くことができます。本番障害の調査でしばしば使う、知っておくと便利なテクニックです。

-- ① タプルのメタデータを覗く
SELECT xmin, xmax, ctid, * FROM users LIMIT 5;
-- xmin   | xmax  | ctid    | id | name
-- 12345  | 0     | (0,1)   | 1  | Alice  ← 生きている (xmax=0)
-- 12350  | 12380 | (0,2)   | 2  | Bob    ← Tx 12380 が殺した
-- 12360  | 0     | (0,3)   | 3  | Carol  ← 生きている

-- ② 現在のトランザクション ID を確認
SELECT pg_current_xact_id();      -- PG 13+
SELECT txid_current();             -- 旧 API (PG 12 以前)

-- ③ 「ある XID は既にコミット済みか?」を判定
-- ヘッダ情報の解釈に使われている内部関数
SELECT pg_xact_status('12345');    -- 'committed' / 'aborted' / 'in progress'

-- ④ ページ単位での物理状態確認 (pageinspect 拡張)
CREATE EXTENSION pageinspect;
SELECT * FROM heap_page_items(get_raw_page('users', 0));
-- lp | t_xmin | t_xmax | t_ctid | t_data
--  1 |  12345 |      0 | (0,1)  | ...     ← まだ有効
--  2 |  12350 |  12380 | (0,4)  | ...     ← UPDATE されて (0,4) を指している

UPDATE が実行されると、内部では次の 3 つが連続で起きます。

これにより、同時に走っている他のトランザクションは古いタプル (xmax = 自分の XID 以外と判定) も参照できる一方、自分は新しいタプルを参照することができます。「異なるバージョンを同時並列で見せる」状態がここで実現されます。

Snapshot Isolation の仕組み

トランザクションは開始時にスナップショットを取ります。これは「いま実行中の XID 一覧」と 「コミット済みの最後の XID」を含む情報。各タプルを読むとき、その xmin/xmax を自分のスナップショットと比較して、 「自分から見えるバージョン」を選びます。

-- 擬似コード: 各タプルが「見える」かの判定
function visible(tuple, my_snapshot):
  // 1. 作成者がコミット済みで、自分より前なら基本見える
  if not committed(tuple.xmin): return false
  if tuple.xmin > my_snapshot.xmax: return false  // 未来のトランザクション

  // 2. 削除者が未確定 or 自分より後なら、まだ有効
  if tuple.xmax == 0: return true
  if not committed(tuple.xmax): return true
  if tuple.xmax > my_snapshot.xmax: return true   // まだ削除されていない時点で見ている

  // 3. それ以外は「削除済み」
  return false

並行実行シナリオ集 — MVCC は実際にどう振る舞うか

抽象的な議論だけでは MVCC の挙動はピンと来ないので、具体的な並行実行例を 4 つ見てみます。それぞれ「Tx A と Tx B が同時に何をしようとした時、どちらがどう動くか」を追います。

MVCC の代償 — 何を払う必要があるか

MVCC は「読み手と書き手が干渉しない」という素晴らしい性質を持ちますが、それと引き換えに大きな代償も払っています。本番運用でハマる場面は、ほぼすべてこれらの代償への対処です。

つまり、PostgreSQL を本番運用する上で「MVCC の代償への対処 = VACUUM の運用」が極めて重要です。Section 13 / 14 で VACUUM と Autovacuum を深く扱うのは、まさにこの代償をきちんと管理するためです。

WAL の流れ

LSN (Log Sequence Number) — WAL 内の位置を指す住所

WAL を扱う上で必ず登場するのが LSN (Log Sequence Number) という概念です。LSN はWAL 全体を 1 本の長い連続したバイト列とみなし、その中の任意の位置をバイト単位で指し示す番号です。「WAL の何バイト目」というオフセットだと考えると分かりやすいでしょう。

LSN は 64 ビット整数で、PostgreSQL の表示上は 0/3000110 のように「上位 32 bit / 下位 32 bit (どちらも 16 進)」の形式で出力されます。「ここまで WAL を書いた」「ここまでディスクに flush した」「ここまで Standby に送信した」「ここまで Standby が適用した」といったあらゆる進捗の物差しとして PostgreSQL 内部で使われます。

WAL ファイルの構造 — 16MB セグメントの連なり

WAL はディスク上では pg_wal/ ディレクトリに置かれた 16MB のセグメントファイルとして保存されます。各ファイルは満杯になると次のセグメントへロールオーバーする、追記専用のリングのような仕組みになっています。

ファイル名は 00000001000000A20000007E のような 24 桁の 16 進文字列で、3 つの意味があります。最初の 8 桁がタイムライン ID (フェイルオーバー時に増える)、次の 8 桁がLSN の上位、最後の 8 桁が同一上位 LSN 内のセグメント番号を表します。

# pg_wal/ ディレクトリの中身
$ ls -la /var/lib/postgresql/16/main/pg_wal/
-rw------- 1 postgres postgres 16777216 Jun 10 12:00 00000001000000A20000007D  # 16MB の WAL
-rw------- 1 postgres postgres 16777216 Jun 10 12:05 00000001000000A20000007E  # 次のセグメント
drwx------ 2 postgres postgres     4096 Jun 10 12:05 archive_status/            # アーカイブ管理

# ファイル名の意味
#  ┌──────── タイムライン ID (フェイルオーバー毎に +1)
#  │       ┌──────── LSN の上位 32bit
#  │       │       ┌──────── セグメント番号 (LSN の下位)
# 00000001 000000A2 0000007E

# 現在使用中のセグメントを SQL で確認
SELECT pg_walfile_name(pg_current_wal_lsn());
# → '00000001000000A20000007E'

# WAL ディレクトリの合計サイズ
SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir();

archive_status/ サブディレクトリには .ready / .done という小さなマーカーファイルが置かれます。WAL ファイルが完成すると .ready が作られ、Archiver がそれを発見して archive_command を実行、成功すると .done にリネームされる、というアーカイブ進捗管理の仕組みです。

WAL レコードの中身 — 1 つの変更がどう記録されるか

WAL 1 ファイル (16MB) の中には、何千〜何万件ものWAL レコードが連続して詰まっています。1 レコードは「ある 1 つの変更操作 (1 行の INSERT、1 ページの更新、Checkpoint の発生など)」を記述した可変長のバイナリです。

各 WAL レコードは大きく次の 3 部構成です。ヘッダ (レコード長・所属トランザクション ID (XID)・レコード種別・前レコードへのリンク・CRC)、変更内容ブロック (実際の差分データ、または影響を受けたページ全体)、そして後続レコードへの境界。再生時は先頭から順に CRC を確認しつつレコードを読み、レコード種別に応じた処理を適用していきます。

pg_waldump コマンドを使うと、実際の WAL ファイルを人間が読める形式で覗けます。「先週リリース後に WAL 量が急増した、何のクエリが原因か」を追跡したい時など、本番トラブル調査で重宝します。

# WAL ファイルの中身をデコード
$ pg_waldump 00000001000000A20000007E | head -5
rmgr: Heap   len (rec/tot):    62/    62, tx:        842, lsn: 0/7E000028,
  prev 0/7D02FFD0, desc: INSERT off 3 flags 0x00, blkref #0: rel 1663/16384/16389 blk 0
rmgr: Btree  len (rec/tot):    72/    72, tx:        842, lsn: 0/7E000068,
  prev 0/7E000028, desc: INSERT_LEAF off 3, blkref #0: rel 1663/16384/16395 blk 1
rmgr: Transaction len (rec/tot):    34/    34, tx:        842, lsn: 0/7E0000B0,
  prev 0/7E000068, desc: COMMIT 2026-06-10 12:34:56.789 JST

# レコード種別ごとに集計 (どの操作が WAL を膨らませているか)
$ pg_waldump --stats 00000001000000A20000007E

synchronous_commit — 「どこまで待ってから COMMIT を返すか」の 5 段階

COMMIT を実行した時、Backend は「WAL がどこまで安全な状態になるまで待ってからクライアントに COMMIT 成功を返すか」を synchronous_commit パラメータで制御します。この設定が 耐障害性とコミット速度のトレードオフを決定します。

実践的な使い分け: 多くのシステムでは全体は on のままにし、必要な部分だけトランザクション単位で SET LOCAL synchronous_commit = 'off' で緩める運用が現実解です。ログテーブルへの大量 INSERT バッチだけ off、決済処理だけ remote_apply、のように業務要件ごとに分けられます。

Full Page Writes と Torn Page Problem

PostgreSQL がもう一つ抱えている根本的な問題が Torn Page Problem (引き裂かれたページ問題) です。PG のデータページは 8KB ですが、Linux などの OS はディスクへの書き込みを通常 4KB (= ファイルシステムのブロック) 単位で行います。つまり 8KB ページの書き込みは内部的に 2 回の I/O に分かれており、その間に電源が落ちると、ページの前半 4KB は新しい内容、後半 4KB は古い内容、という壊れた状態 (torn page) が残ります。

この壊れたページは「変更の差分」だけ持つ WAL レコードでは復元できません (元のページが壊れているので、差分を適用しても意味不明な結果になる)。これを防ぐために、PostgreSQL は Full Page Writes (FPW) という仕組みを使います。full_page_writes = on (デフォルト) の場合、Checkpoint 後に最初にそのページを変更する時だけ、ページ全体 (8KB) を WAL に書き込みます。

こうしておけば、クラッシュ復旧時にまず WAL から「Checkpoint 直後のページ全体」を復元し、それ以降の差分 WAL を順に適用することで、torn page があっても正しい状態に戻せます。デメリットはWAL サイズが膨らむこと。Checkpoint 直後はページ全体が WAL に乗るため、書き込み量が一時的に増加します。

-- WAL サイズを抑える対策 (PG 14+ 推奨)
wal_compression = on        -- pglz / lz4 / zstd で FPW を圧縮
                              -- (PG 15+ では lz4 / zstd を選択可)

-- Checkpoint 頻度を下げて FPW の発生回数を減らす
max_wal_size = 8GB          -- 大きくして checkpoint 間隔を伸ばす
checkpoint_timeout = 30min

-- 注意: full_page_writes = off は ZFS など
-- 「8KB アトミック書き込み保証のあるストレージ」でのみ可。
-- 普通の ext4/xfs では絶対に off にしてはいけない。

クラッシュ復旧の流れ — WAL を再生して元の状態に戻す

ここまで見てきた WAL の仕組みが「実際に何の役に立つか」をまとめると、クラッシュからの自動復旧です。PostgreSQL の最大の価値は「電源が落ちても・OS が落ちても・kill -9 されても、起動し直せば必ず整合性のある状態に戻る」という性質で、これを実現しているのが WAL です。

つまり PG の「クラッシュ後の起動が異常に長い」というのはほぼ全てStep 4 (Redo) の所要時間です。Checkpoint からどれくらい WAL が溜まっていたかで時間が決まります。max_wal_size を巨大にすると平常時の Checkpoint 頻度が減って性能は上がりますが、その分クラッシュ復旧時間も伸びる、というトレードオフがあります。

Checkpoint — WAL とデータファイルを揃える同期処理

Checkpoint は、それまでに変更された Shared Buffers の dirty page を全てディスク (Data Files) へ書き出して、「ここまで WAL は要らない」と宣言する処理です。これが完了すると、それ以前の WAL はクラッシュ復旧に不要になり、削除またはリサイクルできます。

Checkpoint には 2 つのトリガーがあります。時間ベース (checkpoint_timeout 経過) とサイズベース (max_wal_size 到達)。前者は通常運用時の主な発動契機ですが、書き込みが激しいと max_wal_size を先に超えてしまい、サイズベースで強制発動します。後者が頻発しているとシステムが書き込みに耐えきれていないサインで、pg_stat_bgwriter.checkpoints_req の急増で検知できます。

重要なのは checkpoint_completion_target パラメータ。Checkpoint で大量のページを一気に書くと I/O スパイクが起きてクエリが遅くなりますが、completion_target = 0.9 にすると「checkpoint_timeout の 90% の時間をかけて分散して書く」挙動になり、I/O 波が平準化されます。本番ではほぼ常に 0.9 にしておくのが定石です。

WAL チューニングの主要パラメータ

WAL は性能とトレードオフの大きい領域なので、本番投入前に最低限のパラメータは調整が必要です。下表が現代の本番環境で見直すべきパラメータです。

WAL の他用途 — レプリ・PITR・base backup

ここまで WAL は「クラッシュ復旧の道具」として説明してきましたが、PostgreSQL の WAL は「変更の完全な履歴」でもあるため、他の主要機能の土台にもなっています。本記事の後続章で扱う多くのテーマが WAL を中心に組み立てられていることを押さえておくと、全体像が掴みやすくなります。

WAL 関連のトラブルシュート — pg_wal/ が肥大化したら

本番で最頻出の WAL 関連トラブルが 「pg_wal/ ディレクトリが肥大化してディスクが満杯になる」事象です。WAL は本来、Checkpoint 後に削除またはリサイクルされて一定サイズで安定するはずですが、いくつかの原因で削除されずに溜まり続ける状態が発生します。

原因はほぼ 3 パターンに分類できます。下表のいずれに該当するかを切り分けるところが第一歩です。

SELECT * FROM pg_stat_archiver;
-- last_failed_wal が値を持っていて、last_failed_time が新しければ archive 失敗中

SELECT slot_name, active, pg_size_pretty(
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
) AS retained FROM pg_replication_slots
ORDER BY 3 DESC;

SELECT * FROM pg_stat_bgwriter;
-- checkpoints_req (サイズ起因) が checkpoints_timed より多ければ慢性的

WAL を可視化する SQL ツールキット

普段の運用と障害調査でよく使う、WAL 状態を見るための SQL をまとめました。本番監視に組み込んでおく価値があるクエリ群です。

-- ① 現在の WAL 位置と書き込みレート
SELECT pg_current_wal_lsn() AS current_lsn,
       pg_walfile_name(pg_current_wal_lsn()) AS current_file;

-- ② pg_wal/ ディレクトリの合計サイズ
SELECT pg_size_pretty(sum(size)) AS total_wal
FROM pg_ls_waldir();

-- ③ ファイルごとの一覧 (古い順)
SELECT name, pg_size_pretty(size) AS size, modification
FROM pg_ls_waldir()
ORDER BY name;

-- ④ Archiver の状態 (本番監視の必須)
SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

-- ⑤ レプリケーションスロットと WAL 保持量
SELECT slot_name, active,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots
ORDER BY retained_bytes DESC NULLS LAST;

-- ⑥ Checkpoint の発動傾向
SELECT checkpoints_timed, checkpoints_req,
       round(100.0 * checkpoints_req /
         nullif(checkpoints_timed + checkpoints_req, 0), 1) AS pct_req,
       buffers_checkpoint, buffers_clean, buffers_backend
FROM pg_stat_bgwriter;
-- pct_req が 30% 超ならサイズトリガが頻発 → max_wal_size 増やす

-- ⑦ WAL 生成量とコスト (PG 14+)
SELECT wal_records, wal_fpi, wal_bytes,
       pg_size_pretty(wal_bytes) AS wal_size,
       wal_write_time, wal_sync_time
FROM pg_stat_wal;

-- ⑧ レプリ遅延 (Primary 側で)
SELECT application_name, state,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, write_lsn)) AS write_lag_bytes,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, flush_lsn)) AS flush_lag_bytes,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

そもそもなぜ PostgreSQL は「ゴミ」を生み続けるのか — MVCC の代償

VACUUM の話に入る前に、「そもそも PostgreSQL はなぜ放っておくとゴミが溜まる構造になっているのか」をはっきりさせます。これを押さえると、VACUUM が何のために存在するかが本当の意味で腑に落ちます。

PostgreSQL は MVCC (Multi-Version Concurrency Control) という方式を採用しています。MVCC では、UPDATE や DELETE をしても物理的にはデータをすぐには消さないのが原則です。具体的にどう動いているかを 1 ステップずつ追ってみます。

ではなぜ即座に消さないのか。「同じタイミングで動いている別のトランザクションがその古いタプルを参照している可能性がある」からです。たとえば長時間の SELECT 集計が走っている時、その集計開始時点でのスナップショット (一貫した過去の状態) を保証するために、削除済みに見える行も消さずに残しておかないといけないのです。これが MVCC の「並行性とロックフリーの代償」です。

つまり PostgreSQL では、書き換えや削除を行うたびに必ず「使われなくなる予定の古いタプル」が物理的に残り続けます。これがいわゆる「ゴミ」の正体です。これを「もう誰からも参照されないと確認できたタイミングで、まとめて掃除する」のが VACUUM の役割になります。

PostgreSQL に溜まる 5 種類のゴミと、それぞれの居場所

一口に「ゴミ」と言っても、PostgreSQL の内部には実は複数の種類のゴミが異なる場所に溜まっています。VACUUM がそれぞれを処理する方法もちょっとずつ違うので、種類別に把握しておくと挙動が読み解きやすくなります。

このうち ① と ② が「テーブル / インデックスが太る」原因の主犯で、本番でもっとも頻繁に話題に上がります。③ は隠れた肥大化、④ は性能劣化の遠因、⑤ は緊急停止リスク。VACUUM はこれら全種類をまとめて面倒見てくれる「総合掃除人」というわけです。次の Figure では、VACUUM がこれらを具体的にどう処理しているかを見ていきます。

VACUUM が直接掃除しないゴミもある: 古い WAL ファイル (pg_wal/) は Checkpointer の役目 (Section 12 参照)、pg_xact/ 内のコミットログは Autovacuum の Freeze 後に自動削除、ソート溢れの一時ファイル (base/pgsql_tmp/) はクエリ完了時に削除、と別の機構が担当します。「VACUUM = ゴミ全般を掃除」ではなく、「VACUUM = 上記 5 種類の MVCC 由来ゴミを掃除」と理解するのが正確です。

VACUUM がやること — 5 つの仕事

VACUUM は単に「ゴミを消す」だけのコマンドではなく、内部では複数の重要なタスクを同時にこなしています。1 回の VACUUM 実行で具体的に何が行われているかを正確に把握すると、後のチューニングや障害対応で迷わなくなります。

VACUUM の動作フェーズ — 内部で何が起きているか

1 回の VACUUM 実行は、ざっくり「テーブル本体スキャン → インデックススキャン → ページ更新 → クリーンアップ」の 4 フェーズで進みます。テーブルサイズが大きいほど各フェーズが長くなり、特に index が複数あると 2 番目のフェーズが本体スキャンの何倍にもなることがあります。

チューニング観点での重要ポイントは maintenance_work_mem。これが小さいと Phase 1 と 2 の組が複数パスに分かれ、テーブルが大きいほど何倍も遅くなります。本番では 1〜2GB に設定するのが推奨です。

VACUUM vs VACUUM FULL vs pg_repack — 3 つの掃除手段

重要なのは、通常の VACUUM はディスクサイズを縮めないこと。デッドタプルを「空き領域」としてマークするだけで、テーブルファイル自体は同じサイズで残ります。物理的に縮めるには別の手段が必要で、それぞれに性能特性と本番での扱いやすさが大きく異なります。

HOT update — UPDATE のコストを劇的に下げる最適化

通常の UPDATE は「新しいタプルをページに追加 + 全インデックスエントリを追加」というコストの大きい操作です。しかし、UPDATE で変更するカラムにどのインデックスも張られていない場合、PostgreSQL は HOT (Heap-Only Tuple) update という最適化を使い、コストを大幅に下げてくれます。

HOT update が発動すると、新しいタプルは同じページ内に追加され、古いタプルから新タプルへの「ポインタチェーン」だけが作られます。インデックスは古いタプルを指したまま (ポインタチェーンを辿れば新タプルに到達できる)、追加更新は不要。さらに、後続の SELECT 時にチェーンを辿る過程で、不要になった古いタプルをその場で削除する Mini-VACUUM も自動的に起こります。

実運用では、頻繁に UPDATE されるテーブルではfillfactor を 80〜90 に下げてページ内に意図的に空きを作っておくのが定石です。空きがあるほど HOT update が成立しやすくなります。確認は pg_stat_user_tables の n_tup_hot_upd / n_tup_upd 比率で。

-- UPDATE が頻繁なテーブルは fillfactor を下げて HOT 余地を確保
CREATE TABLE orders (
  id bigint generated by default as identity PRIMARY KEY,
  user_id bigint NOT NULL,
  status text,
  updated_at timestamptz
) WITH (fillfactor = 85);  -- ページの 85% まで使用、15% を HOT 用に予約

-- 既存テーブルにも適用可
ALTER TABLE orders SET (fillfactor = 85);
-- 次回 VACUUM FULL / pg_repack 後から有効

-- HOT update が効いているか確認
SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_ratio
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY hot_ratio NULLS LAST;
-- hot_ratio が 70% 以上なら効いている

XID Wraparound と Freeze — 32-bit ラップアラウンド問題

PostgreSQL のもっとも怖い障害の一つが XID Wraparound (XID 周回) です。MVCC で各タプルに記録されるトランザクション ID (XID) は32 ビット整数で、約 21 億 (2³² の半分) で 1 周します。1 周すると、過去のタプルの xmin が「未来の値」と判定され、全データが見えなくなる致命的な状態に陥ります。

これを防ぐために PostgreSQL はFreeze (凍結) という仕組みを持ちます。古いタプルの xmin を「FrozenXID」という特殊な値に書き換えると、そのタプルは「絶対に過去のもの = 周回問題と無関係」になります。VACUUM が通常の掃除ついでに Freeze も行います (vacuum_freeze_min_age を超える年齢のタプルから順に)。

ただし通常の VACUUM は「変更があったページ」しか見ないので、更新されないテーブル (例: ログテーブル) は Freeze されないまま XID が溜まります。これを救うのが Aggressive Vacuum (積極的バキューム)。XID の最古値が autovacuum_freeze_max_age (デフォルト 2 億) を超えたテーブルは、全ページを強制スキャンする Aggressive モードに切り替わって Freeze します。

-- ① XID 残量を監視 (本番では絶対モニタリングすべき)
SELECT datname,
       age(datfrozenxid) AS xid_age,
       2^31 - age(datfrozenxid) AS xid_remaining
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
-- xid_age が autovacuum_freeze_max_age (2億) に近づいたら警戒

-- ② テーブル単位の XID 年齢
SELECT relname,
       age(relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_stat_user_tables
JOIN pg_class ON pg_class.oid = relid
ORDER BY age(relfrozenxid) DESC LIMIT 20;

-- ③ 緊急時の手動 FREEZE
VACUUM (FREEZE, VERBOSE) huge_log_table;
-- 巨大テーブルなら処理時間が長いので注意

可視性マップと Index-Only Scan — VACUUM のもう一つの効果

VACUUM がデッドタプルを掃除する副作用として、可視性マップ (Visibility Map, VM) というファイルが更新されます。VM は各ページにつき 2 bit の小さなマップで、「このページの全タプルが全トランザクションから見える状態か」「全タプルが Frozen 済みか」の 2 フラグを持っています。

これが大事なのは、可視性が確定したページに対してはIndex-Only Scan が使えるためです。通常の Index Scan はインデックスからタプル位置を引いた後、必ずテーブル本体を読みに行って可視性を再確認します (MVCC では index だけで判断できないため)。VM で「全員から見える」と確定しているページは、テーブル本体を読まずに index だけで結果を返せるのです。これは大量の集計クエリで劇的な高速化を生みます。

つまり VACUUM は単にゴミを掃除しているだけでなく、「SELECT を Index-Only Scan に高速化する」効果も同時に提供しています。これが Autovacuum を止めると性能が落ちるもう一つの理由でもあります。

-- ① 可視性マップの占有率 (高いほど Index-Only Scan が効きやすい)
SELECT relname,
       pg_size_pretty(pg_relation_size(relid, 'main')) AS heap_size,
       pg_size_pretty(pg_relation_size(relid, 'vm'))   AS vm_size,
       n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_live_tup DESC LIMIT 10;

-- ② Index-Only Scan が使われているか EXPLAIN で確認
EXPLAIN (ANALYZE, BUFFERS)
  SELECT count(*) FROM orders WHERE user_id = 123;
-- → "Index Only Scan" が出ていれば成功
-- → "Heap Fetches" の値が 0 に近いほど良い (再フェッチが少ない)

VACUUM の進捗と効果を観察する SQL

本番運用で「VACUUM が進んでいない」「bloat が増えている」を診断するためのクエリ群です。これらをダッシュボードに組み込んでおくと、VACUUM 起因の問題に早期に気付けます。

-- ① 実行中の VACUUM の進捗 (PG 9.6+)
SELECT pid, datname, relid::regclass AS table,
       phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed,
       round(100.0 * heap_blks_scanned / nullif(heap_blks_total, 0), 1) AS pct
FROM pg_stat_progress_vacuum;

-- ② デッドタプル比率の高いテーブル Top 20
SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup, 0), 1) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 100
ORDER BY n_dead_tup DESC LIMIT 20;
-- dead_pct が 20% 超えるテーブルは Autovacuum 設定見直しの候補

-- ③ Autovacuum が走った回数と頻度
SELECT relname,
       n_vacuum_count AS manual_vacuum,
       autovacuum_count,
       n_analyze_count AS manual_analyze,
       autoanalyze_count,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY autovacuum_count DESC LIMIT 20;

-- ④ bloat 推定 (pgstattuple 拡張で正確に)
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');
-- dead_tuple_percent / free_percent が bloat の指標

-- ⑤ 手動 VACUUM 実行 (緊急時)
VACUUM (VERBOSE, ANALYZE) orders;            -- 通常
VACUUM (VERBOSE, ANALYZE, PARALLEL 4) orders; -- 並列 (PG 13+)
VACUUM (FREEZE, VERBOSE) orders;             -- Freeze 含む

VACUUM コマンドの完全ガイド — 構文とオプション

ここまで「VACUUM の仕組み」を学んできましたが、SQL コマンドとしての VACUUM の構文そのものは案外知られていません。本番運用では手動 VACUUM を打つ場面 (緊急の bloat 対処、Autovacuum 設定確認後の即時実行、メンテナンスウィンドウでのまとめ掃除など) が必ずあるので、構文とオプションを正確に押さえておきましょう。

基本構文は次の 2 形式です。括弧付き形式が現代的で、新しいオプションはこちらでしか書けません。

-- ① 旧式 (PG 8.x までの構文、今でも動く)
VACUUM [FULL] [FREEZE] [VERBOSE] [ANALYZE] [table_name [(column, ...)]];

-- ② 推奨: 括弧付き形式 (PG 9.0+、新オプションはこちらのみ)
VACUUM (option [, option ...]) [table_name [(column, ...)]];

-- 引数なしで全テーブルを対象 (Autovacuum と同等の挙動)
VACUUM;

-- 特定テーブルのみ
VACUUM orders;

-- 特定テーブルの特定カラムだけ ANALYZE
VACUUM (ANALYZE) orders (user_id, status);

代表的なオプションを 1 つずつ見ていきます。FULL と FREEZE は特に挙動が大きく変わるので注意。

実運用での代表的なコマンド組み合わせを以下にまとめます。

-- ① 日常的な手動 VACUUM (バッチ後に統計も更新)
VACUUM (ANALYZE, VERBOSE) orders;

-- ② 巨大テーブルを並列で速く (PG 13+)
VACUUM (ANALYZE, PARALLEL 4, VERBOSE) transactions;

-- ③ ログテーブルの計画的 FREEZE (Aggressive Vacuum 回避)
VACUUM (FREEZE, VERBOSE, ANALYZE) event_log;

-- ④ 複数テーブルをロック競合スキップで一気に掃除 (PG 12+)
VACUUM (ANALYZE, SKIP_LOCKED) orders, products, users;

-- ⑤ 本番キャッシュを汚さず VACUUM (PG 16+)
VACUUM (ANALYZE, BUFFER_USAGE_LIMIT '256MB') huge_table;

-- ⑥ 緊急高速化: インデックス処理を切って速く
VACUUM (ANALYZE, INDEX_CLEANUP OFF) emergency_table;
-- 注: index は別途 REINDEX が必要になる

-- ⑦ クラスタ全体 (全 DB / 全テーブル) を VACUUM
-- シェルから (psql ではない)
$ vacuumdb --all --analyze-in-stages
$ vacuumdb --all --analyze-in-stages --jobs 4   -- 並列で速く

-- ⑧ 単一 DB を VACUUM (vacuumdb は内部で VACUUM 文を発行)
$ vacuumdb -d myapp --analyze --verbose --parallel 4

シェルコマンドの vacuumdb は SQL の VACUUM 文をラップしたユーティリティで、「クラスタ全体」「複数 DB」「複数テーブル並列」のような psql 単発では書きにくい操作を簡潔に表現できます。本番のメンテナンスバッチではこちらを使うのが定石です。pg_upgrade 後の統計復旧で必須の --analyze-in-stages もここに含まれます。

ANALYZE コマンド — Planner の脳に最新情報を渡す

ANALYZE は VACUUM と並んで重要なメンテナンスコマンドです。テーブルからランダムサンプルを取って pg_statistic ビューに統計情報を書き込み、Planner が実行計画を立てる時の材料を最新化する役割を担います。

ANALYZE が走らないと何が起きるか。Planner は古い統計情報のまま判断するので、「テーブルに 100 万行あるのに 10 行だと思ってネステッドループ JOIN を選ぶ」「Index Scan が最適なのに Seq Scan を選ぶ」など、桁違いに遅い実行計画を選ぶ事故が頻発します。pg_upgrade 直後にクエリが急に遅くなる現象も、本質的にはこれです。

-- ① 基本構文
ANALYZE [VERBOSE] [table_name [(column, ...)]];

-- ② 引数なしで全テーブル (= cluster-wide analyze)
ANALYZE;

-- ③ 特定テーブルのみ
ANALYZE orders;

-- ④ 特定カラムのみ (頻繁に WHERE で使うカラムを優先)
ANALYZE orders (user_id, status, created_at);

-- ⑤ 進捗確認付き
ANALYZE VERBOSE orders;

-- ⑥ より詳しい統計を集める (default_statistics_target を一時的に上げる)
SET default_statistics_target = 1000;     -- デフォルト 100
ANALYZE orders;
RESET default_statistics_target;

-- ⑦ カラム単位で統計詳細度を変える
ALTER TABLE orders ALTER COLUMN status
  SET STATISTICS 1000;                    -- このカラムだけ詳しく
ANALYZE orders (status);

-- ⑧ シェルからクラスタ全体 (pg_upgrade 後の必須コマンド)
$ vacuumdb --all --analyze-in-stages
-- これは「速い精度→普通の精度→高い精度」の 3 段階で
-- ANALYZE を順に実行する。本番停止時間を最小化する。

ANALYZE と VACUUM ANALYZE の違い: ANALYZE は統計のみ更新、VACUUM ANALYZE はデッドタプル掃除と統計更新の両方を行います。バッチ INSERT の直後など「データを大量に変更したが bloat はない」場面では ANALYZE 単体で十分です。

default_statistics_target の話: ANALYZE はテーブルから 300 × default_statistics_target 行のランダムサンプルを抽出して統計を作ります。デフォルトの 100 は中規模 DB で標準的ですが、データ分布が偏っているカラム (例: VIP ユーザーの user_id がアクセスの 80% を占める) では Planner が誤判定するので、該当カラムだけ 500〜1000 に上げる、というのが定番の対処です。

Autovacuum Launcher → Worker の動作フロー

Autovacuum は実は1 つのプロセスではなく、Launcher と複数の Worker の協調動作です。Launcher は「いつ・誰が掃除すべきか」を判定するスケジューラ、Worker が実際の掃除を担当する実行係、という分業構造になっています。

ここで重要なのは autovacuum_max_workers の意味。これは「同時に動かせる Worker の最大数」で、テーブル数が多いと「Worker 上限まで詰まっていて新しいテーブルは次回まで待たされる」状況が起きます。テーブル数が多い大規模 DB では本番で 5〜10 に増やすことが多いです。

Autovacuum 発動の 3 つのトリガー

Autovacuum が「あるテーブルに対して VACUUM すべき」と判断するトリガーは、実は1 つではなく 3 つあります。それぞれ意味と閾値の計算式が違うので、まとめて理解しておく必要があります。

threshold = autovacuum_vacuum_threshold (50)
          + autovacuum_vacuum_scale_factor (0.2) × reltuples

threshold = autovacuum_vacuum_insert_threshold (1000)
          + autovacuum_vacuum_insert_scale_factor (0.2) × reltuples

age(relfrozenxid) > autovacuum_freeze_max_age (2億)

つまり「VACUUM が走っていない」ように見えても、実は巨大なログテーブルが密かに XID 年齢を貯めていて、いずれ Aggressive Vacuum で突然 I/O が詰まるといったことが起き得ます。本番運用では trigger ① だけでなく、③ も意識した監視が必要です。

チューニングすべき主要パラメータ — 詳しい意味と推奨値

以下は本番運用で必ず見直すべきパラメータです。「デフォルトがなぜそうなっているか」と「なぜ本番で変える必要があるか」を併記しました。

本番運用での postgresql.conf テンプレート

上で示したパラメータを実際に postgresql.conf に書き起こした、中〜大規模本番 DB 向けの推奨設定例です。

# ========================================
# Autovacuum 本番推奨設定 (中〜大規模 DB 向け)
# ========================================
autovacuum = on                                       # 絶対に off にしない

# 発動を頻繁に
autovacuum_vacuum_scale_factor = 0.05                 # 5% で発動 (デフォルト 20%)
autovacuum_vacuum_threshold = 50                      # 最低閾値はそのまま
autovacuum_vacuum_insert_scale_factor = 0.1           # INSERT 起因も早めに (PG 13+)
autovacuum_analyze_scale_factor = 0.02                # ANALYZE はもっと頻繁に

# 並列度
autovacuum_max_workers = 6                            # 同時 6 テーブルまで
autovacuum_naptime = 30s                              # 30 秒毎に巡回

# 1 回あたりの速度 (SSD 想定)
autovacuum_vacuum_cost_delay = 2ms                    # デフォルト維持
autovacuum_vacuum_cost_limit = 2000                   # 速く回す (デフォルト 200)

# メモリ
maintenance_work_mem = 2GB                            # Autovacuum も使う

# ログ (1 秒以上の Autovacuum はログに残す)
log_autovacuum_min_duration = 1s
log_lock_waits = on                                   # ロック待ちも記録

# Freeze (基本デフォルト)
autovacuum_freeze_max_age = 200000000                 # 2 億
vacuum_freeze_table_age = 150000000                   # 1.5 億で Aggressive 移行

テーブル分類別のチューニング戦略

テーブルの性格 (使われ方) に応じて、Autovacuum の設定を変えるのが上級の運用です。ALTER TABLE でテーブル単位の上書きを組み合わせると、グローバル設定 1 本で全部賄うより遥かに健全な状態を保てます。

テーブル単位の Autovacuum 設定 — 実例集

上で挙げた分類戦略を実際の ALTER TABLE 文に落とし込んだ例です。コピペで使える形にしてあります。

-- ========================================
-- ① ホットテーブル (頻繁 UPDATE) — 注文・セッションなど
-- ========================================
ALTER TABLE orders SET (
  fillfactor = 85,                              -- HOT update 用の空き
  autovacuum_vacuum_scale_factor = 0.02,        -- 2% で発動
  autovacuum_vacuum_threshold = 100,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 3000,          -- 速く回す
  autovacuum_vacuum_cost_delay = 2
);

-- ========================================
-- ② Append-only ログテーブル — イベントログなど
-- ========================================
ALTER TABLE event_log SET (
  fillfactor = 100,                             -- 空き不要
  autovacuum_vacuum_insert_scale_factor = 0.05, -- INSERT 起因を早めに
  autovacuum_analyze_scale_factor = 0.05,
  -- 巨大なら parallel VACUUM 設定
  autovacuum_vacuum_cost_limit = 2000
);

-- ========================================
-- ③ 巨大トランザクションテーブル (1 億行超)
-- ========================================
ALTER TABLE transactions SET (
  autovacuum_vacuum_scale_factor = 0.01,        -- 1% で発動
  autovacuum_vacuum_threshold = 1000,
  autovacuum_vacuum_cost_limit = 5000,          -- I/O を多く使って速く
  autovacuum_freeze_min_age = 50000000          -- Freeze を早めに
);

-- ========================================
-- ④ 設定の確認
-- ========================================
SELECT relname, reloptions
FROM pg_class
WHERE relname IN ('orders', 'event_log', 'transactions');

-- ========================================
-- ⑤ 設定の解除 (デフォルトに戻す)
-- ========================================
ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor);
ALTER TABLE orders RESET (fillfactor);

Autovacuum が間に合っているかの監視 SQL

パラメータをいじったら、それが効いているかを観測する必要があります。下記は本番ダッシュボードに組み込みたい監視クエリ群です。

-- ① Autovacuum が遅れているテーブルの発見
SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup, 0), 1) AS dead_pct,
       last_autovacuum,
       now() - last_autovacuum AS time_since_last_av
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 20;

-- ② 同時稼働中の Autovacuum Worker (max_workers の足りなさ判定)
SELECT pid, datname, query_start, query
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%'
ORDER BY query_start;
-- これが常に autovacuum_max_workers と同数なら、上限張り付き = 増やすべき

-- ③ Autovacuum がブロックされていないか確認
SELECT activity.pid, activity.usename, activity.query,
       pg_blocking_pids(activity.pid) AS blocking_pids,
       activity.state, activity.wait_event_type, activity.wait_event
FROM pg_stat_activity AS activity
WHERE activity.query LIKE 'autovacuum:%';

-- ④ XID 年齢の追跡 (Aggressive Vacuum の予兆)
SELECT datname,
       age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 200000000, 1) AS pct_to_aggressive
FROM pg_database
WHERE datallowconn
ORDER BY age(datfrozenxid) DESC;
-- pct_to_aggressive > 50% で計画的に対処を始める

-- ⑤ log_autovacuum_min_duration を有効にしてある場合、
--    PostgreSQL ログから Autovacuum 実行履歴を確認
-- grep "automatic vacuum" /var/log/postgresql/*.log

Autovacuum がブロックされる原因と対処

「Autovacuum が走らない」「テーブルがどんどん bloat していく」という事象は、ほとんどの場合「設定が悪い」のではなく「何かに邪魔されている」のが原因です。代表的なブロック要因を整理しました。

① Parser

受け取った文字列を字句解析・構文解析し、Parse Tree に変換します。 ここでは意味解析はしません (テーブルがあるかは見ない)。例:

SELECT name FROM users WHERE id = 5;

↓ Parser の出力 (簡略化)

SelectStmt {
  targetList: [ResTarget { name: 'name' }]
  fromClause: [RangeVar { relname: 'users' }]
  whereClause: A_Expr {
    op: '=',
    lexpr: ColumnRef { name: 'id' },
    rexpr: A_Const { val: 5 }
  }
}

② Analyzer + Rewriter

続いてAnalyzer が「テーブル/カラムは実在するか」「型は合うか」を検証し、Parse Tree を Query Tree に変換します。 その後Rewriter が VIEW やルールを実テーブル参照に展開します。

-- VIEW の展開例
CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL;

-- ユーザーが書いたクエリ
SELECT name FROM active_users WHERE id = 5;

-- Rewriter 後 (実テーブル参照に展開される)
SELECT name FROM users
  WHERE deleted_at IS NULL AND id = 5;

③ Planner

最も重要なステージ。Query Tree から複数の実行計画 (Plan Tree) 候補を生成し、コストが最小のものを選びます。 具体的にやっていること:

④ Executor

Plan Tree のノードをルートから辿りつつ、各ノードがタプルを返す Iterator Pattern (Volcano モデル) で動きます。 上位ノードが「次のタプルくれ」と聞き、下位ノードが 1 つ返す。これをループします。

-- 実行プラン例
Sort  (cost=10.45..10.55 rows=100)
  Sort Key: users.name
  ->  Hash Join  (cost=1.23..8.67 rows=100)
        Hash Cond: (orders.user_id = users.id)
        ->  Seq Scan on orders
        ->  Hash
              ->  Seq Scan on users
                    Filter: (active = true)

-- Executor の動き:
-- Sort が "next" → Hash Join "next" → Seq Scan orders "next" → 1 行返す
-- Sort はこれを 100 行ためてからソート → 上位に返す

⑤ Output

Executor が出したタプルを、PostgreSQL のワイヤープロトコルでクライアントへ送ります。 大きい結果セットの場合、ここがボトルネックになることもあります (ネットワーク帯域)。

基本的な EXPLAIN 出力

EXPLAIN SELECT * FROM users WHERE email = 'taro@example.com';

                  QUERY PLAN
─────────────────────────────────────────────
 Index Scan using users_email_idx on users
   (cost=0.28..8.30 rows=1 width=128)
   Index Cond: (email = 'taro@example.com')

cost の読み方

主要なスキャン方式

EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
WHERE user_id = 5 AND status = 'shipped';

                       QUERY PLAN
─────────────────────────────────────────────────────
 Index Scan using orders_user_status_idx on orders
   (cost=0.42..23.45 rows=18 width=64)
   (actual time=0.018..0.052 rows=15 loops=1)
   Index Cond: ((user_id = 5) AND (status = 'shipped'))
   Buffers: shared hit=4
 Planning Time: 0.123 ms
 Execution Time: 0.082 ms

EXPLAIN コマンドの完全ガイド — 構文と全オプション

EXPLAIN は本番デバッグで最も頻繁に叩くコマンドの一つです。基本構文と全オプションを正確に押さえると、調査の精度と速度が大きく上がります。

-- ① 旧式構文 (PG 8.x 以前から)
EXPLAIN [ANALYZE] [VERBOSE] statement;

-- ② 推奨: 括弧付き形式 (PG 9.0+、新オプションはこちらのみ)
EXPLAIN (option [, option ...]) statement;

-- 動かさず計画だけ表示
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- 実際に動かして実測値も
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

-- 推奨フル装備
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, WAL, SETTINGS)
  SELECT * FROM orders WHERE user_id = 123;

オプション一覧:

実運用での典型的な組み合わせをまとめます。

-- ① 本番調査の標準セット (これを最初に試す)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, WAL)
  SELECT * FROM orders WHERE user_id = $1 AND status = $2;

-- ② 危険なクエリは ROLLBACK で囲む
BEGIN;
  EXPLAIN (ANALYZE, BUFFERS)
    UPDATE orders SET status = 'shipped' WHERE id IN (1,2,3);
ROLLBACK;

-- ③ ツール解析用に JSON で出力
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
  SELECT ... ;
-- → explain.dalibo.com / pev2 などに貼り付けて可視化

-- ④ PREPARE 経由のプラン確認 (PG 16+)
EXPLAIN (GENERIC_PLAN) SELECT * FROM orders WHERE user_id = $1;

-- ⑤ 大量行クエリで ANALYZE オーバーヘッドを軽減
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS)
  SELECT * FROM large_table WHERE indexed_col = 'X';

-- ⑥ auto_explain で本番の遅いクエリを自動取得
-- postgresql.conf:
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '1s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_format = 'json'

コスト計算の基本式

-- Seq Scan のコスト（おおざっぱ）
total_cost = (relpages × seq_page_cost)
           + (reltuples × cpu_tuple_cost)
           + (reltuples × cpu_operator_cost × フィルタ数)

例: 10,000 ページ × 100 行/ページ のテーブル
seq_page_cost     = 1.0   (デフォルト)
cpu_tuple_cost    = 0.01  (デフォルト)
cpu_operator_cost = 0.0025 (デフォルト)

total_cost = 10,000 × 1.0 + 1,000,000 × 0.01 + ...
           = 10,000 + 10,000 + ...
           = 約 20,000

-- Index Scan のコスト
total_cost = startup_cost (= ツリー降下)
           + matched_pages × random_page_cost
           + matched_tuples × cpu_index_tuple_cost

random_page_cost のデフォルト = 4.0 (HDD 想定)

チューニングすべき主要パラメータ

統計情報の重要性

コスト計算はテーブルの行数・カラムの値分布などの統計に依存します。これらは ANALYZE(または VACUUM ANALYZE) で更新され、pg_statistic ビューに保存されます。

-- カラムごとの統計を確認
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'users' AND attname = 'status';

-- 統計サンプルを増やす (デフォルト 100 → 1000 へ)
ALTER TABLE users ALTER COLUMN status SET STATISTICS 1000;
ANALYZE users;

6 種類の比較

使い分けマトリクス

CREATE INDEX コマンドの完全ガイド — 構文と全オプション

インデックスを作る CREATE INDEX は実は非常に多機能で、知らないオプションだけで本番性能が桁違いに変わります。基本構文とオプションを順に押さえます。

-- 完全構文 (PG 16 時点)
CREATE [UNIQUE] INDEX [CONCURRENTLY] [IF NOT EXISTS] [index_name]
    ON [ONLY] table_name [USING method]
    ( {column | (expression)} [COLLATE collation]
        [opclass [(opclass_parameter = value [, ...])]]
        [ASC | DESC] [NULLS {FIRST | LAST}] [, ...] )
    [INCLUDE ( column_name [, ...] )]
    [NULLS [NOT] DISTINCT]
    [WITH (storage_parameter [= value] [, ...])]
    [TABLESPACE tablespace_name]
    [WHERE predicate];

本番でよく使う組み合わせの実例集です。CONCURRENTLY 付きが本番の基本。

-- ① 本番の標準形 (CONCURRENTLY + IF NOT EXISTS)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_id
  ON orders (user_id);

-- ② UNIQUE 制約付き (重複防止)
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email
  ON users (email);

-- ③ 関数インデックス (大文字小文字無視の検索)
CREATE INDEX CONCURRENTLY idx_users_lower_email
  ON users (lower(email));

-- ④ 部分インデックス (生存ユーザーだけ)
CREATE INDEX CONCURRENTLY idx_users_active_email
  ON users (email) WHERE deleted_at IS NULL;

-- ⑤ マルチカラム (順序が重要、WHERE と ORDER BY を網羅)
CREATE INDEX CONCURRENTLY idx_orders_user_status_created
  ON orders (user_id, status, created_at DESC);

-- ⑥ カバリングインデックス (Index-Only Scan 用)
CREATE INDEX CONCURRENTLY idx_orders_user_covering
  ON orders (user_id) INCLUDE (status, amount, created_at);

-- ⑦ LIKE 'foo%' 高速化用 opclass 指定
CREATE INDEX CONCURRENTLY idx_users_name_prefix
  ON users (name varchar_pattern_ops);

-- ⑧ JSONB の特定キーへの GIN インデックス
CREATE INDEX CONCURRENTLY idx_events_data_gin
  ON events USING GIN (data jsonb_path_ops);

-- ⑨ BRIN (巨大な時系列テーブルで)
CREATE INDEX CONCURRENTLY idx_logs_created_brin
  ON logs USING BRIN (created_at) WITH (pages_per_range = 32);

-- ⑩ CONCURRENTLY 失敗時の INVALID 検出と削除
SELECT indexrelid::regclass FROM pg_index WHERE indisvalid = false;
DROP INDEX CONCURRENTLY idx_failed;
-- 再実行
CREATE INDEX CONCURRENTLY ...;

CONCURRENTLY の注意点: ① トランザクション内で実行不可、② 失敗するとインデックスが INVALID 状態で残り indisvalid = false になる、③ DROP INDEX も CONCURRENTLY 付きで実行可能 (PG 9.2+)。マイグレーションフレームワークのデフォルトは非 CONCURRENTLY が多いので、明示指定が必要なことが多い。

B-tree の構造

ページ分割 (Page Split)

INSERT で新しいキーがリーフに入りきらなくなると、Page Split が発生します。 リーフを 2 つに分割し、親ノードへ新しい区切り値を昇格させる処理です。これは O(1) でなく書き込みコストが大きいため、 INSERT が頻発するインデックスはこの分割が継続的に起きます。

GIN

GIN は転置インデックス (inverted index)。Google などの検索エンジンと同じ仕組みで、 「各要素から、それを含むタプルへのポインタ」を持ちます。

-- 元データ
id | tags
 1 | ['rust', 'go']
 2 | ['python', 'go']
 3 | ['rust', 'python']

-- GIN インデックスの中身 (転置)
'rust'   → [tuple_1, tuple_3]
'go'     → [tuple_1, tuple_2]
'python' → [tuple_2, tuple_3]

-- クエリ例
SELECT * FROM articles WHERE tags @> ARRAY['rust'];
-- → GIN で 'rust' を引いて tuple_1, tuple_3 を取得

JSONB への GIN

-- 戦略 A: jsonb_ops (デフォルト) - 全演算子サポート
CREATE INDEX idx_data ON events USING GIN (data);
-- ? ?| ?& @> @@ @? 全て使える
-- インデックスサイズは大きい

-- 戦略 B: jsonb_path_ops - @> のみだが小さく速い
CREATE INDEX idx_data ON events USING GIN (data jsonb_path_ops);
-- @> のみサポート (キー存在 ? は使えない)
-- インデックスサイズは A の 1/2〜1/3、検索も速い

-- 大抵の場合 @> しか使わないので B が推奨

GiST

GiST は木構造の汎用フレームワークで、空間データ・範囲データ・類似検索に最適化できます。 PostGIS の地理データ検索の土台もこれです。

GIN vs GiST の使い分け

① 部分インデックス (Partial Index)

条件を絞ったインデックス。「テーブルの一部だけ」のインデックスを作ります。

-- 全注文のうち「未発送」だけインデックス
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

-- クエリでマッチすればインデックスが効く
SELECT * FROM orders
  WHERE status = 'pending' AND created_at > NOW() - INTERVAL '1 day';

-- メリット:
-- ① インデックスサイズが激小 (全体の数 %)
-- ② 更新コストも下がる
-- ③ 統計の精度向上 (絞ったデータでの分布が明確)

② 関数インデックス (Expression Index)

式をインデックスに張れます。「大文字小文字を無視した検索」「JSON のフィールド抽出」などで威力を発揮。

-- 大文字小文字を区別せず email 検索
CREATE INDEX idx_users_email_lower
  ON users (LOWER(email));

-- クエリは LOWER で囲む必要がある
SELECT * FROM users WHERE LOWER(email) = 'taro@example.com';

-- JSONB の特定フィールドにインデックス
CREATE INDEX idx_data_user_id
  ON events ((data->>'user_id'));

SELECT * FROM events WHERE data->>'user_id' = '12345';

-- 日付の年月単位
CREATE INDEX idx_orders_yearmonth
  ON orders (DATE_TRUNC('month', created_at));

③ マルチカラムインデックス (Composite Index)

複数カラムを1 つのインデックスに。列順が極めて重要です。

CREATE INDEX idx_orders_user_status
  ON orders (user_id, status);

-- ◯ 効く
WHERE user_id = 5                            -- 前方一致
WHERE user_id = 5 AND status = 'shipped'     -- 両方
WHERE user_id = 5 ORDER BY status            -- ORDER BY も

-- × 効きにくい
WHERE status = 'shipped'                     -- user_id を飛ばすと弱い
WHERE user_id IN (1,2,3) AND status = 'x'    -- 部分的に効くが完璧でない

-- ルール: 「最も選択性が高い列」を先に
-- = ID系 → 状態系 → 日付系 の順が定石

① 関数で包む

WHERE LOWER(email) = 'a@example.com'

-- 関数インデックスを作るか、保存時に正規化
CREATE INDEX idx_email_lower ON users (LOWER(email));
-- または、保存時に小文字化して通常 B-tree

② 暗黙の型変換

-- id は integer のカラム
WHERE id = '5'  -- 文字列と比較

WHERE id = 5  -- 数値で比較

③ NOT IN / != が大きい集合

WHERE status NOT IN ('active', 'paused', ...)

WHERE status = 'cancelled'  -- 正のリストで書く

④ LIKE '%foo%' (中間一致)

WHERE name LIKE '%東京%'

-- pg_trgm + GIN
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON places USING GIN (name gin_trgm_ops);
-- これで LIKE '%foo%' も使えるようになる

⑤ OR 条件で異なるカラム

WHERE user_id = 5 OR email = 'x@y.com'

-- UNION で書き換えるか、複数の Bitmap Index Scan を期待
SELECT * FROM users WHERE user_id = 5
UNION
SELECT * FROM users WHERE email = 'x@y.com';

⑥ 統計情報が古い

-- ANALYZE してから半年経過
-- → 行数推定が桁違いに外れる → Seq Scan を選んでしまう

ANALYZE orders;  -- または autovacuum_analyze の調整
-- 重要カラムは STATISTICS 値を上げる
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;

⑦ 範囲条件の後ろのカラム

-- index: (created_at, user_id)
WHERE created_at > '2024-01-01' AND user_id = 5

-- index 列順を逆に
CREATE INDEX ON orders (user_id, created_at);
-- 等価条件のカラムを前に置く

インデックス bloat の検出

-- ① 各インデックスのサイズと使用状況
SELECT
  schemaname,
  relname AS table,
  indexrelname AS index,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- ② 未使用インデックス (idx_scan = 0) を削除候補に
SELECT schemaname, relname, indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%pkey'    -- PK は除外
ORDER BY pg_relation_size(indexrelid) DESC;

-- ③ bloat 推定 (pgstattuple 拡張、より正確)
CREATE EXTENSION pgstattuple;

SELECT * FROM pgstatindex('idx_orders_user_id');
-- avg_leaf_density が 50% 以下なら bloat 大

REINDEX

REINDEX はインデックスを完全に作り直して bloat を解消します。 ただし、デフォルトの REINDEX はACCESS EXCLUSIVE ロックを取るので、本番では使えません。

-- ❌ 本番で使うな (ACCESS EXCLUSIVE で全停止)
REINDEX INDEX idx_orders_user_id;
REINDEX TABLE orders;

-- ◯ 本番で使える (PG 12+)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;
REINDEX TABLE CONCURRENTLY orders;

-- PG 11 以前は CREATE INDEX CONCURRENTLY で代替
CREATE INDEX CONCURRENTLY idx_orders_user_id_new
  ON orders (user_id);
DROP INDEX CONCURRENTLY idx_orders_user_id;
ALTER INDEX idx_orders_user_id_new RENAME TO idx_orders_user_id;

CONCURRENTLY オプションの落とし穴

pg_repack

pg_repack 拡張は、テーブル本体・インデックスを完全無停止で再構築できます。 REINDEX CONCURRENTLY より柔軟で、テーブル bloat も同時に解消できます。

# インストール (RHEL/CentOS)
sudo yum install pg_repack15

# DB に拡張作成
psql -c "CREATE EXTENSION pg_repack;"

# テーブルを再構築 (bloat 解消)
pg_repack -h host -U user -d mydb -t orders

# インデックスのみ再構築
pg_repack -h host -U user -d mydb --index=idx_orders_user_id

# 並列度を上げる
pg_repack -j 4 -h host -U user -d mydb -t orders

REINDEX コマンドの完全ガイド — 構文とオプション

REINDEX はインデックスを再構築するコマンドです。bloat したインデックスを「捨てて作り直す」ことでサイズと検索速度を回復させます。本番運用では CONCURRENTLY 付きでの使用が必須です。

-- 基本構文
REINDEX [(option [, ...])] {INDEX | TABLE | SCHEMA | DATABASE | SYSTEM}
        [CONCURRENTLY] name;

-- オプション
-- ( VERBOSE )
-- ( CONCURRENTLY )           -- PG 12+ で別名指定可能
-- ( TABLESPACE name )        -- PG 14+
-- ( CONCURRENTLY [boolean] ) -- PG 12+

実運用での組み合わせ例:

-- ① 本番標準: 個別 index を無停止再構築
REINDEX (CONCURRENTLY, VERBOSE) INDEX idx_orders_user_id;

-- ② テーブル全 index を無停止再構築
REINDEX (CONCURRENTLY) TABLE orders;

-- ③ スキーマ全体 (深夜計画停止)
REINDEX (CONCURRENTLY) SCHEMA public;

-- ④ DB 全体 (大規模再構築、計画停止前提)
REINDEX (CONCURRENTLY) DATABASE myapp;

-- ⑤ 別テーブルスペースへ移動しつつ再構築 (PG 14+)
REINDEX (TABLESPACE fast_ssd, CONCURRENTLY) INDEX idx_huge;

-- ⑥ シェルから (reindexdb は内部で REINDEX 文を発行)
$ reindexdb --concurrently -d myapp -t orders
$ reindexdb --concurrently -d myapp --jobs 4    -- 並列再構築

-- ⑦ CONCURRENTLY 失敗時の検出と削除
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY idx_failed_reindex;

-- ⑧ システムカタログの REINDEX (CONCURRENTLY 不可)
-- 単一ユーザーモードで実行する必要あり (緊急修復用)
$ postgres --single -D /var/lib/postgresql/16/main myapp
backend> REINDEX SYSTEM myapp;

REINDEX vs CREATE INDEX CONCURRENTLY vs pg_repack の使い分け

似た目的のコマンドが複数あるので、使い分けの整理が重要です。

4 つの異常現象 (アノマリ)

4 分離レベル × 4 アノマリのマトリクス

設定方法

-- トランザクション単位で
BEGIN;
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  -- ...
COMMIT;

-- セッション単位
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- DB 全体 (postgresql.conf)
default_transaction_isolation = 'read committed'

BEGIN / COMMIT / ROLLBACK コマンドの完全ガイド

トランザクションを開始・確定・取り消すコマンド群です。アプリ側のフレームワーク (Rails / Django / Prisma 等) が普段自動でこれを発行していますが、psql で手動操作する場面 (調査・修復・バッチ) では正確な構文知識が必要です。

-- ① 基本構文
BEGIN [WORK | TRANSACTION]
      [ ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } ]
      [ READ WRITE | READ ONLY ]
      [ NOT DEFERRABLE | DEFERRABLE ]
      [ , ... ];

COMMIT [WORK | TRANSACTION] [AND [NO] CHAIN];
ROLLBACK [WORK | TRANSACTION] [AND [NO] CHAIN];

-- ② START TRANSACTION は BEGIN と同義 (SQL 標準形)
START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;

-- ① 普通のトランザクション
BEGIN;
  UPDATE orders SET status = 'shipped' WHERE id = 1;
  INSERT INTO audit_log (action) VALUES ('shipped order 1');
COMMIT;

-- ② Serializable + READ ONLY DEFERRABLE で長時間レポート
BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
  SELECT count(*), sum(amount) FROM orders WHERE created_at >= '2026-01-01';
COMMIT;

-- ③ 念のためトランザクションで実行して動作確認
BEGIN;
  EXPLAIN (ANALYZE, BUFFERS)
    UPDATE orders SET ... WHERE ...;
ROLLBACK;  -- 必ず ROLLBACK で巻き戻す

-- ④ 自動コミットなしで複数 Tx を連続実行 (PG 12+)
BEGIN;
  -- 処理 1
COMMIT AND CHAIN;
  -- 処理 2 (同じ設定で自動的に新トランザクション)
COMMIT;

-- ⑤ psql のオートコミットを切る
\set AUTOCOMMIT off  -- psql クライアント側の設定
-- 以降、明示的に COMMIT/ROLLBACK が必要

SAVEPOINT コマンドの完全ガイド — 部分ロールバック

SAVEPOINT はトランザクション内に「戻り点」を作るコマンドで、エラーが起きてもトランザクション全体を巻き戻さずに、SAVEPOINT 以降だけを取り消せます。複雑なバッチ処理や、エラー耐性のある一括投入で重宝します。

-- 基本構文
SAVEPOINT name;
ROLLBACK TO [SAVEPOINT] name;     -- name までロールバック (Tx は継続)
RELEASE [SAVEPOINT] name;         -- savepoint を解放 (もう戻れなくなる)

-- ① バッチで「失敗した 1 件だけスキップ」したい時
BEGIN;
  INSERT INTO orders (...) VALUES (...);  -- ① 成功
  SAVEPOINT sp_item_2;
  INSERT INTO orders (...) VALUES (...);  -- ② エラー!
  ROLLBACK TO sp_item_2;                  -- ② だけ取り消し
  INSERT INTO orders (...) VALUES (...);  -- ③ 続行 (成功)
COMMIT;
-- → ① と ③ だけ保存される

-- ② エラーハンドリングと組み合わせ
BEGIN;
  SAVEPOINT before_risky;
  -- 失敗する可能性のある処理
  INSERT INTO ... VALUES (...);
  -- 成功したらここで RELEASE
  RELEASE SAVEPOINT before_risky;
COMMIT;
-- 失敗したら ROLLBACK TO before_risky を実行する設計

-- ③ psql の自動 SAVEPOINT (エラー時に自動巻き戻し)
\set ON_ERROR_ROLLBACK interactive
-- 以降、エラーが出てもトランザクションが abort 状態にならず続行可

-- ④ ネストした SAVEPOINT
BEGIN;
  SAVEPOINT outer_sp;
    -- 何か処理
    SAVEPOINT inner_sp;
      -- 内側の処理
    RELEASE SAVEPOINT inner_sp;
  ROLLBACK TO outer_sp;  -- inner も outer も巻き戻る
COMMIT;

SAVEPOINT のコスト: SAVEPOINT は内部的にサブトランザクションを作るため、1 トランザクション内に大量 (数千〜) の SAVEPOINT を作ると subtransaction overflow が起きて性能が劇的に劣化します。一部 ORM のデフォルト挙動 (例: 古い ActiveRecord) が毎クエリで SAVEPOINT を打つアンチパターンがあるので注意。

ロックのライフサイクル — いつ取得され、いつ解放されるか

PostgreSQL の重量ロック (heavyweight lock)はすべて「取得した瞬間」から「トランザクション終了 (COMMIT / ROLLBACK)」まで保持され続けるのが大原則です。文単位で解放されないため、長いトランザクションは取得した全ロックをその間ずっと抱え続けます。これが本番事故の根本原因になります。

特に重要なのは④ の待ち行列です。たとえば本番で素の ALTER TABLE (ACCESS EXCLUSIVE 要求) を発行すると、たとえ要求自体は数ミリ秒の処理でも、先行する長い SELECT が終わるまで待たされ、その間に後続の SELECT が全員「ALTER の後ろ」に並ばされるため、結果として全停止します。これが「ALTER TABLE は速いはずなのに本番が止まる」現象の正体です。

テーブルロック 8 レベル

ACCESS EXCLUSIVE が最強。これを取るとSELECT すら止まります。 ALTER TABLE や VACUUM FULL でこれが取られるため、本番で安易に打つと全停止します。覚え方は「ACCESS が付くと SELECT すら止める / EXCLUSIVE が付くと DML を止める」。

テーブルロックの競合マトリクス (8 × 8)

行が「自分が持っているロック」、列が「他が取りに来たロック」。✓ なら共存可、✗ なら後から来た方が待たされます。表の右下に向かうほど競合が増えていきます。