ארכוב נתוני מסד נתונים ישנים: אסטרטגיה ויישום

טבלת events עם 500 מיליון שורות וגדלה ב-2 מיליון ליום היא בעיה שמחמירה מדי יום. SELECT מאט, VACUUM לא מצליח לעמוד בקצב, אינדקסים תופסים ג'יגה-בייט. ללא ארכוב נתוני מסד נתונים ישנים, מסד הנתונים מתנפח, נעילות מפריעות לפעולות, ועלויות אחסון SSD פוגעות בתקציב. אנו פותרים משימות כאלה מקצה לקצה — מיישמים מערכת ארכוב שמפרידה נתונים חמים מקרים ללא השבתה או אובדן ביצועים. הניסיון שלנו מראה שאסטרטגיית ארכוב נתונים נכונה מפחיתה את עומס מסד הנתונים ב-70% וחותכת עלויות אחסון בפקטור של 2–3, וחוסכת כ-$500 בחודש על מערך נתונים של 1TB.

אילו בעיות פותר ארכוב נתונים ישנים?

הכאב העיקרי הוא הידרדרות בביצועים. כשטבלה גדלה, אפילו SELECT פשוט עם אינדקס מאט בגלל עומק B-tree ופיצול. VACUUM לא מצליח לנקות שורות מתות, ו-autovacuum מפגר. עלויות אחסון עולות — SSD יקר לנתונים שנגישים פעם בשנה. תחרות על נעילות במחיקות בכמות גדולה — DELETE ללא אצווה נועל את הטבלה לדקות. אנו פותרים זאת עם ארכוב באצוות באמצעות SKIP LOCKED — עובדים מקבילים לא מתנגשים. הבנת MVCC (Multi-Version Concurrency Control) ו-WAL (Write-Ahead Log) עוזרת לכוונן ספי autovacuum ולמנוע נפיחות.

לאחר הארכוב, עומס הדיסק יורד ב-70%, זמן ה-SELECT מצטמצם פי 3–5, וגודל מסד הנתונים קטן בפקטור של 2.

סקירת אסטרטגיית ארכוב נתונים

בחירת השיטה הנכונה לארכוב נתוני מסד נתונים ישנים היא קריטית. להלן השוואה בין אסטרטגיות נפוצות.

אסטרטגיות ארכוב: השוואת שיטות

איך לבחור את השיטה הנכונה?

איך אנו מיישמים ארכוב: הוראות שלב אחר שלב

הנה מתאר תמציתי של השלבים הכרוכים בארכוב נתוני מסד נתונים ישנים:

1. ניתוח מבנה ועומס
2. בחירת אסטרטגיה (למשל, ארכוב באצוות עם SKIP LOCKED)
3. כתיבת פונקציית ארכוב עם SKIP LOCKED
4. הגדרת מתזמן (למשל, פקודת Laravel)
5. ניטור ו-VACUUM
6. שחזור מהארכיון
7. אימות תקינות

-- Архивная таблица (может быть в отдельной схеме или базе)
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); 

// 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()));

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

מה כלול בעבודה

• ניתוח מבנה טבלאות ועומס
• עיצוב סכמת ארכיון (חלוקה למחיצות, מסד נתונים נפרד, או קבצים)
• כתיבת פונקציות/סקריפטים לארכוב
• הגדרת מתזמן וניטור
• מדיניות שמירה עם רוטציה
• תרחיש שחזור מהארכיון
• תיעוד והדרכת צוות

לוח זמנים ליישום

טעויות נפוצות

• שכחת // 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())); לאחר מחיקה המונית — נפיחות טבלה.
• שימוש בטרנזקציה אחת לכל הנפח — סיכון לגלגול אחורי של שעות.
• אי אימות הארכיון לפני מחיקה — אובדן נתונים.
• התעלמות מ-VACUUM ANALYZE — תהליכים מקבילים חוסמים זה את זה.

למה לסמוך עלינו בארכוב

יש לנו למעלה מ-10 שנות ניסיון בניהול PostgreSQL ו-MySQL. יישמנו מערכות ארכוב לפרויקטים עם פטה-בייט של נתונים. אנו מבטיחים שהתהליך לא ישפיע על הלוגיקה העסקית הליבה ויהיה אוטומטי לחלוטין. צור קשר — נעריך את הפרויקט שלך ונציע את הפתרון האופטימלי. קבל ייעוץ ממהנדס ביצועי מסדי נתונים.

למידע נוסף עיין בתיעוד הרשמי של PostgreSQL בכתובת https://www.postgresql.org/docs/current/sql-createtable.html. מדיניות השמירה מסוכמת עם דרישות עסקיות ורגולטוריות (למשל, GDPR).