PostgreSQL パフォーマンスチューニング完全ガイド

数据库

2026年にPostgreSQLを選ぶ理由

PostgreSQLは2026年、世界で最も人気のあるオープンソースリレーショナルデータベースとして確固たる地位を築いています。強力な拡張性、豊富なデータ型、優れたSQL標準準拠性により、スタートアップから大企業までのデータベースの第一選択となっています。

PostgreSQLのコア利点

次元 PostgreSQL MySQL SQL Server Oracle
拡張性 極めて強い（拡張フレームワーク） 中程度 中程度 強い
データ型 極めて豊富（JSONB、GIS、配列） 中程度 豊富 豊富
SQL標準準拠 標準に最も近い 中程度 高い 高い
オープンソース 完全オープン（PostgreSQL License） GPL 部分的オープン クローズド
同時実行制御 MVCC（優秀） MVCC MVCC MVCC
パーティショニング ネイティブ宣言型パーティショニング ネイティブ ネイティブ ネイティブ

2026年のPostgreSQL主要進展

  1. PostgreSQL 18：パラレルクエリのさらなる強化、論理レプリケーションパフォーマンスの大幅向上
  2. インクリメンタルソート最適化：複数カラムソートシナリオで3-5倍のパフォーマンス向上
  3. JSONパス式の強化：より強力なJSONBクエリとインデックスサポート
  4. 非同期I/Oサポート：基盤I/Oアーキテクチャの改善、高同時実行スループットの大幅向上
  5. 論理レプリケーション改善：DDLレプリケーションサポート、データ同期アーキテクチャの大幅簡素化

💡 SQLフォーマッターツールを使ってPostgreSQLクエリを整形し、実行計画の分析を容易にしましょう。

EXPLAIN ANALYZE 深掘り解析

EXPLAIN ANALYZEはPostgreSQLパフォーマンスチューニングの最も重要なツールです。クエリ計画を表示するだけでなく、クエリを実行して実際のタイミング統計を返します。

基本的な使い方

-- 実行計画を表示（実行なし）
EXPLAIN SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
  AND o.status = 'shipped';

-- クエリを実行して実際の統計を返す
EXPLAIN ANALYZE SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01'
  AND o.status = 'shipped';

-- 詳細出力を表示（バッファ統計付き）
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE status = 'shipped';

スキャンタイプの解説

シーケンシャルスキャン（Seq Scan）

EXPLAIN ANALYZE SELECT * FROM orders WHERE amount > 1000;
-- Seq Scan on orders  (cost=0.00..15432.00 rows=100000 width=88)
--   Filter: (amount > 1000)
  • 適用シナリオ：テーブルデータ量が小さい、クエリが大部分の行を返す、適切なインデックスがない
  • 最適化方向：返される行数が総行数の5%未満の場合、インデックスの追加を検討

インデックススキャン（Index Scan）

EXPLAIN ANALYZE SELECT * FROM orders WHERE order_id = 12345;
-- Index Scan using orders_pkey on orders  (cost=0.42..8.44 rows=1 width=88)
--   Index Cond: (order_id = 12345)
  • 適用シナリオ：等価クエリ、高選択性条件、少数行の返却
  • 最適化方向：インデックスカラムの順序がクエリ条件と一致することを確認

ビットマップスキャン（Bitmap Scan）

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';
-- Bitmap Heap Scan on orders  (cost=523.12..8234.56 rows=15000 width=88)
--   Recheck Cond: (status = 'shipped')
--   -> Bitmap Index Scan on idx_orders_status  (cost=0.00..519.37 rows=15000)
  • 適用シナリオ：中程度の行数を返す（テーブルの5%-15%）、複数条件の組み合わせクエリ
  • 最適化方向：Bitmap Index Scan + Bitmap Heap Scanの組み合わせ、中程度の選択性に適している

ジョインタイプの解説

ネステッドループジョイン（Nested Loop）

EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.customer_id = 100;
-- Nested Loop  (cost=0.85..234.56 rows=50 width=200)
--   -> Index Scan using idx_orders_customer on orders o
--   -> Index Scan using idx_items_order on order_items oi
  • 適用シナリオ：小テーブル駆動大テーブル、内テーブルにインデックスがある、少数行の返却
  • 最適化方向：内テーブルのジョインカラムにインデックスがあることを確認

ハッシュジョイン（Hash Join）

EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- Hash Join  (cost=345.67..12345.89 rows=100000 width=200)
--   Hash Cond: (o.customer_id = c.customer_id)
--   -> Seq Scan on orders o
--   -> Hash
--       -> Seq Scan on customers c
  • 適用シナリオ：大テーブル同士のジョイン、等価ジョイン、ソート不要
  • 最適化方向：Build側（ハッシュテーブル）がwork_memに収まることを確認

マージジョイン（Merge Join）

EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.customer_id;
-- Merge Join  (cost=0.86..15678.90 rows=100000 width=200)
--   Merge Cond: (o.customer_id = c.customer_id)
--   -> Index Scan using idx_orders_customer on orders o
--   -> Index Scan using customers_pkey on customers c
  • 適用シナリオ：両側がソート済み、等価ジョイン、大データ量
  • 最適化方向：両側のジョインカラムにインデックス（ソート済み）があることを確認

EXPLAIN出力のキーフィールド

フィールド 意味 注目点
cost 推定コスト（起動..合計コスト） 合計コストが高い場合は最適化が必要
rows 推定返却行数 実際の行数との差が大きい場合はANALYZEが必要
width 平均行幅（バイト） 広すぎる場合はSELECTカラムの削減を検討
actual time 実際の所要時間（ミリ秒） 遅いノードに注目
actual rows 実際の返却行数 推定との差が大きい場合は統計を更新
loops 実行回数 ネステッドループでは loops × time = 合計時間
Buffers バッファヒット統計 shared hitが高い＝キャッシュフレンドリー

💡 JSONフォーマッターツールを使ってFORMAT JSON出力のEXPLAIN結果を解析しましょう。

インデックスタイプと選定実践

PostgreSQLは豊富なインデックスタイプを提供しています。適切なインデックスタイプの選択がパフォーマンスチューニングの鍵です。

B-treeインデックス（デフォルト）

-- B-treeインデックスの作成
CREATE INDEX idx_orders_date ON orders (order_date);

-- 複合B-treeインデックス
CREATE INDEX idx_orders_status_date ON orders (status, order_date);

-- 適用シナリオ：等価クエリ、範囲クエリ、ソート、LIKE 'prefix%'
-- 最も一般的なインデックスタイプ、90%以上のシナリオをカバー

GINインデックス（転置インデックス）

-- JSONBフィールドインデックス
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- 配列フィールドインデックス
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- 全文検索インデックス
CREATE INDEX idx_docs_content ON documents USING GIN (to_tsvector('english', content));

-- クエリ例
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'performance'];
SELECT * FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & tuning');
  • 適用シナリオ：JSONB、配列、全文検索
  • 注意：GINインデックスは構築が遅いがクエリは速い；fastupdate = onで書き込みを高速化

GiSTインデックス（汎用検索ツリー）

-- 地理空間インデックス（PostGIS）
CREATE INDEX idx_stores_location ON stores USING GiST (location);

-- 範囲型インデックス
CREATE INDEX idx_events_period ON events USING GiST (time_range);

-- クエリ例
SELECT * FROM stores WHERE ST_DWithin(location, ST_Point(116.4, 39.9), 5000);
SELECT * FROM events WHERE time_range && '[2026-01-01, 2026-06-30)'::daterange;
  • 適用シナリオ：地理空間、範囲型、ツリー構造
  • 注意：GiSTはカスタム演算子クラスをサポート

BRINインデックス（ブロック範囲インデックス）

-- 時系列データインデックス
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

-- カスタムブロックサイズ
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at) WITH (pages_per_range = 32);

-- クエリ例
SELECT * FROM logs WHERE created_at >= '2026-06-01' ORDER BY created_at DESC LIMIT 100;
  • 適用シナリオ：大テーブルで自然にソートされたカラム（時系列データ、オートインクリメントID）
  • 利点：インデックスサイズが極めて小さい（通常B-treeの1/100）
  • 注意：物理的に順序付けられたデータにのみ適している；選択性クエリではB-treeに劣る

Hashインデックス

-- Hashインデックスの作成
CREATE INDEX idx_users_email_hash ON users USING HASH (email);

-- 適用シナリオ：純粋な等価クエリ
-- 注意：PostgreSQL 10+でHashインデックスはWALをサポートし、安全に使用可能
-- ただし通常B-treeで十分；Hashインデックスの使用場面は限定的

インデックスタイプ選定デシジョンテーブル

シナリオ 推奨インデックス 理由
等価/範囲クエリ B-tree 最も汎用的、安定したパフォーマンス
JSONB包含クエリ GIN ネイティブ@>演算子サポート
全文検索 GIN tsvectorによる効率的検索
地理空間クエリ GiST PostGIS標準の組み合わせ
範囲重複クエリ GiST ネイティブ&&演算子サポート
時系列範囲クエリ BRIN 極小インデックス、物理的に順序付けられたデータに適している
純粋な等価クエリ（超大テーブル） Hash 等価クエリでB-treeよりわずかに高速

部分インデックスと式インデックス

部分インデックス（Partial Index）

-- 未完了の注文のみインデックス（インデックスサイズを削減）
CREATE INDEX idx_orders_pending ON orders (customer_id, order_date)
WHERE status = 'pending';

-- アクティブユーザーのみインデックス
CREATE INDEX idx_users_active ON users (last_login_at)
WHERE is_active = true;

-- クエリは自動的に部分インデックスを使用
SELECT * FROM orders WHERE status = 'pending' AND customer_id = 100;
  • 利点：インデックスサイズが大幅に削減、書き込みオーバーヘッドが低下
  • 注意：クエリ条件がWHERE句を満たす必要がある

式インデックス

-- 大文字小文字を区別しないクエリ
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- 日付切り詰めクエリ
CREATE INDEX idx_orders_date_trunc ON orders (DATE(order_date));

-- JSONB特定キーインデックス
CREATE INDEX idx_products_color ON products ((attributes->>'color'));

-- クエリ例
SELECT * FROM users WHERE LOWER(email) = 'admin@example.com';
SELECT * FROM orders WHERE DATE(order_date) = '2026-06-11';
SELECT * FROM products WHERE attributes->>'color' = 'red';
  • 利点：クエリ時の式計算を回避、インデックスを直接使用
  • 注意：クエリ条件がインデックス式と完全に一致する必要がある

複合インデックスのカラム順序の原則

-- 順序：等価条件カラム → 範囲条件カラム → ソートカラム
CREATE INDEX idx_orders_optimal ON orders (status, customer_id, order_date DESC);

-- カバリングインデックス：すべてのクエリカラムを含め、テーブルルックアップを回避
CREATE INDEX idx_orders_covering ON orders (status, order_date)
INCLUDE (customer_id, amount);

クエリ最適化パターン

SELECT * の回避

-- アンチパターン
SELECT * FROM orders WHERE customer_id = 100;

-- 最適化：必要なカラムのみクエリ
SELECT order_id, order_date, amount
FROM orders WHERE customer_id = 100;

ページネーション最適化

-- アンチパターン：OFFSETは大きなオフセットでパフォーマンスが極めて悪い
SELECT * FROM orders ORDER BY order_id OFFSET 100000 LIMIT 20;

-- 最適化1：キーセットページネーション（カーソルページネーション）
SELECT * FROM orders WHERE order_id > 100000 ORDER BY order_id LIMIT 20;

-- 最適化2：カバリングインデックス + JOIN
SELECT o.* FROM orders o
JOIN (SELECT order_id FROM orders ORDER BY order_id OFFSET 100000 LIMIT 20) t
ON o.order_id = t.order_id;

サブクエリ最適化

-- アンチパターン：相関サブクエリ
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.order_id AND oi.price > 100);

-- 最適化：JOINに書き換え
SELECT DISTINCT o.* FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE oi.price > 100;

CTE最適化

-- PostgreSQL 12+：CTEはデフォルトでインライン最適化可能
WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date >= '2026-01-01'
)
SELECT ro.*, c.customer_name
FROM recent_orders ro
JOIN customers c ON ro.customer_id = c.customer_id;

-- CTEをマテリアライズする必要がある場合は明示的に指定
WITH MATERIALIZED expensive_calc AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders GROUP BY customer_id
)
SELECT * FROM expensive_calc WHERE total > 10000;

バッチ操作最適化

-- バッチ挿入（単一INSERTで複数値）
INSERT INTO orders (customer_id, amount, status)
VALUES
  (1, 100.00, 'pending'),
  (2, 200.00, 'shipped'),
  (3, 150.00, 'pending');

-- CASEを使用したバッチ更新
UPDATE orders SET status = CASE
  WHEN order_id = 1 THEN 'shipped'
  WHEN order_id = 2 THEN 'delivered'
  WHEN order_id = 3 THEN 'cancelled'
  ELSE status
END
WHERE order_id IN (1, 2, 3);

-- COPYによる高速インポート
COPY orders FROM '/data/orders.csv' WITH (FORMAT csv, HEADER true);

コネクションプール設定（PgBouncer）

PgBouncerはPostgreSQLで最も人気のあるコネクションプールで、接続数過多によるパフォーマンス問題を効果的に解決します。

なぜコネクションプールが必要か？

各PostgreSQL接続はプロセスをforkし、約10MBのメモリを消費します。1000接続 = 10GBのメモリオーバーヘッド、さらにプロセススイッチングコストも高いです。

PgBouncerのインストールと設定

# PgBouncerのインストール
sudo apt install pgbouncer

# PgBouncerの起動
sudo systemctl start pgbouncer
sudo systemctl enable pgbouncer

; /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
max_client_conn = 10000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 10
reserve_pool_timeout = 3
server_idle_timeout = 300
server_lifetime = 3600
log_connections = 0
log_disconnections = 0
stats_period = 60

コネクションプールモード

モード 特徴 適用シナリオ
session セッションレベルの再利用、接続をクライアントにバインド セッション変数、一時テーブルが必要
transaction トランザクションレベルの再利用、トランザクション終了後に接続を返却 ほとんどのWebアプリ（推奨）
statement ステートメントレベルの再利用、各ステートメント後に返却 短いクエリ、トランザクションなし

接続数の計画

-- 現在の接続数を確認
SELECT count(*) FROM pg_stat_activity;

-- ステータス別にグループ化
SELECT state, count(*) FROM pg_stat_activity GROUP BY state ORDER BY count DESC;

-- PostgreSQLの最大接続数を確認
SHOW max_connections;

-- 推奨計算式
-- PgBouncer default_pool_size = CPUコア数 × 2 + ディスク数
-- PostgreSQL max_connections = 全PgBouncer pool_sizeの合計 + 予約分

コアパラメータチューニング

shared_buffers

-- 共有バッファサイズ、PostgreSQLの最も重要なパラメータ
-- 推奨値：システム総メモリの25%、ただし8GBを超えない（Linux）
ALTER SYSTEM SET shared_buffers = '4GB';

work_mem

-- ソートとハッシュ操作のメモリ上限
-- 推奨値：総メモリ / (max_connections × 3)、通常16MB-256MB
ALTER SYSTEM SET work_mem = '64MB';

-- work_memによるディスクソートを確認
SELECT query, calls, temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC LIMIT 10;

effective_cache_size

-- クエリオプティマイザのシステムキャッシュ推定値（実際にはメモリを割り当てない）
-- 推奨値：システム総メモリの50%-75%
ALTER SYSTEM SET effective_cache_size = '12GB';

WAL関連パラメータ

-- WAL書き込み方式：open_datasync（Linuxデフォルト）、fdatasync、fsyncなど
ALTER SYSTEM SET wal_sync_method = 'open_datasync';

-- WALバッファサイズ
ALTER SYSTEM SET wal_buffers = '64MB';

-- チェックポイント間隔
ALTER SYSTEM SET checkpoint_timeout = '10min';
ALTER SYSTEM SET max_wal_size = '2GB';
ALTER SYSTEM SET min_wal_size = '512MB';

-- 整合性保証（本番環境では必ずON）
ALTER SYSTEM SET fsync = 'on';
ALTER SYSTEM SET synchronous_commit = 'on';

主要パラメータクイックリファレンス

パラメータ 推奨値（16GB RAM） 説明
shared_buffers 4GB 共有バッファ
work_mem 64MB ソート/ハッシュメモリ
effective_cache_size 12GB オプティマイザキャッシュ推定
maintenance_work_mem 1GB メンテナンス操作メモリ
wal_buffers 64MB WALバッファ
max_wal_size 2GB WAL最大サイズ
checkpoint_timeout 10min チェックポイント間隔
random_page_cost 1.1（SSD） ランダムページコスト
effective_io_concurrency 200（SSD） 同時I/O数
max_connections 100-200 最大接続数 
# pgtuneを使用して推奨設定を自動生成
pgtune -i postgresql.conf -M 16GB -T web -c 200

パーティショニング戦略

PostgreSQL 10+はネイティブ宣言型パーティショニングをサポートし、大テーブル処理のコアアプローチです。

レンジパーティショニング（Range）

-- 月別に注文テーブルをパーティショニング
CREATE TABLE orders (
  order_id BIGINT,
  customer_id INT,
  order_date DATE,
  amount DECIMAL(10,2),
  status TEXT
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2026_q1 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE orders_2026_q2 PARTITION OF orders
  FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');
CREATE TABLE orders_2026_q3 PARTITION OF orders
  FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');
CREATE TABLE orders_2026_q4 PARTITION OF orders
  FOR VALUES FROM ('2026-10-01') TO ('2027-01-01');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

リストパーティショニング（List）

-- 地域別パーティショニング
CREATE TABLE customers (
  customer_id BIGINT,
  name TEXT,
  region TEXT,
  created_at TIMESTAMP
) PARTITION BY LIST (region);

CREATE TABLE customers_north PARTITION OF customers
  FOR VALUES IN ('Beijing', 'Tianjin', 'Hebei');
CREATE TABLE customers_south PARTITION OF customers
  FOR VALUES IN ('Guangdong', 'Fujian', 'Hainan');
CREATE TABLE customers_default PARTITION OF customers DEFAULT;

ハッシュパーティショニング（Hash）

-- ハッシュパーティショニング（均等分散）
CREATE TABLE access_logs (
  log_id BIGINT,
  user_id INT,
  path TEXT,
  created_at TIMESTAMP
) PARTITION BY HASH (user_id);

CREATE TABLE access_logs_p0 PARTITION OF access_logs
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE access_logs_p1 PARTITION OF access_logs
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE access_logs_p2 PARTITION OF access_logs
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE access_logs_p3 PARTITION OF access_logs
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

パーティションメンテナンス

-- 将来のパーティションを自動作成（pg_partman拡張を使用）
CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
  p_parent_table := 'public.orders',
  p_control := 'order_date',
  p_type := 'range',
  p_interval := '1 month',
  p_premake := 6
);

-- パーティションプルーニングの検証
EXPLAIN SELECT * FROM orders WHERE order_date = '2026-06-11';
-- orders_2026_q2パーティションのみをスキャンすべき

-- 古いパーティションの削除（アーカイブ後）
DROP TABLE orders_2024_q1;

パラレルクエリ

PostgreSQL 9.6+でパラレルクエリが導入され、バージョン18で大幅に強化されました。

パラレルクエリの有効化

-- パラレルクエリを有効にする
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET max_parallel_workers = 8;
ALTER SYSTEM SET parallel_tuple_cost = 0.01;
ALTER SYSTEM SET parallel_setup_cost = 100;

-- パラレルをトリガーする最小テーブルサイズ（デフォルト8MB）
ALTER SYSTEM SET min_parallel_table_scan_size = '8MB';

-- テーブルサイズに応じてパラレルワーカー数を増加
ALTER SYSTEM SET min_parallel_index_scan_size = '512kB';

パラレルクエリの検証

EXPLAIN ANALYZE SELECT COUNT(*), SUM(amount)
FROM orders WHERE order_date >= '2026-01-01';
-- Gather Merge
--   -> Sort
--       -> Partial Aggregate
--           -> Parallel Seq Scan on orders

パラレルクエリの適用シナリオ

シナリオ パラレル効果 備考
大テーブルフルスキャン 非常に良い Parallel Seq Scan
大テーブル集約 非常に良い Parallel Aggregation
大テーブルJOIN 良い Parallel Hash Join
インデックススキャン 普通 Parallel Bitmap Scan
小テーブルクエリ 効果なし min_parallel_table_scan_size未満
CTE（マテリアライズド） 非サポート MATERIALIZED CTEはパラレル化されない

VACUUMとautovacuumチューニング

PostgreSQLのMVCCメカニズムはデッドタプルを生成し、VACUUMによって領域を回収する必要があります。

手動VACUUM

-- 通常VACUUM（ディスク領域を回収せず、再利用可能としてマークのみ）
VACUUM orders;

-- VACUUM FULL（ディスク領域を回収、テーブルをロック！）
VACUUM FULL orders;

-- パラレルVACUUM（PostgreSQL 12+、テーブルロックなし）
VACUUM (PARALLEL 4) orders;

-- 統計情報の分析
ANALYZE orders;

-- VACUUM + ANALYZEを同時に実行
VACUUM ANALYZE orders;

autovacuumチューニング

-- autovacuumを有効にする（デフォルトで有効）
ALTER SYSTEM SET autovacuum = 'on';

-- autovacuumワーカー数
ALTER SYSTEM SET autovacuum_max_workers = 4;

-- トリガー閾値（デッドタプル数がこの値を超えるとトリガー）
-- 計算式：autovacuum_vacuum_scale_factor × テーブル行数 + autovacuum_vacuum_threshold
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.1;
ALTER SYSTEM SET autovacuum_vacuum_threshold = 50;

-- 分析トリガー閾値
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.05;
ALTER SYSTEM SET autovacuum_analyze_threshold = 50;

-- autovacuum1回あたりのコスト制限
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';

テーブルごとのautovacuumチューニング

-- 頻繁に更新されるテーブルのトリガー閾値を下げる
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 5000
);

-- テーブルのVACUUM統計を確認
SELECT relname, n_live_tup, n_dead_tup,
  last_vacuum, last_autovacuum,
  last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

VACUUM進捗のモニタリング

-- PostgreSQL 12+ VACUUM進捗を確認
SELECT pid, datname, relid::regclass, phase,
  heap_blks_total, heap_blks_scanned, heap_blks_vacuumed,
  index_vacuum_count, num_dead_item_ids
FROM pg_stat_progress_vacuum;

pg_stat_statementsによるスロークエリ分析

pg_stat_statementsはPostgreSQLの最も強力なスロークエリ分析ツールです。

インストールと有効化

-- 拡張をロード
CREATE EXTENSION pg_stat_statements;

-- postgresql.confを変更
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all
-- pg_stat_statements.max = 10000

Top N スロークエリ

-- 合計時間でソート
SELECT query, calls, total_exec_time, mean_exec_time,
  min_exec_time, max_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- 平均時間でソート
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC LIMIT 10;

-- ディスクI/Oでソート
SELECT query, calls, shared_blks_read, shared_blks_hit,
  ROUND(shared_blks_hit::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0), 4) AS cache_hit_ratio
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY shared_blks_read DESC LIMIT 10;

-- 統計をリセット
SELECT pg_stat_statements_reset();

キャッシュヒット率分析

-- 全体キャッシュヒット率（> 99%であるべき）
SELECT
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- テーブル別キャッシュヒット率
SELECT relname,
  heap_blks_hit, heap_blks_read,
  ROUND(heap_blks_hit::numeric / NULLIF(heap_blks_hit + heap_blks_read, 0), 4) AS cache_hit_ratio
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY heap_blks_read DESC;

一般的なパフォーマンスアンチパターン

アンチパターン1：過剰インデックス

-- 問題：各インデックスは書き込みオーバーヘッドを増加させる
-- 未使用インデックスを確認
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 未使用インデックスを削除
DROP INDEX CONCURRENTLY idx_orders_unused;

アンチパターン2：大規模トランザクション

-- 問題：長時間トランザクションはVACUUMによるデッドタプル回収を妨げ、テーブル膨張を引き起こす
-- 長時間トランザクションを確認
SELECT pid, now() - xact_start AS duration, query, state
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
  AND xact_start IS NOT NULL
ORDER BY duration DESC;

-- トランザクションタイムアウトを設定（PostgreSQL 17+）
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60000';

アンチパターン3：型の不一致

-- 問題：暗黙的な型変換によりインデックスが使用できない
-- アンチパターン
SELECT * FROM orders WHERE order_id = '12345';  -- VARCHARとBIGINTの不一致

-- 最適化：型を一致させる
SELECT * FROM orders WHERE order_id = 12345;

アンチパターン4：OR条件の乱用

-- アンチパターン
SELECT * FROM orders WHERE customer_id = 100 OR amount > 5000;

-- 最適化：UNION ALLを使用
SELECT * FROM orders WHERE customer_id = 100
UNION ALL
SELECT * FROM orders WHERE amount > 5000 AND customer_id != 100;

アンチパターン5：関数によるインデックス無効化

-- アンチパターン：インデックスカラムに関数を使用
SELECT * FROM orders WHERE DATE(created_at) = '2026-06-11';

-- 最適化：範囲クエリを使用
SELECT * FROM orders WHERE created_at >= '2026-06-11' AND created_at < '2026-06-12';

-- または式インデックスを使用
CREATE INDEX idx_orders_created_date ON orders (DATE(created_at));

Prometheus + Grafana モニタリング

postgres_exporterのデプロイ

# postgres_exporterのインストール
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz
tar xzf postgres_exporter-0.15.0.linux-amd64.tar.gz

# データソースの設定
export DATA_SOURCE_NAME="postgresql://monitor:password@localhost:5432/mydb?sslmode=disable"

# 起動
./postgres_exporter --web.listen-address=:9187

Prometheus設定

scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['localhost:9187']
    metric_relabel_configs:
      - source_labels: [__name__]
        regex: 'pg_.*'
        action: keep

主要モニタリングメトリクス

メトリクス 意味 アラート閾値
pg_stat_activity_count 現在の接続数 > max_connections × 80%
pg_stat_database_deadlocks デッドロック数 > 0
pg_stat_user_tables_n_dead_tup デッドタプル数 > 総行数 × 10%
pg_stat_statements_mean_exec_time 平均クエリ時間 P99 > 500ms
pg_replication_lag レプリケーション遅延 > 30s
pg_database_size_bytes データベースサイズ > ディスク85%

アラートルール例

groups:
  - name: postgresql_alerts
    rules:
      - alert: PostgreSQLHighConnections
        expr: pg_stat_activity_count > 160
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL接続数が高すぎます"
          description: "現在の接続数が160を超え、5分間継続"

      - alert: PostgreSQLReplicationLag
        expr: pg_replication_lag > 30
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQLレプリケーション遅延が高すぎます"
          description: "レプリケーション遅延が30秒を超過"

      - alert: PostgreSQLDeadTuplesHigh
        expr: pg_stat_user_tables_n_dead_tup / pg_stat_user_tables_n_live_tup > 0.1
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQLデッドタプル比率が高すぎます"
          description: "デッドタプルが10%を超過、VACUUMが必要"

💡 ハッシュ暗号化ツールを使ってモニタリングアラートwebhookの署名キーを生成しましょう。

FAQ

Q1：shared_buffersはどのくらいに設定すべきですか？

システム総メモリの**25%**に設定することを推奨しますが、8GBを超えないようにしてください（Linux）。PostgreSQLはOSのページキャッシュに依存しているため、shared_buffersが大きすぎるとOSキャッシュと競合します。16GB RAMのサーバーの場合、shared_buffers = 4GBに設定します。

Q2：B-treeの代わりにBRINインデックスを使うべき時は？

テーブルが非常に大きく（1億行超）、カラム値が物理ストレージ順序と相関している場合（タイムスタンプ、オートインクリメントIDなど）、BRINインデックスのサイズはB-treeの1/100で、クエリパフォーマンスは同等です。典型的なユースケース：ログテーブルの時間カラム、時系列データの収集タイムスタンプ。

Q3：パーティショニングが必要かどうかの判断方法は？

単一テーブルが1000万行を超え、クエリが通常部分的なデータのみを対象とする場合（例：時間範囲クエリ）、パーティショニングを検討してください。パーティションプルーニングによりスキャンデータ量を大幅に削減できます。

Q4：VACUUM FULLはいつ使うべきですか？

VACUUM FULLはテーブルをロックし全体を再構築するため、すべての断片化した領域を回収します。テーブル膨張が深刻（デッドタプル > 50%）で、通常のVACUUMで回収できない場合にのみ使用してください。オンライン代替としてpg_repack拡張の使用を推奨します。

Q5：PgBouncerのtransactionモードの制限は？

transactionモードでは、トランザクション終了後に接続が回収されるため、一時テーブル（トランザクション間）、SETによるセッションレベルパラメータ（トランザクション間）、LISTEN/NOTIFY（トランザクション間）、プリペアドステートメント（トランザクション間）は使用できません。これらの機能が必要な場合はsessionモードを使用してください。

Q6：スロークエリを素早く特定する方法は？

  1. pg_stat_statements拡張を有効にする
  2. pg_stat_statementsをtotal_exec_timeでソートしてクエリ
  3. スロークエリにEXPLAIN ANALYZEを使用して実行計画を分析
  4. Seq Scan、高コストノードがないか確認
  5. 適切にインデックスを追加またはクエリを書き換え

Q7：random_page_costはいくつに設定すべきですか？

  • SSD/NVMe：1.1に設定（シーケンシャルI/Oコストに近い）
  • HDD：デフォルトの4.0を維持
  • このパラメータはオプティマイザがインデックススキャンとシーケンシャルスキャンのどちらを選択するかに直接影響します

Q8：PostgreSQLの接続数上限の決め方は？

各接続は約10MBのメモリを消費します。推奨計算式：max_connections = 利用可能メモリ(GB) × 100、ただし実際の接続数はPgBouncerプーリングで管理すべきです。PostgreSQLのmax_connectionsは100-200に設定し、外部接続はPgBouncerで管理します。

まとめ

PostgreSQLは2026年も最も強力なオープンソースリレーショナルデータベースであり続けています。パフォーマンスチューニングには、クエリ分析、インデックス設計、パラメータ設定、アーキテクチャ最適化の4つの次元から体系的にアプローチする必要があります。重要なポイントの振り返り：

  1. EXPLAIN ANALYZEはパフォーマンスチューニングの出発点；各スキャンとジョインタイプの適用シナリオを理解する
  2. インデックス選定が重要：B-treeは汎用、GINはJSONB/配列、BRINは時系列データに適している
  3. パラメータチューニングの4つのコア：shared_buffers、work_mem、effective_cache_size、WAL設定
  4. パーティショニング戦略は大テーブル管理の基盤：レンジパーティショニングが最も一般的、ハッシュパーティショニングで均等分散
  5. VACUUMチューニングでテーブル膨張を防止：autovacuumを必ず有効にし適切に設定
  6. モニタリングシステムが保障：pg_stat_statements + postgres_exporter + Grafanaの3点セット

ブラウザローカルツールを無料で試す →

#PostgreSQL#性能调优#数据库#SQL优化#教程