PostgreSQLのスケーラビリティ向上：PgBouncer接続プーリングの実装

PostgreSQLはその堅牢性とACIDコンプライアンスで有名ですが、あらゆるエンタープライズグレードのリレーショナルデータベースと同様に、特に接続管理に関して、極度の負荷下では課題に直面します。トラフィックの多いアプリケーションが水平スケーリングを行うと、結果的になだれ込むような多数の同時接続がデータベースサーバーをすぐに飽和させ、高い遅延とリソースの枯渇を引き起こす可能性があります。

この記事は、PostgreSQL向けの主要なコネクションプーラーであるPgBouncerの実装に関する包括的なガイドです。ネイティブの接続処理がなぜ高負荷下で非効率なのかを探り、3つの主要なプーリングモードを定義し、構成とデプロイのための実践的な手順を提供することで、PostgreSQLデプロイメントのスケーラビリティとスループットを劇的に向上させることができるようにします。

ボトルネック：ネイティブPostgreSQL接続のオーバーヘッド

PostgreSQLは、接続ごとに専用のプロセスを使用するモデルを採用しています。これは非常に安定しており分離を保証しますが、ストレス下ではこのアーキテクチャが大きなオーバーヘッドをもたらします。

リソース消費: 新しい接続ごとに、サーバーは新しいバックエンドプロセスをフォークする必要があり、メモリとCPUリソースを消費します。何百、何千ものアイドル接続が不必要にRAMを占有します。
確立の遅延: 新しい接続の確立には、ネットワークハンドシェイク、認証、プロセス初期化が含まれ、特に頻繁に接続を開閉するアプリケーションのリクエストに対して測定可能な遅延を追加します。
スケーリングの限界: これらのリソース要件により、パフォーマンスが破綻する前にPostgreSQLサーバーが現実的に処理できる同時接続数には実質的な上限が課せられます。

PgBouncerの紹介：軽量プロキシ

PgBouncerは、クライアントアプリケーションとPostgreSQLデータベースサーバーの間に配置される軽量なプロキシサーバーとして機能します。その核となる機能は、PostgreSQLバックエンドへの開かれた接続数を固定数で維持し、一時的なアプリケーションクライアントのリクエストのためにこれらの接続をプールして再利用することです。

このアプローチは、2つの重要な利点をもたらします。

オーバーヘッドの削減: PostgreSQLサーバーは、PgBouncerによって維持される固定された接続プールのみを認識するため、着信クライアントリクエストに対するコストのかかる接続ごとのフォークサイクルがなくなります。
スループットの向上: 確立された接続を再利用することで、PgBouncerは認証と接続初期化の時間を最小限に抑え、その結果、アプリケーションのスループットが大幅に向上し、遅延が減少します。

PgBouncerのプーリングモードの理解

PgBouncerの効率は、選択されたプーリングモードに大きく依存します。PgBouncerは3つの基本的なモードを提供しており、それぞれが異なるアプリケーションアーキテクチャと並行処理のニーズに適しています。

1. セッションプーリング（`pool_mode = session`）

セッションプーリングはデフォルトであり、最も安全なモードです。クライアントが接続すると、PgBouncerはそのクライアントが切断するまでプールされたサーバー接続を専用に割り当てます。接続がプールに戻されるのは、クライアントが明示的にセッションを閉じたときだけです。

ユースケース: セッション固有の機能（例：プリペアドステートメント、一時テーブル、カスタム変数のためのSETコマンド）に大きく依存するアプリケーション。
長所: 最も安全で、すべてのPostgreSQL機能と完全に互換性があります。
短所: クライアントのアイドル時間中も接続が保持されるため、プーリング効率は最も低くなります。

2. トランザクションプーリング（`pool_mode = transaction`）

トランザクションプーリングは、特にステートレスAPIを使用する高トラフィックのWebアプリケーションで推奨されます。サーバー接続は、単一のトランザクション（BEGINからCOMMIT/ROLLBACKまで）の期間中のみクライアントに専任されます。トランザクションが終了するとすぐに、接続は再利用のためにプールに返され、待機中の別のクライアントに提供されます。

ユースケース: OLTPシステムやマイクロサービスで一般的な、短く頻繁なトランザクション。
長所: サーバーリソースの利用効率が非常に高い。
短所: アプリケーションがトランザクションを注意深く管理する必要があります。トランザクション間で、セッションレベルの状態変更（例：SET extra_float_digits = 3）が失われるか、他のクライアントに漏洩する可能性があります。

⚠️ トランザクションプーリングのベストプラクティス

pool_mode = transactionを使用する場合、pgbouncer.iniでserver_reset_query = DISCARD ALLを設定することが強く推奨されます。このコマンドは、接続がプールに戻されたときに、残存する可能性のあるセッション状態（一時テーブル、アドバイザリロック、シーケンス状態）が即座にクリアされることを保証し、次のクライアントに対するデータの漏洩や予期せぬ動作を防ぎます。

3. ステートメントプーリング（`pool_mode = statement`）

ステートメントプーリングは最も積極的なモードです。サーバー接続は、単一のステートメント実行ごとにプールに戻されます。このモードは、複数ステートメントトランザクションの使用を事実上禁止し、非常に制限的です。

ユースケース: トランザクションが明示的に禁止されているか、不要な、高度に特化した読み取り専用の負荷。
長所: 接続の再利用を最大化します。
短所: すべてのトランザクションを壊します。 トランザクションが使用されないことが保証されている環境でのみ適しています。

PgBouncerのセットアップと初期設定

1. インストール

PgBouncerは標準のディストリビューションリポジトリで利用できることがよくあります。

# Debian/Ubuntuの場合
sudo apt update && sudo apt install pgbouncer

# RHEL/CentOSの場合
sudo dnf install pgbouncer

2. 設定ファイル

PgBouncerは主に2つの設定ファイルに依存しており、通常は/etc/pgbouncer/に配置されています。

pgbouncer.ini: メイン設定。データベース、プール制限、動作モードを定義します。
userlist.txt: PgBouncerがPostgreSQLサーバーに認証するために使用するユーザーとパスワードを定義します。

3. ユーザーの定義（`userlist.txt`）

セキュリティのため、PgBouncerはPostgreSQLのpg_authidテーブルを直接読み取りません。認証できるユーザーを手動で定義する必要があります。このファイルは保護されていることを確認してください（例：pgbouncerユーザーが所有し、権限が制限されている）。

```text:userlist.txt
"app_user" "MD5HASH_OF_PASSWORD_OR_PLANTEXT"
"admin_user" "another_hash"

> 注意: 平文パスワードも可能ですが、`psql -c "SELECT md5('your_password')"`のようなツールを使用して生成したMD5ハッシュを使用する方が安全です。

### 4. `pgbouncer.ini`の設定

`pgbouncer.ini`ファイルは、プーラーの動作を定義します。以下は、トランザクションプーリングを使用した一般的なWebアプリケーションのセットアップに合わせて調整された例です。

```ini:pgbouncer.ini Snippet
[databases]
# クライアント接続文字列の定義:
# <データベース名> = host=<pg_サーバーIP> port=<pg_ポート> dbname=<db_名> user=<pgbouncer_認証ユーザー>
myappdb = host=10.0.0.5 port=5432 dbname=productiondb user=pgbouncer_service

[pgbouncer]

; リッスン設定
listen_addr = *
listen_port = 6432

; 認証設定
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; プーリングモード（アプリケーションのニーズに基づいて設定）
pool_mode = transaction
server_reset_query = DISCARD ALL

; 接続制限とサイズ
; PgBouncerへの最大合計クライアント接続数
max_client_conn = 1000

; PgBouncerがデータベースごとに開いておく最大接続数（プールのサイズ）
default_pool_size = 20

; すべてのデータベースでプール全体で許可される接続の最大数
max_db_connections = 100

; プールが枯渇した場合に予約するスロット数
reserve_pool_size = 5

; ロギングと管理
admin_users = postgres, admin_user
stats_users = postgres

モニタリングと管理

PgBouncerは、管理者がプーラーのステータス、統計、接続をリアルタイムで監視できるpgbouncerという擬似データベースを公開しています。定義されたadmin_usersのいずれかを使用して、PgBouncerのリッスンポート（例：6432）に接続します。

psql -p 6432 -U admin_user pgbouncer

主要な管理コマンド：

コマンド	説明	使用上の注意
`SHOW STATS;`	接続統計（リクエスト数、バイト数、合計実行時間）を表示します。	パフォーマンス分析に役立ちます。
`SHOW POOLS;`	設定されたすべてのデータベースのプールの状態を表示します。	`cl_active`、`sv_active`、`sv_idle`を監視します。
`SHOW CLIENTS;`	PgBouncerに接続されているすべてのクライアント接続をリストします。
`RELOAD;`	接続を中断せずに設定を再ロードしようとします。
`PAUSE;`	新しいクエリの受け入れを停止し、現在のトランザクションが終了するのを待ちます。	メンテナンスやPgBouncerのアップグレード前などに使用されます。

スケーリングのヒント

配置: 遅延を最小限に抑えるため、PgBouncerをアプリケーションと同じサーバー、または専用の、ネットワーク最適化されたマシンにインストールします。
プールサイジング: default_pool_sizeは妥当な数値（通常10〜50）に設定する必要があります。これは通常、PostgreSQLサーバー自体で許可される接続数よりもはるかに少なくなります。過剰なプールサイズは、プーリングの目的を無効にします。
クライアント制限: max_client_connを使用して、接続ストームがPgBouncer自体を飽和させるのを防ぎます。これは堅牢なフロントエンドスロットルとして機能します。

結論

PgBouncer接続プーリングの実装は、高並行環境におけるPostgreSQLのスケーラビリティを向上させるための最も影響力の大きい単一のステップであると言えます。接続管理を一元化し、効率的なプーリングモードを活用することにより、アプリケーションは接続オーバーヘッドを劇的に削減し、データベースサーバー上での安定したメモリ使用量を維持し、PostgreSQLの信頼性を損なうことなく、より高いリクエストスループットを達成できます。