Если твоя постгря много весит, на то есть две причины:
1. Сам по себе датасет большой
2. Table bloating
... table bloating — это ситуация, когда физический размер таблицы существенно превосходит размер датасета. Это происходит из-за:
2.1. Накопления dead tuples
2.2 Фрагментации таблицы, когда текущих "дырок" не хватает для записи новых данных и приходится выделять новые страницы
——
Чисткой dead tuples занимается autovacuum, а как бороться с фрагментацией — сильно интереснее. Схематично она выглядит так:
page 1: [row][free][row][free]
page 2: [free][row][row][free]
page 3: [row][free][free][row]
...
То есть когда данные по страницам расположены не плотно, а разреженно.
Наша цель — освободить место, для этого нужно "уплотнить данные", чтобы они выглядели так:
page 1: [row][row][row][row]
page 2: [row][row][row][row]
page 3: [row][row][row][row]
...
page 999: [free][free][free][free]
В таком случае autovacuum сможет физически освободить место:
The standard form of VACUUM ... will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free
——
Как этого достичь?
1. vacuum full — долгая блокировка на таблицу и перезапись ее в новый файл. Требует даунтайма
2. pg_repack — копирует таблицу без блокировки, доливает дифф, под локом подменяет таблицы. Требует х2 диска от размера таблицы, генерирует burst IO нагрузку, держит долгую транзакцию
3. pgcompacttable
pgcompacttable построен на простой и красивой идее. Вспомним, что update в postgres создает новую версию строки, а старую помечает dead
На основе этой механики и работает pgcompacttable:
1. Он берет последние страницы:
page 1: [row][free][row][free]
page 2: [free][row][row][free]
...
→ page 998: [free][row][free][row]
→ page 999: [row][row][free][row]
2. Делает у строк с этих страниц фиктивный апдейт, который текущие версии пометит dead, а новые запишет в свободные дырки в начале таблицы:
page 1: [row][row][row][row]
page 2: [row][row][row][row]
...
→ page 998: [free][dead][free][dead]
→ page 999: [dead][dead][free][dead]
3. vacuum помечает dead tuples как свободные
page 1: [row][row][row][row]
page 2: [row][row][row][row]
...
→ page 998: [free][free][free][free]
→ page 999: [free][free][free][free]
4. Постгрес может с чистой совестью освободить эти страницы, потому что они полностью пустые и находятся в конце таблицы
И так далее для следующей пачки
Такой способ работает медленно, но
• не требует даунтайма
• не требует доп места на диске
• не генерирует burst нагрузки
Пользуйтесь!
upd: в комментах сделали хорошее дополнение
• по умолчанию pgcompacttable помимо того, что описано выше, начнет пересобирать индексы у таблицы, что уже потребует значительного свободного места. Чтобы индексы не трогать, нужно запускать с флажком --no-reindex
• чтобы vacuum физически освободил место от пустых страниц в конце таблицы, ему нужна будет короткая access exclusive блокировка