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

Архівування даних підвищує продуктивність бази даних за рахунок винесення старих даних БД в архівне сховище. Таблиця 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).

Послуги бекенд-розробки: production-grade надійність

На production-сервері о 3:14 ночі черга Laravel Jobs перестала оброблятися — 40 000 необроблених завдань у Redis. Причина: worker упав через memory leak у статичній змінній Eloquent observer, supervisor не перезапустив через misconfigured stopwaitsecs. Ми розбирали такий інцидент на проекті з 500 RPS: діагностика 4 години, фікс — 20 хвилин. Щоб ви не втрачали гроші, пропонуємо послуги бекенд-розробки з акцентом на production-grade надійність — 10+ років досвіду, 50+ проектів, 5 років на ринку. Оцінимо ваш проект за 2 дні.

Які проблеми вирішуємо

N+1 запити: головний вбивця швидкості

N+1 — найпоширеніша причина повільних сторінок у Laravel-додатках. Стандартна історія: сторінка працювала нормально на dev з 10 записами, на production з 10 000 — 8-секундне завантаження.

Laravel Debugbar у dev-оточенні показує кількість запитів. Більше 20 — сигнал для audit.

Model::preventLazyLoading(! app()->isProduction());

Telescope для профілювання: логує всі запити, jobs, mail, notifications з деталізацією. Після впровадження eager loading час завантаження сторінки падає з 8 с до 0.3 с — у 27 разів.

Memory leak у статичних змінних

У Laravel Octane або Swoole додаток тримається в пам’яті між запитами. Статичні змінні не скидаються — призводять до неконтрольованого росту пам’яті. Використовуємо defer-функції та контейнерні біндинги для коректного скидання стану.

Неправильний connection pool

Rails, Laravel, Django відкривають нове з'єднання PostgreSQL на кожен PHP/Python процес. 100 воркерів — 100 з'єднань. PostgreSQL деградує від 200+ активних з'єднань через overhead на управління.

PgBouncer у transaction pooling: 1000 воркерів → 20–50 реальних з'єднань. Це знижує latency на 40% та зменшує витрати на хостинг на 30% — при середній вартості хостингу $2,000/міс економить $600/міс. GIN-індекс для JSONB до 100 разів швидший за B-tree при пошуку.

Як Octane справляється з високим навантаженням?

Laravel Octane (RoadRunner або Swoole) прибирає overhead bootstrap на кожен HTTP-запит. Приріст: 3–8x на синтетичних бенчмарках, 2–4x на реальних додатках. Важливо: не зберігати стан у статичних змінних — застосовуємо це на проектах >1000 RPS.

Як PostgreSQL допомагає уникнути повільних запитів?

Використовуємо composite indexes для WHERE + ORDER BY, partial indexes для фільтрів з високою селективністю, GIN-індекси для JSONB та full-text search. to_tsvector + GIN замість LIKE '%query%' — запобігає seq scan навіть на мільйонах записів. Аналізуємо плани через EXPLAIN ANALYZE та pg_stat_statements.

Як обрати стек для вашого проекту?

Node.js виправданий для realtime: Laravel публікує події в Redis Pub/Sub, Node.js підписується та транслює клієнтам. Go — для goroutines (10k з'єднань на сервер — норма), але розробка повільніша, ніж Laravel.

Чому Redis критичний для продуктивності?

Redis виконує кілька ролей:

Redis Cluster для горизонтального масштабування, Sentinel для автоматичного failover. Замовте консультацію щодо оптимізації Redis для вашого проекту.

Що входить в роботу під ключ

• Архітектурне проектування (документація API, схема БД, діаграма сервісів)
• Реалізація за узгодженим ТЗ з code review
• Налаштування CI/CD (GitHub Actions, Docker), моніторингу (Sentry, Grafana), алертингу
• Навантажувальне тестування (k6, wrk) зі звітом
• Передача вихідних кодів, доступів, інструкція з деплою
• Навчання команди замовника (2–3 сесії)
• Гарантійна підтримка 1 місяць після здачі

Орієнтири по термінах

Вартість розраховується індивідуально після аналізу вимог до навантаження, інтеграцій та бізнес-логіки. Зв'яжіться з нами для безкоштовного аудиту вашого поточного backend — отримайте план оптимізації за 2 дні. Замовте консультацію та дізнайтеся, як знизити витрати на інфраструктуру на 30% без втрати продуктивності.