PostgreSQL: ตรวจสอบ Index Bloat และการบำรุงรักษา GiST Index

การดูแลรักษาและจูน GiST Index บน Production มีจุดเฉพาะตัวต่างจาก B-Tree เนื่องจากโครงสร้างแบบ Bounding Box มีความอ่อนไหวต่อการแตกตัวของโหนด (Node Splits) และการเกิดกล่องทับซ้อน (Overlapping Bounding Boxes) สูงกว่าปกติ

1. วิธีตรวจสอบประสิทธิภาพการใช้งาน Index

ก่อนจะปรับแต่ง ต้องยืนยันก่อนว่า Query Engine ดึง GiST ไปใช้งานจริงและมีสถิติการ Hit เป็นอย่างไร

ตรวจสอบอัตราการเรียกใช้งานและ Cache Hit Ratio

SELECT 
    schemaname,
    relname AS tablename,
    indexrelname AS indexname,
    idx_scan,                      -- จำนวนครั้งที่ถูกดึงไปใช้
    idx_tup_read,                  -- จำนวน entries ที่อ่านจาก index
    idx_tup_fetch,                 -- จำนวนแถวที่ดึงขึ้นมาจริง
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE indexrelname = 'idx_bookings_period';

วิเคราะห์ Buffer I/O ร่วมกับ EXPLAIN รันคำสั่งโดยเปิด BUFFERS เพื่อดูว่า Index ทำงานผ่าน RAM (Shared Hit) หรือต้องไปดึงจากดิสก์ (Shared Read)

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM bookings
WHERE period && daterange('2026-06-01', '2026-06-15', '[]');

จุดสังเกต
หากพบสัดส่วน shared read สูงต่อเนื่อง และเวลา actual time ช้าลง แปลว่า Index ใหญ่เกินขนาดหน่วยความจำ หรือเกิดปัญหา Bloat ทำให้ต้องอ่านหลาย Blocks เกินความจำเป็น

2. วิธีตรวจสอบ Index Bloat และการเสื่อมสภาพของโครงสร้าง

GiST ไม่ได้เสื่อมสภาพแค่เรื่องพื้นที่ว่าง (Unused Space) เท่านั้น แต่มีปัญหาเฉพาะตัวคือ “Overlapping Degradation” เมื่อมีการ INSERT/UPDATE บ่อยครั้ง Bounding Box ของโหนดแม่จะขยายจนทับซ้อนกันไปหมด ทำให้เวลาค้นหา Engine ต้องวิ่งลงหลายกิ่งพร้อมกัน (Branch Pruning ล้มเหลว)

ใช้ Extension มาตรฐานอย่าง pageinspect เพื่อเจาะดูลึกถึงสถิติภายในของ GiST

CREATE EXTENSION IF NOT EXISTS pageinspect;

-- ตรวจสอบสถิติระดับภาพรวมของ GiST
SELECT * FROM gist_page_opaque_info(get_raw_page('idx_bookings_period', 0));

สคริปต์ประเมินขนาดพื้นที่ว่าง (Bloat Estimation Query)

SELECT
    current_database(),
    nspname AS schemaname,
    tblname,
    idxname,
    bs * (relpages)::bigint AS actual_size,
    pg_size_pretty(bs * (relpages)::bigint) AS actual_size_pretty,
    reltuples
FROM (
    SELECT 
        c.relname AS tblname,
        i.relname AS idxname,
        c.relnamespace,
        i.relpages,
        i.reltuples,
        current_setting('block_size')::numeric AS bs
    FROM pg_index x
    JOIN pg_class c ON c.oid = x.indrelid
    JOIN pg_class i ON i.oid = x.indexrelid
    JOIN pg_am am ON i.relam = am.oid
    WHERE am.amname = 'gist'
) sub
JOIN pg_namespace n ON n.oid = sub.relnamespace
ORDER BY actual_size DESC;

(หากตารางผ่านการ DELETE หรือ UPDATE ปริมาณมากแล้วพบว่าขนาด Index ใหญ่กว่าขนาดของคอลัมน์ข้อมูลจริงหลายเท่า แสดงว่าเกิด Index Bloat)

3. แนวทางการบำรุงรักษา (Maintenance Strategies)

3.1 การ Rebuild Index โดยไม่บล็อกระบบ (Zero-Downtime)

คำสั่ง VACUUM ปกติจะทำหน้าที่เพียงเก็บกวาด Dead Tuples แต่ ไม่สามารถคืนพื้นที่บนดิสก์และไม่สามารถจัดระเบียบ Bounding Box ใหม่ได้ การแก้ปัญหา Bloat บน GiST ที่ได้ผลที่สุดคือการ Reindex

-- PostgreSQL 12 ขึ้นไป สามารถ Reindex แบบไม่ล็อกการเขียน (Online Rebuild)
REINDEX INDEX CONCURRENTLY idx_bookings_period;

คำแนะนำ: ควรตั้งเป็น Scheduled Task (เช่น รันช่วง Off-peak สัปดาห์ละหรือเดือนละครั้ง) สำหรับตารางที่มี Write Volume สูง

3.2 การปรับแต่ง Fillfactor เพื่อลด Page Splits

โดยปกติ GiST มีค่า fillfactor เริ่มต้นที่ 90 หากตารางของคุณมีการบันทึกข้อมูลและอัปเดตตลอดเวลา การลด Fillfactor ลงจะช่วยเหลือพื้นที่ว่างในแต่ละ Page เพื่อรองรับการขยายของ Bounding Box ทำให้เกิดการแตกหน้าน้อยลง

-- ปรับลด fillfactor ตอนสร้าง หรือใช้ ALTER แล้ว REINDEX
ALTER INDEX idx_bookings_period SET (fillfactor = 80);
REINDEX INDEX CONCURRENTLY idx_bookings_period;

3.3 การจัดเรียงข้อมูลล่วงหน้าก่อนสร้าง Index (Clustering / Ordered Bulk Insert)

อัลกอริทึมการสร้าง GiST จะทำงานได้สมบูรณ์และมี Bounding Box ที่กระชับที่สุด หากข้อมูลถูกใส่เข้ามาตามลำดับทางกายภาพ

หากต้องการ Build Index ขนาดใหญ่ ควรสร้าง Index หลังจากเรียงข้อมูลแล้ว หรือสร้างผ่านตารางที่มีการจัดระเบียบตามช่วงเวลา
การใช้ buffering = on ตอนสร้าง GiST Index สำหรับข้อมูลปริมาณมหาศาล (Bulk Load) จะช่วยเร่งความเร็วในการสร้าง Index ได้อย่างมาก
```
CREATE INDEX idx_bookings_period ON bookings 
USING GIST (period) WITH (buffering = on);
```

ข้อควรระวังในการ Monitoring

หลีกเลี่ยงการดูเฉพาะ Index Size
ดัชนี GiST ที่มีขนาดเล็กไม่ได้แปลว่ามีประสิทธิภาพเสมอไป หาก Bounding Boxes ทับซ้อนกันมาก คิวรีจะยังช้าอยู่ ให้ใช้ EXPLAIN (ANALYZE, BUFFERS) เป็นตัววัดความเร็วที่แท้จริง
Disk Space สำรองขณะรัน REINDEX CONCURRENTLY
ในขณะที่กระบวนการกำลังทำงาน ระบบจะสร้าง Index ชุดใหม่ควบคู่ไปกับชุดเก่า ต้องเตรียมพื้นที่ดิสก์ว่างไว้อย่างน้อยเท่ากับขนาด Index ปัจจุบัน (บวกเผื่ออีกประมาณ 20-30%)

อ่านเพิ่มเติม

Tags: .web, backend, cloud, data, SQL, ซอฟต์แวร์, ระบบ, สาระความรู้