ข้อผิดพลาดที่พบบ่อยในการเปลี่ยนสคีมาฐานข้อมูลใน Postgres

Here is a summary of the common database schema change mistakes, translated and structured in Korean:

ข้อผิดพลาดที่เกี่ยวกับการทำงานพร้อมกัน

• การได้มาซึ่ง lock ล้มเหลว
• อัปเดตแถวมากเกินไปในครั้งเดียว
• เปิดทรานแซกชันค้างไว้นานหลังจากได้ exclusive lock

ข้อผิดพลาดด้านความถูกต้องของแต่ละขั้นตอน - ประเด็นเชิงตรรกะ

• ความคลาดเคลื่อนของสคีมาที่ไม่คาดคิด
• สคีมากับโค้ดแอปไม่สอดคล้องกัน
• ข้อมูลที่ไม่คาดคิด

ข้อผิดพลาดอื่น ๆ

• ถึง statement_timeout
• ใช้ primary key แบบจำนวนเต็ม 4 ไบต์กับตารางที่อาจเติบโตขึ้นได้
• มองข้ามการทำงานของ VACUUM และความเสี่ยงของ bloat

กรณีที่ 1. สคีมาไม่ตรงกัน

• ทำงานได้ในสภาพแวดล้อมพัฒนา/ทดสอบ แต่ล้มเหลวใน QA/Staging/Production
• หลังหาสาเหตุได้แล้ว ควรแก้ด้วยการปรับปรุง workflow

กรณีที่ 2. ใช้ IF [NOT] EXISTS ผิด

• อย่าพยายามมองข้ามข้อผิดพลาดจากสคีมาไม่ตรงกันด้วย IF NOT EXISTS
• ต้องหาสาเหตุที่แท้จริงของปัญหาและแก้ไขให้ได้

กรณีที่ 3. ถึง statement_timeout

• ควรทดสอบทุกการเปลี่ยนแปลงกับข้อมูลขนาดใหญ่ล่วงหน้าเพื่อให้รู้ปัญหาก่อน

กรณีที่ 4. การเปลี่ยนแปลงขนาดใหญ่แบบไม่จำกัด

• หากเปลี่ยนแถวจำนวนมากเกินไปในทรานแซกชันเดียว จะกระทบทรานแซกชันอื่น
• หากไม่ได้จูน Checkpointer อาจทำให้เกิดข้อมูล WAL มากเกินไป
• อาจทำให้การเขียนดิสก์อิ่มตัวและประสิทธิภาพโดยรวมลดลง
• อาจเกิดปัญหา VACUUM/Bloat
• ควรแบ่งทำเป็น batch และจัดการ VACUUM ให้เหมาะสม

กรณีที่ 5. รออยู่ในทรานแซกชันหลังจากได้ exclusive lock

• หากทำงานอื่นระหว่าง BEGIN/ALTER TABLE/COMMIT lock จะถูกถือไว้นาน
• หลังจากได้ exclusive lock แล้ว ควรจบทรานแซกชันให้เร็วที่สุด

กรณีที่ 6. ทรานแซกชันที่มีทั้ง DDL + DML ปริมาณมาก

• lock ที่ได้มาในขั้น DDL จะถูกถือยาวไปจนถึงขั้น DML
• ควรแยก DDL และ DML ออกเป็นคนละทรานแซกชัน/ขั้น migration

กรณีที่ 7. session อื่นถูกบล็อกจากการรอได้ exclusive lock

• เมื่อ autovacuum อยู่ในโหมดป้องกัน wraparound จะไม่ yield ให้ DDL
• ระหว่างรอได้ lock แม้แต่ SELECT ก็อาจถูกบล็อก
• ควรตั้ง lock_timeout ให้ต่ำและทำ retry logic

กรณีที่ 8. ข้อควรระวังเมื่อสร้าง FK

• เมื่อสร้าง FK บนตารางใหญ่ จะใช้เวลาเพราะต้องสแกนตาราง referenced
• ควรกำหนด FK ด้วยตัวเลือก not valid แล้วค่อย validate ในอีกทรานแซกชันหนึ่ง

กรณีที่ 9. ข้อควรระวังเมื่อ ลบ FK

• ต้องใช้ lock กับทั้งสองตาราง จึงควรมี retry logic สำหรับ lock_timeout

กรณีที่ 10. ข้อควรระวังเมื่อเพิ่ม CHECK constraint

• มีการสแกนทั้งตาราง จึงควรใช้แนวทาง 2 ขั้นตอนคล้ายกับ FK

กรณีที่ 11. ข้อควรระวังเมื่อเพิ่ม NOT NULL

• ใน Postgres ต่ำกว่า 11 การเพิ่ม NOT NULL ให้คอลัมน์ใหม่จะทำให้เกิดการสแกนทั้งตาราง
• ตั้งแต่ Postgres 11 สามารถแก้ได้ด้วยการเพิ่มคอลัมน์ NOT NULL DEFAULT
• ตั้งแต่ Postgres 12 สามารถตั้ง NOT NULL ได้ผ่านการเพิ่ม CHECK constraint

กรณีที่ 12. ข้อควรระวังเมื่อเปลี่ยนชนิดข้อมูลของคอลัมน์

• อาจทำให้ต้องเขียนตารางใหม่ทั้งตาราง
• ต้องใช้แนวทางเพิ่มคอลัมน์ใหม่แล้วคัดลอกข้อมูลด้วย trigger

กรณีที่ 13. ข้อควรระวังเมื่อ CREATE INDEX

• ในระบบ OLTP ควรใช้ CREATE INDEX CONCURRENTLY
• หากการสร้าง unique index ล้มเหลว ต้องจัดการลบ invalid index

กรณีที่ 14. ข้อควรระวังเมื่อ DROP INDEX

• มีปัญหาเรื่องการได้ lock จึงควรใช้ DROP INDEX CONCURRENLTY

กรณีที่ 15. ข้อควรระวังเมื่อเปลี่ยนชื่ออ็อบเจ็กต์

• ต้องปรับลำดับการ deploy เพื่อหลีกเลี่ยงความไม่สอดคล้องกันระหว่างโค้ดแอปกับสคีมา DB

กรณีที่ 16. การเพิ่มคอลัมน์ที่มีค่า DEFAULT

• ก่อน PG 11 จะเกิดการเขียนตารางใหม่ทั้งตาราง
• ตั้งแต่ PG 11 การเพิ่มคอลัมน์ที่มีค่า DEFAULT ทำได้เร็วขึ้น

กรณีที่ 17. การจัดการสิ่งตกค้างเมื่อ CREATE INDEX CONCURRENTLY ล้มเหลว

• หากล้มเหลวจะเหลือ invalid index จึงต้องล้างก่อน retry

กรณีที่ 18. ใช้ primary key แบบจำนวนเต็ม 4 ไบต์กับตารางขนาดใหญ่

• ควรใช้ int8 โดย framework ส่วนใหญ่ก็ใช้ int8 อยู่แล้ว

คำแนะนำ

• ทดสอบด้วยขนาดข้อมูลที่สมจริง
• ตรวจสอบระยะเวลาที่ถือ exclusive lock
• ปรับปรุงระบบ deploy อัตโนมัติ
• เรียนรู้จากผู้อื่นและแบ่งปันความรู้

ความเห็นของ GN⁺

บทความนี้สรุปข้อผิดพลาดและข้อควรระวังต่าง ๆ ที่อาจพบได้ระหว่างการเปลี่ยนสคีมา DB ในงานจริงไว้ได้ดีมาก โดยเฉพาะประเด็นที่เกี่ยวกับ exclusive lock ซึ่งยิ่งเป็นฐานข้อมูลขนาดใหญ่ก็ยิ่งอาจก่อให้เกิดปัญหาร้ายแรงมากขึ้น

นอกจากนี้ยังอธิบายข้อควรระวังเวลา dealing กับ FK, NOT NULL, index และองค์ประกอบที่นักพัฒนามักมองข้ามได้อย่างเป็นรูปธรรม การเข้าใจและใช้ประโยชน์จากการปรับปรุงตามแต่ละเวอร์ชันของ Postgres ก็น่าจะช่วยได้มาก

เหนือสิ่งอื่นใด เห็นด้วยอย่างยิ่งว่าการทดสอบอย่างเข้มงวดด้วยขนาดข้อมูลที่สมจริง และการปรับปรุงระบบ deploy อัตโนมัติอย่างต่อเนื่อง คือหัวใจสำคัญในการลดความเสี่ยงของการเปลี่ยนสคีมาให้น้อยที่สุด การลองใช้เครื่องมืออย่าง Database Lab Engine เพื่อช่วยเรื่องการทดสอบและการ deploy automation ก็น่าจะเป็นทางเลือกที่ดี

หวังว่าจะมีบทความจากเทคบล็อกที่แชร์เคล็ดลับมีประโยชน์แบบนี้เพิ่มขึ้นอีก ยิ่งข้อมูลลักษณะนี้เผยแพร่ออกไปกว้างเท่าไร ก็ยิ่งช่วยยกระดับทักษะของนักพัฒนาที่ทำงานกับฐานข้อมูลได้มากขึ้นเท่านั้น