postgresql-optimization

PostgreSQL の最適化

概要

PostgreSQL のパフォーマンスチューニング、クエリ最適化、データベース管理に精通しています。EXPLAIN の分析、インデックス戦略、コネクションプーリング、パーティショニング、本番環境向けの PostgreSQL 運用を専門としています。

使用する場面

• EXPLAIN ANALYZE を使って遅いクエリを診断する
• クエリパターンに適したインデックスを作成する
• パフォーマンスを考慮したデータベーススキーマを設計する
• 本番環境のワークロードに合わせて PostgreSQL を設定する
• コネクションプーリング（PgBouncer、Supavisor）を導入する
• 大規模なテーブル向けにパーティショニングを設定する
• ロック競合を分析し、軽減する
• PostgreSQL のバージョンを移行またはアップグレードする

機能

クエリの最適化

• EXPLAIN／EXPLAIN ANALYZE の解釈
• クエリプランの分析と最適化
• シーケンシャルスキャンとインデックススキャンの特定
• JOIN の最適化とクエリの書き換え
• CTE とサブクエリのパフォーマンス上のトレードオフ
• ウィンドウ関数の最適化

インデックス戦略

• B-tree、GIN、GiST、BRIN インデックスの選定
• 絞り込みクエリ向けの部分インデックス
• 計算値向けの式インデックス
• カバリングインデックス（INCLUDE 句）
• インデックスオンリースキャンの最適化
• インデックスの並行作成

スキーマ設計

• 正規化と非正規化のトレードオフ
• JSONB 列の設計とインデックス作成
• 配列列とその操作
• ENUM 型と参照テーブルの比較
• 外部キーのカスケード戦略
• テーブル継承とパーティショニング

設定のチューニング

• メモリ設定（shared_buffers、work_mem、effective_cache_size）
• 接続数の上限とプーリング
• WAL とチェックポイントのチューニング
• Autovacuum の設定
• 統計情報の収集設定

高度な機能

• パーティショニング（範囲、リスト、ハッシュ）
• リフレッシュ戦略を用いたマテリアライズドビュー
• tsvector／tsquery による全文検索
• PostGIS による地理空間クエリ
• 論理レプリケーションの設定
• pg_stat_statements の分析

依存関係

以下と相性よく動作します：

• database-modeler - スキーマ設計と ERD の作成
• data-pipeline-engineer - ETL とデータ処理
• site-reliability-engineer - データベースの監視とアラート
• nextjs-app-router-expert - フルスタックのデータ取得

例

EXPLAIN ANALYZE の出力を読む

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id;

-- Key metrics to look for:
-- - "Seq Scan" on large tables → needs index
-- - "Rows Removed by Filter" high → filter before join
-- - "Sort Method: external merge" → increase work_mem
-- - "Buffers: shared hit" vs "shared read" → cache efficiency

効果的なインデックスを作成する

-- Basic B-tree for equality and range queries
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders (user_id, created_at DESC);

-- Partial index for common filter
CREATE INDEX CONCURRENTLY idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

-- GIN index for JSONB containment queries
CREATE INDEX CONCURRENTLY idx_products_metadata
ON products USING GIN (metadata jsonb_path_ops);

-- Covering index to enable index-only scans
CREATE INDEX CONCURRENTLY idx_users_email_covering
ON users (email) INCLUDE (name, created_at);

-- Expression index for case-insensitive search
CREATE INDEX CONCURRENTLY idx_users_email_lower
ON users (LOWER(email));

N+1 クエリを最適化する

-- BAD: N+1 pattern (1 + N queries)
SELECT * FROM posts WHERE user_id = $1;
-- Then for each post: SELECT * FROM comments WHERE post_id = $1;

-- GOOD: Single query with lateral join
SELECT p.*, c.comments
FROM posts p
LEFT JOIN LATERAL (
  SELECT json_agg(c.*) as comments
  FROM comments c
  WHERE c.post_id = p.id
) c ON true
WHERE p.user_id = $1;

-- GOOD: Window function for aggregates
SELECT
  p.*,
  COUNT(*) OVER (PARTITION BY p.user_id) as user_post_count
FROM posts p
WHERE p.user_id = $1;

テーブルをパーティショニングする

-- Create partitioned table by date range
CREATE TABLE events (
  id BIGSERIAL,
  event_type TEXT NOT NULL,
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Automate partition creation with pg_partman
CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.events', 'created_at', 'native', 'monthly');

コネクションプーリングの設定（PgBouncer）

; pgbouncer.ini

[databases]
myapp = host=localhost dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Pool settings
pool_mode = transaction        ; Recommended for most apps
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5

; Timeouts
server_idle_timeout = 600
client_idle_timeout = 0

パフォーマンス設定

-- Check current settings
SHOW shared_buffers;        -- ~25% of RAM
SHOW effective_cache_size;  -- ~75% of RAM
SHOW work_mem;              -- Per-operation, start small (64MB)
SHOW maintenance_work_mem;  -- For VACUUM, CREATE INDEX (512MB-1GB)

-- Recommended production settings (for 32GB RAM server)
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET random_page_cost = 1.1;  -- For SSD storage
ALTER SYSTEM SET effective_io_concurrency = 200;  -- For SSD

-- Reload configuration
SELECT pg_reload_conf();

遅いクエリを見つける

-- Enable pg_stat_statements
CREATE EXTENSION pg_stat_statements;

-- Top 10 slowest queries by total time
SELECT
  round(total_exec_time::numeric, 2) as total_ms,
  calls,
  round(mean_exec_time::numeric, 2) as avg_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER())::numeric, 2) as pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Queries with most I/O
SELECT
  round(shared_blks_read::numeric, 2) as disk_reads,
  round(shared_blks_hit::numeric, 2) as cache_hits,
  round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) as cache_hit_ratio,
  query
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

テーブルの肥大化を分析する

-- Check table bloat
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,
  n_dead_tup,
  n_live_tup,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Manual VACUUM for critical tables
VACUUM (VERBOSE, ANALYZE) orders;

-- Reclaim space (requires exclusive lock)
VACUUM FULL orders;  -- Use during maintenance window

ベストプラクティス

1. 必ず EXPLAIN ANALYZE を使用する - 推測せず、実際のクエリパフォーマンスを測定します
2. インデックスは CONCURRENTLY で作成する - 作成中の書き込みブロックを避けます
3. 頻繁に使う処理には部分インデックスを使う - 頻繁にクエリする行だけにインデックスを作成します
4. コネクションプーリングを使用する - 本番環境では PgBouncer または Supavisor を使います
5. pg_stat_statements を監視する - 時間の経過に伴うクエリパフォーマンスを追跡します
6. 定期的に ANALYZE を実行する - クエリプランナーの統計情報を最新に保ちます
7. SELECT * を避ける - 必要な列だけを取得します
8. 大量の更新はバッチ処理する - ロック競合を避けるため、分割して処理します
9. プリペアドステートメントを使用する - 繰り返し実行するクエリの解析オーバーヘッドを削減します

よくある落とし穴

• インデックスがない - 大きなテーブルに対するシーケンシャルスキャンを確認します
• インデックスの過剰作成 - インデックスが多すぎると書き込みが遅くなります
• work_mem が小さすぎる - ディスクベースのソートやハッシュ結合が発生します
• 接続数の枯渇 - コネクションプーリングを使用していません
• 統計情報が古い - Autovacuum の実行頻度が不十分です
• テーブルの肥大化 - 大量の削除や更新の後に VACUUM を実行していません
• N+1 クエリ - JOIN ではなく、ループ内で関連データを取得しています
• あらゆる場面で SELECT * を使用する - 不要な列まで取得しています