PostgreSQL connection pool lab 的核心責任是讓讀者看到 connection pressure 如何從 application pool 傳到 PostgreSQL backend process。這篇承接 Connection Scaling 與 PgBouncer Config。

本文的驗收標準是：你能比較 direct connection 與 PgBouncer transaction pooling，取得 pg_stat_activity、PgBouncer SHOW POOLS、latency / error sample 與 failure note。

Baseline Direct Connections

Baseline direct connections 的核心責任是先看 application 直連 PostgreSQL 時的 backend 數。

1export DATABASE_URL="postgres://lab_admin:lab_admin_pw@localhost:54329/appdb?sslmode=disable"
2psql "$DATABASE_URL" -c "SELECT count(*) FROM pg_stat_activity WHERE datname = current_database();"

用多個 terminal 或簡單 workload 產生 idle connection：

1for i in 1 2 3 4 5; do
2  psql "$DATABASE_URL" -c "SELECT pg_sleep(10);" &
3done
4psql "$DATABASE_URL" -c "SELECT state, count(*) FROM pg_stat_activity WHERE datname = current_database() GROUP BY state;"

這一步證明每個 client session 會占用 PostgreSQL backend process。

Add PgBouncer

Add PgBouncer 的核心責任是把 client connection 與 server connection 拆開。以下 compose fragment 可加入 local lab：

 1  pgbouncer:
 2    image: edoburu/pgbouncer:latest
 3    environment:
 4      DB_HOST: postgres
 5      DB_USER: lab_admin
 6      DB_PASSWORD: lab_admin_pw
 7      DB_NAME: appdb
 8      POOL_MODE: transaction
 9      MAX_CLIENT_CONN: 100
10      DEFAULT_POOL_SIZE: 5
11    ports:
12      - "64329:5432"

啟動後設定 pooler URL：

1export POOL_URL="postgres://lab_admin:lab_admin_pw@localhost:64329/appdb?sslmode=disable"

Compare Pool Behavior

Compare pool behavior 的核心責任是觀察 client 多、server 少的效果。

1for i in $(seq 1 20); do
2  psql "$POOL_URL" -c "SELECT pg_sleep(1);" &
3done
4psql "$DATABASE_URL" -c "SELECT state, count(*) FROM pg_stat_activity WHERE datname = current_database() GROUP BY state;"

再進 PgBouncer admin console，實際命令依 image 設定調整：

1psql "postgres://lab_admin:lab_admin_pw@localhost:64329/pgbouncer?sslmode=disable" -c "SHOW POOLS;"

驗收重點是：client workload 增加時，PostgreSQL backend 數量被 pool size 控制，排隊發生在 pooler 層。

Pool Exhaustion

Pool exhaustion 的核心責任是看過載時的錯誤與等待。

1for i in $(seq 1 50); do
2  psql "$POOL_URL" -c "BEGIN; SELECT pg_sleep(5); COMMIT;" &
3done

觀察：

1psql "$DATABASE_URL" -c "SELECT count(*) FROM pg_stat_activity WHERE datname = current_database();"
2psql "postgres://lab_admin:lab_admin_pw@localhost:64329/pgbouncer?sslmode=disable" -c "SHOW POOLS;"

Pool exhaustion 的 evidence 包含 waiting clients、timeout、application latency 與 error message。這些要接到 production alert。

Failure Note

Failure note 的核心責任是把 lab 結果轉成 runbook。記錄三件事：

  1. Direct connection baseline backend 數。
  2. PgBouncer transaction pooling 下 server connection 數。
  3. Pool exhaustion 時的 latency / error / queue。

若 application 使用 session state、prepared statement、temp table 或 advisory lock，還要補 transaction pooling compatibility matrix。

下一步路由

完成本篇後，回到 Connection Pooler Comparison 做選型；要看 PgBouncer production 設定讀 PgBouncer Config。