✍️ 필사 모드: PostgreSQL 内部完全攻略 — MVCC、VACUUM、WAL、Query Planner、Index、Partitioning、pgvector まで (2025)

"Postgres is not a fashion. It's a philosophy." — Tom Lane (PostgreSQL major contributor, 20+ years)

2024 年 Stack Overflow Developer Survey で PostgreSQL が初めて MySQL を抜いて 1 位。偶然ではない。過去 10 年で Postgres は「JSON は MongoDB より上手く、ベクトル検索は Pinecone より強く、分析も ClickHouse 級」という怪物に進化した。

1986 年 UC Berkeley の Michael Stonebraker による POSTGRES プロジェクトが起源。1996 年に SQL サポートを追加し PostgreSQL に。以後 30 年、哲学は ACID 完全性、拡張性、標準準拠。本稿は「とりあえず使う」から「内部を理解してチューニングする」へ移る人のための地図。

1. MVCC — PostgreSQL の心臓

MVCC はなぜ革命的か

従来 DB は読み取りロック: A が読むと B は書けない。OLTP 性能キラー。

MVCC (Multi-Version Concurrency Control): 各トランザクションにその時点のスナップショットを見せる。読み書きが互いをブロックしない。

"Readers don't block writers, writers don't block readers."

Oracle vs PostgreSQL

Oracle: Undo Segment に旧版、現行版はメインテーブル。 PostgreSQL: すべてのバージョンをテーブルに保存、dead tuple は VACUUM で掃除。

Postgres のやり方はシンプルだが代償がある: テーブルが必然的に膨張する。これが VACUUM の宿命。

Tuple の属性 — xmin, xmax

各行 (tuple) には隠しシステム列がある。

t_xmin   — この tuple を作ったトランザクション ID
t_xmax   — 削除/更新したトランザクション ID (0 ならまだ生存)
t_cmin   — 同一トランザクション内コマンド順

トランザクションは自身のスナップショット (開始時アクティブ XID 集合) で判定。

• t_xmin < MyXid で commit 済、t_xmax が無いか未コミット → 可視
• それ以外 → 不可視

ロック無しで読取一貫性。

UPDATE の真実 — 「その場更新」は存在しない

UPDATE users SET name = 'Alice' WHERE id = 1;

実際は:

1. 旧 tuple の t_xmax に現 TX ID (論理削除)
2. 新 tuple 挿入、t_xmin = 現 TX ID
3. 関連インデックスもすべて新ポインタ追加 (HOT update 例外)

PostgreSQL の UPDATE は INSERT 級のコスト、write amplification を生む。HOT (Heap-Only Tuple) はインデックスキー不変時に同ページに配置しインデックス更新を回避。

2. VACUUM — 避けられぬ宿命

VACUUM の役割

1. Dead tuple 再利用 — テーブル/インデックス領域回収
2. Visibility Map 更新 — Index-Only Scan 可能領域を記録
3. Free Space Map 更新 — INSERT が使う空間追跡
4. XID Wraparound 防止 — frozen マーキング
5. 統計収集 (ANALYZE と共に)

VACUUM FULL vs VACUUM

• VACUUM — 領域を再利用可能マーク、テーブルサイズは維持
• VACUUM FULL — テーブル再書込、サイズ縮小、AccessExclusiveLock (サービス停止級)

本番では pg_repack 拡張によるオンライン repack を使う。

XID Wraparound — 32 ビットの呪い

トランザクション ID は 32 ビット (約 42 億)。枯渇すると過去/未来の区別不能、DB が読取専用に。

防御:

• VACUUM が古い tuple を FrozenXID に → 永久可視
• autovacuum_freeze_max_age (既定 2 億) 超で強制 vacuum freeze

2020 年代に大規模サービスで wraparound 障害報告あり。PG 17 以降 64 ビット XID 議論が本格化。18 でもまだ 32 ビット。

autovacuum チューニング — 大抵ここで詰まる

既定値は小規模 DB 想定。数千万行テーブルでは:

autovacuum_vacuum_scale_factor = 0.02  # 既定 0.2 → 2%
autovacuum_naptime = 15s               # 既定 1min → 頻度上げる
autovacuum_max_workers = 6             # CPU に合わせる
autovacuum_vacuum_cost_limit = 2000   # 既定 200、I/O 許容量↑

Dead tuple 比率監視が要:

SELECT relname, n_dead_tup, n_live_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables ORDER BY dead_pct DESC NULLS LAST;

3. WAL — Write-Ahead Log の優雅

WAL の原則

「データファイル変更前にログに先行記録」

この一行が:

• クラッシュリカバリ
• レプリケーション
• Point-in-Time Recovery (PITR)
• Logical Decoding

すべての基盤。

WAL 書き込みフロー

1. トランザクションがデータ変更
2. WAL レコードを WAL バッファに書く
3. COMMIT 時に WAL を fsync
4. データファイル自体は後で CHECKPOINT が処理

commit 時に実データがディスクに無くても良い。WAL さえ永続なら復旧可能。

CHECKPOINT

• 定期的に dirty page をディスクへ flush
• 頻繁すぎ → I/O 爆発
• 稀すぎ → WAL 増大、復旧時間↑
• checkpoint_timeout (既定 5min)、max_wal_size (既定 1GB) で制御

レプリケーション — Physical vs Logical

Physical Replication (Streaming):

• WAL をバイト単位で Standby に転送
• 完全同一
• クラスタ単位
• 同期/非同期/クォーラム対応

Logical Replication (10+):

• WAL をデコードし論理 SQL 変更へ
• テーブル/カラム単位選択
• 異バージョン PG 間レプリ可
• アップグレード/マイグレーション必須

同期レプリ設定

synchronous_commit = on
synchronous_standby_names = 'FIRST 2 (replica1, replica2, replica3)'

3 のうち 2 の ACK を待つ。1 台障害でも commit 継続。

4. Query Planner — 「なぜ同じクエリが突然遅くなるのか」

Parse → Rewrite → Plan → Execute

1. Parser: SQL → AST
2. Rewriter: ビュー展開、ルール適用
3. Planner: 複数計画から最小コスト選択 ← 核
4. Executor: 実行

コストモデル

各操作のコスト単位:

• seq_page_cost = 1.0 (順次)
• random_page_cost = 4.0 (ランダム)
• cpu_tuple_cost = 0.01
• cpu_index_tuple_cost = 0.005
• cpu_operator_cost = 0.0025

SSD なら random_page_cost = 1.1 くらいに下げるのがほぼ常に得。

統計がすべて

Planner は pg_statistic からカーディナリティ推定:

• n_distinct — 一意値数推定
• most_common_vals — 頻出値
• histogram_bounds — 分布

ANALYZE が回らないと統計が古くなり Planner 誤判。

EXPLAIN ANALYZE の読み方

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';

Index Scan using idx_user_status on orders
  (cost=0.43..125.67 rows=12 width=80)
  (actual time=0.045..0.312 rows=15 loops=1)
  Index Cond: ((user_id = 123) AND (status = 'paid'::text))
  Buffers: shared hit=8 read=0

順番:

1. ノード型: Index Scan, Seq Scan, Hash Join, Merge Join, Nested Loop
2. cost: 推定開始-終了
3. rows: 推定 vs 実 — 10 倍以上ズレたら統計問題
4. Buffers: hit (キャッシュ)/read (ディスク)
5. loops: Nested Loop 内ループ回数

よくある問題パターン

5. Index — 6 種を使い分ける

B-Tree — 既定にして王

• 汎用: =, <, >, BETWEEN, ORDER BY
• 複合インデックスは左端から利用可
• PG 12 以降 重複排除でサイズ 30%+ 減

Hash

• = のみ、範囲不可
• PG 10 で WAL 対応しようやく crash-safe
• ほぼ使わない (B-Tree が大抵優)

GiST — Generalized Search Tree

• 幾何 (PostGIS)、範囲型、全文
• プラガブル — 独自型に合わせたインデックス作成可
• 例: CREATE INDEX ON events USING GIST (during) (tsrange)

GIN — Generalized Inverted Index

• 多値 (配列、JSONB、tsvector) に最適
• 各「トークン」→ 出現文書の逆インデックス
• JSONB インデックスの既定
• 書込コスト大、fastupdate で緩和

BRIN — Block Range Index

• 物理的に整列された巨大テーブル (時系列、ログ) 専用
• ブロック範囲の min/max のみ → 極小
• 10TB 時系列に BRIN 100MB 以下も可
• 条件: データが insert 順に整列

HNSW (pgvector) — ベクトル検索の標準

• コアでなく pgvector 拡張 (0.5.0+)
• Hierarchical Navigable Small World グラフ
• ANN (Approximate Nearest Neighbor) 検索
• 2024 年以降事実上 RAG 標準

CREATE EXTENSION vector;
CREATE TABLE items (id bigint, embedding vector(1536));
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
SELECT id FROM items ORDER BY embedding <=> '[...]'::vector LIMIT 10;

インデックス選択ガイド

Partial & Expression Index

-- 条件付きインデックス
CREATE INDEX ON orders (user_id) WHERE status = 'paid';

-- 式インデックス
CREATE INDEX ON users (lower(email));

-- Covering index (PG 11+)
CREATE INDEX ON orders (user_id) INCLUDE (total, created_at);

Partial でサイズが劇的に縮小。

6. Partitioning — 関係 DB のシャーディング

Declarative Partitioning — PG 10 以降

CREATE TABLE events (
  id bigserial,
  user_id bigint,
  occurred_at timestamp
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_04 PARTITION OF events
  FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

戦略

• RANGE — 時間、範囲 (最多)
• LIST — カテゴリ (country, region)
• HASH — 均等分散 (シャーディング)

パーティションプルーニング

WHERE がパーティションキー参照 → 該当パーティションのみスキャン。

WHERE occurred_at >= '2026-04-15'
  → events_2026_04 のみ

自動管理 — pg_partman

毎月手動で作るのは自殺行為。pg_partman が自動生成/削除。

Citus — Postgres を水平シャード

2019 年 Microsoft 買収。複数 Postgres ノードに distributed table:

• シャードキーで自動分配
• 分散クエリ実行
• 並列 INSERT/SELECT

2024 年、Citus が Azure Cosmos DB for PostgreSQL のエンジンに。自前運用も可。

7. 接続管理 — なぜ Postgres の接続は高価か

プロセスモデル

PostgreSQL は接続ごとにプロセス (スレッド無し)。利点:

• 隔離 — 一接続のクラッシュが全体に波及しない
• 安定 — 数十年検証

欠点:

• メモリ — 接続あたり 10-20MB
• 接続生成 — 数 ms
• 1000+ 接続で過負荷

pgBouncer — 接続プールの標準

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
pool_mode = transaction
default_pool_size = 20
max_client_conn = 1000

pool_mode:

• session — クライアント = サーバー (1:1、プールの意味無)
• transaction — トランザクション中のみ (最多)
• statement — クエリごと (極めて限定)

Transaction Mode 注意

• Prepared Statement 非互換 — PG 17+ で改善
• SET LOCAL のみ、SET 不可
• LISTEN/NOTIFY 不可
• ドライバ設定必須 (例: PreparedStatementCacheSize=0)

PgCat & Supavisor

• PgCat — Rust 製 pgBouncer 代替、シャーディング対応
• Supavisor — Supabase 製 Elixir ベース、数百万接続

経験則

• プールサイズ = core_count * 2 + spindle_count (PostgreSQL wiki)
• 通常 20-50 がスイートスポット
• 増やしてもスループット不変、レイテンシのみ増

8. JSONB — 「Postgres で全部」の真実

JSON vs JSONB

ほぼ常に JSONB。

GIN インデックス戦略

-- 汎用 (大きく柔軟)
CREATE INDEX ON docs USING GIN (data);

-- パス演算子 (`@>`) 専用 (小)
CREATE INDEX ON docs USING GIN (data jsonb_path_ops);

jsonb_path_ops は約 30% 小、@> 照会には十分。他演算子 (?, ?|, ?&) は汎用型のみ対応。

MongoDB 代替になるか

• スキーマ柔軟性: JSONB が近接
• 性能: 小文書は近似、巨大文書は Mongo 優位
• 集約: Postgres の SQL が遥かに強力
• トランザクション: Postgres 勝ち (Mongo 4.0+ 追随も制約)
• 書込スケール: Mongo シャーディングが成熟 (Citus あり)

結論: 極端な規模でなければ Postgres が「文書 + 関係 + 分析」を 1 DB で解決。

9. AI 時代の Postgres — pgvector と pg_duckdb

pgvector (2023 年以降爆発)

• 2021 年 Andrew Kane 作
• 2023 年 HNSW 追加で本番可
• 2024 年 pgvectorscale (Timescale) で 10 倍高速化
• 2025 年には Supabase、Neon、RDS すべて標準搭載

DiskANN & Binary Quantization

• DiskANN インデックス (pgvectorscale) — メモリ超のベクトル集合をディスク検索
• Binary Quantization — float32 → 1bit、メモリ 32 倍削減

pg_duckdb (2024 年末)

DuckDB を PostgreSQL に埋込。分析クエリを DuckDB へ。

SELECT duckdb.query('
  SELECT date_trunc(''hour'', ts), count(*)
  FROM read_parquet(''s3://logs/*.parquet'')
  GROUP BY 1
');

OLTP は Postgres、OLAP は DuckDB を 1 DB で。2025 年 HTAP 戦争のダークホース。

AI ネイティブ PG エコシステム

• Neon — サーバーレス、ブランチング、copy-on-write
• Supabase — Postgres + pgvector + Realtime + Auth バンドル
• Timescale — 時系列 + pgvector + AI 最適化
• MotherDuck + DuckDB — OLAP 分離

10. PostgreSQL 18 (2025) 新機能

AIO — Asynchronous I/O

• 従来は同期 I/O
• 18 から io_uring (Linux) 対応 → 30-50% 高速な順次スキャン
• 自動 read-ahead 改善

Direct I/O

• OS ページキャッシュ迂回、PG が直接管理 (shared_buffers を大きく)
• io_direct = data オプション

UUIDv7 ネイティブ対応

• UUIDv4 はランダム → インデックス断片化
• UUIDv7 は時刻整列 → B-Tree 親和
• gen_uuid_v7() ビルトイン

Logical Replication 改善

• DDL レプリ (CREATE TABLE も)
• 双方向レプリ (BDR 的) の基盤

Skip Scan

• 複合インデックスで先頭列条件無しでも利用
• 「Loose Index Scan」— Oracle/MySQL には既存機能

11. 監視 — 見るべき指標

pg_stat_statements

全クエリの累積統計。最重要拡張。

SELECT query, calls, total_exec_time/1000 AS total_sec,
       mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

核心指標

長寿命トランザクションの呪い

長時間開いた TX は:

• VACUUM を阻害 (その TX が見られる tuple は掃除不可)
• dead tuple 急増 → テーブル膨張
• idle_in_transaction_session_timeout で防御

12. アンチパターン TOP 10

1. ORM の N+1 — EXPLAIN を見て驚かず、INCLUDE / JOIN
2. BEGIN 後アイドル — 長時間放置、VACUUM ブロック
3. 短命接続の乱発 — pgBouncer 無しで直結
4. ANALYZE 未実行 — 統計古で Planner 誤判
5. 本番で VACUUM FULL — AccessExclusive ロック、停止
6. 全列インデックス — 書込低下、維持コスト↑
7. Prepared Statement + pgBouncer transaction mode — 非互換
8. UUIDv4 PK 乱用 — インデックス断片化 (→ UUIDv7)
9. SELECT * — Planner 最適化機会喪失
10. 巨大 JSONB 全更新 — TOAST 再書込爆発

13. Postgres を賢く使うチェックリスト

• autovacuum チューニング — scale_factor、cost_limit
• pg_stat_statements 常時有効化
• ANALYZE 周期確認、統計ターゲット調整
• pgBouncer (transaction mode) 展開
• random_page_cost を SSD 基準に調整
• work_mem 慎重に上げる (接続ごと乗算)
• shared_buffers を RAM の 25%
• レプリ lag 監視 — slot 堆積確認
• Long-running TX タイムアウト設定
• pg_stat_user_tables dead_pct アラート
• WAL 保管/アーカイブ — PITR 可能に
• 主要クエリ EXPLAIN ANALYZE ベースライン保存

結び — 「Postgres で全部できる」の真意

"Postgres everything" ミームは事実。OLTP、OLAP (pg_duckdb)、ベクトル検索 (pgvector)、時系列 (Timescale)、地理情報 (PostGIS)、グラフ (Apache AGE)、全文検索 (GIN)、さらにはメッセージキュー (pg_later) まで。

だがこれは「あらゆるワークロードを Postgres に押し込め」ではない。**「1 DB で大半を賄え、運用複雑度を下げられる」**の意味。極限規模 (数十 TB OLAP、毎秒 100 万イベント) では専用システムが依然必要。

Postgres を扱うとは MVCC の tuple モデル、VACUUM のコスト、WAL の役割、Planner の統計依存を理解すること。これ無しで「なぜ遅いか」をデバッグするのは地図無しの洞窟探検。

次回予告 — Elasticsearch/OpenSearch と検索の科学

Postgres が「構造化世界の王」なら、Elasticsearch/OpenSearch は「非構造テキストの王」。次稿:

• Inverted Index の本質 — Lucene が GIN より速い理由
• Lucene 内部 — Segment、Commit、Merge 戦略
• BM25 vs TF-IDF — スコアリングの数学
• Sharding & Replication — primary/replica、routing
• Ingest パイプライン — Logstash、Beats、OpenTelemetry → ES
• Query DSL の森 — bool、match、term、function_score
• Vector Search in ES — kNN、HNSW、ハイブリッド取得
• 2021 年ライセンス騒動 — Elastic vs AWS、OpenSearch フォーク
• 運用の痛み — Heap、GC、split brain、circuit breakers
• Hybrid Search & RAG — BM25 + Vector = 答え

検索の歴史と未来を一気に。

"The first rule of Postgres: never fight the Planner. Give it good statistics, and it will give you good plans. The second rule: know where the bodies are buried — MVCC, VACUUM, WAL. Everything else is commentary." — Bruce Momjian (PostgreSQL Global Development Group)