5.10. Секционирование таблиц
Пред. НаверхГлава 5. Определение данныхНачало След.

5.10. Секционирование таблиц

5.10.1. Обзор
5.10.2. Декларативное секционирование
5.10.3. Реализация с использованием наследования
5.10.4. Секционирование и исключение по ограничению
5.10.5. Рекомендации по декларативному секционированию

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

5.10.1. Обзор

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание

В этой главе рассматривается декларативный синтаксис, поддерживаемый ядром Postgres Pro. По умолчанию в Postgres Pro Enterprise используется декларативный синтаксис, реализуемый расширением pg_pathman, как описано в Подразделе F.40.2.6. Если вместо него вы хотите использовать функциональность, встроенную в ядро, установите для параметра конфигурации partition_backend значение internal.

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

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

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

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

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

  • Ограничения CHECK вместе с NOT NULL, определённые в секционированной таблице, всегда наследуются всеми её секциями. Ограничения CHECK с характеристикой NO INHERIT в секционированных таблицах создавать нельзя.

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

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

  • Ограничение NOT NULL для столбца в секции нельзя удалить, если это ограничение существует в родительской таблице.

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

5.10.2.1. Пример

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

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);

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

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

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

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

    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_y2006m02 (logdate);
CREATE INDEX ON measurement_y2006m03 (logdate);
...
CREATE INDEX ON measurement_y2007m11 (logdate);
CREATE INDEX ON measurement_y2007m12 (logdate);
CREATE INDEX ON measurement_y2008m01 (logdate);

  4. Убедитесь в том, что параметр конфигурации constraint_exclusion не выключен в postgresql.conf. Иначе запросы не будут оптимизироваться должным образом.

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

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

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

Самый лёгкий способ удалить старые данные — просто удалить секцию, ставшую ненужной: 

DROP TABLE measurement_y2006m02;

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

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

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;

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

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

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

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

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, рекомендуется создать ограничение CHECK в присоединяемой таблице, соответствующее ожидаемому ограничению секции. Благодаря этому система сможет не сканировать таблицу для проверки неявного ограничения секции. Без этого ограничения CHECK таблицу нужно будет просканировать и убедиться в выполнении ограничения секции, удерживая блокировку ACCESS EXCLUSIVE в родительской таблице. После выполнения команды ATTACH PARTITION это ставшее ненужным ограничение CHECK при желании можно удалить.

5.10.2.3. Ограничения

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

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

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

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

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

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

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

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

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

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

  • Механизм наследования таблиц поддерживает множественное наследование.

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

  • Для некоторых операций с декларативным секционированием требуется более сильная блокировка, чем с использованием наследования. Например, для добавления или удаления секций из секционированной таблицы требуется установить блокировку ACCESS EXCLUSIVE в родительской таблице, тогда как в случае с обычным наследованием достаточно блокировки SHARE UPDATE EXCLUSIVE.

5.10.3.1. Пример

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

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

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

    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 PROCEDURE 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. Убедитесь в том, что параметр конфигурации constraint_exclusion не выключен в postgresql.conf. Иначе запросы не будут оптимизироваться должным образом.

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

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

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

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;

5.10.3.3. Ограничения

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

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

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

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

    ANALYZE measurement;

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

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

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

5.10.4. Секционирование и исключение по ограничению

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

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

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

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

SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement
  WHERE logdate >= DATE '2008-01-01';

                              QUERY PLAN
---------------------------------------------------------------------------
 Aggregate  (cost=158.66..158.68 rows=1 width=0)
   ->  Append  (cost=0.00..151.88 rows=2715 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m02 measurement
                      (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2006m03 measurement
                      (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
...
         ->  Seq Scan on measurement_y2007m12 measurement
                      (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01 measurement
                      (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)

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

SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement
  WHERE logdate >= DATE '2008-01-01';

                              QUERY PLAN
--------------------------------------------------------------------------
 Aggregate  (cost=63.47..63.48 rows=1 width=0)
   ->  Append  (cost=0.00..60.75 rows=1086 width=0)
         ->  Seq Scan on measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)
         ->  Seq Scan on measurement_y2008m01 measurement
                      (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2008-01-01'::date)

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

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

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

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

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

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

5.10.5. Рекомендации по декларативному секционированию

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

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

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

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

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

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

Пред. Наверх След.
5.9. Наследование Начало 5.11. Сторонние данные