JavaRush /Курси /SQL SELF /Оптимізація масового завантаження даних

Оптимізація масового завантаження даних

SQL SELF

Рівень 24 , Лекція 3

Відкрита

Уяви, що тобі треба завантажити мільйон рядків даних. Якщо робити це повільно, твій сервер буде довго зайнятий, користувачі можуть відчути гальма в роботі бази, а що ще гірше — твоя кава встигне охолонути до завершення процесу. Оптимізація допоможе уникнути перевантаження сервера, скоротити час очікування й мінімізувати ймовірність помилок під час завантаження.

Почнемо з простих кроків, а потім перейдемо до більш хитрих і просунутих фішок.

Вимкнення індексів і тригерів

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

Щоб тимчасово зняти це навантаження, їх можна вимкнути.

Приклад вимкнення індексів і тригерів:

-- Вимикаємо тригери для таблиці
ALTER TABLE students DISABLE TRIGGER ALL;

-- Завантажуємо дані
COPY students FROM '/path/to/students.csv' DELIMITER ',' CSV HEADER;

-- Вмикаємо тригери назад
ALTER TABLE students ENABLE TRIGGER ALL;

Як це працює?

Ми тимчасово вимикаємо всі тригери за допомогою команди DISABLE TRIGGER ALL.
Після завантаження даних вмикаємо тригери назад через ENABLE TRIGGER ALL.

Типова помилка: якщо ти забув увімкнути тригери назад, деякі автоматичні процеси (наприклад, оновлення полів за замовчуванням) можуть працювати некоректно. Тому не забувай повернути все на місце — це як вийти з "авіарежиму" на телефоні.

Використання транзакцій

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

Приклад використання транзакції:

-- Починаємо транзакцію
BEGIN;

-- Завантажуємо дані
COPY courses FROM '/path/to/courses.csv' DELIMITER ',' CSV HEADER;

-- Підтверджуємо зміни
COMMIT;

Чому це працює швидше?

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

Вимкнення перевірки цілісності

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

Приклад вимкнення перевірки цілісності:

SET session_replication_role = 'replica';

-- Завантажуємо дані
COPY enrollments FROM '/path/to/enrollments.csv' DELIMITER ',' CSV HEADER;

SET session_replication_role = 'origin';

session_replication_role = 'replica' вимикає перевірку цілісності даних (наприклад, унікальність і обмеження FOREIGN KEY).

Збільшення пам’яті для виконання

Налаштування пам’яті PostgreSQL може покращити продуктивність завантаження даних. Ключові параметри — work_mem і maintenance_work_mem.

Приклад збільшення пам’яті:

-- Збільшуємо обсяг пам’яті
SET work_mem = '64MB';
SET maintenance_work_mem = '256MB';

-- Завантажуємо дані
COPY teachers FROM '/path/to/teachers.csv' DELIMITER ',' CSV HEADER;

Що це дає?

work_mem використовується для проміжних операцій, таких як сортування чи хешування.
maintenance_work_mem впливає на операції, пов’язані з індексами, наприклад, їх перебудову.

Порада: Будь обережний зі збільшенням пам’яті, особливо на системах з обмеженими ресурсами.

Підготовка даних перед завантаженням

Підготовка даних може суттєво скоротити час завантаження. Наприклад, якщо у тебе є дублікати рядків, відфільтруй їх заздалегідь, щоб сервер PostgreSQL не витрачав час на обробку зайвого.

Приклад очищення даних:

Якщо у тебе є файл з дубльованими рядками, можна використати Python для їх видалення.

import pandas as pd

# Завантажуємо CSV-файл
data = pd.read_csv('students.csv')

# Видаляємо дублікати
data = data.drop_duplicates()

# Зберігаємо чистий CSV
data.to_csv('students_clean.csv', index=False)

Партиціювання даних

Якщо у тебе величезний файл, розбий його на кілька менших. Це дозволить PostgreSQL ефективніше обробляти дані.

Приклад:

Розбий файл large_data.csv на шматки по 1000 рядків через Linux:

split -l 1000 large_data.csv chunk_

Потім завантажуй їх окремо:

COPY students FROM 'chunk_aa' DELIMITER ',' CSV HEADER;
COPY students FROM 'chunk_ab' DELIMITER ',' CSV HEADER;
-- І так далі

Виконання завантаження у фоновому режимі

Якщо є можливість, можна використати фонові процеси для завантаження даних, щоб не навантажувати основну базу.

Інструменти, такі як pg_cron, допоможуть запускати задачі за розкладом.

Приклад: налаштування фонового завантаження через pg_cron:

CREATE EXTENSION pg_cron;

SELECT cron.schedule('*/5 * * * *', $$COPY students FROM '/path/to/data.csv' DELIMITER ',' CSV HEADER$$);

Кожні 5 хвилин дані з файлу будуть завантажуватись у таблицю.

Це лише приклад, насправді так робити не треба! Я просто хотів показати, що PostgreSQL дуже гнучкий, і ти можеш ну дуже гнучко керувати додаванням даних прямо з SQL-скриптів.

Підводні камені

Деякі моменти, які варто враховувати:

Якщо ти вимикаєш індекси й тригери, не забудь увімкнути їх назад! Забудеш — будеш потім лагодити баги після завантаження.
Збільшуючи параметри пам’яті, слідкуй за ресурсами сервера: один жадібний запит може з’їсти всю оперативку.
При використанні транзакцій переконайся, що файл даних не містить критичних помилок. Однієї помилки достатньо, щоб відкотити все завантаження.