คู่มือเอาตัวรอดกับ Postgres สำหรับสตาร์ทอัพ

 (hatchet.run)
3 คะแนน โดย GN⁺ 4 시간 전 | 1 ความคิดเห็น | แชร์ทาง WhatsApp
  • Hatchet สรุปหลักปฏิบัติด้านการปฏิบัติการเป็นลำดับขั้น ตั้งแต่การออกแบบ schema/คิวรี ในช่วงต้น ไปจนถึงการเขียนปริมาณมากและการ migrate ตาราง โดยอิงจากปัญหาที่พบใน production ตลอด 2 ปี
  • เพื่อให้อ่านได้เร็ว ควรจัด index ให้สอดคล้องกับ ORDER BY แต่ query planner อาจเลือก sequential scan ตามสถิติและต้นทุนได้ จึงควรใช้ EXPLAIN ANALYZE เปรียบเทียบค่าประมาณกับการรันจริง
  • ประสิทธิภาพและเสถียรภาพของการเขียนขึ้นอยู่กับ transaction ที่สั้น การล็อกเฉพาะแถวที่จำเป็น CREATE INDEX CONCURRENTLY และ connection pooling โดย batch processing เพิ่ม throughput ได้ราว 10 เท่าจากการวัดของ Hatchet
  • ในสภาพแวดล้อมที่มีการเขียนถี่ การตั้งค่า autovacuum เริ่มต้นอาจเก็บคืน dead tuple และ transaction ID ได้ไม่ทัน และเมื่อถึง transaction ID wraparound จะเกิด downtime ขนาดใหญ่
  • เมื่อระบบเติบโตขึ้น ควรใช้คิวงานที่อิงกับ FOR UPDATE SKIP LOCKED, partitioning, trigger และ batch backfill แต่ต้องสามารถ ควบคุม SQL โดยตรงนอก abstraction ของ ORM ได้

กลุ่มผู้อ่านเป้าหมายและข้อจำกัดของ ORM

  • เป็นคู่มือที่จัดทำขึ้นเพื่อให้นักพัฒนาที่รู้พื้นฐานของ SQL, row, table และ index สามารถรับมือกับปัญหา Postgres ใน production ได้
  • คู่มือ Postgres ครอบคลุมมาก แต่ยากต่อการใช้อ้างอิงอย่างรวดเร็วในสถานการณ์ขัดข้อง จึงย่อเนื้อหาโดยเน้นประสบการณ์ปฏิบัติการที่ Hatchet พบตลอด 2 ปี
  • แม้ใช้ ORM หลักการเหล่านี้ยังใช้ได้ แต่ยิ่งระบบมีขนาดใหญ่ขึ้น ก็ยิ่งมี optimization จำนวนมากที่ทำได้ก็ต่อเมื่อออกจากชั้น abstraction แล้ว เขียน SQL โดยตรง
    • สามารถใช้ฟีเจอร์อย่าง Prisma TypedSQL เพื่อใช้ ORM ร่วมกับ SQL โดยตรงได้
    • Hatchet ซึ่งใช้ Go ใช้ sqlc ที่ให้พฤติกรรมคล้ายกัน
    • สำหรับสภาพแวดล้อมที่ Claude เขียนคิวรี แนะนำ supabase/agent-skills

การออกแบบ schema ที่เปลี่ยนได้ยาก

  • หลัง deploy แล้ว การเปลี่ยน schema เป็นสิ่งที่ยากที่สุด จึงควรร่าง table และ primary key ก่อน แล้วเขียนคิวรีที่แอปพลิเคชันต้องใช้ไปพร้อมกับออกแบบซ้ำ
  • ในกระบวนการออกแบบ ให้ตรวจสอบวิธีใช้งาน table ด้วยคำถามต่อไปนี้
    • ระหว่างการอ่านกับการเขียน อย่างไหนเกิดบ่อยกว่า
    • ตอนอ่าน filter ที่ใช้บ่อยที่สุดคืออะไร
    • column ที่ update บ่อยที่สุดคืออะไร
  • สามารถใช้ 1NF, 2NF, 3NF ของ database normalization ได้ แต่บางครั้ง normal form ก็ขัดกับความสะดวกในการใช้งานที่จำเป็นต่อประสิทธิภาพคิวรีหรือการพัฒนาอย่างรวดเร็ว
    • ในบางสถานการณ์ การใส่ข้อมูลไว้ใน column jsonb จะเรียบง่ายกว่า
  • หลักปฏิบัติจากประสบการณ์ที่ใช้กับการออกแบบ schema มีดังนี้
    • ใช้ integer auto-increment ที่เป็น identity column หรือ UUID ในตัวของ Postgres เป็น primary key
    • identity column เร็วกว่า bigserial เล็กน้อย
    • ใช้ timestamptz สำหรับเวลาเสมอ
    • ทุก table ต้องมี primary key
    • ใช้ foreign key รวมถึง cascade delete กับ table ปริมาณต่ำที่ความสม่ำเสมอและความถูกต้องสำคัญ แต่ต้องระวังในสภาพแวดล้อมปริมาณสูง

คิวรีอ่านและ index

  • โมเดลง่าย ๆ สำหรับทำความเข้าใจ SELECT ที่เร็วคือ Postgres หา row หนึ่งได้เร็วผ่าน index หรืออ่านทุก row ใน table ด้วย sequential scan (seq scan)
  • สำหรับการค้นหา row เดี่ยวอย่างรวดเร็ว ใช้โครงสร้างต่อไปนี้
    • index ที่ระบุชัดเจน
    • unique constraint ซึ่งเป็นรูปแบบพิเศษของ index
    • primary key ที่ Postgres ทำ index ให้อัตโนมัติ
  • index พื้นฐานใช้ btree และอาจมองได้เหมือน table แยกต่างหากที่เก็บข้อมูลในรูปแบบที่เหมาะกับการค้นหา
    • เวลาค้นหา row โดยประมาณคือ log(n) โดย n คือจำนวน row ใน table
  • หากใช้ index ไม่ได้ จะเกิด sequential scan แต่ database สมัยใหม่โหลด row เข้าหน่วยความจำได้เร็ว ดังนั้น table ที่มี น้อยกว่า 20,000 row อาจเสร็จแทบจะทันที

join และ composite index

  • เป้าหมายของ inner join โดยทั่วไปควรใช้ primary key ไม่เช่นนั้นอาจมีปัญหาในการออกแบบ schema หรือ normalization
  • ควรมอง ON clause เหมือน WHERE clause และใช้ index ที่เหมาะสมกับเงื่อนไข join
  • การ query รายการจาก table ขนาดใหญ่มักเป็นคิวรีแรก ๆ ที่แอปพลิเคชันเริ่มรู้สึกช้า
    • หาก filter และ sort ร่วมกันด้วยองค์กรและเวลาสร้าง สามารถใช้ composite index ได้
CREATE INDEX CONCURRENTLY idx_documents_org_created
    ON documents (organization_id, created_at DESC);
  • สำหรับคิวรีซับซ้อน หลักจากประสบการณ์คือวาง column ของ ORDER BY ไว้ท้ายสุดของ index และจัดทิศทางการ sort ให้ตรงกันด้วย
    • Postgres scan btree ได้สองทิศทาง ดังนั้น DESC อาจไม่มีความหมายสำหรับ column เดี่ยว แต่ใน composite index ควรทำให้ตรงไว้
    • รายละเอียดการทำงานของ index แบบ descending ดูได้จาก เอกสารที่เกี่ยวข้อง

การเขียน การล็อก และ migration

  • เงื่อนไขแรกของการเขียนที่สำเร็จคือ รักษา transaction ให้สั้น
    • หากไม่มีเหตุผลพิเศษ อย่าเรียก service ภายนอกระหว่าง transaction
  • เงื่อนไขที่สองคือการล็อกเฉพาะ row ที่จำเป็น
    • เมื่อ update row จะมี lock บน row นั้นจนกว่า transaction จะ commit
    • ยิ่งระบบมีโหลดสูง ผลกระทบของ lock ก็ยิ่งชัดเจน
  • หากรัน CREATE INDEX ปกติบน table ขนาดใหญ่ที่มีอยู่แล้ว table จะถูกล็อกและ insert กับ update จะถูกบล็อก ดังนั้นให้ใช้ CREATE INDEX CONCURRENTLY เสมอ
  • ความสามารถด้าน schema migration ที่ดีช่วยเพิ่มความเร็วในการพัฒนาแบบวนซ้ำและเพิ่ม uptime
    • หลีกเลี่ยงการลบหรือเอา column ออกเท่าที่ทำได้ และเปลี่ยนด้วยการเพิ่มแทน
    • หากเป็นไปได้ ให้รันภายใน transaction เพื่อรับมือกับ rollback และการ apply บางส่วน
    • วิธีที่ก้าวหน้ากว่านั้นคือใช้ migration แบบ expand and contract
  • ก่อนทำ migration ต้องตัดสินใจก่อนว่าจะบล็อกการเขียนทั้งหมดหรือไม่
    • การสร้าง index โดยไม่มี CONCURRENTLY อาจบล็อกการเขียนทั้งหมดและทำให้เกิด downtime
    • งาน ALTER TABLE ควรตรวจสอบซ้ำ และการเพิ่ม check constraint ใน table ขนาดใหญ่ก็อาจบล็อกการเขียนได้
    • การเพิ่ม check constraint แบบ NOT VALID สามารถหลีกเลี่ยงการบล็อกดังกล่าวได้

การจัดการ connection

  • คิวรีและ transaction ทั้งหมดใช้ connection ไปยัง database และ connection มีต้นทุน CPU กับหน่วยความจำสูง จึงควรรักษาไว้ให้นาน
  • การสร้างและลบ connection บ่อย ๆ ทำให้สิ้นเปลืองทรัพยากร
    • connection storm ที่มี connection ใหม่จำนวนมากเกิดขึ้นพร้อมกัน อาจก่อปัญหาที่ debug ยากเกี่ยวกับ lock ภายใน Postgres
  • ควรพิจารณา external connection pooler อย่าง pgbouncer ก่อน และหากใช้ไม่ได้จึงใช้ in-memory connection pool เป็นทางเลือก
    • Hatchet ไม่สามารถสมมติว่า database ของผู้ใช้ใช้ external pooler ได้ จึงใช้ pgxpool สำหรับ Go

query planner และสถิติ

  • คิวรีซับซ้อนที่มี join มากหรือผสมวิธี join หลายแบบ ไม่ได้แก้ได้ด้วยการเพิ่ม index อย่างเดียว
    • index เองก็มี overhead จึงไม่ควรเพิ่มอย่างไม่จำกัด
  • query planner แปลง SQL เป็น operation ภายใน database และตัดสินใจว่าจะใช้ index หรือไม่ แต่ด้วยข้อมูลที่จำกัด อาจเลือก plan ที่เหมาะที่สุดไม่ได้
  • ข้อมูลที่ planner ใช้คือสถิติของ table ซึ่งดูได้จาก pg_stats
SELECT *
FROM pg_stats
WHERE tablename = 'mytable';
  • สถิติถูกรวบรวมตอน ANALYZE และอัปเดตเมื่อ autovacuum รันด้วย
    • หากเพิ่มความถี่ของ autovacuum สถิติคิวรีก็จะทันสมัยอยู่เสมอ
    • หนึ่งในสาเหตุทั่วไปที่คิวรีทำงานผิดพลาดคือการวิเคราะห์ไม่ถี่พอ
  • การตัดสินคิวรีอย่างง่ายจากว่ามี sequential scan หรือไม่ ช่วยลดการเพิ่มความคาดเดาไม่ได้ของ planner จาก micro-optimization
    • หาก query โดยเน้น primary key และ index planner จะเลือก plan ได้ง่ายขึ้น

การวิเคราะห์ execution plan และ sequential scan

  • provider บางราย เช่น Google CloudSQL จะ sample คิวรีและเก็บคิวรีช้าไว้ แต่ไม่ใช่ทุก service ที่รองรับ
  • EXPLAIN ANALYZE รันคิวรีจริง และเปรียบเทียบจำนวน row ที่คาดไว้ตามสถิติของ table กับจำนวน row ที่ scan จริง
    • ต้องระวังใน production เพราะคิวรีจริงจะถูกรัน
    • หากต้องการดูเฉพาะ plan โดยไม่รัน ให้ใช้ EXPLAIN โดยตัด ANALYZE ออก
  • สามารถบันทึก plan แบบละเอียดเป็น JSON แล้วนำไป visualize ที่ explain.dalibo.com ได้
psql -XqAt -f explain.sql -d $DATABASE_URL > analyze.json
  • หากสถิติและ index ปกติแต่ยังเกิด sequential scan อาจเป็นเพราะ planner คำนวณว่า ต้นทุนของ sequential scan ต่ำกว่า
    • index ถูกเก็บแยกจาก heap ที่มีข้อมูล table จริง ดังนั้นหลาย row ที่พบจาก index จะมีต้นทุนในการกลับไปอ่านจาก heap อีกครั้ง
    • หากไม่สามารถปรับโครงสร้างคิวรีครั้งใหญ่ได้ ก็ต้องยอมรับ sequential scan หรือพิจารณา partitioning

การเขียนปริมาณมากและ batch processing

  • แต่ละคิวรีมี overhead ได้แก่ round trip time ไปยัง database, เวลาที่ใช้ขอ connection จาก connection pool ของแอปพลิเคชัน และเวลาประมวลผลของ Postgres
    • lock ภายใน Postgres ก็อาจเป็น bottleneck ในสภาพแวดล้อม throughput สูงได้
  • การรวมหลาย row ไว้ในคิวรีเดียวช่วยลดต้นทุนเหล่านี้ได้
    • วิธีที่ง่ายที่สุดคือส่งหลายคิวรีไปยัง server พร้อมกันใน implicit transaction
    • ใน Go สามารถใช้ SendBatch ของ pgx ได้
  • ที่ Hatchet batch processing เพิ่ม throughput ได้ราว 10 เท่า และการ optimize การ insert เพิ่มเติมสรุปไว้ใน คู่มือ insert Postgres ให้เร็ว

autovacuum และ transaction ID wraparound

  • autovacuum รับผิดชอบการทำความสะอาด dead tuple และการจัดการ transaction ID และในสภาพแวดล้อมที่มีการเขียนถี่อาจต้องปรับค่า
  • tuple คือเวอร์ชันหนึ่งของ row ที่เก็บอยู่ใน file system
    • แม้ update หรือลบ row เวอร์ชันเดิมจะยังคงอยู่จนกว่า transaction ทั้งหมดที่เริ่มก่อนหน้านั้นจะ commit หรือ rollback
    • เวอร์ชันที่ไม่มี transaction ใดอ่านได้อีกต่อไปคือ dead tuple
  • หากความเร็วในการเขียนสูงเกินไป autovacuum อาจตามอัตราการเกิด dead tuple ไม่ทัน ทำให้สถานะ database แย่ลงอย่างรวดเร็ว
  • หากตรวจสอบ process ที่ active ใน pg_stat_activity แล้วพบว่า autovacuum query รันอยู่ ประมาณ 1 ชั่วโมงขึ้นไป ควรพิจารณาปรับการตั้งค่า
  • หากใช้ transaction ID หมดก่อนที่ autovacuum จะเก็บคืน จะเกิด transaction ID wraparound และนำไปสู่ downtime ขนาดใหญ่

table และ index bloat

  • Postgres เก็บ row ใน page ขนาด 8KB บน disk และหากไม่สามารถใส่ row ใหม่ใน page เดิมได้ ก็จะสร้าง page ใหม่
  • เมื่อ dead tuple ถูกเก็บคืนแล้ว page ว่างบางส่วน จะเกิด table bloat ทำให้การใช้ disk เพิ่มขึ้นมาก
    • วิธีป้องกันที่ดีที่สุดคือปรับ autovacuum ก่อนจะเกิด bloat
    • สำหรับ table ที่ bloat ไปแล้ว สามารถใช้ extension อย่าง pg_repack ได้
    • VACUUM FULL ในตัวแทบไม่ใช่ตัวเลือกที่ดี
    • Postgres 19 มีกำหนดเพิ่ม REPACK...CONCURRENTLY สำหรับ repack table แบบพร้อมกัน แต่ Hatchet ยังไม่ได้ทดสอบ
  • index bloat ก็เป็นรูปแบบเฉพาะของ table bloat และลดได้ด้วยการตั้งค่า autovacuum ที่เหมาะสม
    • สำหรับ index ที่ bloat ไปแล้ว สามารถใช้คำสั่งในตัว REINDEX INDEX CONCURRENTLY ได้

การประมวลผลพร้อมกันด้วย FOR UPDATE SKIP LOCKED

  • FOR UPDATE SKIP LOCKED จอง row ที่เลือกไว้สำหรับ transaction ปัจจุบันโดยไม่รบกวนคิวรีอื่น
  • Hatchet ใช้สิ่งนี้กับ คิวงาน และสามารถล็อกงานที่รออยู่ในคิวรีเดียวพร้อมเปลี่ยนสถานะเป็น RUNNING ได้
WITH eligible_tasks AS (
    SELECT *
    FROM tasks
    WHERE status = 'QUEUED'
    ORDER BY id ASC
    FOR UPDATE SKIP LOCKED
    LIMIT 100
)
UPDATE tasks
SET status = 'RUNNING'
FROM eligible_tasks
WHERE tasks.id = eligible_tasks.id
RETURNING tasks.*;
  • ยังมีประโยชน์เมื่อต้อง update row ที่เป็นอิสระต่อกันพร้อมกัน หรือเมื่อหลาย instance ของแอปพลิเคชันจัดการ lease ของ object
    • Hatchet ใช้กระจาย tenant lease ไปยังหลาย engine

partitioning

  • partitioning ในตัวของ Postgres แบ่ง table ตามค่า row เช่น timestamp หรือ hash
  • ให้ประโยชน์ต่อข้อมูล time-series และข้อมูลงานเก่าของ Hatchet ดังนี้
    • รัน autovacuum แยกกันต่อ partition เพื่อขยายขนาดการประมวลผล autovacuum ของ table ได้
    • ลบข้อมูลเก่าได้แทบจะทันทีโดย drop partition table แทนการลบทีละ row
  • หากในขั้น planning Postgres ไม่สามารถตัด partition ที่ไม่จำเป็นออกได้ คิวรีอ่านจะมี overhead
    • Postgres รุ่นล่าสุดปรับปรุง partition pruning ดีขึ้น
    • ประสบการณ์ปฏิบัติการของ Hatchet สรุปไว้ใน บทความ Postgres partitioning

การย้ายข้อมูลระหว่าง table ขนาดใหญ่

  • migration ของ table ขนาดใหญ่ในที่นี้ไม่ได้หมายถึงการเปลี่ยน schema แต่หมายถึงงาน ย้ายข้อมูลจำนวนมาก จาก table หนึ่งไปอีก table หนึ่ง
  • หากคัดลอก table ใหญ่มากใน transaction เดียว อาจใช้เวลาหลายชั่วโมง
    • transaction ที่ยาวนานขัดขวางการทำงานปกติของ autovacuum และทำให้ dead tuple bloat
    • หากยังมีการเขียนไปยัง table เดิมต่อไป ข้อมูลดังกล่าวจะไม่ถูกสะท้อนใน table ใหม่
  • Hatchet รัน batch backfill ขนาดใหญ่นอก transaction และคัดลอกการเขียนใหม่หลังเริ่ม migration ไปยัง table ใหม่ด้วย trigger ของ Postgres
    • ใช้ unique constraint ของ primary key เพื่อป้องกันการเขียนซ้ำ

บทความที่เกี่ยวข้อง

1 ความคิดเห็น

 
GN⁺ 4 시간 전
ความคิดเห็นจาก Hacker News

  • ถ้าเป็นฐานข้อมูลสำหรับงานปฏิบัติการ สิ่งแรกที่ควรวางแผนน่าจะเป็น แผนสำรองข้อมูลและกู้คืน ไม่ใช่หรือ ความพร้อมใช้งานสูงอาจเป็นตัวเลือกในช่วงแรกได้ แต่คู่มือเอาตัวรอดที่ไม่มีเรื่องสำรองข้อมูลและกู้คืนก็น่าสงสัยอยู่
    สงสัยว่าทุกวันนี้การสำรองข้อมูล PostgreSQL ยังใช้ Barman(https://pgbarman.org/) กันเยอะอยู่ไหม

    • ถ้าไม่ใช่ผู้เชี่ยวชาญ PostgreSQL ก็อย่าดำเนินการเอง ใช้ ฐานข้อมูลแบบมีการจัดการอย่าง RDS จะดีกว่า ต้นทุนที่ประหยัดได้จากการโฮสต์เองนั้นเล็กน้อยมากเมื่อเทียบกับค่าใช้จ่ายเพื่อให้ได้ความพร้อมใช้งานสูงที่ผ่านการพิสูจน์แล้ว การสำรองและกู้คืน การกู้คืนไปยังจุดเวลาหนึ่ง และ read replica
    • ใช้ pgBackRest อยู่ ให้ การกู้คืนไปยังจุดเวลาหนึ่ง ที่ดีกว่าโซลูชันสำรองข้อมูลตอนกลางคืนที่เคยใช้มาก่อน ตั้งค่าให้สำรองไปยัง Backblaze B2 ได้ค่อนข้างง่าย และก็ไม่มีปัญหาอะไรเป็นพิเศษ
    • สำหรับส่วนใหญ่ แค่รัน pg_dump_all จาก cron แล้วบีบอัดด้วย zstd จากนั้นคัดลอกไป S3 หรือ FTP ฯลฯ ก็เพียงพอแล้ว เมื่อข้อมูลใหญ่ขึ้น เวลาและต้นทุนของการสำรองข้อมูลเต็มรูปแบบจะเริ่มเป็นภาระ แต่ด้วยวิธีง่าย ๆ นี้ก็ยังยื้อไปได้ค่อนข้างนาน
    • ถ้าเป็นฐานข้อมูลที่รับประกันความทนทานแม้ไฟดับ ก็สามารถสำรองด้วย สแนปช็อตวอลุ่มแบบอะตอมิก ได้ หากต้องการลดเวลากู้คืนให้ทำ checkpoint ก่อน และเพื่อป้องกันข้อมูลเสียหาย ต้องรับประกันความเป็นอะตอมิกของสแนปช็อตให้ได้
      เคยสำรอง MongoDB ขนาดหลาย TB บน AWS ด้วย EBS snapshot เพื่อทำ incremental backup และการกู้คืนที่รวดเร็ว แม้จะกู้คืนไปยังจุดเวลาหนึ่งไม่ได้ แต่สามารถถ่ายได้บ่อยเป็นรายชั่วโมง จึงเหมาะเป็นกลยุทธ์เสริมที่ใช้ควบคู่กับเครื่องมือเฉพาะของ PostgreSQL
    • ถ้าใช้งาน Kubernetes อยู่แล้ว ก็ใช้ CloudNativePG ได้เลย

  • มีบางจุดที่อยากเสริม ควรใช้ UUIDv7 แทน UUIDv4 ทั่วไป และไม่ใช่แค่จำนวนแถวที่ล็อกเท่านั้น แต่ต้องทำให้ลำดับการล็อกในทุก query เป็นแบบกำหนดแน่นอนเหมือน id ASC เพื่อหลีกเลี่ยง deadlock
    ใช้ EXPLAIN (GENERIC_PLAN) แล้วสามารถคัดลอก query โดยคง placeholder ของพารามิเตอร์ไว้ และยังดูแผนการ optimize ตอนที่ PostgreSQL ไม่รู้ค่าจริงได้ด้วย ในตารางที่ว่างหรือเล็ก สามารถใช้ SET enable_seqscan = off เพื่อตรวจสอบความเป็นไปได้ในการใช้อินเด็กซ์ได้
    อินเด็กซ์ B-tree ที่ทุกคนใช้เป็นค่าเริ่มต้นนั้นหนักและบวมได้ง่าย ดังนั้นหากเป็นเพียงการค้นหาแบบง่าย ๆ โดยไม่มีการเรียงลำดับหรือค้นหาช่วง ก็ควรพิจารณา hash index ได้เช่นกัน สร้าง unique hash index ไม่ได้ แต่สามารถทำผลลัพธ์ใกล้เคียงด้วย hash exclusion constraint ได้ และไม่รองรับ unique index แบบหลายคอลัมน์
    ควรเรียนรู้ GIN·GiST index ไว้ด้วย สำหรับผู้ใช้ MySQL อาจดูคาดไม่ถึง แต่สามารถเร่ง query ธรรมดาอย่าง LIKE '%foo%' ได้โดยไม่ต้องเปลี่ยนไปใช้ full-text search

    • deadlock ไม่ได้เกิดแค่ตอนที่ชุดแถวที่จะล็อกไม่มี ORDER BY ที่สอดคล้องกันเท่านั้น แต่ยังเกิดได้เมื่อ ลำดับการล็อกตาราง ต่างกันด้วย ถ้า transaction หนึ่งล็อกตามลำดับ table_a, table_b และอีก transaction ล็อกกลับกัน แม้จะใช้ ORDER BY และ FOR UPDATE ภายในแต่ละตารางก็ยัง deadlock ได้
      ในทางทฤษฎีเป็นเรื่องชัดเจน แต่ในทางปฏิบัติต้องเข้าใจในระดับโกลบอลว่าการเขียนทั้งหมดแตะตารางใดบ้าง ทำให้ debug ยากขึ้นมาก และเคยเจอกับตัวใน extension บางตัว กำลังทดลองใช้ GIN สำหรับการค้นหา key-value ใน JSONB ซึ่งช่วยเพิ่ม performance ได้มาก และความแตกต่างด้าน performance ระหว่าง AND กับ OR ก็มากพอสมควร
    • ไม่ว่า UUID แบบไหน ถ้าใช้เป็น primary key จะมี primary key join บ่อย ทำให้ต้นทุนสูงและมักได้ประโยชน์น้อย โดยค่าเริ่มต้นควรใช้ primary key แบบเพิ่มตามลำดับ และถ้าจำเป็นต้องเปิดเผยภายนอก ค่อยเพิ่มคอลัมน์ UUIDv4 ที่มี secondary index จะปลอดภัยกว่า อยากรู้ว่า UUIDv7 มี performance บน B-tree ดีกว่า UUIDv4 จริงหรือไม่
    • ถ้าปิด sequential scan ผมคิดว่าเมื่อมีอินเด็กซ์สักตัว PostgreSQL จะพยายามใช้มันแบบฝืน ๆ ดังนั้นคงไม่ได้บอกได้ถึงขั้นว่าเป็น อินเด็กซ์ที่ถูกต้อง หรือไม่
    • เคยมีการแนะนำเครื่องมือแปลง UUIDv7 และ UUIDv4 อย่าง https://github.com/ali-master/uuidv47 และ https://github.com/stateless-me/uuidv47 หลายครั้ง

  • คำแนะนำนี้ก็ดี แต่สตาร์ทอัพที่เคยร่วมงานด้วยมักชนกับ ปัญหาเชิงองค์กร ที่อยู่ต่ำกว่าประเด็นเรื่องการขยายระบบก่อน ควรไม่ใช้ ORM, ใช้คีย์หลักแบบเพิ่มค่าตามลำดับแทนฟิลด์ที่มีความหมาย และใช้ JSONB อย่างจำกัดเฉพาะเมื่อจำเป็นจริง ๆ
    ข้อมูลต้นทางควรเป็นแบบ append-only ที่ทำได้แค่เพิ่ม ไม่ควรแก้ไขหรือลบ ตารางช่วยที่ทำ denormalization เพื่อประสิทธิภาพและความสะดวกสามารถเปลี่ยนแปลงได้ แต่ไม่ควรใช้เป็นแหล่งความจริง
    ควรใช้ connection pool แต่ต้องระวังจำนวน connection และถ้าไม่มีปัญหา ก็อาจไม่จำเป็นต้องไปถึง PgBouncer หากไม่มีเหตุผลชัดเจนควรหลีกเลี่ยง explicit transaction, ห้ามทำงานที่กินเวลานานอย่าง RPC ขณะเปิด transaction ค้างไว้ และโดยมากก็ไม่ควรใช้ SERIALIZABLE
    หากจำเป็นต้องใช้ lock แบบชัดเจนอย่าง SELECT FOR UPDATE ก็อาจเป็นสัญญาณว่าการออกแบบมีปัญหา อย่าสร้างระบบ type ขึ้นใหม่โดยทำให้แถวในตารางเดียวมีความหมายหลายแบบตามค่า type int หรือเลียนแบบ graph database ด้วยตาราง node·edge ที่อ้างอิงตัวเอง ส่วนใหญ่แก้ได้ด้วยตารางแบบ normalized ตามปกติ

    • ใน PHP backend ที่กำลังทำอยู่ ต้อง instantiate object เพื่อทำเรื่องอย่างการตรวจสิทธิ์ ดังนั้น ORM มีประโยชน์มาก ถ้าทำโดยไม่มี ORM ดูเหมือนจะต้องทำงานมากขึ้นมาก จึงสงสัยว่าทำไมถึงเป็นตัวเลือกที่แย่
    • ถ้าเงินเดือนนักพัฒนาคือค่าใช้จ่ายก้อนใหญ่ที่สุด หลักการว่า อย่าใช้ ORM ก็เป็นเรื่องที่ถกเถียงได้ ภายใต้ความต้องการทางธุรกิจของตาราง แรงกดดันจากลูกค้า และงบประมาณที่ตึงตัว ต้นทุนก็ยังเดินต่อระหว่างคุยกับ DBA และออกแบบให้ถูกต้องเป็นเวลานาน ดังนั้นหลักการอย่างการหลีกเลี่ยงคอลัมน์ type หรือโครงสร้างแบบกราฟก็ไม่ได้ง่ายอย่างที่พูด
    • สำหรับสตาร์ทอัพที่ต้องรีบปล่อยผลิตภัณฑ์ ORM เป็นตัวเลือกที่ดีพอแล้ว หากเข้าใจกับดักอย่าง N+1 query และวิธี lazy loading ก็เป็นจุดประนีประนอมที่ดีกว่าการสร้างการจัดการ query และ parameterization เองอีกครั้ง
      ในช่วงต้นโปรเจกต์ ผมจะเลือกใช้เวลาไปกับการพัฒนาผลิตภัณฑ์ มากกว่าคิดมากเกินไปกับ schema ฐานข้อมูลและ optimize ก่อนเวลาอันควร
    • เคยใช้ SELECT FOR UPDATE ได้อย่างมีประโยชน์ในหลายจุด จึงสงสัยว่าปัญหาคืออะไร และอยากรู้ด้วยว่าถ้าใช้แหล่งความจริงแบบ append-only แล้ว lock แบบนี้จะไม่จำเป็นหรือไม่
    • ข้อมูลต้นทางแบบ append-only น่าสนใจ แต่ในหลายระบบที่เคยทำมา มันคงทำให้ปริมาณจัดเก็บของหลายตารางพุ่งขึ้นอย่างมากเพื่อผลประโยชน์ที่น่าสงสัย แม้เป็นเทคนิคที่มีประโยชน์ แต่ก็สงสัยว่าควรเป็นหลักการที่บังคับใช้ทุกที่หรือไม่
      ในทางกลับกัน อยากรู้ว่าวิธีใช้ตาราง relational แบบดั้งเดิมที่แก้ไขได้เป็นแหล่งความจริง แล้วบันทึก change log ด้วย trigger เป็นอย่างไร

  • ไม่ชอบ cascading delete นักพัฒนาส่วนใหญ่อยู่กับชั้นแอปพลิเคชันอย่าง Python, Node, Go มากกว่าฐานข้อมูล ดังนั้น cascading delete มักดูเหมือนเวทมนตร์ เมื่อแถวในตาราง A ถูกลบแล้วข้อมูลในตาราง B หายไปด้วย ถ้าตั้งค่าผิดก็ยิ่งอันตราย ดังนั้นสำหรับการดูแลระยะยาว การเขียนคำสั่งลบอย่างชัดเจนดีกว่า และแค่ใช้ foreign key ให้ถูกต้องก็รักษาความสอดคล้องได้
    กับดักและทางเลี่ยงในการ migrate ตารางขนาดใหญ่นั้นถูกต้อง แต่ก็มีเครื่องมืออย่าง pg-osc อยู่แล้ว ควรเรียบง่ายระดับรันคำสั่งเดียวแล้วเฝ้าดูอย่างลุ้น ๆ ระหว่างที่ข้อมูลถูกคัดลอกตลอด 24 ชั่วโมง
    ควรแยกการ deploy แอปพลิเคชันกับฐานข้อมูลตั้งแต่เนิ่น ๆ เนื่องจากไม่สามารถ deploy การเปลี่ยน schema และแอปพลิเคชันพร้อมกันอย่างสมบูรณ์ใน transaction ได้ เมื่อเข้าสู่ production แล้วจึงต้องสร้างนิสัยทำเฉพาะ การเปลี่ยน schema ที่ backward compatible เช่น ทำคอลัมน์ใหม่ให้ nullable หรือมีค่า default และไม่เปลี่ยนชื่อตาราง/คอลัมน์
    ควรกำหนดกลยุทธ์จัดการ schema ตั้งแต่เนิ่น ๆ เช่นกัน ควรหลีกเลี่ยงกระบวนการ deploy ที่ senior developer รัน DDL ด้วยมือบนฐานข้อมูล production จากเครื่องตัวเอง และสามารถใช้เครื่องมือที่คุ้นเคยอย่าง Liquibase หรือ Flyway ได้

    • สร้าง pgschema ซึ่งเป็นเครื่องมือจัดการ schema แบบ declarative

  • query planner จะ optimize สำหรับ กรณีเฉลี่ย แต่สำหรับแอปพลิเคชัน บางครั้งการ optimize สำหรับกรณีแย่ที่สุดมีประโยชน์กว่า ผู้ใช้ทั่วไปมีแถวน้อย ผลลัพธ์จึงออกมาใน 10ms ด้วย index บางตัว แต่สำหรับผู้ใช้ที่ใช้งานหนัก query เดียวกันใช้เวลามากกว่า 1 วินาทีขึ้นกับ parameter
    จึงใช้ query ที่ซับซ้อนขึ้นเพื่อบังคับเส้นทาง index อื่น ทำให้ประสิทธิภาพเฉลี่ยช้าลงเล็กน้อย แต่กรณีแย่ที่สุดก็ลดลงเหลือต่ำกว่า 100ms สำหรับบริษัทแล้ว การป้องกัน timeout สำคัญกว่าการประหยัดเวลาเฉลี่ย 10ms มาก

  • SKIP LOCKED มีประโยชน์กับ work queue แบบใช้ interactive transaction ที่เปิด transaction ค้างไว้และ lock แถวระหว่างที่แอปพลิเคชันทำงาน ในแอปประสิทธิภาพสูง ควรหลีกเลี่ยง transaction แบบนี้เอง และอัปเดตแถวเป็น pending ทันที จึงไม่จำเป็นต้องใช้ SKIP LOCKED
    ยิ่ง scale ใหญ่ขึ้น ยิ่งต้องลด state ที่ค้างอยู่ในหน่วยความจำของฐานข้อมูล และ interactive transaction ก็เป็น state แบบหนึ่ง ในสภาพแวดล้อมที่ต้องขยายระบบ idempotency ได้เปรียบกว่า atomicity

  • transaction ที่กินเวลานาน อาจทำร้ายสถานะของฐานข้อมูลได้ จึงควรใช้เฉพาะเมื่อมีเหตุผลหนักแน่น ตั้ง idle_in_transaction_session_timeout เพื่อไม่ให้ transaction ที่ idle ถือ lock หรือ tuple ไว้นาน และตั้ง lock_timeout สำหรับ migration เพื่อไม่ให้ DDL ตัวเดียวหยุดทั้งระบบ
    ควรตั้ง statement_timeout ด้วย เพื่อไม่ให้ query แพง ๆ เพียงตัวเดียวทำให้ระบบเป็นอัมพาต

  • จากการรัน PostgreSQL ในช่วงแรกของสตาร์ทอัพ บทความนี้ยังไม่เน้น monitoring และ alerting มากพอ PostgreSQL มี failure mode สำคัญบางอย่างที่ต้องหลีกเลี่ยงให้ได้ และ alert สามารถจับความเสี่ยงได้ตั้งแต่เนิ่น ๆ
    แม้ AWS จะส่งอีเมลมาบอกว่าใกล้ถึง transaction ID wraparound แต่ในสตาร์ทอัพ โดยเฉพาะวันอย่าง Boxing Day ก็พลาดได้ง่าย ควรผูกสัญญาณที่ AWS เฝ้าดูเข้ากับ pager ไม่ใช่อีเมล

  • implementation ของ connection pool มีความแตกต่างใหญ่ที่ไม่ค่อยมีใครรู้ connection pool ของแอปพลิเคชันส่วนใหญ่ใช้ first-in, first-out (FIFO) เพื่อ optimize latency ต่ำและ availability ของ connection แต่เพราะคอยทำให้ connection อุ่นอยู่เสมอ จึงลด connection ที่ไม่จำเป็นได้ยาก
    PgBouncer และ external pooler บางตัวใช้ last-in, first-out (LIFO) เพื่อ optimize จำนวน connection ที่ไปถึง PostgreSQL และ throughput หากนำ connection ล่าสุดกลับมาใช้ก่อน connection ที่เหลือก็จะเย็นลงและถูกปิดไปเองตามธรรมชาติ
    สำหรับแอปพลิเคชันใหม่ FIFO ก็เพียงพอ แต่เมื่อ scale ใหญ่ขึ้น ควรใช้เครื่องมืออย่าง PgBouncer เพื่อลด connection หลายร้อยรายการลงราว 90% โครงสร้างของ PostgreSQL ที่สร้าง process ต่อ connection จะทำงานได้ดีกว่าเมื่อจำนวน connection น้อยลง

  • ในสถานการณ์ที่เฉพาะเจาะจงมาก ๆ เคยได้ผลลัพธ์ที่ดีจากการ join ในหน่วยความจำของแอปพลิเคชัน บางครั้งเพื่อพยายามลดจำนวนรอบไป-กลับกับฐานข้อมูล ก็อาจสร้างคิวรีเดี่ยวที่ซับซ้อนซึ่งมี JOIN, UNION, CASE พันกันยุ่งเหยิง
    แทนที่จะทำเช่นนั้น การรันคิวรีง่าย ๆ หลายคำสั่งอย่างอิสระ แล้ววนดูผลลัพธ์เพื่อเชื่อมแถวที่เกี่ยวข้องกันด้วย map อาจทำให้แผนคิวรีคาดเดาได้มากขึ้น จนได้เปรียบกว่า แม้จะมีต้นทุนการไป-กลับและการวนซ้ำเพิ่มขึ้นก็ตาม ใช้เฉพาะในขอบเขตจำกัด และไม่ได้แนะนำแบบไม่มีเงื่อนไขให้ใช้วิธีที่ ORM บางตัวทำงานแบบนี้ภายใน

    • ประสิทธิผลของวิธีนี้ขึ้นอยู่กับสถานการณ์อย่างมาก หากการ join ทำให้เกิด ผลคูณรวม ที่ใหญ่กว่าข้อมูลต้นทางมาก การดึงเฉพาะชุดข้อมูลต้นทางมาแล้วประกอบกันในเครื่องอาจช่วยลดภาระของ DB และทราฟฟิกเครือข่ายได้
      แต่ inner join แบบคัดเลือกมักสร้างผลลัพธ์ที่เล็กกว่าข้อมูลต้นทางมาก ดังนั้นการดึงทุกเรคคอร์ดมาแล้วทำ intersection และกรองในเครื่องจะมีค่าใช้จ่ายสูงกว่ามาก ส่วน index join อาจทำให้ตัววางแผนคิวรีใช้ดัชนีเพื่อหลีกเลี่ยงการสแกนตารางแบบ brute-force การ sort และการกรองได้
    • เท่าที่ทราบ ยังมีวิธีสร้าง view สองตัวแล้วค่อย join แทนการใช้คิวรีเดี่ยวที่ซับซ้อนด้วย