Когда таблица становится слишком большой, работа с ней замедляется. Представь таблицу с миллионами заказов за несколько лет — каждый SELECT, UPDATE или DELETE начинает тормозить.
Решение — партиционирование (partitioning).
Партиционирование — это способ разделить одну физическую таблицу на несколько более мелких частей, называемых секциями (partitions). Каждая секция обрабатывает данные в диапазоне или по определённому условию.
Партиционирование создаётся на уровне DDL — команд, которые описывают структуру таблиц в базе данных. Партиционирование задаётся при создании таблицы, потому что большинство СУБД (включая PostgreSQL, MySQL и др.) не позволяют ретроспективно изменить обычную таблицу на партиционированную. В случае наличия огромной таблицы без партиций, этот процесс можно выполнить путем создания ее аналога с партициями и осуществления миграции данных.
Зачем использовать партиционирование?
1. Повышение производительности — запросы работают только с нужной секцией, а не со всей таблицей.
2. Упрощение администрирования — старые секции можно удалять или архивировать.
3. Чёткая логическая организация данных — например, по годам или регионам.
Основные типы партиционирования:
1. Диапазонное (RANGE) — делит таблицу по значениям в диапазоне. Например: заказы по годам.
2. Списковое (LIST) — делит по значениям из списка. Например: по странам или типу продукта.
3. Хэш-партиционирование (HASH) — делит данные равномерно по хешу поля. Используется, когда нет логического критерия разделения.
Существует два основных варианта партиционирования в SQL — горизонтальное и вертикальное:
1. Горизонтальное — разделение таблицы на партиции, основанное на строках. Каждая партиция содержит определённое количество строк.
2. Вертикальное — разделение таблицы на партиции, основанное на столбцах. Каждая партиция содержит определённый набор столбцов.
Пример (PostgreSQL, RANGE партиционирование)
ТЗ: Создать таблицу orders горизонтально разбитую на секции по годам, чтобы упростить работу с большим объёмом данных в будущем — ускорить запросы, сделать более быстрым архивирование, повысить производительность.
Создаем таблицу orders:
CREATE TABLE orders (
id SERIAL,
order_date DATE NOT NULL,
customer_id INT,
amount NUMERIC
) PARTITION BY RANGE (order_date);
Что делаем:
1. PARTITION BY RANGE (order_date) означает, что данные будут автоматически распределяться по секциям в зависимости от значения order_date.
2. Сама таблица orders теперь — логическая оболочка, а данные будут физически храниться в её партициях (субтаблицах).
Создаём секции:
CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
1. Теперь все заказы автоматически попадут в нужную секцию в зависимости от order_date.
2. orders_2023 хранит все строки, где order_date от 2023-01-01 до 2023-12-31.
3. orders_2024 — за 2024 год.
4. Диапазоны не пересекаются и не перекрываются, иначе возникнет ошибка при вставке.
🛠 Что происходит при обращении к партиционированным таблицам?
Когда пользователь выполняет запрос:
SELECT * FROM orders WHERE order_date >= '2024-01-01';
PostgreSQL понимает, что нужны только данные из orders_2024, и вообще не трогает остальные таблицы. Это называется pruning — отсечение ненужных секций.
Что нужно помнить
1. Партиционирование — даёт прирост при правильных запросах, особенно с фильтрацией по колонке партиционирования.
2. Партиции ведут себя как обычные таблицы — можно создавать на них отдельные индексы, статистику, правила.
3. Логика запросов остаётся на уровне родительской таблицы — СУБД сама направляет данные в нужную субтаблицу.
3. Хорошее партиционирование требует продуманной модели данных и нагрузки.
#Partition #SQL
📱 Подписаться на канал | Курс автора по SQL DDL