Ubuntu 24.04 PostgreSQL autovacuum «загадкова повільність»: як безпечно налаштувати

October 21, 2025 • February 3, 2026 • Читання: 9 хв • Views: 6

Було корисно?

autovacuum має бути тихим прибиральником. На Ubuntu 24.04 іноді він поводить себе як оркестр: стрибки латентності, сесії «idle in transaction» накопичуються, диски виглядають завантаженими, але «CPU ніби не навантажений», а ваша команда розробників клянеться, що нічого не змінювала.

Пастка — припускати, що autovacuum або «в порядку», або «він винен». Насправді це підсилювач симптомів. Він перетворює наявний bloat, погані шаблони запитів і I/O-обмеження на дуже помітний біль. Добра новина: можна зробити його передбачуваним без перетворення бази даних на науковий експеримент.

Як виглядає «загадкова повільність» у продакшені

Повільність autovacuum рідко проявляється як «vacuum триває довго». Вона проявляється як те, що все інше починає працювати повільніше.

Читальні запити, які раніше були швидкі, починають робити більше випадкових I/O. Ви бачите підвищення shared read, падіння коефіцієнта попадань у кеш, і p99 різко стрибає вниз.
Таблиці з інтенсивними записами показують heap bloat і index bloat. Робочий набір більше не поміщається в RAM. Ваш SSD починає поводитися ніби це механічний диск.
Працюючі autovacuum worker-и виглядають «активними», але пропускна здатність низька. Wait events показують I/O або блокування, а вигляд прогресу vacuum виглядає застряглим.
Іноді з’являється поведінка на кшталт «cannot vacuum … because it contains tuples still needed»: не помилка, але ознака довгих транзакцій, що тримають чистку.
І класична річ: wraparound pressure підкрадається. Раптом vacuum перестає бути опціональним і стає питанням виживання.

Чиста правда: autovacuum не повільний тому, що лінивий. Він повільний тому, що ввічливий — поки ви не змусите його інакше.

Швидкий план діагностики (робіть це спочатку)

Це послідовність «мені потрібен сигнал за 10 хвилин». Робіть у порядку. Кожен крок звужує категорію вузького місця перед тим, як чіпати будь-які налаштування.

1) Це I/O, блокування чи wraparound?

Якщо системний I/O завантажений і wait events Postgres показують очікування I/O → переважно обмеження по сховищу.
Якщо autovacuum блокується на блокуваннях або не може просунутися через довгі транзакції → переважно обмеження по транзакціях/блокуванням.
Якщо datfrozenxid має великий вік → ви в режимі запобігання wraparound. Тепер продуктивність другорядна порівняно з виживанням.

2) Знайдіть найгарячіші таблиці, а не просто «autovacuum»

autovacuum працює на рівні таблиць. Одна таблиця 200 ГБ з поганим патерном оновлень може створити ілюзію, що «вся база повільна». Знайдіть цю таблицю.

3) Перевірте, чи autovacuum недопостачений або навмисно обмежений

Workers, cost delay та cost limit визначають, наскільки інтенсивно vacuum працює. На багатьох продакшн-системах значення за замовчуванням безпечні, але занадто помірні для сучасних швидкостей запису.

4) Перевірте підозри на «pinning»

Довгі транзакції, логічні replication slots та закинуті сесії можуть перешкоджати очищенню. Якщо не виправите це, налаштування autovacuum — як купувати кращу швабру для підлоги, яка все ще затоплюється.

Жарт №1: autovacuum — єдиний працівник, який прибирає за всіма і ще отримує звинувачення в смороді.

Як autovacuum насправді витрачає час

Vacuum — це не одна задача. Це набір задач, що конкурують з вашим робочим навантаженням:

Heap scan: читання сторінок таблиці, щоб знайти мертві кортежі й позначити простір як придатний для повторного використання.
Index cleanup: видалення мертвих записів індексу (якщо цього не уникають опціями індексного vacuum і евристиками).
Freezing: позначення старих transaction ID як frozen, щоб запобігти wraparound.
Оновлення visibility map: дозволяють index-only scans, фіксуючи сторінки як all-visible/all-frozen.
ANALYZE: оновлення статистики планувальника (autovacuum часто виконує analyze одночасно).

«Загадкова повільність» часто виникає, коли робота змінюється з інкрементальної очистки на догоняючу очистку. Коли bloat накопичується, vacuum читає й записує більше сторінок, частіше торкається індексів і створює більше випадкового I/O. Це підвищує латентність для всього іншого, що збільшує тривалість транзакцій, що створює більше мертвих кортежів… і маховик розкручується.

Що робить vacuum повільним, навіть коли він «працює»

Затримка через модель витрат (cost-based delay): vacuum навмисно засинає. Це нормальна поведінка.
Насичення I/O: vacuum виконує реальні читання/записи, але сховище на межі.
Витіснення буферного кеша: vacuum витісняє «гарячі» сторінки, змушуючи прикладні читання частіше промахуватися в кеші.
Конфлікти блокувань: звичайний VACUUM не бере важких блокувань, але йому потрібен режим блокування, який може блокуватися деякими DDL та певними крайніми випадками. VACUUM FULL — інша історія і цілком зіпсує вам післяобідній час.
Довгі транзакції: vacuum не може видаляти кортежі, які ще видимі старим снапшотам.

Особливості Ubuntu 24.04, що мають значення

Ubuntu 24.04 не є по суті «гіршою» для Postgres, але це сучасна Linux-база з сучасними налаштуваннями — деякі корисні, деякі — несподівані.

Ядро та I/O стек: зазвичай це ядро 6.x. За замовчуванням планувальники I/O і поведінка NVMe зазвичай добрі, але неправильно налаштовані cgroups або «шумні сусіди» все ще можуть виснажувати Postgres.
systemd: сервіси можуть запускатися з контрольними ресурсами, які ви не налаштовували свідомо. CPUQuota/IOWeight можуть створити плутанину типу «vacuum повільний, але нічого не завантажено».
Transparent Huge Pages (THP): часто все ще увімкнені за замовчуванням на загального призначення системах. Це може викликати стрибки латентності. Це не налаштування autovacuum, але може змусити autovacuum виглядати винним.
Файлові системи та опції монтування: ext4 vs XFS vs ZFS поводяться по-різному під змішаним read/write навантаженням vacuum. autovacuum не особливий — він просто торкається багатьох сторінок.

Цікаві факти та контекст (бо історія повторюється)

Це невеликі конкретні факти, що допомагають міркувати про autovacuum без забобонів.

autovacuum став стандартом у PostgreSQL 8.1 після того, як був доповненням. До того багато систем просто розкладалися, якщо люди не запускали vacuum вручну.
MVCC — причина існування vacuum: Postgres зберігає старі версії рядків для конкурентності. Очищення відкладається за дизайном.
Vacuum зазвичай не «зменшує» таблиці. Він робить простір доступним всередині файлу; розмір файлу часто лишається тим самим, якщо не використовувати більш агресивні методи.
Wraparound не теоретичний: transaction ID — 32-бітний і рано чи пізно обгорне. Якщо не vacuum/freeze, Postgres захистить себе примусовими агресивними vacuum і врешті відмовиться від записів.
Hot updates можуть зменшити churn індексів, але лише якщо оновлені колонки не проіндексовані. Оновіть неправильну колонку — і індекси швидко заблокуются.
Visibility maps дозволяють index-only scans. Vacuum, який оновлює visibility map, може пришвидшити читання пізніше, навіть якщо зараз це коштує I/O.
Модель затримки на основі витрат існує, щоб захищати латентність, а не щоб дратувати вас. Вона з’явилася коли диски були повільніші й середовища спільного використання були поширені.
Параметри масштабування autovacuum призначені для «звичних» таблиць. Дуже великі таблиці з великим обігом часто потребують пер-табличних переопределень; значення за замовчуванням занадто лінійно масштабується для деяких робочих навантажень.
Точність ANALYZE впливає на вакуум опосередковано: погана статистика приводить до поганих планів, що створюють довші транзакції, що створюють більше мертвих кортежів і більше роботи для vacuum.

Практичні завдання: команди, виводи та рішення (12+)

Це польові перевірки, які я реально виконую. Кожне завдання включає: команду, приклад виводу, що це означає і яке рішення приймається.

Завдання 1: Підтвердити версію Postgres і структуру кластера

cr0x@server:~$ psql --version
psql (PostgreSQL) 16.3 (Ubuntu 16.3-1.pgdg24.04+1)

Значення: У вас клієнтські інструменти PG 16.x. На Ubuntu може бути декілька кластерів/версій.

Рішення: Переконайтеся, що ви налаштовуєте правильний інстанс і редагуєте правильний файл конфігурації для того кластера.

cr0x@server:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

Значення: Один кластер, PG16 main, стандартні шляхи пакування Debian/Ubuntu.

Рішення: Ви знаєте, де шукати логи і на який порт націлюватися.

Завдання 2: Чи ввімкнено autovacuum і які глобальні параметри?

cr0x@server:~$ sudo -u postgres psql -X -c "SHOW autovacuum; SHOW autovacuum_max_workers; SHOW autovacuum_naptime; SHOW autovacuum_vacuum_cost_limit; SHOW autovacuum_vacuum_cost_delay;"
 autovacuum
------------
 on
(1 row)

 autovacuum_max_workers
-----------------------
 3
(1 row)

 autovacuum_naptime
-------------------
 1min
(1 row)

 autovacuum_vacuum_cost_limit
-----------------------------
 -1
(1 row)

 autovacuum_vacuum_cost_delay
-----------------------------
 2ms
(1 row)

Значення: autovacuum увімкнено, лише 3 worker-и, перевірка кожну хвилину. Cost limit -1 означає «використовувати vacuum_cost_limit».

Рішення: Якщо у вас багато активних таблиць і bloat, 3 worker-и часто замало. Але не збільшуйте сліпо — спершу з’ясуйте, чи ви заблоковані або обмежені по I/O.

Завдання 3: Знайти активні autovacuum worker-и і на що вони чекають

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT pid, datname, relid::regclass AS table, phase, wait_event_type, wait_event, now()-xact_start AS xact_age \
FROM pg_stat_progress_vacuum v \
JOIN pg_stat_activity a USING (pid) \
ORDER BY xact_age DESC;"
 pid  | datname |        table        |   phase   | wait_event_type | wait_event | xact_age
------+--------+---------------------+-----------+-----------------+------------+----------
 8421 | appdb  | public.events       | scanning  | IO              | DataFileRead | 00:12:41
 8534 | appdb  | public.sessions     | vacuuming indexes | CPU      |            | 00:05:10
(2 rows)

Значення: Один worker чекає I/O на читання; інший витрачає CPU на vacuuming індексів.

Рішення: Якщо бачите багато очікувань IO, налаштування cost limit може лише збільшити конфлікт. Розгляньте пропускну здатність сховища і кеш спочатку.

Завдання 4: Перевірити, чи vacuum не заблокований блокуваннями

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT a.pid, a.wait_event_type, a.wait_event, a.query, l.relation::regclass AS rel, l.mode, l.granted \
FROM pg_stat_activity a \
JOIN pg_locks l ON l.pid=a.pid \
WHERE a.query ILIKE '%autovacuum%' AND NOT l.granted;"
 pid | wait_event_type |  wait_event   |                 query                 |      rel       |      mode       | granted
-----+-----------------+---------------+---------------------------------------+----------------+-----------------+---------
(0 rows)

Значення: Немає свідчень, що autovacuum worker-и зараз чекають на непричислені блокування.

Рішення: Не ганяйтеся за привидами блокувань. Перейдіть до перевірки pinning і I/O.

Завдання 5: Перевірити довгі транзакції, що можуть блокувати vacuum

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT pid, usename, application_name, state, now()-xact_start AS xact_age, wait_event_type, wait_event \
FROM pg_stat_activity \
WHERE xact_start IS NOT NULL \
ORDER BY xact_age DESC \
LIMIT 10;"
 pid  | usename | application_name |        state        | xact_age  | wait_event_type | wait_event
------+--------+------------------+---------------------+-----------+-----------------+-----------
 7712 | app    | api              | idle in transaction | 03:17:09  | Client          | ClientRead
 9120 | app    | batch            | active              | 00:42:11  |                 |
(2 rows)

Значення: Сесія «idle in transaction» тримає снапшот годинами. Vacuum не може видаляти кортежі, які ще видимі для неї.

Рішення: Виправте помилку в додатку (забутий commit/rollback). Короткостроково завершіть цю сесію, якщо це безпечно; інакше налаштування autovacuum не допоможуть.

Завдання 6: Перевірити ризик wraparound (це важливо навіть коли продуктивність «нормальна»)

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT datname, age(datfrozenxid) AS xid_age, age(datminmxid) AS mxid_age \
FROM pg_database \
ORDER BY xid_age DESC;"
  datname  | xid_age  | mxid_age
-----------+----------+----------
 appdb     | 145000000 | 1800000
 postgres  |   2300000 |  120000
 template1 |   2300000 |  120000
 template0 |   2300000 |  120000
(4 rows)

Значення: appdb має великий вік XID; саме там vacuum/freeze має встигати за програмою.

Рішення: Якщо xid_age наближається до вашого запасу безпеки autovacuum_freeze_max_age, надайте пріоритет прогресу freeze над «приємними» обмеженнями.

Завдання 7: Визначити таблиці з найбільшою кількістю мертвих кортежів і терміновістю vacuum

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT relid::regclass AS table, n_live_tup, n_dead_tup, last_autovacuum, last_vacuum, vacuum_count, autovacuum_count \
FROM pg_stat_user_tables \
ORDER BY n_dead_tup DESC \
LIMIT 15;"
        table        | n_live_tup | n_dead_tup |      last_autovacuum       | last_vacuum | vacuum_count | autovacuum_count
---------------------+------------+------------+----------------------------+-------------+--------------+------------------
 public.events       | 120000000  | 48000000   | 2025-12-30 08:10:01+00     |             |            0 |               91
 public.sessions     |  90000000  | 22000000   | 2025-12-30 08:20:14+00     |             |            0 |              143
(2 rows)

Значення: Величезні кількості мертвих кортежів; autovacuum працює часто, але не встигає. Класичний випадок провалу догоняючої очистки.

Рішення: Розгляньте пер-табличні пороги autovacuum і більш агресивні налаштування vacuum, а також виправлення запитів/додатку.

Завдання 8: Перевірити, чи таблиця «часто вакуумиться», але все ще заблокована (приблизні підказки щодо bloat)

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT schemaname, relname, n_live_tup, n_dead_tup, \
pg_size_pretty(pg_total_relation_size(relid)) AS total_size, \
pg_size_pretty(pg_relation_size(relid)) AS heap_size, \
pg_size_pretty(pg_indexes_size(relid)) AS index_size \
FROM pg_stat_user_tables \
ORDER BY pg_total_relation_size(relid) DESC \
LIMIT 10;"
 schemaname |  relname  | n_live_tup | n_dead_tup | total_size | heap_size | index_size
------------+-----------+------------+------------+------------+-----------+-----------
 public     | events    | 120000000  | 48000000   | 180 GB     | 120 GB    | 60 GB
 public     | sessions  |  90000000  | 22000000   | 110 GB     | 70 GB     | 40 GB
(2 rows)

Значення: Великий heap і великі індекси; відношення мертвих кортежів високе. Vacuum, ймовірно, створює велике I/O.

Рішення: Якщо таблиця інтенсивно оновлюється, оцініть можливість HOT-оновлень і дизайн індексів. Саме налаштування autovacuum замало.

Завдання 9: Перевірити, чи autovacuum обмежується затримками (cost delay в дії)

cr0x@server:~$ sudo -u postgres psql -X -c "SHOW vacuum_cost_limit; SHOW vacuum_cost_delay;"
 vacuum_cost_limit
-------------------
 200
(1 row)

 vacuum_cost_delay
-------------------
 0
(1 row)

Значення: Глобальний vacuum cost delay 0, але autovacuum має власну затримку (зазвичай 2ms). Ефективна поведінка залежить від параметрів autovacuum, які ви бачили раніше.

Рішення: Якщо ви I/O-обмежені, зменшення затримок може пошкодити латентність. Якщо ви відстаєте з очищенням, можливо, треба підвищити cost limit і/або обережно зменшити delay.

Завдання 10: Системна перевірка реальності I/O (чи диск лімітує?)

cr0x@server:~$ iostat -x 1 5
Linux 6.8.0-41-generic (server)  12/30/2025  _x86_64_ (16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.15    0.00    4.33   18.90    0.00   64.62

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   w_await wareq-sz  aqu-sz  %util
nvme0n1         820.0  52480.0     0.0   0.00    9.20    64.0     610.0  48800.0   12.10    80.0    18.4   99.0

Значення: NVMe зайнятий приблизно на 99% з високим await. Ви наситили сховище. «Vacuum повільний» — це, по суті, фізика.

Рішення: Ви все ще можете налаштовувати autovacuum, але більший фікс — зменшення write amplification, збільшення RAM/коефіцієнта попадань у кеш, покращення пропускної здатності сховища або розподіл I/O.

Завдання 11: Швидко перевірити опції монтування файлової системи

cr0x@server:~$ findmnt -no TARGET,SOURCE,FSTYPE,OPTIONS /var/lib/postgresql
/var/lib/postgresql /dev/mapper/vg0-pgdata ext4 rw,relatime,errors=remount-ro

Значення: ext4 з relatime. Нічого очевидно екзотичного.

Рішення: Якщо бачите мережеві файлові системи або дивні sync-опції, зупиніться і переосмисліть. Vacuum не любить повільні fsync-і.

Завдання 12: Перевірити логи autovacuum для істинного часу роботи

cr0x@server:~$ sudo -u postgres psql -X -c "SHOW log_autovacuum_min_duration;"
 log_autovacuum_min_duration
----------------------------
 -1
(1 row)

Значення: Autovacuum не логуватиме тривалості, тож ви відлагоджуєте навмання.

Рішення: Встановіть log_autovacuum_min_duration = '30s' (або навіть 0 тимчасово під час інциденту), щоб зафіксувати, що саме займає час.

cr0x@server:~$ sudo tail -n 5 /var/log/postgresql/postgresql-16-main.log
2025-12-30 08:20:14.902 UTC [8534] LOG:  automatic vacuum of table "appdb.public.sessions": index scans: 1
2025-12-30 08:20:14.902 UTC [8534] DETAIL:  pages: 0 removed, 842113 remain, 500000 skipped due to pins, 0 skipped frozen
2025-12-30 08:20:14.902 UTC [8534] DETAIL:  tuples: 0 removed, 0 remain, 0 are dead but not yet removable
2025-12-30 08:20:14.902 UTC [8534] DETAIL:  buffer usage: 21000 hits, 180000 misses, 40000 dirtied
2025-12-30 08:20:14.902 UTC [8534] DETAIL:  avg read rate: 45.0 MB/s, avg write rate: 10.0 MB/s, I/O timings: read 220000.000 ms, write 90000.000 ms

Значення: «Skipped due to pins» — димлячий пістолет: щось утримує снапшоти/кортежі. Також зверніть увагу на timings I/O — читання повільні і домінують.

Рішення: Виправте pinning спочатку (довгі транзакції, replication slots), потім розгляньте пропускну здатність I/O і налаштування cost.

Завдання 13: Перевірити replication slots (поширена причина pin)

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn \
FROM pg_replication_slots;"
   slot_name   | slot_type | active | restart_lsn | confirmed_flush_lsn
---------------+-----------+--------+-------------+---------------------
 analytics_cdc | logical   | f      | 2A/90000000 | 2A/100000000
(1 row)

Значення: Неактивний logical slot може утримувати WAL. Це не те саме, що заморожування кортежів, але може створювати дисковий тиск і подовжений час відновлення після рестарту. Часто корелює з довгими транзакціями в споживачах CDC.

Рішення: Якщо слот закинутий — видаліть його. Якщо потрібен — виправте споживача, щоб він просувався.

Завдання 14: Перевірити шторм тимчасових файлів і проблеми плану запитів (класика «vacuum винен»)

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp \
FROM pg_stat_database \
ORDER BY temp_bytes DESC;"
 datname | temp_files |  temp
---------+------------+--------
 appdb   |      18234 | 96 GB
(1 row)

Значення: Велике використання тимчасових файлів. Ваші диски зайняті, але не обов’язково через vacuum.

Рішення: Якщо використання temp збігається з періодами повільності, налаштуйте work_mem для запитів, виправте сорти/з’єднання і зменшіть шторм temp. Інакше налаштування vacuum не допоможуть.

Стратегія безпечного налаштування: що змінювати і в якому порядку

Налаштування autovacuum — це гра компромісів: пропускна здатність vs латентність, фонове обслуговування vs прикладні запити. Безпечний підхід — підвищувати спроможність з обмеженнями і цілити в найгірші таблиці, замість того щоб глобально «крутити все на максимум».

Одна перефразована ідея від Werner Vogels (CTO Amazon): Все рано чи пізно ламається; проектуйте і оперуйте так, ніби відмова — нормальна, а не виняткова.

Принцип 1: Ставтеся до «не встигає» як до структурної проблеми

Якщо мертві кортежі ростуть швидше, ніж vacuum їх прибирає, у вас одне з наступного:

Занадто мало worker-ів / надмірне обмеження
I/O-стеля (сховище, кеш або шумні сусіди)
Pinning (довгі транзакції, idle in transaction, replication/slots)
Патерн записів, що створює bloat швидше, ніж будь-який розумний vacuum може впоратись (певні UPDATE-важкі шаблони, відсутність HOT-оновлень, надмірні індекси)

Тільки перший пункт вирішує налаштування autovacuum. Інші вимагають операційних та схемних/аплікаційних змін.

Принцип 2: Віддавайте перевагу пер-табличним налаштуванням для великих або патологічних таблиць

Глобальні налаштування — грубі інструменти. Великі таблиці з високим обігом — там, де значення за замовчуванням провалюються. Параметри на рівні таблиці дозволяють бути агресивними там, де це важливо, і обережними скрізь інде.

Приклад: якщо public.events велика і постійно оновлюється, зменшіть scale factor, щоб autovacuum тригери вмикалися раніше, і, можливо, підвищте cost limit лише для цієї таблиці.

cr0x@server:~$ sudo -u postgres psql -X -c "\
ALTER TABLE public.events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 50000);"
ALTER TABLE

Значення: Vacuum запускається після ~1% змін таблиці + базовий поріг. Це раніше за значення за замовчуванням і допомагає запобігти неконтрольованому bloat.

Рішення: Використовуйте це, коли бачите «autovacuum часто запускається, але завжди запізнюється». Потрібно, щоб він запускався раніше й робив менше роботи кожен раз.

Принцип 3: Обережно збільшуйте workers; легко DOS сам себе

autovacuum_max_workers спокушає. Більше worker-ів може підвищити пропускну здатність, але він множить I/O і витіснення буферів. На швидкому сховищі з достатньою RAM це може бути вигідно. На насиченому сховищі це може перетворити повільну систему на колапс.

Базові рекомендації, з якими я комфортно працюю для багатьох продакшн-систем:

Почніть з переходу від 3 до 5 worker-ів на помірних системах, якщо у вас багато великих таблиць.
Підвищуйте далі лише якщо є докази, що сховище має запас, і wait events не домінуються I/O.
Обмежуйте їх, якщо ви ділите диски з іншими сервісами або маєте жорсткі SLO латентності.

cr0x@server:~$ sudo -u postgres psql -X -c "ALTER SYSTEM SET autovacuum_max_workers = 5;"
ALTER SYSTEM

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

Значення: Підвищено кількість worker-ів; конфіг перезавантажено.

Рішення: Слідкуйте за I/O (iostat), латентністю та прогресом vacuum протягом 30–60 хвилин. Якщо p99 різко погіршується — поверніть назад або підвищте cost delays.

Принцип 4: Використовуйте cost tuning, щоб формувати вплив, а не «прискорювати vacuum» навмання

Модель вартості vacuum обмежує, скільки I/O може зробити vacuum, змушуючи його засинати. Два головні регулятори:

autovacuum_vacuum_cost_limit: скільки роботи перед сном
autovacuum_vacuum_cost_delay: як довго спати

Якщо vacuum відстає і у вас є запас по I/O — підвищуйте cost limit і/або зменшуйте delay. Якщо vacuum викликає латентність — робіть навпаки.

Консервативний «перший крок» на сучасних SSD зазвичай такий:

Помірно підвищити autovacuum_vacuum_cost_limit (наприклад, 200 → 1000)
Залишити delay маленьким, але ненульовим (наприклад, 2ms; 0ms може бути агресивним)

cr0x@server:~$ sudo -u postgres psql -X -c "ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1000;"
ALTER SYSTEM

cr0x@server:~$ sudo -u postgres psql -X -c "ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';"
ALTER SYSTEM

cr0x@server:~$ sudo -u postgres psql -X -c "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

Значення: Autovacuum може виконати більше роботи за одиницю часу, але все ще віддавати керування періодично.

Рішення: Якщо ви вже I/O-насичені, не робіть цього глобально. Віддавайте перевагу налаштуванню на рівні таблиці або вирішіть проблему з I/O.

Принцип 5: Налаштовуйте freeze-поведінку тільки коли розумієте профіль XID age

Є два поширені режими відмови:

Занадто обережно: робота freeze не встигає, ви наближаєтесь до wraparound і vacuum стає терміновим і руйнівним.
Занадто агресивно: ви змушуєте часте важке фризування на великих таблицях і збільшуєте write amplification.

Приймайте рішення на основі вимірів age(relfrozenxid) для ваших найбільших таблиць і віку на рівні бази даних.

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT c.oid::regclass AS table, age(c.relfrozenxid) AS xid_age \
FROM pg_class c \
JOIN pg_namespace n ON n.oid=c.relnamespace \
WHERE n.nspname='public' AND c.relkind='r' \
ORDER BY xid_age DESC \
LIMIT 10;"
       table       | xid_age
-------------------+----------
 public.events      | 142000000
 public.sessions    | 120000000
(2 rows)

Значення: Ці таблиці визначають XID age. Freeze-процес має відбуватися тут.

Рішення: Розгляньте пер-табличні autovacuum_freeze_max_age або агресивніший графік vacuum для цих таблиць — після усунення pinning.

Принцип 6: Пам’ятайте про прихованого партнера: analyze

autovacuum часто також виконує analyze. Якщо статистика застаріла, плани запитів стають дивними, що змінює патерни записів і тривалість транзакцій. Це створює зворотний зв’язок для vacuum.

cr0x@server:~$ sudo -u postgres psql -X -c "\
SELECT relid::regclass AS table, last_analyze, last_autoanalyze, n_mod_since_analyze \
FROM pg_stat_user_tables \
ORDER BY n_mod_since_analyze DESC \
LIMIT 10;"
      table       | last_analyze |      last_autoanalyze      | n_mod_since_analyze
------------------+--------------+----------------------------+--------------------
 public.events     |              | 2025-12-29 22:10:01+00     |            24000000
 public.sessions   |              | 2025-12-30 00:05:44+00     |            11000000
(2 rows)

Значення: Масштабні модифікації з часу останнього analyze; статистика може відставати від обігу.

Рішення: Зменшіть analyze scale factor на рівні таблиці для таблиць з великим обігом. Це може стабілізувати плани й зменшити стрибки тривалості транзакцій.

Принцип 7: Не налаштовуйте autovacuum ізольовано (так, я це сказав)

Ось де інженерія сховища зустрічається з реальністю Postgres:

Vacuum читає багато сторінок. Якщо ваш робочий набір вже ледве поміщається в RAM, vacuum витіснить гарячі сторінки і посилить промахи.
Vacuum також записує (оновлення visibility map, freezing, очищення індексів). Це тиск на fsync і WAL.
На хмарних томах ліміти IOPS/пропускної здатності можуть зробити «випадково I/O-важке» навантаження катастрофою.

Тож: перевіряйте обмеження I/O і розмір пам’яті перед тим, як «виправляти autovacuum».

Три корпоративні міні-історії (анонімізовані)

Міні-історія 1: Інцидент, спричинений неправильною гіпотезою

Компанія вела підписну службу на завантаженому кластері Postgres. Після міграції на Ubuntu 24.04 і новішу мінорну версію Postgres вони помітили нічні стрибки латентності. Висновок дежурного був миттєвим: «Новий ОС, нове ядро, autovacuum став повільніший».

Вони підняли autovacuum_max_workers і знизили cost delay до нуля по всьому кластеру. Стрибки перетворилися на сталу плато нещастя. CPU виглядав нормально, але диски були закриті. Дашборд показував «vacuum постійно працює», що лише посилювало наратив, що vacuum — лиходій.

Потім хтось нарешті подивився в pg_stat_activity і побачив з’єднання від застарілого batch-інструмента, яке стояло idle in transaction годинами. Раніше «було нормально», бо dataset був меншим. Після росту той самий баг став pin для vacuum.

Прибивши ту сесію, vacuum одразу почав видаляти мертві кортежі замість пропуску зафіксованих сторінок. Наступна ніч була без стрибків, без агресивних налаштувань.

Неправильне припущення було не технічним — воно було соціологічним: звинувачувати недавню зміну лише тому, що вона недавня. Виправлення було нудним: знайти pin, виправити клієнта, потім переоцінити налаштування vacuum на основі реальної пропускної здатності.

Міні-історія 2: Оптимізація, що відбилася боком

Фінтех-команда мала ledger-подібну таблицю з інтенсивними оновленнями. Вони помітили bloat і вирішили «допомогти» autovacuum, встановивши дуже агресивні пороги й високий cost limit — глобально. Мислення було таким: якщо vacuum працює постійно, bloat не накопичиться.

Вони отримали тиху катастрофу. autovacuum почав перемелювати великі індекси під час піків, руйнуючи кеш. Read-орієнтовані API почали промахуватися в кеші й звертатися до сховища. Латентність зросла, таймаути почастішали, і повтори в додатку створили ще більше записів.

Дежурний намагався виправити це, додавши worker-ів. Це примножило I/O і ще більше посилило витіснення кешу. Система не була недовакуумлена; вона була недостатньо забезпечена для нового рівня фонового навантаження.

Кінцеве рішення — націленість по таблицях: агресивний vacuum тільки для «гарячої» таблиці, і тільки в визначені вікна низької активності, використовуючи ручні vacuum-скрипти для найгірших періодів bloat. Також вони оптимізували індекси, щоб покращити HOT-оновлення, що зменшило обсяг індексного vacuum.

Урок: ви можете налаштувати autovacuum так, щоб воно стало генератором навантаження. Postgres дозволяє це. Він такий ввічливий.

Міні-історія 3: Нудна, але правильна практика, що врятувала день

SaaS-провайдер використовував multi-tenant Postgres з жорсткими вимогами доступності. У них був ритуал: щотижня скрипт збирав логи тривалості vacuum, топ таблиць за мертвими кортежами, тренди XID age і знімки I/O. Нічого хитромудрого. Просто послідовність.

Одного тижня скрипт підказав, що age(datfrozenxid) швидше зростає в одній тенант-базі. Інциденту ще не було, ніяких тригерів. Просто тенденція, що виглядала неправильною.

Вони розслідували і знайшли довгоживучий replication slot для аналітики, який був вимкнений, але не видалений. Утримання WAL роздувало використання диска, контрольні точки ставали важчими, а autovacuum боровся з більшим I/O. Vacuum не «ламався»; його просто вигравали конкуренти за ресурси.

Видалили закинутий слот, тиск на WAL впав, і vacuum повернувся до передбачуваної поведінки. Зовні ніхто цього не помітив. Найкращий інцидент — той, про який ніколи не доводиться звітувати.

Так, це було нудно. Саме тому воно спрацювало.

Типові помилки: симптом → корінь → виправлення

1) Симптом: autovacuum «працює весь час», а мертві кортежі все одно ростуть

Корінь: vacuum піниться довгими транзакціями або сесіями «idle in transaction», або він постійно сканує, але пропускає сторінки через pin-и.

Виправлення: Знайдіть і усуньте довгі снапшоти. Встановіть timeouts для statement та idle-in-transaction. Підтвердіть pins через логи autovacuum («skipped due to pins») і pg_stat_activity. Поки pins не зникнуть, налаштування worker-ів не допоможуть.

2) Симптом: vacuum здається повільним; `pg_stat_progress_vacuum` фаза «scanning» триває вічно

Корінь: скан heap-у обмежений I/O на заблокованій таблиці з bloat-ом, часто з промахами кеша і насиченим сховищем.

Виправлення: Зменшіть причини bloat (патерни оновлень, індекси), забезпечте пропускну здатність сховища, розгляньте підвищення cost limit лише якщо сховище має запас, і налаштуйте пер-табличні пороги, щоб vacuum запускався раніше.

3) Симптом: стрибки латентності корелюють зі стартом autovacuum

Корінь: vacuum витісняє гарячі сторінки і збільшує випадковий I/O; забагато worker-ів або надто агресивні cost-параметри під час піку.

Виправлення: Обмежте вплив autovacuum: збільшіть cost delay, зменшіть cost limit, знизьте кількість worker-ів і застосуйте агресивність тільки пер-таблично там, де треба. Розгляньте ручні vacuum в позачасові вікна для патологічних таблиць.

4) Симптом: раптове екстрене поведінка vacuum, попередження про wraparound

Корінь: робота freeze відстала; ви близькі до autovacuum_freeze_max_age. Часто спричинено вимкненим autovacuum, pin-ами або занадто великими таблицями з дефолтними порогами.

Виправлення: Ставте це пріоритетом 0. Приберіть pin-ячі сесії. Тимчасово додайте ресурси vacuum і запустіть таргетовані ручні vacuum. Після цього налаштуйте freeze-параметри і пороги, щоб більше не підходити до краю.

5) Симптом: «vacuum викликає великий WAL і replication lag»

Корінь: інтенсивне фризування і оновлення visibility map генерують WAL; агресивні параметри vacuum підсилюють це. Також можуть страждати контрольні точки.

Виправлення: Рівномірне обслуговування: уникайте vacuum-штормів через ранні й менші vacuum-си; налаштуйте параметри checkpoint; переконайтеся, що replica має I/O, щоб наздогнати; використовуйте пер-табличні налаштування замість глобальної агресії.

6) Симптом: vacuum іноді блокується блокуваннями

Корінь: одночасні DDL-операції або операції, що потребують сильніших блокувань (VACUUM FULL, REINDEX без CONCURRENTLY) втручаються.

Виправлення: Не робіть важких DDL в пікові години. Використовуйте concurrent-варіанти де можливо. Залиште VACUUM FULL для планового вікна обслуговування і лише коли це дійсно необхідно.

Жарт №2: VACUUM FULL — як переїзд, щоб знайти пульт від телевізора — ефективно, але весь ваш вікенд зникне.

Чеклісти / покроковий план

Покроково: від інциденту до стабільного налаштування

Перевірка безпеки freeze: запитати age(datfrozenxid) і топ таблиць за age(relfrozenxid). Якщо ви близькі до порогів wraparound — припиніть оптимізації і почніть запобігання простою.
Перевірка pin-ів: знайдіть довгі транзакції і «idle in transaction». Виправте або завершіть порушників і встановіть таймаути запобігання.
Видимість autovacuum: увімкніть log_autovacuum_min_duration (наприклад, 30s), щоб бачити «skipped due to pins», використання буферів і timings I/O.
Топ порушників: перелічіть таблиці за мертвими кортежами і за загальним розміром. Налаштування повинні бути націлені на топ 3–5 таблиць, а не весь кластер.
Запас сховища: перевірте iostat -x, чи є у вас місце по I/O. Якщо ви на стелі, підвищення worker-ів/cost, ймовірно, погіршить латентність.
Пер-табличні пороги: зменшіть autovacuum_vacuum_scale_factor на великі таблиці з високим обігом, щоб vacuum запускався раніше і робив менше роботи кожен раз.
Worker-и: помірно підвищуйте autovacuum_max_workers, якщо багато таблиць і є запас ресурсів.
Налаштування cost: відрегулюйте cost limit/delay, щоб збалансувати пропускну здатність і латентність. Краще робити пер-таблично, коли можливо.
Аналіз: для таблиць з великим обігом зменшіть analyze scale factor. Це запобіжить регресу планів, що ускладнює життя vacuum.
Валідація: порівняйте до/після: тренди мертвих кортежів, тривалості vacuum, p95/p99 латентність, коефіцієнт попадань у кеш, I/O await.
Охоронні заходи: встановіть таймаути (idle in transaction, statement timeout де доречно) і операційні правила для DDL.
Перегляньте схему: зменшіть непотрібні індекси, уникайте оновлення проіндексованих колонок, і розгляньте партиціювання для великих append/update таблиць.

Операційний чекліст: набір «не будіть мене знову»

Логування тривалості autovacuum увімкнене на розумному порозі.
Дашборди для: мертвих кортежів по топ таблицях, XID age, прогрес vacuum, I/O utilization, temp bytes, replication slot lag/LSN-и.
Idle-in-transaction timeout встановлений (де безпечно).
Runbook для завершення очевидних порушників (з правилами ескалації).
Документовані пер-табличні політики autovacuum для топ таблиць з великим обігом.
Політика DDL: жодного VACUUM FULL у робочі години; перевага — concurrent-операції.

FAQ

1) Чи варто просто вимкнути autovacuum і запускати ручні VACUUM вночі?

Ні. Це шлях до bloat, поганої статистики і зрештою wraparound pressure. Використовуйте ручний vacuum як таргетований інструмент, а не як основну стратегію.

2) Чому vacuum іноді «skip due to pins»?

Тому що якась транзакція ще потребує видимості старих версій рядків. Типові причини: довгі запити, «idle in transaction», або певні replication/CDC патерни. Виправте pin — vacuum не може обійти правила MVCC.

3) Чи безпечно завжди збільшувати `autovacuum_max_workers`?

Воно безпечне в тому сенсі, що Postgres не вибухне миттєво, але це цілком може погіршити латентність, наситивши сховище і перемелюючи кеш. Збільшуйте worker-ів тільки після перевірки запасу I/O й усунення pin-ів.

4) Яка найбільш безпечна перша зміна для налаштування?

Увімкнути логування тривалості autovacuum (log_autovacuum_min_duration) і налаштувати пер-табличні scale factor-и для найбільших таблиць з великим обігом, щоб vacuum запускався раніше. Це покращує видимість і зменшує «vacuum-шторм».

5) Чому autovacuum повільний на SSD? SSD ж швидкі.

SSD швидкі, але не безмежні. Vacuum може генерувати змішане випадкове читання і запис плюс WAL. Якщо ваше навантаження вже використовує більшість IOPS/пропускної здатності, vacuum конкурує за той самий бюджет. Також витіснення кеша може зробити «швидке сховище» неважливим, якщо все промахується в RAM.

6) Чи допоможе `VACUUM (ANALYZE)` з загадковою повільністю?

Інколи. Якщо проблема — застаріла статистика, що призводить до поганих планів, то так. Якщо проблема в pin-ах або насиченні I/O — то ні. Але після виправлення основної проблеми ANALYZE може зменшити волатильність планів.

7) Чому індекси роздуваються, навіть коли таблиця виглядає нормально?

Оновлення проіндексованих колонок створюють мертві записи індексів. Навіть з HOT-оновленнями, якщо оновлені колонки індексовані (або рядок не вміщується на тій же сторінці), HOT не застосовується і індекси швидко блояться. Виправлення: зменшити непотрібні індекси і уникати оновлень індексованих колонок.

8) Як зрозуміти, чи autovacuum спричиняє латентність, або просто корелює?

Дивіться wait events і системні метрики I/O під час стрибка. Якщо прикладні запити чекають I/O і vacuum споживає велику частку читань/записів — ймовірно, це причиново. Якщо ж temp bytes, сорти або батчова робота збігаються зі стрибками, vacuum може бути лише на місці події.

9) Чи можу я налаштувати autovacuum по-різному для різних таблиць?

Так, і для великих таблиць з великим обігом це рекомендовано. Використовуйте параметри таблиці як autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor та пер-табличні cost-налаштування за потреби.

10) Що, якщо я на керованій платформі чи в контейнері з обмеженнями cgroups?

Тоді потрібно перевірити квоти CPU і I/O. Vacuum може бути обмежений платформою, навіть якщо VM виглядає «вільною». Перевірте systemd і cgroup-налаштування і узгодьте їх з вашими потребами обслуговування.

Висновок: кроки, які можна відправити цього тижня

Якщо autovacuum «загадково» повільний на Ubuntu 24.04, припускайте, що він каже вам правду про одне з трьох: вас pin-ять, ви обмежені I/O, або вас недообладнано/обмежено квотами.

Увімкніть видимість: встановіть log_autovacuum_min_duration на корисне значення і почніть читати, що vacuum каже про pin-и і timings I/O.
Припиніть pin-и у джерелі: усуньте «idle in transaction», виправте довгі снапшоти і приберіть закинуті replication slots.
Цільте найгірші таблиці: зменшіть scale factor-и vacuum/analyze на величезних таблицях з високим обігом, щоб vacuum працював раніше і менше.
Тільки потім регулюйте worker-ів і cost-параметри, маленькими кроками, спостерігаючи I/O await і p99 латентність.
Зробіть це нудним: відстежуйте тренди мертвих кортежів, XID age і тривалості autovacuum. Нудно = стабільно. Стабільно = швидко.

Схожі статті

Міграція з MariaDB до PostgreSQL: без простоїв і без сюрпризів