PostgreSQL ใช้ระบบ MVCC (Multiversion Concurrency Control) ในการจัดการ Transaction ซึ่งหมายความว่าเมื่อ UPDATE หรือ DELETE ข้อมูล แถวเก่าจะไม่ถูกลบทันทีแต่จะถูกทำเครื่องหมายว่าไม่ใช้แล้ว แถวเหล่านี้เรียกว่า Dead Tuples และจะสะสมมากขึ้นเรื่อย ๆ จนทำให้ตารางใหญ่ขึ้นโดยไม่จำเป็น Query ช้าลง และดิสก์ถูกใช้อย่างสิ้นเปลือง

VACUUM และ ANALYZE เป็นกระบวนการสำคัญที่ช่วยแก้ปัญหาเหล่านี้ บทความนี้จะอธิบายหลักการทำงาน วิธีใช้งาน และการตั้งค่า Autovacuum ให้เหมาะสมกับ Production

ทำไมต้อง VACUUM

เมื่อ PostgreSQL อัพเดตหรือลบแถว แถวเดิมจะไม่ถูกลบออกจากดิสก์ทันที เพราะอาจยังมี Transaction อื่นที่ต้องเห็นข้อมูลเดิมอยู่ หลังจากไม่มี Transaction ใดต้องการแถวเก่าแล้ว แถวเหล่านั้นจะกลายเป็น Dead Tuples ที่กินพื้นที่โดยไม่มีประโยชน์

ปัญหาที่เกิดจาก Dead Tuples ที่สะสม ได้แก่ ตารางและ Index มีขนาดใหญ่ขึ้นเรื่อย ๆ (Table Bloat), Sequential Scan ต้องอ่านข้อมูลมากขึ้น, Index Scan มีประสิทธิภาพลดลง และดิสก์เต็มเร็วกว่าที่ควร

VACUUM คืออะไรและทำงานอย่างไร

VACUUM เป็นกระบวนการที่ทำความสะอาดตารางโดยเรียกคืนพื้นที่จาก Dead Tuples ให้สามารถนำกลับมาใช้ใหม่ได้ PostgreSQL มี VACUUM สองแบบหลัก

VACUUM แบบปกติ

-- VACUUM ตารางเดียว
VACUUM orders;

-- VACUUM ทั้งฐานข้อมูล
VACUUM;

-- VACUUM พร้อมแสดงรายละเอียด
VACUUM VERBOSE orders;

VACUUM แบบปกติจะทำเครื่องหมายพื้นที่ของ Dead Tuples ว่าสามารถนำกลับมาใช้ใหม่ได้ แต่จะไม่คืนพื้นที่กลับให้ระบบปฏิบัติการ หมายความว่าไฟล์ตารางจะไม่เล็กลง แต่พื้นที่ภายในจะถูกนำกลับมาใช้เมื่อมีการ INSERT ข้อมูลใหม่ ข้อดีคือสามารถรันพร้อมกับการอ่านเขียนข้อมูลได้ ไม่บล็อก Query อื่น

VACUUM FULL

-- VACUUM FULL ตารางเดียว (ล็อกตาราง!)
VACUUM FULL orders;

-- VACUUM FULL ทั้งฐานข้อมูล (ล็อกทุกตาราง!)
VACUUM FULL;

VACUUM FULL จะเขียนตารางใหม่ทั้งหมดโดยตัด Dead Tuples ออก ทำให้ไฟล์ตารางเล็กลงจริง ๆ และคืนพื้นที่กลับให้ OS แต่ข้อเสียคือจะล็อกตารางทั้งหมดระหว่างดำเนินการ ทำให้ไม่สามารถอ่านหรือเขียนข้อมูลในตารางนั้นได้ จึงไม่ควรใช้กับ Production ที่ต้องการ Uptime สูง

คุณสมบัติ	VACUUM	VACUUM FULL
คืนพื้นที่ภายในตาราง	ใช่	ใช่
คืนพื้นที่ให้ OS	ไม่	ใช่
ล็อกตาราง	ไม่ (ทำงานพร้อมกันได้)	ใช่ (Exclusive Lock)
ต้องการพื้นที่เพิ่ม	ไม่	ใช่ (เท่ากับตารางใหม่)
เหมาะกับ Production	ใช่	ไม่ (ยกเว้น Maintenance Window)

ANALYZE: อัพเดต Statistics สำหรับ Query Planner

ANALYZE เก็บข้อมูลสถิติเกี่ยวกับการกระจายตัวของข้อมูลในตาราง เช่น จำนวนแถว ค่าที่พบบ่อย การกระจายตัวของค่า เป็นต้น ข้อมูลเหล่านี้ถูกเก็บในตาราง pg_statistic และ Query Planner ใช้ในการตัดสินใจเลือก Query Plan ที่เหมาะสม

-- ANALYZE ตารางเดียว
ANALYZE orders;

-- ANALYZE คอลัมน์เฉพาะ
ANALYZE orders (status, created_at);

-- ANALYZE ทั้งฐานข้อมูล
ANALYZE;

-- ANALYZE พร้อมแสดงรายละเอียด
ANALYZE VERBOSE orders;

ถ้า Statistics ไม่อัพเดต Query Planner อาจเลือก Plan ที่ไม่เหมาะสม เช่น ใช้ Sequential Scan แทน Index Scan เพราะคิดว่าตารางมีข้อมูลน้อย ทั้ง ๆ ที่มีข้อมูลหลายล้านแถวแล้ว

VACUUM ANALYZE: ทำทั้งสองอย่างพร้อมกัน

-- ทำ VACUUM และ ANALYZE พร้อมกัน
VACUUM ANALYZE orders;

-- ทำทั้งฐานข้อมูล
VACUUM ANALYZE;

คำสั่งนี้รวมการทำความสะอาด Dead Tuples และอัพเดต Statistics ไว้ในคำสั่งเดียว เป็นวิธีที่แนะนำสำหรับการบำรุงรักษาตารางที่มีการเปลี่ยนแปลงข้อมูลบ่อย

Autovacuum: ระบบอัตโนมัติ

PostgreSQL มี Autovacuum Daemon ที่รัน VACUUM และ ANALYZE โดยอัตโนมัติตามเงื่อนไขที่กำหนด เปิดใช้งานเป็นค่าเริ่มต้นและไม่ควรปิด

พารามิเตอร์หลักของ Autovacuum

# postgresql.conf — การตั้งค่า Autovacuum

# เปิด/ปิด Autovacuum (ไม่ควรปิด!)
autovacuum = on

# จำนวน Worker ที่ทำงานพร้อมกัน
autovacuum_max_workers = 3

# ความถี่ในการตรวจสอบ (วินาที)
autovacuum_naptime = 1min

# เงื่อนไข VACUUM: จำนวน Dead Tuples ขั้นต่ำ + สัดส่วน
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
# VACUUM เมื่อ dead_tuples > threshold + scale_factor * total_tuples
# ตัวอย่าง: ตาราง 10,000 แถว → VACUUM เมื่อ dead > 50 + 0.2*10000 = 2050

# เงื่อนไข ANALYZE: จำนวนแถวที่เปลี่ยน + สัดส่วน
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1
# ANALYZE เมื่อ changed_tuples > 50 + 0.1*10000 = 1050

ปรับ Autovacuum สำหรับตารางขนาดใหญ่

ค่าเริ่มต้นของ autovacuum_vacuum_scale_factor = 0.2 หมายความว่าตารางที่มี 100 ล้านแถวจะต้องมี Dead Tuples ถึง 20 ล้านแถวก่อน Autovacuum จึงจะทำงาน ซึ่งอาจช้าเกินไป แนะนำให้ปรับเฉพาะตารางใหญ่

-- ปรับ Autovacuum เฉพาะตารางที่มีข้อมูลมาก
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,    -- 1% แทน 20%
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.005,  -- 0.5% แทน 10%
    autovacuum_analyze_threshold = 500
);

-- ดูค่าที่ตั้งไว้
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'orders';

ควบคุมความเร็ว Autovacuum

Autovacuum มีกลไก Cost-Based Delay เพื่อไม่ให้กิน I/O มากเกินไป

# Cost-Based Vacuum Delay
autovacuum_vacuum_cost_delay = 2ms   # หยุดพักทุกครั้งที่ถึง cost limit
autovacuum_vacuum_cost_limit = 200   # ค่า cost สะสมก่อนหยุดพัก

# ค่า cost ของแต่ละ operation
vacuum_cost_page_hit = 1             # อ่าน page จาก shared buffer
vacuum_cost_page_miss = 2            # อ่าน page จากดิสก์ (ไม่อยู่ใน buffer)
vacuum_cost_page_dirty = 20          # เขียน page ที่แก้ไขแล้ว

สำหรับเซิร์ฟเวอร์ที่มี SSD และ I/O เหลือเฟือ สามารถเพิ่ม autovacuum_vacuum_cost_limit หรือลด autovacuum_vacuum_cost_delay เพื่อให้ Autovacuum ทำงานเร็วขึ้น

ตรวจสอบสถานะ Dead Tuples

-- ดูจำนวน Dead Tuples ของแต่ละตาราง
SELECT schemaname, relname,
       n_live_tup AS live_tuples,
       n_dead_tup AS dead_tuples,
       CASE WHEN n_live_tup > 0
            THEN round(100.0 * n_dead_tup / n_live_tup, 1)
            ELSE 0
       END AS dead_pct,
       last_vacuum,
       last_autovacuum,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- ดู Table Bloat (ขนาดจริง vs ขนาดที่ใช้)
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
       pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

ตรวจสอบการทำงานของ Autovacuum

-- ดู Autovacuum Worker ที่กำลังทำงาน
SELECT pid, datname, relid::regclass AS table_name,
       phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed
FROM pg_stat_progress_vacuum;

-- ดูว่า Autovacuum ทำงานล่าสุดเมื่อไหร่
SELECT schemaname, relname,
       last_autovacuum,
       last_autoanalyze,
       autovacuum_count,
       autoanalyze_count
FROM pg_stat_user_tables
WHERE last_autovacuum IS NOT NULL
ORDER BY last_autovacuum DESC
LIMIT 20;

-- ดูตารางที่ Autovacuum ไม่เคยทำงาน
SELECT schemaname, relname, n_dead_tup,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE last_autovacuum IS NULL
  AND n_dead_tup > 0
ORDER BY n_dead_tup DESC;

Transaction ID Wraparound: ปัญหาที่ต้องระวัง

PostgreSQL ใช้ Transaction ID (XID) ขนาด 32 บิต ซึ่งรองรับได้ประมาณ 4 พันล้าน Transaction เมื่อ XID หมุนครบรอบ (Wraparound) ระบบจะไม่สามารถสร้าง Transaction ใหม่ได้ ทำให้ฐานข้อมูลหยุดทำงาน

VACUUM มีหน้าที่ Freeze แถวเก่าที่ไม่ต้องใช้ XID อีกต่อไป เพื่อป้องกัน Wraparound ถ้า Autovacuum ไม่สามารถทำงานได้ (เช่น ถูกยกเลิกซ้ำ ๆ หรือตารางมีขนาดใหญ่มาก) PostgreSQL จะแจ้งเตือนในล็อก

-- ตรวจสอบ XID Age ของแต่ละฐานข้อมูล
SELECT datname,
       age(datfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age') AS freeze_max_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- ตรวจสอบตารางที่มี XID Age สูง
SELECT schemaname, relname,
       age(relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relkind = 'r'
  AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

-- พารามิเตอร์ที่เกี่ยวข้อง
-- autovacuum_freeze_max_age = 200000000  (VACUUM บังคับเมื่อ XID age ถึง)
-- vacuum_freeze_min_age = 50000000       (Freeze แถวที่เก่ากว่า)
-- vacuum_freeze_table_age = 150000000    (Scan ทั้งตารางเมื่อ age ถึง)

ทางเลือกแทน VACUUM FULL

เนื่องจาก VACUUM FULL ล็อกตาราง จึงไม่เหมาะกับ Production มีทางเลือกอื่นที่คืนพื้นที่ได้โดยมีผลกระทบน้อยกว่า

• pg_repack — Extension ที่ Reorganize ตารางโดยไม่ต้องล็อก ทำงานคล้าย VACUUM FULL แต่อนุญาตให้อ่านเขียนข้อมูลได้ระหว่างดำเนินการ
• CLUSTER — จัดเรียงข้อมูลในตารางตาม Index ที่กำหนด แต่ล็อกตารางเช่นเดียวกับ VACUUM FULL
• CREATE TABLE AS + RENAME — สร้างตารางใหม่จากข้อมูลเก่า แล้วสลับชื่อ ใช้ได้เมื่อยอมรับ Downtime สั้น ๆ

# ติดตั้งและใช้ pg_repack
sudo apt install postgresql-16-repack

-- โหลด Extension
CREATE EXTENSION pg_repack;

-- Repack ตาราง (ไม่ล็อก!)
pg_repack -d mydb -t orders

-- Repack ทั้งฐานข้อมูล
pg_repack -d mydb

การตั้งค่า Autovacuum สำหรับ Production

# postgresql.conf — Autovacuum สำหรับ Production

autovacuum = on
autovacuum_max_workers = 5           # เพิ่มจาก 3 สำหรับฐานข้อมูลขนาดใหญ่
autovacuum_naptime = 30s             # ตรวจสอบบ่อยขึ้น

# เงื่อนไข VACUUM
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.05  # 5% แทน 20% (เหมาะกับตารางขนาดกลาง-ใหญ่)

# เงื่อนไข ANALYZE
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.02 # 2% แทน 10%

# Cost-Based Delay (ปรับตาม I/O ของเซิร์ฟเวอร์)
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 400   # เพิ่มจาก 200 สำหรับ SSD

# Freeze Parameters
autovacuum_freeze_max_age = 200000000
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 150000000

# Logging
log_autovacuum_min_duration = 0      # บันทึกทุกครั้งที่ Autovacuum ทำงาน

สรุป

VACUUM และ ANALYZE เป็นกระบวนการบำรุงรักษาที่ขาดไม่ได้ใน PostgreSQL สิ่งสำคัญคืออย่าปิด Autovacuum เด็ดขาด ให้ตรวจสอบ Dead Tuples และ XID Age เป็นประจำ ปรับพารามิเตอร์ Autovacuum ให้เหมาะกับขนาดตาราง และใช้ pg_repack แทน VACUUM FULL เมื่อต้องการคืนพื้นที่ดิสก์ใน Production การดูแล VACUUM อย่างเหมาะสมจะช่วยให้ฐานข้อมูลทำงานได้อย่างเสถียรและมีประสิทธิภาพในระยะยาว

แนะนำบริการ DE

การจัดการ VACUUM ใน PostgreSQL อย่างมีประสิทธิภาพต้องการเซิร์ฟเวอร์ที่มี I/O สูงและ Root Access เพื่อปรับแต่ง Autovacuum Parameters Cloud VPS ของ DE มาพร้อม SSD NVMe ที่รองรับ IOPS สูง เหมาะสำหรับการรัน PostgreSQL ที่ต้องการประสิทธิภาพดิสก์ในระดับ Production

สำหรับผู้ที่โฮสต์เว็บแอปพลิเคชันที่ต้องการฐานข้อมูลที่ดูแลง่าย Cloud Hosting ของ DE มีระบบจัดการพร้อมใช้งานโดยไม่ต้องตั้งค่าเซิร์ฟเวอร์เอง เหมาะกับโปรเจกต์ที่ต้องการความสะดวกสบาย