Про bloat в pg и как с ним бороться

При обновлениях и удалениях строк postgres физически не удаляет/изменяет старую версию строки, а просто создает новую. У каждой такой версии есть поля:

1. xmin — номер транзакции, который создал версию строки
2. xmax — номер транзакции, который удалил версию строки

Чтобы проникнуться идеей xmin/xmax, можно почитать пост, как это позволяет обеспечить snapshot isolation

---

Окей, мы пообновляли строку, у нас появилось несколько версий строки со своими xmin/xmax. Интуитивно кажется, что нам не нужны все эти версии, ведь мы хотим видить только последнее, актуальное состояние строки. Так и есть — если xmax != 0 (версия строки кем-то удалена) и xmax < минимальный xid, среди активных транзакций (версия строки не видна ни для одной живой транзакции), то эта версия больше не нужна и ее можно удалить. Такие версии строк называются dead tuples

"Удалением" dead tuples занимается autovacuum: он помечает, что фрагменты страниц, где раньше лежали dead tuples, можно переиспользовать для записи новых данных. Важно отметить, что автовакуум никак не "двигает" живые данные и не освобождает физическое место. Он просто говорит, что в текущих страницах есть вот такие дырки, куда теперь можно что-то записать

К слову, минимальный xid, среди активных транзакций называется горизонтом базы. То есть автовакуум может удалять только те версии строк, которые "старше" горизонта базы. Это еще один аргумент, почему долгие транзакции — зло: из-за них автовакуум встает, так как такие транзакции долго держат горизонт

---

Итого, у нас есть набор страниц, куда записываются версии строк, потом они "удаляются" автовакуумом, и на эти места записываются новые данные. Казалось бы, если размер датасета не растет, то и физическое занимаемое место не должно расти. Но это не совсем правда

Несмотря на то, что у нас есть "дырки" в страницах, куда можно записать новые данные, этого не всегда хватает. Например, "дырка" может быть слишком маленькой, чтобы туда записать версию строки. Либо таких дырок в моменте может быть недостаточно (например, при массовых апдейтах/удалениях) — все это приводит к тому, что постгрес вынужден аллоцировать новые страницы => растет физический размер таблицы

---

Суммаризируя, table bloating — это ситуация, когда физический размер таблицы существенно превосходит размер датасета. Это происходит из-за:

1. Накопления dead tuples
2. Фрагментации таблицы, когда текущих "дырок" не хватает для записи новых данных и приходится выделять новые страницы

Для борьбы с фрагментацией у постгреса есть vacuum full — он берет эксклюзивную блокировку на таблицу и полностью ее перезаписывает в новый файл "без дырок". Однако на практике он редко применим, поскольку он буквально вызывает даунтайм сервиса (возможно на несколько часов, если таблица большая)

Для борьбы с фрагментацией без даунтайма есть утилита pg_repack

👍 — если нужен пост про принцип работы pg_repack