ตั้งตารอ Postgres 19: ในที่สุดก็ถึงเวลาจัดการกับ “เวลา”

• เพื่อรองรับคำถามว่าข้อมูลมีหน้าตาเป็นอย่างไร ณ วันที่ใดวันที่หนึ่ง (วันอังคารที่ผ่านมา) Postgres 19 จะเพิ่มการรองรับ ตารางประวัติแบบอิงช่วงเวลา (temporal table) แบบเนทีฟ ทำให้ติดตามข้อมูลก่อนและหลังการเปลี่ยนแปลงได้โดยไม่ต้องมีระบบทริกเกอร์ตรวจสอบแยกต่างหาก
• Postgres เพิ่งนำ Temporal table ที่ มาตรฐาน SQL:2011 กำหนดไว้เมื่อกว่าสิบปีก่อนเข้ามาไว้ในคอร์ จึงเข้าร่วมช้ากว่าเอนจินฐานข้อมูลอื่น
• แทนที่จะใช้คอลัมน์ valid_from/valid_to สองคอลัมน์ร่วมกับ ข้อจำกัดแบบไม่ให้ทับซ้อน (exclusion constraint) บนส่วนขยาย btree_gist แบบเดิม จะใช้ คอลัมน์ชนิด range type เพียงคอลัมน์เดียวและข้อจำกัด WITHOUT OVERLAPS เพื่อให้สื่อความหมายได้ตรงไปตรงมากว่า
• ไวยากรณ์ FOR PORTION OF ทำให้ UPDATE·DELETE แบ่งแถวอัตโนมัติ โดยเอนจินจัดการป้องกันช่องว่างและการทับซ้อนบนแกนเวลา
• การเพิ่มครั้งนี้ครอบคลุมครึ่งหนึ่งของระบบสองช่วงเวลา (bi-temporal) คือ เวลาที่มีผลในแอปพลิเคชัน (application time) ยังไม่รองรับเวลาระบบ (system time) แต่เป็นการวางรากฐานสำหรับรีลีสในอนาคต

---

วิธีเดิม - The Old-Fashioned Way

• ความพยายามแรกในการติดตามราคาสินค้าตามลำดับเวลามักประกอบด้วยคอลัมน์วันที่สองคอลัมน์ valid_from, valid_to และข้อจำกัด CHECK valid_from < valid_to
  • แต่ไม่สามารถป้องกันการแทรก สองแถวที่มีช่วงวันที่ทับซ้อนกัน สำหรับสินค้าชิ้นเดียวกันได้ (เช่น สินค้าหมายเลข 42 มีราคา $9.99 และ $14.99 ในวันอังคารเดียวกัน)
• วิธีแก้แบบดั้งเดิมคือใช้ส่วนขยาย btree_gist และข้อจำกัดแบบไม่ให้ทับซ้อน (exclusion constraint)
  • ในรูปแบบ EXCLUDE USING gist (product_id WITH =, daterange(valid_from, valid_to) WITH &&) ซึ่งจะเกิดข้อผิดพลาดเมื่อแทรกแถวที่ทับซ้อนกัน
• ปัญหาของวิธีนี้
  • GiST เป็นดัชนีเฉพาะของ Postgres ที่ต้องอาศัยประสบการณ์ และยังเป็นส่วนขยายแบบเลือกติดตั้ง จึงมีอุปสรรคในการเริ่มใช้งาน
  • ไวยากรณ์ของข้อจำกัดแบบไม่ให้ทับซ้อนไม่ตรงไปตรงมา ทำให้นึกถึงได้ยากว่าเป็นแนวทางมาตรฐาน
  • ตัวตารางเองไม่ได้ รับรู้เรื่องช่วงเวลาในตัว เมื่อเปลี่ยนช่วงเวลา ต้องแบ่งหรือรวมแถวด้วยมือ และภาระเรื่องความสอดคล้องของช่วงเวลาถูกผลักไปให้แอปพลิเคชัน

ประวัติย่อของเวลา - A Brief History of Time

• มาตรฐาน SQL:2011 นำช่วงเวลาที่มีผล (APPLICATION TIME), ข้อจำกัด WITHOUT OVERLAPS และไวยากรณ์ FOR PORTION OF สำหรับจัดการข้อมูลตามช่วงเวลาเข้ามา
• Henrietta Dombrovskaya (Hetti) ร่วมกับ Chad Slaughter พัฒนาส่วนขยาย pg_bitemporal ซึ่งเป็นเฟรมเวิร์กสำหรับจัดการตารางสองช่วงเวลาใน Postgres ด้วย PL/pgSQL
  • ตั้งแต่ปี 2015 ได้นำเสนอแนวคิดในหลายงานประชุม และสาธิตวิธีติดตามทั้ง เวลาที่มีผล (valid time) (ช่วงเวลาที่ข้อเท็จจริงเป็นจริงในโลกจริง) และ เวลาธุรกรรม (transaction time) (ช่วงเวลาที่ฐานข้อมูลบันทึกข้อเท็จจริงนั้น) พร้อมกัน
• ความแตกต่างของมิติเวลาทั้งสอง
  • เวลาที่มีผลหมายถึง “ราคานี้มีผลตั้งแต่มกราคมถึงมิถุนายน”
  • เวลาธุรกรรมคือมุมมองของฐานข้อมูลว่า “แถวนี้ถูกแทรกเมื่อ 12 มีนาคม เวลา 15:47 และถูกแทนที่เมื่อ 3 เมษายน เวลา 09:01”
  • เมื่อนำทั้งสองมารวมกัน จะสร้างตารางสองช่วงเวลาที่ตอบได้ว่า “จากข้อมูลที่เรารู้ในตอนนั้น เรา คิดว่า ราคาของวันอังคารที่ผ่านมาเป็นเท่าไร”
• pg_bitemporal ใช้ EXCLUDE USING gist แบบสองชั้น โดยใช้กับช่วง effective (เวลาที่มีผล) และช่วง asserted (เวลาธุรกรรม) อย่างละหนึ่งรายการ
  • มีฟังก์ชันสำหรับแทรก อัปเดต แก้ไข ปิดใช้งาน และลบแบบสองช่วงเวลา รวมถึงการใช้งาน ความสัมพันธ์ช่วงเวลาของ Allen (Allen's interval relationships) สำหรับอนุมานช่วงเวลา
• ข้อจำกัดของส่วนขยาย
  • ไม่สามารถปรับ query planner ให้รับรู้เงื่อนไขช่วงเวลา ผสานกับระบบข้อจำกัดระดับเอนจิน หรือ提供ไวยากรณ์จัดการแบบเนทีฟได้ → จำเป็นต้องเข้าคอร์
  • Postgres 19 รับครึ่งหนึ่งของระบบสองช่วงเวลา คือ เวลาที่มีผล แม้ยังไม่ครบทั้งหมด แต่ถือเป็นความก้าวหน้าครั้งใหญ่

ใช้ Range มาช่วย - Ranges to the Rescue

• แนวทางของ Postgres 19 ใช้คอลัมน์ ชนิด range type เดี่ยว valid_at DATERANGE แทน valid_from/valid_to แยกกัน
  • PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS) ทำให้ไม่ต้องใช้ส่วนขยาย btree_gist และข้อจำกัดแบบไม่ให้ทับซ้อนโดยตรง
  • WITHOUT OVERLAPS ทำให้ product_id ไม่ซ้ำในเวลาใดเวลาหนึ่ง แต่อนุญาตให้มีหลายแถวของสินค้าชิ้นเดียวกันได้หากช่วงเวลาไม่ทับซ้อน
• ภายในยังคงใช้ดัชนี GiST และต้องใช้ btree_gist สำหรับคอลัมน์ที่ไม่ใช่ช่วงเวลาในคีย์ แต่ Postgres จะจัดการ dependency ให้อัตโนมัติเมื่อเริ่มต้นข้อจำกัด
• รูปแบบการเขียนช่วง: [ คือรวม, ) คือไม่รวม → [2025-01-01, 2025-07-01) รวมวันที่ 1 มกราคม แต่ไม่รวมวันที่ 1 กรกฎาคม
  • แถว Gadget สุดท้าย [2026-01-01,) เป็นช่วงปลายเปิด (open-ended) หมายความว่ายังไม่มีวันสิ้นสุดที่กำหนดสำหรับราคาปัจจุบัน
• พฤติกรรมการป้องกันการทับซ้อน
  • เมื่อแทรกช่วงที่ไม่ถูกต้อง [2025-03-01, 2025-01-01) จะเกิดข้อผิดพลาดว่า “ขอบล่างของช่วงต้องน้อยกว่าหรือเท่ากับขอบบน”
  • เมื่อแทรกช่วงที่ทับซ้อน [2025-03-01, 2025-09-01) จะเกิดข้อผิดพลาดว่าละเมิดข้อจำกัดแบบไม่ให้ทับซ้อน products_pkey
  • การใช้ range เพียงอย่างเดียวทำให้ได้ การตรวจสอบสองแบบ พร้อมกัน

หั่นและแบ่ง - Slicing and Dicing

• กรณีเปลี่ยนราคาสินค้าเป็น $10.99 เฉพาะช่วงมีนาคมถึงกันยายน 2025 วิธีเดิมต้องแบ่งและแทรกแถวด้วยมือ ซึ่งหากพลาดจะเกิดช่องว่างหรือการทับซ้อน
• ในตารางประวัติแบบอิงช่วงเวลา สามารถสื่อความตั้งใจได้โดยตรง
  • UPDATE products FOR PORTION OF valid_at FROM '2025-03-01' TO '2025-09-01' SET price = 10.99 WHERE product_id = 1
• ผลคือแถวของ Widget เพิ่มจาก 3 แถวเป็น 5 แถว
  • แถว $9.99 เดิมถูกย่อเหลือ [2025-01-01, 2025-03-01)
  • เพิ่มแถวใหม่ราคา $10.99 ในช่วงที่เหลือ
  • แถว $12.99 เดิมถูกย่อเหลือ [2025-09-01, 2026-01-01)
  • เพิ่มแถวใหม่ราคา $10.99 ในช่วงที่เหลือ [2025-07-01, 2025-09-01)
• เหตุผลที่ $10.99 ถูกแบ่งเป็นสองแถว เพราะ FOR PORTION OF ประมวลผลแต่ละแถวที่จับคู่ได้อย่างอิสระ และไม่ได้รวมช่วงที่ติดกันภายหลัง (coalesce)
  • ผลลัพธ์สุดท้ายไม่มีช่องว่างหรือการทับซ้อน ซึ่งเป็นประโยชน์ที่ตรรกะข้อจำกัดแบบไม่ให้ทับซ้อน (exclusion logic) อย่างเดียวไม่มี
• กรณีขอบ (edge case)
  • หากช่วงของ FOR PORTION OF อยู่ภายในแถวเดิมแถวเดียวทั้งหมด จะสร้างแถวส่วนที่เหลือได้สูงสุด 2 แถว (หน้าและหลัง)
  • หากตรงกับขอบเดิมพอดี ก็ไม่จำเป็นต้องมีแถวส่วนที่เหลือ
• แถวส่วนที่เหลือตามช่วงเวลาที่สร้างขึ้นใหม่ไม่ต้องใช้สิทธิ์ INSERT แต่ทริกเกอร์ INSERT เดิมจะทำงาน → ต้องระวังใน audit logging หรือฟังก์ชันทริกเกอร์ SECURITY DEFINER

ลบประวัติ - Erasing History

• FOR PORTION OF ใช้กับ DELETE ได้เช่นกัน ตัวอย่างเช่นการนำสินค้าบางรายการออกจากแคตตาล็อกชั่วคราวระหว่างมิถุนายนถึงตุลาคม 2025
  • DELETE FROM products FOR PORTION OF valid_at FROM '2025-06-01' TO '2025-10-01' WHERE product_id = 2
• ผลลัพธ์
  • ช่วงมิถุนายนถึงตุลาคมถูกตัดออก และแถวราคา $22.99 ที่เคยครอบคลุม [2025-04-01, 2026-01-01) ถูกแบ่งเป็นสองแถวส่วนที่เหลือ คือแถวที่สิ้นสุดในเดือนมิถุนายนและแถวที่เริ่มในเดือนตุลาคม
  • ข้อมูลราคาก่อนและหลังช่องว่างถูกเก็บไว้เป็นค่าเดิม ส่งผลให้ DELETE ทำให้จำนวนแถวเพิ่มขึ้น
• กลไกจัดการตารางประวัติแบบอิงช่วงเวลาดำเนินการทั้งหมดให้อัตโนมัติ ลดความเสี่ยงจากการลบเกินหรือชิ้นส่วนกำพร้า (orphaned fragment) ในระดับแอปพลิเคชัน

ความจริงตามที่ประกาศ - Truth in Advertising

• ตารางประวัติแบบอิงช่วงเวลาจะไม่สมบูรณ์หากไม่มี คีย์นอกแบบอิงช่วงเวลา (temporal foreign key) และ Postgres 19 รองรับสิ่งนี้ด้วยคีย์เวิร์ด PERIOD
  • FOREIGN KEY (product_id, PERIOD valid_at) REFERENCES products (product_id, PERIOD valid_at)
• คีย์เวิร์ด PERIOD บอกว่าคีย์นอกนั้นอิงช่วงเวลา
  • product ที่ถูกอ้างอิงต้องมีอยู่ตลอด ช่วงเวลาทั้งหมด ของ valid_at ของ variant
  • การรวมกันของแถวที่จับคู่ทั้งหมดในตารางที่ถูกอ้างอิงต้องครอบคลุมช่วงเวลาของแถวที่อ้างอิงอย่างครบถ้วน
• การพยายามสร้าง variant ที่เกินช่วงเวลาของ product ([2025-01-01, 2027-01-01)) จะถูกปฏิเสธ
  • หากราคาของ Widget ถูกกำหนดไว้ถึงเพียงกลางปี 2026 แล้วมี variant ที่อ้างว่ามีผลถึงปี 2027 จะถูกปฏิเสธเพราะละเมิดข้อจำกัดคีย์นอก
• ข้อจำกัดสำคัญหนึ่งข้อ
  • คีย์นอกแบบอิงช่วงเวลารองรับเฉพาะ NO ACTION สำหรับการกระทำอ้างอิง (referential action) ไม่รวม CASCADE·SET NULL·SET DEFAULT
  • หากลบแถว product ที่ variant พึ่งพาอยู่ จะเกิดข้อผิดพลาดเสมอ เนื่องจากความซับซ้อนของการดำเนินการแบบลูกโซ่ตามช่วงเวลา และแอปพลิเคชันต้องจัดการอย่างชัดเจนเอง

ก้าวเล็ก ๆ - Baby Steps

• ความสามารถที่มีในตอนนี้: ตารางประวัติแบบอิงช่วงเวลาบนเวลาที่มีผลพร้อมการป้องกันการทับซ้อน, การจัดการข้อมูลตามช่วงเวลา, คีย์นอกแบบอิงช่วงเวลา
• สิ่งที่ขาดหายไปมากที่สุดคือ เวลาระบบ (system time) หรือที่เรียกว่าเวลาธุรกรรม
  • เวลาที่มีผลติดตามว่าข้อเท็จจริงเป็นจริงในโลกจริงเมื่อใด ส่วนเวลาระบบติดตามว่าฐานข้อมูลรับรู้ข้อเท็จจริงนั้นเมื่อใด และมีหลายระบบที่ใช้ทั้งคู่
  • ส่วนนี้คือพื้นที่ที่ส่วนขยาย pg_bitemporal เติมเต็มมาตั้งแต่ปี 2015
  • สามารถจำลอง (emulation) เวลาระบบด้วยทริกเกอร์ได้ แต่แตกต่างจากการที่เอนจินจัดการให้อย่างโปร่งใสเหมือนความสามารถใหม่ด้านช่วงเวลาอื่น ๆ
• เอกสารของตารางประวัติแบบอิงช่วงเวลาระบุชัดว่าเวลาระบบยังไม่รองรับแบบเนทีฟและสามารถจำลองได้ ยังไม่แน่ชัดว่าจะเพิ่มใน Postgres 20 หรือหลังจากนั้นหรือไม่ แต่รากฐานถูกวางไว้แล้ว

สรุปส่งท้าย - Final Thoughts

• วิธี EXCLUDE USING gist ใช้งานได้ แต่เป็นทางอ้อมที่ค่อนข้างหยาบ ส่วนขยายอย่าง pg_bitemporal ได้พิสูจน์แนวคิดและผลักดันการถกเถียงต่อเนื่องมา
• แนวทางใหม่นี้ตรงไปตรงมากว่าข้อจำกัดแบบไม่ให้ทับซ้อนของ GiST มาก
  • WITHOUT OVERLAPS ในคีย์หลักอ่านได้เหมือนภาษาอังกฤษธรรมดา และ FOR PORTION OF ก็อธิบายพฤติกรรมได้ตรงตัว
  • การ แบ่งแถวอัตโนมัติ เมื่ออัปเดตหรือลบตามช่วงเวลา ช่วยกำจัดบั๊กที่อาจเกิดขึ้นได้ทั้งกลุ่ม
• เส้นทางจาก SQL:2011 มาถึง Postgres 19 นั้นยาวนาน Hetti และชุมชนได้พิสูจน์มาหลายปีว่ารูปแบบนี้จำเป็นและทำได้จริง และตอนนี้ถูกนำเข้าคอร์แล้ว
• ควรจับตาการรองรับเวลาระบบในรีลีสอนาคต เมื่อ Postgres มีครบทั้งสองครึ่งของ bi-temporal ความเป็นไปได้จะขยายขึ้นอย่างมาก