Главная
Arenadata Postgres
Концепции
Модель данных
Таблицы
Секционирование

Секционирование

Евгения Кузина

Содержание

Обзор
Декларативное секционирование
Секционирование с использованием наследования
- Пример
- Обслуживание таблиц, секционированных через наследование

Обзор

PostgreSQL поддерживает базовое секционирование (partitioning) таблиц. Под секционированием понимается разбиение одной большой логической таблицы на несколько меньших физических секций. Секционирование используется для очень больших таблиц. Выиграют ли таблицы от секционирования, зависит от использующего их приложения, хотя обычно секционирование следует применять для таблиц, размер которых превышает объем оперативной памяти сервера.

Секционирование может дать следующие преимущества:

В определенных ситуациях секционирование может значительно повысить производительность запросов. Например, когда большая часть часто запрашиваемых строк находится в одной или нескольких секциях. Секционирование по сути заменяет верхние уровни дерева индексов, что увеличивает вероятность нахождения наиболее востребованных частей индексов в памяти.
Когда в запросе задействована большая часть одной секции, производительность может возрасти, если будет выполняться последовательное сканирование этой секции, а не поиск по индексу, сопровождаемый произвольным чтением данных, разбросанных по всей таблице.
Массовую загрузку и удаление данных можно осуществлять добавляя и удаляя секции. Команда DROP TABLE, которую можно использовать для удаления отдельной секции, или ALTER TABLE DETACH PARTITION, выполняется быстрее, чем массовая операция над строками.
Редко используемые данные можно перенести на более дешёвые и медленные носители.

Декларативное секционирование

PostgreSQL позволяет определить условия, в соответствии с которыми таблица делится на секции. Разделённая таблица является секционированной (partitioned) таблицей. Декларация секционирования включает метод секционирования и список столбцов или выражений, которые будут использоваться в качестве ключа секционирования. Методы секционирования описаны в таблице ниже.

Методы секционирования
Метод	Описание
Секционирование по диапазонам	Таблица секционируется по диапазонам, определённым по ключевому столбцу или набору столбцов. Диапазоны не должны пересекаться друг с другом. Например, можно секционировать данные по диапазонам дат или по диапазонам идентификаторов определённых бизнес-объектов. Границы каждого диапазона включают нижнее значение и исключают верхнее. Например, если для первой секции задан диапазон значений от 1 до 10, а для второй — от 10 до 20, значение 10 относится ко второй секции, а не к первой
Секционирование по списку	Таблица разделяется с помощью списка, явно указывающего, какие значения ключа должны относиться к каждой секции
Секционирование по хешу	Таблица секционируется по определённым модулям и остаткам, которые указываются для каждой секции. Каждая секция содержит строки, для которых хеш-значение ключа разбиения, разделённое на модуль, равняется заданному остатку

Если вашему приложению требуются другие формы секционирования, можно прибегнуть к альтернативным реализациям, например секционированию с использованием наследования. Такие подходы дают гибкость, но не предоставляют такого выигрыша в производительности, как встроенное декларативное секционирование.

Используйте команду CREATE TABLE, чтобы определить секционированную таблицу и ее секции. Базовый синтаксис создания секционированной таблицы:

CREATE <имя_таблицы> PARTITION BY { RANGE | LIST | HASH } ( { <имя_столбца> | ( <выражение> ) } )

Базовый синтаксис создания секций:

CREATE TABLE <имя_секции> PARTITION OF <имя_таблицы>
    { FOR VALUES FROM (<минимальное_значение>) TO (<максимальное_значение>)
    | FOR VALUES IN ([<значение>])
    | FOR VALUES WITH (MODULUS <значение_модуля>, REMAINDER <значение_остатка>) }

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

Невозможно преобразовать обычную таблицу в секционированную или наоборот. Однако вы можете добавить существующую таблицу в качестве секции к секционированной таблице или удалить секцию и превратить её в отдельную таблицу.

Секционирование по диапазонам

Разбиение по диапазонам часто используется со столбцами типа timestamp и date.

Приведенный ниже код создает три секции и распределяет книги в соответствии со значением поля delivery_date. Книги, доставленные в феврале, будут помещены в секцию books_2022_02, книги, доставленные в марте — в секцию books_2022_03, а в апреле — в секцию books_2022_04. Также добавляется секция по умолчанию (default) для строк, которые не соответствуют другим секциям:

CREATE TABLE books (
    code        char(5) NOT NULL,
    title       varchar(40) NOT NULL,
    delivery_date   date NOT NULL,
    genre        varchar(10)
) PARTITION BY RANGE (delivery_date);

CREATE TABLE books_2022_02 PARTITION OF books FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE books_2022_03 PARTITION OF books FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
CREATE TABLE books_2022_04 PARTITION OF books FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
CREATE TABLE books_default PARTITION OF books DEFAULT;

Следующая строка добавляется в секцию books_2022_04:

INSERT INTO books (code, title, delivery_date, genre) VALUES
    ('DC-34','Hyperion', date'2022-04-28', 'sci-fi');

Кроме того, специальные значения MINVALUE и MAXVALUE, обозначающие минимальное и максимальное значения, содержащиеся в столбце, могут использоваться при указании диапазонов. Например:

CREATE TABLE table1_part1 PARTITION OF table1 FOR VALUES FROM (MINVALUE) TO (100);
CREATE TABLE table1_part2 PARTITION OF table1 FOR VALUES FROM (100) TO (1000);
CREATE TABLE table1_part3 PARTITION OF table1 FOR VALUES FROM (1000) TO (MAXVALUE);

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

В приведенной ниже таблице столбец genre используется в качестве ключа секционирования для секционирования по списку. Этот метод позволяет указать несколько значений для одной секции. Приведенный ниже код создает секцию по умолчанию, секцию для книг жанра novel , секцию для книг жанра sci-fi и одну секцию для книг жанров detective и mystery:

CREATE TABLE books (
    code        char(5) NOT NULL,
    title       varchar(40) NOT NULL,
    delivery_date   date NOT NULL,
    genre        varchar(10) NOT NULL
) PARTITION BY LIST (genre);

CREATE TABLE books_novel PARTITION OF books FOR VALUES IN ('novel');
CREATE TABLE books_scifi PARTITION OF books FOR VALUES IN ('sci-fi');
CREATE TABLE books_detective PARTITION OF books FOR VALUES IN ('detective', 'mystery');
CREATE TABLE books_default PARTITION OF books DEFAULT;

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

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

Пример:

CREATE TABLE books (
    code        char(5) NOT NULL,
    title       varchar(40) NOT NULL,
    delivery_date   date NOT NULL,
    genre        varchar(10) NOT NULL
) PARTITION BY HASH (code);

CREATE TABLE books_part1 PARTITION OF books FOR VALUES WITH (MODULUS 5, REMAINDER 0);
CREATE TABLE books_part2 PARTITION OF books FOR VALUES WITH (MODULUS 5, REMAINDER 1);
CREATE TABLE books_part3 PARTITION OF books FOR VALUES WITH (MODULUS 5, REMAINDER 2);
CREATE TABLE books_part4 PARTITION OF books FOR VALUES WITH (MODULUS 5, REMAINDER 3);
CREATE TABLE books_part5 PARTITION OF books FOR VALUES WITH (MODULUS 5, REMAINDER 4);

Обслуживание секций

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

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

CREATE INDEX ON books (delivery_date);

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

ПРИМЕЧАНИЕ

Для оптимизации запросов значение конфигурационного параметра enable_partition_pruning должно быть равно on. Это значение используется по умолчанию в ADPG. За дополнительной информацией обратитесь к разделу Partition pruning.

Вложенное секционирование

Таблицы-секции могут быть также разбиты на секции. Например, можно добавить вложенные секции в примере секционирования по списку:

CREATE TABLE books_novel PARTITION OF books FOR VALUES IN ('novel');
CREATE TABLE books_detective PARTITION OF books FOR VALUES IN ('detective', 'mystery');
CREATE TABLE books_scifi PARTITION OF books FOR VALUES IN ('sci-fi') PARTITION BY RANGE (delivery_date);
    CREATE TABLE books_scifi_2022_01 PARTITION OF books_scifi FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
    CREATE TABLE books_scifi_2022_02 PARTITION OF books_scifi FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
    CREATE TABLE books_scifi_2022_03 PARTITION OF books_scifi FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');

PostgreSQL вставит следующую строку в таблицу books_scifi_2022_02:

INSERT INTO books (code, title, delivery_date, genre) VALUES
    ('AC-29','The time machine', date'2022-02-28', 'sci-fi');

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

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

Вы можете удалить секцию, которая больше не нужна командой DROP TABLE:

DROP TABLE books_novel;

Эта операция быстро удаляет миллионы записей, но требует блокировки ACCESS EXCLUSIVE для секционированной таблицы.

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

ALTER TABLE books DETACH PARTITION books_novel;
ALTER TABLE books DETACH PARTITION books_novel CONCURRENTLY;

Первая форма команды требует блокировки ACCESS EXCLUSIVE для секционированной таблицы. Команда с квалификатором CONCURRENTLY использует только блокировку SHARE UPDATE EXCLUSIVE. За подробностями обратитесь к разделу DETACH PARTITION.

Добавление секции

Можно использовать следующий код для добавления новой секции:

CREATE TABLE books_2022_05 PARTITION OF books FOR VALUES FROM ('2022-05-01') TO ('2022-06-01');

Также можно создать новую таблицу вне структуры секций и использовать выражение ATTACH PARTITION, чтобы сделать её секцией позже. Такой подход позволяет загружать, проверять и преобразовывать новые данные до того, как они появятся в секционированной таблице:

CREATE TABLE books_2022_06 (LIKE books INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE books_2022_06 ADD CONSTRAINT month_2022_06
   CHECK ( delivery_date >= DATE '2022-06-01' AND delivery_date < DATE '2022-07-01' );

ALTER TABLE books ATTACH PARTITION books_2022_06
    FOR VALUES FROM ('2022-06-01') TO ('2022-07-01' );

Выражение ATTACH PARTITION требует блокировки SHARE UPDATE EXCLUSIVE для секционированной таблицы. Рекомендуется создать ограничение CHECK для добавляемой таблицы, соответствующее ограничению новой секции, как показано выше. Таким образом, PostgreSQL сможет пропустить сканирование, необходимое для проверки неявного ограничения секции.

Ограничения

Использование секционированных таблиц накладывает следующие ограничения:

Создать ограничение уникальности или первичного ключа для секционированной таблицы можно, только если ключи секций не включают никакие выражения или вызовы функций и в ограничение входят все столбцы ключа секционирования.
Создать ограничение-исключение, охватывающее всю секционированную таблицу, нельзя. Можно только поместить такое ограничение в каждую отдельную секцию с данными.
Триггеры BEFORE ROW для INSERT не могут менять секцию, в которую в итоге попадёт новая строка.
Смешивание временных и постоянных таблиц в одном дереве секционирования не допускается.
Секция не может иметь никаких других родителей, кроме секционированной таблицы, к которой она относится.
В секциях не может быть столбцов, отсутствующих в родительской таблице.
Ограничения CHECK и NOT NULL, определённые в секционированной таблице, всегда наследуются всеми её секциями. Также нельзя удалить ограничение NOT NULL, заданное для столбца секции, если такое же ограничение существует в родительской таблице.

Секционирование с использованием наследования

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

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

Пример

В этом примере создается структура секционирования, эквивалентная приведенному выше примеру декларативного секционирования по диапазонам.

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

CREATE TABLE books (
    code        char(5) NOT NULL,
    title       varchar(40) NOT NULL,
    delivery_date   date NOT NULL,
    genre        varchar(10)
);

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

CREATE TABLE books_2022_02 (
CHECK ( delivery_date >= DATE '2022-02-01' AND delivery_date < DATE '2022-03-01' )
) INHERITS (books);

CREATE TABLE books_2022_03 (
CHECK ( delivery_date >= DATE '2022-03-01' AND delivery_date < DATE '2022-04-01' )
) INHERITS (books);

CREATE TABLE books_2022_04 (
CHECK ( delivery_date >= DATE '2022-04-01' AND delivery_date < DATE '2022-05-01' )
) INHERITS (books);

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

CREATE INDEX books_2022_02_delivery_date ON books_2022_02 (delivery_date);
CREATE INDEX books_2022_03_delivery_date ON books_2022_03 (delivery_date);
CREATE INDEX books_2022_04_delivery_date ON books_2022_04 (delivery_date);

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

CREATE OR REPLACE FUNCTION books_select_part()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.delivery_date BETWEEN date'2022-02-01' AND date'2022-03-01' - 1 then
        INSERT INTO books_2022_02 VALUES (NEW.*);
    ELSIF NEW.delivery_date BETWEEN date'2022-03-01' and date'2022-04-01' - 1 then
        INSERT INTO books_2022_03 VALUES (NEW.*);
    ELSIF NEW.delivery_date BETWEEN date'2022-04-01' and date'2022-05-01' - 1 then
        INSERT INTO books_2022_04 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'This date not in your partitions. Add a partition.';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Создайте триггер, который вызывает функцию:

CREATE TRIGGER insert_books_trigger
    BEFORE INSERT ON books
    FOR EACH ROW EXECUTE FUNCTION books_select_part();

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

ПРИМЕЧАНИЕ

При использовании этого подхода, не рекомендуется отключать конфигурационный параметр constraint_exclusion. В противном случае дочерние таблицы могут сканироваться, когда это не требуется. В ADPG значением по умолчанию этого параметра является partition.

Обслуживание таблиц, секционированных через наследование

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

Можно использовать команду DROP TABLE чтобы удалить дочернюю таблицу, которая больше не нужна:

DROP TABLE books_2022_02;

Чтобы удалить дочернюю таблицу из иерархии наследования, но сохранить к ней доступ как к обычной таблице, используйте команду ALTER TABLE:

ALTER TABLE books_2022_02 NO INHERIT books;

Добавление секции

Чтобы добавить новую дочернюю таблицу для обработки новых данных, создайте пустую дочернюю таблицу:

CREATE TABLE books_2022_05 (
CHECK ( delivery_date >= DATE '2022-05-01' AND delivery_date < DATE '2022-06-01' )
) INHERITS (books);

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

CREATE TABLE books_2022_06
  (LIKE books INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE books_2022_06 ADD CONSTRAINT month_2022_06
    CHECK ( delivery_date >= DATE '2022-06-01' AND delivery_date < DATE '2022-07-01' );

ALTER TABLE books_2022_06 INHERIT books;

После добавления новой секции необходимо обновить триггерную функцию:

CREATE OR REPLACE FUNCTION books_select_part()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.delivery_date BETWEEN date'2022-02-01' AND date'2022-03-01' - 1 then
        INSERT INTO books_2022_02 VALUES (NEW.*);
    ELSIF NEW.delivery_date BETWEEN date'2022-03-01' and date'2022-04-01' - 1 then
        INSERT INTO books_2022_03 VALUES (NEW.*);
    ELSIF NEW.delivery_date BETWEEN date'2022-04-01' and date'2022-05-01' - 1 then
        INSERT INTO books_2022_04 VALUES (NEW.*);
    ELSIF NEW.delivery_date BETWEEN date'2022-05-01' and date'2022-06-01' - 1 then
        INSERT INTO books_2022_05 VALUES (NEW.*);
    ELSIF NEW.delivery_date BETWEEN date'2022-06-01' and date'2022-07-01' - 1 then
        INSERT INTO books_2022_06 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'This date not in your partitions. Add a partition.';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Ограничения

С реализацией секционирования через наследование связаны следующие ограничения:

PostgreSQL не может проверить автоматически, являются ли все ограничения CHECK взаимно исключающими. Поэтому безопаснее будет написать код для формирования дочерних таблиц и создания или изменения связанных объектов, чем делать это вручную.
Индексы и внешние ключи относятся к определённой таблице, но не к её иерархии наследования.
В приведенном выше примере предполагается, что значения ключевых столбцов нельзя изменить, чтобы переместить строку в другую секцию. Попытка сделать это не удастся из-за ограничений CHECK. Если нужно обрабатывать такие случаи, добавьте соответствующие триггеры обновления для дочерних таблиц.
Если вы используете команды VACUUM или ANALYZE, нужно запускать их отдельно для родительской и каждой дочерней таблицы.
Операторы INSERT с выражением ON CONFLICT не будут работать ожидаемым образом, так как действие ON CONFLICT выполняется только в случае нарушений уникальности в указанной таблице, а не его дочерних таблицах.
Необходимо создавать триггеры для направления строк в дочерние таблицы. Триггеры сложно написать, и они медленнее, чем маршрутизация, выполняемая при декларативном секционировании.

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней