Реалізація архівування старих даних бази даних

Реалізація архівування старих даних бази даних

Таблиця events з 500 мільйонів рядків та ростом 2 мільйонів на день — це проблема, що стає гострою щодня. SELECT сповільнюється, VACUUM не встигає, індекси займають гігабайти. Архівування розв'язує: гарячі дані залишаються в основній БД, холодні переходять в архів з іншим режимом зберігання.

Стратегії архівування

Partition detach — якщо таблиця партиціонована, старі партиції відсоединяються та переносяться в архівну БД або tablespace. Найшвидший підхід.

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

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

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

PostgreSQL: переміщення партіями

-- Архівна таблиця (може бути в окремій схемі або базі)
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('2024-01-01'::timestamptz, 10000);
-- Batch 1: 10000 rows archived (total: 10000)
-- Batch 2: 10000 rows archived (total: 20000)
-- ...

SKIP LOCKED дозволяє запускати кілька паралельних воркерів без конфліктів.

Планувальник: Artisan команда

// 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 0;
    }
}

Розклад:

// app/Console/Kernel.php
$schedule->command('db:archive --days=365')->daily('03:00');