SSI и production-тюнинг autovacuum

Production OLTP-кластер внезапно логирует сотни ошибок “could not serialize access” в минуту на таблице orders. У тебя 15 минут до эскалации. Путь диагностики — подтверждение уровня изоляции, счётчик предикатных блокировок, подлинность конфликта — это пошаговая процедура, не угадывание.

Внутренности SSI

Postgres 9.1 ввёл SSI по статье Cahill, Röhm и Fekete (2008). Механизм:

1. Каждое чтение отслеживает «предикатные блокировки» — лёгкие, в памяти, ссылки на «я прочитал этот набор»
2. Каждая запись отслеживает rw-антизависимости — «эта транзакция записала что-то, что соответствовало моему предикату, после того как я прочитал»
3. На коммите, если транзакция часть «опасной структуры» — цикла rw-антизависимостей — SSI откатывает её с SQLSTATE 40001 could not serialize access due to read/write dependencies

Математика консервативна: SSI иногда откатывает транзакции, которые закоммитились бы безопасно (false positive корректны, но стоят throughput), но никогда не пропускает non-serializable расписание.

Ограничение памяти: предикатные блокировки ограничены max_pred_locks_per_transaction × max_connections. Под нагрузкой блокировки грубеют: row → page → relation, поднимая долю false positive.

Операционно: отслеживай частоту SQLSTATE 40001 в логах. Если превышает несколько процентов от коммитов — давление блокировок выходит за бюджет.

Диагностика SSI-кризиса

Production-тюнинг autovacuum

Дефолтные ручки слишком мягкие для любой высоконагруженной таблицы. Per-table override:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,   -- запуск на 5% мёртвых (вместо 20%)
  autovacuum_vacuum_cost_limit = 2000,      -- высокий IO-бюджет за цикл
  autovacuum_vacuum_cost_delay = 2          -- 2мс задержки (0 на NVMe)
);

Для NVMe: cost_delay = 0. Для burst-нагрузок — дополнительно VACUUM (PARALLEL 4) orders; ночью по cron.

Кластерные ручки: autovacuum_max_workers = 6–8 на больших хостах; воркеры конкурируют за слоты в max_worker_processes.

Мониторинг прогресса live:

SELECT phase, heap_blks_scanned, heap_blks_total,
       index_vacuum_count
FROM pg_stat_progress_vacuum
WHERE relid = 'orders'::regclass;

Фазы: scanning heap → vacuuming indexes → vacuuming heap → cleaning up indexes → truncating heap → performing final cleanup.

Observability: метрики, которые реально двигаются

• pg_stat_all_tables.n_dead_tup + n_live_tup — доля bloat per-table
• pg_stat_user_tables.last_autovacuum, last_autoanalyze — отставание уборки
• pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY backend_xmin — кто пинит oldest-xmin
• pg_stat_replication_slots.xmin — bloat, удерживаемый slot
• pg_stat_database.deadlocks + pg_stat_database_conflicts — контеншн
• pg_stat_database.xact_commit + xact_rollback — transaction throughput
• Log-scraping на SQLSTATE 40001 — SSI false-positive rate
• SELECT datname, age(datfrozenxid), age(datminmxid) FROM pg_database — алерты на 1B/1.2B

Расширения: pgstattuple (физическое состояние страниц), pg_buffercache (cache occupancy). Prometheus: pg_exporter, postgres_exporter.

HOT-доля и fillfactor

Мониторинг HOT: pg_stat_all_tables.n_tup_hot_upd / n_tup_upd. Выше 80% — хорошо; ниже 50% — fillfactor слишком агрессивный или неправильный набор индексов. pgstattuple выдаёт tuple_count, dead_tuple_count, free_space per-table для детального осмотра.

Tradeoff: стратегия изоляции для банка

DesignPrompt: autovacuum-политика