Connection pooling

ปัญหา production จำนวนมากอย่างน่าประหลาดใจย้อนกลับไปที่ข้อเท็จจริงข้อเดียว: ใน PostgreSQL ทุก client connection มี process ระดับระบบปฏิบัติการของตัวเองบนเซิร์ฟเวอร์ไม่กี่ connection แทบไม่มีต้นทุน แต่ไม่กี่พัน connection กินหน่วยความจำและ overhead การจัดตารางเวลามหาศาล และเซิร์ฟเวอร์ช้าลงมากก่อนจะหมดงานที่มีประโยชน์ให้ทำ

Connection pooling แก้ปัญหานี้โดยวางตัวกลางเล็ก ๆ ระหว่างแอปกับฐานข้อมูล pooler เก็บ server connection จริงจำนวนพอประมาณเปิดไว้และแจกให้ client ตามต้องการ ฐานข้อมูลจึงจัดการกับ connection ไม่กี่อันเสมอไม่ว่ามี client กี่ตัว

ทุกอย่างด้านล่างรันจริงบน PostgreSQL 18.6 และ PgBouncer 1.26.0 (aarch64, Docker Desktop บน Mac, กันยายน 2026) แล็บคือสามคอนเทนเนอร์บนเครือข่าย Docker --internal — ฐานข้อมูลที่จำกัด 768 MB และ 4 CPU คอนเทนเนอร์ client สำหรับ psql และ pgbench และ PgBouncer — ตั้งชื่อ postgresql-b3-* ทั้งหมดและลบเมื่อจบ โปรเจกต์ PgBouncer ไม่เผยแพร่ image ทางการ แล็บจึง build เองจาก apt repository ของ PostgreSQL <your-password> แทนค่าใดก็ได้ที่คุณเลือก ตัวเลข throughput มาจาก Mac เครื่องนี้ และมีแค่สัดส่วนที่ติดตัวไป การทดลองกำหนดขนาด pool วัดซ้ำสามครั้งเพื่อแสดง noise

ทำไม connection ถึงหนัก

เมื่อ client เชื่อมต่อ postmaster ของ PostgreSQL fork backend process เฉพาะเพื่อรับใช้มัน process นั้นถือหน่วยความจำสำหรับ cache, sort buffer และการจัดการภายใน และระบบปฏิบัติการต้องจัดตารางเวลามันร่วมกับ backend อื่นทุกตัว connection ที่ว่างอยู่ก็ยังครอบครอง process 60 client เชื่อมต่อตรงแล้วนั่งเฉย ๆ:

# 60 sessions connect directly and sit there (each one runs pg_sleep(15))

-- database: script
SELECT count(*) AS app_sessions FROM pg_stat_activity
WHERE backend_type = 'client backend' AND usename = 'app';
-- database: psql output
 app_sessions
--------------
 61


$ docker exec postgresql-b3-db sh -c 'ps -eo args | grep -c "^postgres: app shop"'
61

$ docker exec postgresql-b3-db sh -c 'ps -eo pid,args | grep "postgres: app shop" | head -3'
114 postgres: app shop 172.22.0.4(55742) idle
  115 postgres: app shop 172.22.0.3(54550) SELECT
  116 postgres: app shop 172.22.0.3(54564) SELECT

$ docker exec postgresql-b3-db sh -c '(sum RssAnon from /proc/<pid>/status for each backend)'
backends=62 private_memory_total_kB=188464 average_kB=3039

61 session, 61 process ระบบปฏิบัติการชื่อ postgres: app shop <client address> <state> (60 อันตรงบวก connection ว่างหนึ่งอันที่ PgBouncer เก็บไว้) และแต่ละอันถือหน่วยความจำส่วนตัว (RssAnon) ราว 3 MB แม้ว่างอยู่ คูณด้วยหนึ่งพัน connection สามกิกะไบต์หายไปก่อน query แรกจะรัน เพดานปริยายปกป้องเซิร์ฟเวอร์:

# 1. an internal network, a memory-capped database, and a client container for psql and pgbench

$ docker network create --internal postgresql-b3-net

$ docker run -d --name postgresql-b3-db --network postgresql-b3-net --memory 768m --cpus 4 -e POSTGRES_PASSWORD=<your-password> -v postgresql-b3-db-data:/var/lib/postgresql postgres:18.6

$ docker run -d --name postgresql-b3-client --network postgresql-b3-net --memory 1g --cpus 2 --entrypoint sleep postgres:18.6 infinity

-- database: script
CREATE ROLE app LOGIN PASSWORD '<your-password>';
CREATE DATABASE shop OWNER app;
SHOW max_connections;
SHOW superuser_reserved_connections;
-- database: psql output
CREATE ROLE
CREATE DATABASE
 max_connections
-----------------
 100
(1 row)

 superuser_reserved_connections
--------------------------------
 3
(1 row)

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client pgbench -h postgresql-b3-db -U app -i -s 10 -q shop
creating primary keys...
done in 0.79 s (drop tables 0.00 s, create tables 0.00 s, client-side generate 0.58 s, vacuum 0.07 s, primary keys 0.14 s).

# 2. PgBouncer: built from the PostgreSQL apt repository (the PgBouncer project publishes no official image)

$ docker build -t postgresql-b3-pgbouncer:local .

$ docker run --rm --entrypoint pgbouncer postgresql-b3-pgbouncer:local --version | head -1
PgBouncer 1.26.0

$ docker logs postgresql-b3-pgbouncer 2>&1 | tail -3 | cut -c1-140
2026-09-30 08:32:34.457 UTC [1] LOG listening on 0.0.0.0:6432
2026-09-30 08:32:34.459 UTC [1] LOG listening on unix:/tmp/.s.PGSQL.6432
2026-09-30 08:32:34.459 UTC [1] LOG process up: PgBouncer 1.26.0, libevent 2.1.13-stable (epoll), adns: c-ares 1.34.5, tls: OpenSSL 3.5.7 9

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client psql -h postgresql-b3-pgbouncer -p 6432 -U app -d shop -Atc "SELECT current_user, inet_server_addr()"
app|172.22.0.2

max_connections คือ 100 และสามอันสำรองไว้ให้ superuser ขอมากกว่านั้นไม่ได้ต่อคิว แต่ล้มเหลว:

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client pgbench -h postgresql-b3-db -U app -S -c 10 -j 4 -T 6 shop
latency average = 0.139 ms
tps = 71709.884824 (without initial connection time)

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client sh -c "seq 200 | xargs -P 200 -I{} psql -h postgresql-b3-db -U app -d shop -Atc 'SELECT pg_sleep(8)' 2>&1 | sort | uniq -c | sort -rn | head -3"    # 200 direct sessions at once
     96
     48 psql: error: connection to server at "postgresql-b3-db" (172.22.0.2), port 5432 failed: FATAL:  sorry, too many clients already
     42 psql: error: connection to server at "postgresql-b3-db" (172.22.0.2), port 5432 failed: FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client psql -h postgresql-b3-pgbouncer -p 6432 -U app -d pgbouncer -x -c "SHOW POOLS"    # while the 200-client run is going; the record for database shop, selected rows
database              | shop
user                  | app
cl_active             | 16
cl_waiting            | 184
sv_active             | 10
sv_idle               | 0
pool_mode             | transaction

-- database: script
SELECT count(*) AS server_connections FROM pg_stat_activity
WHERE backend_type = 'client backend' AND datname = 'shop';
-- database: psql output
 server_connections
--------------------
 10


$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client pgbench -h postgresql-b3-pgbouncer -p 6432 -U app -S -c 200 -j 4 -T 8 shop
latency average = 3.542 ms
tps = 56466.776538 (without initial connection time)

บล็อกแรกคือการรันตรง: 10 client ได้ราว 72 000 transaction select-only ต่อวินาที จากนั้น 200 session เริ่มพร้อมกัน: 96 เข้าได้ และราว 90 ถูกปฏิเสธ ด้วย FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute (42 อัน) หรือ FATAL: sorry, too many clients already (48 อัน) ขึ้นกับว่าเซิร์ฟเวอร์เต็มแค่ไหนตอนแต่ละอันมาถึง การเพิ่ม max_connections ไม่ทำให้ปัญหาหายไป — แค่ให้คุณสร้าง process ที่แพงขึ้นได้มากขึ้น

flowchart LR
  Cli[Many app clients] -->|hundreds of connections| PG[(PostgreSQL)]
  PG --> B1[Backend process]
  PG --> B2[Backend process]
  PG --> B3[Backend process]
  PG --> B4[Backend process]
  PG --> B5[Backend process]

ถ้าไม่มี pooling แต่ละ client ได้ server process ของตัวเอง

Pooler ตรงกลาง

pooler เช่น PgBouncer อยู่ระหว่าง client กับเซิร์ฟเวอร์ client เชื่อมต่อกับ pooler ซึ่งคุยด้วยถูก และ pooler เก็บชุดเล็ก ๆ ของ connection จริงไปยัง PostgreSQL เมื่อ client ต้องการฐานข้อมูล pooler ให้ยืม server connection แล้วเอาคืนเพื่อให้ client อื่นใช้ซ้ำ

flowchart LR
  Cli[Thousands of clients] -->|cheap client connections| Pool[PgBouncer pooler]
  Pool -->|few reused server connections| PG[(PostgreSQL)]

หลาย client แชร์ server connection ไม่กี่อันผ่าน pooler

บล็อกสุดท้ายข้างบนคือ 200 client เดิม ตอนนี้ผ่าน PgBouncer ด้วย pool ขนาด 10 ระหว่างการรัน SHOW POOLS ใน admin console แสดง 16 client อยู่ใน transaction จริง 184 รอคิว 10 server connection ทำงาน และ pg_stat_activity บนฐานข้อมูลยืนยัน server connection พอดี 10 ทั้ง 200 client ได้รับบริการที่ราว 56 000 transaction ต่อวินาที latency เฉลี่ย 3.5 ms ไม่มี client ใดถือ server connection ขณะว่าง

โหมด pooling

PgBouncer มีสามโหมดที่ต่างกันที่ นานแค่ไหน ที่ client ถือ server connection ที่ยืมไป (docs)

โหมด	Server connection ถูกคืนหลัง…	หมายเหตุ
Session	client ตัดการเชื่อมต่อ	ปลอดภัยที่สุด แต่ถือ connection ตลอด session
Transaction	แต่ละ transaction commit หรือ rollback	ตัวเลือกที่พบบ่อย; reuse ดีมากแต่มีข้อควรระวัง
Statement	แต่ละ statement	ก้าวร้าวที่สุด; ห้าม transaction หลาย statement

Transaction mode เป็นตัวเลือกยอดนิยม server connection ถูกยืมเฉพาะช่วงหนึ่ง transaction ระหว่าง transaction จึงว่างให้คนอื่น ให้การใช้ซ้ำยอดเยี่ยมกับทราฟฟิกเว็บทั่วไปที่แต่ละ request เป็น transaction สั้น ๆ ข้อควรระวังคือทุกอย่างที่ผูกกับ session ไม่ใช่ transaction พังได้ เพราะ transaction ถัดไปอาจไปตกที่ server connection อื่น ตารางความสามารถของ PgBouncer ระบุว่าอะไรใช้ไม่ได้ใน transaction mode “by design”: SET/RESET, LISTEN, cursor WITH HOLD, PREPARE/DEALLOCATE, LOAD และ advisory lock ระดับ session (docs)

pgbouncer.ini ขั้นต่ำ

PgBouncer ตั้งค่าด้วยไฟล์ INI เล็ก ๆ สิ่งจำเป็นคือมันเป็นหน้าด่านให้ฐานข้อมูลใด ฟังที่ไหน และโหมด pooling นี่คือไฟล์ที่แล็บใช้; รายการ shopN คือฐานข้อมูลเดียวกันที่ขนาด pool ต่างกัน สำหรับการทดลองด้านล่าง userlist.txt เก็บ SCRAM verifier ของ role app และ admin_users ให้ app เปิด admin console (ฐานข้อมูลจำลอง pgbouncer)

[databases]
shop   = host=postgresql-b3-db port=5432 dbname=shop
shop1  = host=postgresql-b3-db port=5432 dbname=shop pool_size=1
shop1b = host=postgresql-b3-db port=5432 dbname=shop pool_size=1
shop2  = host=postgresql-b3-db port=5432 dbname=shop pool_size=2
shop4  = host=postgresql-b3-db port=5432 dbname=shop pool_size=4
shop30 = host=postgresql-b3-db port=5432 dbname=shop pool_size=30
shop60 = host=postgresql-b3-db port=5432 dbname=shop pool_size=60

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = app
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 10
max_prepared_statements = 0
ignore_startup_parameters = extra_float_digits

max_client_conn คือจำนวน client ที่เชื่อมต่อ PgBouncer ได้ ส่วน default_pool_size คือจำนวน connection จริงไป PostgreSQL ที่มันเก็บต่อฐานข้อมูลและผู้ใช้ (pool_size= ต่อฐานข้อมูลเขียนทับมัน) แล็บ build และเริ่มทุกอย่างแบบนี้:

# Dockerfile: PgBouncer from the PostgreSQL apt repository that the postgres image already uses
FROM postgres:18.6
RUN apt-get update && apt-get install -y --no-install-recommends pgbouncer && rm -rf /var/lib/apt/lists/*
USER postgres

docker network create --internal postgresql-b3-net
docker volume create postgresql-b3-db-data
docker run -d --name postgresql-b3-db --network postgresql-b3-net --memory 768m --cpus 4 \
  -e POSTGRES_PASSWORD=<your-password> -v postgresql-b3-db-data:/var/lib/postgresql postgres:18.6
docker run -d --name postgresql-b3-client --network postgresql-b3-net --memory 1g --cpus 2 \
  --entrypoint sleep postgres:18.6 infinity

docker build -t postgresql-b3-pgbouncer:local .
docker create --name postgresql-b3-pgbouncer --memory 128m -p 127.0.0.1:6432:6432 \
  --entrypoint pgbouncer postgresql-b3-pgbouncer:local /etc/pgbouncer/pgbouncer.ini
docker network connect postgresql-b3-net postgresql-b3-pgbouncer
docker cp pgbouncer.ini postgresql-b3-pgbouncer:/etc/pgbouncer/pgbouncer.ini
docker cp userlist.txt  postgresql-b3-pgbouncer:/etc/pgbouncer/userlist.txt
docker start postgresql-b3-pgbouncer

อะไรพังใน transaction mode

เพราะ client ไม่ถือ server connection ระหว่าง transaction สถานะ session อาจรั่วระหว่าง client หรือหายไป สามกรณี รันบน alias shop1 ที่ pool มี server connection เดียวพอดี ทุก client จึงแชร์มันแน่นอน (กับ pool ที่ใหญ่กว่า bug เดียวกันเกิดเป็นช่วง ๆ ซึ่งแย่กว่า)

# each command is a separate client connection; the database alias shop1 has pool_size=1, so every client shares ONE server connection

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client psql -h postgresql-b3-pgbouncer -p 6432 -U app -d shop1 -c "SET app.tenant = 'alice'"
SET

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client psql -h postgresql-b3-pgbouncer -p 6432 -U app -d shop1 -c "SHOW app.tenant"    # a different client
 app.tenant
------------
 alice
(1 row)

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client psql -h postgresql-b3-db -U app -d shop -c "SHOW app.tenant"    # a direct connection, for comparison
ERROR:  unrecognized configuration parameter "app.tenant"

# the transaction-local version: shop1b is a separate one-connection pool

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client psql -h postgresql-b3-pgbouncer -p 6432 -U app -d shop1b   (script below)
BEGIN
 set_config
------------
 bob
(1 row)

 app.tenant
------------
 bob
(1 row)

COMMIT

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client psql -h postgresql-b3-pgbouncer -p 6432 -U app -d shop1b -c "SELECT current_setting('app.tenant', true) AS tenant_seen_by_next_client"
 tenant_seen_by_next_client
----------------------------

(1 row)

# session-level advisory locks

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client psql -h postgresql-b3-pgbouncer -p 6432 -U app -d shop1 -c "SELECT pg_advisory_lock(42)"
 pg_advisory_lock
------------------

(1 row)

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client psql -h postgresql-b3-pgbouncer -p 6432 -U app -d shop1 -c "SELECT pg_try_advisory_lock(42) AS another_client_gets_it"
 another_client_gets_it
------------------------
 t
(1 row)

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client psql -h postgresql-b3-db -U app -d shop -c "SELECT pg_try_advisory_lock(42) AS direct_client_gets_it"
 direct_client_gets_it
-----------------------
 f
(1 row)

-- database: script
SELECT locktype, objid, mode, granted FROM pg_locks WHERE locktype = 'advisory';
-- database: psql output
 locktype | objid |     mode      | granted
----------+-------+---------------+---------
 advisory |    42 | ExclusiveLock | t
(1 row)

SET รั่ว client หนึ่งรัน SET app.tenant = 'alice' แล้วตัดการเชื่อมต่อ; client อื่นที่เพิ่งเชื่อมต่ออ่านได้ alice การเชื่อมต่อตรงไม่รู้เรื่องอะไร รูปแบบที่ปลอดภัยคือ SET LOCAL หรือ set_config(name, value, true) ใน transaction: ค่าอยู่จนกว่า transaction จบ และ client ถัดไปเห็นค่าว่าง

Advisory lock เป็นของ server connection client หนึ่งถือ pg_advisory_lock(42) แล้วจากไป client ที่สองขอ lock เดียวกันและได้ (t) เพราะมันคือ backend เดียวกัน ขณะที่การเชื่อมต่อตรงถูกปฏิเสธ (f) และ pg_locks แสดงหนึ่ง lock ที่ไม่มีเจ้าของชัดเจน ใช้ pg_advisory_xact_lock ใน transaction mode

Prepared statement prepared statement ระดับโปรโตคอลคือสิ่งที่ driver ส่วนใหญ่ใช้โดย default และ prepared statement อยู่บน server connection เดียว ก่อน PgBouncer 1.21 นั่นทำให้ใช้ใน transaction mode ไม่ได้; ตั้งแต่นั้น PgBouncer ติดตามมันและ prepare ใหม่บน server connection ที่ client ไปตก ถ้าคุณให้ที่ว่างด้วย max_prepared_statements (docs) ค่า default ใน 1.26 คือ 200 ไฟล์ในแล็บตั้งเป็น 0 เพื่อแสดงความล้มเหลว pgbench -M prepared ใช้ extended query flow ระดับโปรโตคอล:

# max_prepared_statements = 0: protocol-level prepared statements are not supported in transaction mode

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client sh -c 'psql -h postgresql-b3-pgbouncer -p 6432 -U app -d pgbouncer -Atc "SHOW CONFIG" | grep ^max_prepared_statements | cut -d"|" -f1,2'
max_prepared_statements|0

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client pgbench -h postgresql-b3-pgbouncer -p 6432 -U app -M prepared -c 20 -j 4 -T 4 -S shop    # distinct error messages, with counts
ERROR:  prepared statement "P_0" already exists  (x13)
ERROR:  prepared statement "P_0" does not exist  (x17)
pgbench: error: Run was aborted; the above results are incomplete.

# max_prepared_statements = 100 in pgbouncer.ini, then RELOAD from the admin console

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client sh -c 'psql -h postgresql-b3-pgbouncer -p 6432 -U app -d pgbouncer -Atc "SHOW CONFIG" | grep ^max_prepared_statements | cut -d"|" -f1,2'
max_prepared_statements|100

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client pgbench -h postgresql-b3-pgbouncer -p 6432 -U app -M prepared -c 20 -j 4 -T 4 -S shop
number of failed transactions: 0 (0.000%)
tps = 47442.525949 (without initial connection time)

เมื่อ max_prepared_statements = 0 การรัน abort ด้วย prepared statement "P_0" already exists และ does not exist — สอง client บน server connection เดียวชนกัน หรือ client ไปตกที่ connection ที่ไม่เคยเห็น statement ของมัน เมื่อ 100 และ RELOAD config จาก admin console การรันเดียวกันจบด้วย 0 transaction ที่ล้ม

กำหนดขนาด pool

default_pool_size ควรตามความสามารถของฐานข้อมูล ไม่ใช่จำนวน client การรัน pgbench แบบเขียนหนักเดียวกัน (60 client, คล้าย TPC-B) กับฐานข้อมูลที่จำกัด 4 CPU รันตรงแล้วผ่าน pool ที่ใหญ่ขึ้นเรื่อย ๆ:

# the same TPC-B-like pgbench run (60 clients, 8 s each, three runs per line, database limited to 4 CPUs), direct first, then through pools of different sizes

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client pgbench -h postgresql-b3-db -U app -c 60 -j 4 -T 8 shop      (direct)    # three runs, tps: 7078, 6697, 6379

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client pgbench -h postgresql-b3-pgbouncer -p 6432 -U app -c 60 -j 4 -T 8 shop1      (pool_size=1)    # three runs, tps: 1350, 1250, 1429

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client pgbench -h postgresql-b3-pgbouncer -p 6432 -U app -c 60 -j 4 -T 8 shop2      (pool_size=2)    # three runs, tps: 2601, 2547, 2591

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client pgbench -h postgresql-b3-pgbouncer -p 6432 -U app -c 60 -j 4 -T 8 shop4      (pool_size=4)    # three runs, tps: 4231, 3409, 4055

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client pgbench -h postgresql-b3-pgbouncer -p 6432 -U app -c 60 -j 4 -T 8 shop      (pool_size=10)    # three runs, tps: 5708, 4300, 5439

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client pgbench -h postgresql-b3-pgbouncer -p 6432 -U app -c 60 -j 4 -T 8 shop30      (pool_size=30)    # three runs, tps: 6022, 5738, 6469

$ docker exec -e PGPASSWORD=<your-password> postgresql-b3-client pgbench -h postgresql-b3-pgbouncer -p 6432 -U app -c 60 -j 4 -T 8 shop60      (pool_size=60)    # three runs, tps: 6492, 5709, 5738

แต่ละบรรทัดคือสามการรัน คุณจึงเห็น noise (ราว 10 เปอร์เซ็นต์) throughput ไต่เกือบเป็นเส้นตรงจาก 1 connection (ราว 1 300 transaction ต่อวินาที) เป็น 2 (2 600) และ 4 (ราว 3 900) pool 10 ถึงราว 5 200 แล้ว และ 30 (ราว 6 100) กับ 60 (ราว 6 000) ไม่ดีกว่ากัน; การรันตรงด้วย 60 connection ได้ราว 6 700 เมื่อ CPU เต็ม server connection ที่มากขึ้นเพิ่มแต่การแย่งกัน สิ่งที่ pool ให้คุณไม่ใช่ความเร็วที่ยอดบน แต่คือตัวเลขที่ถูกจำกัด: 200 client ยังรันบน 10 connection จุดหักของกราฟขึ้นกับฮาร์ดแวร์และส่วนผสมของ query; วัดเอา

เชื่อมต่อผ่าน pooler

จากมุมมองของแอป pooler ดูเหมือน PostgreSQL คุณชี้ driver ไปที่ host และพอร์ตของ pooler — 6432 ข้างบนแทน 5432 — และไม่มีอะไรอื่นเปลี่ยน ยกเว้นกฎ transaction-mode ข้างบน โปรแกรมด้านล่างแต่ละอันเปิด client connection 10 อัน รัน 20 worker ที่รัน query แบบมี parameter เดียวกันสามครั้ง และถูกรันสองครั้งกับ pool แค่ 2 server connection เพื่อบังคับให้ client ต้องแชร์: ครั้งแรกด้วย max_prepared_statements = 0 แล้วด้วย 100

psql "host=127.0.0.1 port=6432 dbname=shop user=app"

import pg from 'pg';

// Point the driver at the pooler's port, not 5432
const pool = new pg.Pool({
  host: '127.0.0.1',
  port: 6432,
  database: 'shop',
  user: 'app',
  password: process.env.PGPASSWORD,
  max: 10,
});

// a named query is a protocol-level prepared statement
const query = { name: 'balance_by_account', text: 'SELECT abalance FROM pgbench_accounts WHERE aid = $1' };
const results = await Promise.all(Array.from({ length: 20 }, (_, i) => pool.query({ ...query, values: [i + 1] })));
console.log(results.length, 'queries ok');
await pool.end();

import os
from concurrent.futures import ThreadPoolExecutor

import psycopg

dsn = f"host=127.0.0.1 port=6432 dbname=shop user=app password={os.environ['PGPASSWORD']}"

def one(i):
    with psycopg.connect(dsn, prepare_threshold=0) as conn:  # prepare on first use
        for _ in range(3):
            conn.execute("SELECT abalance FROM pgbench_accounts WHERE aid = %s", (i,)).fetchone()
    return i

with ThreadPoolExecutor(10) as ex:
    print(len(list(ex.map(one, range(1, 21)))), "workers ok")

package main

import (
  "context"
  "fmt"
  "os"
  "sync"
  "sync/atomic"

  "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
  ctx := context.Background()
  dsn := "host=127.0.0.1 port=6432 dbname=shop user=app pool_max_conns=10 password=" + os.Getenv("PGPASSWORD")
  pool, err := pgxpool.New(ctx, dsn)
  if err != nil {
    panic(err)
  }
  defer pool.Close()

  var wg sync.WaitGroup
  var ok int32
  for i := 1; i <= 20; i++ {
    wg.Add(1)
    go func(aid int) {
      defer wg.Done()
      for k := 0; k < 3; k++ {
        var v int
        if err := pool.QueryRow(ctx, "SELECT abalance FROM pgbench_accounts WHERE aid = $1", aid).Scan(&v); err != nil {
          fmt.Println("ERR", err)
          return
        }
      }
      atomic.AddInt32(&ok, 1)
    }(i)
  }
  wg.Wait()
  fmt.Println(ok, "workers ok")
}

use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let password = std::env::var("PGPASSWORD").unwrap();
    let pool = PgPoolOptions::new()
        .max_connections(10)
        .connect(&format!("postgres://app:{password}@127.0.0.1:6432/shop"))
        .await?;

    let mut tasks = Vec::new();
    for aid in 1..=20_i32 {
        let pool = pool.clone();
        tasks.push(tokio::spawn(async move {
            for _ in 0..3 {
                sqlx::query_scalar::<_, i32>("SELECT abalance FROM pgbench_accounts WHERE aid = $1")
                    .bind(aid)
                    .fetch_one(&pool)
                    .await?;
            }
            Ok::<(), sqlx::Error>(())
        }));
    }
    let mut ok = 0;
    for t in tasks {
        if t.await.unwrap().is_ok() {
            ok += 1;
        }
    }
    println!("{ok} workers ok");
    Ok(())
}

# max_prepared_statements = 0, default_pool_size = 2   (20 workers on 10 client connections, each worker runs the same prepared query 3 times)

[Node.js (pg 8.23.0)] error: prepared statement "balance_by_account" already exists

[Python (psycopg 3.3.6)] psycopg.errors.DuplicatePreparedStatement: prepared statement "_pg3_0" already exists

[Go (pgx v5.11.0)] ERR ERROR: prepared statement "stmtcache_<id>" already exists (SQLSTATE 42P05)  (x18) | 2 workers ok

[Rust (sqlx 0.9.0)] 3 workers ok

# max_prepared_statements = 100, but sqlx sends a startup parameter PgBouncer does not know

[Rust (sqlx 0.9.0)] Error: Database(PgDatabaseError { severity: Fatal, code: "08P01", message: "unsupported startup parameter: extra_float_digits", detail: None, hint: None, position: None,

# max_prepared_statements = 100 and ignore_startup_parameters = extra_float_digits

[Node.js (pg 8.23.0)] 20 queries ok

[Python (psycopg 3.3.6)] 20 workers ok

[Go (pgx v5.11.0)] 20 workers ok

[Rust (sqlx 0.9.0)] 20 workers ok

ทุก driver ล้มเมื่อ max_prepared_statements = 0 — prepared statement ... already exists ใน Node.js, Python และ Go และการรัน Go กับ Rust จบเพียง 2 และ 3 worker จาก 20 — และทุก driver ทำงานเมื่อ 100 ยกเว้นการตั้งค่าหนึ่ง: sqlx ส่ง startup parameter extra_float_digits และ PgBouncer ปฏิเสธ startup parameter ที่มันไม่ติดตาม (unsupported startup parameter: extra_float_digits) จนกว่าคุณจะใส่มันใน ignore_startup_parameters (docs) ถ้า PgBouncer ของคุณเก่ากว่า 1.21 ให้หาในเอกสารของ driver เรื่องสวิตช์ที่ปิด server-side prepared statement

ใน pgAdmin: ลงทะเบียนการเชื่อมต่อเซิร์ฟเวอร์ใหม่ที่ host และพอร์ตชี้ไป PgBouncer (พอร์ต 6432) pgAdmin ปฏิบัติต่อมันเหมือนเซิร์ฟเวอร์ PostgreSQL ซึ่งเป็นวิธีสะดวกยืนยันว่าเข้าถึง pooler ได้

เมื่อเสร็จ ลบทุกอย่างที่แล็บสร้าง (รวมถึง image):

docker rm -f postgresql-b3-pgbouncer postgresql-b3-client postgresql-b3-db
docker volume rm postgresql-b3-db-data
docker network rm postgresql-b3-net
docker rmi postgresql-b3-pgbouncer:local

Reading AI-generated code

ผู้ช่วย AI เขียนการเข้าถึงข้อมูลแบบ multi-tenant และเมื่อถูกบอกว่า deployment ใช้ PgBouncer ใน transaction mode ก็ตัดสินว่าไม่เป็นไร:

🐛 หา bug ในโค้ดนี้

# AI comment: "transaction-mode PgBouncer is fine: set the tenant once when we get the connection"
def get_conn(tenant):
  conn = pool.getconn()
  conn.execute("SET app.tenant = %s", (tenant,))  # session-level setting
  return conn

def orders_for(conn):
  return conn.execute("SELECT * FROM orders WHERE tenant = current_setting('app.tenant')").fetchall()

เฉลย

SET เป็นคำสั่งระดับ session และใน transaction mode session ไม่ได้เป็นของ client ค่าที่ตั้งติดกับ server connection และ client ถัดไปที่ยืมมันสืบทอด tenant ก่อนหน้า — alice ใน transcript ข้างบนเป๊ะ เมื่อใช้ row-level security หรือ filter WHERE tenant = current_setting(...) นั่นคือข้อมูลรั่วระหว่างลูกค้า และไม่ล้มดัง ๆ วิธีแก้คือจำกัดค่าให้อยู่ใน transaction ตามที่การรัน shop1b ทำ: set_config('app.tenant', value, true) (หรือ SET LOCAL) ภายใน BEGIN ... COMMIT และอ่านข้อมูลใน transaction เดียวกัน client ถัดไปเห็นค่าว่าง

def orders_for(conn, tenant):
    with conn.transaction():  # the setting lives exactly as long as this transaction
        conn.execute("SELECT set_config('app.tenant', %s, true)", (tenant,))
        return conn.execute("SELECT * FROM orders WHERE tenant = current_setting('app.tenant')").fetchall()

Mental Model

pooler คือคิวแท็กซี่ client คือผู้โดยสาร server connection คือรถ และ transaction คือหนึ่งเที่ยว ใน transaction mode ผู้โดยสารถือรถเฉพาะตลอดเที่ยว รถยี่สิบคันจึงรับใช้ผู้โดยสารหลายพันคน — แต่ทุกอย่างที่คุณทิ้งไว้ในรถ (SET, session lock, prepared statement ที่ไม่มีการรองรับ) ไม่ใช่ของคุณอีกแล้วเมื่อจบเที่ยว

เคล็ดลับและข้อควรระวัง

transaction mode เป็นคำแนะนำปกติเพราะให้ connection reuse ดีที่สุดสำหรับ request เว็บสั้น ๆ ใช้ session mode เมื่อต้องการความต่อเนื่องของ session จริง ๆ เท่านั้น
ใน transaction mode หลีกเลี่ยงฟีเจอร์ที่ข้าม transaction บน connection เดียว — SET ระดับ session, advisory lock ระดับ session และ LISTEN/NOTIFY prepared statement ใช้ได้ใน PgBouncer 1.21 ขึ้นไปเมื่อ max_prepared_statements ไม่ใช่ศูนย์
pooler ไม่ทดแทน connection pool ของ driver เอง รันทั้งสองได้ แต่กำหนดขนาดให้ driver ไม่ขอมากกว่าที่ pooler ให้ได้
ตั้ง default_pool_size ตามความสามารถของเซิร์ฟเวอร์ (จำนวน core ไม่ใช่ client) และจำไว้ว่า server connection ของ pooler เองก็นับรวมใน max_connections ด้วย
ใช้ SHOW POOLS และ SHOW STATS บน admin console เพื่อดู client ที่รอ (cl_waiting) ก่อนผู้ใช้จะบอกคุณ
อย่าใส่ credential ของฐานข้อมูลลงใน image; แล็บใช้รหัสผ่านใช้แล้วทิ้งบนเครือข่ายภายใน

ข้อแลกเปลี่ยน

ตัวเลือก	Benefit	Cost
Transaction-mode pooling (PgBouncer)	client ต่อ PostgreSQL connection จริงได้มากกว่าเยอะ (200 client บน 10)	ฟีเจอร์ระดับ session (`SET`, session advisory lock, `LISTEN`) พัง; prepared statement ต้องมี `max_prepared_statements` และ PgBouncer ที่ใหม่พอ
Session-mode pooling / ไม่มี pooler	รองรับฟีเจอร์ระดับ session เต็มที่ การเชื่อมต่อตรงไปตรงมา	PostgreSQL connection แพงและจำกัด (ราว 3 MB ต่ออัน เพดานแข็งที่ `max_connections`)
pool ที่ใหญ่ขึ้น	client รอน้อยลง	เกินจำนวน CPU แล้ว server connection มากขึ้นไม่เพิ่ม throughput (30 เทียบ 60 ข้างบน)

Common Misconceptions

“pooler ทำให้ฐานข้อมูลเร็วขึ้น” มันจำกัดจำนวน connection; throughput ที่ยอดบนเท่าเดิมโดยประมาณทั้งมีและไม่มีมัน
“เพิ่ม max_connections แก้ ‘too many clients’” มันสร้าง process มากขึ้นที่ราว 3 MB ต่ออันและการแย่งกันมากขึ้น pooler แก้ได้
“prepared statement ใช้กับ PgBouncer ไม่ได้” จริงก่อน 1.21; เมื่อ max_prepared_statements มากกว่าศูนย์ มันใช้ได้
“transaction mode ปลอดภัยถ้าโค้ดเปิด transaction” เฉพาะเมื่อไม่มีอะไรพึ่งสถานะ session; SET ธรรมดานอก BEGIN รั่วไปยัง client อื่น
“ขนาด pool ควรเท่าจำนวน client” ควรตามความจุของเซิร์ฟเวอร์; 60 server connection ไม่ดีกว่า 30