Понимание и реализация декларативного секционирования таблиц в PostgreSQL 14+

Секционирование PostgreSQL стоит рассмотреть, когда одна таблица стала неудобной для запросов, вакуумирования, архивирования или удаления. Обычный пример — таблица событий, которая получает миллионы строк в день и почти всегда запрашивается по временному диапазону. Без секционирования даже хорошие индексы могут оставить вас с таблицей, которую дорого обслуживать и трудно очищать от устаревших данных.

Декларативное секционирование позволяет одной логической таблице направлять строки в меньшие физические таблицы, называемые секциями. PostgreSQL 10 представил нативный синтаксис, а более поздние версии улучшили планирование, отсечение, индексирование и поведение при обслуживании. PostgreSQL 14+ достаточно зрелый, чтобы многие команды могли использовать секционирование без схем наследования на основе триггеров, но он все еще требует тщательного проектирования. Плохой ключ секционирования может усложнить систему, не сделав ее быстрее.

Что такое декларативное секционирование таблиц?

Декларативное секционирование — это функция базы данных, которая позволяет разделить одну логическую таблицу (родительскую или секционированную таблицу) на несколько физических таблиц (дочерние или секции) на основе определенного набора правил. Каждая секция содержит подмножество данных из родительской таблицы. Ключ секционирования определяет, к какой секции принадлежит строка.

Ключевые преимущества декларативного секционирования включают:

• Улучшенная производительность запросов: Запросы, фильтрующие по ключу секционирования, могут быть быстрее, поскольку PostgreSQL может отсекать секции, которые не могут содержать соответствующие строки.
• Более легкое управление данными: Операции, такие как удаление старых данных или архивирование, могут выполняться гораздо эффективнее путем отсоединения или удаления отдельных секций, а не выполнения массовых операций DELETE для одной большой таблицы.
• Упрощенное обслуживание: Индексирование и вакуумирование можно управлять на уровне отдельных секций, уменьшая влияние на всю таблицу.
• Меньшие единицы обслуживания: Индексы на уровне секций, операции отсоединения и целенаправленная вакуумизация могут уменьшить радиус воздействия рутинного обслуживания.

Типы декларативного секционирования

PostgreSQL поддерживает несколько методов декларативного секционирования, каждый из которых подходит для разных шаблонов распределения данных:

1. Секционирование по диапазону

Секционирование по диапазону делит данные на основе непрерывного диапазона значений в определенном столбце (например, даты, числа).

Сценарий использования: Идеально подходит для данных временных рядов, таких как логи, события или записи продаж, где вы часто запрашиваете данные в определенных диапазонах дат или чисел.

Пример: Секционирование таблицы sales по столбцу sale_date.

Создание таблицы, секционированной по диапазону

Сначала создайте родительскую таблицу, указав метод и ключ секционирования:

CREATE TABLE sales (
    sale_id SERIAL,
    product_name VARCHAR(100),
    sale_amount NUMERIC(10, 2),
    sale_date DATE NOT NULL
)
PARTITION BY RANGE (sale_date);

Затем создайте отдельные секции. Каждая секция определяется с помощью предложения FOR VALUES, указывающего диапазон, который она будет содержать.

-- Секция для продаж в январе 2023 года.
-- Верхняя граница является исключительной, поэтому сюда входит 31 января.
CREATE TABLE sales_2023_01
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

-- Секция для продаж в феврале 2023 года
CREATE TABLE sales_2023_02
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

-- Секция для продаж в марте 2023 года
CREATE TABLE sales_2023_03
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');

Совет: При определении диапазонов убедитесь, что они непрерывны и покрывают все возможные значения. Избегайте перекрывающихся диапазонов. Значение TO является исключительным.

2. Секционирование по списку

Секционирование по списку делит данные на основе дискретного списка значений в столбце.

Сценарий использования: Подходит для столбцов с фиксированным, известным набором значений, таких как географические регионы, коды статусов или категории продуктов.

Пример: Секционирование таблицы orders по столбцу region.

Создание таблицы, секционированной по списку

Определите родительскую таблицу с помощью PARTITION BY LIST:

CREATE TABLE orders (
    order_id SERIAL,
    customer_name VARCHAR(100),
    order_total NUMERIC(10, 2),
    region VARCHAR(50) NOT NULL
)
PARTITION BY LIST (region);

Создайте секции для конкретных регионов:

-- Секция для заказов из 'Северной Америки'
CREATE TABLE orders_north_america
    PARTITION OF orders ()
    FOR VALUES IN ('North America');

-- Секция для заказов из 'Европы'
CREATE TABLE orders_europe
    PARTITION OF orders ()
    FOR VALUES IN ('Europe');

-- Секция для заказов из 'Азии'
CREATE TABLE orders_asia
    PARTITION OF orders ()
    FOR VALUES IN ('Asia');

Важно: Если вы вставите значение для region, которое не соответствует ни одному списку IN существующей секции, и нет секции DEFAULT, вставка завершится ошибкой. Вы можете создать секцию DEFAULT, чтобы перехватывать все остальные значения.

Создание секции по умолчанию

-- Секция по умолчанию для любого региона, не указанного явно
CREATE TABLE orders_other
    PARTITION OF orders ()
    DEFAULT;

3. Секционирование по хешу

Секционирование по хешу распределяет данные по нескольким секциям на основе хеш-значения ключа секционирования.

Сценарий использования: Полезно, когда у вас большой объем данных и вы хотите равномерно распределить их по секциям без четкого распределения по диапазону или списку. Это хорошо для балансировки нагрузки.

Пример: Секционирование таблицы users по user_id.

Создание таблицы, секционированной по хешу

Определите родительскую таблицу с помощью PARTITION BY HASH и укажите количество секций:

CREATE TABLE users (
    user_id BIGSERIAL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
)
PARTITION BY HASH (user_id);

PostgreSQL автоматически создаст секции за вас, если вы их не укажете, но обычно рекомендуется создавать их явно, особенно когда вы хотите контролировать количество и имена секций.

Создание явных хеш-секций

-- Создание 4 хеш-секций
CREATE TABLE users_p0
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 0);

CREATE TABLE users_p1
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 1);

CREATE TABLE users_p2
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 2);

CREATE TABLE users_p3
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 3);

Примечание: При использовании секционирования по хешу вам нужно указать modulus (общее количество секций) и remainder (какая это секция).

Реализация декларативного секционирования: лучшие практики

• Выберите правильный ключ секционирования: Ключ секционирования должен соответствовать вашим наиболее частым фильтрам запросов и операциям управления данными. Хороший ключ значительно повышает производительность.
• Учитывайте количество секций: Слишком мало секций может не дать достаточного преимущества, а слишком много может увеличить накладные расходы. Стремитесь к количеству, которое балансирует управляемость и производительность. Для секционирования по диапазону учитывайте скорость роста данных и политики хранения.
• Используйте pg_partman для автоматизации: Для секционирования по диапазону, особенно с данными временных рядов, рассмотрите возможность использования расширений, таких как pg_partman. Он автоматизирует создание новых секций и архивирование/удаление старых, значительно сокращая ручные усилия.
• Стратегическое индексирование: Индексы физически хранятся в каждой секции. Создание индекса для родительской таблицы создает соответствующие индексы секций, но вам все равно следует проверить, нужен ли каждой секции одинаковый шаблон индекса.
• Отсечение секций: Убедитесь, что ваши запросы написаны так, чтобы использовать отсечение секций, включая ключ секционирования в предложения WHERE. Команда EXPLAIN может показать, происходит ли отсечение.
• Секции DEFAULT: Для секционирования по списку секция DEFAULT имеет решающее значение для избежания ошибок вставки, если неожиданно появятся новые значения.
• Уникальные ограничения: Уникальное ограничение или первичный ключ для секционированной таблицы обычно должен включать все столбцы ключа секционирования. Это часто застает врасплох многие первые проекты.
• Типы данных: Убедитесь, что тип данных ключа секционирования подходит и согласован в родительской и дочерних таблицах.

Управление секциями

Присоединение и отсоединение секций

Хотя секции создаются напрямую с помощью CREATE TABLE ... PARTITION OF ..., вы также можете отсоединять и присоединять существующие таблицы в качестве секций. Это полезно для миграции данных или управления большими наборами данных.

Отсоединение секции: Отсоединение превращает секцию в обычную таблицу, сохраняя ее данные.

-- Отсоединение секции sales_2023_01
ALTER TABLE sales DETACH PARTITION sales_2023_01;

Присоединение таблицы в качестве секции: Вы можете присоединить обычную таблицу, которая соответствует схеме родительской таблицы и содержит данные, соответствующие границам секции.

-- Предположим, что sales_2022_12 — это обычная таблица с теми же столбцами, что и sales,
-- и содержит только строки за декабрь 2022 года.
ALTER TABLE sales ATTACH PARTITION sales_2022_12
    FOR VALUES FROM ('2022-12-01') TO ('2023-01-01');

Перед присоединением большой таблицы сначала добавьте соответствующее ограничение CHECK. PostgreSQL может использовать это ограничение, чтобы избежать сканирования всей таблицы для проверки соответствия строк границам секции.

Удаление секций

Удаление секции — это быстрая операция, так как она удаляет только таблицу секции, а не данные внутри нее (если не указано иное). Это намного быстрее, чем DELETE.

-- Чтобы удалить секцию, вы можете просто удалить дочернюю таблицу
DROP TABLE sales_2023_01;

Пример: Улучшение производительности запросов с помощью отсечения секций

Рассмотрим таблицу sales, секционированную по sale_date, как показано ранее.

Запрос без отсечения секций (гипотетически для несекционированной таблицы):

SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

Если бы sales была огромной несекционированной таблицей, этот запрос сканировал бы всю таблицу. Однако с декларативным секционированием:

-- Этот запрос будет сканировать только секцию sales_2023_01
SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

Планировщик запросов PostgreSQL распознает, что sale_date является ключом секционирования и что указанный диапазон полностью попадает в секцию sales_2023_01. Поэтому он будет сканировать только эту секцию, что резко сократит ввод-вывод и повысит производительность.

Чтобы проверить это, используйте EXPLAIN:

EXPLAIN SELECT SUM(sale_amount) FROM sales WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

Вывод должен показывать только соответствующую секцию или может показывать удаленные подпланы в зависимости от версии PostgreSQL и формы плана. Важным признаком является то, что несвязанные секции не сканируются.

Практический контрольный список проектирования

Секционируйте только тогда, когда можете назвать операционное преимущество. «Таблица большая» само по себе недостаточно. Большая таблица с хорошо индексированными точечными запросами может быть в порядке. Секционирование имеет больше смысла, когда большинство запросов включают ключ секционирования, когда старые данные регулярно архивируются или удаляются, или когда обслуживание одной огромной таблицы уже вызывает проблемы.

Для таблиц временных рядов выбирайте размеры секций, соответствующие вашим шаблонам запросов и хранения. Ежедневные секции полезны для очень высокой загрузки и короткого хранения. Ежемесячные секции часто проще управлять при умеренном объеме событий. Слишком много крошечных секций может замедлить планирование и сделать обслуживание шумным; слишком мало гигантских секций может не решить исходную проблему.

Планируйте вставки до развертывания. Если строки могут поступать с опозданием, держите старые секции доступными достаточно долго, чтобы их получить. Если ключ секционирования может содержать неожиданные значения, создайте секцию DEFAULT и отслеживайте ее. Секция по умолчанию должна быть предохранительной сетью, а не местом, где забытые данные тихо накапливаются месяцами.

Наконец, тестируйте с реальными формами запросов. Отсечение секций работает лучше всего, когда предложение WHERE явно раскрывает ключ секционирования, например, sale_date >= '2023-01-01' AND sale_date < '2023-02-01'. Обертывание ключа в функции может затруднить отсечение:

-- Менее дружелюбно к отсечению
WHERE date_trunc('month', sale_date) = DATE '2023-01-01';

-- Легче для планировщика
WHERE sale_date >= DATE '2023-01-01'
  AND sale_date <  DATE '2023-02-01';

Декларативное секционирование — это инструмент обслуживания в той же мере, что и инструмент запросов. При правильном использовании оно делает старые данные дешевыми для удаления, а горячие данные — более легкими для сканирования. При небрежном использовании оно добавляет больше таблиц, больше индексов и больше граничных случаев. Начните с шаблона доступа, выберите ключ секционирования из этого шаблона и проверьте план, прежде чем считать дизайн завершенным.

Для существующей большой таблицы не планируйте рискованное одноразовое преобразование во время пикового трафика. Обычный путь миграции — создать новую секционированную таблицу, скопировать данные частями, поддерживать новые записи через логику приложения или тщательно протестированный триггер, а затем переименовать таблицы во время короткого окна обслуживания. Точный подход зависит от объема записи и допустимого времени простоя, но принцип тот же: докажите копию, докажите ограничения и отрепетируйте переключение перед касанием продакшена.