PostgreSQLコネクションプール最適化実践：PgBouncerからPgpool-IIまで6つのプロダクションパターン
数据库
FATAL: sorry, too many connections — 深夜3時のアラートSMS
深夜3時、スマホがアラートSMSで爆撃される：FATAL: sorry, too many connections already。50のマイクロサービスインスタンス、各インスタンス20コネクション、しかしPostgreSQLのmax_connectionsは200しかない。コネクションストーム（Connection Storm）がデータベースに新規接続を拒否させ、システム全体が雪崩のように崩壊する。2026年、PostgreSQLコネクションプール最適化は高同時接続システムの命綱である — PgBouncerの3つのプーリングモードからPgpool-IIの負荷分散、Odysseyの高性能アーキテクチャからコネクション容量計画まで、正しい選択で単一サーバーで10万同時接続をサポートできる。
本記事では、PostgreSQLのコネクションモデルから出発し、6つのプロダクショングレードのコネクションプールパターンを、設定から監視、落とし穴から高度な最適化まで、フルスタックで実践する。
PostgreSQLコネクションプールコア概念
| 概念 | 説明 |
|---|---|
| コネクションプール（Connection Pool） | データベース接続を再利用し、頻繁な作成/破棄のオーバーヘッドを回避 |
| PgBouncer | 軽量PostgreSQLコネクションプーラー、Session/Transaction/Statementの3モードをサポート |
| トランザクションプーリング（Transaction Pooling） | トランザクション終了後にコネクションをプールに返却、最大再利用率 |
| セッションプーリング（Session Pooling） | クライアント切断時のみコネクションを解放、最高の互換性 |
| ステートメントプーリング（Statement Pooling） | 各SQL文の後にコネクションを解放、最高再利用率だが互換性は最低 |
| Pgpool-II | PostgreSQLミドルウェア、コネクションプール、負荷分散、読み書き分離をサポート |
| Odyssey | Yandex開発の高性能PostgreSQLコネクションプーラー、マルチDB マルチユーザールーティング対応 |
| max_connections | PostgreSQL最大接続数、各接続はOSプロセス、約10MBメモリ |
PostgreSQLプロセスモデルとコネクションオーバーヘッド
PostgreSQL Process-Per-Connection モデル：
┌─────────────┐ ┌──────────────┐
│ アプリケーション│────→│ Postmaster │ (メインプロセス、ポート待受)
└─────────────┘ └──────┬───────┘
│ fork()
┌──────▼───────┐
│ Backend │ (接続ごとに1プロセス)
│ Process │ ≈ 10MB メモリ
│ (PG_CONN_1) │ ≈ 1つのOSプロセス
└──────────────┘
┌──────────────┐
│ Backend │
│ Process │ 1000接続 ≈ 10GBメモリ
│ (PG_CONN_2) │ コンテキストスイッチのオーバーヘッド甚大
└──────────────┘
...N個
コネクションプーラー導入後：
┌─────────────┐ ┌──────────────┐ ┌──────────────┐
│ App (1000) │────→│ Pooler (20) │────→│ PostgreSQL │
│ 接続 │ │ 再利用 │ │ 実接続 │
└─────────────┘ └──────────────┘ └──────────────┘
問題分析：PostgreSQLコネクションプール最適化の5つの課題
- コネクションストーム：マイクロサービスの起動/再起動時に大量のインスタンスが同時接続、瞬時に
max_connectionsを使い切り、データベースが全新規接続を拒否 - コネクションリーク：アプリケーションがコネクションを正しく解放せず、アイドル接続が蓄積、プール枯渇で新リクエストがブロック
- モード選択の困難：PgBouncerの3モードにはそれぞれトレードオフがある — Sessionは接続を浪費、TransactionはSET/PREPAREと非互換、Statementは互換性が最低
- 容量計画の欠如：接続数上限を計算する公式がなく、経験則で設定するとリソースの無駄や接続不足を招く
- 監視の死角：コネクションプールメトリクスの監視が欠如、接続枯渇やキュータイムアウトの発見が遅すぎる
ステップバイステップ：6つのプロダクショングレードコネクションプールパターン
パターン1：PgBouncer Sessionモード設定
SessionモードはネイティブPostgreSQLの動作に最も近く、互換性が最も高い。セッション状態（SET変数、PREPARE文、一時テーブル）が必要なシナリオに適している。
; /etc/pgbouncer/pgbouncer.ini — Sessionモード設定
[databases]
; プロダクションデータベース接続
myapp_db = host=10.0.1.100 port=5432 dbname=myapp_production
; 読み取り専用レプリカ
myapp_readonly = host=10.0.1.101 port=5432 dbname=myapp_production
[pgbouncer]
; リスニングアドレスとポート
listen_addr = 0.0.0.0
listen_port = 6432
; Sessionモード：クライアント切断時のみコネクションを解放
pool_mode = session
; プールサイズ：データベース/ユーザーペアあたりの最大サーバー接続数
; Sessionモードでは、この値は >= クライアント同時接続数である必要がある
default_pool_size = 50
; 最小プールサイズ、接続の事前ウォーム
min_pool_size = 10
; 予約接続数、管理者の緊急アクセス用
reserve_pool_size = 5
reserve_pool_timeout = 3
; アイドル接続タイムアウト（秒）、長時間アイドルのサーバー接続を回収
idle_timeout = 600
; サーバー接続の最大生存時間（秒）、長期間接続の問題を防止
server_lifetime = 3600
; クライアントアイドルタイムアウト（秒）
client_idle_timeout = 0
; サーバーログインリトライタイムアウト（秒）
server_login_retry = 3
; 接続待機タイムアウト（秒）、プール満杯時のクライアント待機時間
query_timeout = 30
query_wait_timeout = 30
; 認証方式
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
; 管理インターフェース
admin_users = pgadmin
stats_users = pgmonitor
; ログ
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1
verbose = 0
# 認証ファイルの作成
echo '"myapp_user" "SCRAM-SHA-256$4096:...hash..."' > /etc/pgbouncer/userlist.txt
# PgBouncerの起動
pgbouncer -d /etc/pgbouncer/pgbouncer.ini
# Sessionモードの確認
psql -h 127.0.0.1 -p 6432 -U myapp_user myapp_db -c "SHOW pool_mode;"
# PythonアプリケーションからPgBouncerに接続（Sessionモード）
import psycopg2
from contextlib import contextmanager
POOL_CONFIG = {
"host": "10.0.1.50",
"port": 6432,
"database": "myapp_db",
"user": "myapp_user",
"password": "secure_password",
"connect_timeout": 5,
"options": "-c search_path=public"
}
@contextmanager
def get_connection():
conn = psycopg2.connect(**POOL_CONFIG)
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
with get_connection() as conn:
cur = conn.cursor()
cur.execute("SET LOCAL work_mem = '256MB'")
cur.execute("SELECT * FROM orders WHERE user_id = %s", (12345,))
print(cur.fetchall())
パターン2：PgBouncer Transactionモード — 高同時接続の最適解
Transactionモードはトランザクション終了後に直ちにコネクションをプールに返却する、高同時接続シナリオの最適な選択肢。1000のクライアントに必要な実際のデータベース接続はわずか20かもしれない。
; /etc/pgbouncer/pgbouncer.ini — Transactionモード設定
[databases]
myapp_db = host=10.0.1.100 port=5432 dbname=myapp_production
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
; Transactionモード：トランザクション終了時にコネクションを解放
pool_mode = transaction
; プールサイズ：Transactionモードではクライアント数よりはるかに小さくできる
; 経験則：pool_size = ((コア数 * 2) + 有効ディスク数)
; 16コア + SSD → 約 40-50 接続
default_pool_size = 40
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 3
; トランザクション内アイドルタイムアウト（秒）、アイドルトランザクションを防止
idle_transaction_timeout = 60
; アイドル接続タイムアウト
idle_timeout = 600
; サーバー接続の最大生存時間
server_lifetime = 1800
; サーバーヘルスチェック間隔（秒）
server_check_query = SELECT 1
server_check_delay = 30
; 重要：Transactionモードでの待機タイムアウト
query_timeout = 30
query_wait_timeout = 60
; クライアント最大接続数
max_client_conn = 2000
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgadmin
stats_users = pgmonitor
# Transactionモードのベストプラクティス：明示的トランザクション制御
import psycopg2
from psycopg2 import pool
threaded_pool = pool.ThreadedConnectionPool(
minconn=5,
maxconn=20,
host="10.0.1.50",
port=6432,
database="myapp_db",
user="myapp_user",
password="secure_password"
)
def transfer_funds(from_id, to_id, amount):
conn = threaded_pool.getconn()
try:
cur = conn.cursor()
cur.execute("BEGIN")
cur.execute(
"UPDATE accounts SET balance = balance - %s WHERE id = %s",
(amount, from_id)
)
cur.execute(
"UPDATE accounts SET balance = balance + %s WHERE id = %s",
(amount, to_id)
)
cur.execute("COMMIT")
except Exception as e:
cur.execute("ROLLBACK")
raise e
finally:
threaded_pool.putconn(conn)
def get_user_orders(user_id):
conn = threaded_pool.getconn()
try:
cur = conn.cursor()
cur.execute(
"SELECT id, status, total FROM orders WHERE user_id = %s",
(user_id,)
)
return cur.fetchall()
finally:
threaded_pool.putconn(conn)
-- Transactionモードで使用できない機能一覧
-- ❌ SET文（トランザクション終了後にリセットされる）
SET work_mem = '256MB'; -- トランザクション終了後に失われる
-- ❌ PREPARE文
PREPARE get_user AS SELECT * FROM users WHERE id = $1; -- トランザクション終了後に失われる
-- ❌ 一時テーブル
CREATE TEMP TABLE tmp_cache AS SELECT * FROM products; -- トランザクション終了後に失われる
-- ❌ LISTEN/NOTIFY
LISTEN channel_name; -- トランザクションを跨いで通知を受信できない
-- ✅ 正しいアプローチ：トランザクション内でSET LOCALを使用
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT * FROM large_table ORDER BY data;
COMMIT; -- work_memはこのトランザクション内でのみ有効
パターン3：Pgpool-II — 負荷分散と読み書き分離
Pgpool-IIはコネクションプールだけでなく、PostgreSQLミドルウェアであり、読み書き分離、負荷分散、自動フェイルオーバーをサポートする。
; /etc/pgpool-II/pgpool.conf — Pgpool-II設定
; リスニング設定
listen_addresses = '*'
port = 9999
unix_socket_directories = '/var/run/pgpool'
; バックエンドノード設定
backend_hostname0 = '10.0.1.100'
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALLOW_TO_FAILBACK'
backend_application_name0 = 'primary'
backend_hostname1 = '10.0.1.101'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILBACK'
backend_application_name1 = 'replica1'
backend_hostname2 = '10.0.1.102'
backend_port2 = 5432
backend_weight2 = 1
backend_flag2 = 'ALLOW_TO_FAILBACK'
backend_application_name2 = 'replica2'
; コネクションプール設定
connection_cache = on
num_init_children = 50
max_pool = 4
child_life_time = 300
connection_life_time = 0
child_max_connections = 0
; 負荷分散
load_balance_mode = on
; SELECTクエリは自動的にレプリカに分散
; 書き込み操作は自動的にプライマリにルーティング
; 読み書き分離
; デフォルト：プライマリが書き込みを処理、レプリカが読み取りを処理
; ヒントで正確に制御可能
; ヘルスチェック
health_check_period = 10
health_check_timeout = 5
health_check_user = 'pgpool_health'
health_check_password = 'health_check_pass'
health_check_max_retries = 3
; フェイルオーバー
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %H %P %m %M'
failback_command = '/etc/pgpool-II/failback.sh %d %h %p %D %H %P'
follow_master_command = '/etc/pgpool-II/follow_master.sh %d %h %p %D %H %P'
; 自動フェイルバック
auto_failback = off
failover_on_backend_error = off
; ログ
log_connections = on
log_disconnections = on
log_statement = off
log_per_node_statement = off
# Pgpool-II読み書き分離の確認
# 書き込み操作 → プライマリ
psql -h 127.0.0.1 -p 9999 -U myapp_user myapp_production -c \
"INSERT INTO orders (user_id, status) VALUES (1, 'pending');"
# 読み取り操作 → 自動的にレプリカに負荷分散
psql -h 127.0.0.1 -p 9999 -U myapp_user myapp_production -c \
"SELECT * FROM orders WHERE user_id = 1;"
# ヒントを使用してプライマリに強制ルーティング
psql -h 127.0.0.1 -p 9999 -U myapp_user myapp_production -c \
"/*NO_LOAD_BALANCE*/ SELECT * FROM orders WHERE user_id = 1;"
# ノードステータスの確認
psql -h 127.0.0.1 -p 9999 -U pgpool_admin -c "SHOW pool_nodes;"
# PythonアプリケーションからPgpool-IIを使用
import psycopg2
def get_pgpool_connection(read_only=False):
conn = psycopg2.connect(
host="10.0.1.50",
port=9999,
database="myapp_production",
user="myapp_user",
password="secure_password"
)
if read_only:
conn.set_session(readonly=True, autocommit=True)
return conn
# 書き込み操作
write_conn = get_pgpool_connection(read_only=False)
cur = write_conn.cursor()
cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
write_conn.commit()
write_conn.close()
# 読み取り操作（自動負荷分散）
read_conn = get_pgpool_connection(read_only=True)
cur = read_conn.cursor()
cur.execute("SELECT * FROM accounts WHERE id = 1")
print(cur.fetchall())
read_conn.close()
パターン4：Odyssey — 高性能コネクションプーラー
OdysseyはYandexが開発、高スループットシナリオ向けに設計され、マルチデータベース マルチユーザールーティングをサポート、PgBouncerより高性能。
; /etc/odyssey/odyssey.conf — Odyssey設定
storage myapp_production {
type remote
host 10.0.1.100
port 5432
}
storage myapp_replica {
type remote
host 10.0.1.101
port 5432
}
database default {
storage default
pool default {
pool_mode session
pool_size 0
}
}
database myapp_db {
storage myapp_production
pool myapp_pool {
pool_mode transaction
pool_size 50
pool_timeout 5000
; アイドルタイムアウト（ミリ秒）
idle_timeout 600000
; サーバー接続ライフタイム
server_lifetime 1800000
; 自動プリペアドステートメントサポート
discard_idle 600000
}
}
database myapp_readonly {
storage myapp_replica
pool readonly_pool {
pool_mode transaction
pool_size 30
pool_timeout 5000
}
}
; ユーザールーティングルール
user myapp_user {
authentication scramsha256
password "SCRAM-SHA-256$4096:...hash..."
; デフォルトルート：プライマリ
database default {
pool myapp_pool
}
; 読み取り専用ルート：レプリカ
database myapp_readonly {
pool readonly_pool
}
}
; リスニング設定
listen {
host 0.0.0.0
port 6432
}
; ログ
log_format "%p %t %l [%c %s] %m\n"
log_debug 0
log_config 0
; 統計
stats_interval 60
# Odysseyの起動
odyssey /etc/odyssey/odyssey.conf
# 接続テスト
psql -h 127.0.0.1 -p 6432 -U myapp_user myapp_db -c "SELECT version();"
# 読み取り専用接続
psql -h 127.0.0.1 -p 6432 -U myapp_user myapp_readonly -c "SELECT pg_is_in_recovery();"
# PythonアプリケーションからOdysseyを使用
import psycopg2
from psycopg2 import pool
class OdysseyConnectionManager:
def __init__(self):
self.write_pool = pool.ThreadedConnectionPool(
minconn=2, maxconn=10,
host="10.0.1.50", port=6432,
database="myapp_db",
user="myapp_user", password="secure_password"
)
self.read_pool = pool.ThreadedConnectionPool(
minconn=2, maxconn=20,
host="10.0.1.50", port=6432,
database="myapp_readonly",
user="myapp_user", password="secure_password"
)
def get_write_conn(self):
return self.write_pool.getconn()
def get_read_conn(self):
return self.read_pool.getconn()
def put_write_conn(self, conn):
self.write_pool.putconn(conn)
def put_read_conn(self, conn):
self.read_pool.putconn(conn)
manager = OdysseyConnectionManager()
# 書き込み操作
conn = manager.get_write_conn()
try:
cur = conn.cursor()
cur.execute("INSERT INTO events (type, data) VALUES (%s, %s)", ("click", "{}"))
conn.commit()
finally:
manager.put_write_conn(conn)
# 読み取り操作
conn = manager.get_read_conn()
try:
cur = conn.cursor()
cur.execute("SELECT COUNT(*) FROM events")
print(cur.fetchone())
finally:
manager.put_read_conn(conn)
パターン5：コネクションサイジング公式と容量計画
容量計画はコネクションプール最適化の基盤である — 公式なしでは手探り状態に陥る。
コネクションサイジング公式：
1. PostgreSQL最大接続数の計画：
max_connections = (CPUコア数 × 2) + 有効ディスク数
例：16 CPUコア + SSD（4ディスク相当）
max_connections = (16 × 2) + 4 = 36
プロダクション推奨：max_connections = 100（余裕を持たせる）、プーラーで実際の接続を制御
2. PgBouncerプールサイズ：
pool_size = CPUコア数 × 2 + 有効ディスク数
例：16コア + SSD
pool_size = 16 × 2 + 4 = 36 → 40に切り上げ
3. アプリケーション側プールサイズ（インスタンスごと）：
app_pool_size = pool_size / インスタンス数
例：40総接続 / 10インスタンス = 4/インスタンス
4. クライアント最大接続数：
max_client_conn = インスタンス数 × app_pool_size × 1.5
例：10 × 4 × 1.5 = 60
5. メモリ推定：
PostgreSQL合計メモリ ≈ max_connections × 10MB + shared_buffers
例：100 × 10MB + 4GB = 5GB
-- 現在の接続数分布を確認
SELECT
datname AS database,
usename AS username,
state,
COUNT(*) AS connection_count,
AVG(EXTRACT(EPOCH FROM (now() - query_start))) AS avg_query_duration_sec
FROM pg_stat_activity
WHERE datname IS NOT NULL
GROUP BY datname, usename, state
ORDER BY connection_count DESC;
-- 接続数の履歴トレンドを確認（pg_stat_statementsが必要）
SELECT
EXTRACT(HOUR FROM query_start) AS hour,
COUNT(*) AS query_count
FROM pg_stat_activity
WHERE query_start > now() - interval '24 hours'
GROUP BY hour
ORDER BY hour;
-- 接続設定の確認
SHOW max_connections;
SHOW superuser_reserved_connections;
-- アクティブ接続の割合を計算
SELECT
state,
COUNT(*) AS count,
ROUND(COUNT(*)::numeric / (SELECT COUNT(*) FROM pg_stat_activity WHERE datname IS NOT NULL) * 100, 2) AS percentage
FROM pg_stat_activity
WHERE datname IS NOT NULL
GROUP BY state
ORDER BY count DESC;
# コネクションサイジング計画ツール
def calculate_pool_config(cpu_cores, disk_type, instance_count, workload="mixed"):
effective_spindles = {
"hdd_1": 1, "hdd_4": 4, "hdd_8": 8,
"ssd": 4, "nvme": 8
}.get(disk_type, 4)
pg_max_connections = (cpu_cores * 2) + effective_spindles
pg_max_connections = max(pg_max_connections, 20)
pool_size = (cpu_cores * 2) + effective_spindles
if workload == "read_heavy":
pool_size = int(pool_size * 1.2)
elif workload == "write_heavy":
pool_size = int(pool_size * 0.8)
app_pool_per_instance = max(pool_size // instance_count, 2)
max_client_conn = int(instance_count * app_pool_per_instance * 1.5)
shared_buffers_gb = max(cpu_cores * 0.5, 2)
total_memory_gb = pg_max_connections * 0.01 + shared_buffers_gb
return {
"cpu_cores": cpu_cores,
"disk_type": disk_type,
"instance_count": instance_count,
"workload": workload,
"pg_max_connections": min(pg_max_connections + 20, 200),
"pgbouncer_pool_size": pool_size,
"pgbouncer_min_pool_size": max(pool_size // 4, 5),
"app_pool_per_instance": app_pool_per_instance,
"max_client_conn": max_client_conn,
"shared_buffers_gb": shared_buffers_gb,
"estimated_total_memory_gb": round(total_memory_gb, 1),
"pool_mode": "transaction" if workload in ("read_heavy", "mixed") else "session"
}
config = calculate_pool_config(
cpu_cores=16,
disk_type="ssd",
instance_count=10,
workload="mixed"
)
for key, value in config.items():
print(f"{key}: {value}")
パターン6：プロダクション監視とアラート設定
監視なしのコネクションプールは時限爆弾である — 包括的な監視とアラートはプロダクションデプロイのベースライン。
# prometheus-pgbouncer-exporter 設定
# /etc/prometheus/pgbouncer_exporter.yml
global:
scrape_interval: 15s
scrape_configs:
- job_name: 'pgbouncer'
static_configs:
- targets: ['10.0.1.50:9127']
metrics_path: /metrics
params:
pgbouncer_addr: ['10.0.1.50:6432']
# Prometheusアラートルール
# /etc/prometheus/rules/pgbouncer_alerts.yml
groups:
- name: pgbouncer_alerts
rules:
- alert: PgbouncerPoolExhausted
expr: pgbouncer_pools_server_active >= pgbouncer_pools_server_max
for: 2m
labels:
severity: critical
annotations:
summary: "PgBouncerプール枯渇"
description: "データベース {{ $labels.database }} のプールが満杯、アクティブ接続数が上限に到達"
- alert: PgbouncerClientWaitQueue
expr: pgbouncer_pools_client_active > pgbouncer_pools_server_active * 2
for: 5m
labels:
severity: warning
annotations:
summary: "PgBouncerクライアント待機キューが長すぎる"
description: "データベース {{ $labels.database }} のクライアント待機数がサーバーの2倍以上"
- alert: PgbouncerHighWaitTime
expr: pgbouncer_pools_client_wait_time_seconds > 5
for: 1m
labels:
severity: warning
annotations:
summary: "PgBouncer待機時間が長すぎる"
description: "データベース {{ $labels.database }} の待機時間が5秒を超過"
- alert: PostgreSQLTooManyConnections
expr: pg_stat_activity_count > 80
for: 5m
labels:
severity: warning
annotations:
summary: "PostgreSQL接続数が高すぎる"
description: "現在のアクティブ接続数 {{ $value }}、max_connections の上限に接近"
-- PgBouncer管理コマンド
-- psqlでPgBouncer管理データベースに接続
-- psql -h 127.0.0.1 -p 6432 -U pgadmin pgbouncer
-- プールステータスの確認
SHOW POOLS;
-- クライアント一覧の確認
SHOW CLIENTS;
-- サーバー一覧の確認
SHOW SERVERS;
-- データベース一覧の確認
SHOW DATABASES;
-- 統計情報の確認
SHOW STATS;
-- アクティブソケットの確認
SHOW ACTIVE_SOCKETS;
-- アイドル接続の強制解放
PAUSE myapp_db;
RESUME myapp_db;
-- 指定データベースの全接続を閉じる
KILL myapp_db;
#!/bin/bash
# /etc/pgbouncer/health_check.sh — PgBouncerヘルスチェックスクリプト
PGBOUNCER_HOST="127.0.0.1"
PGBOUNCER_PORT="6432"
PGBOUNCER_ADMIN_USER="pgadmin"
ALERT_WEBHOOK="https://hooks.slack.com/services/xxx"
check_pool_status() {
local result=$(psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT \
-U $PGBOUNCER_ADMIN_USER pgbouncer -c "SHOW POOLS;" -t 2>/dev/null)
if [ -z "$result" ]; then
send_alert "PgBouncerに接続できません、ダウンしている可能性"
return 1
fi
echo "$result" | while read line; do
active=$(echo "$line" | awk '{print $5}')
max_conn=$(echo "$line" | awk '{print $7}')
if [ -n "$active" ] && [ -n "$max_conn" ] && [ "$active" -ge "$max_conn" ]; then
send_alert "PgBouncerプール枯渇: active=$active, max=$max_conn"
fi
done
}
check_postgresql_connections() {
local conn_count=$(psql -h 10.0.1.100 -p 5432 -U monitor -d myapp_production \
-t -c "SELECT COUNT(*) FROM pg_stat_activity WHERE datname = 'myapp_production';" 2>/dev/null)
conn_count=$(echo $conn_count | tr -d ' ')
if [ "$conn_count" -gt 80 ]; then
send_alert "PostgreSQL接続数が高すぎる: $conn_count / 100"
fi
}
send_alert() {
local message="$1"
curl -s -X POST "$ALERT_WEBHOOK" \
-H "Content-Type: application/json" \
-d "{\"text\": \"[ALERT] $message\"}" > /dev/null
echo "$(date): $message" >> /var/log/pgbouncer_health.log
}
check_pool_status
check_postgresql_connections
落とし穴ガイド：5つのよくある罠
罠1：TransactionモードでSET文を使用する
-- ❌ 誤り：SETはトランザクション終了後に失われる
SET work_mem = '256MB';
SELECT * FROM large_table ORDER BY data;
-- 次のクエリで work_mem がデフォルトに戻る
-- ✅ 正しい：トランザクション内でSET LOCALを使用
BEGIN;
SET LOCAL work_mem = '256MB';
SELECT * FROM large_table ORDER BY data;
COMMIT;
罠2：アプリケーション側プールとPgBouncerの二重プーリング
# ❌ 誤り：大きなHikariCPプール + PgBouncer
# HikariCP: maximumPoolSize=50
# PgBouncer: default_pool_size=40
# 結果：50のクライアント接続が40のサーバー接続を争奪、大量の待機発生
# ✅ 正しい：小さなアプリプール + PgBouncer
# HikariCP: maximumPoolSize=5（インスタンスごと）
# PgBouncer: default_pool_size=40
# 10インスタンス × 5接続 = 50クライアント接続、40サーバー接続で十分
# HikariCP設定（PgBouncer Transactionモードと組み合わせ）
spring.datasource.hikari.maximum-pool-size=5
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.connection-timeout=3000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-test-query=SELECT 1
罠3：PgBouncer server_lifetimeが長すぎる
; ❌ 誤り：server_lifetimeが長すぎると、サーバー接続に古いセッション状態が残る可能性
server_lifetime = 86400
; ✅ 正しい：適切なserver_lifetimeで定期的に接続をリフレッシュ
server_lifetime = 1800
; server_check_queryと組み合わせて定期的にヘルスチェック
server_check_query = SELECT 1
server_check_delay = 30
罠4：superuser_reserved_connectionsの無視
-- ❌ 誤り：max_connections=100、スーパーユーザー予約接続なし
-- 接続が枯渇すると、管理者すらログインできない
-- ✅ 正しい：スーパーユーザー予約接続を設定
ALTER SYSTEM SET superuser_reserved_connections = 3;
SELECT pg_reload_conf();
-- 緊急時にスーパーユーザー接続を使用
psql -h 10.0.1.100 -U postgres -d myapp_production -c \
"SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'idle_app' AND state = 'idle';"
罠5：Pgpool-IIヘルスチェックユーザーの権限不足
-- ❌ 誤り：通常ユーザーでヘルスチェックを実行
-- CREATE USER health_check WITH PASSWORD 'pass';
-- ✅ 正しい：ヘルスチェックユーザーに十分な権限を付与
CREATE USER pgpool_health WITH PASSWORD 'health_check_pass' LOGIN;
GRANT pg_monitor TO pgpool_health;
-- または最小権限
GRANT CONNECT ON DATABASE myapp_production TO pgpool_health;
エラートラブルシューティング表
| エラーメッセージ | 原因 | 解決策 |
|---|---|---|
FATAL: sorry, too many connections already |
接続数がmax_connectionsを超過 |
プーラーを追加、default_pool_sizeを削減、コネクションリークを確認 |
FATAL: remaining connection slots are reserved for non-replication superuser connections |
通常ユーザーの接続数が上限に到達 | max_connectionsを増やすかスーパーユーザーでアイドル接続をクリーンアップ |
FATAL: no pg_hba.conf entry for host |
クライアントIPがホワイトリストにない | pg_hba.confにクライアントIPまたはプーラーサーバーIPを追加 |
FATAL: password authentication failed for user |
認証情報のエラー | PgBouncerのuserlist.txtまたはauth_query設定を確認 |
ERROR: connection not open |
PgBouncerがPostgreSQLに接続できない | バックエンドデータベース設定、ネットワーク、PostgreSQLの稼働状態を確認 |
ERROR: query_wait_timeout |
クライアントの接続待機がタイムアウト | query_wait_timeoutまたはdefault_pool_sizeを増やす |
ERROR: idle_transaction_timeout |
トランザクション内のアイドル時間が長すぎる | アプリにコミットされていない長いトランザクションがないか確認、idle_transaction_timeoutを増やす |
ERROR: server_login_retry failed |
PgBouncerのPostgreSQL再接続に失敗 | PostgreSQLの到達可能性、pg_hba.confが接続を許可しているか確認 |
ERROR: prepared statement "s1" does not exist |
TransactionモードでPREPARE文が失われる | Sessionモードに切り替えまたはprepared_statement_cache_size=0を使用 |
ERROR: cannot drop temporary table because it is being used |
Transactionモードで一時テーブルがトランザクションをまたいで使用されている | Sessionモードに切り替えまたは一時テーブルの代わりに永久テーブルを使用 |
高度な最適化
1. PgBouncer auth_query動的認証
; /etc/pgbouncer/pgbouncer.ini — 動的認証設定
[databases]
myapp_db = host=10.0.1.100 port=5432 dbname=myapp_production auth_query="SELECT usename, passwd FROM pg_shadow WHERE usename=$1"
[pgbouncer]
auth_type = md5
; userlist.txtを維持する必要がなく、PostgreSQLに直接クエリしてパスワードを取得
; ユーザーの頻繁な変更があるシナリオに適している
-- auth_query用の読み取り専用ユーザーを作成
CREATE USER pgbouncer_auth WITH PASSWORD 'auth_query_pass' LOGIN;
GRANT pg_monitor TO pgbouncer_auth;
-- pg_hba.confでpgbouncer_authの接続を許可するよう設定
-- host myapp_production pgbouncer_auth 10.0.1.50/32 md5
2. マルチティアコネクションプールアーキテクチャ
マルチティアコネクションプールアーキテクチャ設計：
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ Appインスタンス1│ │ Appインスタンス2│ │ Appインスタンス3│
│ HikariCP(5) │ │ HikariCP(5) │ │ HikariCP(5) │
└──────┬───────┘ └──────┬───────┘ └──────┬───────┘
│ │ │
└────────┬────────┴────────┬────────┘
│ │
┌────────▼────────┐ ┌─────▼──────────┐
│ PgBouncer │ │ PgBouncer │
│ (書き-Session) │ │ (読み-Transact)│
│ pool_size=20 │ │ pool_size=40 │
│ :6432 │ │ :6433 │
└────────┬────────┘ └──────┬──────────┘
│ │
┌────────▼────────┐ ┌─────▼──────────┐
│ PostgreSQL │ │ PostgreSQL │
│ Primary │ │ Replica ×2 │
│ max_conn=100 │ │ max_conn=100 │
└─────────────────┘ └─────────────────┘
主要設計：
- 書き込み操作：Sessionモード（PREPARE/SETと互換）
- 読み取り操作：Transactionモード（最大再利用率）
- 小さなアプリプール + 大きなPgBouncerプール
- コネクションプール層で読み書き分離を完了
3. コネクションプールの自動スケーリング
# 負荷ベースのコネクションプール自動スケーリング
import psycopg2
import time
import logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger("pool_autoscaler")
class PoolAutoScaler:
def __init__(self, pgbouncer_host, pgbouncer_port, admin_user, admin_db="pgbouncer"):
self.conn_params = {
"host": pgbouncer_host,
"port": pgbouncer_port,
"user": admin_user,
"database": admin_db
}
self.min_pool = 10
self.max_pool = 100
self.scale_up_threshold = 0.8
self.scale_down_threshold = 0.3
def get_pool_stats(self):
conn = psycopg2.connect(**self.conn_params)
cur = conn.cursor()
cur.execute("SHOW POOLS;")
pools = cur.fetchall()
cur.execute("SHOW DATABASES;")
databases = cur.fetchall()
conn.close()
return pools, databases
def calculate_utilization(self, active, total):
if total == 0:
return 0
return active / total
def scale_pool(self, database, new_size):
new_size = max(self.min_pool, min(self.max_pool, new_size))
conn = psycopg2.connect(**self.conn_params)
cur = conn.cursor()
cur.execute(f"SET default_pool_size = {new_size} FOR {database};")
conn.close()
logger.info(f"Pool scaled: {database} → {new_size}")
def run(self):
pools, databases = self.get_pool_stats()
for pool in pools:
database = pool[0]
active = pool[4] if len(pool) > 4 else 0
total = pool[6] if len(pool) > 6 else 1
utilization = self.calculate_utilization(active, total)
if utilization > self.scale_up_threshold:
new_size = int(total * 1.5)
self.scale_pool(database, new_size)
elif utilization < self.scale_down_threshold:
new_size = int(total * 0.7)
self.scale_pool(database, new_size)
if __name__ == "__main__":
scaler = PoolAutoScaler("10.0.1.50", 6432, "pgadmin")
while True:
try:
scaler.run()
except Exception as e:
logger.error(f"Auto-scaler error: {e}")
time.sleep(60)
コネクションプールソリューション比較
| 特徴 | PgBouncer | Pgpool-II | Odyssey | PostgreSQL内蔵 | SQLAlchemy Pool |
|---|---|---|---|---|---|
| プーリングモード | Session/Transaction/Statement | Session/Transaction | Session/Transaction | なし | Session |
| 読み書き分離 | ❌ 外部設定が必要 | ✅ 内蔵 | ✅ 設定ルーティング | ❌ | ❌ |
| 負荷分散 | ❌ | ✅ 内蔵 | ✅ 設定ルーティング | ❌ | ❌ |
| フェイルオーバー | ❌ | ✅ 内蔵 | ❌ | ❌ | ❌ |
| パフォーマンス | 非常に高い（C言語） | 高い（C言語） | 非常に高い（C言語） | N/A | 中（Python層） |
| マルチDBルーティング | ✅ | ✅ | ✅ | ❌ | ❌ |
| メモリ使用量 | 非常に低い（~2MB） | 中程度 | 低い | N/A | 低い |
| デプロイ複雑さ | 低い | 高い | 中程度 | なし | なし |
| ユースケース | ほとんどのシナリオ | RW分離+LBが必要な場合 | 超高同時+マルチテナント | 開発/テスト | 単一Pythonアプリ |
| PREPAREサポート | Sessionモード | ✅ | ✅ | ✅ | ✅ |
| 推奨度 | ⭐⭐⭐⭐⭐ | ⭐⭐⭐⭐ | ⭐⭐⭐⭐ | ⭐⭐ | ⭐⭐⭐ |
まとめ
PostgreSQLコネクションプール最適化の重要ポイント：1) Process-Per-Connectionモデルのオーバーヘッドを理解する — 各接続 ≈ 10MBメモリ；2) 高同時接続シナリオではPgBouncer Transactionモードを推奨 — 1000クライアントに必要な実接続はわずか20-40；3) 読み書き分離+負荷分散が必要ならPgpool-II；4) 超高同時+マルチテナントならOdyssey；5) コネクションサイジング公式：
pool_size = CPUコア数 × 2 + 有効ディスク数；6) 小さなアプリプール+大きなPgBouncerプール、二重プーリングを回避；7) プロダクションには監視とアラートが必須 — プール枯渇はP0インシデント；8)superuser_reserved_connectionsを予約し、接続枯渇時のログイン不能を防止。
おすすめツール
- JSONフォーマッター — PgBouncer/Pgpool-II設定JSONのフォーマット
- ハッシュジェネレーター — SCRAM-SHA-256認証パスワードハッシュの生成
- Base64エンコード/デコード — PostgreSQL接続文字列の認証情報のデコード
ブラウザローカルツールを無料で試す →
#PostgreSQL#连接池#PgBouncer#Odyssey#性能优化#2026#数据库