MVCC، tuple مرده و VACUUM: چرا جدول پف می‌کند

پستگرس هیچ ردیفی را درجا عوض نمی‌کند

در پستگرس، UPDATE یعنی «نسخه‌ی قدیمی را منقضی کن و نسخه‌ی جدیدی بنویس» و DELETE یعنی «نسخه را منقضی کن». به این مدل MVCC (کنترل همروندی چندنسخه‌ای) می‌گویند. هر نسخه (tuple) دو برچسب پنهان دارد: xmin شماره‌ی تراکنشی که آن را ساخته و xmax شماره‌ی تراکنشی که منقضی‌اش کرده. هر تراکنش با یک snapshot تصمیم می‌گیرد کدام نسخه‌ها را ببیند. نتیجه‌ی شیرین: خواننده هرگز نویسنده را معطل نمی‌کند و برعکس.

CREATE TABLE demo (id int PRIMARY KEY, qty int);
INSERT INTO demo VALUES (1, 10);
SELECT ctid, xmin, xmax, * FROM demo;   -- (0,1) | 812 | 0 | 1 | 10
UPDATE demo SET qty = 9 WHERE id = 1;
SELECT ctid, xmin, xmax, * FROM demo;   -- (0,2) | 813 | 0 | 1 | 9
-- نسخه‌ی (0,1) هنوز روی دیسک است: یک tuple مرده

هزینه‌ی این مدل: نسخه‌های مرده تا وقتی کسی پاکشان نکند فضا می‌گیرند. جدول انبار نخ که روزی صدها هزار بار موجودی‌اش UPDATE می‌شود، بدون نگهداری چند برابر حجم واقعی‌اش می‌شود؛ این همان bloat است.

VACUUM و autovacuum

VACUUM نسخه‌هایی را که دیگر هیچ تراکنشی نمی‌بیند، علامت «قابل استفاده‌ی مجدد» می‌زند، visibility map را به‌روز می‌کند (برای Index Only Scan) و ورودی‌های ایندکس متناظر را پاک می‌کند. فایل را کوچک نمی‌کند؛ فقط فضای خالی را برای درج‌های بعدی آماده می‌کند. VACUUM FULL جدول را از نو می‌نویسد و کوچک می‌کند، اما در تمام مدت قفل ACCESS EXCLUSIVE دارد: نه خواندن، نه نوشتن.

autovacuum وقتی سراغ جدول می‌رود که تعداد tuple مرده از 50 + 0.2 × تعداد ردیف‌ها بیشتر شود. برای جدول ۵۰ میلیونی یعنی ده میلیون ردیف مرده؛ خیلی دیر. برای جدول‌های بزرگ و پرتغییر، تنظیم جدول‌به‌جدول بدهید:

ALTER TABLE factory.yarn_stock SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 1000,
  autovacuum_analyze_scale_factor = 0.02
);

-- وضعیت tupleهای مرده و آخرین vacuum
SELECT 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_autovacuum, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

HOT و fillfactor

اگر UPDATE هیچ ستون ایندکس‌شده‌ای را تغییر ندهد و در همان صفحه جای خالی باشد، پستگرس نسخه‌ی جدید را در همان صفحه می‌نویسد و ایندکس‌ها را دست نمی‌زند (HOT update). برای جدول‌هایی که مدام UPDATE می‌شوند، fillfactor کمتر از ۱۰۰ جا برای HOT می‌گذارد:

ALTER TABLE factory.yarn_stock SET (fillfactor = 80);
SELECT relname, n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'yarn_stock';

اندازه‌گیری bloat

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

نکته‌هایی که کمتر کسی می‌داند

• افزودن یک ایندکس روی ستونی که مدام UPDATE می‌شود (مثل updated_at) همه‌ی HOT updateهای جدول را از بین می‌برد؛ گاهی حذف همین یک ایندکس bloat را حل می‌کند.
• برای کوچک کردن جدول پف‌کرده بدون قفل طولانی، اکستنشن pg_repack همان کار VACUUM FULL را با قفل لحظه‌ای انجام می‌دهد.
• VACUUM فقط صفحه‌های خالیِ انتهای فایل را به سیستم‌عامل برمی‌گرداند؛ فضای خالی وسط فایل فقط برای درج‌های بعدی همان جدول قابل استفاده است.
• از نسخه‌ی 13، autovacuum با درج‌های زیاد هم فعال می‌شود (autovacuum_vacuum_insert_threshold)؛ جدول‌های فقط‌درج (لاگ) هم visibility map به‌روز می‌گیرند.
• هر ROLLBACK هم tuple مرده تولید می‌کند؛ برنامه‌ای که هزاران INSERT را به خاطر خطا rollback می‌کند، بی‌صدا bloat می‌سازد.