Оптимізація БД та ручне управління історією змін

1. Огляд

З часом продуктивність бази даних може знижуватися через накопичення змінених або видалених записів, фрагментацію індексів та застарілу статистику. Це впливає на швидкість виконання запитів, використання дискового простору та загальну ефективність роботи системи.

Щоб підтримувати стабільну продуктивність, рекомендується періодично виконувати оптимізацію бази даних. PostgreSQL надає вбудовані механізми, такі як VACUUM, ANALYZE та REINDEX, які допомагають підтримувати ефективну роботу системи, зменшуючи зайнятий дисковий простір та покращуючи швидкість виконання запитів.

2. Основні команди оптимізації

У цьому документі ми будемо працювати з таблицею protocols як прикладом для оптимізації.

Щоб зменшити розмір таблиць, оновити статистику для оптимізатора запитів і підтримувати ефективність індексів, рекомендується виконувати такі операції:

  • VACUUM – звільняє дисковий простір, зайнятий видаленими або оновленими рядками, які більше не використовуються.

  • ANALYZE – оновлює статистику для оптимізатора запитів PostgreSQL, допомагаючи вибирати найефективніші плани виконання.

  • REINDEX – перевизначає індекси, що покращує продуктивність запитів після значних змін у таблицях.

3. Виконання оптимізаційних команд вручну

Якщо потрібно оптимізувати конкретну таблицю вручну, можна виконати такі команди:

VACUUM ANALYZE protocols;
REINDEX TABLE protocols;

Ці команди допоможуть підтримувати ефективну роботу таблиці protocols, особливо після масового видалення або оновлення записів.

4. Автоматизація оптимізації

У базі даних реєстрів налаштовано автовакуум, який автоматично виконує VACUUM і ANALYZE, не потребуючи додаткових дій з боку адміністратора. Однак у деяких випадках може знадобитися примусове виконання цих команд, зокрема, якщо:

  • Відбулося масове видалення записів.

  • Спостерігається значне зниження продуктивності при виконанні запитів.

  • Таблиця містить велику кількість оновлень (UPDATE) або видалень (DELETE).

5. Налаштування оптимізації через cron

Для автоматизації процесу можна налаштувати виконання VACUUM та ANALYZE через cron. Це потребує доступу адміністратора бази даних. Наприклад, у crontab можна додати запис:

0 3 * * * psql -d database_name -c 'VACUUM ANALYZE protocols;'

Цей запис означає, що VACUUM ANALYZE для таблиці protocols виконуватиметься щоночі о 03:00.

🔗 Детальний механізм роботи автовакууму описано в офіційній документації PostgreSQL.

6. Ручне управління історією змін

За замовчуванням система автоматично зберігає всі зміни в історичних таблицях _hst. Однак у деяких випадках може знадобитися гнучкіше управління історією змін, яке виходить за межі стандартного механізму.

Альтернативний підхід передбачає створення окремої таблиці для історичних записів та їхнє збереження за допомогою бізнес-логіки.

Основні кроки:

  1. Створіть окрему таблицю для історичних записів. Наприклад, protocols_history, яка міститиме старі версії записів.

  2. Перед оновленням або видаленням запису в protocols переміщуйте його копію в protocols_history. Це можна реалізувати через спеціальну бізнес-логіку.

  3. Використовуйте окремий бізнес-процес для керування логікою збереження історичних даних. Це дає змогу визначати, які саме зміни потрібно зберігати, як довго їх утримувати та чи потрібно додавати додаткові метадані.

Такий підхід дозволяє ефективніше керувати історичними даними, коли стандартного механізму _hst недостатньо, і потрібно більше контролю над процесом збереження історії змін.

7. Пов’язані сторінки