01MVCC และ Transaction Isolation

PostgreSQL ใช้ Multi-Version Concurrency Control ทำให้ transaction อ่าน snapshot ของข้อมูลได้โดยไม่ต้อง block writer ในกรณีทั่วไป การ update สร้าง row version ใหม่ ขณะที่ version เก่ารอ VACUUM เก็บคืน จึงต้องดู dead tuples และ long-running transaction ที่ขัดขวาง cleanup

Read Committed

แต่ละ statement เห็น snapshot ใหม่ เป็นค่าเริ่มต้นและเหมาะกับงานทั่วไป

Repeatable Read

Transaction เห็น snapshot เดิมตลอด ต้องรับมือ serialization failure บางกรณี

Serializable

ให้ผลเหมือนรันตามลำดับ แต่ application ต้อง retry เมื่อเกิด serialization failure

02Index และ Query Plan

Index เร่งการอ่านแต่เพิ่ม storage และต้นทุนทุก write เลือก column order จาก predicate จริง: equality ก่อน range และ sort โดยทั่วไป ตรวจด้วย `EXPLAIN (ANALYZE, BUFFERS)` เพื่อดู row estimate, scan type, loop count และ I/O จริง

policy-query.sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, created_at
FROM policy_applications
WHERE customer_id = $1
  AND status = 'PENDING'
ORDER BY created_at DESC
LIMIT 20;

-- Index ให้ตรงกับ filter และ sort ของ query สำคัญ
CREATE INDEX CONCURRENTLY idx_policy_customer_pending
ON policy_applications (customer_id, created_at DESC)
WHERE status = 'PENDING';
  • B-tree: equality, range และ ordering ทั่วไป
  • GIN: array, JSONB และ full-text ที่ค้นหาสมาชิกภายใน
  • BRIN: ตารางใหญ่มากที่ค่ามี correlation กับตำแหน่ง physical เช่น timestamp
  • Partial index: index เฉพาะ subset ที่ query บ่อย ลดขนาดและ write cost

03Locking และ Concurrent Update

ใช้ optimistic locking เมื่อ conflict น้อย โดย update พร้อม `version` แล้วตรวจจำนวน row ที่เปลี่ยน ใช้ `SELECT ... FOR UPDATE` เมื่อจำเป็นต้อง serialize การแก้ไข record เดียว แต่ให้ transaction สั้นและ lock ตามลำดับเดิมเพื่อลด deadlock

อย่าเปิด transaction แล้วเรียก external API เพราะจะถือ connection และ lock ระหว่างรอ network สำหรับ workflow ข้ามระบบใช้ outbox pattern: บันทึก business state และ event ใน transaction เดียว แล้วให้ worker publish ภายหลัง

04Migration และ Connection Pool

Migration แบบไม่หยุดระบบใช้แนวทาง expand/contract: เพิ่ม schema ที่ backward-compatible, deploy code ที่อ่านเขียนได้ทั้งสองแบบ, backfill ทีละ batch แล้วค่อยลบของเก่า ระวัง DDL ที่ lock ตารางและ index creation บน production

อ่านเอกสารทางการ: PostgreSQL Concurrency Control ↗