Партиционирование таблиц

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

Обзор

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

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

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

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

  • Редко используемые данные можно перенести на более дешевые и медленные носители.

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

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

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

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

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

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

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

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

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

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

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

Кроме того, партиции могут быть сторонними таблицами, хотя при этом требуется немалая доля осмотрительности, поскольку именно пользователь отвечает за то, чтобы содержимое сторонней таблицы удовлетворяло правилу партиционирования. Также имеются и другие ограничения. Подробную информацию см. на справочной странице команды 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);

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

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

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

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. Указанный ключ партиционирования может перекрываться с ключом партиционирования родительской таблицы, хотя при указании границ вложенной партиции следует соблюдать осторожность, чтобы принимаемое ей множество данных входило в подмножество, допускаемое собственными границами партиции; система не пытается проверить, так ли это на самом деле.

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

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

  1. Создайте для партиционированной таблицы индекс по ключевому столбцу (или столбцам), а также любые другие индексы, которые могут понадобиться. (В ключевом индексе нет особой необходимости, но в большинстве сценариев он полезен). Это автоматически создает соответствующий индекс для каждой партиции, и все партиции, которые вы создаете или присоединяете позже, тоже будут содержать индекс. Индекс или ограничение уникальности, декларируемые для партиционированной таблицы, являются «виртуальными» наравне с самой этой таблицей: фактические данные находятся в дочерних индексах отдельных таблиц-партиций.
CREATE INDEX ON measurement (logdate);
  1. Убедитесь, что параметр конфигурации enable_partition_pruning в qhb.conf не выключен. Если это так, запросы не будут оптимизированы должным образом.

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

Обслуживание партиций

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

Самый простой вариант удаления старых данных — удалить ненужную партицию:

DROP TABLE measurement_y2006m02;

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

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

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

Это позволяет выполнять дальнейшие операции с данными до удаления таблицы. Например, зачастую это подходящее время для проведения резервного копирования данных с помощью COPY, qhb_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;

В качестве альтернативы иногда удобнее создать новую таблицу вне структуры партиций, а затем сделать ей подходящую партицию. Это позволяет загружать, проверять и преобразовывать данные до их появления в партиционированной таблице. Кроме того, операция ATTACH PARTITION требует для партиционированной таблицы только блокировку SHARE UPDATE EXCLUSIVE, в отличие от команды CREATE TABLE ... PARTITION OF, требующей блокировку ACCESS EXCLUSIVE, поэтому она удобнее для выполнения параллельных операций с партиционированной таблицей. Избежать утомительного повторения определения родительской таблицы помогает вариант 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'
-- возможно, какая-то дополнительная работа по подготовке данных

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

Перед запуском команды ATTACH PARTITION рекомендуется создать ограничение CHECK для присоединяемой таблицы, соответствующее ожидаемому ограничению партиции, как показано выше. Таким образом, система сможет пропустить сканирование, которому в противном случае потребуется проверить неявное ограничение партиции. Без ограничения CHECK таблица будет сканироваться для проверки ограничения партиции, при этом удерживая блокировку ACCESS EXCLUSIVE для этой партиции. После завершения ATTACH PARTITION рекомендуется сбросить ставшее лишним ограничение CHECK. Если присоединяемая таблица сама является партиционированной, то каждая из ее вложенных партиций будет рекурсивно блокироваться и сканироваться до тех пор, пока не встретится подходящее ограничение 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 CONCURRENTLY 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 для 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
);
  1. Создайте несколько «дочерних» таблиц, каждая из которых наследуется от корневой таблицы. Обычно в этих таблицах нет добавочных столбцов, кроме набора, унаследованного от корневой. Как и при декларативном партиционировании, эти таблицы во всех отношениях являются обычными таблицами QHB (или сторонними таблицами).
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);

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

    Типичные примеры:

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);
  1. Для каждой дочерней таблицы создайте индекс по ключевому столбцу (или столбцам), а также любые другие индексы, которые могут понадобиться.
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);
  1. Мы хотим, чтобы приложение могло сказать 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 запускает триггеры, поэтому если вы используете вариант с триггерами, эту команду можно применять как обычно.

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

  1. Убедитесь, что в qhb.conf не выключен параметр конфигурации constraint_exclusion, иначе дочерние таблицы могут сканироваться, когда в этом нет необходимости.

Как можно видеть, сложная иерархия таблиц может потребовать значительный объем кода 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'
-- возможно, какая-то дополнительная работа по подготовке данных
ALTER TABLE measurement_y2008m02 INHERIT measurement;

Ограничения применимости партиционирования через наследование

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

  • Не существует автоматического способа проверить, являются ли все ограничения CHECK взаимоисключающими. Безопаснее создать код, генерирующий дочерние таблицы и создающий и/или изменяющий связанные объекты, чем каждый раз делать это вручную.

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

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

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

ANALYZE measurement;

будет обрабатывать только корневую таблицу.

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

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

Отсечение партиций

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

SET enable_partition_pruning = on;                 -- по умолчанию
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, что делает его медленным по сравнению с отсечением партиций, но иногда это можно использовать как преимущество: поскольку ограничения можно определить даже для декларативно партиционированных таблиц, в дополнение к их внутренним границам партиций, исключение по ограничению может исключить из плана запроса дополнительные партиции.

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

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

  • Исключение по ограничению применяется только при планировании запросов, в отличие от отсечения партиций, которое также может применяться и при их выполнении.

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

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

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

Оптимальные методы декларативного партиционирования

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

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

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

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

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

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

Copyright © KVANTOM LLC, 2019-2025