Если таблиц стала занимать много места на диске, первым дело стоит проверить работу процесса autovacuum.
Проверить обрабатывал ли данную таблицу autovacuum:
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM
pg_stat_all_tables ORDER BY n_dead_tup/ (n_live_tup * current_setting('autovacuum_vacuum_scale_factor')::float8 + current_setting('autovacuum_vacuum_threshold')::float8);
Если таблицы в списке нет, и поля n_dead_tup, last_autovacuum пустые то необходимо проверить работу сбора статистики : https://www.cybertec-postgresql.com/en/stale-statistics-cause-table-bloat/
Если autovacuum «приходил», колонка last_autovacuum не пустая, проверить логи сервера БД.
В логах можно встретить такую запись:
LOG: automatic vacuum of table "my_db.big_table": index scans: 0
pages: 0 removed, 5 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 0 removed, 87 remain, 84 are dead but not yet removable, oldest xmin: 2032996903
buffer usage: 9363 hits, 9236 misses, 0 dirtied
avg read rate: 499.531 MB/s, avg write rate: 0.000 MB/s
system usage: CPU: user: 0.01 s, system: 0.02 s, elapsed: 0.14 s
Данную запись можно перевести как: «Процесс auto vacuum не выполнил очистку таблцы my_db.big_table так как данные в ней еще используются xmin: 2032996903«.
Есть 3 причины блокирующие процесс Vacuum:
Долго выполняющиеся транзакции
Старые слоты репликации
Зависшие транзакции
Долго выполняющиеся транзакции
Для поиска процесса который заблокировал очистку выполнить запрос:
select pid, query, usename, application_name, client_addr, age(clock_timestamp(), xact_start ), state from pg_stat_activity where backend_xmin ='2032996903';
При необходимости остановить данный процесс:
pg_terminate_backend()
pg_cancel_backend ()
Старые слоты репликации
Так же блокировать процесс autovacuum могут старые слоты репликации. Такое может произойти если сервер реплики завис или сильно отстал от мастера, в этом случае слот репликации будет мешать процессу autovacuum.
Для поиска слотов репликации по их xmin, выполнить запрос:
SELECTslot_name, slot_type, database, xminFROMpg_replication_slotsORDERBYage(xmin) DESC;
Для удаления слота репликации выполнить команду:
pg_drop_replication_slot()
Зависшие транзакции
Для поиска таких транзакций выполняем:
SELECT gid, prepared, owner, database, transaction AS xmin FROM pg_prepared_xacts ORDER BY age(transaction) DESC;
Для отката такой транзакции выполняем:
Rollback prepared
Полезные запросы
Список таблиц с указанием «мертвых» и живых кортежей:
SELECT schemaname,relname ,n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_all_tables order by n_dead_tup desc;
Просмотр процесса autovacuum
select * from pg_stat_progress_vacuum;