วิธีต่าง ๆ ในการจับการเปลี่ยนแปลงใน Postgres

 (blog.sequin.io)
4 คะแนน โดย GN⁺ 2023-09-24 | 1 ความคิดเห็น | แชร์ทาง WhatsApp
  • หากต้องการส่งต่อการเปลี่ยนแปลงใน Postgres ไปยังระบบอื่นแบบเรียลไทม์ จำเป็นต้องใช้ CDC(Change Data Capture) และตัวเลือกแต่ละแบบตั้งแต่การแจ้งเตือนอย่างง่ายไปจนถึงการทำสำเนาบนฐาน WAL ก็มีความน่าเชื่อถือและภาระในการดูแลระบบต่างกันมาก
  • Listen/Notify เป็นจุดเริ่มต้นที่เบาที่สุด แต่ด้วยข้อจำกัดเรื่องการส่งแบบ at-most-once, การแจ้งเตือนชั่วคราว, และเพย์โหลดขนาดสูงสุด 8000 ไบต์ จึงใกล้เคียงกับสัญญาณเสริมมากกว่าจะเป็น CDC หลัก
  • การโพลตารางและ ตารางตรวจสอบ(audit table / outbox pattern) สามารถทำได้ด้วยตารางมาตรฐานและทริกเกอร์เท่านั้น แต่ต้องจัดการเรื่องการตรวจจับการลบ, diff, ลำดับการ commit, write amplification และ backpressure ด้วยตัวเอง
  • การทำสำเนาเชิงตรรกะ(logical replication) เป็นวิธีที่ทรงพลังในการสตรีม insert/update/delete จาก WAL แต่แอปพลิเคชันต้องจัดการ replication slot, ack, การรีสตาร์ต และการรองรับปริมาณงานด้วยตัวเอง
  • Sequin ใช้ logical replication ของ Postgres เป็นฐานเพื่อส่งต่อการเปลี่ยนแปลงไปยัง SQS, Kafka, Elasticsearch, Redis, HTTP endpoints และอื่น ๆ ช่วยลดภาระจากการต้องจัดการ replication slot โดยตรง

สถานการณ์ที่ต้องใช้ Postgres CDC

  • Postgres เก่งมากในการจัดการข้อมูลที่เก็บอยู่ แต่ถ้าต้องการใช้การเปลี่ยนแปลงในตารางเพื่อทริกเกอร์เวิร์กโฟลว์ หรือสตรีมข้อมูลแบบเรียลไทม์ไปยังแหล่งเก็บข้อมูล ระบบ หรือบริการอื่น ก็จำเป็นต้องออกแบบ การเคลื่อนย้ายข้อมูล แยกต่างหาก
  • Change Data Capture(CDC) คือแนวทางในการระบุและจับการเปลี่ยนแปลงในฐานข้อมูล แล้วส่งต่อไปยังระบบ downstream แบบเรียลไทม์
  • มีหลายวิธีในการจับการเปลี่ยนแปลงจาก Postgres โดยแต่ละวิธีมีความยากในการติดตั้งใช้งาน ความน่าเชื่อถือ และภาระในการปฏิบัติการแตกต่างกัน

Listen/Notify: pub-sub ที่ง่ายที่สุด

  • Listen/Notify ของ Postgres เป็นความสามารถสำหรับการสื่อสารระหว่างโปรเซส และทำงานตามแพตเทิร์น publish-subscribe
  • เซสชันสามารถ listen ช่องทางหนึ่งไว้ และกิจกรรมในฐานข้อมูลหรือเซสชันอื่นสามารถส่ง notify ไปยังช่องทางนั้นได้
  • สามารถใช้กับทริกเกอร์เพื่อจับการเปลี่ยนแปลงได้
    • ตัวอย่างทริกเกอร์จะสร้าง JSON ของ table, id, action ของเรคคอร์ดที่เปลี่ยนแปลงในจังหวะ after insert or update or delete แล้วเรียก pg_notify('table_changes', payload::text)
  • ข้อจำกัดค่อนข้างชัดเจน
    • มี semantics แบบ at-most-once และ listener ต้องเชื่อมต่ออยู่ในเวลาที่มีการส่งการแจ้งเตือน
    • listener จะได้รับเฉพาะการแจ้งเตือนหลังจากเริ่ม subscribe เท่านั้น ดังนั้นแม้จะหลุดจากเครือข่ายเพียงชั่วคราวก็อาจพลาดการแจ้งเตือนได้
    • ขนาดเพย์โหลดถูกจำกัดไว้ที่ 8000 ไบต์ และถ้าเกินคำสั่ง notify จะล้มเหลว
    • ขนาดเพย์โหลดยังนับรวมชื่อช่องทางด้วย และชื่อช่องทางก็อาจยาวได้สูงสุด 64 ไบต์เหมือน identifier ของ Postgres
  • ใช้ได้กับการตรวจจับการเปลี่ยนแปลงพื้นฐานหรือการปรับแต่งการโพลตารางให้มีประสิทธิภาพขึ้น แต่สำหรับความต้องการ CDC ที่ซับซ้อนอาจไม่เหมาะนัก

การโพลตาราง: เรียบง่ายแต่ไม่เก่งเรื่องการลบและ diff

  • วิธีจับการเปลี่ยนแปลงแบบ ทนทาน ที่ง่ายที่สุดคือการโพลตารางโดยตรง
  • แต่ละตารางต้องมีคอลัมน์อย่าง updated_at ที่อัปเดตทุกครั้งเมื่อแถวถูกแก้ไข และหากต้องการก็สร้างด้วยทริกเกอร์ได้
  • ใช้คู่ของ updated_at กับ id เป็นเคอร์เซอร์ และให้ลอจิกของแอปพลิเคชันเก็บและจัดการเคอร์เซอร์
  • หากใช้การ subscribe กับ Notify ร่วมด้วย ก็สามารถแจ้งแอปพลิเคชันได้ว่ามีการแทรกหรือแก้ไขเรคคอร์ด เพื่อลดความถี่ในการโพล
    • เนื่องจากการแจ้งเตือนของ Postgres เป็นแบบชั่วคราว จึงเหมาะจะใช้เป็นเพียงตัวเพิ่มประสิทธิภาพบนการโพลเท่านั้น
  • มีข้อเสียหลักอยู่สามข้อ
    • แถวที่ถูกลบจะไม่อยู่ในตารางแล้ว จึง ตรวจจับการลบ ไม่ได้
    • วิธีแก้คือให้ delete trigger บันทึก id และคอลัมน์ที่จำเป็นลงในตารางแยก เช่น deleted_contacts แล้วให้แอปพลิเคชันโพลตารางนั้น
    • เราอาจรู้ว่าเรคคอร์ดถูกอัปเดตแล้ว แต่ไม่รู้ว่า อะไรเปลี่ยนไปบ้าง
    • datetime และ sequence ของ Postgres อาจไม่เรียงตามลำดับ commit จึงอาจพลาดแถวที่ยังอยู่ระหว่าง commit ในระหว่างที่อ่านบล็อกตาม updated_at
  • เป็นตัวเลือกที่สมเหตุสมผลสำหรับการติดตามการเปลี่ยนแปลงแบบง่าย ๆ ที่การลบ, diff และการตกหล่นเป็นครั้งคราวไม่ใช่ปัญหาใหญ่

ตารางตรวจสอบ: เก็บบันทึกการเปลี่ยนแปลงด้วย outbox pattern

  • แนวทาง ตารางตรวจสอบ(audit table) คือการบันทึกการเปลี่ยนแปลงลงในตาราง changelog แยกต่างหาก และมักเรียกว่า outbox pattern
  • changelog สามารถมีคอลัมน์ที่เกี่ยวข้องกับการเปลี่ยนแปลงได้ เช่น
    • action: เป็น insert, update, delete
    • old: jsonb ของเรคคอร์ดก่อนเปลี่ยนแปลง โดยจะว่างในกรณี insert
    • values: jsonb ของฟิลด์ที่เปลี่ยนแปลง โดยจะว่างในกรณี delete
    • inserted_at: เวลาที่เกิดการเปลี่ยนแปลง
  • ในการติดตั้งใช้งาน ต้องมี trigger function ที่แทรกข้อมูลลง changelog ทุกครั้งที่มีการเปลี่ยนแปลง และมีทริกเกอร์สำหรับแต่ละตารางที่ต้องการเฝ้าดู
  • ยังสามารถใช้ changelog เป็นเหมือนคิวสำหรับการบริโภคข้อมูลได้ด้วย
    • worker ของแอปพลิเคชันจะดึงการเปลี่ยนแปลงจากตาราง
    • สามารถใช้ for update skip locked ของ Postgres เพื่อให้ได้การประมวลผลแบบใกล้เคียง exactly-once
    • worker สามารถเปิดทรานแซกชัน ล็อกข้อมูลเป็นชุดด้วย order by timestamp limit 100 for update skip locked จากนั้นประมวลผล ลบเรคคอร์ดที่เสร็จแล้ว แล้วค่อย commit
  • มีข้อเสียด้านการปฏิบัติการ
    • การเขียนลงตารางเดียวก่อให้เกิด write amplification เพราะมีการเขียนหลายครั้งลงในตารางตรวจสอบ
    • โดยทั่วไปจะมีอย่างน้อยสามครั้ง คือ insert เริ่มต้นลงตารางตรวจสอบ, update ระหว่างประมวลผล, และ delete หลังประมวลผลเสร็จ
    • หากจะ fan-out ผ่าน worker ก็ต้องออกแบบเองให้เหมาะกับแอปพลิเคชัน
    • ก่อนนำไปใช้ในโปรดักชันระดับใหญ่ มีโอกาสสูงที่จะต้องปรับ trigger function และโครงสร้างตาราง
    • อาจต้องพิจารณานโยบายย่อย เช่น เวลาสูงสุดที่ worker จะถือครองการ checkout การเปลี่ยนแปลงไว้ได้
    • หาก worker ประมวลผลไม่สำเร็จ ตารางตรวจสอบก็ยังคงสะสมข้อมูลต่อไป จึงจัดการ backpressure ได้ไม่ดีนัก

Foreign Data Wrapper: ตัวเลือกที่ใกล้เคียงกับการซิงก์ระหว่าง Postgres บางกรณี

  • Foreign Data Wrapper(FDW) เป็นความสามารถที่ทำให้ฐานข้อมูล Postgres อ่านและเขียนแหล่งข้อมูลภายนอกได้
  • ส่วนขยายแบบ FDW ที่รองรับแพร่หลายที่สุดคือ postgres_fdw
    • สามารถเชื่อมฐานข้อมูล Postgres สองตัว และสร้างโครงสร้างคล้าย view ที่อ้างอิงตารางของอีกฐานข้อมูลหนึ่งจากอีกฝั่งได้
    • ภายในแล้วฐานข้อมูล Postgres ตัวหนึ่งจะเป็นไคลเอนต์ และอีกตัวจะเป็นเซิร์ฟเวอร์
    • เมื่อ query foreign table ฐานข้อมูลฝั่งไคลเอนต์จะส่งคำสั่งไปยังฐานข้อมูลฝั่งเซิร์ฟเวอร์ผ่าน Postgres wire protocol
  • FDW ไม่ใช่วิธีจับการเปลี่ยนแปลงที่พบได้ทั่วไป และนอกเหนือจากสถานการณ์ที่เฉพาะมากก็ยากจะแนะนำ
  • หากต้องการเขียนการเปลี่ยนแปลงจากฐานข้อมูล Postgres หนึ่งไปยังอีกฐานข้อมูล Postgres หนึ่ง FDW อาจเหมาะสม
    • ตัวอย่างคือกรณีที่ใช้ฐานข้อมูลสำหรับงานบัญชีแยกจากฐานข้อมูลของแอปพลิเคชัน
    • สามารถข้ามขั้นตอนจับการเปลี่ยนแปลงระหว่างกลาง แล้วสะท้อนข้อมูลโดยตรงระหว่างฐานข้อมูลด้วย postgres_fdw
  • ยังสามารถสร้าง FDW เองเพื่อ POST การเปลี่ยนแปลงไปยัง API ภายในได้
    • เพราะเขียนไปยัง API ภายใน commit จึงสามารถให้ API ปฏิเสธการเปลี่ยนแปลงและ rollback commit ได้
  • FDW ทรงพลัง แต่ไม่ค่อยเป็นตัวเลือกที่ดีที่สุดสำหรับงาน CDC และการเขียน FDW เองก็ถือว่าเป็นงานใหญ่ที่สุดแบบหนึ่งในบรรดาวิธีจับการเปลี่ยนแปลง
    • การเขียน FDW เองง่ายขึ้นด้วยเครื่องมืออย่าง Supabase wrappers แต่ก็ยังเป็นงานใหญ่

ทำ logical replication เอง: CDC ทรงพลังบนฐาน WAL

  • Postgres มีโปรโตคอลสำหรับการทำสำเนาฐานข้อมูล และหนึ่งในนั้นคือ logical replication
  • logical replication สร้างอยู่บน WAL(write-ahead log) ของ Postgres
    • มีการติดตามทุก insert, update, delete ของฐานข้อมูล
    • การเปลี่ยนแปลงจะถูกสตรีมไปยัง subscriber
  • ผู้ใช้ต้องสร้าง replication slot บน primary ก่อน
    • ใช้รูปแบบ pg_create_logical_replication_slot('<your_slot_name>', '<output_plugin>')
  • output_plugin ใช้ระบุปลั๊กอินที่จะถอดรหัสการเปลี่ยนแปลงจาก WAL
    • pgoutput เป็นปลั๊กอินเริ่มต้น และส่งออกเป็นรูปแบบไบนารีที่ไคลเอนต์เซิร์ฟเวอร์คาดหวัง
    • test_decoding เป็นปลั๊กอินเอาต์พุตแบบง่ายที่แสดงการเปลี่ยนแปลงจาก WAL ในรูปแบบที่มนุษย์อ่านได้
    • แม้ไม่ใช่ปลั๊กอินที่มากับ Postgres แต่ wal2json ก็เป็นปลั๊กอินยอดนิยม และ JSON ก็จัดการได้ง่ายกว่าเป็นจุดเริ่มต้นสำหรับแอปพลิเคชันเมื่อเทียบกับรูปแบบไบนารีของ Postgres
  • หลังสร้าง replication slot แล้วก็สามารถเริ่มและบริโภคข้อมูลได้
    • replication slot ใช้ส่วนของโปรโตคอล Postgres ที่ต่างจาก query ปกติ
    • ไลบรารีฝั่งไคลเอนต์หลายตัวมีฟังก์ชันช่วยทำงานกับ replication slot
    • ตัวอย่าง psycopg2 ใช้ cursor.start_replication(...) และ cursor.consume_stream(...) เพื่อบริโภคข้อความ WAL และใช้ cursor.send_feedback(flush_lsn=msg.wal_end) เพื่อส่ง ack
  • ไคลเอนต์ต้องส่ง ack ให้ข้อความ WAL ที่ได้รับ และ replication slot ทำงานคล้าย Kafka ที่มี offset
  • logical replication เป็นวิธีที่ทนทานซึ่งถูกสร้างมาเพื่อ CDC โดยตรง แต่ก็ซับซ้อน
    • replication slot และ replication protocol ไม่คุ้นเคยกับนักพัฒนาเท่ากับตารางและ query ปกติ
    • จำเป็นต้องมีกลยุทธ์เพื่อไม่ให้พลาดข้อความระหว่างการรีสตาร์ต
    • ต้องออกแบบให้รองรับข้อความปริมาณมากที่ส่งออกมาจาก Postgres ได้

Sequin: เครื่องมือ CDC ที่ห่อหุ้ม logical replication

  • Sequin เป็นเครื่องมือ CDC ที่ส่งต่อการเปลี่ยนแปลงและแถวจาก Postgres ไปยังคิว สตรีม ดัชนีค้นหา แคช และ HTTP endpoints เป็นต้น
  • ปลายทางรวมถึง SQS, Kafka, Elasticsearch, Redis, HTTP endpoints และอื่น ๆ
  • Sequin ใช้ logical replication ของ Postgres ภายใน แต่ทำ abstraction ความซับซ้อนของโปรโตคอลระดับล่างไว้
  • สามารถจับ insert, update, delete ได้ทั้งหมด และในกรณี update กับ delete จะจับทั้งค่า new และ old ของแถว
  • เงื่อนไขที่เหมาะจะพิจารณา Sequin มีดังนี้
    • ต้องการ CDC แบบเรียลไทม์
    • ต้องการสตรีมตรงไปยังปลายทางอย่าง SQS หรือ webhook โดยไม่มีระบบกลาง
    • ต้องการฟีเจอร์อย่างการ backfill ข้อมูลเก่าและการกรองการเปลี่ยนแปลงด้วย SQL where clause
    • ต้องการทางเลือกที่ง่ายกว่าการจัดการ replication slot ด้วยตัวเอง
    • ต้องการการรับประกันการประมวลผลแบบ exactly-once
  • แต่ก็มีข้อเสีย
    • Sequin ไม่ใช่ส่วนขยายภายใน Postgres แต่เป็น เครื่องมือของ third party ที่รันอยู่ข้างฐานข้อมูล
    • เพราะไม่ใช่ extension จึงเข้ากันได้กว้างกับฐานข้อมูล Postgres หลายแบบ แต่ถ้าไม่ได้ใช้ Sequin Cloud ก็ต้องตั้งโครงสร้างพื้นฐานเพิ่มเอง

เกณฑ์ในการเลือก

  • ในระยะเริ่มต้น Listen/Notify และการโพลตารางเหมาะสมที่สุด
    • Listen/Notify เหมาะกับการจับเหตุการณ์ที่ไม่สำคัญมาก การทำต้นแบบ และการปรับปรุงการโพลให้มีประสิทธิภาพ
    • การโพลเป็นทางแก้ที่ตรงไปตรงมาและใช้งานได้ดีกับกรณีใช้งานแบบง่าย
  • ในระยะที่จริงจังขึ้น ตารางตรวจสอบอาจเป็นตัวเลือกระดับกลาง
    • สามารถจับ payload ของ new และ old ของแถวได้
    • หากทำอย่างถูกต้อง ก็สามารถได้ระบบประมวลผลแบบ exactly-once
    • เมื่อขยายระบบ ปัญหา write amplification และการขาดการจัดการ backpressure จะเด่นชัดขึ้น และหากตั้งค่าด้วยมือผิดพลาดก็อาจทำข้อความตกหล่นได้
  • ในระยะขยายระบบ logical replication เป็นแนวทางที่ใกล้เคียงกับคำตอบที่ทนทานที่สุด
    • อย่างไรก็ตาม แนะนำให้ใช้เครื่องมืออย่าง Sequin มากกว่าการอ่านจาก slot โดยตรง
  • FDW เป็นความสามารถที่น่าสนใจ แต่มีโอกาสน้อยที่จะตอบโจทย์ความต้องการ CDC ทั่วไป

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

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

 
GN⁺ 2023-09-24
ความคิดเห็นจาก Hacker News

  • ทริกเกอร์ + ตารางประวัติ (ตาราง audit) คือคำตอบที่ถูกต้องใน 98% ของกรณี ถ้ายังไม่ได้ใช้ ก็เริ่มใช้ได้ตั้งแต่วันนี้ เป็นเทคนิคที่ผ่านการพิสูจน์มากว่า 30 ปีแล้ว
    ตัวอย่างง่าย ๆ สำหรับการทำแบบ generic อยู่ที่ https://gist.github.com/slotrans/353952c4f383596e6fe8777db5d... เป็นแนวทางที่ยอมสละประสิทธิภาพด้านพื้นที่จัดเก็บ แล้วเลือก “ทำให้ง่าย”
    ถ้าเก็บข้อมูลแบบ immutable ได้ก็คงดีมาก แต่ในฐานข้อมูลน่าจะมี ข้อมูลที่เปลี่ยนแปลงได้ จำนวนมหาศาล และมีโอกาสสูงว่าคุณกำลังลืมหลายสิ่งไปทุกวัน อย่าลืม ใช้ตารางประวัติซะ
    อ้างอิง: https://github.com/matthiasn/talk-transcripts/blob/master/Hi...
    ไม่ควรใช้ไลบรารีหรือเทคนิคติดตามประวัติในชั้นแอปพลิเคชันอย่าง Papertrail เพราะช้าและเกิดข้อผิดพลาดได้ง่าย อีกทั้งจับการเปลี่ยนแปลง DB ที่อ้อมผ่าน app stack ไม่ได้ การพยายามให้แอปเป็นคนประทับเวลา updated ก็ผิดโดยพื้นฐาน เพราะแต่ละเว็บเซิร์ฟเวอร์มีนาฬิกาไม่ตรงกัน ต้องใช้ นาฬิกาของ DB และนั่นคือนาฬิกาเดียวที่ถูกต้อง

    • เพื่อความสอดคล้อง ไม่ควรสร้างเวลาในไคลเอนต์ แต่ควรใส่การเรียกอย่าง now() ไว้ใน query เพื่อใช้ นาฬิกาของ DB
      แต่การซิงก์โดยอาศัย timestamp นี้อย่างเดียวไม่เพียงพอ เพราะ timestamp ถูกสร้าง ณ เวลาเริ่ม transaction ไม่ใช่เวลาที่ transaction commit
      หาก poll ตารางแล้วกรองด้วย timestamp ล่าสุด อาจพลาดบาง transaction ที่ลำดับ commit สลับกันได้ อาจตั้ง buffer โดยย้อนเวลาไปอีกหลายนาทีแล้วกำจัดรายการซ้ำได้ แต่ใน PostgreSQL ระยะเวลาของ transaction ไม่มีขีดจำกัด และถ้าย้อนไปไกลเกินไปก็สิ้นเปลืองมาก ถ้าความถูกต้องและประสิทธิภาพสำคัญ วิธีนี้ไม่เหมาะ
    • Estuary (https://estuary.dev ผมเป็น CTO) สร้าง change log ของ data lake แบบเรียลไทม์ สำหรับการเปลี่ยนแปลงทั้งหมดในฐานข้อมูลไว้บน cloud storage โดยไม่ต้องตั้งค่าเพิ่มเติมใน DB ที่ใช้งานจริง
      หากใช้ log sequence number, เวลา DB และ REPLICA IDENTITY FULL ก็จะรวมสถานะก่อน/หลังการเปลี่ยนแปลงด้วย จากนั้นเมื่อ materialize collection ไปยังที่อย่าง Snowflake ก็จะได้ตารางซิงก์ที่ตามการอัปเดตของ source DB เป็นพื้นฐาน
      จาก data lake พื้นฐานเดียวกัน ยังสามารถแปลงหรือ materialize ประวัติทั้งตารางเพื่อวัตถุประสงค์ด้าน audit ได้ด้วย จึงไม่ต้องไปติดตัวจับข้อมูลหรือ WAL reader เพิ่มที่ source DB อีก
    • ถ้าอ้างอิง session variable ในทริกเกอร์ ก็สามารถใส่ข้อมูลเพิ่มเติมอย่างคอมเมนต์เกี่ยวกับเหตุผลของการเปลี่ยนแปลงลงในประวัติได้ เคยลองแค่ในโปรเจกต์ส่วนตัวเล็ก ๆ แต่จนถึงตอนนี้ก็ทำงานได้ดี
    • ผมพอร์ตตัวอย่างไปเป็น SQLite และสาธิตการทำงานไว้: https://chat.openai.com/share/b5113cb1-10df-4a38-adde-5ec0e7...
      ผมยังอธิบายวิธีของ SQLite ที่ใช้แพตเทิร์นคล้ายกัน แต่ทำแบบอิงคอลัมน์แทน JSON แยกไว้อีกด้วย: https://simonwillison.net/2023/Apr/15/sqlite-history/
    • แนวทางนี้ดี และจริง ๆ แล้ว activity feed ของแอปก็ทำแบบนี้อยู่ อย่างไรก็ตาม มันไม่ได้แก้ปัญหา “การ push การเปลี่ยนแปลงออกไปข้างนอก” โดยตรง แน่นอนว่าถ้า listen การเปลี่ยนแปลง WAL ของตาราง audit ก็จะได้ข้อดีทั้งสองอย่าง

  • บทความนี้สรุปแนวทางต่าง ๆ ที่ทำได้ด้วยฟีเจอร์พื้นฐานของ Postgres ไว้ได้กระชับดี
    ในส่วน “จับการเปลี่ยนแปลงลงในตาราง audit” ที่บริษัทก่อนหน้านี้ เราใช้ แพตเทิร์น Temporal Tables ได้ผลดีมาก แม้ Postgres เองจะไม่มีในตัว ต่างจาก RDBMS รายใหญ่ตัวอื่น ๆ แต่มีแพตเทิร์นเรียบง่ายที่ใช้งานผ่านฟังก์ชัน SQL ได้: https://github.com/nearform/temporal_tables
    สามารถดูสถานะของตาราง ณ เวลาหนึ่ง ๆ ได้ จึงตอบคำถามอย่าง “วันที่ 12 สิงหาคม การตั้งค่าของผู้ใช้รายนี้คืออะไร”, “เมื่อคืน 23:55 มีเรคคอร์ดที่ยังไม่ประมวลผลกี่รายการ”, “ขอให้แสดงความแตกต่างของ feature flag ระหว่างตอนนี้กับหนึ่งสัปดาห์ก่อน” ได้

  • เคยไปให้คำปรึกษากับบริษัทหนึ่งที่มี SQL Server แบบโมโนลิธขนาดใหญ่มาก มาก่อน แม้จะไม่ใช่ Postgres แต่ถ้าสมมติว่าเป็น Postgres ก็คงคล้ายกัน
    มันถูกใช้งานมาหลายสิบปี และถูกใช้กับสารพัดงานภายในบริษัท แทบทุกแอปพลิเคชันและกระบวนการทางธุรกิจทั้งบริษัทต่างก็บันทึกข้อมูลลงในฐานข้อมูลนี้
    ปัญหาคือมีแอปพลิเคชันจำนวนมากที่อ่าน DB นี้ และมีกระบวนการกับขั้นตอนที่แทรก·แก้ไขข้อมูลเยอะมาก จนเมื่อกระบวนการแทรก·แก้ไขฝั่ง upstream เปลี่ยนไปหรือมีของใหม่เพิ่มเข้ามา ก็เกิดเหตุที่ไปทำลาย invariant ระดับแอปพลิเคชัน แม้แต่กระบวนการปกติก็ทำงานต่างออกไปหากมีข้อมูลเสีย
    การไล่หาสาเหตุทำได้ยากมาก เพราะสิ่งที่ต้องไปดูส่วนใหญ่เขียนไว้เมื่อ 10 ปีก่อน และพนักงานเหล่านั้นก็ออกจากบริษัทไปแล้ว
    เลยสงสัยว่าจะจับการเปลี่ยนแปลงในฐานข้อมูล Postgres ให้อยู่ในรูป DAG บางแบบได้ไหม เพื่อให้รู้ว่ากระบวนการใดแทรก·แก้ไข·ลบข้อมูล และในอดีตมันมีพฤติกรรมอย่างไร รวมถึงแอปพลิเคชันต่าง ๆ อ่านข้อมูลนี้อย่างไร และสถิติของ query เปลี่ยนไปตามเวลาอย่างไร
    ไม่แน่ใจว่ามีกรณีตัวอย่างแบบนี้มาก่อนหรือไม่ หรือควรใช้แนวทางใดจึงจะสร้างเครื่องมือแบบนี้ได้ เคยคิดจะทำอะไรคล้าย ๆ กันมาก่อน แต่ดูเหมือนเป็นขอบเขตที่ต้องเข้าใจระดับวิศวกรแกนหลักของ Postgres ถึงจะตัดสินใจได้ดี

    • Logical replication ของ Postgres มีคำสั่งการเปลี่ยนแปลงทั้งหมดสำหรับสร้างสถานะเดียวกันขึ้นใหม่ในฐานข้อมูลอื่นในเชิงตรรกะ กล่าวคือมีข้อมูลการแทรก·แก้ไข·ลบครบ
      แต่ในแต่ละการเปลี่ยนแปลงจะไม่ได้ข้อมูลต้นทางระดับ client มาด้วย
      ถึงอย่างนั้นก็มีทางเลี่ยงได้ สตรีม logical replication สามารถรวมข้อความข้อมูลจากฟังก์ชัน pg_logical_emit_message ได้ ดังนั้น client จึงใส่ metadata เองได้ อาจตั้งค่าให้ปล่อยตัวระบุ client ตอนเริ่มแต่ละ transaction ได้ด้วย
    • ไม่รู้ว่าจะจัดการ query อย่างไร แต่สำหรับการแทรก·แก้ไข เราใส่คอลัมน์ติดตาม แหล่งที่มาของเหตุการณ์ (last updated by) ไว้ อาจเป็น antipattern ก็ได้ จึงอยากได้วิธีที่แข็งแรงกว่านี้
    • ในเชิงเทคนิค log replication มีทุกการกระทำที่ทุก actor ทำไว้ทั้งหมด และถ้าใช้ trigger อย่างระมัดระวัง ก็สามารถติดตามทุกอย่างด้วย ตารางจับ DDL/DML ได้เช่นกัน ถ้ากังวลเรื่อง DCL ก็รวมเข้าไปได้
      แนวทางนี้ใช้ได้กับโซลูชันตระกูล SQL เกือบทั้งหมดที่ใช้ WAL หรือ trigger
      เคยใช้วิธี trigger กับ SQL Server หลายครั้ง แต่ถ้า log ทุก query ก็มักจะช้าลง การออกแบบกลไก insert ที่ไม่ขวางงาน production นั้นไม่สมบูรณ์แบบ และอาจต้องใช้ sampling
    • แค่ให้แต่ละแอปพลิเคชันมี DB user ของตัวเอง ก็ได้ข้อมูลค่อนข้างมากแล้ว
    • เคยมีไอเดียว่าจะไล่ดูสคริปต์และโปรแกรมทั้งหมดที่ส่ง query เข้า DB แล้วใส่ คอมเมนต์ ID เฉพาะ ในแต่ละ query ที่เชื่อมกลับไปยังสคริปต์/โปรแกรมนั้น ถ้าใน query log เหลือคอมเมนต์กับ ID นั้นไว้ ก็น่าจะติดตามแหล่งที่มาได้

  • ถ้าจะไปทาง “ตาราง audit” ก็ใช้ pgaudit ได้เลย เป็น extension ที่ผ่านการพิสูจน์ในการใช้งานจริง และถ้าใช้ AWS ก็ใช้บน RDS ได้ด้วย
    https://github.com/pgaudit/pgaudit/blob/master/README.md
    https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appen...

  • ไม่จำเป็นต้องทำก็ได้ การอยากได้สิ่งนี้หมายถึงการเปลี่ยน relation ของ Postgres ให้เป็น สัญญา ซึ่งจะทำให้ไม่มี service ใดสามารถ persist สถานะภายในของตัวเองได้
    ถ้าทุ่มเทกับ Domain-Driven Design จริง ๆ ก็อาจเป็นไปได้ แต่ใช้ ระบบ event-driven ที่เบาและใช้งานได้จริงจะดีกว่า

    • relation ของฐานข้อมูลนั้น ไม่ว่าชอบหรือไม่ ก็เป็น สัญญา อยู่แล้ว
      อะไรสักอย่างแบบ event-driven ซับซ้อนกว่าพันเท่า

  • วิธี polling คอลัมน์ updated_at ในรูปแบบที่ง่ายที่สุดนั้นไม่แข็งแรง เพราะไม่มีการรับประกันว่า transaction จะ commit ตามลำดับนั้น

    • ผมเป็นผู้เขียน ประเด็นนี้ดีมาก เช่น Transaction A เริ่มขึ้น before trigger ทำงาน และตั้งค่า updated_at ของ Row 1 เป็น 2023-09-22 12:00:01
      ถัดมาไม่นาน Transaction B เริ่มขึ้น ตั้งค่า updated_at ของ Row 2 เป็น 2023-09-22 12:00:02 แล้ว B commit ก่อน
      query สำหรับ polling ทำงาน เห็น Row 2 เป็นการเปลี่ยนแปลงล่าสุดและอัปเดต cursor เป็น 2023-09-22 12:00:02 จากนั้นถ้า A ค่อยมา commit ภายหลัง ก็จะพลาด Row 1
      วิธีง่าย ๆ ที่หลีกเลี่ยงปัญหานี้คือไม่ polling แบบเกือบ real-time เพราะสุดท้ายลำดับจะสอดคล้องกันเอง
      ข้อเสนอที่แข็งแรงกว่าน่าจะเป็นการใช้ sequence เช่น มีคอลัมน์ updated_at_idx ที่เพิ่มค่าทุกครั้งเมื่อ row ถูกเปลี่ยน
    • เพิ่งรู้เรื่องนี้เป็นครั้งแรก ถ้าใช้ trigger เพื่ออัปเดตคอลัมน์ก็ยังเป็นแบบนั้นหรือเปล่า?
      สงสัยว่าถ้าใช้ before trigger ที่ใส่ now() timestamp ของ updated_at ในสองแถวอาจไม่ตรงกับลำดับ commit ของ transaction ได้หรือไม่ updated_at ไม่จำเป็นต้องเท่ากับ commit timestamp แต่ updated_at ควรแสดงลำดับ commit ได้แม่นยำในระดับมิลลิวินาที/ไมโครวินาที
    • สำหรับ polling เราใช้คอลัมน์ _txid แทน updated_at โดยให้ trigger ตั้งค่าเป็น transaction ID ปัจจุบัน จากนั้นตอน polling ก็ใช้ txid_current() เพื่อตรวจว่า transaction ใด commit แล้วและ transaction ใดยังไม่ commit
      ค่อนข้างหวาดเสียวและเกิด off-by-one ได้ง่ายมาก แต่ก็ทำงานได้ดีใน production มาหลายปีแล้ว

  • บทความยอดเยี่ยมมาก
    ถ้าใช้ Elixir กับ Postgres ผมทำไลบรารีเล็ก ๆ ที่ฟังการเปลี่ยนแปลงของ WAL ด้วยแนวทางคล้ายกันไว้: https://github.com/cpursley/walex

  • วิธีเหล่านี้ทั้งหมดดูไม่ค่อยดีเท่าไร และโดยส่วนตัวผมมองว่า การโพลลิง ใช้งานได้จริงที่สุด
    อยากให้ Postgres สร้างนวัตกรรมในด้านนี้

    • เคยมีความพยายามที่จะใส่ temporality หลายชนิดเข้าไปเป็นฟีเจอร์ระดับ first-class ในมาตรฐาน SQL
      ผมคิดว่าจนกว่าจะถูกบรรจุในมาตรฐาน SQL ก็ยากที่จะเกิดแรงผลักดันในพื้นที่ kernel ของ DBMS เชิงสัมพันธ์ ทางเลือกมีมากและซับซ้อน อีกทั้งโซลูชันที่ประสบความสำเร็จใน user space ก็ไม่ได้มีภาระด้านประสิทธิภาพมากเกินไปนัก
      อนึ่ง คนที่วิจัยด้านนี้โดยมากจะเอนเอียงไปทางแนวทาง ตาราง audit เพราะยังคงคุณสมบัติ ACID ที่สอดคล้องกันไว้ภายในฐานข้อมูล และทำให้ Postgres ยังคงเป็นจุดล้มเหลวเดียว แทนที่จะเพิ่มพร็อกซีหรืองานโพลลิงเข้ามา
    • ช่วงโพลลิง 1 วินาที ใช้งานได้จริงหรือไม่?

  • ในโลกของข้อมูลมีช่องว่างขนาดใหญ่อยู่ แทนที่จะถามผลลัพธ์จากแหล่งเก็บข้อมูล คงจะดีถ้า ผลลัพธ์ของคิวรีถูก push แบบเพิ่มทีละส่วน
    เราทำ real-time/streaming analytics กันมาก สามารถทำ stream processing ได้ และบางส่วนก็ประมวลผลเป็น materialized view ภายในแหล่งเก็บข้อมูลได้ แต่หลังจากข้อมูลเข้าไปอยู่ใน DB หรือ data lake แล้ว ถ้าต้องการดูการเปลี่ยนแปลงจาก downstream ก็แทบจะต้องกลับไปโพลลิงอีก
    ถ้าต้องการตอบสนองเมื่อมีบางสถานการณ์เกิดขึ้นในข้อมูล หรืออัปเดตหน้าจอโดยไม่ต้อง refresh หน้า ก็แทบไม่มีโซลูชันที่สะอาด วิธีในบทความนี้ก็ดูใกล้เคียงกับ workaround มากกว่าฟีเจอร์ระดับ first-class
    ถ้าต้องการทำรายงานที่อัปเดตแบบเรียลไทม์โดยไม่ต้อง refresh หน้า โดยทั่วไปจะกลายเป็นการโหลดข้อมูลจาก DB แล้วส่งการเปลี่ยนแปลงไปยัง GUI ผ่าน Kafka และ WebSocket แบบนั้นก็จะต้องดูแล Lambda architecture แปลก ๆ ที่วิเคราะห์บางส่วนด้วยโค้ด บางส่วนด้วย DB
    มีนวัตกรรมในด้านนี้อยู่ KSQL และ Kafka Streams ส่งการเปลี่ยนแปลงออกมาได้, Materialize มี subscription และ ClickHouse มี live view แต่ฟีเจอร์จำนวนมากยังใหม่หรืออยู่ในขั้น preview และไม่ได้พอดีกับความต้องการนัก ผมลองใช้มาหมดแล้ว แต่รู้สึกว่ามันผลักภาระงานไปให้ developer มากเกินไป
    ถ้ามีไลบรารีที่รับ change feed ได้ทันทีด้วยตัวเลือกอย่าง [select * from orders with suscribe] ก็คงดี เป็นด้านที่สำคัญพอสมควรแต่ที่ผ่านมาได้รับความสนใจน้อย

  • มีหลุมพรางใหญ่ของ replication ที่บทความไม่ได้พูดถึง และเพราะแบบนั้นผมจึงไม่ใช้ replication
    Postgres พยายามรับประกันอย่างแข็งแรงมากว่าผู้บริโภคของ replication slot จะไม่พลาดข้อมูล ดังนั้นถ้าผู้บริโภคไม่ consume ข้อมูลจาก slot, Postgres ก็จะเก็บข้อมูลที่พลาดไว้ให้อย่างใจดีต่อไป และสุดท้ายก็ไปจนดิสก์เต็มแล้ว DB ล้ม ผมเจอมาแล้วกับ SaaS DB สองเจ้าระหว่างทำ prototype และวิธี recovery ก็มีแค่ต้องเปิด support ticket
    ถ้า replication slot consumer หยุดอ่าน จะต้องมีการแจ้งเตือนดังขึ้นเสมอ
    อีกเหตุผลหนึ่งคือ code path สำหรับดึง initial snapshot ของตารางกับ code path สำหรับอ่านการเปลี่ยนแปลงนั้นต่างกันโดยสิ้นเชิง การ initialize การอ่าน replication slot โดยไม่ให้พลาดการเปลี่ยนแปลงแม้แต่รายการเดียวไม่ใช่เรื่องเล็กน้อย
    น่าเสียดายที่จากมุมมอง change capture แล้ว replication เป็นโซลูชันที่ hacky น้อยที่สุด
    ผมใช้การโพลลิง แต่แทนที่จะใช้ updated_at ผมเก็บ txid

    • สามารถตั้ง ขีดจำกัดขนาด ให้ slot ถูกทำเครื่องหมายว่า invalid เมื่อเกินขนาดหนึ่ง แทนที่จะยึดพื้นที่ไว้เรื่อย ๆ ได้: https://www.postgresql.org/docs/current/runtime-config-repli...
      อยากรู้ว่าคุณต้องการพฤติกรรมแบบไหนมากกว่ากัน
      ถ้าจัดการกับปริมาณข้อมูลขนาดใหญ่ ก็จะเริ่มอยากให้ initial snapshot กับการอ่านการเปลี่ยนแปลงถูกจัดการต่างกัน เพราะต้องทำงานอย่าง parallel initialization หรือการ initialize บนฐานของ physical backup ได้ อย่างไรก็ดี เข้าใจว่าฟีเจอร์ที่ช่วย stream ข้อมูลเดิมแบบเลือกได้หลังสร้าง slot อาจมีประโยชน์
      ส่วนการ initialize การอ่าน replication slot เพื่อไม่ให้พลาดการเปลี่ยนแปลงนั้นไม่น่าจะยาก เลยอยากรู้ว่าติดตรงไหน
    • หนึ่งในเทคนิคในการจัดการปัญหาแรกคือการส่ง logical decoding message ให้ตัวเอง วิธีนี้จะช่วยรักษา WAL ที่ถูกเก็บไว้ให้อยู่ในระดับต่ำได้
      เมื่อไม่ต้องการการเปลี่ยนแปลงทั้งหมด temporary replication slot ที่ล้างตัวเองเมื่อการเชื่อมต่อหลุดก็มีประโยชน์เช่นกัน นอกจากนี้ยังมี configuration สำหรับตั้งค่าสูงสุดของ WAL ที่เก็บไว้เพื่อไม่ให้เซิร์ฟเวอร์ล้ม
    • เคยตกหลุมพรางนี้มาแล้ว มันละเอียดอ่อนจริง ๆ ถ้าเอาผู้บริโภคออก ก็ดูเหมือนว่าไม่น่าจะมีผลอะไรกับ primary DB แต่ในความเป็นจริงกลับเกิด ระเบิดเวลา ขึ้น
      อยากให้ช่วยอธิบายเพิ่มเติมว่าใช้ txid แทน updated_at อย่างไร