LabHub

ブログ

PostgreSQL 内部完全攻略 — MVCC、VACUUM、WAL、Query Planner、Index、Partitioning、pgvector まで (2025)

2026-04-15 · 18 分で読めます · #postgresql#database#mvcc#query-optimization#infra#culture#japanese

한국어English日本語

"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 集合) で判定。

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

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

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

XID Wraparound — 32 ビットの呪い

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

防御:

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 の原則

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

この一行が:

すべての基盤。

WAL 書き込みフロー

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

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

CHECKPOINT

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

Physical Replication (Streaming):

Logical Replication (10+):

同期レプリ設定

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: 実行

コストモデル

各操作のコスト単位:

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

統計がすべて

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

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 内ループ回数

よくある問題パターン

症状原因対処
行多数で Seq Scan統計古/インデックス無ANALYZE / CREATE INDEX
推定 1 行、実際数万相関 WHERECREATE STATISTICS
Nested Loop 内が巨大Planner 誤判SET enable_nestloop = off で検証
Sort が溢れるwork_mem 不足work_mem 上げる

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

B-Tree — 既定にして王

Hash

GiST — Generalized Search Tree

GIN — Generalized Inverted Index

BRIN — Block Range Index

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

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;

インデックス選択ガイド

ワークロード推奨
一般 OLTP 照会B-Tree
JSONB フィールド検索GIN (jsonb_path_ops)
全文検索 (FTS)GIN (tsvector)
地理座標GiST (PostGIS)
巨大追記型時系列BRIN
AI 埋め込み類似度HNSW (pgvector)
範囲型 (tsrange)GiST

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');

戦略

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

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

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

自動管理 — pg_partman

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

Citus — Postgres を水平シャード

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

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

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

プロセスモデル

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

欠点:

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:

Transaction Mode 注意

PgCat & Supavisor

経験則

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

JSON vs JSONB

属性JSONJSONB
格納テキストパース済バイナリ
サイズ小やや大
入力速度速やや遅
照会速度遅速
インデックス不可 (一部)GIN 可
キー順保持非保持
重複キー許容最後のみ

ほぼ常に JSONB。

GIN インデックス戦略

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

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

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

MongoDB 代替になるか

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

9. AI 時代の Postgres — pgvector と pg_duckdb

pgvector (2023 年以降爆発)

DiskANN & Binary Quantization

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 エコシステム

10. PostgreSQL 18 (2025) 新機能

AIO — Asynchronous I/O

Direct I/O

UUIDv7 ネイティブ対応

Logical Replication 改善

Skip Scan

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;

核心指標

指標確認元警戒線
Cache hit ratiopg_stat_database99%+
Dead tuple %pg_stat_user_tablesテーブル 20%
Replication lagpg_stat_replication1MB
Long-running TXpg_stat_activity5 分+
Lock waitspg_locks + pg_stat_activity30s+
Connectionspg_stat_activity countmax_connections の 80%
WAL generationpg_stat_walベースライン比
Autovacuum lagn_dead_tup + last_vacuum24h+

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

長時間開いた TX は:

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 を賢く使うチェックリスト

結び — 「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 は「非構造テキストの王」。次稿:

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

"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)

コメント

まだコメントはありません。

ログインするとコメントできます