PostgreSQL یک سیستم پایگاه داده قدرتمند و قابل اعتماد است که ویژگی‌ها و مزایای زیادی ارائه می‌دهد. با این حال، برخی از چالش‌ها و معایب نیز وجود دارد، مانند مشکل table fragmentation. Table fragmentation زمانی رخ می‌دهد که فضای اشغال‌شده توسط یک جدول یا شاخص بزرگ‌تر از اندازه واقعی داده‌ها باشد. این موضوع می‌تواند بر عملکرد و کارایی پایگاه داده تأثیر منفی بگذارد و همچنین زمان پشتیبان‌گیری و بازیابی را افزایش دهد.
یکی از دلایل اصلی اجرای vacuum بر روی جداول، تجمع dead tuples است. Dead tuples به ردیف‌هایی اطلاق می‌شود که حذف یا به‌روزرسانی شده‌اند، اما همچنان برای تراکنش‌های همزمان قابل مشاهده هستند. PostgreSQL از مکانیزمی به نام MVCC (Multi-Version Concurrency Control) برای اطمینان از جداسازی و سازگاری تراکنش‌ها استفاده می‌کند. این مکانیزم به هر تراکنش اجازه می‌دهد بدون قفل کردن ردیف‌ها یا جداول، وضعیت پایگاه داده را در یک نقطه زمانی خاص مشاهده کند. این بدان معناست که نسخه‌های قدیمی ردیف‌ها بلافاصله حذف نمی‌شوند، بلکه به‌عنوان dead tuples علامت‌گذاری می‌شوند و برای تراکنش‌های جدید غیرقابل مشاهده می‌گردند.
برای بازیابی فضای اشغال‌شده توسط dead tuples، PostgreSQL فرآیندی به نام vacuum را اجرا می‌کند. Vacuum این dead tuples را از جداول و شاخص‌ها حذف می‌کند و فضای آزادشده را برای استفاده مجدد آماده می‌نماید. Vacuum همچنین نقشه دید (visibility map) را به‌روزرسانی می‌کند که نشان می‌دهد کدام صفحات یک جدول شامل ردیف‌های قابل مشاهده هستند. این به تسریع index-only scans کمک می‌کند، زیرا اگر نقشه دید نشان دهد که نیازی به خواندن صفحات جدول نیست، از خواندن آنها جلوگیری می‌شود.
PostgreSQL دارای قابلیتی به نام autovacuum است که به‌صورت خودکار در پس‌زمینه اجرا می‌شود و بر اساس نیاز جداولی که به vacuum نیاز دارند، عمل می‌کند. Autovacuum بر اساس عواملی مانند تعداد dead tuples، سن شناسه تراکنش و اندازه جدول فعال می‌شود. علاوه بر vacuum، autovacuum فرآیند analyze را نیز انجام می‌دهد که آمار جداول را برای به‌روزرسانی برنامه‌ریز پرس‌وجو و انتخاب بهترین طرح اجرا به‌روز می‌کند.
با این حال، در برخی موارد ممکن است autovacuum به‌موقع اجرا نشود، به‌ویژه در جداولی که نرخ به‌روزرسانی یا حذف بالایی دارند. این امر می‌تواند منجر به تجمع بیش از حد table fragmentation و عملکرد ضعیف پرس‌وجوها شود. در این مقاله، نحوه کنترل فرکانس اجرای autovacuum برای جداول خاص را با استفاده از پارامترهای ذخیره‌سازی بررسی خواهیم کرد.
پارامترهای ذخیره‌سازی
Storage parameters تنظیماتی هستند که می‌توان برای هر جدول یا شاخص تعیین کرد تا ویژگی‌های فیزیکی ذخیره‌سازی آنها را کنترل کرد. برخی از این پارامترها مستقیماً بر نحوه اجرای autovacuum تأثیر می‌گذارند. دو پارامتری که در اینجا به آنها می‌پردازیم عبارتند از:
autovacuum_vacuum_threshold: حداقل تعداد dead tuples در یک جدول قبل از اینکه autovacuum دستور VACUUM را اجرا کند. مقدار پیش‌فرض آن ۵۰ است.
autovacuum_vacuum_scale_factor: کسری از تعداد کل ردیف‌های یک جدول که به autovacuum_vacuum_threshold اضافه می‌شود تا تصمیم‌گیری شود که آیا autovacuum باید دستور VACUUM را اجرا کند یا خیر. مقدار پیش‌فرض ۰٫۲ است.
فرمول اجرای autovacuum به این صورت است:
(تعداد dead tuples) > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * (تعداد کل ردیف‌ها)
به‌عنوان مثال، اگر یک جدول ۱۰۰۰۰۰ ردیف و ۲۰۰۰۰ dead tuples داشته باشد، autovacuum زمانی اجرا می‌شود که:
۲۰۰۰۰ > 50 + 0.2 * 100000
که ساده می‌شود به:
۲۰۰۰۰ > 20050
ازآنجاکه این شرط نادرست است، autovacuum اجرا نخواهد شد.
اما اگر پارامترهای ذخیره‌سازی این جدول را به این شکل تغییر دهیم:
autovacuum_vacuum_threshold = 1000
autovacuum_vacuum_scale_factor = 0
حال autovacuum زمانی اجرا می‌شود که:
۲۰۰۰۰ > 1000 + 0 * 100000
که ساده می‌شود به:
۲۰۰۰۰ > 1000
و ازآنجاکه این شرط درست است، autovacuum اجرا خواهد شد.
با تنظیم این پارامترها، می‌توانیم بر اساس نیاز خود رفتار autovacuum را تهاجمی‌تر یا محافظه‌کارانه‌تر کنیم.
مثال
برای مثال، از جدول pgbench_accounts از ابزار pgbench استفاده می‌کنیم. این جدول شامل ۱۰۰۰۰۰ ردیف است و به‌طور مرتب توسط تراکنش‌ها به‌روزرسانی می‌شود.
می‌توانیم پارامترهای ذخیره‌سازی فعلی این جدول را با دستور \d+ در psql بررسی کنیم:
postgres=# \d+ pgbench_accounts
نتیجه:
Table “public.pgbench_accounts”
Column | Type | Collation | Nullable | Default | Storage | Description
———+————+———–+———-+———+———-+————-
aid | integer | | not null | | plain |
bid | integer | | | | plain |
abalance| integer | | | | plain |
filler | char(84) | | | | extended |
Indexes:
“pgbench_accounts_pkey” PRIMARY KEY, btree (aid)
“pgbench_accounts_bid_index” btree (bid)
Options: autovacuum_vacuum_threshold=1000, autovacuum_vacuum_scale_factor=0
این جدول دارای پارامترهای ذخیره‌سازی است که مدنظر ما است. اگر بخواهیم آنها را تغییر دهیم، می‌توانیم از دستور ALTER TABLE استفاده کنیم:
postgres=# ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_threshold = 1000);
ALTER TABLE
postgres=# ALTER TABLE pgbench_accounts SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE
حال، چندین ردیف از این جدول را به‌روزرسانی می‌کنیم تا dead tuples ایجاد کنیم:
postgres=# update pgbench_accounts set abalance = 1 where aid < 1002;
UPDATE 1001
می‌توانیم تعداد dead tuples و آخرین زمان اجرای autovacuum را از نمای pg_stat_all_tables بررسی کنیم:
postgres=# SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_all_tables WHERE relname = ‘pgbench_accounts’;
relname | n_dead_tup | last_autovacuum
——————+————+——————————-
pgbench_accounts | ۱۰۰۱ | ۲۰۲۳-۰۷-۰۱ ۰۰:۳۲:۲۳٫۶۲۷۹۳۱+۰۹
پس از چند دقیقه، می‌بینیم که autovacuum اجرا می‌شود:
postgres=# SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_all_tables WHERE relname = ‘pgbench_accounts’;
relname | n_dead_tup | last_autovacuum
——————+————+——————————-
pgbench_accounts | ۰ | ۲۰۲۳-۰۷-۰۱ ۰۰:۴۳:۲۴٫۴۹۸۵۹۸+۰۹
همان‌طور که مشاهده می‌شود، تعداد dead tuples به صفر رسیده و autovacuum به‌موقع اجرا شده است. این نشان می‌دهد که تنظیمات autovacuum به‌درستی عمل کرده است.
