pgcompactor
Думаю багатьом відома особливість PostgreSQL, яка викликає ефект роздування таблиць, або table bloat. Відомо що вона проявляє себе в випадках інтенсивного оновлення даних, як при частих UPDATE так і при INSERT / DELETE операціях. В результаті такого роздмухування знижується продуктивність. Власне питання, як стиснути таблицю в постгрес? Нижче відповідь, а заодно і р ассмотрім чому це відбувається і як з цим можна боротися.
Таблиці в PostgreSQL представлені у вигляді сторінок, розміром 8Kb, в яких розміщені записи. Коли одна сторінка повністю заповнюється записами, до таблиці додається нова сторінка. При удалалені записів за допомогою DELETE або зміні за допомогою UPDATE, місце де були старі записи не може бути повторно використано відразу ж. Для цього процес очищення autovacuum, або команда VACUUM, пробігає по зміненим сторінок і позначає таке місце як вільне, після чого нові записи можуть спокійно записуватися в це місце. Якщо autovacuum не справляється, наприклад в результаті активного зміни більшої кількості даних або просто через погані налаштувань, то до таблиці будуть надмірно додаватися нові сторінки в міру надходження нових записів. І навіть після того як очищення дійде до наших віддалених записів, нові сторінки залишаться. Виходить що таблиця стає більш розрядженою в плані щільності записів. Це і називається ефектом роздування таблиць, table bloat.
Процедура очищення, autovacuum або VACUUM, може зменшити розмір таблиці прибравши повністю порожні сторінки, але тільки за умови, що вони перебувають в самому кінці таблиці. Щоб максимально зменшити таблицю в PostgreSQL є VACUUM FULL або CLUSTER, але обидва ці способи припускають установку важких і тривалих блокувань на таблицю, що далеко не завжди є підходящим рішенням.
Розглянемо одне з рішень. При оновленні записи за допомогою UPDATE, якщо в таблиці є вільне місце, то нова версія піде саме в вільне місце, без виділення нових сторінок. Перевага віддається вільного місця ближче до початку таблиці. Якщо оновлювати таблицю за допомогою т.зв. fake updates, типу some_column = some_column з останньої сторінки, в якийсь момент, все записи з останньої сторінки перейдуть в вільне місце в попередніх сторінках таблиці. Таким чином, після декількох таких операцій, останні сторінки виявляться порожніми і звичайний неблокірующій VACUUM зможе відрізати їх від таблиці, тим самим зменшивши розмір.
У підсумку, за допомогою такої техніки можна максимально стиснути таблицю, при цьому не викликаючи критичних блокувань, а значить без перешкод для інших сесій і нормальної роботи бази.
І тепер найважливіше)))) Для автоматизації цієї процедури існує утиліта pgcompactor.
Її основні характеристики:
- не вимагає ніяких залежностей крім Perl> = 5.8.8, тобто можна просто скопіювати pgcompactor на сервер і працювати з ним;
- працює через адаптери DBD :: Pg, DBD :: PgPP або навіть через стандартну утиліту psql, якщо у перших двох на сервері немає;
- обробка як окремих таблиць, так і всіх таблиць всередині схеми, бази або всього кластера;
- можливість виключення баз, схем або таблиць з обробки;
- аналіз ефекту роздування і обробка тільки тих таблиць, у яких він присутній, для більш точних розрахунків рекомендується встановити розширення pgstattuple;
- аналіз і перестроювання індексів з ефектом роздування;
- аналіз і перестроювання унікальних обмежень (unique constraints) і первинних ключів (primary keys) з ефектом роздування;
- инкрементальное використання, тобто можна зупинити процес стиснення без шкоди будь-чого;
- динамічна підстроювання під поточне навантаження бази даних, щоб не впливати на продуктивність запитів користувачів (з можливістю регулювання при запуску);
- рекомендації адміністраторам, супроводжувані готовим DDL, для перестроювання об'єктів бази, які не можуть бути перебудовані в автоматичному режимі.
Пара прикладів використання:
запуск на всьому кластері з обов'язковим перестроюванням індексів:
# Pgcompactor --all --reindex
запуск на окремій таблиці (з перестроюванням індексів):
# Pgcompactor --reindex --dbname geodata --table cities --verbose info> pgcompactor.log 2> 1
Розмір таблиці знизився з 9,2GB до 5,6GB. Сукупний розмір усіх індексів знизився з 7,5GB до 2,8GB
