Day 8: Pillar 4 - Pre-computation with Materialized Views & Background Scheduling Engine with pg_cron (กลไก Pre-computation ด้วย Materialized Views, การทำ Incremental/Concurrent Refresh, และการตั้งเวลาประมวลผลฉากหลังอัตโนมัติด้วย pg_cron)

เสาหลักต้นที่สี่คือการเสิร์ฟรายงาน — คำนวณของยากไว้ล่วงหน้าฉากหลัง แล้วให้แดชบอร์ดอ่านผลที่พร้อมแล้ว ไม่ไปดึง CPU ของระบบซื้อขายหน้าร้าน

โดยรวมวันนี้คือการคำนวณรายงานยากๆ ล่วงหน้าแล้วเซฟลง Materialized View โดยให้ pg_cron สั่งรีเฟรชฉากหลังแบบไม่บล็อกคนใช้งาน (REFRESH CONCURRENTLY)

ช่วยแยกงานคำนวณรายงานหนักๆ ออกจากงานซื้อขายหน้าร้าน ทำให้แดชบอร์ดเปิดได้เร็ว โดยไม่ดึง CPU ของระบบหลัก

ในระบบงานระดับ Enterprise คำสั่งประเภทวิเคราะห์ข้อมูล (Analytical & Dashboard Queries) มักต้องทำ SUM, AVG, COUNT และ GROUP BY บนตารางประวัติธุรกรรมที่มีข้อมูลหลายสิบล้านถึงหลายร้อยล้านแถว หากปล่อยให้ผู้ใช้งานหรือแดชบอร์ดเรียกยิง Query เหล่านี้สดๆ บนตาราง OLTP ในช่วงเวลาที่มีปริมาณการใช้งานสูงสุด (Peak Hours) ฐานข้อมูลจะต้องสแกนดิสก์และใช้ CPU มหาศาล จนทำให้ระบบรับชำระเงินและหน้าบ้านค้างล่ม!

⚖️ การเปรียบเทียบเชิงสถาปัตยกรรม: Standard View vs. Materialized View ใน PostgreSQL

การสร้างระบบ Pre-computation ใน PostgreSQL ต้องเริ่มตั้งแต่การใช้คำสั่ง CREATE MATERIALIZED VIEW, การสร้าง UNIQUE INDEX เพื่อเปิดทางให้ทำ Concurrent Refresh, และการผูกเข้ากับ pg_cron สั่งงานอัตโนมัติ:

-- [สเต็ปที่ 1: สร้าง Materialized View บันทึกผลลัพธ์ลงดิสก์]
CREATE MATERIALIZED VIEW mv_daily_sales_summary AS
SELECT
    order_date,
    category_id,
    COUNT(order_id)   AS total_orders,
    SUM(total_amount) AS total_revenue
FROM orders
GROUP BY order_date, category_id
WITH DATA; -- 🎯 [1] คำนวณผลลัพธ์และเซฟลง Data Pages บนดิสก์ทันที

-- [สเต็ปที่ 2: สร้าง Unique Index จำเป็นสำหรับการทำ REFRESH CONCURRENTLY]
CREATE UNIQUE INDEX idx_mv_daily_sales_key
ON mv_daily_sales_summary (order_date, category_id); -- 🎯 [2] ต้องมี Unique Key สำหรับ Merge ข้อมูล

-- [สเต็ปที่ 3: สั่งรีเฟรชแบบ Non-blocking (อ่านข้อมูลได้ตลอดเวลาขณะรีเฟรช)]
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales_summary; -- 🎯 [3] ไม่ล็อกตาราง SELECT อ่านได้ตลอด

-- [สเต็ปที่ 4: ตั้งเวลาทำงานอัตโนมัติด้วย pg_cron (เช่น รันทุก 15 นาที)]
SELECT cron.schedule(
    'refresh_daily_sales_job',                           -- [4.1] ชื่อ Job ในระบบ
    '*/15 * * * *',                                      -- [4.2] Cron Expression (ทุก 15 นาที)
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales_summary' -- [4.3] คำสั่ง SQL ที่จะรัน
);

🏋️‍♂️ แบบฝึกหัดไต่ระดับการเขียนจริง (จากระดับเริ่มต้น ➔ Pre-computation Engine ระดับ Production)

-- [1] สร้าง Materialized View สำหรับสรุปยอดคงเหลือสินค้ารายคลัง
CREATE MATERIALIZED VIEW mv_warehouse_inventory_summary AS
SELECT
    warehouse_id,
    COUNT(product_id) AS total_products,
    SUM(quantity)     AS total_stock_quantity
FROM inventory
GROUP BY warehouse_id
WITH DATA;

-- 📤 [2] เรียกดูข้อมูล (เร็วเท่ากับการ SELECT ตารางปกติ เพราะอ่านจากดิสก์ที่คำนวณไว้แล้ว)
SELECT * FROM mv_warehouse_inventory_summary WHERE warehouse_id = 10;

-- 🔄 [3] สั่งรีเฟรชข้อมูลแบบธรรมดา
-- ⚠ หมายเหตุ: คำสั่งนี้จะวาง Exclusive Lock บน MV ทำให้ผู้ใช้คนอื่น SELECT อ่านไม่ได้ชั่วคราวขณะรีเฟรช
REFRESH MATERIALIZED VIEW mv_warehouse_inventory_summary;

-- [1] สร้าง Unique Index บนคอลัมน์ที่เป็น Key หลักของ Materialized View
CREATE UNIQUE INDEX idx_mv_wh_inv_pk
ON mv_warehouse_inventory_summary (warehouse_id);

-- 🚀 [2] สั่งรีเฟรชแบบ CONCURRENTLY (Non-blocking)
-- ผลลัพธ์: ผู้ใช้งานยังคง SELECT อ่านข้อมูลจาก MV ได้อย่างราบรื่นขณะที่ระบบกำลังประมวลผลอัปเดตข้อมูลฉากหลัง!
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_warehouse_inventory_summary;

-- 💡 ตรวจสอบขนาดพื้นที่ของ Materialized View บนดิสก์:
SELECT pg_size_pretty(pg_total_relation_size('mv_warehouse_inventory_summary'));

🔴 Level 3: Refactoring โค้ด Dashboard Analytics ระดับ Production (Pre-computation Engine + pg_cron)

เปรียบเทียบโค้ดเดิมที่เป็น Standard View ที่บังคับสแกนสด 100 ล้านแถวในทุก Query กับโค้ดใหม่ที่เป็น Materialized View พร้อม Stored Procedure และการตั้งเวลาด้วย pg_cron:

-- ❌ Standard View ไม่ได้บันทึกข้อมูลลงดิสก์
-- เมื่อผู้ใช้เปิดดู จะวิ่งไปสแกน 100,000,000 แถวใหม่ทุกรอบ!
CREATE OR REPLACE VIEW v_sales_report_flawed AS
SELECT
    DATE(created_at) AS sale_date,
    region_id,
    SUM(amount)      AS total_sales,
    AVG(amount)      AS avg_order_value
FROM orders
GROUP BY DATE(created_at), region_id;

-- 🚨 เมื่อคน 200 คนยิง Query พร้อมกัน:
-- SELECT * FROM v_sales_report_flawed;
-- ระบบจะพังทลายจาก Disk I/O และ CPU 100% ทันที!

-- [Statement 1] สร้าง Materialized View สรุปข้อมูลลงดิสก์ล่วงหน้า
CREATE MATERIALIZED VIEW mv_sales_report_production AS
SELECT
    DATE(created_at) AS sale_date,
    region_id,
    COUNT(id)        AS total_orders,
    SUM(amount)      AS total_sales,
    AVG(amount)      AS avg_order_value
FROM orders
GROUP BY DATE(created_at), region_id
WITH DATA;

-- [Statement 2] สร้าง UNIQUE INDEX เพื่อรองรับ CONCURRENTLY Refresh
CREATE UNIQUE INDEX idx_mv_sales_rep_pk
ON mv_sales_report_production (sale_date, region_id);

-- [Statement 3] สร้าง Stored Procedure สำหรับห่อหุ้มตรรกะการรีเฟรชและทำ Logging
CREATE OR REPLACE PROCEDURE pr_refresh_sales_report_mv()
LANGUAGE plpgsql
AS $$
DECLARE
    v_start_time TIMESTAMPTZ := CURRENT_TIMESTAMP;
BEGIN
    RAISE NOTICE 'เริ่มกระบวนการ REFRESH MATERIALIZED VIEW CONCURRENTLY...';

    -- 🎯 สั่งรีเฟรชแบบไม่บล็อกการอ่านของผู้ใช้
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_report_production;

    RAISE NOTICE 'รีเฟรช MV สำเร็จในเวลา %', CURRENT_TIMESTAMP - v_start_time;
END $$;

-- [Statement 4] สั่งตั้งเวลาการประมวลผลฉากหลังอัตโนมัติด้วย pg_cron (รันทุกๆ 30 นาที)
SELECT cron.schedule(
    'cron_refresh_sales_mv_job',                  -- ชื่อ Job ในระบบ
    '*/30 * * * *',                               -- Cron Schedule (ทุก 30 นาที)
    'CALL pr_refresh_sales_report_mv()'           -- เรียกใช้งาน Procedure สั่งรีเฟรช
);

แบบทดสอบวัดระดับความเข้าใจ Pillar 4: Pre-computation with Materialized Views & Background Scheduling with pg_cron (Standard vs Materialized View, REFRESH CONCURRENTLY, UNIQUE INDEX, cron.schedule) และข้อสอบสะสมทบทวนครบทั้ง Day 1–7 คลิกเลือกคำตอบ ก, ข, ค, หรือ ง เพื่อดูผลลัพธ์และคำอธิบายอย่างละเอียดทันที

🎯 สรุปหมุดเชื่อมโยง & ก้าวต่อไปสู่ Day 9

บทเรียน Day 8: Pillar 4 - Pre-computation with Materialized Views & Background Scheduling Engine with pg_cron แสดงให้เห็นถึงเทคนิคการยกเลิกภาระการคำนวณสถิติหนักๆ บนตาราง OLTP โดยการเปลี่ยนมาทำ Pre-computation บันทึกผลลัพธ์ลงดิสก์ด้วย Materialized Views พร้อมใช้ REFRESH CONCURRENTLY ร่วมกับ pg_cron เพื่ออัปเดตข้อมูลฉากหลังอัตโนมัติโดยไม่กระทบต่อผู้ใช้งานหน้าบ้าน