postgres-ops

PostgreSQL の運用

スキーマ設計から本番環境の運用までを網羅する、PostgreSQL の包括的なスキルです。

クイック接続

# Standard connection
psql "postgresql://user:pass@localhost:5432/dbname"

# With SSL
psql "postgresql://user:pass@host:5432/dbname?sslmode=require"

# Environment variables (libpq)
export PGHOST=localhost PGPORT=5432 PGDATABASE=mydb PGUSER=myuser PGPASSWORD=secret
psql

# Connection pooling (pgBouncer default)
psql "postgresql://user:pass@localhost:6432/dbname"

-- Check current connection
SELECT current_database(), current_user, inet_server_addr(), inet_server_port();

-- Active connections
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

インデックスの種類の選択

What query pattern are you optimizing?
│
├─ Equality (WHERE col = val)
│  └─ B-tree (default, almost always right)
│
├─ Range (WHERE col > val, ORDER BY, BETWEEN)
│  └─ B-tree
│
├─ Array/JSONB containment (@>, ?, ?|, ?&)
│  └─ GIN
│
├─ Full-text search (@@)
│  └─ GIN with tsvector
│
├─ Geometric/range overlap (&&, <->)
│  └─ GiST
│
├─ Pattern matching (LIKE '%text%', similarity)
│  └─ GIN with pg_trgm (gin_trgm_ops)
│
├─ Large table, few distinct values, append-only
│  └─ BRIN (tiny index, good for timestamps)
│
└─ Exact equality only, no range/sort needed
   └─ Hash (rare - B-tree usually better)

インデックスのクイックリファレンス

インデックス	最適な用途	サイズ	書き込みコスト
B-tree	等価検索、範囲検索、ソート	中	低
GIN	配列、JSONB、全文検索、トライグラム	大	高
GiST	ジオメトリ、範囲、全文検索	中	中
BRIN	相関のあるデータ（タイムスタンプ）	極小	非常に低
Hash	完全一致のみ	中	低

詳細：複合、部分、式、カバリングインデックスの戦略については、./references/indexing.md を読み込んでください。

EXPLAIN ANALYZE のワークフロー

-- Step 1: Run with ANALYZE and BUFFERS
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- Step 2: Read bottom-up. Find the slowest node.
-- Step 3: Check estimates vs actuals
--   actual rows=10000, rows=100  -> bad estimate, run ANALYZE
-- Step 4: Look for these red flags:

要注意項目	意味	対処法
大きなテーブルでの Seq Scan	使用可能なインデックスがありません	WHERE／JOIN に適合するインデックスを追加します
actual rows が estimated rows を大幅に上回る	統計情報が古くなっています	ANALYZE tablename を実行します
行数の多い Nested Loop	O(n*m) の結合です	結合条件を確認し、インデックスを追加します
external merge を伴う Sort	work_mem が小さすぎます	セッションの work_mem を増やします
Buffers: shared read が hit を大幅に上回る	キャッシュが空、またはテーブルが大きすぎます	shared_buffers を確認し、カバリングインデックスを追加します
Hash Batch > 1	ハッシュ結合がディスクに書き出されています	work_mem を増やします

詳細：実行計画ノードのリファレンスと最適化パターンについては、./references/query-tuning.md を読み込んでください。

ワークロードプロファイル

設定	OLTP	OLAP	備考
shared_buffers	RAM の 25%	RAM の 25%	同じ基本値
work_mem	4-16 MB	256 MB-1 GB	OLAP では大きなソートが必要です
effective_cache_size	RAM の 75%	RAM の 75%	プランナーへのヒント
random_page_cost	1.1（SSD）	1.1（SSD）	SSD では値を下げます
max_parallel_workers_per_gather	2	4-8	OLAP のほうが恩恵を受けます
checkpoint_completion_target	0.9	0.9	チェックポイントの I/O を分散します
wal_buffers	64 MB	64 MB	自動設定には -1
maintenance_work_mem	512 MB	1-2 GB	VACUUM、CREATE INDEX 用

詳細：postgresql.conf の全項目の解説と拡張機能の設定については、./references/config-tuning.md を読み込んでください。

一般的な操作

バックアップと復元

# Logical backup (single database)
pg_dump -Fc dbname > backup.dump

# Restore
pg_restore -d dbname backup.dump

# Parallel backup (faster for large DBs)
pg_dump -Fc -j4 dbname > backup.dump

# Base backup for PITR
pg_basebackup -D /backup/base -Ft -Xs -P

バキュームとメンテナンス

-- Manual vacuum (reclaim space, update stats)
VACUUM (VERBOSE, ANALYZE) tablename;

-- Full vacuum (rewrites table, exclusive lock)
VACUUM FULL tablename;  -- CAUTION: locks table

-- Reindex without downtime
REINDEX INDEX CONCURRENTLY idx_name;

-- Update statistics only
ANALYZE tablename;

主要なメトリクスの監視

-- Slow queries (requires pg_stat_statements)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

-- Table bloat indicator
SELECT schemaname, relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 1) AS dead_pct
FROM pg_stat_user_tables WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Lock contention
SELECT pid, relation::regclass, mode, granted, query
FROM pg_locks JOIN pg_stat_activity USING (pid)
WHERE NOT granted;

-- Cache hit ratio (should be > 99%)
SELECT sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS ratio
FROM pg_statio_user_tables;

詳細：WAL アーカイブ、PITR、自動バキュームの調整、コネクションプーリングについては、./references/operations.md を読み込んでください。

データ型のクイックリファレンス

型	使用する場面	例
JSONB	半構造化データ、柔軟なスキーマ	'{"tags": ["a","b"]}'::jsonb
ARRAY	型が固定されたリスト	ARRAY['a','b','c']
tsrange	期間、スケジュール管理	'[2024-01-01, 2024-12-31)'::tsrange
tsvector	全文検索	to_tsvector('english', body)
uuid	分散 ID	gen_random_uuid()
inet/cidr	IP アドレス、ネットワーク	'192.168.1.0/24'::cidr

詳細：正規化、制約、RLS、生成列、テーブル継承については、./references/schema-design.md を読み込んでください。

注意点とアンチパターン

間違い	問題点	修正方法
本番環境での SELECT *	帯域幅を浪費し、カバリングインデックススキャンを妨げます	列を明示的に列挙します
インデックス付き列に関数を適用する（WHERE UPPER(email) = ...）	インデックスが使われなくなります	式インデックスを作成します：CREATE INDEX ... ON (UPPER(email))
NULL を含む NOT IN (subquery)	サブクエリに NULL が含まれると、行が1件も返されません	NOT EXISTS を使います
一括ロード後に ANALYZE を実行しない	プランナーが古い行数推定を使います	ANALYZE tablename を実行します
本番環境での VACUUM FULL	テーブル全体に排他ロックがかかります	定期的な VACUUM と pg_repack を使います
ORDER BY のない LIMIT	結果が非決定的になります	必ず ORDER BY と組み合わせます
大規模テーブルでのオフセットページネーション	行をスキャンして破棄します	キーセットページネーションを使います：WHERE id > last_id
インデックスが多すぎる	書き込みが遅くなり、領域を浪費します	pg_stat_user_indexes で監査します
共有接続プールが1つだけ	サービス間で競合が発生します	pgBouncer を使い、サービスごとにプールを用意します
default_transaction_isolation = serializable	シリアライゼーションエラーが過剰に発生します	read committed を維持し、必要な箇所で明示的に SERIALIZABLE を使います

行レベルセキュリティ（RLS）のクイックスタート

-- Enable RLS on table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Policy: users see only their own rows
CREATE POLICY user_isolation ON documents
    USING (owner_id = current_setting('app.current_user_id')::int);

-- Policy: admins see everything
CREATE POLICY admin_access ON documents
    USING (current_setting('app.role') = 'admin');

-- Set context per request (from app layer)
SET app.current_user_id = '42';
SET app.role = 'user';

全文検索のクイックスタート

-- Add search column
ALTER TABLE articles ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;

-- Index it
CREATE INDEX idx_articles_fts ON articles USING gin(search_vector);

-- Search with ranking
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database & optimization') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

LISTEN/NOTIFY

-- Publisher
NOTIFY order_events, '{"order_id": 123, "status": "shipped"}';

-- Subscriber (in psql or app)
LISTEN order_events;

-- Check for notifications (app code)
-- Python: conn.poll(); conn.notifies
-- Node: client.on('notification', callback)

参考ファイル

詳細なトピックについては、これらを読み込んでください。各ファイルは単独で完結しています。

参考資料	読み込むタイミング
./references/schema-design.md	テーブルの設計、型の選択、制約、RLS ポリシー、JSONB のモデリング
./references/indexing.md	インデックスの種類、複合／部分／式インデックスの選択、インデックスのメンテナンス
./references/query-tuning.md	EXPLAIN プランの読み方、pg_stat_statements、特定のクエリパターンの最適化
./references/operations.md	バックアップ／復元、WAL/PITR、VACUUM のチューニング、モニタリング、接続プーリング
./references/replication.md	ストリーミング／論理レプリケーション、フェイルオーバー、パーティショニング、FDW
./references/config-tuning.md	postgresql.conf の設定、OLTP/OLAP プロファイル、拡張機能のセットアップ

関連項目

• sql-ops - ベンダーに依存しない SQL パターン（CTE、ウィンドウ関数、JOIN）
• sqlite-ops - SQLite 固有のパターンと操作
• python-database-ops - SQLAlchemy ORM と非同期データベースのパターン