Перейти к основному содержимому

Практика: Партиционирование таблиц


Онлайн редактор кода для PostgreSQL

Здесь вы можете попробовать свои силы в программировании на языке SQL для работы с PostgreSQL. Для этого мы предоставляем вам онлайн редактор кода, в котором вы можете написать свой код и запустить его.


❗️ При обновлении страницы код пропадёт, по этому, сохраните свой код куда-нибудь, если он важный.

Задание 1 — Простое range-партиционирование​

Задание 1: Range-партиционирование по времени

⏱️ Примерное время: 20-35 минут

Создай партиционированную таблицу по дате и проверь partition pruning.


Требования:

  • создай главную таблицу с PARTITION BY RANGE (дата)
  • создай 4–6 партиций вручную (или функцию для автосоздания)
  • вставь 100–500 тыс. тестовых строк (разные даты)
  • выполни 3 запроса с EXPLAIN ANALYZE:
    • запрос только к одной партиции → должен сканировать одну
    • запрос к диапазону → должен сканировать нужные партиции
    • запрос без фильтра по дате → должен сканировать все
  • сравни время и Buffers с непартиционированной копией таблицы

Пример:

EXPLAIN ANALYZE SELECT * FROM logs WHERE log_time BETWEEN '2025-03-01' AND '2025-03-31';
-- Должен показать: Seq Scan on logs_2025_03 (или Index Scan)
-- НЕ должен сканировать logs_2025_01, logs_2025_02 и т.д.

Задание 2 — List-партиционирование + DEFAULT​

Задание 2: List-партиционирование + DEFAULT-партиция

⏱️ Примерное время: 25-45 минут

Раздели данные по категориям / регионам / статусам.


Требования:

  • создай таблицу с PARTITION BY LIST (колонка)
  • создай 3–5 именованных партиций + одну DEFAULT
  • вставь данные так, чтобы часть попала в DEFAULT
  • выполни запросы с фильтром по ключу → проверь pruning
  • запрос без фильтра → покажет сканирование всех партиций + DEFAULT
  • перемести данные из DEFAULT в правильную партицию (ATTACH/DETACH)
  • создай индексы на партициях

Пример:

EXPLAIN SELECT * FROM orders_by_status WHERE status = 'shipped';
-- Должен сканировать ТОЛЬКО orders_shipping

Задание 3 — Hash-партиционирование + многоуровневое​

Задание 3: Hash + многоуровневое партиционирование

⏱️ Примерное время: 35-60 минут

Распредели нагрузку равномерно + добавь второй уровень.


Требования:

  • первый уровень — HASH (4–8 партиций)
  • второй уровень — RANGE или LIST (на каждой хеш-партиции)
  • вставь ~200–500 тыс. строк
  • проверь равномерность распределения (COUNT(*) по партиям)
  • сравни план запроса с фильтром по второму уровню (должен сканировать минимум партиций)
  • создай глобальный индекс на главной таблице

Пример:

EXPLAIN SELECT * FROM user_sessions WHERE session_start >= '2025-02-01' AND session_start < '2025-02-02';
-- Должен сканировать только суб-партиции за этот день во всех хеш-партициях

Задание 4 — Миграция существующей таблицы в партиционированную​

Задание 4: Миграция большой таблицы в партиционированную

⏱️ Примерное время: 60-120 минут

Переведи обычную таблицу в партиционированную с минимальным downtime.


Требования:

  • создай новую партиционированную таблицу ..._new
  • скопируй данные батчами (INSERT INTO ... SELECT ... LIMIT/OFFSET или по ключу)
  • создай индексы, триггеры, ограничения на новой таблице
  • переименуй таблицы (ALTER TABLE old RENAME TO old_backup; ALTER TABLE new RENAME TO old)
  • проверь работу зависимых объектов (вью, функции)
  • (бонус) реализуй миграцию с логической репликацией или ATTACH PARTITION

Пример батчевой миграции:

DO $$
DECLARE
    min_id BIGINT; max_id BIGINT; batch BIGINT := 100000;
BEGIN
    SELECT MIN(id), MAX(id) INTO min_id, max_id FROM logs_old;
    FOR i IN min_id..max_id BY batch LOOP
        INSERT INTO logs_new
        SELECT * FROM logs_old
        WHERE id >= i AND id < i + batch;
        COMMIT;
    END LOOP;
END $$;

Задание 5 — Итоговый проект: партиционированная система логов / заказов​

Задание 5: Полноценная партиционированная система (итоговое)

⏱️ Примерное время: 120-240 минут

Спроектируй и реализуй полноценную партиционированную систему.


Требования:

  • главная таблица + минимум 2 уровня партиционирования (range + list/hash)
  • автоматическое создание партиций (функция или pg_partman)
  • механизм удаления/архивирования старых партиций (drop/attach → s3/холодный диск)
  • индексы на горячих и холодных партициях (разные стратегии)
  • проверка partition pruning на 3–5 типичных запросах
  • мониторинг размеров партиций и количества строк
  • миграция тестовых данных (если нужно)
  • бонус: материализованное представление для агрегированных метрик по старым данным

Это задание — полноценный продакшн-кейс для резюме или собеседования на позицию DBA / Senior Backend.

подсказка

Партиционирование — это не панацея, а инструмент.
Главные выгоды:

  • partition pruning → ускорение запросов в 5–100 раз
  • DROP/DETACH партиции → мгновенное удаление старых данных
  • меньший bloat и быстрее VACUUM/ANALYZE
    Главные ошибки:
  • неправильный ключ партиционирования
  • слишком много мелких партиций
  • UPDATE ключа партиционирования → медленный перенос строк
  • забывать про DEFAULT-партицию и мониторинг