Connection pooling

ปัญหาในโปรดักชันจำนวนมากอย่างน่าประหลาดใจสืบย้อนกลับไปที่ข้อเท็จจริงหนึ่ง: ใน PostgreSQL ทุก client connection จะมี process ของระบบปฏิบัติการเป็นของตัวเองอยู่ฝั่ง server ถ้ามีไม่กี่ connection ก็แทบไม่มีต้นทุนอะไร แต่พอถึงหลักพัน จะกินหน่วยความจำและ overhead ในการ scheduling อย่างมาก จน server ช้าลงจนคืบคลานก่อนที่งานจริงจะหมดเสียอีก

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

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

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

นี่คือเหตุผลที่การติดตั้งเริ่มต้นจำกัด max_connections ไว้ที่ตัวเลขต่ำ ๆ เช่น 100 การเพิ่มค่านี้ไม่ได้ทำให้ปัญหาหายไป แค่เปิดให้คุณสร้าง 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 แต่ละตัวจะได้ process ฝั่ง server เป็นของตัวเอง

pooler อยู่ตรงกลาง

Pooler อย่าง PgBouncer ตั้งอยู่ระหว่าง client กับ server ตัว 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

ผลลัพธ์น่าทึ่ง: client ของแอปพลิเคชันหนึ่งหมื่นตัวอาจได้รับบริการด้วย PostgreSQL connection จริงเพียงยี่สิบตัว เพราะไม่มี client ตัวไหนถือ server connection ค้างไว้ตอนที่ตัวเองว่าง

โหมด pooling

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

โหมด	server connection ถูกคืนหลังจาก…	หมายเหตุ
Session	client ตัดการเชื่อมต่อ	ปลอดภัยที่สุด แต่ connection ถูกถือไว้ตลอดทั้ง session
Transaction	แต่ละ transaction commit หรือ roll back	ตัวเลือกที่ใช้กันทั่วไป; นำกลับมาใช้ซ้ำได้ดีโดยมีข้อแม้หนึ่งข้อ
Statement	แต่ละ statement เดี่ยว	รุนแรงที่สุด; ห้าม transaction ที่มีหลาย statement

Transaction mode เป็นค่าเริ่มต้นยอดนิยม server connection ถูกยืมไปเฉพาะช่วงของ transaction เดียวเท่านั้น พอจบ transaction ก็ว่างให้คนอื่นใช้ต่อทันที สิ่งนี้ให้การนำกลับมาใช้ซ้ำที่ยอดเยี่ยมสำหรับ traffic เว็บทั่วไป ที่แต่ละ request เป็น transaction สั้น ๆ

ข้อจับใจคือสิ่งใดก็ตามที่ผูกอยู่กับ session แทนที่จะเป็น transaction อาจพังได้ เพราะ transaction ถัดไปอาจไปตกบน server connection คนละตัว ทั้ง prepared statement, คำสั่ง SET ระดับ session, advisory lock, และ LISTEN/NOTIFY ล้วนสันนิษฐานว่า connection มีความต่อเนื่อง ซึ่ง transaction mode ไม่ได้ให้

pgbouncer.ini ขั้นต่ำ

PgBouncer ถูกตั้งค่าด้วยไฟล์ INI เล็ก ๆ สิ่งที่ต้องระบุคือ อยู่หน้าฐานข้อมูลไหน ฟังที่ port ใด และใช้โหมด pooling แบบไหน

[databases]
shop = host=127.0.0.1 port=5432 dbname=shop

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 20

ในที่นี้ max_client_conn คือจำนวน client ที่อาจเชื่อมต่อกับ PgBouncer ได้ ส่วน default_pool_size คือจำนวน PostgreSQL connection จริงที่ PgBouncer เปิดค้างไว้ต่อหนึ่งฐานข้อมูล — server connection ยี่สิบตัวรองรับ client ได้ถึงหนึ่งหมื่นตัว

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

จากมุมมองของแอปพลิเคชันคุณ pooler ดูเหมือน PostgreSQL ทุกประการ คุณชี้ driver ของคุณไปที่ host และ port ของ pooler — 6432 ด้านบนแทนที่จะเป็น 5432 — และไม่มีอะไรอื่นเปลี่ยนแปลง

psql

# Connect to PgBouncer instead of PostgreSQL directly
psql "host=127.0.0.1 port=6432 dbname=shop user=app"

Node.js

import { Pool } from 'pg';

// Point the driver at the pooler's port, not 5432
const pool = new Pool({
  host: '127.0.0.1',
  port: 6432,
  database: 'shop',
  user: 'app',
});

Python

import psycopg

conn = psycopg.connect(
    host="127.0.0.1",
    port=6432,
    dbname="shop",
    user="app",
)

Go

conn, err := pgx.Connect(ctx,
    "host=127.0.0.1 port=6432 dbname=shop user=app")

Rust

let pool = sqlx::postgres::PgPoolOptions::new()
    .connect("postgres://[email protected]:6432/shop")
    .await?;

ใน pgAdmin: ลงทะเบียน server connection ใหม่โดยตั้ง host และ port ชี้ไปที่ PgBouncer (port 6432) pgAdmin จะมองว่าเป็น PostgreSQL server ตัวหนึ่งตามปกติ เป็นวิธีสะดวก ๆ ในการยืนยันว่า pooler เข้าถึงได้

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

• Transaction mode เป็นคำแนะนำตามปกติ เพราะให้การนำ connection กลับมาใช้ซ้ำได้ดีที่สุดสำหรับ request เว็บสั้น ๆ คว้า session mode มาใช้ก็ต่อเมื่อคุณต้องการความต่อเนื่องของ session จริง ๆ เท่านั้น
• ใน transaction mode ให้หลีกเลี่ยงฟีเจอร์ที่กินข้าม transaction บน connection เดียว — server-side prepared statement, SET ระดับ session, advisory lock, และ LISTEN/NOTIFY อาจทำงานผิดปกติได้ driver จำนวนมากมีการตั้งค่าให้ปิด server-side prepared statement ด้วยเหตุผลนี้
• Pooler ไม่ได้มาแทน connection pool ของ driver เอง คุณรันทั้งสองได้ แต่ต้องตั้งขนาดให้ driver ไม่ขอเกินกว่าที่ pooler จ่ายได้
• ตั้ง default_pool_size ตามความจุของ server คุณ ไม่ใช่ตามจำนวน client pool เล็กที่ยุ่งอยู่ตลอดดีกว่า pool ใหญ่ที่ท่วมฐานข้อมูล

🐘 ความว่างคือศัตรู

แก่นของ pooling คือ connection ของแอปพลิเคชันส่วนใหญ่นั่งว่างอยู่เป็นส่วนใหญ่ของเวลา แต่ PostgreSQL connection ที่ว่างแต่ละตัวก็ยังเสียต้นทุนเป็น process pooler ทำให้ช่วงว่างเหล่านั้นถูกแชร์กัน คุณไม่ได้เพิ่มความจุให้ฐานข้อมูล แต่กำลังกัน client ไม่ให้กักตุน connection ไว้ตอนที่ไม่มีอะไรทำ

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

ตัวเลือก	Benefit	Cost
Transaction-mode pooling (PgBouncer)	รองรับ client จำนวนมากต่อ PostgreSQL connection จริงหนึ่งตัว ราคาถูกและ scale ได้	ฟีเจอร์ระดับ session เช่น prepared statement, SET, advisory lock ใช้งานไม่ได้อย่างปลอดภัย
Session-mode pooling / ไม่มี pooler เลย	รองรับฟีเจอร์ระดับ session ได้เต็มรูปแบบ เชื่อมต่อตรงไปตรงมา	PostgreSQL connection แพงและมีจำกัด, scale จำนวน client ได้น้อยกว่ามาก

ข้อผิดพลาดที่พบบ่อย

• มองข้าม connection pooling จนกว่าจะเจอ error too many connections ในโปรดักชัน — ตั้ง pooler ไว้ตั้งแต่ต้น อย่ารอให้ปัญหาเกิดก่อนแล้วค่อยแก้
• ใช้ transaction-mode pooling ทั้งที่โค้ดพึ่งพาฟีเจอร์ระดับ session อย่าง SET หรือ advisory lock — ฟีเจอร์เหล่านี้จะพังแบบเงียบ ๆ เพราะ transaction ถัดไปอาจไปตกบน server connection คนละตัว ตรวจสอบโค้ดก่อนเปลี่ยนโหมด
• กำหนดขนาด pool ให้ใหญ่กว่า max_connections ที่ฐานข้อมูลรองรับได้จริงมาก — pool ที่ใหญ่เกินไปแค่ย้ายปัญหาไปกองไว้ที่ฐานข้อมูล ให้กำหนดขนาดตามความจุจริงของ server

💡 ตัวอย่างจากของจริง

Supabase — ให้ pooler (PgBouncer แบบ managed) เป็นค่าเริ่มต้นหน้า PostgreSQL instance ของทุกโปรเจกต์ เพราะแอปพลิเคชัน serverless เปิด connection พร้อมกันได้มากกว่าที่ฐานข้อมูลรองรับโดยตรง

Amazon RDS Proxy — วางตัวเป็น pooler ระดับ managed service สำหรับ RDS PostgreSQL เพื่อให้ Lambda function หรือแอปที่ scale เร็วไม่ทำให้ connection ของฐานข้อมูลพุ่งจนล้น

ทำไม PostgreSQL connection จึงถือว่าหนัก?

แต่ละตัวดาวน์โหลดทั้งฐานข้อมูล แต่ละตัวมี process ของระบบปฏิบัติการเป็นของตัวเองอยู่ฝั่ง server แต่ละตัวต้องการดิสก์แยกต่างหาก แต่ละตัวเปิด TCP port ใหม่บน client

ในโหมด transaction ของ PgBouncer server connection จะถูกคืนกลับสู่ pool เมื่อไหร่?

เมื่อ client ตัดการเชื่อมต่อ หลังจากแต่ละ statement เดี่ยว เมื่อ transaction ปัจจุบัน commit หรือ roll back ไม่เคยคืน เพราะถือไว้ตลอด session

ฟีเจอร์ใดมีแนวโน้มทำงานผิดปกติมากที่สุดภายใต้ pooling โหมด transaction?

SELECT แบบ single-statement ธรรมดา คำสั่ง SET ระดับ session และ advisory lock ที่สันนิษฐานว่ามี connection เดียว INSERT สั้น ๆ ภายใน transaction เดียว การเชื่อมต่อด้วย username และ password

Submit

Mark as complete