❓ Что это?
Это один из аспектов архитектуры высоконагруженных систем (или претендующих). Это разбиение данных, логически являющихся одной большой таблицей, на более мелкие физические части (секции).
Для приложения ситуация не меняется — таблица как была, так и останется. Но у DBA появится инструментарий для её гибкого сопровождения.
❓ Когда следует применять?
Чтобы однозначно ответить на вопрос когда применять секционирование, нужны нагрузочные тесты. Они позволят посмотреть: действительно ли вы не теряете в производительности на этапе построения планов запроса. Или для части запросов, которые охватывают данные нескольких секций. Может, наоборот, случиться деградация производительности!
Некоторые критерии, когда секционирование может быть полезно:
(1) У вас большие таблицы. Например, размер которых превышает объем ОЗУ сервера, который используется под СУБД.
(2) У вас высокая скорость загрузки данных. Здесь можно заранее предусмотреть необходимость секционирования.
(3) У вас уже снижается производительность выполнения запросов. Например, замедляется вставка записей в системную TOAST-таблицу (в неё автоматически попадают большие значения, >2 килобайт) так как записи в ней приближаются к нескольким млрд. Этот пункт очень актуален, если охватывается малая часть данных, но скорость снижается из-за того, что приходится просматривать полностью все индексы.
(4) У вас данные временных рядов. Секционирование по дате может помочь быстро извлечь записи за определенный период времени, не сканируя всю таблицу.
(5) У вас высокие расходы на обслуживание таблиц.
Команда VACUUM (очистка диска от записей), к нему ANALYZE (параметр для сбора статистики по таблицам), создание индексов становятся длительными операциями.
(6) У вас применяются политики хранения данных, например, часть данных таблиц разрешается выносить в архив или вообще удалять. Удаление старой секции происходит быстрее и менее ресурсоемко, чем удаление записей.
(7) Вам необходимо снизить потребление памяти. Индексы и фрагменты данных меньшего размера лучше размещаются в памяти и повышают частоту попаданий в кэш.
❓ Какие есть виды секционирования?
(1) По диапазонам не пересеющимся друг с другом, определённым по ключевому полю или набору полей.
Как выглядит код секционирования, на примере компании, торгующей мороженым, можно посмотреть в документации PostgreSQL.
(2) По списку. В нем явно указывается какие значения ключа должны относиться к каждой секции.
CREATE TABLE customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY LIST(status);
---Создание секции:
CREATE TABLE cust_active PARTITION OF customers FOR VALUES IN ('ACTIVE');
(3) По хэшу. Таблица секционируется по определенным модулям и остаткам, которые указываются для каждой секции. Цель - равномерное распределение данных по разным секциям.
CREATE TABLE customers (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY HASH(id);
---Создание секции:
CREATE TABLE cust_part1 PARTITION OF customers FOR VALUES WITH (modulus 3, remainder 0);
🔪 Отсечение
Секционирование тесно связано с механизмом отсечения секций (pruning), позволяющим оптимизировать SQL-запросы. На этапе выполнения плана построения запроса, оптимизатор бд понимает что в часть секций в принципе заглядывать не нужно. И это экономит время выполнения. Отсечение может происходить не только на этапе построения плана, но и в процессе выполнения запроса, т.к. некоторые значения могут появляться в процессе (например, значения из подзапросов).
📈 Проанализировать эффективность отсечения можно так:
(1) Используем команду EXPLAIN / EXPLAIN ANALYZE и смотрим план без отсечения секций.
(2) Включаем команду SET enable_partition_pruning = on; и снова выполянем EXPLAIN, и смотрим новый план с отсеченными секциями (их кол-во прописано в свойстве Subplans Removed).