PostgreSQL接続プロキシの選択

はじめに：データベースパフォーマンスの陰の立役者

高性能アプリケーションの世界では、データベースアクセスがしばしばクリティカルなボトルネックとなります。PostgreSQLデータベースへの新規接続ごとに、認証、プロセス作成、リソース割り当てといったオーバーヘッドが発生します。頻繁に短時間接続を確立するアプリケーションでは、このオーバーヘッドがパフォーマンスを急速に低下させ、サーバーリソースを枯渇させる可能性があります。フラッシュセール中の人気ECサイトを想像してみてください。数千人のユーザーが同時に接続しようとします。これらの接続を効率的に管理するメカニズムがなければ、データベースは対応に苦慮するでしょう。ここで接続プーリングプロキシが登場します。アプリケーションとPostgreSQLサーバーの間に配置され、リクエストごとに新しい接続を確立するのではなく、既存の接続をインテリジェントに再利用します。これらは、スケーラビリティと応答性を劇的に向上させる、静かに効率的なゲートキーパーとして機能します。この重要な役割のために、PostgreSQLエコシステムで際立った2つの著名なオープンソースソリューションがあります：PgBouncerとPgpool-IIです。それらのニュアンスを理解することは、データベースインフラストラクチャを最適化するための鍵となり、この記事では、それぞれの強みと理想的なアプリケーションについて解説します。

PgBouncer vs. Pgpool-II：接続管理の解明

PgBouncerとPgpool-IIの詳細に入る前に、それらの操作の根底にあるコアコンセプトの基本的な理解を確立しましょう。

主要な専門用語

接続プーリング (Connection Pooling): 各リクエストごとに新しい接続を開くのではなく、既存のデータベース接続を再利用するプラクティス。これにより、接続のセットアップと切断に関連するオーバーヘッドが削減され、パフォーマンスとリソース利用率が向上します。
プロキシ (Proxy): 他のサーバーからのリソースを求めるクライアントからのリクエストの中継サーバー。私たちの文脈では、アプリケーションとPostgreSQLデータベースの間に配置されます。
ロードバランシング (Load Balancing): 単一のサーバーがボトルネックにならないように、着信ネットワークトラフィックを複数のバックエンドサーバーに分散させること。これにより、アプリケーションの応答性と可用性が向上します。
高可用性 (High Availability - HA): 単一障害点を排除し、フェイルオーバーメカニズムを提供することによって、高いレベルの運用継続性を保証するシステム設計アプローチ。
リードレプリカ (Read Replicas): 読み取り専用クエリを提供できるマスターデータベースのコピー。プライマリデータベースの負荷をオフロードし、読み取りスケーラビリティを向上させます。
ステートメントレベルプーリング (Statement-level Pooling): 各SQLステートメントの実行後に物理接続がプールに戻されるプーリングモード。これは接続再利用の可能性が最も高いですが、注意深く処理しないと複数のステートメントにまたがるトランザクションを破損させる可能性があります。
トランザクションレベルプーリング (Transaction-level Pooling): トランザクション（COMMITまたはROLLBACK）が完了した後、物理接続がプールに戻されるプーリングモード。これは、トランザクション境界に依存するアプリケーションにとってより安全です。
セッションレベルプーリング (Session-level Pooling): 物理接続がクライアントが切断されるまでクライアントに割り当てられるプーリングモード。これは直接接続に最も似ていますが、短時間のクライアントセッションに対してプーリングの利点を提供します。

PgBouncer：軽量で強力な接続マシン

PgBouncerは、そのシンプルさ、効率性、および接続プーリングへの集中で知られています。これは、1つのことを例外的にうまく行うように設計された、軽量で単一プロセス、マルチスレッドのプロキシです：接続の管理。

原理と機能

PgBouncerは、PostgreSQLバックエンドへの接続セットを開き、それらを着信クライアント接続に渡すことによって機能します。クライアントが切断すると、バックエンド接続はプールに戻され、次のクライアントの準備が整います。その主な強みは、さまざまなプーリングモードにあります。

セッションプーリング (デフォルト): サーバー接続は、セッション全体でクライアントセッションに割り当てられます。クライアントが切断すると、サーバー接続はプールに戻されます。これは、セッション状態を完全に保持するため、最も安全なモードです。
トランザクションプーリング: サーバー接続は、トランザクションの期間中のみクライアントに割り当てられます。COMMITまたはROLLBACKの後、接続はプールに戻されます。これにより、接続の再利用性が高まりますが、アプリケーションはトランザクション境界を認識する必要があります。セッション固有の状態（SET TIMEZONEなど）は、トランザクションごとにリセットされます。
ステートメントプーリング: サーバー接続は、単一ステートメントの期間中のみクライアントに割り当てられます。ステートメントが完了すると、接続は戻されます。これは再利用性を最も高くしますが、マルチステートメントトランザクションを破損させ、アプリケーションで注意深く処理されない場合に予期しない動作につながる可能性があるため、最もリスクが高いです。

実装と設定例

PgBouncerは、通常pgbouncer.iniというシンプルなINI形式ファイルで設定されます。

; pgbouncer.ini 例

[databases]
; データベースを定義します。クライアント側のデータベース名を
; 潜在的に異なるバックエンドデータベース名と資格情報にマッピングできます。
mydb = host=localhost port=5432 dbname=my_appdb user=webapp_user password=mypwd pool_size=20

[pgbouncer]
; ポート6432のすべてのインターフェースでリッスンします
listen_addr = *
listen_port = 6432

; 認証ファイル
auth_type = md5
auth_file = users.txt

; 接続プーリングモード
pool_mode = transaction

; 許可される最大クライアント接続数
max_client_conn = 1000

; データベースのデフォルトの最大サーバー接続数
default_pool_size = 10

; プールで維持する最小サーバー接続数
min_pool_size = 5

; 接続が切断されるまで待機する時間（秒）
server_idle_timeout = 60

; 接続が利用可能になるまで待機する時間
query_wait_timeout = 120

; ログレベル（0=DEBUG、1=INFO、2=NOTICE、3=WARNING、4=ERROR、5=FATAL）
logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

users.txtファイルには、認証用のusername "password_hash"エントリが含まれます。

PgBouncerを実行するには：

pgbouncer -d /path/to/pgbouncer.ini

アプリケーションは、PostgreSQLに直接接続するのではなく、PgBouncerのlisten_port（例：6432）に接続します。

アプリケーションシナリオ

高トラフィックWebアプリケーション: 短時間の要求のためにデータベース接続を頻繁に開閉するWebサーバーやマイクロサービスのようなアプリケーションに最適です。
リソース制約のある環境: その最小限のオーバーヘッドのため、PgBouncerは、すべてのリソースが重要となるコンテナや小規模VMに適しています。
シンプルな接続プーリング: 高度な機能（ロードバランシングや高可用性）を必要とせずに、効率的な接続再利用が主な要件である場合。

Pgpool-II：多機能スイイスアーミーナイフ

Pgpool-IIは、接続プーリングだけでなく、ロードバランシング、レプリケーション、高可用性機能も提供する、より包括的なソリューションです。これはPostgreSQLの「ミドルウェア」として設計されており、スケーラビリティと信頼性を向上させるための幅広い機能を提供します。

原理と機能

Pgpool-IIは、クエリをバックエンドPostgreSQLサーバーにルーティングし、読み取りクエリをレプリカにインテリジェントに分散させ、書き込みクエリをプライマリに送信します。その主な機能は次のとおりです。

接続プーリング: PgBouncerと同様に、バックエンドサーバーへの接続を再利用します。
ロードバランシング（読み書き分離）: SELECTステートメントをリードレプリカに自動的にルーティングし、プライマリサーバーの負荷を軽減します。一方、INSERT、UPDATE、DELETEステートメント（およびその他のDDL/DML）はプライマリに送信されます。
高可用性＆自動フェイルオーバー: バックエンドPostgreSQLサーバーを監視し、プライマリ障害発生時にスタンバイサーバーに自動的に切り替えることができ、継続的な運用を保証します。
レプリケーション管理: さまざまなPostgreSQLレプリケーションセットアップ（例：ストリーミングレプリケーション、論理レプリケーション）を管理できます。
クエリキャッシュ: クエリ結果をキャッシュし、読み取り負荷の高いワークロードをさらに高速化できます（ただし、この機能はアプリケーションレベルのキャッシュや、より専門的なツールで処理されることがよくあります）。
インバンド管理: 管理コマンド用の疑似データベースを提供します。

実装と設定例

Pgpool-IIの設定は、その豊富な機能セットにより、通常pgpool.confで管理されるため、より複雑です。

; pgpool.conf 例

; 接続プーリング設定
num_init_children = 32      ; 起動するPgpool-II子プロセスの数
max_pool = 4                ; バックエンドへの各Pgpool-II子プロセスの最大接続数
connection_cache = on       ; 接続キャッシュを有効にする

; バックエンドサーバー定義
backend_hostname0 = '192.168.1.10'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/13/main'
backend_flag0 = 'ALLOW_FAILBACK' ; プライマリ用

backend_hostname1 = '192.168.1.11'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/13/main'
backend_flag1 = 'ALLOW_FAILBACK' ; スタンバイ/レプリカ用

; ロードバランシング設定
load_balance_mode = on      ; 読み書き分離を有効にする
replication_mode = on       ; レプリケートされたバックエンドを扱う場合

; 高可用性設定
health_check_period = 5     ; バックエンドのヘルスチェック間隔
failover_command = '/etc/pgpool2/failover.sh %d %H %W %P %r' ; フェイルオーバー時に実行されるスクリプト
failback_command = '/etc/pgpool2/failback.sh %d %H %W %P %r' ; フェイルバック時に実行されるスクリプト

; 認証設定
listen_addresses = '*'
port = 9999
auth_methods = 'md5'
pg_md5 = '/etc/pgpool2/pg_md5' ; Pgpool-II固有のユーザー認証用ファイル

; バックエンドへの認証委任
enable_pool_hba = on
pool_hba_file = '/etc/pgpool2/pool_hba.conf' ; PostgreSQLのpg_hba.confに似ています

pg_md5には、Pgpool-II管理用のusername:password_hashが含まれます。pool_hba.confは、Pgpool-IIへのクライアントアクセスを制御します。

Pgpool-IIを開始するには：

pgpool -n -d

アプリケーションは、Pgpool-IIのport（例：9999）に接続します。

アプリケーションシナリオ

読み取り負荷の高いワークロードのスケーラビリティ: SELECTクエリの割合が高いアプリケーションにとって、読み書き分離機能は、複数のレプリカに効率的に分散させるために不可欠です。
自動高可用性: プライマリデータベース障害発生時に、自動フェイルオーバー機能と最小限のダウンタイムを必要とするミッションクリティカルなシステム向け。
単純化されたデータベースアーキテクチャ: アプリケーションが複数のPostgreSQLサーバーへの単一のエントリポイントを持ち、レプリケーションとフェイルオーバーの複雑さをアプリケーション層から抽象化したい場合。
レガシーアプリケーション: ネイティブでリードレプリカやフェイルオーバーをサポートしていない古いアプリケーションに役立ち、これらの機能を外部から追加できます。

プロキシの選択

PgBouncerとPgpool-IIの間の決定は、主に特定のニーズとアーキテクチャ目標に依存します。

機能/考慮事項	PgBouncer	Pgpool-II
主な目的	効率的な接続プーリング	ロードバランシング、HA、レプリケーション、接続プーリング
複雑さ	シンプル、軽量、設定が容易	より複雑、多機能、習得難易度が高い
リソース使用量	最小限	高い（より多くの機能のため）
パフォーマンス（プーリング）	優れた、高速な接続再利用に最適化	良好だが、追加機能のためにオーバーヘッドが存在する
ロードバランシング	ネイティブのロードバランシングなし	はい、インテリジェントな読み書き分離
高可用性	ネイティブのHA/フェイルオーバーなし	はい、自動フェイルオーバーとヘルスチェック
リードレプリカ	単一レプリカへの接続をプールできるが、複数のレプリカ間でのインテリジェントなルーティングやロードバランシングはない。	はい、読み取りをレプリカに分散させる
レプリケーション管理	いいえ	はい、さまざまなレプリケーションセットアップを管理する
クエリキャッシュ	いいえ	はい（結果をキャッシュできる）
最適なユースケース	単一データベースインスタンス（プライマリまたはレプリカ）への pure な接続多重化が必要なアプリケーション。	アプリケーション層からレプリケーションの詳細を抽象化しながら、ロードバランシング、HA、およびレプリケーションの管理が必要なアプリケーション。

PgBouncerを使用する場合：

主な関心事は、接続オーバーヘッドを削減することである。
アプリケーションがすでに読み書き分離を処理しているか、プライマリデータベースにのみ接続する。
最小限のオーバーヘッドを持つミニマリストソリューションを探している。
HAを別のレイヤー（例：クラウドプロバイダーサービス、Patroni）で管理している。

Pgpool-IIを使用する場合：

読み取りをスケーリングするために、複数のレプリカに読み取りクエリを分散する必要がある。
自動フェイルオーバー機能を使用して、高可用性を確保する必要がある。
レプリケーションの詳細を抽象化することにより、アプリケーションのデータベースインタラクションを単純化したい。
PostgreSQLサーバーのクラスタへのアプリケーションの単一エントリポイントが必要である。

また、一部の高度なセットアップでは、PgBouncerがPgpool-IIの上にレイヤー化されている場合や、Patroniのような他のHAソリューションとともに使用されている場合があることに注意してください。たとえば、PgBouncerはPatroniクラスタ（HAとフェイルオーバーを処理する）の前に配置でき、Pgpool-IIのHA機能なしで接続プーリングを提供できます。

結論：PostgreSQLフロントエンドのカスタマイズ

PgBouncerとPgpool-IIの間の選択は、アプリケーションの特定のスケーラビリティ、可用性、および複雑さのニーズを慎重に評価することに要約されます。PgBouncerは、エレガントで高性能な接続プーリングを最小限のフットプリントで提供し、純粋な多重化に最適です。一方、Pgpool-IIは、高度なロードバランシング、高可用性、およびレプリケーション管理のための堅牢で多機能なミドルウェアを提供し、アプリケーションからかなりの複雑さを抽象化します。どちらも強力なツールですが、適切な選択は、PostgreSQLインフラストラクチャが最適なパフォーマンスを発揮するだけでなく、将来の需要にもスムーズに対応できるようにします。