Home » PostgreSQL » 中小企業エンジニアのためのPostgreSQL14 チューニング完全ガイド

中小企業エンジニアのためのPostgreSQL14 チューニング完全ガイド

2021年12月23日木曜日

DB PostgreSQL

中小企業のエンジニアがすぐ実践できるチューニング手順として、PostgreSQL 14の性能を引き出すための最適な設定と運用方法をまとめました。読むだけで理解し、即実装できるよう、具体的な設定例や判断指標を豊富に盛り込んでいます。

目次

  • 導入：なぜチューニングが必要か？
  • 共有バッファとOSキャッシュ
  • クエリ計画とインデックス戦略
  • MVCCとVACUUMの効率化
  • WALとチェックポイントの最適化
  • ロックとトランザクション並列制御
  • パラレルクエリとワーカ制御
  • 実行統計とpg_stat_statements
  • レプリケーションとHA構成（Ver.14構成例）
  • 運用監視とメンテナンス
  • まとめ

導入：なぜチューニングが必要か？

PostgreSQLは堅牢で高機能なRDBMSですが、デフォルト設定のままではオンプレ環境に最適化されているとは限りません。特に中小企業で使われる一般的なLinuxサーバにおいては、メモリ・I/O・CPUなどのリソースが限られているため、適切なチューニングが必要不可欠です。

共有バッファとOSキャッシュ

PostgreSQLではshared_buffersが内部キャッシュとして利用され、ディスクI/Oの低減に貢献します。OSのキャッシュとのバランスをとるため、RAMの25%〜40%を目安に設定します。

shared_buffers = 4GB
effective_cache_size = 12GB

Linuxではvm.swappiness=0、transparent_hugepages=neverの設定を推奨します。

クエリ計画とインデックス戦略

インデックスの利用状況はEXPLAIN ANALYZEで確認できます。PostgreSQL 14ではB-tree以外にBRIN、GIN、GiSTなどの高度なインデックスも改善されています。

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

適切なインデックス設計により、シーケンシャルスキャンを回避し、パフォーマンスを劇的に向上させます。

MVCCとVACUUMの効率化

PostgreSQLはMVCCにより並行性を確保していますが、更新が頻繁なテーブルではdead tuplesが増え、VACUUM処理が重要になります。

autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

PostgreSQL 14ではparallel VACUUMが改善され、大規模テーブルでも効率よく処理できます。

WALとチェックポイントの最適化

WAL（Write-Ahead Logging）の書き込みはパフォーマンスに大きく影響します。特にチェックポイントの頻度を調整することで、突発的なI/O負荷を軽減可能です。

checkpoint_timeout = 15min
max_wal_size = 2GB
min_wal_size = 1GB
wal_compression = on

ロックとトランザクション並列制御

PostgreSQL 14ではトランザクション並列処理が改善されましたが、依然としてロック競合には注意が必要です。pg_locksやpg_stat_activityを定期的に監視し、待機時間が長いクエリはアプリ側での見直しが必要です。

パラレルクエリとワーカ制御

分析系クエリにおいて、パラレル実行の有効活用が重要です。

max_parallel_workers = 4
max_parallel_workers_per_gather = 2
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000

EXPLAINでParallel Seq Scanが表示されることを確認してください。

実行統計とpg_stat_statements

ボトルネックを発見するには、pg_stat_statementsによる統計分析が有効です。

CREATE EXTENSION pg_stat_statements;
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;

これにより、負荷の高いクエリを特定し、優先的に最適化できます。

レプリケーションとHA構成（Ver.14構成例）

PostgreSQL 14ではレプリケーション機能が標準で整備されており、standby.signalを用いたスタンバイ構成が導入されています。これにより、メインとスタンバイ間でのログ転送・同期が自動で行われ、可用性が向上します。

HA（高可用性）を構築するには、スタンバイ構成に加えて自動フェイルオーバー機能の導入が重要です。以下のようなツールが代表的です：

  • Patroni：EtcdやConsulと連携し、自動昇格・クラスタ制御を実現。
  • pg_auto_failover：軽量で構築が簡単。複雑な設定を必要とせず、自動監視と昇格が可能。
  • repmgr：PostgreSQL専用のクラスタ管理ツール。CLIでの管理が特徴。

いずれの方式でも、仮想IP（VIP）またはアプリ側での再接続処理が求められます。ネットワーク構成とあわせて設計しましょう。

運用監視とメンテナンス

パフォーマンスを継続的に維持するには、ログ分析・統計監視・クエリ最適化のサイクルが不可欠です。

  • pg_stat_user_tablesで更新状況を定期確認
  • VACUUM/ANALYZEのスケジューリング
  • pgBadgerによるログ解析
  • ZabbixやPrometheusとの連携監視

まとめ

PostgreSQL 14は高機能かつ柔軟なRDBMSですが、その性能を最大限引き出すにはチューニングが不可欠です。本ガイドを元に、自社の業務要件やハードウェア環境に合わせた最適な構成を実現してください。

時刻:
ラベル: ,