PostgreSQL設定チューニングガイド - postgresql.confの最適化

はじめに

PostgreSQLはデフォルト設定でも動作しますが、本番環境で最大限のパフォーマンスを発揮するには、サーバーのハードウェアリソースとワークロードに応じた適切なチューニングが必要です。設定ファイルpostgresql.confには数百のパラメータがありますが、パフォーマンスに大きな影響を与える重要なパラメータは限られています。

本記事では、PostgreSQLの主要な設定パラメータの意味と調整指針、ワークロード別の設定例、そして設定変更を反映する方法について解説します。

この記事を読むことで、以下のことができるようになります。

• 主要なメモリ関連パラメータの役割を理解し、適切な値を設定できる
• ワークロードの特性に応じたチューニング方針を立てられる
• 設定変更の反映方法と検証手順を実践できる
• パフォーマンス問題の原因を設定面から分析できる

前提条件

• PostgreSQL 14以降がインストールされていること
• PostgreSQLサーバーへの管理者権限（スーパーユーザー）でのアクセスが可能であること
• 基本的なLinuxコマンド操作ができること
• PostgreSQLの基本的なアーキテクチャを理解していること

検証環境

本記事の設定例は以下の環境で検証しています。

postgresql.confの基本

設定ファイルの場所

postgresql.confの場所はインストール方法によって異なります。以下のSQLで確認できます。

1

SHOW config_file;

一般的な配置場所は以下のとおりです。

設定の反映タイミング

PostgreSQLの設定パラメータは、変更の反映タイミングによって以下の3つに分類されます。

設定のコンテキストはpg_settingsビューで確認できます。

1
2
3

SELECT name, context, setting, unit
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'max_connections');

メモリ関連パラメータの最適化

PostgreSQLのパフォーマンスに最も大きな影響を与えるのがメモリ関連のパラメータです。

shared_buffers

shared_buffersは、PostgreSQLがデータのキャッシュに使用する共有メモリ領域のサイズです。ディスクI/Oを削減し、クエリパフォーマンスを向上させるための最も重要なパラメータです。

推奨設定値:

# 32GBメモリの場合
shared_buffers = 8GB

設定のポイント:

• デフォルト値（128MB）は本番環境には不十分です
• システムメモリの40%を超える設定は、OSのファイルキャッシュとの競合により逆効果になることがあります
• 変更にはPostgreSQLの再起動が必要です

効果の確認方法:

pg_buffercache拡張機能を使って、バッファキャッシュの使用状況を確認できます。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

-- バッファキャッシュの使用状況サマリ
SELECT
    c.relname,
    pg_size_pretty(count(*) * 8192) AS buffered,
    round(100.0 * count(*) / (SELECT setting FROM pg_settings WHERE name = 'shared_buffers')::integer, 2) AS buffers_percent
FROM pg_buffercache b
INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY count(*) DESC
LIMIT 10;

work_mem

work_memは、ソート操作やハッシュテーブルなど、クエリ内の個々の操作が使用できるメモリ量です。

推奨設定値:

work_mem = システムメモリ / (max_connections × 2〜4)

# 32GBメモリ、max_connections = 200の場合
# 32GB / (200 × 4) = 約40MB
work_mem = 40MB

設定のポイント:

• 1つのクエリ内で複数のソートやハッシュ操作が並列実行されることがあり、それぞれがwork_memを使用します
• 並列クエリでは各ワーカープロセスもwork_memを使用します
• 値が小さすぎるとディスクへの一時ファイル書き出しが発生し、パフォーマンスが低下します

一時ファイル使用の確認:

1
2
3
4

-- 一時ファイルの使用状況を確認
SELECT datname, temp_files, temp_bytes
FROM pg_stat_database
WHERE datname = current_database();

temp_filesやtemp_bytesが大きい場合、work_memの増加を検討してください。

hash_mem_multiplier

PostgreSQL 13以降で追加されたパラメータで、ハッシュ操作に対するwork_memの乗数を設定します。

# ハッシュ操作は work_mem × 2 まで使用可能（デフォルト）
hash_mem_multiplier = 2.0

データウェアハウス系のワークロードでは、2.0〜8.0の範囲で増加させると効果的です。

maintenance_work_mem

VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEYなどのメンテナンス操作で使用するメモリ量です。

# 32GBメモリの場合
maintenance_work_mem = 2GB

推奨設定値:

• 通常はwork_memよりも大きな値を設定します
• メンテナンス操作は同時に1つのセッションでしか実行されないため、大きめの値を設定しても安全です
• 大きなテーブルへのインデックス作成やVACUUM処理が高速化されます

effective_cache_size

クエリプランナーが利用可能と想定するディスクキャッシュのサイズです。実際のメモリ割り当てには影響せず、クエリプランの選択にのみ使用されます。

# 32GBメモリの場合（shared_buffers + OSキャッシュを考慮）
effective_cache_size = 24GB

推奨設定値:

effective_cache_size = システムメモリの50-75%

設定のポイント:

• 値が大きいほど、プランナーはインデックススキャンを選択しやすくなります
• 過大な値を設定してもメモリ消費は増えませんが、実際のキャッシュ能力を超えた最適化計画を立てる可能性があります
• 変更はpg_reload_conf()で即座に反映されます

接続関連パラメータの最適化

max_connections

同時に接続可能なクライアント数の上限です。

max_connections = 200

設定のポイント:

• デフォルト値（100）は小規模なシステム向けです
• 各接続はメモリを消費するため（約5-10MB/接続）、無制限に増やすことはできません
• 接続数の増加はshared_buffersなど他のパラメータにも影響します

接続プーリングの推奨:

max_connectionsを大きくするよりも、PgBouncerなどの接続プーラーの導入を推奨します。

graph LR
    A[アプリケーション] -->|多数の接続| B[PgBouncer]
    B -->|少数の接続| C[PostgreSQL]
    
    style B fill:#f9f,stroke:#333

接続プーラーを使用する場合の設定例:

# PgBouncer使用時
max_connections = 50  # プーラーからの接続数 + 管理用

# プーラーなしで直接接続する場合
max_connections = 200

superuser_reserved_connections

スーパーユーザー用に予約する接続数です。

superuser_reserved_connections = 3  # デフォルト

max_connectionsに到達した場合でも、スーパーユーザーはこの予約分で接続できます。緊急時のメンテナンス用に必ず確保しておきましょう。

クエリプランナー関連パラメータ

random_page_cost

ランダムなディスクページ読み取りのコスト推定値です。シーケンシャル読み取り（seq_page_cost = 1.0）に対する相対値で設定します。

# HDDの場合（デフォルト）
random_page_cost = 4.0

# SSDの場合
random_page_cost = 1.1

# 全データがメモリに載る場合
random_page_cost = 1.0

SSDを使用している場合は、この値を下げることでインデックススキャンが選択されやすくなります。

effective_io_concurrency

PostgreSQLが期待する同時I/O操作数です。

# HDD
effective_io_concurrency = 2

# SSD
effective_io_concurrency = 200

# NVMe SSD
effective_io_concurrency = 200

SSDを使用している場合は大きな値を設定すると、ビットマップヒープスキャンなどの並列I/O操作が効率化されます。

ワークロード別設定例

以下に代表的なワークロード別の設定例を示します。

OLTP（トランザクション処理）向け設定

短いクエリが多数実行される環境向けの設定です。

# サーバースペック: 32GB RAM, 8コア, SSD
# 想定接続数: 200

# メモリ設定
shared_buffers = 8GB
work_mem = 32MB
maintenance_work_mem = 1GB
effective_cache_size = 24GB

# 接続設定
max_connections = 200

# プランナー設定
random_page_cost = 1.1
effective_io_concurrency = 200

# WAL設定
wal_buffers = 64MB
checkpoint_completion_target = 0.9

OLAP（分析処理）向け設定

大量データを処理する分析クエリ向けの設定です。

# サーバースペック: 128GB RAM, 32コア, NVMe SSD
# 想定接続数: 20（少数の重いクエリ）

# メモリ設定
shared_buffers = 32GB
work_mem = 1GB
hash_mem_multiplier = 4.0
maintenance_work_mem = 4GB
effective_cache_size = 96GB

# 接続設定
max_connections = 30

# 並列クエリ設定
max_parallel_workers_per_gather = 8
max_parallel_workers = 16
max_parallel_maintenance_workers = 4

# プランナー設定
random_page_cost = 1.1
effective_io_concurrency = 200

# WAL設定
wal_buffers = 128MB
checkpoint_completion_target = 0.9

Webアプリケーション向け設定

接続プーラー（PgBouncer）と組み合わせた設定です。

# サーバースペック: 16GB RAM, 4コア, SSD
# PgBouncer経由の接続

# メモリ設定
shared_buffers = 4GB
work_mem = 64MB
maintenance_work_mem = 512MB
effective_cache_size = 12GB

# 接続設定（PgBouncerからの接続のみ）
max_connections = 50

# プランナー設定
random_page_cost = 1.1
effective_io_concurrency = 200

# タイムアウト設定
statement_timeout = 30s
idle_in_transaction_session_timeout = 60s

設定変更の反映手順

手順1: 現在の設定値を確認

1
2
3
4

-- 変更対象のパラメータを確認
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'effective_cache_size', 'max_connections');

手順2: postgresql.confを編集

1
2

# 設定ファイルを編集
sudo vim /etc/postgresql/16/main/postgresql.conf

手順3: 設定の検証

1
2

# 設定ファイルの構文チェック
postgres -C shared_buffers

手順4: 設定を反映

再起動が必要なパラメータ（postmasterコンテキスト）の場合:

1
2
3
4
5

# systemdを使用している場合
sudo systemctl restart postgresql

# pg_ctlを使用している場合
pg_ctl restart -D $PGDATA

リロードで反映されるパラメータ（sighupコンテキスト）の場合:

1
2
3
4
5

# systemdを使用している場合
sudo systemctl reload postgresql

# pg_ctlを使用している場合
pg_ctl reload -D $PGDATA

または、SQLから実行:

1

SELECT pg_reload_conf();

手順5: 変更の確認

1
2
3

-- 設定が反映されたことを確認
SHOW shared_buffers;
SHOW work_mem;

設定変更時の注意点

1. 本番環境での変更は慎重に: 設定変更前に必ずバックアップを取得してください
2. 段階的な変更: 複数のパラメータを一度に変更せず、1つずつ変更して効果を測定してください
3. 負荷テスト: 可能であれば、本番適用前にステージング環境でテストしてください
4. モニタリング: 変更後はパフォーマンス指標を継続的に監視してください

パラメータ設定の計算ツール

手動での計算が面倒な場合、以下のようなオンラインツールが参考になります。

• PGTune: システムスペックを入力すると推奨設定を生成
• PostgreSQL Configurator: ワークロードタイプに応じた設定を提案

ただし、これらのツールが出力する値はあくまで出発点であり、実際のワークロードに応じた調整が必要です。

チューニング効果の測定

設定変更の効果は以下の指標で測定できます。

バッファキャッシュヒット率

1
2
3
4
5

SELECT
    sum(heap_blks_read) AS heap_read,
    sum(heap_blks_hit) AS heap_hit,
    round(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100, 2) AS cache_hit_ratio
FROM pg_statio_user_tables;

キャッシュヒット率は99%以上を目標にします。

一時ファイル使用量

1
2
3

SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_size
FROM pg_stat_database
WHERE datname = current_database();

temp_bytesが大きい場合、work_memの増加を検討します。

チェックポイント頻度

1
2

SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint
FROM pg_stat_bgwriter;

checkpoints_req（要求によるチェックポイント）が多い場合、max_wal_sizeの増加を検討します。

まとめ

PostgreSQLの設定チューニングは、以下のポイントを押さえることが重要です。

• メモリ設定が最重要: shared_buffers、work_mem、effective_cache_sizeの適切な設定がパフォーマンスの基盤
• ワークロードに応じた調整: OLTP/OLAPなど、システムの特性に合わせて設定を調整
• ストレージ特性の反映: SSD使用時はrandom_page_costとeffective_io_concurrencyを調整
• 接続プーリングの活用: 接続数が多い場合はPgBouncerなどの導入を検討
• 継続的なモニタリング: 設定変更後は効果を測定し、必要に応じて再調整

設定チューニングは一度行えば終わりではなく、データ量やアクセスパターンの変化に応じて継続的に見直すことが重要です。

参考リンク

• PostgreSQL公式ドキュメント - Server Configuration
• PostgreSQL公式ドキュメント - Resource Consumption
• PostgreSQL公式ドキュメント - Query Planning
• PostgreSQL公式ドキュメント - Connections and Authentication
• PostgreSQL Wiki - Tuning Your PostgreSQL Server
• PgBouncer公式ドキュメント