Autovacuum: แก้ตารางบวมใน PostgreSQL ด้วย VACUUM ที่ถูกวิธี

Autovacuum คือกระบวนการเบื้องหลังของ PostgreSQL ที่คอยเก็บกวาดแถวข้อมูลที่ถูกลบหรือถูกแก้ไขแล้ว ทุกครั้งที่สั่ง UPDATE หรือ DELETE ระบบไม่ได้เขียนทับแถวเดิม แต่สร้างเวอร์ชันใหม่และทิ้งเวอร์ชันเก่าไว้เป็น dead tuple ตามหลัก MVCC ถ้าไม่มีใครเก็บกวาด ตารางจะบวม (bloat) ขนาดไฟล์โตขึ้นเรื่อย ๆ ทั้งที่จำนวนแถวไม่เพิ่ม และ Query ช้าลงเพราะต้องอ่านหน้าข้อมูลมากขึ้น

คู่มือนี้พาคุณหาตารางที่บวม หาสาเหตุที่การเก็บกวาดทำงานไม่ทัน รัน VACUUM ด้วยมืออย่างปลอดภัย ตั้งค่ารายตารางให้ตารางที่เขียนหนัก และรู้ว่าเมื่อใดควรใช้ VACUUM FULL ซึ่งล็อกตารางทั้งตาราง ค่ากลางของทั้งเซิร์ฟเวอร์ดูได้ใน คู่มือปรับแต่ง PostgreSQL เบื้องต้น

สิ่งที่ต้องเตรียม

• PostgreSQL 14 ขึ้นไปบน Linux และเข้า psql ด้วยผู้ใช้ postgres หรือเจ้าของตารางได้
• สิทธิ์สร้าง extension ในฐานข้อมูล สำหรับใช้ pgstattuple วัดการบวม
• พื้นที่ดิสก์ว่างอย่างน้อยเท่าขนาดตารางรวม Index ถ้าจะใช้ VACUUM FULL
• ช่วงเวลาปิดปรับปรุงที่ตกลงกับผู้ใช้แล้ว สำหรับงานที่ต้องล็อกตาราง
• ข้อมูลสำรองล่าสุด ดู คู่มือ pg_dump และ pg_restore

ขั้นตอนที่ 1: หาตารางที่มีแถวตายมากที่สุด

SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

ตารางที่ dead_pct สูงกว่า 20% และเวลาเก็บกวาดล่าสุดเก่ามากคือผู้ต้องสงสัย สาเหตุที่พบบ่อยคือสูตรเริ่มทำงานของ Autovacuum ซึ่งรอให้แถวตายถึง autovacuum_vacuum_threshold (ค่าเริ่มต้น 50) บวก autovacuum_vacuum_scale_factor (ค่าเริ่มต้น 0.2) คูณจำนวนแถว ตารางสิบล้านแถวจึงต้องมีแถวตายราวสองล้านแถวก่อนจะถูกเก็บกวาดหนึ่งครั้ง

ขั้นตอนที่ 2: วัดขนาดและการบวมจริง

SELECT pg_size_pretty(pg_total_relation_size('public.orders')) AS total,
       pg_size_pretty(pg_relation_size('public.orders')) AS table_only;

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT table_len, tuple_percent, dead_tuple_percent, free_percent
FROM pgstattuple('public.orders');

tuple_percent คือสัดส่วนข้อมูลจริง dead_tuple_percent คือแถวตายที่ยังไม่ถูกเก็บกวาด ส่วน free_percent คือที่ว่างภายในไฟล์ที่นำกลับมาใช้ได้ ถ้า free_percent สูงมาก เช่นเกิน 50% หลังลบข้อมูลจำนวนมาก แปลว่าไฟล์ใหญ่เกินข้อมูลจริงหลายเท่า pgstattuple อ่านทั้งตาราง จึงควรรันนอกเวลางาน หรือใช้ pgstattuple_approx ซึ่งเร็วกว่า

ขั้นตอนที่ 3: หาสิ่งที่ขวางการเก็บกวาด

VACUUM ลบได้เฉพาะแถวที่ไม่มี transaction ใดมองเห็นแล้ว transaction ที่เปิดค้างไว้นาน โดยเฉพาะสถานะ idle in transaction จะทำให้แถวตายทั้งฐานข้อมูลลบไม่ได้ ตรวจด้วยคำสั่งนี้

SELECT pid, usename, state, now() - xact_start AS duration, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

SELECT slot_name, active, xmin, catalog_xmin FROM pg_replication_slots;

ถ้าเจอ transaction ที่ค้างเป็นชั่วโมง ให้ตรวจกับเจ้าของแอปก่อนปิดด้วย SELECT pg_terminate_backend(pid); และป้องกันในอนาคตด้วยการตั้งเวลาตัด session ที่ค้างกลาง transaction

ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
SELECT pg_reload_conf();

Replication slot ที่ active เป็น false มานานก็ขวางได้เช่นกัน ถ้าเป็นเครื่องสำรองที่เลิกใช้แล้ว ให้ลบ slot นั้นออกด้วย pg_drop_replication_slot ดูเรื่อง slot เพิ่มเติมได้ใน คู่มือ Streaming Replication

ขั้นตอนที่ 4: รัน VACUUM ด้วยมือและดูความคืบหน้า

VACUUM (VERBOSE, ANALYZE) public.orders;

VACUUM ธรรมดาใช้ล็อกระดับ SHARE UPDATE EXCLUSIVE ผู้ใช้ยังอ่านและเขียนตารางได้ตามปกติ บล็อกเฉพาะคำสั่งแก้โครงสร้างตาราง จึงรันระหว่างวันได้ ถ้าตารางใหญ่ ให้เปิดอีกหน้าต่างเพื่อดูความคืบหน้า

SELECT pid, relid::regclass, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;

ในผลลัพธ์ VERBOSE ให้ดูบรรทัด tuples ถ้าค่า dead but not yet removable สูง แปลว่ายังมีสิ่งขวางตามขั้นตอนที่ 3 อยู่ การรันซ้ำจะไม่ช่วยจนกว่าจะแก้ต้นเหตุ

ALTER TABLE public.orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01
);

SELECT relname, reloptions FROM pg_class WHERE relname = 'orders';

SET lock_timeout = '5s';
VACUUM (FULL, VERBOSE, ANALYZE) public.orders;