Посмотри глазами autovacuum: пороги и раздувание

Шаги

1. 01

   Заведи таблицу с выключенным autovacuum

   sqlcopy

   CREATE TABLE av_demo (id serial PRIMARY KEY, payload text)

     WITH (autovacuum_enabled = false);

   INSERT INTO av_demo (payload)

     SELECT repeat('x', 200) FROM generate_series(1, 2000);

   Выключенный autovacuum гарантирует: мёртвые версии не исчезнут, пока мы сами не позовём VACUUM. Это нужно только для чистоты опыта.

   ✓ Таблица готова, autovacuum для неё отключён.

2. 02

   Посчитай порог срабатывания autovacuum

   Autovacuum сработал бы, когда мёртвых версий станет больше порога threshold + scale_factor × reltuples. Посчитаем его и сравним с числом мёртвых версий:

   sqlcopy

   SELECT n_dead_tup,

          50 + 0.2 * reltuples AS av_threshold

   FROM pg_stat_all_tables s

   JOIN pg_class c ON c.relname = s.relname

   WHERE s.relname = 'av_demo';

   Пока таблицу только заполнили - мёртвых версий нет, порог не взят.

   ✓ Порог посчитан — теперь видно, сколько мусора нужно для срабатывания.

3. 03

   Перешагни порог: наплоди мёртвые версии

   sqlcopy

   UPDATE av_demo SET payload = repeat('y', 200);

   SELECT dead_tuple_count FROM pgstattuple('av_demo');

   Обновили все 2000 строк - столько же версий стали мёртвыми. Это намного больше порога (около 450). Будь autovacuum включён, он бы уже встал в очередь.

   ✓ Порог перейдён — мусора больше, чем нужно для автоочистки.

4. 04

   Измерь раздувание числом

   sqlcopy

   SELECT tuple_percent, dead_tuple_percent, free_percent

   FROM pgstattuple('av_demo');

   pgstattuple сканирует таблицу и считает доли. Высокий dead_tuple_percent - то самое раздувание. На большой таблице такой полный скан дорог, там берут pgstattuple_approx.

   ✓ Раздувание измерено числом, а не на глаз.

5. 05

   Подкрути scale_factor и верни autovacuum

   Горячей таблице нужен агрессивный autovacuum. Снизим scale_factor и снова его включим:

   sqlcopy

   ALTER TABLE av_demo SET (

     autovacuum_vacuum_scale_factor = 0.01,

     autovacuum_enabled = true

   );

   SELECT reloptions FROM pg_class WHERE relname = 'av_demo';

   Теперь порог - 1% от размера вместо 20%. Так настраивают крупные и часто обновляемые таблицы индивидуально.

   ✓ scale_factor=0.01 — autovacuum будет чистить таблицу куда раньше.

6. 06

   Убери мусор руками и проверь, что файл не сжался

   sqlcopy

   SELECT pg_relation_size('av_demo') AS size_before;

   VACUUM av_demo;

   SELECT dead_tuple_count FROM pgstattuple('av_demo');

   SELECT pg_relation_size('av_demo') AS size_after;

   Обычный VACUUM убрал мёртвые версии (dead_tuple_count упал до нуля), но размер файла не уменьшился - место освободилось внутри под будущие вставки. Вернуть его ОС умеет только VACUUM FULL.

   подсказка

   Горизонт ничто не держит, поэтому VACUUM уберёт весь мусор.

   ✓ Мусор убран, но файл прежнего размера — классическое поведение VACUUM.