Day 2: Storage Engine & Buffer Pool Mechanics (กลไกหน่วยความจำ Shared Buffers, Disk I/O Bottleneck และการประมวลผลแบบ Buffer Pool Proximity)

เมื่อเราอยากประมวลผลในฐานข้อมูล คำถามถัดไปคือฐานข้อมูลเก็บและดึงข้อมูลอย่างไร — วันนี้เราดูสองชั้นที่ทำงานคู่กัน: ความจำบน RAM กับไฟล์บนดิสก์

โดยรวมวันนี้คือเรื่องที่ RAM กับดิสก์ส่งไม้ต่อกันอย่างไร เวลาฐานข้อมูลจะค้นหาหรือแก้ไขข้อมูลขนาด 8KB — ถ้าบล็อกนั้นอยู่บน RAM อยู่แล้ว งานจะจบเร็วมาก ถ้าไม่มี ต้องเดินไปเปิดไฟล์บนดิสก์ก่อน

จาก Day 1 ที่บอกว่าต้องย้ายงานเข้าฐานข้อมูล วันนี้บอกต่อว่าให้เขียน Query ให้เจอข้อมูลบน RAM (Buffer Hit) แทนที่จะบังคับให้ฐานข้อมูลเดินไปอ่านดิสก์ (Disk I/O) ที่ช้ากว่าเป็นพันเท่า

การสร้าง In-Database Automation Engine บน PostgreSQL ให้มีประสิทธิภาพระดับ Nanoseconds เราไม่สามารถมองฐานข้อมูลเป็นเพียงตารางลอยๆ ได้ แต่ต้องเข้าใจกลไกภายในของ Storage Engine โดยเฉพาะ Shared Buffer Pool (shared_buffers) ซึ่งเป็นพื้นที่ RAM หลักที่ PostgreSQL ใช้พักบล็อกข้อมูล (Data Pages ขนาด 8KB)

⚡ บันไดความเร็วของคอมพิวเตอร์ (Latency Numbers Every Architect Must Know)

ทำไมการรันบน RAM (shared_buffers) ถึงเร็วกว่าการปล่อยให้หลุดไปอ่าน Disk? ดูความแตกต่างทางฟิสิกส์ของหน่วยเวลา:

ถอดรหัสคำศัพท์เชิงลึก (Storage Engine Vocab & Mental Models)

ในการเขียน PL/pgSQL ร่วมกับ Storage Engine การฮาร์ดโค้ดชนิดตัวแปร (เช่น v_name VARCHAR(50);) มีความเสี่ยงสูงหากอนาคตตารางเปลี่ยน Schema การใช้ Dynamic Data Anchoring (%TYPE และ %ROWTYPE) ช่วยผูกชนิดข้อมูลกับตารางโดยตรงในระดับ RAM ป้องกัน Data Alignment Mismatch ได้อย่างสมบูรณ์

DO $$
DECLARE
    -- [Statement 1: %TYPE] ผูกชนิดข้อมูลของตัวแปรตามคอลัมน์เฉพาะในตาราง
    -- ไวยากรณ์: ชื่อตาราง.ชื่อคอลัมน์%TYPE;
    v_emp_id   employees.employee_id%TYPE := 101;
    v_salary   employees.salary%TYPE;

    -- [Statement 2: %ROWTYPE] สร้างตัวแปรเรกคอร์ดรับโครงสร้างทั้งแถวของตาราง
    -- ไวยากรณ์: ชื่อตาราง%ROWTYPE;
    v_emp_rec  employees%ROWTYPE;

BEGIN
    -- [Statement 3: SELECT ... INTO Record] ดึงข้อมูลทั้งแถวจาก Buffer มาพักใน RAM Record
    SELECT * INTO v_emp_rec
    FROM employees
    WHERE employee_id = v_emp_id;

    -- [Statement 4: Accessing Record Fields] อ้างอิงฟิลด์ผ่านเครื่องหมายจุด (.field_name)
    RAISE NOTICE 'พนักงาน: % % | เงินเดือนปัจจุบัน: %',
                 v_emp_rec.first_name, v_emp_rec.last_name, v_emp_rec.salary;
END $$;

🏋️️‍♂️ แบบฝึกหัดไต่ระดับการเขียนจริง (จากระดับเริ่มต้น ➔ แก้ปัญหา Disk I/O Bottleneck)

DO $$
DECLARE
    -- [1] ประกาศตัวแปรโดยอ้างอิงชนิดข้อมูลจากตาราง accounts โดยตรง
    v_acc_id  accounts.account_id%TYPE := 5001; -- ถ้านานไป account_id เปลี่ยนเป็น BIGINT โค้ดนี้จะไม่พัง
    v_balance accounts.balance%TYPE;            -- อ้างอิงชนิดข้อมูล balance จากตาราง accounts
BEGIN
    -- [2] ดึงค่า balance มาใส่ตัวแปร v_balance
    SELECT balance INTO v_balance
    FROM accounts
    WHERE account_id = v_acc_id;

    -- [3] แสดงผลลัพธ์ออก Log
    RAISE NOTICE 'รหัสบัญชี: % | ยอดเงินคงเหลือ: % บาท', v_acc_id, v_balance;
END $$;

-- 📤 ผลลัพธ์ใน Log:
-- NOTICE: รหัสบัญชี: 5001 | ยอดเงินคงเหลือ: 45000.00 บาท

DO $$
DECLARE
    v_target_id accounts.account_id%TYPE := 5001;
    v_acc_row   accounts%ROWTYPE; -- [1] สร้างตัวแปรเรกคอร์ดรองรับทุกคอลัมน์ของตาราง accounts
BEGIN
    -- [2] ดึงข้อมูลทั้งแถว (Data Page ใน shared_buffers) มายัดใส่ v_acc_row
    SELECT * INTO v_acc_row
    FROM accounts
    WHERE account_id = v_target_id;

    -- [3] แสดงผลข้อมูลจากฟิลด์ต่างๆ ของ %ROWTYPE โดยใช้เครื่องหมายจุด (.)
    IF v_acc_row.status = 'ACTIVE' THEN
        RAISE NOTICE 'บัญชี % ของคุณ % % มีสถานะใช้งานปกติ ยอดเงิน: % บาท',
                     v_acc_row.account_id, v_acc_row.first_name, v_acc_row.last_name, v_acc_row.balance;
    ELSE
        RAISE NOTICE 'บัญชี % มีสถานะ: %', v_acc_row.account_id, v_acc_row.status;
    END IF;
END $$;

🔴 Level 3: Refactoring โค้ดแก้ปัญหา Disk I/O Bottleneck (Buffer Pool Proximity)

เปรียบเทียบโค้ดเดิมที่กวาดอ่านข้อมูลข้าม Network จนล้าง Cache ใน RAM ทิ้ง กับโค้ดใหม่ที่เขียนด้วย PL/pgSQL ประมวลผลติดชิด shared_buffers:

# โค้ดฝั่ง Application Server
# วนลูปดึงข้อมูลทีละ 1,000 แถวมาคำนวณดอกเบี้ย
# เกิด Buffer Read จาก Disk ซ้ำซ้อน และ Cache Thrashing!
accounts = db.execute(
    "SELECT account_id, balance FROM accounts WHERE status = 'ACTIVE'"
)
for acc in accounts:
    new_balance = acc.balance + (acc.balance * 0.05) # คำนวณดอกเบี้ย 5%
    db.execute(
        "UPDATE accounts SET balance = %s WHERE account_id = %s",
        (new_balance, acc.account_id)
    )

DO $$
DECLARE
    -- [บรรทัดที่ 1] ประกาศตัวแปรอัตราดอกเบี้ย โดยใช้อ้างอิง %TYPE จากคอลัมน์ balance
    v_interest_rate accounts.balance%TYPE := 0.05;

    -- [บรรทัดที่ 2] ประกาศตัวแปรเก็บจำนวนแถวที่ประมวลผลสำเร็จ
    v_processed_cnt INT;
BEGIN
    -- [บรรทัดที่ 3] เริ่มต้น CTE เพื่อประมวลผลคำนวณและอัปเดตข้อมูลบน shared_buffers ใน RAM โดยตรง
    WITH updated_accounts AS (

        -- [บรรทัดที่ 4-7] คำสั่ง UPDATE ประมวลผลแบบ Set-based ติดชิด Buffer Pool
        -- ทำการอ่านและแก้ไข Data Pages ขนาด 8KB ที่อยู่ใน shared_buffers รวดเดียว ไม่ต้องดึงออกนอก DB Engine
        UPDATE accounts
        SET balance = balance + (balance * v_interest_rate),
            last_interest_applied = CURRENT_TIMESTAMP
        WHERE status = 'ACTIVE'

        -- [บรรทัดที่ 8] พ่น account_id ของแถวที่อัปเดตสำเร็จบน RAM ส่งต่อไปเก็บใน CTE updated_accounts
        RETURNING account_id
    )
    -- [บรรทัดที่ 9] สั่งนับจำนวนรายการที่ถูกประมวลผลทั้งหมด ยัดใส่ v_processed_cnt
    SELECT COUNT(*) INTO v_processed_cnt FROM updated_accounts;

    -- [บรรทัดที่ 10] แสดงผลการประมวลผลบน RAM
    RAISE NOTICE 'ประมวลผลคิดดอกเบี้ยสำเร็จ % รายการ (Buffer Cache Hit Rate > 99%%)', v_processed_cnt;
END $$;

แบบทดสอบวัดระดับความเข้าใจสถาปัตยกรรม Storage Engine, Shared Buffers, Data Pages 8KB, Dynamic Anchoring (%TYPE / %ROWTYPE) และข้อสอบทบทวน Day 1 คลิกเลือกคำตอบ ก, ข, ค, หรือ ง เพื่อดูผลลัพธ์และคำอธิบายอย่างละเอียดทันที

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

บทเรียน Day 2: Storage Engine & Buffer Pool Mechanics ชี้ให้เห็นถึงความสำคัญของการประมวลผลติดชิดหน่วยความจำ Shared Buffer Pool (shared_buffers) โดยใช้หลักการ Buffer Pool Proximity ร่วมกับการประกาศตัวแปรที่ปลอดภัยแบบ %TYPE และ %ROWTYPE ซึ่งช่วยลด Disk I/O Bottleneck และรักษาค่า Cache Hit Ratio ให้สูงกว่า 99%