PostgreSQL 内部構造ガイド - 初心者向け
このドキュメントでは、PostgreSQLがどのように動作しているかを初心者向けに解説します。データベースの「中身」を理解することで、より効率的なアプリケーション開発やトラブルシューティングができるようになります。
目次
1. アーキテクチャ全般
1.1 PostgreSQLの全体像
PostgreSQLは「クライアント/サーバーモデル」で動作します。簡単に言えば、データベースサーバーが常に起動していて、アプリケーション（クライアント）からの接続を待ち受けています。
┌─────────────────────────────────────────────────────────────┐
│ PostgreSQL Server │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ Backend 1 │ │ Backend 2 │ │ Backend 3 │ ... │
│ │ (接続1用) │ │ (接続2用) │ │ (接続3用) │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
│ ▲ ▲ ▲ │
│ │ │ │ │
│ ┌──────┴───────────────┴───────────────┴──────┐ │
│ │ Postmaster (親プロセス) │ │
│ └─────────────────────────────────────────────┘ │
│ ▲ │
└─────────│────────────────────────────────────────────────────┘
│ TCP/IP または Unix Socket
┌─────┴─────┐
│ Client │ (アプリケーション)
└───────────┘
1.2 プロセス構造
PostgreSQLは「マルチプロセスアーキテクチャ」を採用しています。これは各接続に対して独立したプロセスを作成する方式です。
主要なプロセ ス
| プロセス名 | 役割 |
|---|---|
| Postmaster | 親プロセス。クライアント接続を受け付け、Backendプロセスを生成する |
| Backend | 各クライアント接続に対応。SQLの解析・実行を担当 |
| Background Writer | 共有バッファからディスクへデータを書き出す |
| WAL Writer | WAL（トランザクションログ）をディスクに書き出す |
| Checkpointer | 定期的にチェックポイントを実行し、データの永続化を保証 |
| Autovacuum Launcher | 自動バキュームプロセスを管理 |
| Stats Collector | データベースの統計情報を収集 |
┌────────────────────────────────────────────────────────────────┐
│ PostgreSQL プロセス群 │
├────────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────┐ │
│ │ Postmaster │ ← 全てのプロセスの親 │
│ └──────┬───────┘ │
│ │ │
│ ┌──────┴──────────────────────────────────────┐ │
│ │ │ │
│ ▼ ▼ │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────────┐│
│ │ユーザー接続用 │ │ユーザー接続用 │ ... │ バックグ ラウンド ││
│ │ Backend 1 │ │ Backend 2 │ │ プロセス群 ││
│ └──────────────┘ └──────────────┘ └──────────────────┘│
│ │ │
│ ┌──────────────┼──────────────┐ │
│ ▼ ▼ ▼ │
│ ┌─────────┐ ┌───────────┐ ┌───────┐ │
│ │BG Writer│ │WAL Writer │ │ その他 │ │
│ └─────────┘ └───────────┘ └───────┘ │
└────────────────────────────────────────────────────────────────┘
1.3 メモリ構造
PostgreSQLのメモリは大きく「共有メモリ」と「ローカルメモリ」に分かれます。
共有メモリ (Shared Memory)
全てのプロセスが共有して使用するメモリ領域です。
┌─────────────────────────────────────────────────────────────┐
│ 共有メモリ │
├─────────────────────────────────────────────────────────────┤
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Shared Buffer Pool │ │
│ │ (テーブルやインデックスのページをキャッシュ) │ │
│ │ ※ shared_buffers パラメータで設定 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ ┌─────────────────────┐ ┌─────────────────────────────┐ │
│ │ WAL Buffers │ │ Lock Space │ │
│ │ (WAL の書き込み用) │ │ (ロック情報を管理) │ │
│ └─────────────────────┘ └─────────────────────────────┘ │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ CLOG (Commit Log) │ │
│ │ (トランザクションのコミット状態を記録) │ │
│ └─────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
ローカルメモリ (Backend ごと)
各Backendプロセスが個別に持つメモリ領域です。
| メモリ領域 | 用途 | 関連パラメータ |
|---|---|---|
| work_mem | ソートやハッシュ処理用 | work_mem |
| maintenance_work_mem | VACUUM、CREATE INDEX用 | maintenance_work_mem |
| temp_buffers | 一時テーブル用 | temp_buffers |
1.4 WAL (Write-Ahead Logging)
WALはPostgreSQLの「安全装置」です。データを変更する前に、まずWALに記録することで、障害発生時にデータを復旧できます。
WALの基本原則
【データ更新の流れ】
1. トランザクション開始
↓
2. WALバッファに変更内容を記録 ← 最初にログを書く！
↓
3. 共有バッファ上のデータを変更
↓
4. COMMIT時にWALをディスクに書き込み (fsync)
↓
5. トランザクション完了
↓
(後で) 共有バッファの内容をデータファイルに反映
なぜWALが必要なのか？
もしWALがなければ、以下のような問題が起きます：
- 障害時に「どこまで処理が完了していたか」がわからない
- データの整合性が保証できない
- リカバリができない
WALがあることで：
- 障害発生後、WALを再生することでデータを復旧できる
- COMMITされたトランザクションは確実に永続化される
- レプリケーションにも利用できる
1.5 データファイルの構造
PostgreSQLは「ページ」という単位でデータを管理します。
┌──────────────────────────────── ─────────────────────────────┐
│ 1ページ = 8KB (デフォルト) │
├─────────────────────────────────────────────────────────────┤
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Page Header (24バイト) │ │
│ │ - ページのメタ情報 │ │
│ └─────────────────────────────────────────────────────┘ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Line Pointers (行へのポインタ配列) │ │
│ │ - 各タプル(行)の位置を指す │ │
│ └─────────────────────────────────────────────────────┘ │
│ ↓ │
│ (空き領域) │
│ ↑ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Tuples (実際のデータ行) │ │
│ │ - 下から上に向かって格納される │ │
│ └─────────────────────────────────────────────────────┘ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Special Space (インデックスページ用) │ │
│ └─────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
2. クエリ実行の仕組み
SQLを発行してから結果が返ってくるまで、内部では複数のステップが実行されます。
2.1 クエリ処理の全体フロー
┌─────────────────────────────────────────────────────────────┐
│ クエリ処理パイプライン │
└─────────────────────────────────────────────────────────────┘
SQL文: SELECT * FROM users WHERE id = 1;
│
▼
┌──────────────────┐
│ Parser │ ① 構文解析
│ (パーサー) │ SQLを解析して構文木を作成
└────────┬─────────┘
│ Parse Tree (構文木)
▼
┌──────────────────┐
│ Analyzer │ ② 意味解析
│ (アナライザー) │ テーブル・カラムの存在確認等
└────────┬─────────┘
│ Query Tree (クエリ木)
▼
┌──────────────────┐
│ Rewriter │ ③ 書き換え
│ (リライター) │ ビューやルールの適用
└────────┬─────────┘
│ Rewritten Query Tree
▼
┌──────────────────┐
│ Planner │ ④ 実行計画作成
│ (プランナー) │ 最適な実行方法を決定
└────────┬─────────┘
│ Plan Tree (実行計画)
▼
┌──────────────────┐
│ Executor │ ⑤ 実行
│ (エグゼキューター) │ 計画に従ってデータを取得
└────────┬─────────┘
│
▼
結果セット
2.2 Parser (パーサー)
パーサーはSQL文を「構文木 (Parse Tree)」に変換します。
SELECT name, email FROM users WHERE age > 20;
この SQL は以下のような構造に分解されます：
SelectStmt
├── targetList: [name, email] -- 取得するカラム
├── fromClause: [users] -- 対象テーブル
└── whereClause: -- 条件
└── A_Expr (>)
├── left: age
└── right: 20
2.3 Analyzer (アナライザー)
アナライザーは構文木を検証し、「クエリ木 (Query Tree)」を作成します。
主な処理：
- テーブルやカラムが実際に存在するか確認
- データ型の検証と変換
- 権限のチェック
- システムカタログの参照
2.4 Rewriter (リライター)
リライターはクエリを書き換えます。
主なケース：
- ビューの展開: ビューへのクエリを実テーブルへのクエリに変換
- ルールの適用: CREATE RULEで定義されたルールを適用
-- ビューの例
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';
-- このクエリは...
SELECT * FROM active_users WHERE age > 20;
-- 内部的にこう書き換えられる
SELECT * FROM users WHERE status = 'active' AND age > 20;
2.5 Planner (プランナー)
プランナーは「最適な実行計画」を立てる、PostgreSQLの頭脳です。
コストベース最適化
プランナーは複数の実行方法を比較し、最も「コスト」が低いものを選びます。
【同じ結果を得る複数の方法】
方法1: Sequential Scan (全件スキャン)
→ テーブル全体を読む
→ コスト: 1000
方法2: Index Scan (インデックス使用)
→ インデックスで該当行を特定してから読む
→ コスト: 50
→ 方法2を採用！
実行計画の確認 (EXPLAIN)
EXPLAIN SELECT * FROM users WHERE id = 1;
-- 結果例
Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=100)
Index Cond: (id = 1)
主なスキャン方式
| 方式 | 説明 | 使われる場面 |
|---|---|---|
| Sequential Scan | テーブル全体を順番に読む | 大部分の行を取得する場合 |
| Index Scan | インデックスを使って行を特定 | 少数の行を取得する場合 |
| Index Only Scan | インデックスだけで完結 | SELECT対象がインデックスに含まれる場合 |
| Bitmap Scan | 複数条件を組み合わせる | 中程度の行数を取得する場合 |
結合 (JOIN) の方式
┌─────────────────────────────────────────────────────────────┐
│ JOINアルゴリズム │
├─────────────────────────────────────────────────────────────┤
│ │
│ ① Nested Loop Join │
│ 外側テーブルの各行に対し、内側テーブルを検索 │
│ → 小さいテーブル同士、またはインデックスがある場合に有効 │
│ │
│ ② Hash Join │
│ 一方のテーブルでハッシュテーブルを作成し、 │
│ もう一方をそれに照合 │
│ → 等価結合で、両テーブルが大きい場合に有効 │
│ │
│ ③ Merge Join │
│ 両テーブルをソートしてマージ │
│ → ソート済みまたはソートが安価な場合に有効 │
│ │
└─────────────────────────────────────────────────────────────┘
2.6 Executor (エグゼキューター)
エグゼキューターは実行計画に従って実際にデータを取得します。
【実行計画の例】
HashAggregate
└── Hash Join
├── Seq Scan on orders
└── Hash
└── Index Scan on customers
【実行順序】（下から上に）
1. Index Scan: customers テーブルをインデックスで読む
2. Hash: customers のハッシュテーブルを作成
3. Seq Scan: orders テーブルを順次読む
4. Hash Join: orders と customers をハッシュ結合
5. HashAggregate: 集約処理を実行
2.7 PL/pgSQL関数の実行ライフサイクル
PL/pgSQL関数は、通常のSQLとは異なる実行フローを持ちます。
SQL vs PL/pgSQL の実行フロー比較
┌─────────────────────────────────────────────────────────────────┐
│ SQL vs PL/pgSQL 実行フロー │
├─────────────────────────────────────────────────────────────────┤
│ │
│ 【通常のSQL】 【PL/pgSQL関数】 │
│ │
│ ① Parser ① Parser │
│ ② Analyzer ② Analyzer │
│ ③ Rewriter ③ Rewriter │
│ ④ Planner ④ 関数呼び出し │
│ ⑤ Executor ↓ │
│ ⑤ PL/pgSQLエンジン起動 │
│ ↓ │
│ ⑥ 関数コードのコンパイル │
│ (初回のみ) │
│ ↓ │
│ ⑦ 変数初期化 │
│ ↓ │
│ ⑧ 本体処理実行 │
│ - SQL文を発行 │
│ - 制御構造 │
│ - エラーハンドリング │
│ ↓ │
│ ⑨ 結果を返す │
│ │
└─────────────────────────────────────────────────────────────────┘
PL/pgSQL関数の詳細な実行フロー
┌─────────────────────────────────────────────────────────────────┐
│ PL/pgSQL関数実行の内部処理 │
└─────────────────────────────────────────────────────────────────┘
SELECT my_function(123); -- 関数呼び出し
│
▼
┌──────────────────────────────────────────────────────────────┐
│ ① 関数のキャッシュ確認 │
│ - すでにコンパイル済み？ → キャッシュから実行 │
│ - 初回呼び出し？ → コンパイル処理へ │
└──────────────────┬───────────────────────────────────────────┘
│ (初回のみ)
▼
┌──────────────────────────────────────────────────────────────┐
│ ② PL/pgSQLソースコードのパース │
│ CREATE FUNCTION my_function(p_id INTEGER) │
│ RETURNS TEXT AS $$ │
│ DECLARE │
│ v_name TEXT; │
│ BEGIN │
│ SELECT name INTO v_name FROM users WHERE id = p_id; │
│ RETURN v_name; │
│ END; │
│ $$ LANGUAGE plpgsql; │
│ │
│ ↓ 内部的なバイトコードに変換 │
│ │
│ - DECLARE部の変数宣言を解析 │
│ - BEGIN-END内の文をバイトコードに変換 │
│ - 各SQL文を識別（Executor に渡すため） │
└──────────────────┬───────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────┐
│ ③ 実行コンテキストの準備 │
│ - ローカル変数用のメモリ確保 │
│ - パラメータ値の設定 (p_id = 123) │
│ - EXCEPTION用のエラーハンドラ設定 │
└──────────────────┬────────────────── ─────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────┐
│ ④ 関数本体の実行 (バイトコードインタープリタ) │
│ │
│ ┌────────────────────────────────────────────────────┐ │
│ │ DECLARE v_name TEXT; -- 変数宣言 │ │
│ │ → メモリスロット確保、NULL で初期化 │ │
│ └────────────────────────────────────────────────────┘ │
│ ↓ │
│ ┌────────────────────────────────────────────────────┐ │
│ │ SELECT name INTO v_name FROM users WHERE id=p_id; │ │
│ │ → 通常のクエリ処理パイプラインへ委譲 │ │
│ │ (Parser → Analyzer → Planner → Executor) │ │
│ │ → 結果を変数 v_name に格納 │ │
│ └────────────────────────────────────────────────────┘ │
│ ↓ │
│ ┌────────────────────────────────────────────────────┐ │
│ │ RETURN v_name; -- 戻り値を返す │ │
│ │ → v_name の値を関数の戻り値として設定 │ │
│ └─────── ─────────────────────────────────────────────┘ │
└──────────────────┬───────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────┐
│ ⑤ クリーンアップ │
│ - ローカル変数のメモリ解放 │
│ - エラーハンドラの解除 │
│ - 戻り値を呼び出し元に返す │
└──────────────────────────────────────────────────────────────┘
PL/pgSQL関数内でのSQL実行
PL/pgSQL関数内のSQL文は、通常のSQLと同じパイプラインで処理されます。
┌─────────────────────────────────────────────────────────────────┐
│ PL/pgSQL関数内でのSQL文の実行 │
└─────────────────────────────────────────────────────────────────┘
CREATE FUNCTION process_users() RETURNS VOID AS $$
DECLARE
v_user RECORD;
BEGIN
FOR v_user IN SELECT * FROM users WHERE age > 20 LOOP
-- ループ内の処理
UPDATE users SET last_processed = now() WHERE id = v_user.id;
END LOOP;
END;
$$ LANGUAGE plpgsql;
【実行時の処理】
1. FOR文に到達
↓
2. "SELECT * FROM users WHERE age > 20" を実行
┌──────────────────────────────────────────┐
│ 通常のクエリパイプラインで処理 │
│ Parser → Analyzer → Planner → Executor │
└──────────────────────────────────────────┘
↓
3. 結果セットを取得 (カーソルとして)
↓
4. 各行をループ
├─ v_user変数に行データを格納
├─ "UPDATE users ..." を実行
│ ┌──────────────────────────────────────────┐
│ │ 再度クエリパイプラインで処理 │
│ │ Parser → Analyzer → Planner → Executor │
│ └──────────────────────────────────────────┘
└─ 次の行へ
キャッシュと再コンパイル
┌─────────────────────────────────────────────────────────────────┐
│ 関数のキャッシュメカニズム │
├─────────────────────────────────────────────────────────────────┤
│ │
│ 【セッションごとのキャッシュ】 │
│ │
│ Session 1 Session 2 │
│ │ │ │
│ ├─ my_function() 初回呼び出し ├─ my_function() │
│ │ └─ コンパイル → キャッシュ │ └─ コンパイル │
│ │ │ │
│ ├─ my_function() 2回目 ├─ my_function() 2回目 │
│ │ └─ キャッシュから実行 │ └─ キャッシュから │
│ │ │ │
│ ├─ CREATE OR REPLACE FUNCTION ... (関数を再定義) │
│ │ │
│ ├─ my_function() 3回目 ├─ my_function() 3回目 │
│ │ └─ 再コンパイル │ └─ 旧版のまま │
│ │ │ (再接続で新版) │
│ │
│ 【キャッシュ無効化のタイミング】 │
│ - 関数が再定義された時 (CREATE OR REPLACE) │
│ - セッションが終了した時 │
│ - 依存オブジェクトが変更された時 │
│ │
└─────────────────────────────────────────────────────────────────┘
トリガー関数の実行フロー
トリガー関数は、テーブル操作（INSERT/UPDATE/DELETE）に連動して自動実行されます。
┌─────────────────────────────────────────────────────────────────┐
│ トリガー実行のライフサイクル │
└─────────────────────────────────────────────────────────────────┘
UPDATE users SET email = 'new@example.com' WHERE id = 1;
│
▼
┌──────────────────────────────────────────────────────────────┐
│ ① UPDATE文の解析・実行計画作成 │
│ (通常のクエリパイプライン) │
└──────────────────┬───────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────┐
│ ② BEFORE TRIGGERの実行 (行ごと) │
│ │
│ トリガー関数呼び出し: │
│ validate_email_trigger() │
│ │
│ ┌────────────────────────────────────────────┐ │
│ │ NEW: 新しい行データ (email = 'new@...') │ │
│ │ OLD: 古い行データ (email = 'old@...') │ │
│ │ │ │
│ │ IF NEW.email IS NULL THEN │ │
│ │ RAISE EXCEPTION 'Email required'; │ │
│ │ END IF; │ │
│ │ │ │
│ │ NEW.email := lower(NEW.email); -- 正規化 │ │
│ │ RETURN NEW; -- 変更後のNEWを返す │ │
│ └────────────────────────────────────────────┘ │
│ │
└──────────────────┬───────────────────────────────────────────┘
│ NEW が更新される
▼
┌──────────────────────────────────────────────────────────────┐
│ ③ 実際のデータ更新 │
│ users テーブルの該当行を更新 │
└──────────────────┬───────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────┐
│ ④ AFTER TRIGGERの実行 (行ごと) │
│ │
│ トリガー関数呼び出し: │
│ audit_log_trigger() │
│ │
│ ┌─── ─────────────────────────────────────────┐ │
│ │ INSERT INTO audit_log (...) │ │
│ │ VALUES ( │ │
│ │ TG_TABLE_NAME, -- 'users' │ │
│ │ TG_OP, -- 'UPDATE' │ │
│ │ row_to_json(OLD), │ │
│ │ row_to_json(NEW), │ │
│ │ now() │ │
│ │ ); │ │
│ │ RETURN NEW; │ │
│ └────────────────────────────────────────────┘ │
│ │
└──────────────────┬───────────────────────────────────────────┘
│
▼
トランザクション完了
動的SQLの実行フロー
EXECUTE文を使った動的SQLは、実行時にパース・最適化されます。
┌─────────────────────────────────────────────────────────────────┐
│ 動的SQLの実行フロー │
└─────────────────────────────────────────────────────────────────┘
CREATE FUNCTION dynamic_query(p_table_name TEXT) RETURNS BIGINT AS $$
DECLARE
v_count BIGINT;
BEGIN
EXECUTE format('SELECT COUNT(*) FROM %I', p_table_name)
INTO v_count;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;
【実行時の処理】
1. 関数呼び出し: SELECT dynamic_query('users');
↓
2. format()関数でSQL文字列を構築
'SELECT COUNT(*) FROM users'
↓
3. EXECUTE文に到達
↓
4. SQL文字列を動的にパース
┌──────────────────────────────────────────┐
│ Parser: 文字列 → 構文木 │
│ Analyzer: テーブル存在確認 │
│ Planner: 実行計画作成 │
│ Executor: 実行 │
└──────────────────────────────────────────┘
↓
5. 結果を v_count に格納
↓
6. RETURN v_count;
【注意点】
- 動的SQLは毎回パース・最適化が実行される
- プリペアドステートメントのようなキャッシュはない
- format()でSQLインジェクション対策が必須
PL/pgSQL vs SQL関数の実行コスト比較
┌─────────────────────────────────────────────────────────────────┐
│ SQL関数 vs PL/pgSQL関数 のコスト │
├─────────────────────────────────────────────────────────────────┤
│ │
│ 【SQL関数 (インライン化可能)】 │
│ │
│ CREATE FUNCTION get_tax(p_amount NUMERIC) │
│ RETURNS NUMERIC AS $$ │
│ SELECT p_amount * 0.1; │
│ $$ LANGUAGE sql IMMUTABLE; │
│ │
│ SELECT total_amount, get_tax(total_amount) FROM orders; │
│ ↓ │
│ プランナーがインライン化 │
│ ↓ │
│ SELECT total_amount, total_amount * 0.1 FROM orders; │
│ (関数呼び出しオーバーヘッドなし) │
│ │
│ ────────────────────────────────── ───────────────────────────── │
│ │
│ 【PL/pgSQL関数 (インライン化不可)】 │
│ │
│ CREATE FUNCTION get_tax_pl(p_amount NUMERIC) │
│ RETURNS NUMERIC AS $$ │
│ BEGIN │
│ RETURN p_amount * 0.1; │
│ END; │
│ $$ LANGUAGE plpgsql IMMUTABLE; │
│ │
│ SELECT total_amount, get_tax_pl(total_amount) FROM orders; │
│ ↓ │
│ 各行ごとに関数呼び出し │
│ ├─ 実行コンテキスト準備 │
│ ├─ バイトコード実行 │
│ └─ クリーンアップ │
│ (行数 × 関数呼び出しコスト) │
│ │
└─────────────────────────────────────────────────────────────────┘
【推奨】
- 単純な計算 → SQL関数 (IMMUTABLE指定でインライン化)
- 制御構造が必要 → PL/pgSQL関数
3. インデックスの内部構造
インデックスは「本の索引」のようなもので、データを高速に検索するための仕組みです。
3.1 なぜインデックスが必要か？
【インデックスなし】
SELECT * FROM users WHERE email = 'alice@example.com';
→ 100万行すべてをチェック (Sequential Scan)
→ 時間: 数秒
【インデックスあり】
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'alice@example.com';
→ インデックスで直接該当行を特定 (Index Scan)
→ 時間: 数ミリ秒
3.2 B-tree インデックス
PostgreSQLのデフォルトのインデックスタイプです。
B-treeの構造
┌─────────────────────┐
│ Root Node │
│ [30] [60] │
└──────┬──────┬───────┘
↙ ↓ ↘
┌───────────┐ ┌───────────┐ ┌───────────┐
│ Internal │ │ Internal │ │ Internal │
│ [10][20] │ │ [40][50] │ │ [70][80] │
└─────┬─────┘ └─────┬─────┘ └─────┬─────┘
↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓
┌─────┐ ┌─────┐ ┌─────┐
│Leaf │ ←→ │Leaf │ ←→ │Leaf │
│Nodes│ │Nodes│ │Nodes│
└─────┘ └─────┘ └─────┘
データへのポインタを含む
【特徴】
- 木構造で、ルートからリーフまでの深さが均一
- リーフノード同士が双方向リンク（範囲検索に有利）
- 検索・挿入・削除がO(log n)
B-treeが得意な検索
-- 等価検索
WHERE id = 100
-- 範囲検索
WHERE created_at > '2024-01-01'
WHERE price BETWEEN 1000 AND 5000
-- 前方一致
WHERE name LIKE 'Alice%'
-- ソート
ORDER BY created_at DESC
3.3 Hash インデックス
ハッシュ関数を使用したインデックスです。
┌──────────────────────────────────────────────────────────────┐
│ Hash Index │
├──────────────────────────────────────────────────────────────┤
│ │
│ キー値 → hash(キー値) → バケット番号 → データ位置 │
│ │
│ email = 'alice@example.com' │
│ ↓ │
│ hash('alice@example.com') = 12345 │
│ ↓ │
│ Bucket[12345 % N] → 該当行へのポインタ │
│ │
└──────────────────────────────────────────────────────────────┘
| 特徴 | 説明 |
|---|---|
| 得意 | 等価検索 (=) のみ |
| 苦手 | 範囲検索、ソート |
| 用途 | 等価検索が大部分を占める場合 |
3.4 GiST (Generalized Search Tree)
地理データや全文検索など、特殊なデータ型に対応するインデックスです。
-- 地理データの例
CREATE INDEX idx_locations ON places USING gist(coordinates);
-- 「この点から10km以内」のような検索が高速に
SELECT * FROM places
WHERE ST_DWithin(coordinates, ST_Point(139.7, 35.6), 10000);
GiSTの用途
- 地理情報（PostGIS）
- 範囲型（int4range, daterangeなど）
- 全文検索（tsvector）
3.5 GIN (Generalized Inverted Index)
「複数の値を含む」データに対するインデックスです。
┌──────────────────────────────────────────────────────────────┐
│ GIN Index (転置インデックス) │
├──────────────────────────────────────────────────────────────┤
│ │
│ 【配列カラムの例】 │
│ id=1: tags = ['python', 'web'] │
│ id=2: tags = ['java', 'web'] │
│ id=3: tags = ['python', 'ml'] │
│ │
│ 【GINインデックスの構造】 │
│ 'java' → [2] │
│ 'ml' → [3] │
│ 'python' → [1, 3] │
│ 'web' → [1, 2] │
│ │
│ WHERE tags @> ARRAY['python'] │
│ → 'python' で検索 → id 1, 3 を返す │
│ │
└──────────────────────────────────────────────────────────────┘
GINの用途
-- 配列
CREATE INDEX idx_tags ON articles USING gin(tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];
-- JSONB
CREATE INDEX idx_data ON events USING gin(data);
SELECT * FROM events WHERE data @> '{"type": "click"}';
-- 全文検索
CREATE INDEX idx_content ON documents USING gin(to_tsvector('english', content));
SELECT * FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('database');
3.6 BRIN (Block Range Index)
物理的に連続したデータに対する軽量インデックスです。
┌──────────────────────────────────────────────────────────────┐
│ BRIN Index │
├──────────────────────────────────────────────────────────────┤
│ │
│ テーブルのブロック範囲ごとに最小値・最大値を記録 │
│ │
│ Block 1-128: created_at: 2024-01-01 〜 2024-01-15 │
│ Block 129-256: created_at: 2024-01-16 〜 2024-01-31 │
│ Block 257-384: created_at: 2024-02-01 〜 2024-02-14 │
│ ... │
│ │
│ WHERE created_at = '2024-01-20' │
│ → Block 129-256 のみをスキ ャン │
│ │
└──────────────────────────────────────────────────────────────┘
BRINの特徴
| 項目 | 説明 |
|---|---|
| サイズ | 非常に小さい（B-treeの1/100以下も） |
| 適した用途 | 時系列データなど、物理的に順序づけられたデータ |
| 検索速度 | B-treeより遅いが、十分実用的 |
3.7 インデックス選択の指針
┌─────────────────────────────────────────────────────────────┐
│ インデックス選択フローチャート │
└─────────────────────────────────────────────────────────────┘
データ型は？
│
├─ 単純な値 (数値、文字列、日付など)
│ │
│ └─ 検索パターンは？
│ ├─ 等価・範囲・ソート → B-tree
│ ├─ 等価のみ → Hash (または B-tree)
│ └─ 時系列で物理順序あり → BRIN
│
├─ 配列・JSONB・全文検索
│ └─ GIN
│
└─ 地理データ・範囲型
└─ GiST
4. MVCCとトランザクション
4.1 MVCC とは？
MVCC (Multi-Version Concurrency Control) は、PostgreSQLの同時実行制御の仕組みです。複数のトランザクションが同じデータに同時にアクセスしても、互いにブロックされにくい設計になっています。
基本原理：データの複数バージョン
┌──────────────────────────────────────────────────────────────┐
│ MVCCの基本原理 │
├──────────────────────────────────────────────────────────────┤
│ │
│ 【従来の方式】ロ ックベース │
│ トランザクションA: データを更新中 → ロック取得 │
│ トランザクションB: 同じデータを読みたい → ロック待ち！ │
│ │
│ 【PostgreSQL】MVCC │
│ トランザクションA: 新バージョンを作成（旧バージョンは残す） │
│ トランザクションB: 旧バージョンを読める → 待ち不要！ │
│ │
└──────────────────────────────────────────────────────────────┘
4.2 タプルのバージョン管理
PostgreSQLでは、行（タプル）に「いつ作られたか」「いつ削除されたか」の情報を持たせています。
┌──────────────────────────────────────────────────────────────┐
│ タプルのヘッダ情報 │
├──────────────────────────────────────────────────────────────┤
│ │
│ 各タプルには以下の情報が含まれる： │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ xmin: このタプルを作成したトランザクションID │ │
│ │ xmax: このタプルを削除/更新したトランザクションID │ │
│ │ (0 = まだ削除されていない) │ │
│ │ cmin, cmax: コマンド番号（同一トランザクション内の順序）│ │
│ │ ctid: 物理的な位置 (ページ番号, 行番号) │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘
UPDATE時の動作
【UPDATE前】
Page内:
┌──────────────────────────────────────────┐
│ Tuple1: id=1, name='Alice', xmin=100, xmax=0 │
└──────────────────────────────────────────┘
【UPDATE実行】(トランザクションID=150)
UPDATE users SET name='Bob' WHERE id=1;
【UPDATE後】
Page内:
┌──────────────────────────────────────────┐
│ Tuple1: id=1, name='Alice', xmin=100, xmax=150 │ ← 旧版（削除済みマーク）
│ Tuple2: id=1, name='Bob', xmin=150, xmax=0 │ ← 新版
└──────────────────────────────────────────┘
4.3 可視性の判定
各トランザクションが「どのバージョンを見るか」は、以下のルールで決まります：
┌──────────────────────────────────────────────────────────────┐
│ タプル可視性の判定 │
├──────────────────────────────────────────────────────────────┤
│ │
│ あるタプルが見えるかどうか： │
│ │
│ 1. xmin（作成TX）がコミット済み AND │
│ xmin が自分のスナップショットより前 │
│ → 作成が確定している │
│ │
│ 2. xmax（削除TX）がない OR │
│ xmax がまだコミットしていない OR │
│ xmax が自分のスナップショットより後 │
│ → まだ削除されていない（自分から見て） │
│ │
│ 両方満たせば → 見える！ │
│ │
└──────────────────────────────────────────── ──────────────────┘
4.4 トランザクション分離レベル
PostgreSQLは4つの分離レベルをサポートしています。
| 分離レベル | 特徴 | PostgreSQLでの実装 |
|---|---|---|
| Read Uncommitted | 最も緩い（コミット前も見える） | Read Committed として扱われる |
| Read Committed | コミット済みのみ見える（デフォルト） | 文ごとに新しいスナップショット |
| Repeatable Read | トランザクション開始時のスナップショットで固定 | スナップショット分離 |
| Serializable | 完全な直列化 | SSI (Serializable Snapshot Isolation) |
Read Committed vs Repeatable Read
【Read Committed】
時刻1: TX-A 開始
時刻2: TX-A: SELECT balance FROM accounts WHERE id=1; → 1000
時刻3: TX-B: UPDATE accounts SET balance=500 WHERE id=1; COMMIT;
時刻4: TX-A: SELECT balance FROM accounts WHERE id=1; → 500 (TX-Bの結果が見える！)
【Repeatable Read】
時刻1: TX-A 開始 (スナップショット取得)
時刻2: TX-A: SELECT balance FROM accounts WHERE id=1; → 1000
時刻3: TX-B: UPDATE accounts SET balance=500 WHERE id=1; COMMIT;
時刻4: TX-A: SELECT balance FROM accounts WHERE id=1; → 1000 (同じ値！)
4.5 ロックの種類
MVCCでも、一部の操作ではロックが必要です。
┌──────────────────────────────────────────────────────────────┐
│ 主なロックの種類 │
├──────────────────────────────────────────────────────────────┤
│ │
│ 【テーブルレベルロック】 │
│ ├─ ACCESS SHARE : SELECT │
│ ├─ ROW SHARE : SELECT FOR UPDATE/SHARE │
│ ├─ ROW EXCLUSIVE : INSERT, UPDATE, DELETE │
│ ├─ SHARE UPDATE EXCLUSIVE: VACUUM, CREATE INDEX CONCURRENTLY│
│ ├─ SHARE : CREATE INDEX │
│ ├─ SHARE ROW EXCLUSIVE : CREATE TRIGGER │
│ ├─ EXCLUSIVE : REFRESH MATERIALIZED VIEW │
│ └─ ACCESS EXCLUSIVE : ALTER TABLE, DROP TABLE │
│ │
│ 【行レベルロック】 │
│ ├─ FOR UPDATE : 更新用にロック │
│ ├─ FOR NO KEY UPDATE : キー以外の更新用 │
│ ├─ FOR SHARE : 読み取り用の共有ロック │
│ └─ FOR KEY SHARE : 外部キー参照用 │
│ │
└──────────────────────────────────────────────────────────────┘
5. バキュームの仕組み
5.1 なぜVACUUMが必要か？
MVCCでは、UPDATE/DELETEで古いバージョンが残り続けます。これを放置すると：
- ディスク容量が増え続ける
- テーブルが肥大化してパフォーマンス低下
- トランザクションIDの周回問題（後述）
┌──────────────────────────────────────────────────────────────┐
│ テーブル肥大化の例 │
├──────────────────────────────────────────────────────────────┤
│ │
│ 【初期状態】 │
│ 有効な行: 1000件、ファイルサイズ: 1MB │
│ │
│ 【1ヶ月後】毎日全件UPDATEを実行 │
│ 有効な行: 1000件、ファイルサイズ: 30MB ← 死んだ行が蓄積！ │
│ │
│ 【VACUUMなし】 │
│ - 死んだ行もスキャン対象 → クエリが遅くなる │
│ - ディスクが無駄に消費される │
│ │
└──────────────────────────────────────────────────────────────┘
5.2 VACUUMの種類
┌──────────────────────────────────────────────────────────────┐
│ VACUUMの種類 │
├──────────────────────────────────────────────────────────────┤
│ │
│ ① VACUUM (通常) │
│ - 死んだタプルを「再利用可能」としてマーク │
│ - ディスク容量は解放されない（OSには返さない） │
│ - テーブルをロックしない（同時に読み書き可能） │
│ │
│ ② VACUUM FULL │
│ - テーブルを完全に再構築 │
│ - ディスク容量を解放（OSに返す） │
│ - 排他ロックが必要（その間アクセス不可） │
│ - 時間がかかる │
│ │
│ ③ VACUUM ANALYZE │
│ - VACUUM + 統計情報の更新 │
│ - プランナーの判断に使う統計を最新化 │
│ │
└──────────────────────────────────────────────────────────────┘
5.3 VACUUMの処理フロー
【VACUUM の処理ステップ 】
Step 1: 死んだタプルの特定
┌─────────────────────────────────────────┐
│ テーブルをスキャンして、どのトランザクション │
│ からも見えなくなったタプルを探す │
└─────────────────────────────────────────┘
↓
Step 2: 死んだタプルの回収
┌─────────────────────────────────────────┐
│ 見つけた死んだタプルの領域を │
│ 「Free Space Map」に登録 │
│ → 次のINSERTで再利用可能に │
└─────── ──────────────────────────────────┘
↓
Step 3: インデックスのクリーンアップ
┌─────────────────────────────────────────┐
│ インデックスから死んだタプルへの │
│ ポインタを削除 │
└─────────────────────────────────────────┘
↓
Step 4: Visibility Mapの更新
┌─────────────────────────────────────────┐
│ 「全タプルが全トランザクションから見える」 │
│ ページをマーク（Index Only Scanに活用） │
└─────────────────────── ──────────────────┘
5.4 Autovacuum
PostgreSQLには自動でVACUUMを実行する仕組みがあります。
┌──────────────────────────────────────────────────────────────┐
│ Autovacuumの仕組み │
├──────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────────┐ │
│ │ Autovacuum │ │
│ │ Launcher │ 定期的にチェック（デフォルト1分） │
│ └────────┬─────────┘ │
│ │ 条件を満たしたテーブルに対して │
│ ▼ │
│ ┌──────────────────┐ ┌──────────────────┐ │
│ │ Autovacuum │ │ Autovacuum │ ... │
│ │ Worker 1 │ │ Worker 2 │ │
│ └──────────────────┘ └──────────────────┘ │
│ │
│ 【起動条件】 │
│ dead tuples > autovacuum_vacuum_threshold │
│ + autovacuum_vacuum_scale_factor × テーブル行数 │
│ │
│ デフォルト: 50 + 0.2 × テーブル行数 │
│ 例: 10000行のテーブル → 2050件の死んだ行でVACUUM起動 │
│ │
└──────────────────────────────────────────────────────────────┘
5.5 トランザクションIDの周回問題
PostgreSQLのトランザクションIDは32ビット（約42億）で、使い切ると周回します。この対策もVACUUMの重要な役割です。
┌──────────────────────────────────────────────────────────────┐
│ トランザクションID周回問題 │
├──────────────────────────────────────────────────────────────┤
│ │
│ 【問題】 │
│ トランザクションID（XID）は約42億で一周する │
│ 古いXIDと新しいXIDの区別がつかなくなる可能性 │
│ │
│ XID: 1 → 2 → ... → 2^31 → 2^31+1 → ... → 2^32 → 1 │
│ ↑ 周回! │
│ │
│ 【対策: Freeze処理】 │
│ VACUUMが古いタプルのXIDを「FrozenXID」に置き換える │
│ FrozenXIDは「全トランザクションから見える」特別なID │
│ │
│ 【モニタリング】 │
│ SELECT datname, age(datfrozenxid) FROM pg_database; │
│ → 20億に近づいたら要注意 │
│ │
└──────────────────────────────────────────────────────────────┘
6. レプリケーションとHA構成
6.1 レプリケーションの概要
レプリケーショ ンとは、データを複数のサーバーに複製する仕組みです。
┌──────────────────────────────────────────────────────────────┐
│ レプリケーションの目的 │
├──────────────────────────────────────────────────────────────┤
│ │
│ ① 可用性の向上 (High Availability) │
│ - プライマリが故障しても、スタンバイに切り替えて継続 │
│ │
│ ② 読み取り負荷の分散 (Read Scaling) │
│ - 読み取りクエリをスタンバイに分散 │
│ │
│ ③ 災害対策 (Disaster Recovery) │
│ - 地理的に離れた場所にデータを複製 │
│ │
└──────────────────────────────────────────────────────────────┘
6.2 ストリーミングレプリケーション
PostgreSQL標準のレプリケーション方式です。WALを使ってデータを複製します。
┌──────────────────────────────────────────────────────────────┐
│ ストリーミングレプリケーション │
├──────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────┐ WAL Stream ┌─────────────┐
│ │ Primary │ ─────────────────→ │ Standby │
│ │ (読み書き可能) │ │ (読み取り専用)│
│ └─────────────────────┘ └─────────────┘
│ │ │ │
│ ▼ ▼ │
│ ┌───────────┐ ┌───────────┐ │
│ │ WAL Files │ │ WAL Files │ │
│ └───────────┘ └───────────┘ │
│ │ │ │
│ ▼ ▼ │
│ ┌───────────┐ ┌───────────┐ │
│ │Data Files │ │Data Files │ │
│ └───────────┘ └───────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘
【データの流れ】
1. Primary で更新が発生
2. WAL に記録
3. WAL Sender プロセスが WAL を Standby に送信
4. Standby の WAL Receiver が受信
5. Standby で WAL を再生（リカバリ）
6.3 同期レプリケーション vs 非同期レプリケーション
┌──────────────────────────────────────────────────────────────┐
│ 同期 vs 非同期 │
├──────────────────────────────────────────────────────────────┤
│ │
│ 【非同期レプリケーション】(デフォルト) │
│ Primary: COMMIT完了 → クライアントに応答 │
│ → 後か らStandbyに送信 │
│ │
│ メリット: 高速（Standbyの応答を待たない） │
│ デメリット: Primary障害時、一部データ損失の可能性 │
│ │
│ ────────────────────────────────────────────── │
│ │
│ 【同期レプリケーション】 │
│ Primary: COMMIT → Standbyに送信 → Standbyの確認を待つ │
│ → クライアントに応答 │
│ │
│ メリット: データ損失なし │
│ デメリット: 遅延が発生（Standbyの応答待ち） │
│ │
└──────────────────────────────────────────────────────────────┘
6.4 論理レプリケーション
テーブル単位で複製できる、より柔軟なレプリケーション方式です。
┌───── ─────────────────────────────────────────────────────────┐
│ 論理レプリケーション │
├──────────────────────────────────────────────────────────────┤
│ │
│ 【ストリーミング vs 論理】 │
│ │
│ ストリーミング: │
│ - 物理的なWALをそのまま送信 │
│ - 完全なコピー（全テーブル） │
│ - PostgreSQL同士のみ │
│ │
│ 論理レプリケーション: │
│ - WALをデコードして論理的な変更に変換 │
│ - テーブル単位で選択可能 │
│ - 異なるバージョン間、異なるDBへも可能 │
│ │
│ 【構成要素】 │
│ ┌──────────────┐ Publication ┌──────────────┐ │
│ │ Publisher │ ────────────→ │ Subscriber │ │
│ │ (送信元) │ │ (受信先) │ │
│ └──────────────┘ └──────────────┘ │
│ │
└────────────── ────────────────────────────────────────────────┘
-- Publisher側
CREATE PUBLICATION my_pub FOR TABLE users, orders;
-- Subscriber側
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=primary dbname=mydb'
PUBLICATION my_pub;
6.5 HA (High Availability) 構成
自動フェイルオーバーを実現するための一般的な構成パターンです。
┌──────────────────────────────────────────────────────────────┐
│ HA構成の例 │
├──────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Load Balancer │ │
│ │ (または VIP / DNS切り替え) │ │
│ └────────────────────────┬────────────────────────────┘ │
│ │ │
│ ┌───────────────┼───────────────┐ │
│ ▼ ▼ ▼ │
│ ┌────────────┐ ┌────────────┐ ┌────────────┐ │
│ │ Primary │ │ Standby 1 │ │ Standby 2 │ │
│ │ (Active) │ │ (Sync) │ │ (Async) │ │
│ └─────┬──────┘ └─────┬──────┘ └─────┬──────┘ │
│ │ │ │ │
│ └───────────────┼───────────────┘ │
│ │ │
│ ┌─────────────────────┴─────────────────────┐ │
│ │ Failover Manager │ │
│ │ (Patroni, repmgr, pg_auto_failover など) │ │
│ └───────────────────────────────────────────┘ │
│ │
│ 【フェイルオーバーの流れ】 │
│ 1. Primary の障害を検知 │
│ 2. Standby を新 Primary に昇格 (pg_ctl promote) │
│ 3. VIP / DNS を切り替え │
│ 4. 他の Standby を新 Primary に接続し直す │
│ │
└──────────────────────────────────────────────────────────────┘
6.6 よく使われるHAツール
| ツール | 特徴 |
|---|---|
| Patroni | Kubernetes/etcd/Consul対応、最も人気 |
| repmgr | シンプルで歴史がある |
| pg_auto_failover | Citus（Microsoft）製、シンプル |
| Pgpool-II | 接続プーリング + HA |
まとめ
このドキュメントで解説した内容の要点をまとめます。
| トピック | ポイント |
|---|---|
| アーキテクチャ | マルチプロセス、共有メモリ、WALによる永続化 |
| クエリ実行 | Parser → Analyzer → Rewriter → Planner → Executor |
| インデックス | B-tree（基本）、GIN（配列/JSONB）、GiST（地理）、BRIN（時系列） |
| MVCC | 複数バージョン管理、スナップショット分離、行レベルロック |
| VACUUM | 死んだタプル回収、Autovacuum、トランザクションID周回対策 |
| レプリケーション | ストリーミング（物理）、論理、同期/非同期 |
これらの仕組みを理解することで、より効率的なクエリの書き方、適切なインデックス設計、パフォーマンスチューニングができるようになります。
参考リンク
- PostgreSQL公式ドキュメント
- PostgreSQL内部構造（公式）
- The Internals of PostgreSQL - 詳細な内部構造解説（無料オンライン書籍）