นักพัฒนาส่วนใหญ่มอง PostgreSQL เป็นกล่องดำที่บางครั้งสร้างความประหลาดใจให้ — ตารางที่โตขึ้นทั้ง ๆ ที่ควรหดลง คิวรีที่คลานหลังจากรัน batch job เสร็จ การตั้งค่า autovacuum ที่ก๊อปมาจากคำตอบบน Stack Overflow ที่ไม่มีใครเข้าใจจริง ๆ ความประหลาดใจเหล่านี้จะหมดไปทันทีเมื่อคุณมองเห็นว่าแถวข้อมูลถูกเก็บ ถูกทำเวอร์ชัน และถูกเรียกคืนพื้นที่อย่างไร โพสต์นี้จะพาเดินผ่านโมเดลการจัดเก็บข้อมูลตั้งแต่ระดับไบต์ขึ้นไป เพื่อให้ปุ่มปรับจูนต่าง ๆ กลายเป็นเรื่องชัดเจน

TL;DR

ทุก UPDATE ใน Postgres จริง ๆ แล้วคือ INSERT + DELETE — เวอร์ชันเก่าของแถวยังคงอยู่บนดิสก์จนกว่า VACUUM จะเรียกคืนพื้นที่

tuple แต่ละตัวมี transaction ID xmin/xmax ติดอยู่ เพื่อให้ MVCC ตัดสินใจเรื่อง visibility ได้โดยไม่ต้องล็อก

HOT update ช่วยเลี่ยงการกระทบ index เมื่อแถวใหม่ยังพอใส่ในเพจเดิมและไม่มี indexed column ใดเปลี่ยน — ตั้งเป้าให้เกิน 80% HOT บนตาราง OLTP ที่ทำงานหนัก

bloat คือราคาปกติของ MVCC; วัดด้วย pgstattuple / pgstatindex และจูน autovacuum แบบรายตาราง ไม่ใช่ตั้งค่าเดียวทั่วทั้งระบบ

VACUUM ทำสามหน้าที่: เรียกคืนพื้นที่, freeze XID เก่า และอัปเดต visibility map — VACUUM FULL คือเหตุการณ์ระบบล่ม ไม่ใช่งาน maintenance

transaction ID wraparound คือเหตุล่มแบบหนัก ๆ ถ้า autovacuum แพ้การแข่ง; ให้มอนิเตอร์ age(datfrozenxid) และตั้งเตือนที่ 40-50%

transaction ที่รันยาวและ replication slot ที่ถูกทิ้งไว้จะยึด xmin horizon เอาไว้ ซึ่งเป็นสาเหตุของ bloat ที่พบได้บ่อยที่สุด

ประโยคเดียวที่นักพัฒนาส่วนใหญ่ไม่รู้

ใน PostgreSQL UPDATE จริง ๆ แล้วคือ INSERT + DELETE

ข้อเท็จจริงเดียวนี้อธิบายเกือบทุกสิ่งที่น่าประหลาดใจของ Postgres ได้ มันอธิบายว่าทำไมตารางของคุณยังโตขึ้นเรื่อย ๆ หลัง batch job ที่แค่แก้ไขแถว มันอธิบายว่าทำไมต้องมี VACUUM เลย มันอธิบายว่าทำไมตารางที่มีแถวยังมีชีวิต 10 ล้านแถวสามารถกินพื้นที่บนดิสก์ที่ปกติพอใส่ 40 ล้านแถวได้ มันอธิบายว่าทำไม autovacuum_vacuum_scale_factor โผล่อยู่ในคู่มือจูนทุกฉบับ และทำไมไม่มีใครเห็นพ้องว่าควรตั้งค่าเป็นเท่าไหร่

โพสต์เรื่องการจูน performance ของ Postgres ที่มีอยู่ในเว็บนี้คือรายการของคันโยกที่ดึงได้ ส่วนโพสต์นี้คือ “ทำไม” — โมเดลการจัดเก็บ กฎ visibility และวงจร maintenance ที่คันโยกเหล่านั้นจริง ๆ แล้วกำลังจูนอยู่ ถ้าคุณเคยจ้อง query plan แล้วสงสัยว่าทำไม Postgres ต้องสแกน 4 GB เพื่อดึง 12 แถวจากตารางเล็ก ๆ คำตอบอยู่ในนี้

กลุ่มเป้าหมาย: senior backend engineer ที่รู้แล้วว่า index ช่วยเงื่อนไข WHERE ได้ และอยากเข้าใจกลไกที่อยู่ข้างใต้

Pages, Tuples และ ctid Address

Postgres เก็บตารางเป็นลำดับของ page ขนาดคงที่ ปกติคือ 8 KB heap relation ทุกตัวบนดิสก์ก็แค่ไฟล์เดียว — หรือชุดไฟล์ ไฟล์ละ 1 GB — ที่เต็มไปด้วย page ขนาด 8 KB ที่นับเลขจากศูนย์

ภายในเพจ แถวข้อมูลเรียกว่า tuple tuple ไม่ใช่แค่ข้อมูลคอลัมน์ของคุณ แต่ประกอบด้วย:

header HeapTupleHeaderData ขนาด 23 ไบต์
null bitmap (เผื่อไว้ มีหรือไม่ก็ได้)
ค่าของคอลัมน์ จัดเรียงตามลำดับ attribute ของตารางและกฎการ alignment

header เป็นที่อยู่ของฟิลด์ที่น่าสนใจ:

t_xmin — transaction ID ที่ insert tuple นี้
t_xmax — transaction ID ที่ลบหรืออัปเดตมัน (เป็น 0 ถ้ายังมีชีวิตอยู่)
t_cid — command ID ภายในธุรกรรม
t_ctid — pointer ทางกายภาพไปยังเวอร์ชันถัดไปของแถวนี้ (ถ้ามี)
t_infomask / t_infomask2 — บิตธง รวมถึง hint bits, สถานะ HOT, สถานะ freeze และธง null

แถวทุกแถวมี address เรียกว่า ctid เขียนเป็น (block, offset) block 0 offset 3 คือ (0,3) มันคือตำแหน่งทางกายภาพในไฟล์ index — สำหรับ entry ที่ไม่ใช่ HOT — เก็บ ctid ของแถวที่มันชี้ไป เมื่อแถวย้ายไปอยู่ที่ตำแหน่งกายภาพใหม่ index entry เก่าก็จะกลายเป็น stale และตำแหน่งใหม่ต้องการ entry ใหม่

ย่อหน้าสุดท้ายนั้นคือเหตุผลทั้งหมดที่ HOT update ดำรงอยู่ มีรายละเอียดต่อไปด้านล่าง

พื้นฐานของ MVCC

PostgreSQL ใช้ Multi-Version Concurrency Control (MVCC) แทนกลยุทธ์ “ล็อกทุกอย่างแล้วภาวนา” ที่ฐานข้อมูลรุ่นเก่าใช้กัน แนวคิดหลักคือ:

writer ไม่บล็อก reader
reader ไม่บล็อก writer
ธุรกรรมแต่ละตัวเห็น snapshot ของฐานข้อมูลที่สอดคล้องกันในจุดเวลาหนึ่ง

กลไกนั้นเป็นไปตาม header ที่บอกใบ้พอดี ทุก tuple จะถูกประทับด้วยธุรกรรมที่สร้างมัน (xmin) และเมื่อจำเป็น ธุรกรรมที่ลบมัน (xmax) ธุรกรรมที่รันด้วย snapshot S จะเห็น tuple ก็ต่อเมื่อ:

xmin ถูก commit แล้วและมองเห็นได้โดย S และ
xmax เป็น 0, ถูก abort หรือยังไม่ปรากฏแก่ S

ดังนั้น UPDATE ในเชิงแนวคิดคือ:

ตั้ง xmax = current_xid ของ tuple เก่า
insert tuple ใหม่ที่มี xmin = current_xid และ xmax = 0
ชี้ t_ctid ของ tuple เก่าไปที่ tuple ใหม่

ไม่มีอะไรถูกเขียนทับเลย เวอร์ชันเก่ายังคงอยู่บนดิสก์ มองเห็นได้โดยใครก็ตามที่ snapshot เก่ากว่า update นั้น จนกว่า vacuum จะมาเรียกคืนพื้นที่

ลองดูด้วยตาตัวเอง

วิธีที่ดีที่สุดในการซึมซับเรื่องนี้คือลองมองมันจริง ๆ Postgres เปิดเผย ctid, xmin และ xmax เป็น pseudo-column ในทุกตาราง

CREATE TABLE toy (id int PRIMARY KEY, note text);

INSERT INTO toy VALUES (1, 'original');
SELECT ctid, xmin, xmax, * FROM toy;
--  ctid  | xmin | xmax | id |   note
-- -------+------+------+----+----------
--  (0,1) |  742 |    0 |  1 | original

UPDATE toy SET note = 'first edit' WHERE id = 1;
SELECT ctid, xmin, xmax, * FROM toy;
--  ctid  | xmin | xmax | id |    note
-- -------+------+------+----+------------
--  (0,2) |  743 |    0 |  1 | first edit

UPDATE toy SET note = 'second edit' WHERE id = 1;
SELECT ctid, xmin, xmax, * FROM toy;
--  ctid  | xmin | xmax | id |    note
-- -------+------+------+----+-------------
--  (0,3) |  744 |    0 |  1 | second edit

มีแถวที่มีชีวิตเพียงแถวเดียวที่มองเห็น แต่เวอร์ชันที่ตายแล้วสองตัวยังคงนั่งอยู่ที่ (0,1) และ (0,2) คุณยืนยันได้ด้วย extension pageinspect:

CREATE EXTENSION IF NOT EXISTS pageinspect;

SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask2
FROM   heap_page_items(get_raw_page('toy', 0));
--  lp | t_xmin | t_xmax | t_ctid | t_infomask2
-- ----+--------+--------+--------+-------------
--   1 |    742 |    743 | (0,2)  |       16386
--   2 |    743 |    744 | (0,3)  |       40962
--   3 |    744 |      0 | (0,3)  |       32770

มี item pointer สามตัว สองตัวแรกมี xmax หมายความว่ามันถูกแทนที่ไปแล้ว สาย t_ctid เดินจากเก่าไปใหม่: (0,1) → (0,2) → (0,3) ส่วนค่า 16386 และ 40962 ใน t_infomask2 คือบิตธง — บิตบนสุดบอกว่า “tuple นี้เป็น HOT updated” และ “นี่คือ heap-only tuple” ซึ่งเป็นหัวข้อต่อไป

ทำไม SELECT ถึงไม่ล็อก

SELECT แบบธรรมดาใน Postgres ไม่จับ row-level lock เลย มันจับ AccessShareLock แบบเบา ๆ บนตารางเพื่อบล็อก DROP แต่ไม่มีอะไรในระดับแถว ธุรกรรมสองตัวที่อ่านแถวเดียวกันพร้อมกันเป็นอิสระจากกันโดยสิ้นเชิง

ทำได้อย่างไร? ด้วย snapshot isolation ที่จุดเริ่มต้นของ statement (สำหรับ READ COMMITTED) หรือจุดเริ่มต้นของธุรกรรม (สำหรับ REPEATABLE READ) Postgres จับ snapshot ที่ประกอบด้วย:

xmin — transaction ID ที่เก่าที่สุดที่ยังรันอยู่
xmax — transaction ID ตัวถัดไปที่จะถูกออกให้
xip[] — รายการ transaction ID ที่ยังดำเนินอยู่ระหว่างสองค่าด้านบน

เมื่อสแกน ค่า t_xmin และ t_xmax ของแต่ละ tuple จะถูกตรวจกับ snapshot นั้นบวกกับ commit log (clog) tuple จะถูกเห็นถ้าผู้สร้างมัน commit ก่อนที่ snapshot จะถูกจับ และผู้ทำลายมันไม่มีตัวตนหรือ commit หลังจากนั้น ไม่ต้องใช้ล็อกเลยเพราะข้อมูลตัวมันเองพกพาข้อมูลที่พอจะตัดสินเรื่อง visibility อยู่แล้ว

ผลพวง: ธุรกรรมที่รันยาวจะถือ snapshot ของมันเปิดอยู่ และทุกแถวที่ถูก update หรือ delete ระหว่างชีวิตของธุรกรรมนั้น ต้องอยู่ต่อ — แม้จะไม่มี session อื่นใดอ่านแถวนั้นเลยก็ตาม — จนกว่าธุรกรรมนั้นจะจบ นี่คือวิธีที่ reporting query ที่ค้างอยู่สามารถทำให้ตาราง OLTP ใน production บวมขึ้นเป็นกิกะไบต์ Postgres ทำความสะอาดสิ่งที่อาจยังมองเห็นได้โดยใครบางคนไม่ได้

HOT: Heap-Only Tuple Updates

กลับไปที่สายของ tuple ที่ตายแล้วนั้น ลองนึกว่าจะเกิดอะไรขึ้นกับ index เมื่อแถวย้ายจาก (0,1) ไปที่ (0,2) ถ้า index entry ทุกตัวชี้ไปที่ (0,1) index ทุกตัวต้องมี entry ใหม่ที่ชี้ไปที่ (0,2) — write amplification ระดับหายนะบนตารางที่มี index หลายตัว

Postgres เลี่ยงเรื่องนี้ด้วยการ optimize แบบ Heap-Only Tuple (HOT) HOT update คือ update ที่:

เวอร์ชันแถวใหม่ใส่ลงในเพจเดียวกับเวอร์ชันเก่าได้
ไม่มี indexed column ใดเปลี่ยน

เมื่อทั้งสองเงื่อนไขเป็นจริง Postgres จะ:

เขียนเวอร์ชันใหม่ลงในเพจเดียวกัน
เชื่อม t_ctid ของ tuple เก่ากับตัวใหม่
ทำเครื่องหมาย tuple ใหม่เป็น heap-only (HEAP_ONLY_TUPLE) และ tuple เก่าเป็น HOT updated (HEAP_HOT_UPDATED)
ไม่แตะ index ใดเลย

การ lookup ผ่าน index จะมาตกที่ tuple เก่า เห็นธง HOT แล้วเดินตามสาย ctid ไปยังเวอร์ชันที่มีชีวิต index ไม่จำเป็นต้องรู้เลยว่าแถวย้ายไป

สองเงื่อนไขนั้นคือสิ่งที่ทำให้ HOT เป็นไปได้ ถ้า update ของคุณเปลี่ยนคอลัมน์ใน index ใด ๆ — แม้แต่ index ที่คุณลืมไปแล้ว แม้แต่คอลัมน์ใน WHERE ของ partial index — คุณจะเสีย HOT และจ่ายค่าเต็ม: heap tuple ใหม่บวกกับ entry ใหม่ใน index ทุกตัว

คุณดูอัตราส่วนได้ดังนี้:

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS hot_pct
FROM   pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT  20;

ตาราง OLTP ที่สุขภาพดี ซึ่งทำการ update คอลัมน์ที่ไม่ได้ index แบบ in-place ควรมีค่ามากกว่า 80% HOT ถ้าตารางที่ทำงานหนักอยู่ที่ 5% นั่นเป็นเพราะมีบางสิ่งใน update ทุกตัวแตะ index — บ่อยครั้งคือคอลัมน์ updated_at ที่ใครบางคน index ไว้เพื่อใช้เรียงลำดับ การลบ index นั้นหรือเลือกกลยุทธ์อื่นมักเป็นชัยชนะด้าน write performance ที่ใหญ่ที่สุดเพียงการเปลี่ยนแปลงเดียวที่ทำได้

ปัญหาเรื่อง Bloat

Dead tuple สะสมขึ้นเรื่อย ๆ นั่นไม่ใช่ข้อบกพร่องของการออกแบบ มันคือต้นทุนของการให้ reader และ writer อยู่ร่วมกันได้โดยไม่มีล็อก มันจะกลายเป็นปัญหาเมื่อ dead tuple มีจำนวนมากกว่าที่ยังมีชีวิต หรือเมื่อเพจถูกแยกส่วน (fragmented) จนการ sequential scan อ่านพื้นที่ว่างเสียส่วนใหญ่

bloat มีสองแบบ:

Table bloat — dead tuple ใน heap page ตาราง 1 GB อาจมีข้อมูลที่มีชีวิต 200 MB และพื้นที่ตายอีก 800 MB ที่ไม่มีคิวรีใดจะเห็นอีกเลย
Index bloat — index entry ที่ stale ชี้ไปยัง dead tuple หรือ leaf page ที่ตายแล้วซึ่งการ rebalance ของ B-tree ทิ้งไว้เป็นกำพร้า B-tree ไม่หดตัวเองเลย มันจะกว้างขึ้นอย่างเดียว

extension pgstattuple วัด bloat ได้แม่นยำ — โดยแลกกับการสแกนทั้งตาราง:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('orders');
-- table_len         | 1073741824
-- tuple_count       | 4821305
-- tuple_len         | 689432716
-- tuple_percent     | 64.21
-- dead_tuple_count  | 1842910
-- dead_tuple_len    | 263489520
-- dead_tuple_percent| 24.54
-- free_space        | 88901632
-- free_percent      | 8.28

SELECT * FROM pgstattuple_approx('orders');  -- sampled, much faster

ประมาณหนึ่งในสี่ของตารางนี้คือน้ำหนักที่ตายแล้ว นั่นพอมีพื้นที่ให้ปรับปรุงแต่ไม่ใช่เหตุฉุกเฉิน ถ้าตายเกิน 50% มันจะกลายเป็นเหตุฉุกเฉิน: ทุก sequential scan อ่าน I/O สองเท่าของที่ควร, buffer cache ปั่นป่วน, และ query plan ก็ค่อย ๆ เสื่อมลงเงียบ ๆ

สำหรับ index pgstatindex บอกเรื่องราวเทียบเคียงกัน:

SELECT * FROM pgstatindex('orders_customer_id_idx');
-- version             | 4
-- tree_level          | 3
-- index_size          | 184549376
-- root_block_no       | 412
-- internal_pages      | 487
-- leaf_pages          | 21941
-- empty_pages         | 0
-- deleted_pages       | 6104
-- avg_leaf_density    | 52.11
-- leaf_fragmentation  | 18.3

avg_leaf_density ที่ต่ำกว่าเป้า 90% มาก บวกกับ deleted_pages ที่สูง หมายความว่า index นี้ปั่นป่วนหนักและไม่เคยถูก rebuild

VACUUM: จริง ๆ แล้วมันทำอะไร

VACUUM มีสามหน้าที่ และการเข้าใจว่าคุณสนใจหน้าที่ไหนคือครึ่งหนึ่งของการจูนมัน

1. เรียกคืนพื้นที่จาก dead tuple สำหรับ dead tuple แต่ละตัวที่ xmax เก่ากว่าทุก snapshot ปัจจุบัน VACUUM จะทำเครื่องหมาย line pointer ของมันเป็นใช้ซ้ำได้ พื้นที่ว่างของเพจจะถูกอัปเดตใน Free Space Map (FSM) เพื่อให้การ insert ในอนาคตใช้งานได้ ไฟล์ตารางบนดิสก์ไม่หดลง พื้นที่ถูกนำกลับมาใช้ใน extent ที่มีอยู่

2. Freeze transaction ID เก่า ๆ ค่า xmin ขนาด 32 บิตจะ wrap ทุก ๆ 4 พันล้านธุรกรรม VACUUM จะเขียน tuple ที่เก่ากว่า vacuum_freeze_min_age ใหม่ ให้ xmin ของมันถูกแทนที่ด้วยตัวบ่งชี้ “frozen” พิเศษซึ่งถือว่ามองเห็นได้โดย snapshot ทุกตัวในอนาคต ถ้าไม่มีกระบวนการนี้ cluster จะปิดตัวเองในที่สุดเพื่อป้องกันข้อมูลเสียหาย (ดูส่วน wraparound)

3. อัปเดต visibility map และ statistics visibility map ติดตามว่าเพจไหน “all-visible” (ไม่มี tuple ที่ต้องตรวจ visibility) เพื่อเปิดใช้ index-only scan VACUUM อัปเดต map นี้ และเมื่อรันเป็น VACUUM ANALYZE ก็จะ refresh statistics ที่ planner ใช้คำนวณต้นทุนคิวรีด้วย

สามตัวแปร

VACUUM table — เวอร์ชันปกติแบบ concurrent จับ ShareUpdateExclusiveLock ที่อนุญาตให้อ่านและเขียนได้ ไม่คืนพื้นที่ดิสก์ให้ OS
VACUUM FULL table — เขียนทั้งตารางใหม่ลงในไฟล์ใหม่ กำจัดพื้นที่ตายทั้งหมด แล้วสลับเข้ามา คืนพื้นที่ดิสก์ให้ OS จับ AccessExclusiveLock — บล็อกทุกอย่าง รวมถึงการอ่าน — ตลอดเวลาที่รัน บนตาราง 200 GB นี่คือเหตุล่มหลายชั่วโมง แทบไม่เคยเป็นทางเลือกที่ถูกต้องใน production ใช้ pg_repack หรือ pg_squeeze แทนสำหรับการทำแบบ online
VACUUM FREEZE table — freeze tuple ทุกตัวที่เข้าเกณฑ์อย่างจริงจัง ไม่ใช่แค่ตัวที่เลย vacuum_freeze_min_age มีประโยชน์ก่อน pg_upgrade หรือเมื่อรู้ว่า wraparound กำลังจะมาถึง นอกนั้นถือว่าเป็นเสียงรบกวน

Autovacuum

ไม่มีใครรัน VACUUM ด้วยมือในระบบปกติ launcher ของ autovacuum จะ spawn worker process ขึ้นมาเลือกตารางที่จะ vacuum ตาม threshold

ตารางจะเข้าเกณฑ์ของ autovacuum เมื่อ:

dead_tuples > autovacuum_vacuum_threshold
            + autovacuum_vacuum_scale_factor * reltuples

ค่าเริ่มต้นคือ threshold = 50, scale_factor = 0.2 หมายความว่าตารางที่มี 10 ล้านแถวจะรอจนกว่าจะมี dead tuple 2 ล้านแถวก่อนถูกแตะ บนตาราง OLTP ที่ทำงานหนักด้วย 10,000 update ต่อวินาที นั่นคือภูเขาของ bloat ก่อน autovacuum จะเริ่มเสียอีก และพอมันรันเสร็จภูเขาลูกถัดไปก็ก่อตัวขึ้นแล้ว

วิธีรักษาคือจูนเป็นรายตาราง:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor   = 0.02,  -- 2% instead of 20%
  autovacuum_vacuum_threshold      = 1000,
  autovacuum_analyze_scale_factor  = 0.01,
  autovacuum_vacuum_cost_limit     = 2000,  -- let workers run faster
  autovacuum_vacuum_cost_delay     = 2      -- ms between I/O batches
);

cost-based delay คืออีกครึ่งหนึ่งของการจูน autovacuum worker รันด้วยงบ I/O — ทำงาน cost_limit work unit แล้วหลับ cost_delay มิลลิวินาที ค่าเริ่มต้นถูกตั้งให้ระมัดระวังโดยจงใจ เพื่อไม่ให้ vacuum แย่งทรัพยากรจากคิวรี foreground บนระบบที่ใช้ SSD สมัยใหม่ ค่าเริ่มต้นเหล่านั้นมักช้ากว่าที่ควรถึง 10 เท่า ตารางที่ทำงานหนักอาจสะสม dead tuple ได้เร็วกว่าที่ autovacuum จะเก็บกวาดทันด้วยค่าเริ่มต้น และมันจะตามหลังไปเรื่อย ๆ จนคุณรู้ตัวด้วยการแจ้งเตือน bloat

ตัวอย่างการตั้งค่า autovacuum ขั้นต่ำสำหรับ cluster ที่เขียนหนักบน storage เร็ว:

# postgresql.conf fragments — illustrative, not one-size-fits-all
autovacuum: on
autovacuum_max_workers: 6
autovacuum_naptime: 15s
autovacuum_vacuum_cost_delay: 2ms
autovacuum_vacuum_cost_limit: 2000
autovacuum_vacuum_scale_factor: 0.05
autovacuum_vacuum_insert_scale_factor: 0.10   # v13+
autovacuum_analyze_scale_factor: 0.02
maintenance_work_mem: 1GB                     # lets vacuum batch index cleanup

เพิ่ม maintenance_work_mem ก็ต่อเมื่อเครื่องมี RAM พอเท่านั้น — autovacuum worker แต่ละตัวอาจใช้ได้ถึงค่านี้ การตั้ง 1GB กับ 6 worker บนเครื่อง 2 GB คือสูตรของ OOM

Transaction ID Wraparound

ตัวนับธุรกรรม 32 บิตมีการ wrap เมื่อเกิดขึ้น transaction ID เมื่อ 2 พันล้านธุรกรรมก่อนจะกลายเป็น “อยู่ในอนาคต” และ tuple ของมันจะกลายเป็นมองไม่เห็น ข้อมูลของคุณจะหายไปอย่างมีประสิทธิภาพ

Postgres ป้องกันสิ่งนี้ด้วยกลไก freeze ที่อธิบายไปแล้ว แต่ถ้า autovacuum ตามหลังมากพอจนมี tuple ที่ xmin กำลังเข้าใกล้ระยะ wraparound Postgres จะ:

ที่ autovacuum_freeze_max_age (ค่าเริ่มต้น 200 ล้าน) ปล่อย anti-wraparound autovacuum แบบรุนแรงที่ยกเลิกไม่ได้
ที่ vacuum_failsafe_age (ค่าเริ่มต้น 1.6 พันล้าน) ข้าม cost limit และการเก็บกวาด index เพื่อ freeze ให้เร็วขึ้น
ที่ประมาณ 3 ล้านธุรกรรมก่อน wraparound ปฏิเสธธุรกรรมใหม่ และ log ว่า: ERROR: database is not accepting commands to avoid wraparound data loss

ตอนนี้คุณ offline แล้ว การกู้คืนคือ VACUUM FREEZE ใน single-user mode ซึ่งบน cluster ใหญ่ใช้เวลาเป็นชั่วโมงถึงเป็นวัน

ให้มอนิเตอร์เรื่องนี้ตลอด:

SELECT  datname,
        age(datfrozenxid)                              AS xid_age,
        2^31 - age(datfrozenxid)                       AS xids_remaining,
        round(100.0 * age(datfrozenxid) / 2^31, 2)     AS pct_used
FROM    pg_database
ORDER BY age(datfrozenxid) DESC;

ถ้า pct_used เกิน 50% เมื่อใด แปลว่า autovacuum กำลังแพ้การแข่ง รีบสืบสวนทันที สาเหตุเกือบเป็นเสมอ คือ (ก) ธุรกรรมที่อายุยืนมากหรือ replication slot ที่ยึด xmin horizon ไว้, (ข) การตั้งค่า autovacuum ระมัดระวังเกินไปสำหรับอัตราการเขียน หรือ (ค) autovacuum worker ที่ค้างเพราะเจอล็อกที่ขอไม่ได้

Index และ Bloat

Index bloat เป็นสัตว์ร้ายอีกตัวหนึ่ง VACUUM บนตารางจะวนผ่าน index แต่ละตัวเพื่อลบ entry ที่ชี้ไปที่ heap tuple ที่ถูกลบ แต่โครงสร้างภายในของ B-tree ไม่ยุบลงเอง พอ leaf page ว่างมันใช้ซ้ำได้ก็จริง แต่เพจระดับเหนือกว่ายังเก็บคีย์ของมันไว้ และต้นไม้ก็ไม่เคยเตี้ยลง

หลังจากการปั่นป่วนหนักหลายเดือน B-tree บนตาราง 5 GB อาจใหญ่ถึง 8 GB ได้ วิธีแก้คือ rebuild มัน:

REINDEX INDEX CONCURRENTLY orders_customer_id_idx;
REINDEX TABLE CONCURRENTLY orders;    -- all indexes on the table

CONCURRENTLY (เพิ่มใน Postgres 12) สร้าง index ใหม่ขนานกับตัวเก่า สลับกันแบบ atomic แล้ว drop ตัวเก่า — ทั้งหมดโดยไม่บล็อกการเขียน มันใช้เวลานานกว่า REINDEX ธรรมดาประมาณ 2-3 เท่า และใช้ดิสก์เพิ่ม แต่ปลอดภัยพอที่จะรันใน production ถ้า concurrent reindex ล้มกลางคัน คุณจะเหลือ index ที่ invalid ที่ต้อง drop ออกก่อนลองใหม่

หลักทั่วไป: ตั้งเวลา REINDEX CONCURRENTLY บนตารางที่ทำงานหนักที่สุดทุก ๆ ไตรมาส มอนิเตอร์ด้วย pgstatindex ตามที่แสดงไว้ก่อนหน้า

Fill Factor: เมื่อใดที่มันช่วย HOT จริง ๆ

ตารางและ index ทุกตัวมี fillfactor — เปอร์เซ็นต์ของเพจที่ Postgres เติมเต็มระหว่าง INSERT ค่าเริ่มต้นคือ 100 สำหรับตาราง (เติมเต็ม) และ 90 สำหรับ B-tree (เหลือพื้นที่ว่าง 10% สำหรับการเติบโตของ leaf)

การลด fillfactor ของตาราง — เช่นเหลือ 80 — จะทำให้ทุกเพจเหลือพื้นที่ว่าง 20% ตอน insert นั่นสำคัญเพราะ HOT update ต้องการให้ tuple ใหม่ใส่ในเพจเดียวกันได้ ถ้าเพจเต็ม 100% อยู่แล้ว update จะเป็น HOT ไม่ได้และต้องย้ายไปเพจใหม่ (และอัปเดตทุก index) ถ้ามีพื้นที่ว่าง 20% update ส่วนใหญ่ก็จะอยู่ที่เดิมได้

ALTER TABLE sessions SET (fillfactor = 80);
-- Existing pages are unchanged; new pages use the new fill factor.
VACUUM FULL sessions;  -- or pg_repack, to actually re-pack with the new fill factor

ทำสิ่งนี้เฉพาะกับตารางที่ update หนักบนคอลัมน์ที่ไม่ได้ index บนตารางที่มีแต่ insert (event log, immutable fact) fill factor ต่ำคือการเปลืองล้วน ๆ บนตารางที่ update หนักและ update ทุกครั้งเปลี่ยน indexed column ก็ไม่ช่วยเหมือนกัน เพราะ update เหล่านั้นเป็น HOT ไม่ได้ตั้งแต่แรก จุดที่เหมาะสมคือแถวที่เปลี่ยนแปลงได้ซึ่งคอลัมน์ index มีเสถียรภาพ — session heartbeat, counter, timestamp “last seen” ที่ไม่ได้ index

การตรวจวินิจฉัยภาคปฏิบัติ

รายการสั้น ๆ ของ statistics view ที่บอกคุณว่ากำลังเกิดอะไรขึ้นจริง ๆ:

-- Which tables are accumulating dead tuples fastest?
SELECT  schemaname, relname,
        n_live_tup, n_dead_tup,
        round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
        last_vacuum, last_autovacuum, autovacuum_count
FROM    pg_stat_user_tables
WHERE   n_dead_tup > 1000
ORDER BY dead_pct DESC NULLS LAST
LIMIT   20;

-- What is autovacuum working on right now?
SELECT  pid, datname, relid::regclass AS table,
        phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed,
        index_vacuum_count
FROM    pg_stat_progress_vacuum;

-- Which sessions are holding the xmin horizon back?
SELECT  pid, usename, state, backend_xmin,
        now() - xact_start AS xact_age, query
FROM    pg_stat_activity
WHERE   backend_xmin IS NOT NULL
ORDER BY backend_xmin
LIMIT   10;

-- Replication slots can also hold xmin; check them too
SELECT slot_name, active, xmin, catalog_xmin,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM   pg_replication_slots;

รูปแบบที่ควรสังเกต: n_dead_tup สูงพร้อม last_autovacuum เก่าหมายความว่า autovacuum กำลังอด คิวรีจาก block ที่สามที่มี backend_xmin เก่ามากหมายถึงมีคนลืม commit ธุรกรรมและกำลังขัดขวางการเก็บกวาดทั่วทั้ง cluster replication slot ที่ไม่ active จาก block ที่สี่สามารถยึด xmin horizon ไว้แบบไม่มีกำหนด และเป็นสาเหตุคลาสสิกของ bloat ที่บานปลาย

probe ระดับ shell ที่มีประโยชน์ รันจาก psql:

psql -X -c "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
            FROM pg_stat_user_tables
            ORDER BY pg_total_relation_size(relid) DESC
            LIMIT 10;"

psql -X -c "SELECT relname, pg_size_pretty(pg_indexes_size(relid)) AS idx
            FROM pg_stat_user_tables
            ORDER BY pg_indexes_size(relid) DESC
            LIMIT 10;"

ถ้าขนาด index เทียบเท่าหรือเกินขนาด heap บนตารางเก่าที่ปั่นป่วนหนัก แสดงว่าคุณมี index bloat ที่รอการ reindex อยู่

สรุปเช็คลิสต์

ก่อนเดินจาก Postgres cluster ที่คุณตั้งใจจะรันใน production ให้ตรวจสอบว่า:

คุณรู้อัตราส่วน HOT ของตาราง 10 อันดับแรกที่เขียนหนัก ต่ำกว่า 50% หมายถึง indexed column กำลังถูกเขียนบ่อยเกินไป — หาให้เจอแล้วตัดสินใจว่า index นั้นคุ้มกับต้นทุนหรือไม่
autovacuum รายตาราง ถูกตั้งค่าบนตารางที่ทำงานหนัก ค่าเริ่มต้นเหมาะกับ workload ปี 1999
maintenance_work_mem สูงพอ (อย่างน้อย 256 MB; 1 GB บนเครื่องใหญ่กว่า) สำหรับ vacuum เก็บกวาด index ในรอบเดียว
ติดตาม wraparound เตือนที่ใช้ไป 40% ส่ง page ที่ 60%
จำกัดธุรกรรมที่รันยาว ตั้ง idle_in_transaction_session_timeout มอนิเตอร์อายุของ backend_xmin
replication slot ต้องสุขภาพดี slot ที่ถูกทิ้งคือระเบิดเวลา ตั้งสัญญาณเตือนสำหรับ slot ที่ไม่ active เกินหนึ่งชั่วโมง
REINDEX CONCURRENTLY ทุกไตรมาส บน index ที่ปั่นป่วนหนัก วัดด้วย pgstatindex อย่าเดา
Fill factor 80-90 บนตารางที่ update หนักซึ่ง update ไม่แตะคอลัมน์ index ใช้ค่าเริ่มต้นที่อื่น
VACUUM FULL อยู่ในคู่มือเหตุฉุกเฉิน ไม่ใช่คู่มือ maintenance ถ้าคุณคว้ามันมาใช้บ่อย ๆ แสดงว่ามีบางอย่างต้นน้ำผิดพลาด

หัวใจของ Postgres คือฐานข้อมูลที่ตั้งอยู่บนหลักการอย่างเคร่งครัด พฤติกรรมทุกอย่างที่ทำให้คุณประหลาดใจ — ไฟล์ที่โตขึ้น query latency ที่ค่อย ๆ คืบคลาน พายุ autovacuum ตอนตีสาม — ล้วนสืบเนื่องโดยตรงจากโมเดล MVCC บวก storage format บวกวงจร maintenance เมื่อคุณมองเห็น tuple, การเต้นรำของ xmin/xmax และสาย ctid ปุ่มจูนต่าง ๆ จะหยุดเป็นเรื่องสุ่มและกลายเป็นเรื่องชัดเจน

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

PostgreSQL Documentation: Concurrency Control — คำอธิบายมาตรฐานของ MVCC และ isolation level สั้นและคุ้มที่จะอ่านตั้งแต่ต้นจนจบ
PostgreSQL Documentation: Routine Database Maintenance Tasks — บทเกี่ยวกับ VACUUM และ autovacuum อย่างเป็นทางการ
The Internals of PostgreSQL — หนังสือออนไลน์ฟรีของ Hironobu Suzuki บทเกี่ยวกับ Concurrency Control และ VACUUM Processing เป็น deep dive ที่ดีที่สุดที่หาได้ฟรี
src/backend/access/heap/README.HOT ในซอร์สโค้ดของ Postgres — เอกสารการออกแบบกลไก HOT ที่อ่านง่ายอย่างน่าประหลาดใจ เขียนโดยนักพัฒนาที่สร้างมันขึ้น

Postgres ตอบแทน operator ที่เข้าใจมัน ใช้เวลาบ่ายหนึ่งกับ pageinspect และ pgstattuple บน staging copy ของฐานข้อมูล production ของคุณ คุณจะไม่อ่าน query plan ในมุมเดิมอีกเลย

เจาะลึก PostgreSQL Internals: MVCC, VACUUM และ HOT Updates