ระบบเก่ายังอยู่ ระบบใหม่ก็ต้องมี: Sync 2 database with Transactional Outbox Pattern

เมื่อระบบเก่ายังเขียนลง MongoDB แต่ระบบใหม่ต้องการ PostgreSQL เป็น source of truth — เราจะ “Sync 2 database” อย่างไรไม่ให้ข้อมูลหลุดออกจากกัน?

ปัญหา classic ของ dual-write

• เขียน Postgres สำเร็จ แต่ Mongo พัง → ข้อมูลใหม่อยู่ใน Postgres แต่ Mongo ไม่มี → ที่เก่ามองไม่เห็นของ
• เขียน Mongo สำเร็จ แต่ Postgres พัง → สถานะกลับกัน
• Service ล่มกลางทาง

ระบบต้องการ atomic operation และที่สำคัญ ผู้ใช้ต้องไม่รอนาน

ภาพรวมของระบบ

1. Postgres + Job Table: Postgres เก็บทั้งข้อมูล และ table ของ pg-boss สำหรับ queue — ทำให้ insert ข้อมูล + insert job อยู่ใน transaction เดียวกัน
2. Sync Worker: process แยกที่ subscribe pg-boss queue เพื่อเขียน Mongo — แยกออกจาก request lifecycle เพื่อ retry/recover ได้
3. Polling Loop: หลัง enqueue Service B จะ poll สถานะ job จนเสร็จหรือ fail (3s) ก่อน return กลับให้ผู้ใช้ — ทำให้ API ยังรู้สึก “ซิงโครนัส”
4. Reconcile Loop: ตอน boot service — ตรวจ job ค้างคิว/ค้าง state แล้ว requeue ด้วย jitter เพื่อให้ระบบกลับมา consistent หลังพัง

pg-boss ทำงานยังไง ?

pg-boss คือ job queue ที่ใช้ Postgres เป็น storage

Concept สำคัญของ pg-boss

• Storage คือ Postgres table ทำให้ enqueue ทำใน transaction เดียวกับ business logic ได้

1. **SELECT … FOR UPDATE SKIP LOCKED** (FIG.05) สิ่งที่ทำให้ pg-boss scale worker หลายตัวได้โดยไม่มี duplicate processing
2. State machine — job ไม่หาย มันเปลี่ยน state ใน table เดียวกัน ทำให้ debug, monitor, reconcile ได้ง่าย
3. Retry built-in — กำหนด retryLimit, retryBackoff, retryDelay ได้ใน options
4. expireInSeconds — ถ้า worker หยิบ job ขึ้นมาแล้วเงียบ job จะถูก mark expired และ requeue อัตโนมัติ

ข้อจำกัดของ pg-boss

1. Retry Delay ขั้นต่ำคือ 1 วินาที ต้องเขียน jitter loop ก่อน enqueue เอง เนื่องจาก pg-boss เก็บ retryDelay เป็น integer (column type คือ int)
2. Worker Polling Interval: pg-boss ใช้ polling-based ไม่ใช่ push หรือ Postgres LISTEN/NOTIFY newJobCheckInterval (default 2 วินาที)

Outbox & Inbox Pattern: ทำไมต้องเขียน Queue ใน Transaction

ก่อนจะไปต่อ — มี 2 pattern ที่อยากให้รู้ และเป็นเหตุผลว่าทำไมถึงเลือก pg-boss มากกว่าการ “publish event ตรงๆ ไป Kafka/RabbitMQ/bullmq” pattern นี้ชื่อ Transactional Outbox กับ Inbox

แล้วเราเขียน 2 ที่ตรงๆเลยไม่ได้?

ถ้าเราไม่ใช้ outbox แล้วเขียน code ตรงๆ แบบนี้:

await db.transaction(async (trx) => {
  await trx.products.insert(product);   // 1) เขียน Postgres
});
await kafka.publish('product.created', product); // 2) publish event

มี race condition ที่เกิดได้หลายแบบ:

นี่คือ dual-write problem — ไม่ว่าจะเขียนลำดับไหน ก็ทำให้ระบบ inconsistent

Outbox Pattern: เปลี่ยน Dual-Write เป็น Single-Write

SELECT FOR UPDATE SKIP LOCKED: จุดสำคัญของ pg-boss

เมื่อ outbox table อยู่ใน Postgres และมี worker หลายตัว คอย poll งานพร้อมกัน จะเกิดปัญหา worker 2 ตัวอาจหยิบ job เดียวกันไปทำพร้อมกัน

pg-boss แก้ปัญหานี้ด้วย Postgres feature SELECT ... FOR UPDATE SKIP LOCKED :

• **FOR UPDATE** — บอก Postgres ว่า "แก้ไข row นี้" → lock เอาไว้ ไม่ให้ transaction อื่นแก้
• **SKIP LOCKED** — ถ้าเจอ row ที่ถูก lock โดย transaction อื่นอยู่แล้ว ให้ ข้ามไปเลย ไม่ต้องรอ → query คืนเฉพาะ row ที่ยังว่าง

Inbox Pattern: Consumer handles at-least-once duplicate

ปัญหา at-least-once delivery: pg-boss อาจส่ง job เดิมซ้ำในกรณี worker crash หลังเขียน Mongo ก่อน mark complete

Idempotency Key: สิ่งที่เชื่อมทุกอย่างไว้ด้วยกัน

ก่อนจะลงไปดู flow จริง — มีหนึ่งสิ่งที่เป็นส่วนสำคัญของทั้งระบบ นั่นคือ idempotency_key ตัวเดียวที่ถูกใช้ ทุกที่ ตั้งแต่ Postgres, document ใน MongoDB, ไปจนถึง job ID ในคิว

แนวคิดง่ายๆ คือ — ตอน Service B เริ่มทำงาน เราจะ generate UUID ขึ้นมา 1 ตัว แล้วเอาตัวเดียวกันนี้ไปฝังในทุกอย่างที่เกี่ยวกับ operation นี้:

ทำไมต้องเป็น Key เดียวกัน?

เพราะ idem key เดียว ทำหลายหน้าที่

• Correlation ID: ค้นหา row ใน Postgres, document ใน Mongo, และ job ใน queue ที่เกี่ยวกับ operation เดียวกันได้โดยใช้ key เดียว
• Dedup Guard: UNIQUE INDEX บน Postgres กัน duplicate insert; upsert by idem key บน Mongo กัน duplicate write จาก at-least-once delivery
• Rollback: rollback job รับ idem key มา แล้ว ลบทั้ง Postgres row และ Mongo doc ที่ key ตรงกัน
• Job ID: ใช้ idem key เป็น job.id ของ pg-boss → ถ้า reconcile ยิงซ้ำ pg-boss reject

การใช้ Idem Key ตอน Rollback

เมื่อ job หลัก fail หรือครบ retry แล้ว Service B จะส่ง rollback job โดยส่งแค่ idem key ตัวเดียว

ทำไม Rollback ถึง “ปลอดภัย” ที่จะรันซ้ำ เพราะ DELETE WHERE idempotency_key = ? ลบครั้งแรก row หาย, ลบครั้งที่ 2 ไม่เจอ row ก็ไม่ทำอะไร

Flow การทำงาน

Happy flow

1. Client → Service A → Service B
2. เปิด Transaction Postgres
3. เขียน Product + Enqueue Job ใน TXN เดียวกัน
4. Commit
5. Worker หยิบ Job ไปเขียน Mongo
6. Service B Polling สถานะ Job
7. Return ให้ Client

Alternative flow

• เมื่อ Job ฝั่ง Mongo พัง → Service B polling เจอ failed จะเช็คสถานะ transaction commit → สั่ง rollback ฝั่ง Postgres และ Mongo เพื่อให้ระบบกลับมา consistent

ทำไมต้อง “เช็ค Transaction commit แล้วก่อน rollback”

เพราะตอนเรา enqueue job ไป Postgres ระบบอาจล้มกลางทาง

Reconcile: เมื่อ Service ล่มแล้วรันขึ้นมาใหม่

สมมติหลัง Commit ไปแล้ว worker ดันล้มก่อนได้ job, หรือ Service B ค้าง polling อยู่แล้วถูก kill ไป เมื่อ service boot ขึ้นมาใหม่ เราต้องทำ reconcile เพื่อให้ระบบกลับมา consistent

ความสำคัญของ reconcile

read จาก pg-boss job table ใน Postgres เพราะตารางนี้คือ source of truth ของ job ที่ค้างอยู่

Trade-offs

Pros

• Atomic Enqueue: insert job ใน TXN เดียวกับ data → ไม่มีทางที่ data ลง Postgres แต่ job หาย
• Visibility: state ทุก job อยู่ใน Postgres → debug, monitor, audit ได้ด้วย query ปกติ, มี pg-boss dashboard
• Sync feel แต่ Async จริง: polling ทำให้ Service response user เหมือน sync — แต่ recover ได้แบบ async
• ใช้ Postgres เดิมเป็น queue → ไม่ต้อง ลดการ implement Redis/Kafka/bullmq

Cons

• rollback logic ต้องเขียนเอง, ต้องคิด edge cases เอง
• pg-boss Throughput: Postgres-based queue scale ได้จำกัด