PostgreSQL
PostgreSQL 総合ガイド — アーキテクチャ・機能・運用の全容
PostgreSQL は、30 年以上の歴史を持つ世界で最も先進的なオープンソースリレーショナルデータベースである。ACID 準拠のトランザクション、MVCC による高い同時実行性能、豊富な拡張機能エコシステム、そして堅牢なレプリケーション機構を備え、スタートアップから Fortune 500 企業まで幅広く採用されている。本ガイドでは、PostgreSQL のアーキテクチャから運用・チューニングまでを包括的に解説し、実務で必要となる知識を体系的にまとめる。
目次
- PostgreSQL の概要と歴史
- アーキテクチャ概要
- プロセスアーキテクチャ
- メモリアーキテクチャ
- ストレージアーキテクチャ
- WAL (Write-Ahead Logging) とリカバリ
- MVCC と同時実行制御
- SQL 処理とクエリオプティマイザ
- インデックス戦略
- パーティショニング
- レプリケーション
- 高可用性と障害復旧
- バックアップとリストア
- セキュリティ
- パフォーマンスチューニング
- 拡張機能エコシステム
- 論理レプリケーションと CDC
- JSON / JSONB サポート
- PL/pgSQL と手続き型言語
- 監視と運用
- クラウド展開
- バージョンアップ戦略
- トラブルシューティング
- ベストプラクティスとまとめ
1. PostgreSQL の概要と歴史
1.1 PostgreSQL とは
PostgreSQL（ポストグレスキューエル）は、カリフォルニア大学バークレー校で 1986 年に始まった POSTGRES プロジェクトを起源とするオープンソースのオブジェクト関係データベース管理システム（ORDBMS）である。SQL 標準への高い準拠性、拡張性、信頼性を特徴とし、商用データベースに匹敵する機能を無償で提供する。
PostgreSQL は「The World's Most Advanced Open Source Relational Database」を標榜しており、その名に恥じない機能の充実ぶりを誇る。
1.2 歴史的経緯
| 年 | 出来事 |
|---|---|
| 1986 | UC Berkeley で Michael Stonebraker 教授が POSTGRES プロジェクトを開始 |
| 1989 | POSTGRES Version 1 リリース |
| 1990 | POSTGRES Version 2 リリース（ルールシステムの書き直し） |
| 1991 | POSTGRES Version 3 リリース（マルチストレージマネージャ対応） |
| 1994 | Postgres95 として SQL 言語サポートを追加（Andrew Yu と Jolly Chen） |
| 1996 | PostgreSQL 6.0 としてオープンソースコミュニティに移行 |
| 2000 | PostgreSQL 7.0 — WAL 導入 |
| 2005 | PostgreSQL 8.0 — Windows ネイティブ対応、PITR、Tablespace |
| 2008 | PostgreSQL 8.3 — HOT (Heap-Only Tuples)、全文検索統合 |
| 2010 | PostgreSQL 9.0 — ストリーミングレプリケーション、HOT Standby |
| 2011 | PostgreSQL 9.1 — 同期レプリケーション、外部データラッパー (FDW) |
| 2012 | PostgreSQL 9.2 — Index-Only Scan、JSON データ型 |
| 2013 | PostgreSQL 9.3 — マテリアライズドビュー、JSON 関数 |
| 2014 | PostgreSQL 9.4 — JSONB、論理デコーディング |
| 2016 | PostgreSQL 9.6 — パラレルクエリ |
| 2017 | PostgreSQL 10 — 宣言的パーティショニング、論理レプリケーション |
| 2018 | PostgreSQL 11 — JIT コンパイル、ストアドプロシージャ |
| 2019 | PostgreSQL 12 — CTE インライン化、プラガブルテーブルアクセスメソッド |
| 2020 | PostgreSQL 13 — インクリメンタルソート、並列 VACUUM |
| 2021 | PostgreSQL 14 — MULTIRANGE 型、接続の多重化改善 |
| 2022 | PostgreSQL 15 — MERGE 文、パブリックスキーマ権限変更 |
| 2023 | PostgreSQL 16 — 論理レプリケーション改善、pg_stat_io |
| 2024 | PostgreSQL 17 — インクリメンタルバックアップ、VACUUM 改善 |
1.3 バージョニングポリシー
PostgreSQL は年次メジャーリリースサイクルを採用している。
- メジャーバージョン: 毎年秋（9〜10 月頃）にリリース。例: 16, 17
- マイナーバージョン: 四半期ごとにバグ修正とセキュリティパッチを提供。例: 16.1, 16.2
- サポート期間: 各メジャーバージョンは 5 年間 サポートされる
現在サポート中のバージョン（2025 年時点）:
- PostgreSQL 17 (2024-09 〜 2029-11)
- PostgreSQL 16 (2023-09 〜 2028-11)
- PostgreSQL 15 (2022-10 〜 2027-11)
- PostgreSQL 14 (2021-09 〜 2026-11)
- PostgreSQL 13 (2020-09 〜 2025-11)
1.4 ライセンス
PostgreSQL は PostgreSQL License（BSD ライセンスに類似した寛容なライセンス）の下で配布されている。
- 商用利用が完全に無料
- ソースコード改変・再配布が自由
- 派生ソフトウェアにライセンス表示義務があるが、ソースコード公開義務はない
- GPL のようなコピーレフト条項がないため、プロプライエタリ製品への組み込みが容易
この寛容なライセンスにより、Amazon Aurora、Google AlloyDB、Supabase、Neon、CockroachDB など多くの商用・クラウドデータベースが PostgreSQL をベースに構築されている。
1.5 他の RDBMS との比較
| 特徴 | PostgreSQL | MySQL | Oracle DB | SQL Server |
|---|---|---|---|---|
| ライセンス | PostgreSQL License | GPL / 商用 | 商用 | 商用 |
| MVCC | 完全な MVCC | InnoDB のみ | Undo ベース | 行バージョニング |
| JSON サポート | JSONB (高機能) | JSON | JSON | JSON |
| 拡張性 | 極めて高い | 限定的 | PL/SQL | CLR |
| パーティショニング | 宣言的 | 宣言的 | 高度 | 高度 |
| 全文検索 | 組み込み | 組み込み | Oracle Text | Full-Text Search |
| GIS サポート | PostGIS | 限定的 | Spatial | Spatial |
| レプリケーション | 物理 / 論理 | グループ / 非同期 | Data Guard | Always On |
| 並列クエリ | あり | 限定的 | あり | あり |
1.6 コミュニティとエコシステム
PostgreSQL のコミュニティは世界的に活発であり、日本にも 日本 PostgreSQL ユーザ会 (JPUG) が存在する。主要なカンファレンスとして以下がある。
- PGConf.dev — 開発者向けの国際カンファレンス
- PostgresConf — 北米最大の PostgreSQL カンファレンス
- PGConf.EU — ヨーロッパ最大の PostgreSQL カンファレンス
- PGConf.Asia — アジア地域のカンファレンス
- PostgreSQL カンファレンス Japan — 日本国内のカンファレンス
2. アーキテクチャ概要
2.1 クライアント/サーバーモデル
PostgreSQL はクライアント/サーバーモデルを採用している。サーバー側では Postmaster プロセスが接続を待ち受け、クライアントからの接続要求ごとに バックエンドプロセス を fork する。
┌─────────────┐ ┌─────────────┐ ┌─────────────┐
│ アプリ (A) │ │ アプリ (B) │ │ アプリ (C) │
│ libpq │ │ JDBC │ │ psql │
└──────┬──────┘ └──────┬──────┘ └──────┬──────┘
│ TCP/IP │ TCP/IP │ Unix Socket
└──────────┬────────┴──────────┬────────┘
│ │
┌────────▼───────────────────▼────────┐
│ Postmaster (PID 1) │
│ (接続リスナー / 監視) │
└───┬──────────┬──────────┬───────────┘
│ │ │
┌────▼───┐ ┌────▼───┐ ┌────▼───┐
│Backend │ │Backend │ │Backend │
│Process │ │Process │ │Process │
│ (A) │ │ (B) │ │ (C) │
└────┬───┘ └────┬───┘ └────┬───┘
│ │ │
┌────────▼──────────▼──────────▼────────┐
│ 共有メモリ領域 │
│ (shared_buffers, WAL buffers, etc.) │
└────────────────┬──────────────────────┘
│
┌────────────────▼──────────────────────┐
│ ディスク (データファイル) │
│ (テーブル, インデックス, WAL, etc.) │
└───────────────────────────────────────┘
2.2 プロセスベースアーキテクチャの特徴
PostgreSQL はスレッドモデルではなくプロセスモデルを採用している。この設計には以下の利点と課題がある。
利点:
- プロセス間の分離が強固で、1 つのバックエンドのクラッシュが他に波及しにくい
- OS レベルのメモリ保護が効く
- デバッグが容易（各バックエンドは独立したプロセス）
課題:
- fork のオーバーヘッドがある（接続プーリングで緩和）
- プロセス間通信に共有メモリや IPC が必要
- 大量の同時接続では消費メモリが増大する
-- 現在の接続数と最大接続数の確認
SELECT count(*) AS current_connections,
(SELECT setting FROM pg_settings WHERE name = 'max_connections') AS max_connections
FROM pg_stat_activity;
-- アクティブなバックエンドプロセスの一覧
SELECT pid, usename, application_name, client_addr,
backend_start, state, query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY backend_start;
2.3 通信プロトコル
PostgreSQL は独自のフロントエンド/バックエンドプロトコル（通称 libpq プロトコル）を使用する。
| フェーズ | 内容 |
|---|---|
| スタートアップ | SSL ネゴシエーション → StartupMessage → 認証 |
| 認証 | パスワード / MD5 / SCRAM-SHA-256 / GSSAPI / SSPI / Certificate |
| クエリ実行 | Simple Query / Extended Query（Parse → Bind → Execute） |
| 終了 | Terminate メッセージ |
Extended Query プロトコル は、SQL インジェクション防止とパフォーマンス向上のために重要である。
Extended Query の流れ:
1. Parse — SQL 文を解析し、プリペアドステートメントを作成
2. Bind — パラメータを バインドし、ポータルを作成
3. Describe — ステートメント / ポータルのメタデータを取得
4. Execute — ポータルを実行
5. Sync — トランザクション境界を同期
2.4 データディレクトリ構造 (PGDATA)
$PGDATA/
├── PG_VERSION # PostgreSQL メジャーバージョン
├── postgresql.conf # メイン設定ファイル
├── postgresql.auto.conf # ALTER SYSTEM で設定された値
├── pg_hba.conf # ホストベース認証設定
├── pg_ident.conf # ident 認証マッピング
├── postmaster.pid # Postmaster の PID ファイル
├── postmaster.opts # 起動時のコマンドラインオプション
│
├── base/ # データベースごとのサブディレクトリ
│ ├── 1/ # template1 データベース
│ ├── 13067/ # template0 データベース
│ └── 16384/ # ユーザーデータベース
│ ├── 16385 # テーブルファイル (OID)
│ ├── 16385_fsm # Free Space Map
│ ├── 16385_vm # Visibility Map
│ └── 16386 # インデックスファイル
│
├── global/ # クラスタ全体のテーブル (pg_database 等)
├── pg_wal/ # WAL セグメントファイル
│ ├── 000000010000000000000001
│ └── archive_status/
├── pg_xact/ # トランザクションコミットステータス (CLOG)
├── pg_multixact/ # マルチトランザクション情報
├── pg_subtrans/ # サブトランザクション情報
├── pg_twophase/ # 2 フェーズコミット状態
├── pg_tblspc/ # テーブルスペースシンボリックリンク
├── pg_stat/ # 統計情報ファイル（永続化分）
├── pg_stat_tmp/ # 統計情報の一時ファイル
├── pg_logical/ # 論理デコーディング用データ
├── pg_snapshots/ # エクスポートされたスナップショット
├── pg_commit_ts/ # トランザクションコミットタイムスタンプ
├── pg_dynshmem/ # 動的共有メモリ
├── pg_notify/ # LISTEN/NOTIFY データ
├── pg_replslot/ # レプリケーションスロット
├── pg_serial/ # シリアライザブルトランザクション情報
└── pg_log/ # サーバーログ（設定による）
2.5 システムカタログ
PostgreSQL のメタデータはすべてシステムカタログテーブルに格納されている。
-- 主要なシステムカタログ
SELECT relname, relkind
FROM pg_class
WHERE relnamespace = 'pg_catalog'::regnamespace
AND relkind = 'r'
ORDER BY relname
LIMIT 20;
-- よく使われるシステムカタログ
-- pg_class — テーブル・インデックス・シーケンス等の一覧
-- pg_attribute — カラム情報
-- pg_index — インデックス情報
-- pg_namespace — スキーマ情報
-- pg_type — データ型情報
-- pg_proc — 関数・プロシージャ情報
-- pg_tablespace — テーブルスペース情報
-- pg_database — データベース情報
-- pg_authid — ロール (ユーザー/グループ) 情報
-- pg_settings — サーバー設定パラメータ
2.6 情報スキーマ
SQL 標準の information_schema も利用可能である。
-- テーブル一覧
SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;
-- カラム情報
SELECT column_name, data_type, character_maximum_length,
is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;
3. プロセスアーキテクチャ
3.1 プロセス一覧の確認
PostgreSQL サーバーを起動すると、複数のプロセスが動作する。
# プロセス一覧の確認
$ ps aux | grep postgres
postgres 1234 postmaster -D /var/lib/postgresql/17/main
postgres 1236 postgres: checkpointer
postgres 1237 postgres: background writer
postgres 1238 postgres: walwriter
postgres 1239 postgres: autovacuum launcher
postgres 1240 postgres: stats collector
postgres 1241 postgres: logical replication launcher
postgres 1250 postgres: user01 mydb 192.168.1.10(54321) idle
postgres 1251 postgres: user02 mydb 192.168.1.11(54322) active
3.2 Postmaster プロセス
Postmaster は PostgreSQL サーバーの親プロセスであり、以下の役割を担う。
- サーバー起動と初期化 — 共有メモリの確保、バックグラウンドプロセスの起動
- 接続リスニング — TCP/IP ソケットおよび Unix ドメインソケットで接続を待機
- 認証処理 —
pg_hba.confに基づくクライアント認証 - バックエンドプロセスの fork — 認証成功後に専用バックエンドプロセスを生成
- プロセス監視 — 子プロセスのクラッシュ検出と再起動
-- Postmaster の起動時刻を確認
SELECT pg_postmaster_start_time();
-- サーバーの PID を確認
SELECT pg_backend_pid(); -- 現在のバックエンドプロセスの PID
3.3 バックエンドプロセス (Backend Process)
各クライアント接続に対して 1 つのバックエンドプロセスが割り当てられる。
クライアント接続のライフサイクル:
1. クライアントが Postmaster に接続要求
2. Postmaster が pg_hba.conf を確認
3. 認証方式に基づいてクライアントを認証
4. Postmaster が fork() で新しいバックエンドプロセスを作成
5. バックエンドプロセスがクライアントとの通信を引き継ぐ
6. クライアントが切断するまでバックエンドプロセスが存続
7. 切断時にバックエンドプロセスが終了
-- バックエンドプロセスの状態確認
SELECT pid, usename, datname, client_addr,
backend_start, state, state_change,
wait_event_type, wait_event,
query_start, query
FROM pg_stat_activity
WHERE backend_type = 'client backend';
-- 特定のバックエンドプロセスを強制終了
SELECT pg_terminate_backend(1250); -- SIGTERM を送信
SELECT pg_cancel_backend(1250); -- 現在のクエリのみキャンセル
3.4 Background Writer (bgwriter)
Background Writer は、共有バッファ内のダーティページを定期的にディスクに書き出すプロセスである。
目的:
- チェックポイント時の I/O スパイクを軽減
- バックエンドプロセスがバッファ確保時にダーティページの書き出しを待たなくて済むようにする
# postgresql.conf — bgwriter 関連設定
bgwriter_delay = 200ms # 書き出しラウンド間のスリープ時間
bgwriter_lru_maxpages = 100 # 1ラウンドで書き出す最大ページ数
bgwriter_lru_multiplier = 2.0 # 最近使用されたバッファの割合
bgwriter_flush_after = 512kB # OS キャッシュの強制フラッシュしきい値
-- bgwriter の統計を確認
SELECT * FROM pg_stat_bgwriter;
-- 重要なカラム:
-- buffers_checkpoint — チェックポイントで書き出されたバッファ数
-- buffers_clean — bgwriter で書き出されたバッファ数
-- buffers_backend — バックエンドが直接書き出したバッファ数 (高い場合は問題)
-- maxwritten_clean — bgwriter が上限に達して停止した回数
3.5 Checkpointer
Checkpointer は、すべてのダーティページをディスクに書き出し、リカバリの開始点（チェックポイント）を作成するプロセスである。
# postgresql.conf — チェックポイント関連設定
checkpoint_timeout = 5min # 自動チェックポイントの間隔
checkpoint_completion_target = 0.9 # チェックポイントの書き出しを分散する割合
checkpoint_flush_after = 256kB # OS キャッシュのフラッシュしきい値
checkpoint_warning = 30s # チェックポイントが頻繁すぎる場合の警告
max_wal_size = 1GB # WAL サイズに基づくチェックポイントトリガー
min_wal_size = 80MB # WAL ファイルのリサイクル最小サイズ
-- 最後のチェックポイント情報を確認
SELECT checkpoint_time, checkpoint_lsn
FROM pg_control_checkpoint();
-- チェックポイント統計
SELECT checkpoints_timed, -- タイマーによるチェックポイント数
checkpoints_req, -- WAL サイズなどによるリクエストチェックポイント数
checkpoint_write_time,-- 書き出し時間 (ms)
checkpoint_sync_time -- fsync 時間 (ms)
FROM pg_stat_bgwriter;
3.6 WAL Writer
WAL Writer は、WAL バッファの内容を WAL ファイルにフラッシュするプロセスである。
# postgresql.conf — WAL Writer 関連設定
wal_writer_delay = 200ms # WAL Writer のスリープ間隔
wal_writer_flush_after = 1MB # 書き出し後にフラッシュするしきい値
WAL Writer は以下の場合に WAL をフラッシュする:
wal_writer_delayの間隔ごと- トランザクションのコミット時（
synchronous_commit = onの場合） - WAL バッファが
wal_writer_flush_afterを超えた場合
3.7 Autovacuum Launcher / Worker
Autovacuum は PostgreSQL の MVCC で発生する不要なタプル（デッドタプル）を回収するプロセスである。
# postgresql.conf — autovacuum 関連設定
autovacuum = on # autovacuum の有効化
autovacuum_max_workers = 3 # 同時実行可能な worker 数
autovacuum_naptime = 1min # launcher の起動間隔
autovacuum_vacuum_threshold = 50 # VACUUM 開始のデッドタプル数の下限
autovacuum_vacuum_scale_factor = 0.2 # テーブルの 20% がデッドタプルになったら VACUUM
autovacuum_analyze_threshold = 50 # ANALYZE 開始の変更タプル数の下限
autovacuum_analyze_scale_factor = 0.1 # テーブルの 10% が変更されたら ANALYZE
autovacuum_vacuum_cost_delay = 2ms # VACUUM のコスト遅延 (I/O 調整)
autovacuum_vacuum_cost_limit = -1 # -1 = vacuum_cost_limit を使用
autovacuum_freeze_max_age = 200000000 # 強制 VACUUM のトランザクション年齢
autovacuum_multixact_freeze_max_age = 400000000
-- テーブルごとの autovacuum 設定のオーバーライド
ALTER TABLE large_table SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_analyze_scale_factor = 0.005,
autovacuum_vacuum_cost_delay = 0
);
-- autovacuum の実行状況を確認
SELECT schemaname, relname,
last_vacuum, last_autovacuum,
last_analyze, last_autoanalyze,
vacuum_count, autovacuum_count,
n_dead_tup, n_live_tup,
CASE WHEN n_live_tup > 0
THEN round(n_dead_tup::numeric / n_live_tup * 100, 2)
ELSE 0
END AS dead_ratio_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
3.8 Statistics Collector
Statistics Collector は、テーブルアクセス統計やブロック I/O 統計などを収集するプロセスである。
-- テーブルレベルの統計
SELECT * FROM pg_stat_user_tables WHERE relname = 'orders';
-- インデックスの使用状況
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan;
-- データベースレベルの統計
SELECT datname, numbackends, xact_commit, xact_rollback,
blks_read, blks_hit,
round(blks_hit::numeric / (blks_hit + blks_read + 1) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();
3.9 WAL Sender / WAL Receiver
レプリケーション環境では追加のプロセスが動作する。
- WAL Sender — プライマリサーバーで動作し、WAL データをスタンバイに送信
- WAL Receiver — スタンバイサーバーで動作し、WAL データを受信・適用
-- WAL Sender の状態確認（プライマリ側）
SELECT pid, application_name, client_addr,
state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;
-- WAL Receiver の状態確認（スタンバイ側）
SELECT * FROM pg_stat_wal_receiver;
3.10 Logical Replication Launcher / Worker
論理レプリケーションを管理するプロセスである。
-- 論理レプリケーションワーカーの確認
SELECT pid, usename, datname, backend_type, query
FROM pg_stat_activity
WHERE backend_type IN ('logical replication launcher', 'logical replication worker');
-- サブスクリプションの状態
SELECT subname, pid, relid, received_lsn, last_msg_send_time
FROM pg_stat_subscription;
4. メモリアーキテクチャ
4.1 メモリ領域の全体像
PostgreSQL のメモリは大きく 共有メモリ と プロセスローカルメモリ に分けられる。
┌─────────────────────────────────────────────────────┐
│ 共有メモリ │
│ ┌──────────────────┐ ┌─────────────────────────┐ │
│ │ shared_buffers │ │ WAL buffers │ │
│ │ (128MB〜数十GB) │ │ (wal_buffers:64MB) │ │
│ └──────────────────┘ └─────────────────────────┘ │
│ ┌──────────────────┐ ┌─────────────────────────┐ │
│ │ CLOG buffers │ │ Lock Manager Tables │ │
│ └──────────────────┘ └─────────────────────────┘ │
│ ┌──────────────────┐ ┌─────────────────────────┐ │
│ │ Proc Array │ │ AutoVacuum Shared Mem │ │
│ └──────────────────┘ └─────────────────────────┘ │
└─────────────────────────────────────────────────────┘
┌──────────────────────┐ ┌──────────────────────┐
│ バックエンドプロセス1 │ │ バックエンドプロセス2 │
│ ┌────────────────┐ │ │ ┌────────────────┐ │
│ │ work_mem │ │ │ │ work_mem │ │
│ │ (4MB〜) │ │ │ │ (4MB〜) │ │
│ ├────────────────┤ │ │ ├────────────────┤ │
│ │ maintenance_ │ │ │ │ maintenance_ │ │
│ │ work_mem │ │ │ │ work_mem │ │
│ ├────────────────┤ │ │ ├────────────────┤ │
│ │ temp_buffers │ │ │ │ temp_buffers │ │
│ ├────────────────┤ │ │ ├────────────────┤ │
│ │ カタログキャッシュ│ │ │ │ カタログキャッシュ│ │
│ └────────────────┘ │ │ └────────────────┘ │
└──────────────────────┘ └──────────────────────┘
4.2 shared_buffers
shared_buffers は PostgreSQL で最も重要なメモリパラメータである。テーブルとインデックスのデータページをキャッシュする共有バッファプールのサイズを指定する。
# postgresql.conf
shared_buffers = 8GB # 物理メモリの 25% が一般的な推奨値
推奨設定ガイドライン:
| 物理メモリ | shared_buffers 推奨値 |
|---|---|
| 1 GB | 256 MB |
| 4 GB | 1 GB |
| 16 GB | 4 GB |
| 64 GB | 16 GB |
| 128 GB | 32 GB |
| 256 GB+ | 32〜64 GB |
注意: 物理メモリの 25% 以上を shared_buffers に割り当てても、OS のページキャッシュと二重キャッシュになるため効果が薄れることがある。Linux では
huge_pages = tryを設定して Huge Pages を活用するのが推奨される。
-- バッファキャッシュのヒット率を確認
SELECT
sum(blks_hit) AS buffer_hits,
sum(blks_read) AS disk_reads,
round(sum(blks_hit)::numeric / (sum(blks_hit) + sum(blks_read) + 1) * 100, 2)
AS cache_hit_ratio
FROM pg_stat_database;
-- pg_buffercache 拡張機能でバッファの詳細を確認
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
SELECT c.relname,
count(*) AS buffers,
pg_size_pretty(count(*) * 8192) AS buffer_size,
round(100.0 * count(*) / (SELECT setting::integer FROM pg_settings WHERE name = 'shared_buffers'), 2) AS pct_of_shared_buffers
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = c.relfilenode
WHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 20;
4.3 work_mem
work_mem は、ソート操作やハッシュ結合などの内部操作に使用されるメモリの量を指定する。
# postgresql.conf
work_mem = 64MB # デフォルトは 4MB
重要な注意点:
- この値は 操作ごと に割り当てられる（セッションごとではない）
- 1 つのクエリが複数のソート/ハッシュ操作を含む場合、各操作に
work_memが割り当てられる - 過大に設定すると、同時接続数 × 操作数 のメモリを消費し OOM のリスクがある
-- セッションレベルでの設定変更
SET work_mem = '256MB'; -- 大規模なクエリの前に一時的に増加
-- クエリ実行後にリセット
RESET work_mem;
-- ソートがメモリ内で行われたか確認 (EXPLAIN ANALYZE)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders ORDER BY created_at DESC LIMIT 100;
-- Sort Method: quicksort Memory: 25kB → メモリ内ソート
-- Sort Method: external merge Disk: 1024kB → ディスクソート（work_mem 不足）
4.4 maintenance_work_mem
maintenance_work_mem は、VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY などのメンテナンス操作に使用されるメモリ量を指定する。
# postgresql.conf
maintenance_work_mem = 2GB # デフォルトは 64MB
-- autovacuum 専用のメモリ制限
-- autovacuum_work_mem = -1 の場合、maintenance_work_mem を使用
-- autovacuum_work_mem = 512MB # autovacuum 専用に制限する場合
4.5 wal_buffers
WAL データを一時的に保持するバッファのサイズを指定する。
# postgresql.conf
wal_buffers = 64MB # デフォルトは shared_buffers の 1/32（最大 16MB）
# -1 を指定すると自動計算
4.6 effective_cache_size
effective_cache_size は、実際にメモリを消費するパラメータではなく、クエリプランナーが利用可能なキャッシュサイズを見積もるためのヒントである。
# postgresql.conf
effective_cache_size = 48GB # shared_buffers + OS ページキャッシュの合計見積もり
# 一般的に物理メモリの 50〜75%
-- effective_cache_size の影響を確認
SET effective_cache_size = '256MB';
EXPLAIN SELECT * FROM large_table WHERE id = 42;
-- Index Scan ... (コスト高 → Seq Scan を選択しやすい)
SET effective_cache_size = '48GB';
EXPLAIN SELECT * FROM large_table WHERE id = 42;
-- Index Scan ... (コスト低 → Index Scan を選択しやすい)
4.7 temp_buffers
一時テーブルにアクセスする際のバッファサイズを指定する。
# postgresql.conf
temp_buffers = 32MB # デフォルトは 8MB
4.8 Huge Pages (Linux)
大容量の shared_buffers を使用する場合、Linux の Huge Pages を活用することで TLB (Translation Lookaside Buffer) ミスを削減し、パフォーマンスを向上させる。
# postgresql.conf
huge_pages = try # on / try / off
# Linux カーネル設定
$ sysctl vm.nr_hugepages
# 必要な Huge Pages 数の計算:
# shared_buffers (bytes) / huge page size (通常 2MB)
# 例: 8GB / 2MB = 4096 + α
$ sudo sysctl -w vm.nr_hugepages=4200
$ echo "vm.nr_hugepages = 4200" | sudo tee -a /etc/sysctl.conf
4.9 メモリ設定のベストプラクティス
典型的な 64 GB メモリのサーバーでの推奨設定例:
# postgresql.conf — メモリ関連の推奨設定 (64GB RAM)
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 64MB
maintenance_work_mem = 2GB
wal_buffers = 64MB
temp_buffers = 32MB
huge_pages = try
# autovacuum 用メモリ
autovacuum_work_mem = 512MB
-- 現在のメモリ関連設定をまとめて確認
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE name IN (
'shared_buffers', 'effective_cache_size',
'work_mem', 'maintenance_work_mem',
'wal_buffers', 'temp_buffers',
'huge_pages', 'autovacuum_work_mem'
)
ORDER BY name;
5. ストレージアーキテクチャ
5.1 テーブルスペース
テーブルスペースは、データベースオブジェクトのファイルを配置するファイルシステム上のディレクトリを定義する。
-- テーブルスペースの作成
CREATE TABLESPACE fast_ssd LOCATION '/mnt/ssd/pgdata';
CREATE TABLESPACE archive_hdd LOCATION '/mnt/hdd/pgdata';
-- テーブルスペースを指定してテーブルを作成
CREATE TABLE hot_data (
id BIGSERIAL PRIMARY KEY,
data JSONB
) TABLESPACE fast_ssd;
-- インデックスを別のテーブルスペースに配置
CREATE INDEX idx_hot_data_id ON hot_data (id) TABLESPACE fast_ssd;
-- テーブルを別のテーブルスペースに移動
ALTER TABLE archive_data SET TABLESPACE archive_hdd;
-- デフォルトテーブルスペースの変更
ALTER DATABASE mydb SET TABLESPACE fast_ssd;
-- テーブルスペースの一覧と使用量
SELECT spcname, pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace;
5.2 ページレイアウト
PostgreSQL のデータは 8KB のページ（ブロック）単位で管理される。
┌─────────────────────────────────────────┐
│ Page Header (24 bytes) │
│ pd_lsn | pd_checksum | pd_flags | │
│ pd_lower | pd_upper | pd_special │
├─────────────────────────────────────────┤
│ Item ID Array (Line Pointers) │
│ ┌─────┬─────┬─────┬─────┬─────┐ │
│ │ LP1 │ LP2 │ LP3 │ LP4 │ ... │ │
│ └─────┴─────┴─────┴─────┴─────┘ │
│ pd_lower ↕ │
│ Free Space (空き領域) │
│ pd_upper ↕ │
├─────────────────────────────────────────┤
│ Tuple Data (タプル) │
│ ┌─────────────────────────────────┐ │
│ │ Tuple 4 (最新のタプルが下から) │ │
│ ├─────────────────────────────────┤ │
│ │ Tuple 3 │ │
│ ├─────────────────────────────────┤ │
│ │ Tuple 2 │ │
│ ├─────────────────────────────────┤ │
│ │ Tuple 1 │ │
│ └─────────────────────────────────┘ │
├─────────────────────────────────────────┤
│ Special Space (optional) │
└─────────────────────────────────────────┘
-- ページヘッダー情報の確認 (pageinspect 拡張機能)
CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT * FROM page_header(get_raw_page('orders', 0));
-- lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-- ページ内のアイテムポインター一覧
SELECT * FROM heap_page_items(get_raw_page('orders', 0)) LIMIT 10;
5.3 タプル構造 (HeapTupleHeader)
各タプルには可視性情報を含むヘッダーがある。
HeapTupleHeader (23 bytes minimum):
┌────────────────────────────────────────┐
│ t_xmin (4 bytes) — 挿入トランザクションID │
│ t_xmax (4 bytes) — 削除/更新トランザクションID │
│ t_cid (4 bytes) — コマンドID │
│ t_ctid (6 bytes) — 現在のタプルID (ブロック+オフセット) │
│ t_infomask2 (2 bytes) — カラム数・フラグ │
│ t_infomask (2 bytes) — 可視性フラグ │
│ t_hoff (1 byte) — ユーザーデータのオフセット │
├────────────────────────────────────────┤
│ Null Bitmap (可変長) │
├────────────────────────────────────────┤
│ OID (4 bytes, オプション) │
├────────────────────────────────────────┤
│ User Data (ユーザーデータ) │
└────────────────────────────────────────┘
-- タプルの物理情報を確認
SELECT ctid, xmin, xmax, cmin, cmax, *
FROM orders
WHERE id = 1;
-- ctid = (0,1) → ブロック 0、オフセット 1
-- xmin = 1234 → 挿入したトランザクションID
-- xmax = 0 → まだ削除/更新されていない
5.4 TOAST (The Oversized-Attribute Storage Technique)
TOAST は、1 ページ（8KB）に収まらない大きなデータを効率的に格納する仕組みである。
TOAST の動作:
1. タプルサイズが TOAST_TUPLE_THRESHOLD (通常 2KB) を超えた場合
2. まず圧縮を試みる (pglz または lz4)
3. 圧縮後もしきい値を超える場合、TOAST テーブルに分割して格納
4. 元のテーブルにはポインター（TOAST ポインター）を格納
TOAST 戦略:
| 戦略 | 説明 |
|---|---|
| PLAIN | TOAST 対象外（固定長型） |
| EXTENDED | 圧縮と外部格納の両方を許可（デフォルト） |
| EXTERNAL | 外部格納のみ（圧縮なし、substring が高速） |
| MAIN | まず圧縮を試み、それでもダメなら外部格納 |
-- カラムの TOAST 戦略を確認
SELECT attname, attstorage
FROM pg_attribute
WHERE attrelid = 'orders'::regclass AND attnum > 0;
-- p = PLAIN, x = EXTENDED, e = EXTERNAL, m = MAIN
-- TOAST 戦略の変更
ALTER TABLE orders ALTER COLUMN description SET STORAGE EXTERNAL;
-- TOAST テーブルのサイズを確認
SELECT c.relname AS table_name,
pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS toast_size,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
WHERE c.relkind = 'r'
AND c.relnamespace = 'public'::regnamespace
ORDER BY pg_total_relation_size(c.oid) DESC;
5.5 Visibility Map (VM)
Visibility Map は各ページのすべてのタプルが「すべてのアクティブトランザクションから可視」かどうかを追跡するデータ構造である。
用途:
- Index-Only Scan の最適化 — VM でページが all-visible と分かれば、ヒープアクセスをスキップできる
- VACUUM の最適化 — all-visible ページは VACUUM でスキップできる
- FREEZE の最適化 — all-frozen ページはフリーズ処理をスキップできる
-- Visibility Map の状態を確認
CREATE EXTENSION IF NOT EXISTS pg_visibility;
SELECT * FROM pg_visibility_map('orders', 0);
-- blkno | all_visible | all_frozen
-- テーブル全体の VM サマリー
SELECT * FROM pg_visibility_map_summary('orders');
-- all_visible_count | all_frozen_count
5.6 Free Space Map (FSM)
Free Space Map は各ページの空き領域を追跡し、INSERT 時に適切なページを素早く見つけるために使用される。
-- FSM の情報を確認
CREATE EXTENSION IF NOT EXISTS pg_freespacemap;
SELECT blkno, avail AS free_bytes
FROM pg_freespace('orders')
WHERE avail > 0
ORDER BY blkno
LIMIT 20;
-- テーブルの全体的な空き領域を確認
SELECT
count(*) AS total_pages,
sum(avail) AS total_free_bytes,
pg_size_pretty(sum(avail)::bigint) AS total_free,
round(avg(avail), 2) AS avg_free_per_page
FROM pg_freespace('orders');
5.7 ファイルレイアウトとセグメント
-- テーブルの物理ファイルパスを確認
SELECT pg_relation_filepath('orders');
-- → base/16384/16385
-- テーブルのファイルノード
SELECT relfilenode, reltablespace, relname
FROM pg_class
WHERE relname = 'orders';
テーブルファイルは最大 1GB のセグメントに分割される:
16385— 最初のセグメント（〜1GB）16385.1— 2 番目のセグメント16385.2— 3 番目のセグメント16385_fsm— Free Space Map16385_vm— Visibility Map
5.8 データ型とアライメント
PostgreSQL はデータのアライメントを考慮してストレージを最適化する。カラムの順序がディスク使用量に影響する。
-- カラムの物理サイズとアライメントを確認
SELECT a.attname, t.typname, t.typalign, t.typlen
FROM pg_attribute a
JOIN pg_type t ON a.atttypid = t.oid
WHERE a.attrelid = 'orders'::regclass
AND a.attnum > 0
ORDER BY a.attnum;
-- typalign: c=char, s=short(2byte), i=int(4byte), d=double(8byte)
-- カラムの順序を最適化した例
-- 悪い例（パディングが多い）:
CREATE TABLE bad_layout (
flag BOOLEAN, -- 1 byte + 7 padding
amount BIGINT, -- 8 bytes
status SMALLINT, -- 2 bytes + 6 padding
price DOUBLE PRECISION -- 8 bytes
); -- 合計: 32 bytes per row
-- 良い例（パディングが少ない）:
CREATE TABLE good_layout (
amount BIGINT, -- 8 bytes
price DOUBLE PRECISION, -- 8 bytes
status SMALLINT, -- 2 bytes
flag BOOLEAN -- 1 byte + 5 padding
); -- 合計: 24 bytes per row
6. WAL (Write-Ahead Logging) とリカバリ
6.1 WAL の基本概念
WAL (Write-Ahead Logging) は、データの変更をデータファイルに書き込む前に、まずログ（WAL）に記録するという原則に基づいた仕組みである。これにより以下が保証される。
- 耐久性 (Durability) — コミット済みトランザクションはクラッシュ後もリカバリ可能
- アトミック性 (Atomicity) — 部分的な書き込みによるデータ破損を防止
- パフォーマンス — ランダム I/O をシーケンシャル I/O に変換
WAL の動作フロー:
1. トランザクションがデータを変更
2. 変更内容が WAL バッファに書き込まれる
3. COMMIT 時に WAL バッファが WAL ファイルにフラッシュされる (fsync)
4. データファイルへの書き込みは後で行われる（チェックポイント時 / bgwriter）
5. クラッシュ時は、最後のチェックポイント以降の WAL を再生してリカバリ
6.2 WAL 関連の設定パラメータ
# postgresql.conf — WAL 関連設定
# WAL レベル
wal_level = replica # minimal | replica | logical
# replica: ストリーミングレプリケーションに必要
# logical: 論理レプリケーション / 論理デコーディングに必要
# WAL セグメントサイズ
# initdb --wal-segsize=64 で変更可能（デフォルト 16MB）
# 同期コミット
synchronous_commit = on # on | remote_apply | remote_write | local | off
# off: WAL フラッシュを待たない（パフォーマンス向上、最大数秒のデータ損失リスク）
# WAL ファイルサイズ制限
max_wal_size = 4GB # チェックポイント間の最大 WAL サイズ
min_wal_size = 1GB # WAL ファイルのリサイクル最小サイズ
# WAL 圧縮
wal_compression = lz4 # off | pglz | lz4 | zstd (PG15+)
# チェックサム
# initdb --data-checksums で有効化（後から pg_checksums で変更可能）
# フルページ書き込み
full_page_writes = on # チェックポイント後の最初の変更でページ全体を WAL に書く
# 部分的な書き込み（torn page）を防止
6.3 WAL セグメントとLSN
-- 現在の WAL 位置 (LSN: Log Sequence Number) を確認
SELECT pg_current_wal_lsn();
-- → 0/1A2B3C4D
-- LSN 間の差分をバイト数で計算
SELECT pg_wal_lsn_diff('0/1A2B3C4D', '0/1A000000');
-- 現在の WAL セグメントファイル名
SELECT pg_walfile_name(pg_current_wal_lsn());
-- → 000000010000000000000001
-- WAL ファイルの一覧（サーバー上）
-- $ ls -la $PGDATA/pg_wal/
-- WAL の書き込み統計
SELECT * FROM pg_stat_wal;
-- wal_records, wal_fpi (full page images), wal_bytes, wal_write, wal_sync
6.4 WAL アーカイブ
WAL アーカイブは、PITR（ポイントインタイムリカバリ）やスタンバイサーバーのセットアップに不可欠である。
# postgresql.conf — WAL アーカイブ設定
archive_mode = on
archive_command = 'cp %p /archive/wal/%f'
# または pgBackRest / Barman と統合:
# archive_command = 'pgbackrest --stanza=mydb archive-push %p'
# アーカイブタイムアウト (WAL セグメントが満たされない場合の強制アーカイブ)
archive_timeout = 300 # 5分ごとに WAL を強制切り替え
-- アーカイブの状態確認
SELECT * FROM pg_stat_archiver;
-- archived_count — アーカイブ成功数
-- last_archived_wal — 最後にアーカイブされた WAL ファイル
-- failed_count — アーカイブ失敗数
-- last_failed_wal — 最後に失敗した WAL ファイル
-- 手動で WAL を切り替え
SELECT pg_switch_wal();
6.5 PITR (Point-In-Time Recovery)
PITR は、特定の時刻までデータベースを復元する機能である。
# 1. ベースバックアップの取得
pg_basebackup -h localhost -D /backup/base -Fp -Xs -P -v
# 2. 復元手順
# バックアップをデータディレクトリにコピー
cp -r /backup/base /var/lib/postgresql/17/main_restore
# 3. recovery.signal ファイルの作成
touch /var/lib/postgresql/17/main_restore/recovery.signal
# 4. postgresql.conf にリカバリ設定を追加
cat >> /var/lib/postgresql/17/main_restore/postgresql.conf << 'EOF'
restore_command = 'cp /archive/wal/%f %p'
recovery_target_time = '2025-06-15 14:30:00+09'
recovery_target_action = 'promote'
EOF
# 5. サーバーを起動（リカバリが自動的に開始される）
pg_ctl -D /var/lib/postgresql/17/main_restore start
-- リカバリの状態確認
SELECT pg_is_in_recovery(); -- true = リカバリ中
SELECT pg_last_xact_replay_timestamp(); -- 最後に再生されたトランザクションの時刻
-- リカバリターゲットのオプション
-- recovery_target_time = '2025-06-15 14:30:00' -- 特定の時刻まで
-- recovery_target_xid = '12345' -- 特定のトランザクションIDまで
-- recovery_target_lsn = '0/1A2B3C4D' -- 特定のLSNまで
-- recovery_target_name = 'my_restore_point' -- 名前付きリストアポイント
-- recovery_target_inclusive = true -- ターゲットを含むかどうか
6.6 pg_basebackup
pg_basebackup は物理バックアップを取得するための公式ツールである。
# 基本的なバックアップ
pg_basebackup \
-h primary-server \
-p 5432 \
-U replication_user \
-D /backup/base_20250615 \
-Fp \ # Plain format (ディレクトリ)
-Xs \ # WAL を streaming で取得
-P \ # 進捗表示
-v # 詳細出力
# tar 形式でバックアップ（圧縮付き）
pg_basebackup \
-h primary-server \
-D /backup/base_20250615 \
-Ft \ # Tar format
-z \ # gzip 圧縮
-Xs \
-P
# インクリメンタルバックアップ (PostgreSQL 17+)
pg_basebackup \
-h primary-server \
-D /backup/incr_20250616 \
--incremental=/backup/base_20250615/backup_manifest \
-Fp -Xs -P
6.7 リストアポイント
-- 名前付きリストアポイントの作成
SELECT pg_create_restore_point('before_migration');
-- これにより PITR で特定のポイントまで復元可能:
-- recovery_target_name = 'before_migration'
6.8 WAL レベルの比較
| wal_level | WAL サイズ | 用途 |
|---|---|---|
| minimal | 最小 | クラッシュリカバリのみ |
| replica | 中 | ストリーミングレプリケーション、PITR |
| logical | 最大 | 論理レプリケーション、論理デコーディング |
-- 現在の wal_level を確認
SHOW wal_level;
7. MVCC と同時実行制御
7.1 MVCC の基本概念
MVCC (Multi-Version Concurrency Control) は、PostgreSQL の同時実行制御の中核である。読み取りが書き込みをブロックせず、書き込みが読み取りをブロックしないことを実現する。
MVCC の動作原理:
1. 各タプルに xmin（挿入トランザクションID）と xmax（削除トランザクションID）を記録
2. UPDATE = 旧タプルに xmax を設定 + 新タプルを挿入（xmin = 新TX）
3. DELETE = タプルに xmax を設定するのみ（物理削除はしない）
4. 各トランザクションは自分のスナップショットに基づいてタプルの可視性を判断
-- MVCC の動作を確認する実験
CREATE TABLE mvcc_test (id INT, value TEXT);
INSERT INTO mvcc_test VALUES (1, 'original');
-- ターミナル A
BEGIN;
SELECT txid_current(); -- → 100
UPDATE mvcc_test SET value = 'updated' WHERE id = 1;
-- ターミナル B (トランザクション A がコミットする前)
BEGIN;
SELECT txid_current(); -- → 101
SELECT ctid, xmin, xmax, * FROM mvcc_test WHERE id = 1;
-- ctid = (0,1), xmin = 99, xmax = 100, value = 'original'
-- (トランザクション 100 はまだコミットしていないので旧タプルが見える)
-- ターミナル A
COMMIT;
-- ターミナル B (READ COMMITTED の場合)
SELECT ctid, xmin, xmax, * FROM mvcc_test WHERE id = 1;
-- ctid = (0,2), xmin = 100, xmax = 0, value = 'updated'
-- (トランザクション 100 がコミットしたので新タプルが見える)
COMMIT;
7.2 トランザクション分離レベル
PostgreSQL は SQL 標準の 4 つの分離レベルのうち 3 つを実装している。
| 分離レベル | Dirty Read | Non-Repeatable Read | Phantom Read | Serialization Anomaly |
|---|---|---|---|---|
| READ UNCOMMITTED | 不可 ※ | 可能 | 可能 | 可能 |
| READ COMMITTED (デフォルト) | 不可 | 可能 | 可能 | 可能 |
| REPEATABLE READ | 不可 | 不可 | 不可 ※ | 可能 |
| SERIALIZABLE | 不可 | 不可 | 不可 | 不可 |
※ PostgreSQL では READ UNCOMMITTED は READ COMMITTED と同じ動作。REPEATABLE READ ではファントムリードも防止される（SQL 標準より厳密）。
-- トランザクション分離レベルの設定
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- または
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- 現在の分離レベルを確認
SHOW transaction_isolation;
-- デフォルトの分離レベルを変更
SET default_transaction_isolation = 'repeatable read';
7.3 スナップショット
各トランザクションはスナップショットを保持し、どのトランザクションが可視かを決定する。
-- スナップショットのエクスポート
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();
-- → '00000003-0000001A-1'
-- 別セッションで同じスナップショットを使用
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-0000001A-1';
-- これで両セッションが同じ時点のデータを参照する
-- 現在のスナップショット情報
SELECT txid_current_snapshot();
-- → 100:105:100,102
-- xmin:xmax:xip_list
-- 100 未満はすべてコミット済み
-- 100,102 は進行中
-- 105 以上はまだ開始していない
7.4 VACUUM の仕組み
MVCC では更新・削除されたタプルは即座に物理削除されないため、VACUUM による不要タプルの回収が必要である。
VACUUM の種類:
1. VACUUM (通常) — デッドタプルを回収し、空き領域を再利用可能にする
2. VACUUM FULL — テーブルを完全に書き直して物理サイズを縮小 (排他ロック)
3. VACUUM FREEZE — 古いトランザクションIDをフリーズ
4. autovacuum — バックグラウンドで自動実行
-- 通常の VACUUM
VACUUM orders;
-- VACUUM + ANALYZE (統計情報も更新)
VACUUM ANALYZE orders;
-- VACUUM VERBOSE (詳細表示)
VACUUM VERBOSE orders;
-- VACUUM FULL (テーブルを書き直し — 排他ロックが必要)
VACUUM FULL orders;
-- 特定のカラムのみ ANALYZE
ANALYZE orders (customer_id, order_date);
-- VACUUM の進捗を確認 (PostgreSQL 12+)
SELECT * FROM pg_stat_progress_vacuum;
-- phase: scanning heap → vacuuming indexes → vacuuming heap → cleaning up
7.5 HOT (Heap-Only Tuples)
HOT は、インデックスが参照するカラムが変更されない UPDATE の場合にインデックスの更新をスキップする最適化である。
-- HOT 更新の条件:
-- 1. 更新されるカラムにインデックスがない
-- 2. 同じページに空き領域がある
-- 3. fill factor が 100% 未満
-- fill factor を設定して HOT を促進
CREATE TABLE hot_test (
id SERIAL PRIMARY KEY,
status TEXT,
description TEXT
) WITH (fillfactor = 80); -- 各ページの 80% のみ使用
-- インデックスは id のみ → status や description の更新は HOT 可能
CREATE INDEX idx_hot_test_id ON hot_test (id);
-- HOT 更新の統計を確認
SELECT relname, n_tup_upd, n_tup_hot_upd,
CASE WHEN n_tup_upd > 0
THEN round(n_tup_hot_upd::numeric / n_tup_upd * 100, 2)
ELSE 0
END AS hot_update_ratio
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC;
7.6 トランザクション ID ラップアラウンド防止
PostgreSQL のトランザクション ID は 32 ビット（約 42 億）であり、ラップアラウンドを防止するためにフリーズ処理が必要である。
# postgresql.conf — フリーズ関連設定
vacuum_freeze_min_age = 50000000 # フリーズ対象の最小トランザクション年齢
vacuum_freeze_table_age = 150000000 # テーブル全体をフリーズする年齢
autovacuum_freeze_max_age = 200000000 # 強制 autovacuum のトリガー年齢
-- テーブルごとのトランザクション年齢を確認
SELECT c.relname,
age(c.relfrozenxid) AS xid_age,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;
-- データベースレベルのトランザクション年齢
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
-- 危険: age が autovacuum_freeze_max_age に近づいた場合
-- autovacuum が強制的に anti-wraparound VACUUM を実行する
-- これはパフォーマンスに影響するため、事前に VACUUM FREEZE を実行すべき
7.7 ロックの種類
-- PostgreSQL のロックモード
-- ACCESS SHARE — SELECT
-- ROW SHARE — SELECT FOR UPDATE/SHARE
-- ROW EXCLUSIVE — UPDATE, DELETE, INSERT
-- SHARE UPDATE EXCLUSIVE — VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY
-- SHARE — CREATE INDEX (非 CONCURRENTLY)
-- SHARE ROW EXCLUSIVE — CREATE TRIGGER
-- EXCLUSIVE — REFRESH MATERIALIZED VIEW CONCURRENTLY
-- ACCESS EXCLUSIVE — ALTER TABLE, DROP TABLE, VACUUM FULL
-- 現在のロック状況を確認
SELECT l.locktype, l.relation::regclass, l.mode, l.granted,
a.pid, a.usename, a.query_start, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation IS NOT NULL
ORDER BY l.relation;
-- ロック待ちの検出
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON blocked.pid = bl.pid AND NOT bl.granted
JOIN pg_locks kl ON bl.locktype = kl.locktype
AND bl.database IS NOT DISTINCT FROM kl.database
AND bl.relation IS NOT DISTINCT FROM kl.relation
AND bl.page IS NOT DISTINCT FROM kl.page
AND bl.tuple IS NOT DISTINCT FROM kl.tuple
AND bl.virtualxid IS NOT DISTINCT FROM kl.virtualxid
AND bl.transactionid IS NOT DISTINCT FROM kl.transactionid
AND bl.classid IS NOT DISTINCT FROM kl.classid
AND bl.objid IS NOT DISTINCT FROM kl.objid
AND bl.objsubid IS NOT DISTINCT FROM kl.objsubid
AND bl.pid != kl.pid
AND kl.granted
JOIN pg_stat_activity blocking ON kl.pid = blocking.pid;
7.8 アドバイザリロック
アプリケーションレベルのロックをデータベースで管理する仕組みである。
-- セッションレベルのアドバイザリロック
SELECT pg_advisory_lock(42); -- ロック取得（ブロッキング）
SELECT pg_try_advisory_lock(42); -- ロック試行（ノンブロッキング）
SELECT pg_advisory_unlock(42); -- ロック解放
-- トランザクションレベルのアドバイザリロック
SELECT pg_advisory_xact_lock(42); -- トランザクション終了時に自動解放
-- 2つの整数キーによるアドバイザリロック
SELECT pg_advisory_lock(classid, objid);
-- 例: テーブル ID + 行 ID でロック
8. SQL 処理とクエリオプティマイザ
8.1 クエリ処理の全体フロー
PostgreSQL でクエリが実行されるとき、以下の段階を経る。
SQL 文 → Parser → Rewriter → Planner/Optimizer → Executor → 結果
詳細フロー:
┌────────────────┐
│ SQL テキスト │
└───────┬────────┘
↓
┌───────▼────────┐
│ Parser │ — 構文解析（パースツリー生成）
│ (gram.y) │ — 型チェック、名前解決
└───────┬────────┘
↓
┌───────▼────────┐
│ Rewriter │ — ビューの展開
│ │ — ルールシステムの適用
└───────┬────────┘
↓
┌───────▼────────┐
│ Planner / │ — コスト見積もり
│ Optimizer │ — 最適な実行計画の選択
│ │ — JOIN 順序、アクセスパスの決定
└───────┬────────┘
↓
┌───────▼────────┐
│ Executor │ — 実行計画に基づいてデータを取得
│ │ — タプルを1行ずつ上位ノードに渡す
└───────┬────────┘
↓
┌───────▼────────┐
│ 結果セット │
└────────────────┘
8.2 Parser (構文解析)
Parser は SQL テキストを解析し、パースツリー（構文木）を生成する。
-- パースエラーの例
SELECT * FORM orders;
-- ERROR: syntax error at or near "FORM"
-- 型チェックエラーの例
SELECT * FROM orders WHERE id = 'abc';
-- ERROR: invalid input syntax for type integer: "abc"
-- (id が integer 型の場合)
8.3 Rewriter (書き換え)
Rewriter はパースツリーを変換する。主にビューの展開とルールの適用を行う。
-- ビューの展開例
CREATE VIEW active_orders AS
SELECT * FROM orders WHERE status = 'active';
-- 以下のクエリ:
SELECT * FROM active_orders WHERE amount > 100;
-- Rewriter により以下に変換される:
SELECT * FROM orders WHERE status = 'active' AND amount > 100;
8.4 Planner / Optimizer (計画 / 最適化)
Planner は最もコストの低い実行計画を選択する。PostgreSQL はコストベースオプティマイザを使用する。
コスト計算の主要パラメータ:
# postgresql.conf — プランナーコストパラメータ
seq_page_cost = 1.0 # シーケンシャルページ読み取りコスト（基準値）
random_page_cost = 4.0 # ランダムページ読み取りコスト (SSD: 1.1〜1.5)
cpu_tuple_cost = 0.01 # タプル処理のCPUコスト
cpu_index_tuple_cost = 0.005 # インデックスタプル処理のCPUコスト
cpu_operator_cost = 0.0025 # 演算子/関数のCPUコスト
parallel_tuple_cost = 0.1 # 並列ワーカーへのタプル転送コスト
parallel_setup_cost = 1000.0 # 並列ワーカー起動コスト
effective_cache_size = 4GB # キャッシュサイズの見積もり
8.5 スキャン方式
| スキャン方式 | 説明 | 使用条件 |
|---|---|---|
| Seq Scan | テーブル全体を順次読み取り | 条件なし、または選択率が高い |
| Index Scan | インデックスを使って特定の行を取得 | 選択率が低い |
| Index Only Scan | インデックスのみでデータを返す | カバリングインデックス + VM が all-visible |
| Bitmap Index Scan | ビットマップで該当ページを特定 | 中程度の選択率、複数インデックスの組み合わせ |
| TID Scan | タプルID (ctid) で直接アクセス | WHERE ctid = '(0,1)' |
-- 各スキャン方式の例
-- Seq Scan
EXPLAIN SELECT * FROM orders;
-- Index Scan
EXPLAIN SELECT * FROM orders WHERE id = 42;
-- Index Only Scan
EXPLAIN SELECT id FROM orders WHERE id BETWEEN 1 AND 100;
-- Bitmap Index Scan
EXPLAIN SELECT * FROM orders
WHERE customer_id = 100 AND status = 'active';
8.6 結合方式
| 結合方式 | 説明 | 適切な場面 |
|---|---|---|
| Nested Loop | 外側テーブルの各行に対して内側テーブルを検索 | 小さいテーブル、インデックスあり |
| Hash Join | 小さい方のテーブルからハッシュテーブルを作成 | 等値結合、大きなテーブル |
| Merge Join | ソートされた2つのテーブルをマージ | ソート済みデータ、等値結合 |
-- 結合方式の制御
SET enable_nestloop = off; -- Nested Loop を無効化（テスト用）
SET enable_hashjoin = off; -- Hash Join を無効化
SET enable_mergejoin = off; -- Merge Join を無効化
-- 結合方式のヒント（pg_hint_plan 拡張機能）
/*+ NestLoop(orders customers) */
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
8.7 EXPLAIN と EXPLAIN ANALYZE
-- 実行計画の表示（実行なし）
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- 実際に実行して統計を表示
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT o.id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2025-01-01'
AND o.status = 'completed'
ORDER BY o.amount DESC
LIMIT 10;
-- 出力例:
-- Limit (cost=1234.56..1234.58 rows=10 width=52)
-- (actual time=12.345..12.367 rows=10 loops=1)
-- -> Sort (cost=1234.56..1267.89 rows=13332 width=52)
-- (actual time=12.344..12.355 rows=10 loops=1)
-- Sort Key: o.amount DESC
-- Sort Method: top-N heapsort Memory: 25kB
-- -> Hash Join (cost=100.00..1100.00 rows=13332 width=52)
-- (actual time=1.234..10.567 rows=13332 loops=1)
-- Hash Cond: (o.customer_id = c.id)
-- -> Bitmap Heap Scan on orders o
-- (cost=50.00..900.00 rows=13332 width=24)
-- (actual time=0.567..5.678 rows=13332 loops=1)
-- Recheck Cond: (order_date >= '2025-01-01')
-- Filter: (status = 'completed')
-- Rows Removed by Filter: 2668
-- Heap Blocks: exact=234
-- Buffers: shared hit=256
-- -> Bitmap Index Scan on idx_orders_date
-- (cost=0.00..46.67 rows=16000 width=0)
-- (actual time=0.456..0.456 rows=16000 loops=1)
-- Index Cond: (order_date >= '2025-01-01')
-- Buffers: shared hit=22
-- -> Hash (cost=30.00..30.00 rows=1000 width=36)
-- (actual time=0.567..0.567 rows=1000 loops=1)
-- Buckets: 1024 Batches: 1 Memory Usage: 72kB
-- Buffers: shared hit=15
-- -> Seq Scan on customers c
-- (cost=0.00..30.00 rows=1000 width=36)
-- (actual time=0.012..0.345 rows=1000 loops=1)
-- Buffers: shared hit=15
-- Planning Time: 0.234 ms
-- Execution Time: 12.456 ms
-- JSON 形式で出力（可視化ツール用）
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 42;
8.8 統計情報と推定精度
-- テーブルの統計情報を確認
SELECT attname, n_distinct, most_common_vals, most_common_freqs,
histogram_bounds, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
-- 統計情報のサンプル数を増やす（推定精度向上）
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
-- デフォルトは 100、最大は 10000
ANALYZE orders;
-- プランナーの行数推定が実際と大きく乖離している場合
-- estimated rows vs actual rows の差が大きい → 統計情報の更新が必要
8.9 パラレルクエリ
-- パラレルクエリの設定
SET max_parallel_workers_per_gather = 4; -- Gather あたりの並列ワーカー数
SET min_parallel_table_scan_size = '8MB'; -- 並列 Seq Scan の最小テーブルサイズ
SET min_parallel_index_scan_size = '512kB'; -- 並列 Index Scan の最小インデックスサイズ
SET parallel_leader_participation = on; -- リーダーもワーカーとして参加
-- パラレルクエリの実行計画例
EXPLAIN (ANALYZE)
SELECT status, count(*), avg(amount)
FROM orders
GROUP BY status;
-- Finalize GroupAggregate
-- -> Gather Merge
-- Workers Planned: 2
-- Workers Launched: 2
-- -> Partial GroupAggregate
-- -> Sort
-- -> Parallel Seq Scan on orders
8.10 Prepared Statements
-- プリペアドステートメント（サーバー側）
PREPARE get_order(integer) AS
SELECT * FROM orders WHERE id = $1;
EXECUTE get_order(42);
-- 実行計画の確認
EXPLAIN EXECUTE get_order(42);
-- ジェネリックプラン vs カスタムプラン
-- PostgreSQL は最初の 5 回はカスタムプランを使い、
-- その後コストが低ければジェネリックプランに切り替える
-- plan_cache_mode = auto | force_generic_plan | force_custom_plan
9. インデックス戦略
9.1 インデックスの種類と概要
PostgreSQL は多様なインデックスタイプをサポートしている。
| インデックス型 | 主な用途 | データ構造 |
|---|---|---|
| B-tree | 等値・範囲検索（デフォルト） | バランス木 |
| Hash | 等値検索のみ | ハッシュテーブル |
| GiST | 空間データ、全文検索、範囲型 | 汎用検索木 |
| SP-GiST | 不均一なデータ分布（電話番号、IP） | 空間パーティション木 |
| GIN | 全文検索、JSONB、配列 | 転置インデックス |
| BRIN | 物理的にソートされた大規模テーブル | ブロック範囲インデックス |
9.2 B-tree インデックス
最も一般的なインデックスで、等値検索（=）と範囲検索（<, >, <=, >=, BETWEEN）に対応する。
-- 基本的な B-tree インデックス
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
-- 複合インデックス
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);
-- ユニークインデックス
CREATE UNIQUE INDEX idx_users_email ON users (email);
-- 条件付きインデックス（部分インデックス）
CREATE INDEX idx_orders_active ON orders (order_date)
WHERE status = 'active';
-- 式インデックス
CREATE INDEX idx_users_lower_email ON users (lower(email));
-- カバリングインデックス (INCLUDE)
CREATE INDEX idx_orders_covering ON orders (customer_id)
INCLUDE (order_date, amount);
-- Index Only Scan で order_date と amount も取得可能
-- NULL FIRST / LAST の指定
CREATE INDEX idx_orders_date_nulls ON orders (order_date DESC NULLS LAST);
-- インデックスの使用状況を確認
SELECT schemaname, tablename, indexname,
idx_scan, idx_tup_read, idx_tup_fetch,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan;
-- 未使用のインデックスを検出
SELECT schemaname, tablename, indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
9.3 Hash インデックス
等値検索（=）のみに対応するインデックスで、B-tree より若干高速な場合がある。
-- Hash インデックスの作成
CREATE INDEX idx_sessions_token ON sessions USING hash (session_token);
-- PostgreSQL 10 以降で WAL 対応（それ以前はクラッシュセーフでなかった）
9.4 GiST (Generalized Search Tree)
GiST は汎用的なインデックスフレームワークで、空間データ、範囲型、全文検索に使用される。
-- 空間データ（PostGIS）
CREATE INDEX idx_locations_geom ON locations USING gist (geom);
-- 範囲型
CREATE TABLE reservations (
id SERIAL PRIMARY KEY,
room_id INT,
during TSRANGE
);
CREATE INDEX idx_reservations_during ON reservations USING gist (during);
-- 重なりの検索
SELECT * FROM reservations
WHERE during && tsrange('2025-06-15 09:00', '2025-06-15 12:00');
-- 排他制約（GiST を使用）
ALTER TABLE reservations ADD CONSTRAINT no_overlap
EXCLUDE USING gist (room_id WITH =, during WITH &&);
-- 全文検索
CREATE INDEX idx_articles_fts ON articles USING gist (to_tsvector('japanese', content));
9.5 SP-GiST (Space-Partitioned GiST)
-- IP アドレスの検索
CREATE INDEX idx_access_log_ip ON access_log USING spgist (client_ip inet_ops);
-- テキストのプレフィックス検索
CREATE INDEX idx_products_name ON products USING spgist (name text_ops);
SELECT * FROM products WHERE name ^@ 'PostgreSQL'; -- プレフィックス検索
9.6 GIN (Generalized Inverted Index)
GIN は転置インデックスで、複数の値を含むデータ型（配列、JSONB、全文検索）に最適である。
-- JSONB の GIN インデックス
CREATE INDEX idx_events_data ON events USING gin (data);
-- JSONB の特定のパスに対する GIN インデックス
CREATE INDEX idx_events_data_path ON events USING gin (data jsonb_path_ops);
-- 配列の GIN インデックス
CREATE INDEX idx_products_tags ON products USING gin (tags);
SELECT * FROM products WHERE tags @> ARRAY['electronics', 'sale'];
-- 全文検索の GIN インデックス
CREATE INDEX idx_articles_search ON articles
USING gin (to_tsvector('english', title || ' ' || body));
-- pg_trgm を使ったトライグラム検索
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);
SELECT * FROM products WHERE name ILIKE '%search%'; -- LIKE 検索にインデックスが使える
-- GIN の保留リスト設定
ALTER INDEX idx_events_data SET (gin_pending_list_limit = 64);
-- fastupdate = on の場合、保留リストに蓄積してからインデックスに反映
9.7 BRIN (Block Range Index)
BRIN は物理的に順序付けされた大規模テーブルに対して非常にコンパクトなインデックスを提供する。
-- 時系列データに最適
CREATE TABLE sensor_data (
id BIGSERIAL,
recorded_at TIMESTAMP NOT NULL,
sensor_id INT,
value DOUBLE PRECISION
);
-- BRIN インデックス（非常に小さい）
CREATE INDEX idx_sensor_data_time ON sensor_data USING brin (recorded_at);
-- pages_per_range の調整
CREATE INDEX idx_sensor_data_time_128 ON sensor_data
USING brin (recorded_at) WITH (pages_per_range = 128);
-- B-tree と BRIN のサイズ比較
SELECT indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE tablename = 'sensor_data';
-- idx_sensor_data_time_btree: 214 MB
-- idx_sensor_data_time_brin: 48 kB ← 劇的にコンパクト
9.8 部分インデックス
テーブルの一部のみをインデックスに含めることで、インデックスサイズを削減し、メンテナンスコストを下げる。
-- アクティブな注文のみインデックス
CREATE INDEX idx_orders_active ON orders (order_date)
WHERE status IN ('pending', 'processing');
-- NULL ではない行のみ
CREATE INDEX idx_users_phone ON users (phone)
WHERE phone IS NOT NULL;
-- 特定の値を除外
CREATE INDEX idx_logs_important ON logs (created_at, level)
WHERE level != 'DEBUG';
9.9 式インデックス
-- 小文字変換した値でインデックス
CREATE INDEX idx_users_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'user@example.com';
-- 日付の一部でインデックス
CREATE INDEX idx_orders_year_month ON orders (date_trunc('month', order_date));
-- JSONB の特定フィールドでインデックス
CREATE INDEX idx_events_type ON events ((data->>'type'));
SELECT * FROM events WHERE data->>'type' = 'purchase';
9.10 インデックスのメンテナンス
-- インデックスの再構築（テーブルロックなし）
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;
-- テーブルの全インデックスを再構築
REINDEX TABLE CONCURRENTLY orders;
-- インデックスの肥大化を確認
SELECT schemaname, tablename, indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
pg_size_pretty(pg_relation_size(indrelid)) AS table_size,
round(100.0 * pg_relation_size(indexrelid) / pg_relation_size(indrelid), 2) AS index_table_ratio
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
-- pgstattuple でインデックスの断片化を確認
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('idx_orders_customer_id');
-- leaf_fragmentation が高い場合は REINDEX を検討
9.11 CREATE INDEX CONCURRENTLY
-- 通常の CREATE INDEX はテーブルに書き込みロックをかける
-- CONCURRENTLY オプションでロックなしにインデックスを作成
CREATE INDEX CONCURRENTLY idx_orders_amount ON orders (amount);
-- 注意点:
-- 1. 通常の 2〜3 倍の時間がかかる
-- 2. テーブルを 2 回スキャンする
-- 3. トランザクション内で実行できない
-- 4. 失敗した場合、INVALID なインデックスが残る
-- INVALID インデックスの確認と削除:
SELECT indexname, indisvalid
FROM pg_indexes
JOIN pg_index ON indexrelid = (schemaname || '.' || indexname)::regclass
WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_amount;
10. パーティショニング
10.1 パーティショニングの概要
パーティショニングは、論理的に 1 つのテーブルを物理的に複数のテーブル（パーティション）に分割する機能である。大規模テーブルのクエリ性能とメンテナンス効率を向上させる。
パーティショニングの利点:
- パーティションプルーニングによるクエリ性能の向上
- 大規模テーブルのインデックス効率の改善
- パーティション単位での VACUUM / メンテナンス
- 古いデータのパーティション単位での削除（DROP / DETACH）
10.2 宣言的パーティショニング (Declarative Partitioning)
PostgreSQL 10 以降で利用可能な宣言的パーティショニングが推奨される。
10.2.1 RANGE パーティショニング
-- 日付による RANGE パーティショニング
CREATE TABLE orders (
id BIGSERIAL,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10,2),
status VARCHAR(20),
PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);
-- パーティションの作成
CREATE TABLE orders_2024_q1 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE orders_2024_q2 PARTITION OF orders
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
CREATE TABLE orders_2024_q3 PARTITION OF orders
FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
CREATE TABLE orders_2024_q4 PARTITION OF orders
FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');
CREATE TABLE orders_2025_q1 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
-- デフォルトパーティション（どのパーティションにも属さないデータ）
CREATE TABLE orders_default PARTITION OF orders DEFAULT;
-- パーティションにインデックスを作成（親テーブルに作ると全パーティションに自動適用）
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_date ON orders (order_date);
10.2.2 LIST パーティショニング
-- 地域による LIST パーティショニング
CREATE TABLE customers (
id BIGSERIAL,
name TEXT NOT NULL,
region VARCHAR(20) NOT NULL,
email TEXT,
PRIMARY KEY (id, region)
) PARTITION BY LIST (region);
CREATE TABLE customers_japan PARTITION OF customers
FOR VALUES IN ('JP');
CREATE TABLE customers_us PARTITION OF customers
FOR VALUES IN ('US');
CREATE TABLE customers_eu PARTITION OF customers
FOR VALUES IN ('DE', 'FR', 'GB', 'IT', 'ES');
CREATE TABLE customers_asia PARTITION OF customers
FOR VALUES IN ('CN', 'KR', 'SG', 'TH');
CREATE TABLE customers_other PARTITION OF customers DEFAULT;
10.2.3 HASH パーティショニング
-- HASH パーティショニング（データの均等分散）
CREATE TABLE events (
id BIGSERIAL,
user_id INTEGER NOT NULL,
event_type TEXT,
created_at TIMESTAMP DEFAULT NOW(),
PRIMARY KEY (id, user_id)
) PARTITION BY HASH (user_id);
CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3);
10.3 多階層パーティショニング
-- 年 → 月のサブパーティショニング
CREATE TABLE logs (
id BIGSERIAL,
log_date DATE NOT NULL,
level TEXT,
message TEXT,
PRIMARY KEY (id, log_date)
) PARTITION BY RANGE (log_date);
CREATE TABLE logs_2025 PARTITION OF logs
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01')
PARTITION BY RANGE (log_date);
CREATE TABLE logs_2025_01 PARTITION OF logs_2025
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE logs_2025_02 PARTITION OF logs_2025
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- ... 以下同様
10.4 パーティションプルーニング
-- パーティションプルーニングの有効化（デフォルトで ON）
SET enable_partition_pruning = on;
-- プルーニングの確認
EXPLAIN SELECT * FROM orders WHERE order_date = '2025-03-15';
-- Append
-- -> Seq Scan on orders_2025_q1 ← このパーティションのみアクセス
-- Filter: (order_date = '2025-03-15'::date)
-- 実行時プルーニング（パラメータ使用時、PostgreSQL 11+）
PREPARE q1(date) AS SELECT * FROM orders WHERE order_date = $1;
EXPLAIN EXECUTE q1('2025-03-15');
-- 実行時にパーティションが絞り込まれる
10.5 パーティション操作
-- パーティションの追加
CREATE TABLE orders_2025_q2 PARTITION OF orders
FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
-- パーティションのデタッチ（テーブルとして残す）
ALTER TABLE orders DETACH PARTITION orders_2024_q1;
-- 非同期デタッチ（長時間ロックを回避、PostgreSQL 14+）
ALTER TABLE orders DETACH PARTITION orders_2024_q1 CONCURRENTLY;
-- デタッチしたパーティションを削除
DROP TABLE orders_2024_q1;
-- 既存テーブルをパーティションとしてアタッチ
ALTER TABLE orders ATTACH PARTITION orders_2025_q3
FOR VALUES FROM ('2025-07-01') TO ('2025-10-01');
-- 注意: アタッチ時に制約チェックが実行される（大きなテーブルでは時間がかかる）
-- 事前に CHECK 制約を追加しておくとスキップされる:
ALTER TABLE orders_2025_q3 ADD CONSTRAINT chk_date
CHECK (order_date >= '2025-07-01' AND order_date < '2025-10-01');
10.6 pg_partman による自動管理
pg_partman は時系列パーティションの自動作成・削除を管理する拡張機能である。
-- pg_partman のインストール
CREATE EXTENSION pg_partman;
-- テンプレートテーブルの利用
SELECT partman.create_parent(
p_parent_table := 'public.events',
p_control := 'created_at',
p_type := 'range',
p_interval := '1 month',
p_premake := 3, -- 3 か月先まで事前作成
p_start_partition := '2025-01-01'
);
-- メンテナンスの実行（cron で定期実行推奨）
SELECT partman.run_maintenance();
-- 自動メンテナンスジョブの設定（pg_cron との連携）
SELECT cron.schedule('partman-maintenance', '30 * * * *',
$$SELECT partman.run_maintenance()$$);
-- 古いパーティションの自動削除設定
UPDATE partman.part_config
SET retention = '12 months',
retention_keep_table = false -- テーブルも削除 (true だとデタッチのみ)
WHERE parent_table = 'public.events';
10.7 パーティショニングのベストプラクティス
-- パーティション情報の確認
SELECT parent.relname AS parent,
child.relname AS partition,
pg_get_expr(child.relpartbound, child.oid) AS partition_bound,
pg_size_pretty(pg_relation_size(child.oid)) AS size
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'orders'
ORDER BY child.relname;
-- パーティション数の確認
SELECT count(*) AS partition_count
FROM pg_inherits
WHERE inhparent = 'orders'::regclass;
推奨事項:
- パーティションキーはクエリの WHERE 句で頻繁に使用するカラムを選ぶ
- パーティション数は数十〜数百が目安（数千以上は計画時間に影響）
- デフォルトパーティションを設定して、範囲外のデータを受け止める
- パーティションキーは PRIMARY KEY に含める必要がある
- 外部キーの参照先にパーティションテーブルは使用不可（PostgreSQL 11 以前）
11. レプリケーション
11.1 レプリケーションの種類
| 種類 | 説明 | 単位 | 用途 |
|---|---|---|---|
| ストリーミングレプリケーション | WAL を物理的に転送・再生 | データベースクラスタ全体 | HA / DR / 読み取りスケールアウト |
| 論理レプリケーション | テーブル単位の論理的な変更転送 | テーブル / スキーマ | 部分レプリケーション / バージョン混在 |
| ファイルベースレプリケーション | WAL ファイルをコピー | WAL セグメント | DR（遅延あり） |
11.2 ストリーミングレプリケーションのセットアップ
プライマリサーバーの設定
# postgresql.conf (プライマリ)
wal_level = replica
max_wal_senders = 10 # WAL Sender プロセスの最大数
wal_keep_size = 1GB # pg_wal に保持する WAL サイズ
max_replication_slots = 10 # レプリケーションスロットの最大数
hot_standby = on # スタンバイでの読み取りクエリを許可
# pg_hba.conf (プライマリ — レプリケーション接続許可)
host replication repl_user 192.168.1.0/24 scram-sha-256
-- レプリケーション用ユーザーの作成
CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'secure_password';
-- レプリケーションスロットの作成
SELECT pg_create_physical_replication_slot('standby1_slot');
スタンバイサーバーのセットアップ
# ベースバックアップの取得
pg_basebackup \
-h primary-server \
-p 5432 \
-U repl_user \
-D /var/lib/postgresql/17/main \
-Fp -Xs -P -R # -R: standby.signal と primary_conninfo を自動設定
# -R オプションにより以下が自動作成される:
# standby.signal (空ファイル — スタンバイモードの指示)
# postgresql.auto.conf に primary_conninfo が追加される
# postgresql.auto.conf (スタンバイ — 自動生成 or 手動設定)
primary_conninfo = 'host=primary-server port=5432 user=repl_user password=secure_password'
primary_slot_name = 'standby1_slot'
# postgresql.conf (スタンバイ)
hot_standby = on
hot_standby_feedback = on # プライマリの VACUUM にスタンバイの情報をフィードバック
max_standby_streaming_delay = 30s # ストリーミング中のクエリキャンセルまでの待機時間
max_standby_archive_delay = 30s # アーカイブリカバリ中のクエリキャンセルまでの待機時間
11.3 同期レプリケーション
# postgresql.conf (プライマリ)
synchronous_standby_names = 'FIRST 1 (standby1, standby2)'
# FIRST N — 最初の N 台が同期
# ANY N — 任意の N 台が同期（PostgreSQL 10+）
# 例: synchronous_standby_names = 'ANY 2 (standby1, standby2, standby3)'
synchronous_commit = on # 同期コミットレベル
# on — ローカル + リモートの WAL フラッシュを待つ
# remote_apply — リモートでの WAL 適用を待つ（最も強い一貫性）
# remote_write — リモートの OS バッファへの書き込みを待つ
# local — ローカルの WAL フラッシュのみ待つ
# off — WAL フラッシュを待たない
-- レプリケーションの状態確認（プライマリ側）
SELECT pid, application_name, client_addr,
state, sync_state, sync_priority,
sent_lsn, write_lsn, flush_lsn, replay_lsn,
write_lag, flush_lag, replay_lag
FROM pg_stat_replication;
-- レプリケーション遅延の計算
SELECT application_name,
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag_pretty,
replay_lag
FROM pg_stat_replication;
-- スタンバイ側での確認
SELECT pg_is_in_recovery(); -- true = スタンバイ
SELECT pg_last_wal_receive_lsn(); -- 最後に受信した WAL 位置
SELECT pg_last_wal_replay_lsn(); -- 最後に再生した WAL 位置
SELECT pg_last_xact_replay_timestamp(); -- 最後に再生したトランザクションの時刻
11.4 レプリケーションスロット
レプリケーションスロットは、スタンバイが必要とする WAL がプライマリで削除されないことを保証する。
-- 物理レプリケーションスロット
SELECT pg_create_physical_replication_slot('standby1_slot');
-- 論理レプリケーションスロット
SELECT pg_create_logical_replication_slot('my_slot', 'pgoutput');
-- スロットの確認
SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn,
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots;
-- 不要なスロットの削除（WAL の肥大化を防止）
SELECT pg_drop_replication_slot('unused_slot');
警告: 非アクティブなレプリケーションスロットは WAL の削除を阻止し、ディスクを枯渇させる原因となる。
max_slot_wal_keep_sizeで上限を設定することが推奨される。
# postgresql.conf
max_slot_wal_keep_size = 10GB # スロットが保持する WAL の上限
11.5 カスケードレプリケーション
プライマリ → スタンバイ1 → スタンバイ2
↓
スタンバイ3
# スタンバイ1 の設定
primary_conninfo = 'host=primary port=5432 ...'
# スタンバイ2 の設定（スタンバイ1 からレプリケーション）
primary_conninfo = 'host=standby1 port=5432 ...'
11.6 遅延レプリケーション
# postgresql.conf (スタンバイ)
recovery_min_apply_delay = '1 hour' # WAL の適用を 1 時間遅延
これにより、誤操作後のデータ復旧に猶予時間を確保できる。
11.7 プロモーション（フェイルオーバー）
# スタンバイをプライマリに昇格
pg_ctl promote -D /var/lib/postgresql/17/main
# または SQL コマンド
SELECT pg_promote();
# タイムラインの確認
SELECT timeline_id FROM pg_control_checkpoint();
11.8 pg_hba.conf のレプリケーション設定
# TYPE DATABASE USER ADDRESS METHOD
# ローカル接続
local all all peer
# レプリケーション接続
host replication repl_user 192.168.1.10/32 scram-sha-256
host replication repl_user 192.168.1.11/32 scram-sha-256
host replication repl_user 192.168.1.12/32 scram-sha-256
# 通常のリモート接続
host all all 192.168.1.0/24 scram-sha-256
# SSL 必須の接続
hostssl all all 0.0.0.0/0 scram-sha-256
12. 高可用性と障害復旧
12.1 高可用性の構成パターン
典型的な HA 構成:
┌──────────────┐
│ アプリケーション │
└──────┬───────┘
│
┌──────▼───────┐
│ HAProxy │ ← 接続ルーティング
│ / pgBouncer │
└──┬───────┬───┘
│ │
┌────────▼──┐ ┌──▼────────┐
│ プライマリ │ │ スタンバイ │
│ (R/W) │ │ (R/O) │
└─────┬─────┘ └─────┬─────┘
│ WAL Stream │
└──────────────┘
┌───────────────────────┐
│ Patroni / etcd │ ← 自動フェイルオーバー管理
└───────────────────────┘
12.2 Patroni によるフェイルオーバー自動化
Patroni は、etcd / Consul / ZooKeeper を DCS (Distributed Configuration Store) として使用し、自動フェイルオーバーを実現するツールである。
# /etc/patroni/patroni.yml — Patroni 設定例
scope: postgres-cluster
name: node1
restapi:
listen: 0.0.0.0:8008
connect_address: 192.168.1.10:8008
etcd3:
hosts:
- 192.168.1.20:2379
- 192.168.1.21:2379
- 192.168.1.22:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576 # 1MB
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_level: replica
hot_standby: on
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: on
initdb:
- encoding: UTF8
- data-checksums
pg_hba:
- host replication replicator 192.168.1.0/24 scram-sha-256
- host all all 192.168.1.0/24 scram-sha-256
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.1.10:5432
data_dir: /var/lib/postgresql/17/main
bin_dir: /usr/lib/postgresql/17/bin
authentication:
superuser:
username: postgres
password: super_secret
replication:
username: replicator
password: repl_secret
parameters:
shared_buffers: 8GB
effective_cache_size: 24GB
work_mem: 64MB
maintenance_work_mem: 2GB
max_connections: 200
tags:
nofailover: false
noloadbalance: false
clonefrom: false
# Patroni のクラスタ状態確認
$ patronictl -c /etc/patroni/patroni.yml list
+---------+--------+-----------+--------+---------+----+-----------+
| Member | Host | Role | State | TL | Lag| Tags |
+---------+--------+-----------+--------+---------+----+-----------+
| node1 | .1.10 | Leader | running| 3 | | |
| node2 | .1.11 | Replica | running| 3 | 0 | |
| node3 | .1.12 | Replica | running| 3 | 0 | |
+---------+--------+-----------+--------+---------+----+-----------+
# 手動スイッチオーバー
$ patronictl -c /etc/patroni/patroni.yml switchover
# --leader node1 --candidate node2 --scheduled now
# 手動フェイルオーバー
$ patronictl -c /etc/patroni/patroni.yml failover
# 設定の動的変更
$ patronictl -c /etc/patroni/patroni.yml edit-config
12.3 pgBouncer (接続プーリング)
pgBouncer は軽量な接続プーラーで、PostgreSQL への同時接続数を効率的に管理する。
# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
# プーリングモード
pool_mode = transaction # session | transaction | statement
# transaction モードが最もバランスが良い
# プール設定
default_pool_size = 25 # データベースあたりの接続数
min_pool_size = 5 # 最小プール接続数
reserve_pool_size = 5 # 予備プール
reserve_pool_timeout = 3 # 予備プール使用までの待機時間
max_client_conn = 1000 # クライアントからの最大接続数
max_db_connections = 100 # データベースへの最大接続数
# タイムアウト
server_idle_timeout = 600 # アイドルサーバー接続のタイムアウト
client_idle_timeout = 0 # クライアントアイドルタイムアウト (0=無制限)
query_timeout = 0 # クエリタイムアウト
query_wait_timeout = 120 # プール待機のタイムアウト
# ログ
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
# TLS
client_tls_sslmode = prefer
client_tls_key_file = /etc/pgbouncer/server.key
client_tls_cert_file = /etc/pgbouncer/server.crt
# pgBouncer の管理コンソール
$ psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer
pgbouncer=# SHOW POOLS;
pgbouncer=# SHOW CLIENTS;
pgbouncer=# SHOW SERVERS;
pgbouncer=# SHOW STATS;
pgbouncer=# RELOAD; -- 設定のリロード
pgbouncer=# PAUSE mydb; -- 特定DBの一時停止
pgbouncer=# RESUME mydb; -- 再開
12.4 HAProxy による負荷分散
# /etc/haproxy/haproxy.cfg
global
maxconn 1000
defaults
mode tcp
timeout connect 5s
timeout client 30s
timeout server 30s
# 書き込み用（プライマリのみ）
frontend pg_write
bind *:5432
default_backend pg_primary
backend pg_primary
option httpchk GET /primary
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server node1 192.168.1.10:5432 check port 8008
server node2 192.168.1.11:5432 check port 8008
server node3 192.168.1.12:5432 check port 8008
# 読み取り用（スタンバイ + プライマリ）
frontend pg_read
bind *:5433
default_backend pg_replicas
backend pg_replicas
balance roundrobin
option httpchk GET /replica
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server node1 192.168.1.10:5432 check port 8008
server node2 192.168.1.11:5432 check port 8008
server node3 192.168.1.12:5432 check port 8008
12.5 pg_rewind
pg_rewind は、フェイルオーバー後に古いプライマリをスタンバイとして復帰させるツールである。
# 古いプライマリ（node1）を新しいプライマリ（node2）のスタンバイにする
pg_rewind \
--target-pgdata=/var/lib/postgresql/17/main \
--source-server='host=node2 port=5432 user=postgres' \
--progress
# 前提条件:
# 1. wal_log_hints = on または data checksums が有効
# 2. 分岐点以降の WAL がまだ利用可能
12.6 監視とアラート
-- レプリケーション遅延の監視クエリ
SELECT
CASE
WHEN pg_is_in_recovery() THEN
EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))
ELSE 0
END AS replication_lag_seconds;
-- Patroni ヘルスチェック
-- curl -s http://192.168.1.10:8008/patroni | jq .
-- curl -s http://192.168.1.10:8008/primary → 200 or 503
-- curl -s http://192.168.1.10:8008/replica → 200 or 503
13. バックアップとリストア
13.1 バックアップ方式の比較
| 方式 | ツール | 種類 | 整合性 | サイズ | 復元速度 |
|---|---|---|---|---|---|
| 論理バックアップ | pg_dump / pg_dumpall | 完全 / 差分なし | トランザクション一貫 | 小〜中 | 遅い |
| 物理バックアップ | pg_basebackup | 完全 / 増分(PG17) | ファイルレベル | 大 | 速い |
| 継続的アーカイブ | WAL + ベースバックアップ | PITR | 任意の時点 | 大 | 中 |
| サードパーティ | pgBackRest / Barman | 完全/差分/増分 | ファイルレベル | 中 | 速い |
13.2 pg_dump / pg_restore
# テキスト形式のダンプ
pg_dump -h localhost -U postgres mydb > mydb_backup.sql
# カスタム形式（圧縮あり、並列リストア可能 — 推奨）
pg_dump -h localhost -U postgres -Fc mydb > mydb_backup.dump
# ディレクトリ形式（並列ダンプ可能）
pg_dump -h localhost -U postgres -Fd -j 4 mydb -f /backup/mydb_dir
# 特定のテーブルのみダンプ
pg_dump -h localhost -U postgres -t orders -t customers mydb > tables_backup.sql
# スキーマのみダンプ（データなし）
pg_dump -h localhost -U postgres --schema-only mydb > schema_only.sql
# データのみダンプ（スキーマなし）
pg_dump -h localhost -U postgres --data-only mydb > data_only.sql
# 特定スキーマのダンプ
pg_dump -h localhost -U postgres -n public mydb > public_schema.sql
# 大きなオブジェクトを含める
pg_dump -h localhost -U postgres --blobs mydb -Fc > mydb_with_blobs.dump
# COPY 形式でのデータエクスポート（高速）
pg_dump -h localhost -U postgres --data-only --column-inserts mydb > inserts.sql
# カスタム形式からのリストア
pg_restore -h localhost -U postgres -d mydb mydb_backup.dump
# 並列リストア（高速）
pg_restore -h localhost -U postgres -d mydb -j 8 mydb_backup.dump
# 特定のテーブルのみリストア
pg_restore -h localhost -U postgres -d mydb -t orders mydb_backup.dump
# クリーンリストア（既存オブジェクトを削除してから復元）
pg_restore -h localhost -U postgres -d mydb --clean --if-exists mydb_backup.dump
# リストア前にダンプの内容を確認
pg_restore --list mydb_backup.dump
13.3 pg_dumpall
# 全データベース + グローバルオブジェクト（ロール、テーブルスペース）
pg_dumpall -h localhost -U postgres > all_databases.sql
# グローバルオブジェクトのみ
pg_dumpall -h localhost -U postgres --globals-only > globals.sql
# ロールのみ
pg_dumpall -h localhost -U postgres --roles-only > roles.sql
13.4 pg_basebackup
# 基本的な物理バックアップ
pg_basebackup \
-h primary-server \
-U repl_user \
-D /backup/base_$(date +%Y%m%d) \
-Fp \ # Plain format
-Xs \ # WAL を streaming で取得
-P \ # 進捗表示
-c fast \ # チェックポイントを即座に実行
--label="daily_backup_$(date +%Y%m%d)"
# tar + 圧縮形式
pg_basebackup \
-h primary-server \
-U repl_user \
-D /backup/base_$(date +%Y%m%d) \
-Ft \ # Tar format
--compress=gzip:9 \ # 圧縮レベル
-Xs -P
# インクリメンタルバックアップ (PostgreSQL 17+)
pg_basebackup \
-h primary-server \
-U repl_user \
-D /backup/incr_$(date +%Y%m%d) \
--incremental=/backup/base_20250601/backup_manifest \
-Fp -Xs -P
# インクリメンタルバックアップの結合
pg_combinebackup \
/backup/base_20250601 \
/backup/incr_20250602 \
/backup/incr_20250603 \
-o /backup/combined_20250603
13.5 pgBackRest
pgBackRest は、エンタープライズ向けの高機能バックアップツールである。
# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/backup/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=encryption_key
# 圧縮設定
compress-type=zst
compress-level=3
# 並列処理
process-max=4
# S3 ストレージ（オプション）
# repo1-type=s3
# repo1-s3-bucket=my-pg-backup
# repo1-s3-region=ap-northeast-1
# repo1-s3-endpoint=s3.amazonaws.com
[mydb]
pg1-path=/var/lib/postgresql/17/main
pg1-port=5432
pg1-user=postgres
# stanza の作成（初回のみ）
pgbackrest --stanza=mydb stanza-create
# フルバックアップ
pgbackrest --stanza=mydb --type=full backup
# 差分バックアップ
pgbackrest --stanza=mydb --type=diff backup
# 増分バックアップ
pgbackrest --stanza=mydb --type=incr backup
# バックアップ一覧
pgbackrest --stanza=mydb info
# リストア（最新バックアップ）
pgbackrest --stanza=mydb restore
# PITR リストア
pgbackrest --stanza=mydb \
--type=time \
--target="2025-06-15 14:30:00" \
--target-action=promote \
restore
# 特定のデータベースのみリストア
pgbackrest --stanza=mydb \
--db-include=mydb \
restore
# WAL アーカイブコマンドとの統合
# postgresql.conf
# archive_command = 'pgbackrest --stanza=mydb archive-push %p'
# restore_command = 'pgbackrest --stanza=mydb archive-get %f "%p"'
13.6 Barman
Barman は、2ndQuadrant (現 EDB) が開発したバックアップマネージャーである。
# /etc/barman.conf (グローバル設定)
[barman]
barman_user = barman
configuration_files_directory = /etc/barman.d
barman_home = /var/lib/barman
log_file = /var/log/barman/barman.log
compression = gzip
# /etc/barman.d/mydb.conf (サーバー設定)
[mydb]
description = "Production PostgreSQL"
ssh_command = ssh postgres@primary-server
conninfo = host=primary-server user=barman dbname=postgres
backup_method = postgres
streaming_archiver = on
slot_name = barman
retention_policy = RECOVERY WINDOW OF 7 DAYS
# バックアップの実行
barman backup mydb
# バックアップ一覧
barman list-backup mydb
# リストア
barman recover mydb latest /var/lib/postgresql/17/main_restore \
--target-time "2025-06-15 14:30:00" \
--remote-ssh-command "ssh postgres@standby-server"
13.7 バックアップ戦略の設計
推奨バックアップスケジュール:
┌─────────────────────────────────────────────────┐
│ 日曜: フルバックアップ │
│ 月〜土: 差分バックアップ (毎日 02:00) │
│ 常時: WAL アーカイブ (継続的) │
│ 保持期間: フル 4 世代、差分 7 日、WAL 14 日 │
│ テスト: 月次でリストアテストを実施 │
└─────────────────────────────────────────────────┘
# cron による自動バックアップ（pgBackRest の例）
# フルバックアップ（日曜 02:00）
0 2 * * 0 pgbackrest --stanza=mydb --type=full backup
# 差分バックアップ（月〜土 02:00）
0 2 * * 1-6 pgbackrest --stanza=mydb --type=diff backup
# バックアップ検証（毎日 06:00）
0 6 * * * pgbackrest --stanza=mydb check
14. セキュリティ
14.1 認証設定 (pg_hba.conf)
pg_hba.conf はクライアント認証の設定ファイルである。上から順に評価され、最初にマッチしたルールが適用される。
# TYPE DATABASE USER ADDRESS METHOD
# ローカル接続（Unix ソケット）
local all postgres peer
local all all scram-sha-256
# IPv4 ローカル接続
host all all 127.0.0.1/32 scram-sha-256
# プライベートネットワーク
host all all 192.168.1.0/24 scram-sha-256
# レプリケーション
host replication repl_user 192.168.1.0/24 scram-sha-256
# SSL 必須（インターネット経由）
hostssl all all 0.0.0.0/0 scram-sha-256
hostssl all all ::/0 scram-sha-256
# 特定のデータベースへのアクセス制限
host finance_db finance_team 10.0.0.0/8 scram-sha-256
# 拒否ルール
host all all 0.0.0.0/0 reject
認証方式:
| 方式 | 説明 | 推奨度 |
|---|---|---|
| trust | 無条件で許可（本番厳禁） | 非推奨 |
| reject | 無条件で拒否 | 制限用 |
| scram-sha-256 | SCRAM-SHA-256 ハッシュ認証 | 推奨 |
| md5 | MD5 ハッシュ認証 | レガシー |
| password | 平文パスワード | 非推奨 |
| peer | OS ユーザー名での認証 | ローカル用 |
| cert | クライアント証明書認証 | 高セキュリティ |
| gss | Kerberos (GSSAPI) | エンタープライズ |
| ldap | LDAP 認証 | 集中管理 |
14.2 ロール管理
-- ロール（ユーザー）の作成
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'readonly_pass';
CREATE ROLE admin_user WITH LOGIN PASSWORD 'admin_pass' CREATEDB CREATEROLE;
-- グループロールの作成
CREATE ROLE app_group NOLOGIN;
CREATE ROLE readonly_group NOLOGIN;
-- グループにメンバーを追加
GRANT app_group TO app_user;
GRANT readonly_group TO readonly_user;
-- パスワードの有効期限設定
ALTER ROLE app_user VALID UNTIL '2026-01-01';
-- 接続制限
ALTER ROLE app_user CONNECTION LIMIT 10;
-- ロール属性の変更
ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE app_user SET work_mem = '256MB';
-- ロール一覧の確認
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb,
rolreplication, rolconnlimit, rolvaliduntil
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%'
ORDER BY rolname;
14.3 権限管理
-- データベースレベルの権限
GRANT CONNECT ON DATABASE mydb TO app_group;
REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;
-- スキーマレベルの権限
GRANT USAGE ON SCHEMA public TO app_group;
GRANT CREATE ON SCHEMA public TO admin_user;
-- テーブルレベルの権限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;
-- 将来のテーブルにもデフォルト権限を設定
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_group;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_group;
-- カラムレベルの権限
GRANT SELECT (id, name, email) ON users TO readonly_group;
REVOKE SELECT (salary) ON employees FROM readonly_group;
-- シーケンスの権限
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_group;
-- 関数の権限
GRANT EXECUTE ON FUNCTION calculate_total(integer) TO app_group;
REVOKE EXECUTE ON FUNCTION dangerous_function() FROM PUBLIC;
14.4 Row Level Security (RLS)
行レベルセキュリティにより、テーブル内の行ごとにアクセスを制御できる。
-- RLS を有効化
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- ポリシーの作成（ユーザーは自分の注文のみ参照可能）
CREATE POLICY orders_user_policy ON orders
FOR ALL
TO app_group
USING (customer_id = current_setting('app.current_user_id')::integer);
-- 管理者はすべての行を参照可能
CREATE POLICY orders_admin_policy ON orders
FOR ALL
TO admin_user
USING (true);
-- マルチテナント向けポリシー
CREATE POLICY tenant_isolation ON data_table
FOR ALL
USING (tenant_id = current_setting('app.tenant_id')::integer)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::integer);
-- アプリケーション側でのコンテキスト設定
SET app.current_user_id = '42';
SET app.tenant_id = '100';
-- ポリシーの確認
SELECT * FROM pg_policies WHERE tablename = 'orders';
-- テーブル所有者にも RLS を適用する場合
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
14.5 SSL/TLS 暗号化
# postgresql.conf — SSL 設定
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'
ssl_min_protocol_version = 'TLSv1.3'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = on
# 自己署名証明書の作成（テスト用）
openssl req -new -x509 -days 365 -nodes \
-out /etc/ssl/certs/server.crt \
-keyout /etc/ssl/private/server.key \
-subj "/CN=postgres-server"
chmod 600 /etc/ssl/private/server.key
chown postgres:postgres /etc/ssl/private/server.key
-- SSL 接続の確認
SELECT ssl, version, cipher, bits, client_dn
FROM pg_stat_ssl
JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid;
14.6 pgAudit (監査ログ)
# postgresql.conf
shared_preload_libraries = 'pgaudit'
# 監査対象の設定
pgaudit.log = 'ddl, write, role'
# none | read | write | function | role | ddl | misc | all
pgaudit.log_catalog = off
pgaudit.log_client = on
pgaudit.log_level = log
pgaudit.log_parameter = on
pgaudit.log_statement_once = off
-- pgAudit 拡張機能の有効化
CREATE EXTENSION pgaudit;
-- ロール単位の監査設定
ALTER ROLE app_user SET pgaudit.log = 'all';
-- オブジェクト単位の監査
ALTER ROLE auditor SET pgaudit.role = 'auditor';
GRANT SELECT ON orders TO auditor; -- orders テーブルの SELECT を監査
14.7 データ暗号化
-- pgcrypto 拡張機能
CREATE EXTENSION pgcrypto;
-- カラムレベルの暗号化
INSERT INTO sensitive_data (id, encrypted_ssn)
VALUES (1, pgp_sym_encrypt('123-45-6789', 'encryption_key'));
-- 復号化
SELECT id, pgp_sym_decrypt(encrypted_ssn, 'encryption_key') AS ssn
FROM sensitive_data WHERE id = 1;
-- ハッシュ化（パスワードなど）
INSERT INTO users (id, password_hash)
VALUES (1, crypt('user_password', gen_salt('bf', 12)));
-- パスワード検証
SELECT id FROM users
WHERE password_hash = crypt('user_password', password_hash);
15. パフォーマンスチューニング
15.1 postgresql.conf の主要パラメータ
# ===== 接続と認証 =====
max_connections = 200 # 最大接続数（pgBouncer 使用時は低めに）
superuser_reserved_connections = 3 # スーパーユーザー用の予約接続
# ===== メモリ =====
shared_buffers = 16GB # 物理メモリの 25%
effective_cache_size = 48GB # 物理メモリの 50-75%
work_mem = 64MB # ソート/ハッシュ操作用
maintenance_work_mem = 2GB # VACUUM/CREATE INDEX 用
huge_pages = try # Linux Huge Pages
# ===== WAL =====
wal_level = replica
wal_buffers = 64MB
wal_compression = lz4
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
# ===== クエリプランナー =====
random_page_cost = 1.1 # SSD の場合（HDD: 4.0）
effective_io_concurrency = 200 # SSD の場合（HDD: 2）
default_statistics_target = 200 # 統計情報の精度
# ===== パラレルクエリ =====
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
# ===== ログ =====
log_min_duration_statement = 1000 # 1秒以上のクエリをログ
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0 # 一時ファイル使用をすべてログ
log_autovacuum_min_duration = 0 # autovacuum をすべてログ
log_line_prefix = '%m [%p] %u@%d '
# ===== autovacuum =====
autovacuum_max_workers = 4
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_scale_factor = 0.05 # 5% で VACUUM
autovacuum_analyze_scale_factor = 0.025 # 2.5% で ANALYZE
# ===== その他 =====
jit = on # JIT コンパイル
track_io_timing = on # I/O 時間の計測
track_functions = all # 関数の実行統計
15.2 pg_stat_statements
pg_stat_statements は、実行されたすべての SQL 文の統計情報を収集する拡張機能である。
# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.track_planning = on
CREATE EXTENSION pg_stat_statements;
-- 実行時間が長いクエリ Top 20
SELECT query,
calls,
round(total_exec_time::numeric, 2) AS total_time_ms,
round(mean_exec_time::numeric, 2) AS avg_time_ms,
round(stddev_exec_time::numeric, 2) AS stddev_ms,
rows,
round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
-- 呼び出し回数が多いクエリ
SELECT query, calls, rows,
round(mean_exec_time::numeric, 2) AS avg_time_ms
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;
-- I/O が多いクエリ
SELECT query,
shared_blks_read + shared_blks_written AS total_blks_io,
temp_blks_read + temp_blks_written AS temp_blks_io,
calls
FROM pg_stat_statements
ORDER BY (shared_blks_read + shared_blks_written) DESC
LIMIT 20;
-- 統計のリセット
SELECT pg_stat_statements_reset();
15.3 auto_explain
クエリの実行計画を自動的にログに記録する。
# postgresql.conf
shared_preload_libraries = 'auto_explain,pg_stat_statements'
auto_explain.log_min_duration = 1000 # 1秒以上のクエリの実行計画をログ
auto_explain.log_analyze = on # 実際の実行統計を含める
auto_explain.log_buffers = on # バッファ使用量を含める
auto_explain.log_timing = on # タイミング情報を含める
auto_explain.log_triggers = on # トリガーの実行時間
auto_explain.log_verbose = on # 詳細表示
auto_explain.log_nested_statements = on # ネストされた文も対象
auto_explain.log_format = json # JSON 形式（解析しやすい）
15.4 接続プーリングの効果
接続プーリングなし:
アプリ → PostgreSQL (max_connections = 500)
問題: 各接続が 10MB のメモリを消費 → 5GB のメモリ使用
接続プーリングあり:
アプリ → pgBouncer (1000 クライアント接続) → PostgreSQL (50 サーバー接続)
効果: メモリ使用が大幅に削減、fork のオーバーヘッドが軽減
15.5 クエリチューニングの手法
-- 1. スロークエリの特定
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '5 seconds'
ORDER BY duration DESC;
-- 2. EXPLAIN ANALYZE で実行計画を分析
EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT YAML)
SELECT ...;
-- 3. 推定行数と実際の行数の乖離をチェック
-- estimated rows と actual rows が大きく異なる場合:
ANALYZE table_name; -- 統計情報を更新
ALTER TABLE table_name ALTER COLUMN col SET STATISTICS 1000; -- 精度向上
-- 4. 不要な Seq Scan を特定
SELECT schemaname, relname, seq_scan, seq_tup_read,
idx_scan, idx_tup_fetch,
CASE WHEN (seq_scan + idx_scan) > 0
THEN round(100.0 * idx_scan / (seq_scan + idx_scan), 2)
ELSE 0
END AS idx_scan_ratio
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_tup_read DESC
LIMIT 20;
-- 5. 使われていないインデックスの特定
SELECT s.schemaname, s.relname, s.indexrelname,
s.idx_scan, s.idx_tup_read, s.idx_tup_fetch,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
WHERE s.idx_scan = 0
AND pg_relation_size(s.indexrelid) > 8192
ORDER BY pg_relation_size(s.indexrelid) DESC;
15.6 OS レベルのチューニング
# Linux カーネルパラメータ
# /etc/sysctl.conf
# 共有メモリ
kernel.shmmax = 68719476736 # 64GB
kernel.shmall = 16777216
# Huge Pages
vm.nr_hugepages = 8400 # shared_buffers / 2MB + α
# Dirty ページの書き出し
vm.dirty_ratio = 10
vm.dirty_background_ratio = 3
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
# スワップの抑制
vm.swappiness = 1
# I/O スケジューラ（SSD 向け）
# echo none > /sys/block/sda/queue/scheduler
# echo 2 > /sys/block/sda/queue/nr_requests
# ファイルシステムマウントオプション
# ext4/xfs: noatime,nodiratime
# XFS が PostgreSQL に推奨
15.7 テーブル設計の最適化
-- 適切なデータ型の選択
-- 悪い例
CREATE TABLE bad_types (
id VARCHAR(10), -- 固定長 ID には INT を使うべき
flag VARCHAR(5), -- BOOLEAN を使うべき
amount VARCHAR(20), -- NUMERIC を使うべき
created VARCHAR(30) -- TIMESTAMP を使うべき
);
-- 良い例
CREATE TABLE good_types (
id INTEGER,
flag BOOLEAN,
amount NUMERIC(10,2),
created TIMESTAMPTZ
);
-- パーシャルインデックスで不要なデータを除外
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
-- テーブルの 5% のデータに対してのみインデックスが作成される
16. 拡張機能エコシステム
16.1 拡張機能の管理
-- 利用可能な拡張機能の一覧
SELECT name, default_version, comment
FROM pg_available_extensions
ORDER BY name;
-- インストール済みの拡張機能
SELECT extname, extversion FROM pg_extension ORDER BY extname;
-- 拡張機能のインストール
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- 拡張機能のバージョンアップ
ALTER EXTENSION pg_trgm UPDATE TO '1.7';
-- 拡張機能の削除
DROP EXTENSION pg_trgm CASCADE;
16.2 PostGIS (地理空間データ)
PostGIS は PostgreSQL に地理空間データ型と関数を追加する拡張機能で、世界で最も強力なオープンソース GIS データベースである。
CREATE EXTENSION postgis;
-- 空間テーブルの作成
CREATE TABLE stores (
id SERIAL PRIMARY KEY,
name TEXT,
geom GEOMETRY(Point, 4326) -- SRID 4326 = WGS84
);
-- データの挿入
INSERT INTO stores (name, geom) VALUES
('渋谷店', ST_SetSRID(ST_MakePoint(139.7013, 35.6580), 4326)),
('新宿店', ST_SetSRID(ST_MakePoint(139.7005, 35.6896), 4326)),
('池袋店', ST_SetSRID(ST_MakePoint(139.7107, 35.7295), 4326));
-- GiST インデックス
CREATE INDEX idx_stores_geom ON stores USING gist (geom);
-- 半径 5km 以内の店舗を検索
SELECT name,
ST_Distance(geom::geography,
ST_SetSRID(ST_MakePoint(139.7671, 35.6812), 4326)::geography) AS distance_m
FROM stores
WHERE ST_DWithin(geom::geography,
ST_SetSRID(ST_MakePoint(139.7671, 35.6812), 4326)::geography,
5000)
ORDER BY distance_m;
16.3 pg_trgm (トライグラム類似検索)
CREATE EXTENSION pg_trgm;
-- トライグラムインデックス
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);
-- あいまい検索（LIKE/ILIKE にインデックスが使える）
SELECT * FROM products WHERE name ILIKE '%PostgreSQL%';
-- 類似度検索
SELECT name, similarity(name, 'Postgre') AS sim
FROM products
WHERE similarity(name, 'Postgre') > 0.3
ORDER BY sim DESC;
-- 類似度のしきい値設定
SET pg_trgm.similarity_threshold = 0.3;
SELECT * FROM products WHERE name % 'Postgre';
16.4 hstore (キー/バリュー型)
CREATE EXTENSION hstore;
-- hstore カラムの使用
CREATE TABLE product_attributes (
product_id INT PRIMARY KEY,
attrs HSTORE
);
INSERT INTO product_attributes VALUES
(1, 'color => "red", size => "M", material => "cotton"');
-- 特定のキーの値を取得
SELECT attrs -> 'color' AS color FROM product_attributes WHERE product_id = 1;
-- キーの存在確認
SELECT * FROM product_attributes WHERE attrs ? 'color';
-- GIN インデックス
CREATE INDEX idx_attrs ON product_attributes USING gin (attrs);
16.5 ltree (階層データ)
CREATE EXTENSION ltree;
-- 階層カテゴリの管理
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
path LTREE,
name TEXT
);
INSERT INTO categories (path, name) VALUES
('root', 'ルート'),
('root.electronics', 'エレクトロニクス'),
('root.electronics.computers', 'コンピュータ'),
('root.electronics.computers.laptops', 'ノートPC'),
('root.electronics.phones', '携帯電話');
-- 子孫の検索
SELECT * FROM categories WHERE path <@ 'root.electronics';
-- 祖先の検索
SELECT * FROM categories WHERE path @> 'root.electronics.computers.laptops';
-- GiST インデックス
CREATE INDEX idx_categories_path ON categories USING gist (path);
16.6 pgvector (ベクトル検索 / AI)
CREATE EXTENSION vector;
-- ベクトルカラムを持つテーブル
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT,
embedding VECTOR(1536) -- OpenAI ada-002 の次元数
);
-- ベクトルの挿入
INSERT INTO documents (content, embedding) VALUES
('PostgreSQL is great', '[0.1, 0.2, ..., 0.5]');
-- コサイン類似度による近傍検索
SELECT id, content,
1 - (embedding <=> '[0.1, 0.2, ..., 0.5]') AS cosine_similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ..., 0.5]'
LIMIT 10;
-- IVFFlat インデックス（近似最近傍検索）
CREATE INDEX idx_docs_embedding ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
-- HNSW インデックス（より高精度）
CREATE INDEX idx_docs_embedding_hnsw ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- 内積距離
SELECT * FROM documents
ORDER BY embedding <#> '[0.1, 0.2, ..., 0.5]'
LIMIT 10;
-- L2 距離
SELECT * FROM documents
ORDER BY embedding <-> '[0.1, 0.2, ..., 0.5]'
LIMIT 10;
16.7 pg_cron (ジョブスケジューラ)
CREATE EXTENSION pg_cron;
-- 毎日 3 時に古いデータを削除
SELECT cron.schedule('cleanup-old-data', '0 3 * * *',
$$DELETE FROM logs WHERE created_at < now() - interval '90 days'$$);
-- 毎時統計情報を更新
SELECT cron.schedule('hourly-analyze', '0 * * * *',
$$ANALYZE$$);
-- 毎週日曜にフルバキュームをスケジュール
SELECT cron.schedule('weekly-vacuum', '0 4 * * 0',
$$VACUUM (ANALYZE, VERBOSE)$$);
-- ジョブの一覧
SELECT * FROM cron.job;
-- ジョブの実行履歴
SELECT * FROM cron.job_run_details ORDER BY start_time DESC LIMIT 20;
-- ジョブの削除
SELECT cron.unschedule('cleanup-old-data');
16.8 TimescaleDB (時系列データ)
-- TimescaleDB の有効化
CREATE EXTENSION timescaledb;
-- ハイパーテーブルの作成
CREATE TABLE metrics (
time TIMESTAMPTZ NOT NULL,
sensor_id INTEGER,
temperature DOUBLE PRECISION,
humidity DOUBLE PRECISION
);
SELECT create_hypertable('metrics', 'time', chunk_time_interval => INTERVAL '1 day');
-- 連続集約（マテリアライズドビュー）
CREATE MATERIALIZED VIEW hourly_metrics
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
sensor_id,
avg(temperature) AS avg_temp,
max(temperature) AS max_temp,
min(temperature) AS min_temp
FROM metrics
GROUP BY bucket, sensor_id;
-- データ保持ポリシー
SELECT add_retention_policy('metrics', INTERVAL '90 days');
16.9 Citus (分散データベース)
-- Citus の有効化
CREATE EXTENSION citus;
-- 分散テーブルの作成
SELECT create_distributed_table('orders', 'customer_id');
-- リファレンステーブル（全ノードに複製）
SELECT create_reference_table('countries');
-- 分散クエリの実行（自動的に各ノードで並列実行）
SELECT customer_id, sum(amount)
FROM orders
GROUP BY customer_id
ORDER BY sum(amount) DESC
LIMIT 10;
17. 論理レプリケーションと CDC
17.1 論理レプリケーションの概要
論理レプリケーションは、テーブル単位のデータ変更をSQL レベルで複製する仕組みである。ストリーミングレプリケーション（物理レプリケーション）とは異なり、異なるバージョン間やテーブルの部分的な複製が可能である。
| 特徴 | 物理レプリケーション | 論理レプリケーション |
|---|---|---|
| 複製単位 | クラスタ全体 | テーブル / スキーマ |
| バージョン混在 | 不可 | 可能 |
| 書き込み | スタンバイは読み取り専用 | サブスクライバーは書き込み可能 |
| DDL 複製 | 自動 | 手動 |
| シーケンス | 自動 | 非対応（手動同期が必要） |
| Large Object | 対応 | 非対応 |
17.2 パブリケーションとサブスクリプション
-- ===== パブリッシャー（送信側）の設定 =====
-- 前提条件
-- wal_level = logical
-- max_replication_slots >= サブスクライバー数
-- max_wal_senders >= サブスクライバー数
-- パブリケーションの作成
CREATE PUBLICATION my_pub FOR TABLE orders, customers;
-- すべてのテーブルを対象にする場合
CREATE PUBLICATION all_tables_pub FOR ALL TABLES;
-- スキーマ全体を対象にする場合 (PostgreSQL 15+)
CREATE PUBLICATION schema_pub FOR TABLES IN SCHEMA public;
-- 特定のカラムのみ (PostgreSQL 15+)
CREATE PUBLICATION partial_pub FOR TABLE orders (id, customer_id, amount, status);
-- 行フィルター (PostgreSQL 15+)
CREATE PUBLICATION filtered_pub FOR TABLE orders WHERE (status = 'completed');
-- パブリケーションの確認
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;
-- ===== サブスクライバー（受信側）の設定 =====
-- サブスクリプションの作成
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=publisher port=5432 dbname=mydb user=repl_user password=secret'
PUBLICATION my_pub
WITH (
copy_data = true, -- 初期データをコピー
create_slot = true, -- スロットを自動作成
enabled = true,
synchronous_commit = 'off', -- サブスクライバー側の同期コミット
binary = true, -- バイナリ転送 (PG14+、高速)
streaming = 'parallel' -- 大きなトランザクションのストリーミング (PG16+)
);
-- サブスクリプションの状態確認
SELECT subname, pid, relid::regclass, received_lsn,
last_msg_send_time, last_msg_receipt_time,
latest_end_lsn, latest_end_time
FROM pg_stat_subscription;
-- テーブルごとの同期状態
SELECT srsubid, srrelid::regclass, srsublsn, srsubstate
FROM pg_subscription_rel;
-- srsubstate: i=初期化中, d=データコピー中, s=同期済み, r=レプリケーション中
-- サブスクリプションの一時停止と再開
ALTER SUBSCRIPTION my_sub DISABLE;
ALTER SUBSCRIPTION my_sub ENABLE;
-- サブスクリプションのリフレッシュ（新しいテーブルの追加時）
ALTER SUBSCRIPTION my_sub REFRESH PUBLICATION;
-- サブスクリプションの削除
DROP SUBSCRIPTION my_sub;
17.3 論理デコーディング
論理デコーディングは、WAL の変更を論理的な形式で出力する仕組みである。CDC (Change Data Capture) の基盤となる。
-- 論理レプリケーションスロットの作成
SELECT pg_create_logical_replication_slot('my_cdc_slot', 'pgoutput');
-- テスト用のデコーディング出力プラグイン
SELECT pg_create_logical_replication_slot('test_slot', 'test_decoding');
-- 変更の取得（test_decoding プラグイン）
SELECT * FROM pg_logical_slot_get_changes('test_slot', NULL, NULL);
-- lsn | xid | data
-- 0/1A | 100 | BEGIN 100
-- 0/1B | 100 | table public.orders: INSERT: id[integer]:1 amount[numeric]:100.00
-- 0/1C | 100 | COMMIT 100
-- 変更のピーク（消費せずに確認）
SELECT * FROM pg_logical_slot_peek_changes('test_slot', NULL, NULL);
-- スロットの進捗を進める
SELECT pg_replication_slot_advance('my_cdc_slot', '0/1A2B3C4D');
17.4 出力プラグイン
| プラグイン | 説明 | 形式 |
|---|---|---|
| pgoutput | 標準（論理レプリケーション用） | バイナリプロトコル |
| test_decoding | テスト・デバッグ用 | テキスト |
| wal2json | JSON 形式で出力 | JSON |
| decoderbufs | Protocol Buffers 形式 | Protobuf |
-- wal2json の使用例
SELECT pg_create_logical_replication_slot('json_slot', 'wal2json');
-- JSON 形式で変更を取得
SELECT data FROM pg_logical_slot_peek_changes('json_slot', NULL, NULL,
'include-timestamp', 'true',
'include-lsn', 'true');
-- 出力例:
-- {
-- "change": [
-- {
-- "kind": "insert",
-- "schema": "public",
-- "table": "orders",
-- "columnnames": ["id", "amount", "status"],
-- "columntypes": ["integer", "numeric", "text"],
-- "columnvalues": [1, 100.00, "pending"]
-- }
-- ]
-- }
17.5 Debezium 連携
Debezium は、データベースの変更をリアルタイムで Apache Kafka に配信する CDC プラットフォームである。
// Debezium PostgreSQL コネクタ設定
{
"name": "pg-connector",
"config": {
"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
"database.hostname": "postgres-server",
"database.port": "5432",
"database.user": "debezium",
"database.password": "secret",
"database.dbname": "mydb",
"topic.prefix": "myapp",
"plugin.name": "pgoutput",
"slot.name": "debezium_slot",
"publication.name": "debezium_pub",
"table.include.list": "public.orders,public.customers",
"snapshot.mode": "initial",
"tombstones.on.delete": true,
"decimal.handling.mode": "string",
"time.precision.mode": "connect",
"heartbeat.interval.ms": 30000,
"slot.drop.on.stop": false
}
}
-- Debezium 用のユーザーとパブリケーション
CREATE ROLE debezium WITH REPLICATION LOGIN PASSWORD 'secret';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO debezium;
CREATE PUBLICATION debezium_pub FOR ALL TABLES;
17.6 論理レプリケーションの制限事項
- DDL は複製されない — スキーマ変更は手動で両側に適用する必要がある
- シーケンスは同期されない —
pg_sequencesを手動で同期する必要がある - Large Object は非対応 — TOAST データは対応するが、
lo型は不可 - TRUNCATE — PostgreSQL 11 以降で対応
- 一意制約が必要 — PRIMARY KEY または REPLICA IDENTITY が必要
-- REPLICA IDENTITY の設定
ALTER TABLE orders REPLICA IDENTITY FULL; -- 全カラムを送信（遅い）
ALTER TABLE orders REPLICA IDENTITY DEFAULT; -- PRIMARY KEY のみ
ALTER TABLE orders REPLICA IDENTITY USING INDEX idx_orders_unique; -- 特定のインデックス
18. JSON / JSONB サポート
18.1 JSON と JSONB の違い
| 特徴 | JSON | JSONB |
|---|---|---|
| 格納形式 | テキスト（原文保持） | バイナリ（パース済み） |
| 挿入速度 | 高速 | やや遅い（パースが必要） |
| 読み取り速度 | 遅い（毎回パース） | 高速 |
| インデックス | 不可 | GIN インデックス対応 |
| 重複キー | 保持 | 最後の値を採用 |
| キー順序 | 保持 | 保持しない |
| 空白 | 保持 | 削除 |
推奨: ほとんどの場合 JSONB を使用すべきである。JSON は原文の保持が必要な場合のみ使用する。
18.2 JSONB の基本操作
-- テーブル作成
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY,
event_type TEXT NOT NULL,
data JSONB NOT NULL DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- データの挿入
INSERT INTO events (event_type, data) VALUES
('purchase', '{
"user_id": 123,
"product": {"name": "PostgreSQL Book", "price": 4500},
"quantity": 2,
"tags": ["database", "learning"],
"shipping": {"address": "東京都渋谷区", "express": true}
}');
-- キーの値を取得 (->> はテキスト、-> は JSON)
SELECT data->>'user_id' AS user_id, -- テキスト: "123"
data->'product'->>'name' AS product_name, -- テキスト: "PostgreSQL Book"
data->'product'->'price' AS price_json, -- JSON: 4500
(data->'product'->>'price')::int AS price -- 整数: 4500
FROM events;
-- ネストされた値のアクセス (#>> は #> のテキスト版)
SELECT data #>> '{product,name}' AS product_name,
data #>> '{shipping,address}' AS address
FROM events;
18.3 JSONB 演算子
-- 含有チェック (@>)
SELECT * FROM events WHERE data @> '{"user_id": 123}';
-- 被含有チェック (<@)
SELECT * FROM events WHERE '{"user_id": 123}' <@ data;
-- キーの存在チェック (?)
SELECT * FROM events WHERE data ? 'shipping';
-- 複数キーの存在チェック (?& = すべて、?| = いずれか)
SELECT * FROM events WHERE data ?& array['user_id', 'product'];
SELECT * FROM events WHERE data ?| array['coupon', 'discount'];
-- JSONB の連結 (||)
UPDATE events SET data = data || '{"status": "completed"}'
WHERE id = 1;
-- キーの削除 (-)
UPDATE events SET data = data - 'tags'
WHERE id = 1;
-- ネストされたキーの削除 (#-)
UPDATE events SET data = data #- '{shipping,express}'
WHERE id = 1;
-- jsonb_set でネストされた値を更新
UPDATE events SET data = jsonb_set(data, '{product,price}', '3800')
WHERE id = 1;
-- jsonb_insert で配列に要素を追加
UPDATE events SET data = jsonb_insert(data, '{tags,0}', '"new_tag"')
WHERE id = 1;
18.4 JSONB 関数
-- JSONB オブジェクトのキーと値を展開
SELECT key, value
FROM events, jsonb_each(data)
WHERE id = 1;
-- JSONB オブジェクトのキーと値をテキストで展開
SELECT key, value
FROM events, jsonb_each_text(data)
WHERE id = 1;
-- JSONB 配列の展開
SELECT jsonb_array_elements_text(data->'tags') AS tag
FROM events WHERE id = 1;
-- JSONB オブジェクトのキー一覧
SELECT jsonb_object_keys(data) FROM events WHERE id = 1;
-- JSONB の型を取得
SELECT jsonb_typeof(data->'user_id'), -- number
jsonb_typeof(data->'tags'), -- array
jsonb_typeof(data->'product'), -- object
jsonb_typeof(data->'shipping'->'express') -- boolean
FROM events WHERE id = 1;
-- JSONB からレコード型への変換
SELECT * FROM jsonb_to_record(
'{"name": "John", "age": 30, "city": "Tokyo"}'::jsonb
) AS x(name text, age int, city text);
-- 行から JSONB への変換
SELECT to_jsonb(t) FROM (SELECT id, event_type, created_at FROM events) t;
-- JSONB の集約
SELECT jsonb_agg(jsonb_build_object('id', id, 'type', event_type))
FROM events;
-- jsonb_build_object / jsonb_build_array
SELECT jsonb_build_object(
'total', count(*),
'types', jsonb_agg(DISTINCT event_type)
) FROM events;
-- JSONB の整形表示
SELECT jsonb_pretty(data) FROM events WHERE id = 1;
18.5 JSON Path (PostgreSQL 12+)
SQL/JSON Path は、JSONB 内のデータを柔軟に検索するための標準的なクエリ言語である。
-- JSON Path によるクエリ
SELECT jsonb_path_query(data, '$.product.name') FROM events;
-- フィルター条件付きクエリ
SELECT jsonb_path_query(data, '$.tags[*] ? (@ == "database")') FROM events;
-- 数値条件
SELECT * FROM events
WHERE jsonb_path_exists(data, '$.product.price ? (@ > 3000)');
-- 配列の要素数
SELECT jsonb_path_query(data, '$.tags.size()') FROM events;
-- JSON Path で値を確認
SELECT jsonb_path_match(data, '$.quantity > 1') FROM events;
-- 再帰検索 (**)
SELECT jsonb_path_query(data, '$.**."price"') FROM events;
-- ネストのどの深さにある "price" も検索
-- JSON Path 変数
SELECT * FROM events
WHERE jsonb_path_exists(data, '$.product.price ? (@ > $min_price)',
'{"min_price": 3000}');
18.6 JSONB インデックス
-- デフォルト GIN インデックス（@>, ?, ?&, ?| 演算子に対応）
CREATE INDEX idx_events_data ON events USING gin (data);
-- jsonb_path_ops GIN インデックス（@> 演算子のみ、より小さくて高速）
CREATE INDEX idx_events_data_path ON events USING gin (data jsonb_path_ops);
-- 特定のキーに対する B-tree インデックス
CREATE INDEX idx_events_user_id ON events ((data->>'user_id'));
CREATE INDEX idx_events_price ON events (((data->'product'->>'price')::int));
-- 式インデックスと部分インデックスの組み合わせ
CREATE INDEX idx_events_active_user ON events ((data->>'user_id'))
WHERE data->>'status' = 'active';
18.7 Generated Columns と JSONB
-- JSONB フィールドから生成カラムを作成 (PostgreSQL 12+)
CREATE TABLE products (
id SERIAL PRIMARY KEY,
data JSONB NOT NULL,
name TEXT GENERATED ALWAYS AS (data->>'name') STORED,
price NUMERIC GENERATED ALWAYS AS ((data->>'price')::numeric) STORED
);
-- 生成カラムに対してインデックスを作成
CREATE INDEX idx_products_name ON products (name);
CREATE INDEX idx_products_price ON products (price);
19. PL/pgSQL と手続き型言語
19.1 PL/pgSQL の概要
PL/pgSQL は PostgreSQL のデフォルトの手続き型言語であり、SQL に変数、制御構文、例外処理を追加する。
19.2 関数 (Functions)
-- 基本的な関数
CREATE OR REPLACE FUNCTION calculate_tax(price NUMERIC, tax_rate NUMERIC DEFAULT 0.10)
RETURNS NUMERIC
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
RETURN round(price * tax_rate, 2);
END;
$$;
SELECT calculate_tax(1000); -- → 100.00
SELECT calculate_tax(1000, 0.08); -- → 80.00
-- 複数の値を返す関数
CREATE OR REPLACE FUNCTION get_order_summary(p_customer_id INT)
RETURNS TABLE (
total_orders BIGINT,
total_amount NUMERIC,
avg_amount NUMERIC,
last_order TIMESTAMPTZ
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
RETURN QUERY
SELECT count(*),
sum(amount),
round(avg(amount), 2),
max(created_at)
FROM orders
WHERE customer_id = p_customer_id;
END;
$$;
SELECT * FROM get_order_summary(42);
-- SETOF を使った集合を返す関数
CREATE OR REPLACE FUNCTION get_overdue_orders(p_days INT DEFAULT 30)
RETURNS SETOF orders
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
RETURN QUERY
SELECT *
FROM orders
WHERE status = 'pending'
AND created_at < NOW() - (p_days || ' days')::INTERVAL
ORDER BY created_at;
END;
$$;
SELECT * FROM get_overdue_orders(7);
19.3 制御構文
CREATE OR REPLACE FUNCTION process_order(p_order_id INT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
v_order orders%ROWTYPE;
v_status TEXT;
v_amount NUMERIC;
BEGIN
-- レコードの取得
SELECT * INTO v_order FROM orders WHERE id = p_order_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Order % not found', p_order_id;
END IF;
-- CASE 文
v_status := CASE v_order.status
WHEN 'pending' THEN 'processing'
WHEN 'processing' THEN 'shipped'
WHEN 'shipped' THEN 'delivered'
ELSE v_order.status
END;
-- IF-ELSIF-ELSE
IF v_order.amount > 10000 THEN
v_amount := v_order.amount * 0.95; -- 5% 割引
ELSIF v_order.amount > 5000 THEN
v_amount := v_order.amount * 0.97; -- 3% 割引
ELSE
v_amount := v_order.amount;
END IF;
-- ループ
-- FOR ループ
FOR i IN 1..10 LOOP
-- 処理
CONTINUE WHEN i = 5; -- スキップ
EXIT WHEN i = 8; -- 終了
END LOOP;
-- WHILE ループ
WHILE v_amount > 0 LOOP
v_amount := v_amount - 100;
END LOOP;
-- カーソルを使った FOREACH
-- FOR rec IN SELECT * FROM items WHERE order_id = p_order_id LOOP
-- RAISE NOTICE 'Item: %', rec.name;
-- END LOOP;
-- 更新
UPDATE orders SET status = v_status WHERE id = p_order_id;
RETURN format('Order %s updated to %s', p_order_id, v_status);
EXCEPTION
WHEN unique_violation THEN
RAISE WARNING 'Duplicate key for order %', p_order_id;
RETURN 'ERROR: duplicate';
WHEN OTHERS THEN
RAISE WARNING 'Error: % %', SQLERRM, SQLSTATE;
RETURN 'ERROR: ' || SQLERRM;
END;
$$;
19.4 ストアドプロシージャ (PostgreSQL 11+)
プロシージャは関数と異なり、トランザクション制御（COMMIT / ROLLBACK）が可能である。
CREATE OR REPLACE PROCEDURE bulk_archive_orders(p_before_date DATE, p_batch_size INT DEFAULT 1000)
LANGUAGE plpgsql
AS $$
DECLARE
v_count INT;
v_total INT := 0;
BEGIN
LOOP
-- バッチ処理
WITH moved AS (
DELETE FROM orders
WHERE id IN (
SELECT id FROM orders
WHERE status = 'completed'
AND created_at < p_before_date
LIMIT p_batch_size
)
RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;
GET DIAGNOSTICS v_count = ROW_COUNT;
v_total := v_total + v_count;
-- バッチごとにコミット（長時間ロックを避ける）
COMMIT;
RAISE NOTICE 'Archived % rows (total: %)', v_count, v_total;
EXIT WHEN v_count < p_batch_size;
END LOOP;
RAISE NOTICE 'Archive completed. Total: % rows', v_total;
END;
$$;
CALL bulk_archive_orders('2024-01-01');
19.5 トリガー
-- 更新日時の自動設定トリガー
CREATE OR REPLACE FUNCTION update_modified_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at := NOW();
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_orders_modified
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_modified_at();
-- 監査ログトリガー
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
table_name TEXT NOT NULL,
operation TEXT NOT NULL,
old_data JSONB,
new_data JSONB,
changed_by TEXT DEFAULT current_user,
changed_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO audit_log (table_name, operation, old_data)
VALUES (TG_TABLE_NAME, 'DELETE', to_jsonb(OLD));
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (table_name, operation, old_data, new_data)
VALUES (TG_TABLE_NAME, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
RETURN NEW;
ELSIF TG_OP = 'INSERT' THEN
INSERT INTO audit_log (table_name, operation, new_data)
VALUES (TG_TABLE_NAME, 'INSERT', to_jsonb(NEW));
RETURN NEW;
END IF;
END;
$$;
CREATE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION audit_trigger_func();
-- INSTEAD OF トリガー（ビュー更新用）
CREATE OR REPLACE FUNCTION view_insert_func()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO orders (customer_id, amount, status)
VALUES (NEW.customer_id, NEW.amount, 'pending');
RETURN NEW;
END;
$$;
-- イベントトリガー（DDL 操作の監視）
CREATE OR REPLACE FUNCTION log_ddl_changes()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'DDL command: % by %', tg_tag, current_user;
END;
$$;
CREATE EVENT TRIGGER ddl_logger ON ddl_command_end
EXECUTE FUNCTION log_ddl_changes();
19.6 その他の手続き型言語
-- PL/Python (信頼されない版)
CREATE EXTENSION plpython3u;
CREATE OR REPLACE FUNCTION py_json_parse(input TEXT)
RETURNS JSONB
LANGUAGE plpython3u
AS $$
import json
data = json.loads(input)
return json.dumps(data)
$$;
-- PL/Perl
CREATE EXTENSION plperl;
CREATE OR REPLACE FUNCTION perl_reverse(input TEXT)
RETURNS TEXT
LANGUAGE plperl
AS $$
return scalar reverse $_[0];
$$;
-- SQL 言語の関数（簡単なケースに推奨）
CREATE OR REPLACE FUNCTION get_active_count()
RETURNS BIGINT
LANGUAGE sql
STABLE
AS $$
SELECT count(*) FROM orders WHERE status = 'active';
$$;
19.7 関数のボラティリティ分類
| 分類 | 意味 | 用途 |
|---|---|---|
| IMMUTABLE | 同じ引数で常に同じ結果 | 数学関数、型変換 |
| STABLE | 同一トランザクション内で同じ結果 | 設定値の読み取り、テーブル参照 |
| VOLATILE | 呼び出しごとに結果が変わりうる | random()、now()、データ変更 |
-- ボラティリティの指定（オプティマイザのヒントとして重要）
CREATE FUNCTION pure_calc(x INT) RETURNS INT
LANGUAGE sql IMMUTABLE PARALLEL SAFE
AS $$ SELECT x * 2 + 1 $$;
-- PARALLEL SAFE / RESTRICTED / UNSAFE
-- パラレルクエリで使用可能かどうかを指定
20. 監視と運用
20.1 pg_stat 系ビュー一覧
| ビュー | 監視対象 |
|---|---|
| pg_stat_database | データベースレベルの統計 |
| pg_stat_user_tables | ユーザーテーブルの統計 |
| pg_stat_user_indexes | ユーザーインデックスの統計 |
| pg_stat_activity | 現在のセッション・クエリ |
| pg_stat_replication | レプリケーション状態 |
| pg_stat_wal_receiver | WAL Receiver 状態 |
| pg_stat_bgwriter | Background Writer 統計 |
| pg_stat_wal | WAL 統計 (PG14+) |
| pg_stat_io | I/O 統計 (PG16+) |
| pg_stat_progress_vacuum | VACUUM 進捗 |
| pg_stat_progress_create_index | CREATE INDEX 進捗 |
| pg_stat_progress_analyze | ANALYZE 進捗 |
| pg_stat_ssl | SSL 接続情報 |
| pg_stat_archiver | WAL アーカイブ統計 |
| pg_stat_subscription | 論理レプリケーションサブスクリプション |
20.2 pg_stat_activity の活用
-- アクティブなクエリの確認
SELECT pid, usename, datname, client_addr,
now() - query_start AS duration,
wait_event_type, wait_event,
state, left(query, 100) AS query
FROM pg_stat_activity
WHERE state = 'active'
AND pid != pg_backend_pid()
ORDER BY duration DESC;
-- 長時間実行中のクエリ
SELECT pid, usename, datname,
now() - query_start AS duration,
query
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '5 minutes'
AND pid != pg_backend_pid();
-- アイドルトランザクション（idle in transaction）
SELECT pid, usename, datname,
now() - state_change AS idle_duration,
query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND now() - state_change > interval '10 minutes';
-- 待機イベントの分類
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC;
-- 接続数の監視
SELECT datname, usename,
count(*) AS connections,
count(*) FILTER (WHERE state = 'active') AS active,
count(*) FILTER (WHERE state = 'idle') AS idle,
count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY datname, usename
ORDER BY connections DESC;
20.3 データベース統計
-- データベース全体の健全性チェック
SELECT datname,
numbackends,
xact_commit,
xact_rollback,
round(100.0 * xact_commit / nullif(xact_commit + xact_rollback, 0), 2) AS commit_ratio,
blks_read,
blks_hit,
round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_ratio,
tup_returned,
tup_fetched,
tup_inserted,
tup_updated,
tup_deleted,
deadlocks,
temp_files,
pg_size_pretty(temp_bytes) AS temp_bytes,
stats_reset
FROM pg_stat_database
WHERE datname = current_database();
20.4 テーブルの肥大化の監視
-- テーブルの膨張率を確認
SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS index_size,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0
THEN round(100.0 * n_dead_tup / n_live_tup, 2)
ELSE 0
END AS dead_pct,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;
-- pgstattuple でより正確な膨張率を確認
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');
-- table_len — テーブルの物理サイズ
-- tuple_count — 有効なタプル数
-- tuple_len — 有効なタプルの合計サイズ
-- tuple_percent — 有効なタプルが占める割合
-- dead_tuple_count — デッドタプル数
-- dead_tuple_len — デッドタプルの合計サイズ
-- dead_tuple_percent — デッドタプルが占める割合
-- free_space — 空き領域
-- free_percent — 空き領域の割合
20.5 ログ設定
# postgresql.conf — ログ設定
# ログ出力先
log_destination = 'stderr' # stderr, csvlog, syslog, jsonlog (PG15+)
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_truncate_on_rotation = on
# ログ内容
log_line_prefix = '%m [%p] %q%u@%d '
log_timezone = 'Asia/Tokyo'
# スロークエリログ
log_min_duration_statement = 1000 # 1秒以上のクエリ
# ログレベル
log_min_messages = warning
log_min_error_statement = error
# 接続ログ
log_connections = on
log_disconnections = on
# DDL ログ
log_statement = 'ddl' # none, ddl, mod, all
# チェックポイント
log_checkpoints = on
# ロック待ち
log_lock_waits = on
deadlock_timeout = 1s
# 一時ファイル
log_temp_files = 0 # すべての一時ファイル使用をログ
# autovacuum
log_autovacuum_min_duration = 0 # すべてログ
20.6 Prometheus + pg_exporter による監視
# prometheus.yml
scrape_configs:
- job_name: 'postgres'
static_configs:
- targets: ['postgres-server:9187']
# pg_exporter の設定
# DATA_SOURCE_NAME=postgresql://monitor:password@localhost:5432/postgres?sslmode=disable
# postgres_exporter --web.listen-address=:9187
-- pg_exporter 用の監視ユーザー
CREATE ROLE monitor WITH LOGIN PASSWORD 'monitor_pass';
GRANT pg_monitor TO monitor;
-- pg_monitor ロールは pg_stat_statements や pg_stat_activity の読み取りを許可する
主要なメトリクス:
| メトリクス | 意味 | アラートしきい値 |
|---|---|---|
| pg_up | PostgreSQL が稼働中か | 0 |
| pg_stat_activity_count | 接続数 | max_connections の 80% |
| pg_stat_database_xact_commit | コミット数/秒 | 通常値の 2σ 逸脱 |
| pg_stat_bgwriter_buffers_backend | バックエンド書き出し | 継続的に高い場合 |
| pg_replication_lag | レプリケーション遅延 | > 30秒 |
| pg_stat_database_deadlocks | デッドロック数 | > 0 |
| pg_database_size_bytes | データベースサイズ | ディスクの 80% |
20.7 pgAdmin
pgAdmin は PostgreSQL の公式 GUI 管理ツールである。Web ベースのインターフェースを提供し、以下の機能を備える。
- サーバー管理・監視ダッシュボード
- SQL エディタ（IntelliSense 対応）
- クエリの実行計画の可視化
- バックアップ / リストアウィザード
- ユーザー / ロール管理
- テーブル設計 GUI
# Docker での pgAdmin 起動
docker run -d \
--name pgadmin \
-p 8080:80 \
-e PGADMIN_DEFAULT_EMAIL=admin@example.com \
-e PGADMIN_DEFAULT_PASSWORD=admin \
dpage/pgadmin4
21. クラウド展開
21.1 マネージド PostgreSQL サービスの比較
| 機能 | Amazon RDS | Aurora PostgreSQL | Cloud SQL | Azure Database |
|---|---|---|---|---|
| 最大ストレージ | 64 TB | 128 TB | 64 TB | 16 TB |
| 最大メモリ | 1 TB+ | 1 TB+ | 624 GB | 1 TB+ |
| レプリカ数 | 5 (読み取り) | 15 (読み取り) | 10 | 5 |
| 自動フェイルオーバー | Multi-AZ | 組み込み | HA | Zone Redundant |
| バックアップ保持 | 35 日 | 35 日 | 365 日 | 35 日 |
| 論理レプリケーション | 対応 | 対応 | 対応 | 対応 |
| pgvector | 対応 | 対応 | 対応 | 対応 |
| メジャーアップグレード | 対応 | 対応 | 対応 | 対応 |
21.2 Amazon RDS for PostgreSQL
# AWS CLI でインスタンス作成
aws rds create-db-instance \
--db-instance-identifier mydb-prod \
--db-instance-class db.r6g.xlarge \
--engine postgres \
--engine-version 17.2 \
--master-username admin \
--master-user-password 'SecureP@ss123' \
--allocated-storage 100 \
--max-allocated-storage 500 \
--storage-type gp3 \
--storage-throughput 500 \
--iops 3000 \
--multi-az \
--vpc-security-group-ids sg-12345678 \
--db-subnet-group-name my-subnet-group \
--backup-retention-period 7 \
--preferred-backup-window 03:00-04:00 \
--preferred-maintenance-window Sun:04:00-Sun:05:00 \
--auto-minor-version-upgrade \
--deletion-protection \
--performance-insights-enabled \
--monitoring-interval 60 \
--enable-cloudwatch-logs-exports postgresql upgrade
-- RDS 固有の設定（パラメータグループ）
-- 主要パラメータ:
-- shared_buffers = {DBInstanceClassMemory/32768} -- 自動計算
-- effective_cache_size = {DBInstanceClassMemory*3/32768}
-- work_mem = 65536 -- 64MB
-- maintenance_work_mem = 2097152 -- 2GB
-- random_page_cost = 1.1
-- max_connections = LEAST({DBInstanceClassMemory/9531392}, 5000)
-- RDS での拡張機能
SELECT * FROM pg_available_extensions WHERE name IN (
'pg_stat_statements', 'pgvector', 'postgis',
'pg_trgm', 'pgcrypto', 'pg_hint_plan', 'pgaudit'
);
-- Performance Insights クエリ
-- AWS コンソールまたは CLI で確認
-- aws pi get-resource-metrics ...
21.3 Amazon Aurora PostgreSQL
Aurora は PostgreSQL 互換のクラウドネイティブデータベースで、独自のストレージレイヤーを持つ。
Aurora のアーキテクチャ:
┌─────────┐ ┌─────────┐ ┌─────────┐
│ Writer │ │ Reader │ │ Reader │
│Instance │ │Instance │ │Instance │
└────┬────┘ └────┬────┘ └────┬────┘
│ │ │
┌────▼────────────▼────────────▼────┐
│ Aurora Storage (共有) │
│ 6 copies across 3 AZs │
│ 自動スケーリング (10GB 単位) │
│ 自動暗号化 │
└───────────────────────────────────┘
# Aurora クラスターの作成
aws rds create-db-cluster \
--db-cluster-identifier aurora-pg-prod \
--engine aurora-postgresql \
--engine-version 17.2 \
--master-username admin \
--master-user-password 'SecureP@ss123' \
--vpc-security-group-ids sg-12345678 \
--db-subnet-group-name my-subnet-group \
--storage-encrypted \
--backup-retention-period 7 \
--deletion-protection \
--serverless-v2-scaling-configuration MinCapacity=0.5,MaxCapacity=16
# Writer インスタンスの作成
aws rds create-db-instance \
--db-instance-identifier aurora-pg-writer \
--db-cluster-identifier aurora-pg-prod \
--db-instance-class db.serverless \
--engine aurora-postgresql
# Reader インスタンスの作成
aws rds create-db-instance \
--db-instance-identifier aurora-pg-reader \
--db-cluster-identifier aurora-pg-prod \
--db-instance-class db.serverless \
--engine aurora-postgresql
21.4 Google Cloud SQL for PostgreSQL
# gcloud CLI でインスタンス作成
gcloud sql instances create mydb-prod \
--database-version=POSTGRES_17 \
--tier=db-custom-4-16384 \
--region=asia-northeast1 \
--availability-type=REGIONAL \
--storage-type=SSD \
--storage-size=100GB \
--storage-auto-increase \
--backup-start-time=03:00 \
--retained-backups-count=7 \
--maintenance-window-day=SUN \
--maintenance-window-hour=4 \
--database-flags=\
max_connections=200,\
shared_buffers=4096MB,\
work_mem=64MB,\
log_min_duration_statement=1000
# 読み取りレプリカの作成
gcloud sql instances create mydb-replica1 \
--master-instance-name=mydb-prod \
--region=asia-northeast1 \
--tier=db-custom-4-16384
21.5 Azure Database for PostgreSQL
# Azure CLI でフレキシブルサーバーを作成
az postgres flexible-server create \
--resource-group myResourceGroup \
--name mydb-prod \
--location japaneast \
--admin-user admin \
--admin-password 'SecureP@ss123' \
--sku-name Standard_D4s_v3 \
--tier GeneralPurpose \
--storage-size 128 \
--version 17 \
--high-availability ZoneRedundant \
--backup-retention 7 \
--geo-redundant-backup Enabled
# サーバーパラメータの設定
az postgres flexible-server parameter set \
--resource-group myResourceGroup \
--server-name mydb-prod \
--name shared_buffers \
--value 4194304
# 読み取りレプリカの作成
az postgres flexible-server replica create \
--resource-group myResourceGroup \
--replica-name mydb-replica1 \
--source-server mydb-prod \
--location japaneast
21.6 クラウド移行の考慮事項
- 拡張機能の互換性 — マネージドサービスでは使用可能な拡張機能が制限される
- パラメータの制限 — 一部のパラメータは変更不可（
shared_preload_librariesなど） - ストレージの特性 — ネットワークストレージのレイテンシを考慮
- バックアップ — マネージドバックアップと自前バックアップの併用を検討
- ネットワーク — VPC / VNet 内でのセキュアな接続
- コスト — リザーブドインスタンスの活用でコスト削減
22. バージョンアップ戦略
22.1 マイナーバージョンアップ
マイナーバージョンアップ（例: 16.1 → 16.4）は、バグ修正とセキュリティパッチのみ含まれ、バイナリ互換性がある。
# パッケージマネージャーでの更新
sudo apt update
sudo apt install postgresql-16
# サーバーの再起動
sudo systemctl restart postgresql@16-main
# バージョンの確認
psql -c "SELECT version();"
22.2 メジャーバージョンアップの方法
メジャーバージョンアップ（例: 16 → 17）には主に 3 つの方法がある。
| 方法 | ダウンタイム | 複雑さ | データ量の影響 |
|---|---|---|---|
| pg_dump / pg_restore | 長い | 低 | 大きい |
| pg_upgrade | 短い | 中 | 小さい |
| 論理レプリケーション | ほぼゼロ | 高 | 小さい |
22.3 pg_upgrade によるメジャーアップグレード
# 1. 新バージョンのインストール
sudo apt install postgresql-17
# 2. 新クラスタの初期化（パッケージマネージャーが自動実行する場合あり）
sudo pg_createcluster 17 main
# 3. 両方のサーバーを停止
sudo systemctl stop postgresql@16-main
sudo systemctl stop postgresql@17-main
# 4. 互換性チェック（--check オプション）
sudo -u postgres /usr/lib/postgresql/17/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/16/main \
--new-datadir=/var/lib/postgresql/17/main \
--old-bindir=/usr/lib/postgresql/16/bin \
--new-bindir=/usr/lib/postgresql/17/bin \
--check
# 5. アップグレードの実行
sudo -u postgres /usr/lib/postgresql/17/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/16/main \
--new-datadir=/var/lib/postgresql/17/main \
--old-bindir=/usr/lib/postgresql/16/bin \
--new-bindir=/usr/lib/postgresql/17/bin \
--link \ # ハードリンクを使用（高速、ただしロールバック不可）
--jobs=4 # 並列処理数
# 6. 新バージョンで起動
sudo systemctl start postgresql@17-main
# 7. 統計情報の収集（pg_upgrade が生成するスクリプト）
/var/lib/postgresql/analyze_new_cluster.sh
# 8. 古いクラスタの削除（確認後）
/var/lib/postgresql/delete_old_cluster.sh
22.4 論理レプリケーションによるマイグレーション
ダウンタイムを最小化する方法として、論理レプリケーションを使用したマイグレーションが有効である。
-- ===== ソース（旧バージョン PG16）の設定 =====
-- wal_level = logical が必要
-- パブリケーションの作成
CREATE PUBLICATION migration_pub FOR ALL TABLES;
-- ===== ターゲット（新バージョン PG17）の設定 =====
-- 1. スキーマのみダンプ・リストア
-- pg_dump --schema-only -h old-server mydb | psql -h new-server mydb
-- 2. サブスクリプションの作成（初期データ同期あり）
CREATE SUBSCRIPTION migration_sub
CONNECTION 'host=old-server port=5432 dbname=mydb user=repl_user password=secret'
PUBLICATION migration_pub
WITH (copy_data = true);
-- 3. 同期完了を待つ
SELECT * FROM pg_stat_subscription;
-- 全テーブルの srsubstate が 'r' (ready) になるまで待つ
-- 4. シーケンスの同期（手動で必要）
-- ソース側でシーケンスの現在値を取得
SELECT schemaname, sequencename, last_value
FROM pg_sequences WHERE schemaname = 'public';
-- ターゲット側で設定
SELECT setval('orders_id_seq', 12345, true);
-- 5. アプリケーションの切り替え
-- a. ソース側への書き込みを停止
-- b. レプリケーション遅延がゼロになるまで待つ
-- c. アプリケーションの接続先を新サーバーに変更
-- 6. クリーンアップ
-- ターゲット側
DROP SUBSCRIPTION migration_sub;
-- ソース側
DROP PUBLICATION migration_pub;
22.5 アップグレード前のチェックリスト
# 1. 拡張機能の互換性を確認
psql -c "SELECT extname, extversion FROM pg_extension ORDER BY extname;"
# 2. 非推奨機能の使用を確認
# リリースノートで非互換変更を確認
# 3. アプリケーションのテスト
# - テストデータベースを新バージョンに移行してテスト
# - パフォーマンステストの実施
# 4. バックアップの取得
pg_basebackup -h localhost -D /backup/before_upgrade -Fp -Xs -P
# 5. ディスク空き容量の確認
# pg_upgrade --link は追加容量が少なくて済む
# pg_upgrade without --link はデータ量の 2 倍の空き容量が必要
# 6. レプリケーション環境の計画
# pg_upgrade は物理レプリケーションのスタンバイにも適用が必要
# rsync を使ってスタンバイを効率的にアップグレードできる
22.6 pg_upgrade 後のスタンバイのアップグレード
# rsync を使ったスタンバイの高速アップグレード
rsync --archive --delete --hard-links --size-only --no-inc-recursive \
/var/lib/postgresql/17/main/ \
standby-server:/var/lib/postgresql/17/main/
# スタンバイ側で standby.signal を作成し、primary_conninfo を設定して起動
22.7 ロールバック計画
# pg_upgrade --link を使わなかった場合:
# 1. 新バージョンのサーバーを停止
sudo systemctl stop postgresql@17-main
# 2. 旧バージョンのサーバーを起動
sudo systemctl start postgresql@16-main
# pg_upgrade --link を使った場合:
# ロールバックにはバックアップからのリストアが必要
pg_basebackup を事前に取得しておくこと
23. トラブルシューティング
23.1 ロック競合 (Lock Contention)
-- ロック待ちの検出
SELECT
blocked.pid AS blocked_pid,
blocked.usename AS blocked_user,
blocked.query AS blocked_query,
now() - blocked.query_start AS blocked_duration,
blocking.pid AS blocking_pid,
blocking.usename AS blocking_user,
blocking.query AS blocking_query,
now() - blocking.query_start AS blocking_duration
FROM pg_stat_activity blocked
JOIN pg_locks bl ON blocked.pid = bl.pid AND NOT bl.granted
JOIN pg_locks kl ON bl.locktype = kl.locktype
AND bl.database IS NOT DISTINCT FROM kl.database
AND bl.relation IS NOT DISTINCT FROM kl.relation
AND bl.page IS NOT DISTINCT FROM kl.page
AND bl.tuple IS NOT DISTINCT FROM kl.tuple
AND bl.virtualxid IS NOT DISTINCT FROM kl.virtualxid
AND bl.transactionid IS NOT DISTINCT FROM kl.transactionid
AND bl.classid IS NOT DISTINCT FROM kl.classid
AND bl.objid IS NOT DISTINCT FROM kl.objid
AND bl.objsubid IS NOT DISTINCT FROM kl.objsubid
AND bl.pid != kl.pid
AND kl.granted
JOIN pg_stat_activity blocking ON kl.pid = blocking.pid;
-- ロックツリー（再帰クエリ）
WITH RECURSIVE lock_tree AS (
SELECT pid, array[pid] AS pids, pid AS root_pid, 0 AS depth
FROM pg_locks WHERE granted
UNION ALL
SELECT bl.pid, lt.pids || bl.pid, lt.root_pid, lt.depth + 1
FROM pg_locks bl
JOIN pg_locks kl ON bl.locktype = kl.locktype
AND bl.database IS NOT DISTINCT FROM kl.database
AND bl.relation IS NOT DISTINCT FROM kl.relation
AND bl.pid != kl.pid
AND NOT bl.granted AND kl.granted
JOIN lock_tree lt ON kl.pid = lt.pid
WHERE NOT bl.pid = ANY(lt.pids)
)
SELECT depth, pid,
(SELECT query FROM pg_stat_activity WHERE pid = lock_tree.pid) AS query
FROM lock_tree
ORDER BY root_pid, depth;
-- ブロッキングクエリの強制終了
SELECT pg_terminate_backend(blocking_pid);
-- デッドロックの確認
-- ログに以下のようなメッセージが出力される:
-- ERROR: deadlock detected
-- DETAIL: Process 1234 waits for ShareLock on transaction 5678;
-- blocked by process 5679.
-- HINT: See server log for query details.
-- デッドロックタイムアウトの設定
SET deadlock_timeout = '1s';
23.2 テーブル / インデックスの肥大化 (Bloat)
-- テーブルの肥大化を推定するクエリ
SELECT
schemaname || '.' || tablename AS table_name,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
n_live_tup,
n_dead_tup,
CASE WHEN n_live_tup > 0
THEN round(100.0 * n_dead_tup / n_live_tup, 1)
ELSE 0
END AS dead_tup_pct,
last_autovacuum,
last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;
-- 対策: VACUUM (通常)
VACUUM VERBOSE orders;
-- 対策: VACUUM FULL (テーブルを完全に書き直す — 排他ロック)
VACUUM FULL orders; -- 注意: テーブル全体をロック
-- 対策: pg_repack (ロックなしで再編成)
-- pg_repack -d mydb -t orders --no-superuser-check
-- インデックスの肥大化
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('idx_orders_customer_id');
-- avg_leaf_density が低い場合 (<50%) → REINDEX が必要
-- インデックスの再構築（ロックなし）
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;
23.3 スロークエリの診断
-- 現在実行中の遅いクエリ
SELECT pid, usename,
now() - query_start AS duration,
state, wait_event_type, wait_event,
query
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '10 seconds'
ORDER BY duration DESC;
-- pg_stat_statements で過去のスロークエリを特定
SELECT
left(query, 80) AS query,
calls,
round(total_exec_time::numeric / 1000, 2) AS total_sec,
round(mean_exec_time::numeric / 1000, 2) AS avg_sec,
round(max_exec_time::numeric / 1000, 2) AS max_sec,
rows
FROM pg_stat_statements
WHERE mean_exec_time > 1000 -- 平均 1 秒以上
ORDER BY total_exec_time DESC
LIMIT 20;
-- 実行計画の分析
EXPLAIN (ANALYZE, BUFFERS, TIMING, VERBOSE)
SELECT ... ; -- 問題のクエリ
-- チェックポイント:
-- 1. Seq Scan が大きなテーブルに対して実行されていないか
-- 2. estimated rows と actual rows に大きな乖離はないか
-- 3. Sort Method が external merge (ディスクソート) になっていないか
-- 4. Hash Batches が 1 より大きくないか
-- 5. Buffers: shared read が多くないか (キャッシュミス)
23.4 接続枯渇 (Connection Exhaustion)
-- 現在の接続数を確認
SELECT count(*) AS total,
count(*) FILTER (WHERE state = 'active') AS active,
count(*) FILTER (WHERE state = 'idle') AS idle,
count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx,
count(*) FILTER (WHERE state = 'idle in transaction (aborted)') AS idle_in_tx_aborted
FROM pg_stat_activity
WHERE backend_type = 'client backend';
-- max_connections と比較
SHOW max_connections;
-- 接続元ごとの集計
SELECT client_addr, usename, datname,
count(*) AS connections
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY client_addr, usename, datname
ORDER BY connections DESC;
-- 長時間 idle な接続を強制切断
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND now() - state_change > interval '30 minutes'
AND pid != pg_backend_pid();
-- 対策: アイドルセッションのタイムアウト (PostgreSQL 14+)
SET idle_session_timeout = '10min';
-- または idle in transaction のタイムアウト
SET idle_in_transaction_session_timeout = '5min';
23.5 OOM (Out of Memory)
OOM の主な原因:
1. work_mem が大きすぎる × 同時接続数が多い
2. shared_buffers が大きすぎて OS のページキャッシュが不足
3. maintenance_work_mem が大きすぎて VACUUM / CREATE INDEX でメモリ枯渇
4. メモリリークのある拡張機能
# OOM Killer のログを確認
dmesg | grep -i oom
journalctl -k | grep -i oom
# PostgreSQL プロセスのメモリ使用量を確認
ps aux --sort=-%mem | grep postgres | head -20
# /proc/pid/status でプロセスのメモリ詳細
cat /proc/$(head -1 $PGDATA/postmaster.pid)/status | grep -i vm
-- メモリ消費を抑える設定の調整
-- 1. work_mem を下げる
SET work_mem = '32MB';
-- 2. hash_mem_multiplier を下げる (PostgreSQL 13+)
SET hash_mem_multiplier = 1.0; -- デフォルト 2.0
-- 3. 同時接続数を制限（pgBouncer 推奨）
-- max_connections を減らす
-- 4. Linux OOM スコアの調整
-- echo -1000 > /proc/$(head -1 $PGDATA/postmaster.pid)/oom_score_adj
23.6 WAL の肥大化
-- WAL の使用状況
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')) AS total_wal;
-- レプリケーションスロットが WAL を保持していないか確認
SELECT slot_name, active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;
-- 不要なスロットの削除
SELECT pg_drop_replication_slot('inactive_slot');
-- WAL サイズの制限
-- max_slot_wal_keep_size = 10GB -- PG13+
23.7 高負荷時のクイックアクション
-- 1. 現状把握
SELECT state, count(*) FROM pg_stat_activity
WHERE backend_type = 'client backend' GROUP BY state;
-- 2. 長時間クエリのキャンセル
SELECT pg_cancel_backend(pid) FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 minutes';
-- 3. idle in transaction の強制終了
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle in transaction'
AND now() - state_change > interval '10 minutes';
-- 4. autovacuum が重い場合の一時抑制
-- ALTER TABLE large_table SET (autovacuum_vacuum_cost_delay = 20);
-- 5. チェックポイントの強制実行
CHECKPOINT;
24. ベストプラクティスとまとめ
24.1 設計のベストプラクティス
データモデリング
- 適切なデータ型を選択する — VARCHAR(255) の安易な使用を避け、INT/BIGINT、BOOLEAN、TIMESTAMPTZ、NUMERIC 等を適切に使い分ける
- 正規化を基本とする — 必要に応じてパフォーマンスのために非正規化する
- サロゲートキーと自然キーの使い分け — BIGSERIAL / UUID の選択を慎重に行う
- NULL の意味を明確にする — NOT NULL 制約を積極的に使用する
- TIMESTAMPTZ を使用する — TIMESTAMP WITHOUT TIME ZONE よりも推奨
- ENUM 型よりも参照テーブル — 変更が容易になる
-- 良いテーブル設計の例
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
order_number TEXT NOT NULL UNIQUE,
customer_id BIGINT NOT NULL REFERENCES customers(id),
status TEXT NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
total_amount NUMERIC(12, 2) NOT NULL CHECK (total_amount >= 0),
currency CHAR(3) NOT NULL DEFAULT 'JPY',
notes TEXT,
metadata JSONB DEFAULT '{}',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- 更新日時の自動更新
CREATE TRIGGER trg_orders_updated
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_modified_at();
-- 部分インデックスで効率化
CREATE INDEX idx_orders_active ON orders (created_at)
WHERE status IN ('pending', 'processing');
インデックス設計
- WHERE 句と JOIN 条件で頻繁に使用するカラムにインデックスを作成
- 複合インデックスの列順序は選択率の高い順
- 部分インデックスで不要なデータを除外
- カバリングインデックスで Index Only Scan を活用
- BRIN は時系列データの大規模テーブルに有効
- 不要なインデックスは削除する — インデックスは書き込み性能に影響する
24.2 運用のベストプラクティス
接続管理
推奨:
- pgBouncer を使用して接続プーリングを導入
- max_connections は実際に必要な数の 1.5 倍程度に設定
- idle_in_transaction_session_timeout を設定
- statement_timeout をアプリケーション側で設定
バックアップ
推奨:
- pgBackRest または Barman を導入
- フルバックアップ (週次) + 差分バックアップ (日次) + WAL アーカイブ (継続)
- バックアップリストアのテストを月次で実施
- バックアップ先を本番とは異なるリージョン/データセンターに保管
監視
推奨:
- pg_stat_statements を有効化
- Prometheus + Grafana + pg_exporter で可視化
- 以下のメトリクスにアラートを設定:
- レプリケーション遅延 > 30 秒
- キャッシュヒット率 < 99%
- デッドロック検出
- ディスク使用量 > 80%
- 接続数 > max_connections の 80%
- 長時間クエリ > 5 分
- トランザクション ID 年齢 > 1 億
autovacuum
推奨:
- autovacuum は絶対に無効化しない
- 大規模テーブルには個別の autovacuum 設定を適用
- autovacuum_vacuum_scale_factor は大規模テーブルで 0.01〜0.05 に下げる
- log_autovacuum_min_duration = 0 で全ログを収集
24.3 セキュリティのベストプラクティス
1. 最小権限の原則
- PUBLIC スキーマのデフォルト権限を削除
- アプリケーション用のロールには必要最小限の権限のみ付与
- スーパーユーザーの使用を制限
2. 認証
- scram-sha-256 を使用（md5 は非推奨）
- SSL/TLS を必須にする
- pg_hba.conf でネットワーク範囲を最小限に制限
3. データ保護
- Row Level Security (RLS) でマルチテナントの行レベル分離
- pgAudit で監査ログを収集
- pgcrypto で機密データを暗号化
- ディスクレベルの暗号化を有効化
-- セキュリティ初期設定の例
-- PUBLIC スキーマのデフォルト権限を削除 (PostgreSQL 15+ ではデフォルト)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydb FROM PUBLIC;
-- アプリケーション用ロールの作成
CREATE ROLE app_readonly NOLOGIN;
GRANT CONNECT ON DATABASE mydb TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;
CREATE ROLE app_readwrite NOLOGIN;
GRANT CONNECT ON DATABASE mydb TO app_readwrite;
GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;
-- ログインユーザーの作成
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';
GRANT app_readwrite TO app_user;
ALTER ROLE app_user SET statement_timeout = '30s';
24.4 パフォーマンスチェックリスト
-- 1. キャッシュヒット率
SELECT round(100.0 * sum(blks_hit) / (sum(blks_hit) + sum(blks_read) + 1), 2) AS cache_hit_ratio
FROM pg_stat_database;
-- 目標: 99% 以上
-- 2. インデックス使用率
SELECT round(100.0 * sum(idx_scan) / (sum(idx_scan) + sum(seq_scan) + 1), 2) AS idx_scan_ratio
FROM pg_stat_user_tables;
-- 目標: 95% 以上（OLTP の場合）
-- 3. デッドタプル率
SELECT schemaname || '.' || relname AS table_name,
n_dead_tup,
round(100.0 * n_dead_tup / (n_live_tup + n_dead_tup + 1), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_ratio DESC;
-- 目標: 10% 以下
-- 4. トランザクション ID 年齢
SELECT datname, age(datfrozenxid) AS xid_age,
round(100.0 * age(datfrozenxid) / 2000000000, 2) AS pct_to_wraparound
FROM pg_database
ORDER BY xid_age DESC;
-- 目標: 50% 以下
-- 5. レプリケーション遅延
SELECT application_name, replay_lag
FROM pg_stat_replication;
-- 目標: 数秒以内
24.5 まとめ
PostgreSQL は、豊富な機能と高い拡張性を備えた世界最先端のオープンソースデータベースである。本ガイドで解説した内容を以下にまとめる。
- アーキテクチャ — プロセスベースのクライアント/サーバーモデルで、共有メモリを介したデータ共有を行う
- MVCC — 読み取りが書き込みをブロックしない高い同時実行性を実現し、VACUUM によるメンテナンスが必要
- WAL — 変更を先行書き込みすることでデータの耐久性を保証し、PITR やレプリケーションの基盤となる
- インデックス — B-tree、GIN、GiST、BRIN など用途に応じた多様なインデックスを使い分ける
- レプリケーション — 物理・論理の両方のレプリケーションをサポートし、HA / DR / スケールアウトに対応
- 拡張性 — PostGIS、pgvector、TimescaleDB など豊富なエコシステムで機能を拡張可能
- セキュリティ — RLS、SSL/TLS、SCRAM 認証、pgAudit により堅牢なセキュリティを実現
- 運用 — pg_stat_statements、auto_explain、Prometheus 連携により効率的な監視と運用が可能
PostgreSQL は毎年新バージョンがリリースされ、継続的に進化している。最新の機能と改善を活用しつつ、本ガイドで紹介したベストプラクティスに従って運用することで、安定的かつ高性能なデータベース環境を構築・維持することができる。
本記事は PostgreSQL 17 を基準として執筆されている。バージョンによって利用可能な機能や設定パラメータが異なる場合がある。最新の情報は PostgreSQL 公式ドキュメント を参照されたい。