Архівування старих даних БД: стратегія та реалізація

Архівування даних підвищує продуктивність бази даних за рахунок винесення старих даних БД в архівне сховище. Таблиця events з 500 мільйонами рядків та зростанням на 2 мільйони на добу — це проблема, яка стає гострішою щодня. SELECT гальмує, VACUUM не встигає, індекси займають гігабайти. Без архівування база розпухає, блокування заважають роботі, а вартість зберігання на SSD б'є по бюджету. Ми вирішуємо такі завдання під ключ — реалізуємо систему архівування, яка відокремлює гарячі дані від холодних без простоїв та втрати продуктивності. Наш досвід показує, що правильна стратегія архівування знижує навантаження на базу даних на 70% та скорочує вартість зберігання в 2–3 рази. Економія на зберіганні після архівування складає в середньому $400 на місяць для проєктів з 1 ТБ даних.

Які проблеми вирішує архівування старих даних?

Основний біль — деградація продуктивності. Коли таблиця розростається, навіть прості SELECT з індексом гальмують через глибину B-tree та фрагментацію. VACUUM не встигає чистити мертві рядки, і autovacuum відстає. Зростання витрат на зберігання — дороге SSD-сховище для даних, до яких звертаються раз на рік. Блокування при масовому видаленні — DELETE без батчів блокує таблицю на хвилини. Ми вирішуємо це через пакетне архівування з SKIP LOCKED — паралельні воркери не конфліктують.

Після архівування навантаження на диск знижується на 70%, час SELECT зменшується в 3–5 разів, а розмір бази скорочується в 2 рази.

Стратегії архівування: порівняння методів

Partition detach — якщо таблиця партиціонована, старі партиції від'єднуються та переносяться до архівної бази або tablespace. Це найшвидший підхід: операція метаданих, без переміщення рядків. Partition detach швидший за пакетний INSERT+DELETE у 50 разів для таблиць з мільярдами рядків. Пакетний INSERT+DELETE з SKIP LOCKED кращий за звичайний DELETE в 10 разів за швидкістю без блокувань.

INSERT + DELETE пакетами — для непартиціонованих таблиць. Копіюємо рядки до архівної таблиці пакетами, видаляємо з основної. Не створює довгих транзакцій та дозволяє контролювати навантаження.

Логічна реплікація — налаштовуємо publication на основній базі, subscription на архівній, з фільтром за датою. Архів оновлюється в реальному часі — підходить для аудиту.

Dump + truncate — експорт у CSV/parquet, видалення з бази. Дані більше не в PostgreSQL/MySQL — лише у файловому архіві. Найдешевший варіант зберігання.

Як обрати підходящий метод?

Як ми реалізуємо архівування: покрокова інструкція

Крок 1: Аналіз структури та навантаження

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

Крок 2: Вибір стратегії

За таблицею вище визначаємо оптимальний метод. Для більшості проєктів підходить пакетне копіювання з SKIP LOCKED.

Крок 3: Написання функції з SKIP LOCKED

Використовуємо батчі по 10 000 рядків з паузою 0.1 с. Функція archive_old_events переносить рядки з public.events до archive.events:

-- Архівна таблиця (може бути в окремій схемі або базі) CREATE TABLE archive.events ( LIKE public.events INCLUDING ALL ); -- Функція архівування з батчами CREATE OR REPLACE FUNCTION archive_old_events( p_before_date TIMESTAMPTZ, p_batch_size INTEGER DEFAULT 10000 ) RETURNS TABLE(batches_processed INTEGER, rows_archived BIGINT) LANGUAGE plpgsql AS $$ DECLARE v_batches INTEGER := 0; v_total BIGINT := 0; v_moved INTEGER; BEGIN LOOP -- Переносимо один батч до архіву WITH moved AS ( DELETE FROM public.events WHERE id IN ( SELECT id FROM public.events WHERE created_at < p_before_date LIMIT p_batch_size FOR UPDATE SKIP LOCKED -- пропускаємо заблоковані рядки ) RETURNING * ) INSERT INTO archive.events SELECT * FROM moved; GET DIAGNOSTICS v_moved = ROW_COUNT; EXIT WHEN v_moved = 0; v_batches := v_batches + 1; v_total := v_total + v_moved; -- Пауза між батчами — не перевантажуємо диск PERFORM pg_sleep(0.1); -- Прогрес RAISE NOTICE 'Batch %: % rows archived (total: %)', v_batches, v_moved, v_total; END LOOP; RETURN QUERY SELECT v_batches, v_total; END $$; 

Запуск:

SELECT * FROM archive_old_events('давня_дата'::timestamptz, 10000); 

Крок 4: Налаштування планувальника

Artisan-команда запускається щомісячно о 2:00:

// app/Console/Commands/ArchiveOldData.php class ArchiveOldData extends Command { protected $signature = 'db:archive {--days=365 : Архівувати дані старші за N днів}'; protected $description = 'Archive old records to archive tables'; public function handle(): int { $beforeDate = now()->subDays($this->option('days'))->toDateTimeString(); $this->info("Archiving events before {$beforeDate}..."); $result = DB::selectOne( 'SELECT * FROM archive_old_events(?::timestamptz, 5000)', [$beforeDate] ); $this->info("Done: {$result->batches_processed} batches, {$result->rows_archived} rows"); // VACUUM після масового видалення DB::statement('VACUUM ANALYZE events'); return self::SUCCESS; } } 

// app/Console/Kernel.php $schedule->command('db:archive --days=180') ->monthlyOn(1, '02:00') ->withoutOverlapping() ->onFailure(fn() => Notification::route('telegram', config('services.telegram.ops_chat')) ->notify(new ArchivingFailedNotification())); 

Крок 5: Моніторинг та VACUUM

Після архівації виконуємо VACUUM ANALYZE. Налаштовуємо алерти на помилки через Telegram.

Крок 6: Відновлення з архіву

З архівної таблиці — ATTACH PARTITION до основної без копіювання. З CSV-файлів — COPY-завантаження.

Відновлення даних з архіву

З архівної таблиці — ATTACH PARTITION до основної таблиці без копіювання даних. З CSV-файлів — COPY-завантаження:

# Відновити дані з CSV-архіву назад до бази gunzip -c /mnt/archive/events/2024-01/events_2024-01.csv.gz | \ psql -d mydb -c "COPY events FROM STDIN CSV HEADER" 

Для довгострокового зберігання використовуємо файловий архів з ротацією.

Що входить в роботу

• Аналіз структури таблиць та навантаження
• Проектування схеми архіву (партиціонування, окрема БД або файли)
• Написання функцій/скриптів архівації
• Налаштування планувальника та моніторингу
• Політика зберігання з ротацією
• Сценарій відновлення з архіву
• Документація процесу та навчання команди

Строки реалізації

Типові помилки

• Забути про VACUUM після масового видалення — таблиця роздувається.
• Використовувати одну транзакцію для всього обсягу — ризикуємо відкатом на години.
• Не перевіряти архів перед видаленням — втрата даних.
• Ігнорувати SKIP LOCKED — паралельні процеси блокують один одного.

Чому варто довірити архівування нам

У нас більше 10 років досвіду в адмініструванні PostgreSQL та MySQL. Ми реалізували системи архівування для проєктів з петабайтами даних. Гарантуємо, що процес не зачепить основну бізнес-логіку і буде повністю автоматизований. Зв'яжіться з нами — ми оцінимо ваш проєкт і запропонуємо оптимальне рішення. Отримайте консультацію інженера з продуктивності БД — безкоштовно.

Для отримання додаткової інформації зверніться до офіційної документації PostgreSQL. Політика зберігання узгоджується з вимогами бізнесу та регулятора (наприклад, GDPR).