Architecture Mastery • Phase 1

RDBMS Internals Review & Database Speed Booster

Bare-Metal Physics, Buffer Pool, Index Engineering, EXPLAIN Mastery & 50-Item Architectural Assessment Bank

1. Hardware Physics & The Latency Hierarchy

ในการออกแบบระบบ High-Frequency และ Financial Ledger การเข้าใจระดับความเร็วของหน่วยความจำทางกายภาพ (Bare-Metal Latency) คือกุญแจสำคัญที่ทำให้เราเห็นว่าทำไม Disk-First Architecture จึงเป็น Bottleneck และทำไม In-Memory Computing ถึงชนะอย่างเด็ดขาด

Hardware Layer Physical Latency Human Scaled (1 Cycle = 1 Sec) Throughput / Characteristics
L1 CPU Cache ~0.5 - 1 ns 1 วินาที Cache Line 64 Bytes ต่อ Core
L2 CPU Cache ~3 - 4 ns 4 วินาที Private per core / Small shared cluster
L3 CPU Cache ~10 - 20 ns 15 วินาที Shared across cores (Unified)
Main RAM (DDR4/DDR5) ~60 - 100 ns 1.5 - 2 นาที Bandwidth ~50-100 GB/s ต่อ Channel
NVMe SSD (PCIe Gen4) ~10 - 50 µs (10,000 ns) 4 - 5 เดือน Random 4K Read/Write Bottleneck
Traditional SATA SSD ~100 - 200 µs 1.5 ปี AHCI Controller Bottleneck
HDD Mechanical Platter ~5 - 10 ms (10,000,000 ns) 300+ ปี Rotational delay + Actuator Seek arm
Core Architectural Takeaway: ความต่างระหว่าง RAM Access (80 ns) กับ NVMe Random Read (30,000 ns) คือเกือบ 400 เท่า! นั่นคือเหตุผลว่าทำไม RDBMS จึงต้องทำทุกวิถีทางเพื่อหลีกเลี่ยง Random Disk I/O และนำมาสู่การออกแบบ shared_buffers และ Write-Ahead Logging (WAL)

2. Disk-First Architecture vs RAM Buffer Pool (shared_buffers)

PostgreSQL และ RDBMS มาตรฐานถูกออกแบบในยุคที่ข้อมูลมีขนาดใหญ่เกินกว่าจะเก็บไว้ใน RAM ทั้งหมด จึงใช้สถาปัตยกรรม Disk-First: ดิสก์คือ Single Source of Truth ส่วน RAM คือ Cache ชั่วคราว

+-------------------------------------------------------------------------+ | POSTGRESQL INSTANCE | | | | +------------------------+ +----------------------------+ | | | Backend Process (Work) | | Background Writer / CKPT | | | | (Sort, Hash, Joins) | +--------------+-------------+ | | +-----------+------------+ | | | | | Flushes | | v v Dirty Pages | | +-------------------------------------------------------------------+ | | | SHARED_BUFFERS (Buffer Pool) | | | | [Page 1: Clean] [Page 2: DIRTY] [Page 3: Clean] [Page 4] | | | +-----------------------------------+-------------------------------+ | +--------------------------------------|----------------------------------+ | Page Fault / OS Read v +-------------------------------------------------------------------------+ | OS KERNEL PAGE CACHE | +-------------------------------------------------------------------------+ | Disk Controller I/O v +-------------------------------------------------------------------------+ | PHYSICAL STORAGE (NVMe / SSD / WAL Disk) | | +-------------------------+ +--------------------------+ | | | WAL Log (Sequential Write| | Data Files (*.tbl / 8KB) | | | +-------------------------+ +--------------------------+ | +-------------------------------------------------------------------------+

วงจรชีวิตของ 8KB Page ใน Buffer Pool

  1. Page Fetch: เมื่อ Query ต้องการอ่าน Tuple ใน Block ที่ X ระบบจะเช็ค Buffer Pool Lookup Table ใน shared_buffers ก่อน หากพบ (Hit) จะนำ Pointer ไปประมวลผลทันทีใน RAM
  2. Buffer Miss (Read from Disk): หากไม่พบ จะเกิด Buffer Miss โดย PostgreSQL จะเรียก Kernel ผ่าน System Call (เช่น read()) ดึง 8KB Block จาก OS Page Cache หรือ NVMe เข้ามาไว้ใน Buffer Pool Frame ที่ว่าง
  3. Page Dirtying & WAL-First: เมื่อเกิดการ UPDATE หรือ INSERT ข้อมูลจะถูกเขียนลง WAL (Write-Ahead Log) บนดิสก์แบบ Sequential Write ก่อนเสมอ (fsync) จากนั้นข้อมูลใน shared_buffers จะถูกแก้ใน RAM และถูกสลากสถานะเป็น Dirty Page
  4. Checkpoint & Background Writer: Dirty Pages จะยัง ไม่ถูกเขียนลง Data Block บนดิสก์ทันที แต่จะรอให้ Checkpointer Process หรือ Background Writer ทยอย Flush กลับดิสก์เป็นระยะ เพื่อลด Random Write I/O

3. Query Optimizer, AST, Execution Plan & EXPLAIN Analysis

การเข้าใจเบื้องหลังการรันคำสั่ง SQL ช่วยให้วิศวกรหลุดพ้นจากการเดา และวิเคราะห์คอขวดได้อย่างแท้จริง

SQL Query Text | v [ 1. Parser ] --------------> AST (Abstract Syntax Tree) | v [ 2. Rewriter / Analyzer ] --> Query Tree (View expansions, rule rewrites) | v [ 3. Cost-Based Optimizer ] -> Generates Alternate Path Trees + Cost Estimation | (Uses pg_statistic / ANALYZE statistics) v [ 4. Plan Selection ] ------> Best Execution Plan (Lowest Arbitrary Disk/CPU Cost) | v [ 5. Executor ] ------------> Scans (Seq, Index, Bitmap) -> Hash/Sort -> Return Rows

การอ่าน EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN (ANALYZE, BUFFERS, COSTS, VERBOSE)
SELECT account_id, balance 
FROM accounts 
WHERE status = 'ACTIVE' AND balance > 1000000;

หัวใจสำคัญของ Buffer Metrics:

ข้อควรระวัง Cost Metric: ตัวเลข Cost (เช่น cost=0.42..8.45) ใน EXPLAIN เป็น ค่าสมมติ (Arbitrary Units) ที่คำนวณจากค่าคอนฟิก seq_page_cost=1.0, random_page_cost=4.0, cpu_tuple_cost=0.01 ไม่ใช่หน่วยมิลลิวินาที แต่ Execution Time ในบล็อก ANALYZE คือเวลาจริงที่ CPU รัน

4. B+ Tree Mechanics & Index Engineering

B+ Tree คือกระดูกสันหลังของระบบ RDBMS สมัยใหม่ ความแตกต่างหลักกับ B-Tree แบบดั้งเดิมคือ B+ Tree จะเก็บ Data Record/TID (Tuple Identifier) ไว้เฉพาะที่ Leaf Nodes เท่านั้น ส่วน Internal Nodes จะเก็บเฉพาะ Search Key สำหรับการ Routing

[ Root Node ] | 50 | 100 | / | v v v [ Internal 20|35 ] [ 70|85 ] [ 120|150 ] / | v v v +-------+ +-------+ +-------+ Leaf: |10 |15 |->|20 |30 |->|35 |45 |-> ... (Doubly Linked List for Range Scans) +-------+ +-------+ +-------+ [TID 1] [TID 3] [TID 5] (Disk Pointer to Table Block & Offset)

กฎทอง Equality -> Range บน Composite Index

สมมติเราสร้าง Composite Index: CREATE INDEX idx_trade ON trades (currency_pair, status, trade_time);

Covering Index & Index-Only Scan

หากสร้าง Index แบบ CREATE INDEX idx_acc ON accounts (status) INCLUDE (balance, account_number);

PostgreSQL จะสามารถทำ Index-Only Scan ได้โดยไม่ต้องวิ่งกลับไปเปิด Heap Table Block (ลด Heap Lookups มหาศาล) ตราบใดที่ Page นั้นถูกมาร์กไว้ใน Visibility Map

5. Database Anti-Patterns Elimination

1. Non-Sargable Predicates

Sargable ย่อมาจาก Search Argument Able หากเขียน Query ที่ครอบ Function ลงบนคอลัมน์ Index จะไม่ทำงาน:

-- ANTI-PATTERN: Full Table Scan แน่นอน เพราะ B+ Tree เก็บค่า raw 'created_at'
SELECT * FROM transactions WHERE DATE(created_at) = '2026-05-01';

-- REFACTORED (Sargable): ใช้ B+ Tree Range Scan ได้อย่างเต็มประสิทธิภาพ
SELECT * FROM transactions 
WHERE created_at >= '2026-05-01 00:00:00' 
  AND created_at < '2026-05-02 00:00:00';

2. The N+1 Query Problem (ORM Disaster)

เกิดขึ้นเมื่อ ORM ทำการ Loop รัน Child Query 1 รอบต่อ Parent Row 1 แถว ส่งผลให้เกิด Network Round-Trip และ Context Switching มหาศาล

-- Query 1: ดึงบัญชี 100 แถว
SELECT * FROM accounts LIMIT 100;
-- Query 2..101: ยิงทีละแถวเพื่อดึงรายการย่อย (100 network round-trips)
SELECT * FROM ledger_entries WHERE account_id = ?;

-- REFACTORED: รวมเป็น Single Set-Based Query ผ่าน JOIN หรือ IN-clause batching
SELECT a.*, l.* 
FROM accounts a 
LEFT JOIN ledger_entries l ON l.account_id = a.id
WHERE a.id IN (...);

3. Write Amplification จาก Catch-All Index

การสร้าง Index เผื่อไว้ทุกคอลัมน์ ไม่เพียงแต่เปลือง Disk/RAM แต่ทำให้ทุกๆ คำสั่ง INSERT, UPDATE, DELETE ต้องไล่ล็อกและอัปเดต B+ Tree ทุกต้น เกิดปัญหา HOT (Heap-Only Tuples) Update Failure และ Page Splits อย่างรุนแรง

6. Architectural Assessment Bank (50 Deep Verification Items)

ชุดข้อสอบสถาปัตยกรรมฐานข้อมูลระดับลึก 50 ข้อ ออกแบบมาเพื่อทดสอบและรีเช็กภาพจำเชิงฮาร์ดแวร์ การทำงานของ Optimizer, Buffer Pool, MVCC, Locking และ Indexing พร้อมระบบวิเคราะห์ผลลัพธ์ทันที

Architecture Assessment Score: 0 / 50