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

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

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

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

PostgreSQL предлагает встроенную поддержку следующих форм секционирования:

Разделение диапазона

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

Разделение списка

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

Разбиение хэша

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

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

Декларативное разбиение

PostgreSQL позволяет объявить, что таблица разделена на разделы. Разделенная таблица называется секционированной таблицей . Объявление включает описанный выше метод разделения , а также список столбцов или выражений, которые будут использоваться в качестве ключа разделения .

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

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

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

Разделы также могут быть внешними таблицами , хотя при этом требуется значительная осторожность, поскольку в этом случае пользователь несет ответственность за то, чтобы содержимое внешней таблицы удовлетворяло правилу разделения. Есть и некоторые другие ограничения. См. CREATE FOREIGN TABLE для получения дополнительной информации.

Пример

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

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

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

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

1) Создайте measurementтаблицу как секционированную, указав PARTITION BYпредложение, которое включает метод секционирования ( RANGEв данном случае) и список столбцов, которые следует использовать в качестве ключа секционирования.

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

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

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

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

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

...
CREATE TABLE measurement_y2007m11 PARTITION OF measurement
    FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');

CREATE TABLE measurement_y2007m12 PARTITION OF measurement
    FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
    TABLESPACE fasttablespace;

CREATE TABLE measurement_y2008m01 PARTITION OF measurement
    FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
    WITH (parallel_workers = 4)
    TABLESPACE fasttablespace;

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

Если вы хотите реализовать подразбиение, снова укажите PARTITION BYпункт в командах, используемых для создания отдельных разделов, например:

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01')
    PARTITION BY RANGE (peaktemp);

После создания разделов measurement_y2006m02, любые данные, вставленные в measurementкоторые сопоставляются measurement_y2006m02(или данные, которые непосредственно вставлены в measurement_y2006m02, что разрешено при условии, что его ограничение раздела удовлетворяется) будут дополнительно перенаправлены в один из его разделов на основе peaktempстолбца. Указанный ключ раздела может перекрываться с ключом родительского раздела, хотя следует соблюдать осторожность при указании границ подраздела, чтобы набор данных, которые он принимает, составлял подмножество того, что позволяют собственные границы раздела; система не пытается проверить, так ли это на самом деле.

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

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

3) Создайте индекс для ключевых столбцов, а также любые другие индексы, которые вам могут понадобиться, в многораздельной таблице. (Индекс ключа не является строго обязательным, но в большинстве случаев он полезен.) Это автоматически создает соответствующий индекс для каждого раздела, и все разделы, которые вы создаете или присоединяете позже, также будут иметь такой индекс. Индекс или уникальное ограничение, объявленное для многораздельной таблицы, является « виртуальным » так же, как и сама многораздельная таблица: фактические данные находятся в дочерних индексах отдельных таблиц разделов.

CREATE INDEX ON measurement (logdate);

4) Убедитесь, что параметр конфигурации enable_partition_pruningpostgresql.conf не отключен в . Если это так, запросы не будут оптимизированы должным образом.

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

Обслуживание Разделов

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

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

DROP TABLE measurement_y2006m02;

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

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

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02 CONCURRENTLY;

Это позволяет выполнять дальнейшие операции с данными перед их удалением. Например, это часто полезно для резервного копирования данных с помощью COPY, pg_dump или подобных инструментов. Это также может быть полезно для объединения данных в меньшие форматы, выполнения других операций с данными или создания отчетов. Первая форма команды требует ACCESS EXCLUSIVEблокировки родительской таблицы. Добавление CONCURRENTLYквалификатора, как во второй форме, позволяет операции отсоединения требовать только SHARE UPDATE EXCLUSIVEблокировки родительской таблицы, но см. ALTER TABLE ... DETACH PARTITIONподробности об ограничениях.

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

CREATE TABLE measurement_y2008m02 PARTITION OF measurement
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
    TABLESPACE fasttablespace;

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

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
  TABLESPACE fasttablespace;

ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );

\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work

ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );

Команда ATTACH PARTITIONтребует SHARE UPDATE EXCLUSIVEблокировки секционированной таблицы.

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

Аналогичным образом, если у многораздельной таблицы есть DEFAULTраздел, рекомендуется создать CHECKограничение, исключающее ограничение присоединяемого раздела. Если этого не сделать, то DEFAULTраздел будет просканирован, чтобы убедиться, что он не содержит записей, которые должны находиться в присоединяемом разделе. Эта операция будет выполняться при удержании ACCESS EXCLUSIVEблокировки на DEFAULTразделе. Если DEFAULTраздел сам является секционированной таблицей, то каждый из его разделов будет рекурсивно проверяться таким же образом, как и присоединяемая таблица, как указано выше.

Как объяснялось выше, можно создавать индексы для секционированных таблиц, чтобы они автоматически применялись ко всей иерархии. Это очень удобно, так как индексируются не только существующие разделы, но и любые разделы, которые будут созданы в будущем. Одним из ограничений является невозможность использования CONCURRENTLYквалификатора при создании такого секционированного индекса. Чтобы избежать длительного времени блокировки, можно использовать CREATE INDEX ON ONLYсекционированную таблицу; такой индекс помечается как недействительный, и разделы не получают автоматически применяемый индекс. Индексы разделов можно создавать индивидуально с помощью CONCURRENTLY, а затем прикреплять к индексу родителя с помощьюALTER INDEX .. ATTACH PARTITION. Как только индексы для всех разделов присоединены к родительскому индексу, родительский индекс автоматически помечается как действительный. Пример:

CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);

CREATE INDEX measurement_usls_200602_idx
    ON measurement_y2006m02 (unitsales);
ALTER INDEX measurement_usls_idx
    ATTACH PARTITION measurement_usls_200602_idx;
...

Этот метод также можно использовать с ограничениями и UNIQUE; PRIMARY KEYиндексы создаются неявно при создании ограничения. Пример:

ALTER TABLE ONLY measurement ADD UNIQUE (city_id, logdate);

ALTER TABLE measurement_y2006m02 ADD UNIQUE (city_id, logdate);
ALTER INDEX measurement_city_id_logdate_key
    ATTACH PARTITION measurement_y2006m02_city_id_logdate_key;
...

Ограничения

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

  • Ограничения уникальности (и, следовательно, первичные ключи) в секционированных таблицах должны включать все столбцы ключей секционирования. Это ограничение существует, потому что отдельные индексы, составляющие ограничение, могут напрямую обеспечивать уникальность только в своих собственных разделах; следовательно, сама структура разделов должна гарантировать отсутствие дубликатов в разных разделах.
  • Невозможно создать ограничение исключения, охватывающее всю секционированную таблицу. Такое ограничение возможно только для каждого листового раздела по отдельности. Опять же, это ограничение связано с невозможностью применять межраздельные ограничения.
  • BEFORE ROWтриггеры on INSERTне могут изменить, какой раздел является конечным пунктом назначения для новой строки.
  • Смешивание временных и постоянных отношений в одном дереве разделов не допускается. Следовательно, если многораздельная таблица постоянна, то должны быть и ее разделы, а также, если многораздельная таблица является временной. При использовании временных отношений все члены дерева разделов должны быть из одного сеанса.

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

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

  • Разделы не могут иметь столбцы, которых нет в родительском разделе. Невозможно указать столбцы при создании разделов с помощью CREATE TABLE, а также невозможно добавить столбцы в разделы постфактум с помощью ALTER TABLE. Таблицы могут быть добавлены как раздел ALTER TABLE ... ATTACH PARTITIONтолько в том случае, если их столбцы точно соответствуют родителю.
  • Оба CHECKи NOT NULLограничения секционированной таблицы всегда наследуются всеми ее секциями. CHECKпомеченные ограничения NO INHERITнельзя создавать в многораздельных таблицах. Вы не можете удалить NOT NULLограничение для столбца раздела, если такое же ограничение присутствует в родительской таблице.
  • Добавление ONLYили удаление ограничения только для секционированной таблицы поддерживается, если нет секций. Если разделы существуют, их использование ONLYприведет к ошибке. Вместо этого ограничения на сами разделы могут быть добавлены и (если они отсутствуют в родительской таблице) удалены.
  • Поскольку секционированная таблица сама по себе не содержит никаких данных, попытки использовать TRUNCATE ONLYее с секционированной таблицей всегда будут возвращать ошибку.

Разделение с использованием наследования

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

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

Пример

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

1) Создайте « корневую » таблицу, от которой будут наследоваться все « дочерние » таблицы. Эта таблица не будет содержать данных. Не определяйте никаких проверочных ограничений для этой таблицы, если только вы не собираетесь применять их в равной степени ко всем дочерним таблицам. Также нет смысла определять для него какие-либо индексы или уникальные ограничения. В нашем примере корневая таблица — это measurementтаблица, определенная изначально:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

2) Создайте несколько « дочерних » таблиц, каждая из которых наследует корневую таблицу. Обычно эти таблицы не добавляют никаких столбцов к набору, унаследованному от корня. Как и в случае с декларативным секционированием, эти таблицы во всех отношениях являются обычными таблицами PostgreSQL (или внешними таблицами).

CREATE TABLE measurement_y2006m02 () INHERITS (measurement);
CREATE TABLE measurement_y2006m03 () INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 () INHERITS (measurement);
CREATE TABLE measurement_y2007m12 () INHERITS (measurement);
CREATE TABLE measurement_y2008m01 () INHERITS (measurement);

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

Типичными примерами могут быть:

CHECK ( x = 1 )
CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
CHECK ( outletID >= 100 AND outletID < 200 )

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

CHECK ( outletID BETWEEN 100 AND 200 )
CHECK ( outletID BETWEEN 200 AND 300 )

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

CREATE TABLE measurement_y2006m02 (
    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2006m03 (
    CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);

...
CREATE TABLE measurement_y2007m11 (
    CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2007m12 (
    CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' )
) INHERITS (measurement);

CREATE TABLE measurement_y2008m01 (
    CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
) INHERITS (measurement);

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

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

5) Мы хотим, чтобы наше приложение могло сказать INSERT INTO measurement ...и перенаправить данные в соответствующую дочернюю таблицу. Мы можем организовать это, присоединив к корневой таблице подходящую триггерную функцию. Если данные будут добавляться только к последнему дочернему элементу, мы можем использовать очень простую триггерную функцию:

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

После создания функции мы создаем триггер, который вызывает функцию триггера:

CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE FUNCTION measurement_insert_trigger();

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

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

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.logdate >= DATE '2006-02-01' AND
         NEW.logdate < DATE '2006-03-01' ) THEN
        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
            NEW.logdate < DATE '2006-04-01' ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
    ...
    ELSIF ( NEW.logdate >= DATE '2008-01-01' AND
            NEW.logdate < DATE '2008-02-01' ) THEN
        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Определение триггера такое же, как и раньше. Обратите внимание, что каждый IFтест должен точно соответствовать CHECKограничению для своей дочерней таблицы.

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

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

CREATE RULE measurement_insert_y2006m02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
DO INSTEAD
    INSERT INTO measurement_y2006m02 VALUES (NEW.*);
...
CREATE RULE measurement_insert_y2008m01 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' )
DO INSTEAD
    INSERT INTO measurement_y2008m01 VALUES (NEW.*);

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

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

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

6) Убедитесь, что параметр конфигурации limited_exclusion не отключен вpostgresql.conf ; в противном случае доступ к дочерним таблицам может быть ненужным.

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

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

Чтобы быстро удалить старые данные, просто удалите дочернюю таблицу, которая больше не нужна:

DROP TABLE measurement_y2006m02;

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

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

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

CREATE TABLE measurement_y2008m02 (
    CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )
) INHERITS (measurement);

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

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- possibly some other data preparation work
ALTER TABLE measurement_y2008m02 INHERIT measurement;

Сокращение секционирования

Сокращение секционирования — это метод оптимизации запросов, повышающий производительность декларативно секционированных таблиц. Например:

SET enable_partition_pruning = on;                 -- the default
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';

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

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

SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-------------------------------------------------------------------​----------------
 Aggregate  (cost=188.76..188.77 rows=1 width=8)
   ->  Append  (cost=0.00..181.05 rows=3085 width=0)
         ->  Seq Scan on measurement_y2006m02  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m11  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2007m12  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
               Filter: (logdate >= '2008-01-01'::date)

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

SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-------------------------------------------------------------------​----------------
 Aggregate  (cost=37.75..37.76 rows=1 width=8)
   ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
         Filter: (logdate >= '2008-01-01'::date)

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

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

  • Во время инициализации плана запроса. Здесь можно выполнить отсечение разделов для значений параметров, которые известны на этапе инициализации выполнения. Разделы, обрезанные на этом этапе, не будут отображаться в запросах EXPLAINили EXPLAIN ANALYZE. Можно определить количество разделов, которые были удалены на этом этапе, наблюдая за свойством « Subplans Removed » в EXPLAINвыходных данных.
  • Во время фактического выполнения плана запроса. Здесь также может быть выполнена обрезка разделов для удаления разделов с использованием значений, которые известны только во время фактического выполнения запроса. Сюда входят значения из подзапросов и значения из параметров времени выполнения, например, из параметризованных объединений вложенных циклов. Поскольку значения этих параметров могут меняться много раз во время выполнения запроса, сокращение секций выполняется всякий раз, когда изменяется один из параметров выполнения, используемых при сокращении секций. Чтобы определить, были ли разделы удалены на этом этапе, требуется тщательная проверка loopsсвойства в EXPLAIN ANALYZEвыходных данных. Подпланы, соответствующие разным разделам, могут иметь для него разные значения в зависимости от того, сколько раз каждый из них был сокращен во время выполнения. Некоторые могут быть показаны как(never executed)если бы их каждый раз обрезали.

Сокращение разделов можно отключить с помощью параметра enable_partition_pruning .

Разделение и ограничение исключения

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

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

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

Значение по умолчанию (и рекомендуемое) для ограничения_исключения — это ни , onни off, а промежуточное значение, называемое partition, что приводит к тому, что этот метод применяется только к запросам, которые, вероятно, будут работать с секционированными таблицами наследования. Параметр onзаставляет планировщик проверять CHECKограничения во всех запросах, даже в простых, которые вряд ли принесут пользу.

К исключению ограничений относятся следующие предостережения:

  • Исключение ограничений применяется только во время планирования запроса, в отличие от сокращения секций, которое также может применяться во время выполнения запроса.
  • Исключение ограничений работает, только если предложение запроса WHEREсодержит константы (или внешние параметры). Например, сравнение с неизменяемой функцией, такой как CURRENT_TIMESTAMPне может быть оптимизировано, поскольку планировщик не может знать, в какую дочернюю таблицу может попасть значение функции во время выполнения.
  • Сохраняйте ограничения секционирования простыми, иначе планировщик не сможет доказать, что посещение дочерних таблиц не требуется. Используйте простые условия равенства для разделения списка или простые проверки диапазона для разделения диапазона, как показано в предыдущих примерах. Хорошее эмпирическое правило заключается в том, что ограничения секционирования должны содержать только сравнения столбцов секционирования с константами с использованием операторов индексации B-дерева, поскольку в ключе секционирования разрешены только индексируемые столбцы B-дерева.
  • Все ограничения для всех дочерних элементов родительской таблицы проверяются во время исключения ограничений, поэтому большое количество дочерних элементов может значительно увеличить время планирования запроса. Таким образом, устаревшее секционирование на основе наследования будет хорошо работать, возможно, с сотней дочерних таблиц; не пытайтесь использовать многие тысячи детей.

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

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

Одним из наиболее важных проектных решений будет столбец или столбцы, по которым вы разбиваете данные. Часто лучшим выбором будет секционирование по столбцу или набору столбцов, которые чаще всего появляются в WHEREпредложениях запросов, выполняемых в секционированной таблице. WHEREпредложения, совместимые с ограничениями, связанными с разделами, могут использоваться для удаления ненужных разделов. Однако вы можете быть вынуждены принять другие решения из-за требований PRIMARY KEYили UNIQUEограничения. Удаление нежелательных данных также является фактором, который следует учитывать при планировании стратегии разделения. Весь раздел может быть отсоединен достаточно быстро, поэтому может быть полезно разработать стратегию разделения таким образом, чтобы все данные, которые нужно удалить сразу, располагались в одном разделе.

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

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

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

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