«idle in transaction» در PostgreSQL: قاتل خاموش autovacuum

اگر یک کلاستر PostgreSQL را مدتی در پروداکشن رها کنید، دیر یا زود با یکی از این سه نشانه روبه‌رو می‌شوید: خطای FATAL: sorry, too many clients already هنگام بالا آمدن اپلیکیشن، هشدار WARNING: oldest xmin is far in the past در لاگ، و دیسکی که بدون هیچ رشد واقعی در حجم داده مدام پرتر می‌شود. در بیشتر موارد مقصر یک چیز است: نشست‌هایی که در وضعیت idle in transaction گیر کرده‌اند.

شرح مسئله: چه اتفاقی می‌افتد و چه نشانه‌هایی دارد

سناریوی کلاسیک این است: اپلیکیشن زیر بار ترافیک بالا نمی‌آید و pool اتصالات خالی نمی‌شود:

FATAL:  sorry, too many clients already

همزمان در لاگ خود PostgreSQL این خطوط تکرار می‌شوند:

WARNING:  oldest xmin is far in the past
HINT:  Close open transactions soon to avoid wraparound problems.
WARNING:  database "appdb" must be vacuumed within 1499321 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.

و کوئری زیر معمولاً همه چیز را روشن می‌کند؛ تراکنش‌هایی که ساعت‌ها باز مانده‌اند و هیچ کاری هم نمی‌کنند:

SELECT pid,
       usename,
       application_name,
       client_addr,
       state,
       now() - xact_start   AS xact_age,
       now() - state_change AS state_age,
       left(query, 80)      AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY xact_start
LIMIT 20;

  pid  | usename | application_name |        state        |  xact_age
-------+---------+------------------+---------------------+------------
 48213 | app     | api-gateway      | idle in transaction | 03:41:22
 48290 | app     | api-gateway      | idle in transaction | 02:58:10
 48877 | report  | metabase         | idle in transaction | 01:12:44

حالت idle in transaction (aborted) هم یعنی یک دستور داخل تراکنش خطا داده، اما اپلیکیشن نه ROLLBACK زده و نه COMMIT؛ اتصال همان‌جا معلق مانده است.

علت ریشه‌ای: چرا باز ماندن یک تراکنش این‌قدر مخرب است

PostgreSQL از MVCC استفاده می‌کند. هر تراکنش یک snapshot از دیتابیس می‌گیرد و تا وقتی آن تراکنش تمام نشده، هیچ tuple ای که «نسخه‌ی قدیمی‌تر از snapshot آن» است قابل حذف نیست. به این مرز، xmin horizon می‌گویند.

وقتی یک نشست با BEGIN باز می‌ماند و بعدش هیچ اتفاقی نمی‌افتد، آن نشست یک snapshot منقضی‌نشده نگه می‌دارد. نتیجه این است که autovacuum روی جدول‌های داغ اجرا می‌شود، همه‌ی صفحات را می‌خواند، و به tuple های مرده می‌رسد که هنوز «مرگ‌شان برای همه تأیید نشده» — پس آن‌ها را پس نمی‌گیرد. جدول و ایندکس‌هایش باد می‌کنند، صفحات بیشتری در buffer cache مصرف می‌شود و کوئری‌ها کندتر می‌شوند. بدتر از آن، datfrozenxid بالا می‌رود و اگر این وضعیت ادامه پیدا کند، در نهایت نوشتن روی دیتابیس متوقف می‌شود تا از wraparound جلوگیری شود.

سه اثر جانبی دیگر هم وجود دارد:

• اشغال اتصال: هر تراکنش باز یک کانکشن از pool را قفل می‌کند. با pool_size = 20 و بیست تراکنش رهاشده، تمام درخواست‌های جدید در صف می‌مانند و خطای too many clients ظاهر می‌شود.
• نگه‌داشتن lock: تمام قفل‌هایی که تراکنش گرفته (حتی قفل‌های ACCESS SHARE روی جدول‌ها) تا پایان تراکنش آزاد نمی‌شوند و ALTER TABLE یا DROP را بلاک می‌کنند.
• تشدید در replica: اگر hot_standby_feedback روشن باشد، همین xmin به standby هم تحمیل می‌شود و آنجا هم vacuum عقب می‌افتد.

ریشه‌ی اصلی تقریباً همیشه سمت اپلیکیشن است: ORM با autocommit=false که session را بدون commit() یا rollback() رها می‌کند، نبود بلوک try/finally، یا انجام یک کار کند (کال به API بیرونی، ارسال ایمیل، تولید گزارش سنگین) داخل تراکنش دیتابیس.

راه‌حل گام‌به‌گام

گام ۱: بفهمید کدام تراکنش xmin را نگه داشته است

SELECT pid,
       state,
       now() - xact_start       AS xact_age,
       age(backend_xmin)        AS xmin_age,
       usename,
       application_name,
       left(query, 60)          AS last_query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC
LIMIT 10;

-- چه کسی چه کسی را بلاک کرده؟
SELECT pid,
       pg_blocking_pids(pid) AS blockers,
       state,
       now() - xact_start    AS xact_age,
       left(query, 60)       AS last_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

ستون xmin_age همان مقداری است که autovacuum را متوقف می‌کند. اگر این عدد در حد ساعت است، مسئله را پیدا کرده‌اید.

گام ۲: شدت بلoat را اندازه بگیرید

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,
       last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- اندازه‌گیری دقیق روی یک جدول مشخص
SELECT * FROM pgstattuple('public.orders');

خروجی pgstattuple شامل dead_tuple_percent و free_percent است. اگر این دو مجموعاً بالای ۳۰٪ باشند، جدول واقعاً باد کرده و reclaim فیزیکی لازم است.

گام ۳: به تراکنش‌های رهاشده پایان دهید

برای کوئری‌های در حال اجرا اول pg_cancel_backend، و برای نشست‌های بی‌کار بازمانده pg_terminate_backend:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - state_change > interval '10 minutes'
  AND pid != pg_backend_pid();

حواس‌تان باشد که این کار تراکنش را rollback می‌کند؛ پس مطمئن شوید که آن تراکنش در میانه‌ی یک عملیات حساس نیست.

گام ۴: تور محافظ را پهن کنید

مهم‌ترین تنظیم همین است. از PostgreSQL 9.6 به بعد، idle_in_transaction_session_timeout نشستی را که تراکنش باز دارد ولی بی‌کار است بعد از مهلت مشخص می‌کشد:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
ALTER SYSTEM SET statement_timeout = '30s';
ALTER SYSTEM SET lock_timeout = '5s';

-- فقط در PostgreSQL 14 و بالاتر:
ALTER SYSTEM SET idle_session_timeout = '30min';

SELECT pg_reload_conf();

اگر نمی‌خواهید این محدودیت برای همه اعمال شود، آن را روی نقش یا دیتابیس خاص بگذارید:

ALTER ROLE app_user IN DATABASE appdb
  SET idle_in_transaction_session_timeout = '60s';

ALTER DATABASE reporting
  SET idle_in_transaction_session_timeout = '30min';

برای کاربر مانیتورینگ هم نقش pg_monitor را بدهید تا بتواند بدون superuser همه‌ی این view ها را بخواند:

GRANT pg_monitor TO monitoring_user;

گام ۵: سمت اپلیکیشن و pooler را درست کنید

سمت اپلیکیشن، قاعده ساده است: هیچ تراکنشی نباید باز بماند. در SQLAlchemy این یعنی هر عملیات در بلوک مدیریت‌شده انجام شود و pool هم سقف داشته باشد:

from sqlalchemy import create_engine, text

engine = create_engine(
    "postgresql+psycopg2://app:secret@db:5432/appdb",
    pool_size=10,
    max_overflow=5,
    pool_pre_ping=True,
    pool_recycle=1800,
    connect_args={"options": "-c idle_in_transaction_session_timeout=60000"},
)

# تراکنش خودکار commit یا rollback می‌شود
with engine.begin() as conn:
    conn.execute(text("UPDATE accounts SET balance = balance - 100 WHERE id = :id"), {"id": 42})

اگر از PgBouncer استفاده می‌کنید، حالت transaction و مهلت‌های سرور را فعال کنید:

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
server_idle_timeout = 60
query_wait_timeout = 30

نکته مهم: در حالت transaction سرور بعد از هر تراکنش به pool برمی‌گردد، بنابراین یک اپلیکیشن باگ‌دار نمی‌تواند همه‌ی کانکشن‌ها را برای همیشه ببلعد.

گام ۶: اگر جدول قبلاً باد کرده باشد

VACUUM معمولی فضا را به سیستم‌عامل برنمی‌گرداند، فقط برای استفاده مجدد آزاد می‌کند. برای جدول‌های خیلی بادکرده:

VACUUM (VERBOSE, ANALYZE) public.orders;

اگر حجم فیزیکی هم باید کم شود، VACUUM FULL قفل ACCESS EXCLUSIVE می‌گیرد و روی پروداکشن معمولاً گزینه نیست. به‌جایش از pg_repack استفاده کنید که آنلاین کار می‌کند:

pg_repack -d appdb -t public.orders --no-superuser-check

گام ۷: autovacuum را برای جدول‌های داغ تهاجمی‌تر کنید

پیش‌فرض autovacuum_vacuum_scale_factor = 0.2 یعنی روی جدول ۱۰۰ میلیون ردیفی، تا ۲۰ میلیون ردیف مرده انباشته نشود vacuum شروع نمی‌شود. برای جدول‌های پرنوسان این را جدولی تنظیم کنید:

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

-- برای جدول‌هایی که فقط insert می‌شوند (PostgreSQL 13+)
ALTER TABLE public.events SET (
  autovacuum_vacuum_insert_threshold    = 10000,
  autovacuum_vacuum_insert_scale_factor = 0.05
);

در سطح کلاستر هم اگر I/O اجازه می‌دهد:

ALTER SYSTEM SET autovacuum_max_workers = 6;
ALTER SYSTEM SET autovacuum_naptime = '30s';
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = 0;
SELECT pg_reload_conf();

autovacuum_vacuum_cost_delay = 0 throttle را حذف می‌کند؛ روی استوریج با IOPS محدود این کار می‌تواند تأخیر کوئری‌ها را بالا ببرد، پس با احتیاط و همراه با مانیتورینگ اعمالش کنید. هرگز autovacuum = off نگذارید.

بررسی اینکه مشکل واقعاً حل شده است

بعد از اعمال تغییرات، این چهار بررسی را انجام دهید:

1. هیچ تراکنش بی‌کاری نمانده باشد:

   SELECT count(*) AS stuck
   FROM pg_stat_activity
   WHERE state IN ('idle in transaction', 'idle in transaction (aborted)');

   این عدد باید صفر بماند. اگر بعد از چند ساعت دوباره بالا رفت، یعنی باگ سمت اپلیکیشن هنوز رفع نشده و timeout فقط دارد علامت را درمان می‌کند.
2. autovacuum دوباره کار می‌کند:

   SELECT relname, n_dead_tup, last_autovacuum
   FROM pg_stat_user_tables
   WHERE relname = 'orders';
   
   SELECT pid, relid::regclass AS table_name, phase,
          heap_blks_scanned, heap_blks_total
   FROM pg_stat_progress_vacuum;

   مقدار n_dead_tup باید بعد از هر vacuum به‌طور محسوس افت کند و last_autovacuum به‌روز شود.
3. سن xmin در حال کاهش باشد:

   SELECT datname, age(datfrozenxid) AS xid_age
   FROM pg_database
   ORDER BY xid_age DESC;

   این عدد باید در طول روزهای بعد تدریجاً پایین بیاید، نه اینکه مدام رشد کند.
4. اتصال‌ها آزاد باشند:

   SELECT count(*) AS total,
          count(*) FILTER (WHERE state = 'active') AS active
   FROM pg_stat_activity;

   تعداد کل باید به‌طور پایدار زیر max_connections بماند و اپلیکیشن دیگر خطای too many clients نگیرد.

پیشگیری و بهترین روش‌ها

• کارهای کند را از تراکنش بیرون بکشید. هیچ درخواست شبکه‌ای، ارسال ایمیل یا تولید PDF داخل BEGIN ... COMMIT انجام ندهید.
• idle_in_transaction_session_timeout را همیشه روشن بگذارید — حتی روی دیتابیس‌های داخلی. این تنظیم ارزان‌ترین بیمه‌نامه‌ی ممکن است.
• statement_timeout و lock_timeout را سطح‌به‌سطح تعریف کنید. برای سرویس‌های API چند ثانیه، برای کارهای batch چند دقیقه، و برای گزارش‌های تحلیلی روی یک role جدا با مقدار بالاتر.
• از connection pooler استفاده کنید. PgBouncer در حالت transaction جلوی خیلی از این فاجعه‌ها را می‌گیرد و max_connections را هم پایین نگه می‌دارد.
• هشدار بگذارید، نه واکنش. روی تعداد نشست‌های idle in transaction با عمر بیشتر از ۵ دقیقه، روی age(datfrozenxid) بالای ۵۰۰ میلیون، و روی تعداد اتصال‌های مصرف‌شده بالای ۸۰٪.
• گزارش‌های سنگین را از OLTP جدا کنید. یک replica اختصاصی برای کوئری‌های طولانی، هم مسئله‌ی xmin و هم مسئله‌ی IOPS را حل می‌کند.
• هرگز autovacuum را خاموش نکنید. اگر بار آن زیاد است، آن را تنظیم کنید؛ خاموش کردنش فقط بدهی را به آینده منتقل می‌کند.

قاعده سرانگشتی: هر تراکنشی که بیشتر از چند ثانیه باز بماند، در حال مصرف منابعی است که هیچ‌کس نمی‌بیند — تا روزی که دیسک پر شود یا دیتابیس از پذیرفتن نوشتن خودداری کند.