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

примечание

Эта страница переведена при помощи нейросети GigaChat.

Предупреждение!

Изменено поведение оригинальной функциональности. Изменения описаны в рамках подраздела «Доработки Pangolin».

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

Обзор

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

В определенных случаях работа с таблицей становится значительно быстрее, особенно если большая часть активно используемых строк находится всего в одном или нескольких разделах. Партиционирование может заменить верхние уровни индексов, что повышает шанс того, что наиболее востребованные их части будут находиться в оперативной памяти.
Если запросы или обновления затрагивают значительную часть одного раздела, можно использовать последовательное сканирование этого раздела вместо индексного поиска. Это снижает количество случайных чтений, распределенных по всей таблице.
Если архитектура партиционирования построена с учетом сценариев использования, можно добавлять или удалять целые разделы вместо выполнения массовых операций. Например, команды DROP TABLE или ALTER TABLE DETACH PARTITION, применяемые для отдельного раздела, работают намного быстрее, чем массовое удаление строк, и не требуют последующей очистки (VACUUM).
Данные, к которым редко обращаются, можно переместить на более дешевые и медленные носители.

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

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

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

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

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

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

Доработки Pangolin

В СУБД Pangolin реализован механизм, который позволяет автоматически создавать партиции (секции) для таблиц при перемещении или вставке новых данных со значениями, не подходящими под критерий данных для существующих партиций. Подробнее о нем описано в разделе Интервальное нативное партиционирование. Также реализован механизм глобальных индексов, который описан в разделе Глобальные индексы и глобальные ограничения на партиционированные таблицы.

В СУБД Pangolin усовершенствован механизм «отсечение партиций». Подробнее о функциональности описано в разделе «Оптимизация механизма «отсечение партиций» для таблиц с композитным ключом партиционирования».

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

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

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

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

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

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

Пример

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

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

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

Порядок действий для использования декларативного партиционирования:

Создание таблицы measurement как партиционированной с помощью предложения PARTITION BY, где указывается метод партиционирования (RANGE) и столбцы, которые будут образовывать ключ партиционирования:
```
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
```
Для каждой партиции задаются границы, соответствующие методу и ключу партиционирования родительской таблицы. Пересечение границ с уже существующими партициями приведет к ошибке.

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

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

Ограничения CHECK, описывающие границы партиций, создаются автоматически.
Создание индексов. Индекс по ключевым столбцам (и любым другим) можно создать на партиционированной таблице:
```
CREATE INDEX ON measurement (logdate);
```
Такой индекс создает соответствующий индекс в каждой партиции, и все новые партиции также будут его иметь. Индекс или уникальное ограничение на партиционированной таблице являются «виртуальными» — фактические данные хранятся в индексах партиций.
Параметр enable_partition_pruning в postgresql.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;

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

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

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

CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);

Это создаст индекс, помеченный как invalid, и он не будет автоматически применен к существующим партициям. Индексы на партиции можно затем создать с ключевым словом CONCURRENTLY и присоединить их к индексу родительской таблицы с помощью ALTER INDEX ... ATTACH PARTITION. После присоединения индексов всех партиций родительский индекс автоматически будет помечен как valid, например:

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 выдаст ошибку для любых ограничений, кроме UNIQUE и PRIMARY KEY. Вместо этого ограничения можно добавлять на сами партиции и удалять их (если они отсутствуют в родительской таблице).
Поскольку партиционированная таблица не содержит собственных данных, попытка выполнить TRUNCATE ONLY для нее всегда приведет к ошибке.

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

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

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

Пример

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

Последовательность действий:

Создание корневой таблицы, от которой будут наследоваться все дочерние таблицы. Корневая таблица не будет содержать данных. Ограничения CHECK в ней не определяются, если нет необходимости, чтобы они применялись ко всем дочерним таблицам. Индексы и уникальные ограничения также не имеют смысла на этом уровне:
```
CREATE TABLE measurement (
    city_id   int NOT NULL,
    logdate   date NOT NULL,
    peaktemp  int,
    unitsales int
);
```
Создание дочерних таблиц, наследующих корневую. Обычно они не добавляют собственных столбцов. Такие таблицы — полноценные таблицы или внешние таблицы:
```
CREATE TABLE measurement_y2006m02 () INHERITS (measurement);
CREATE TABLE measurement_y2006m03 () INHERITS (measurement);
...
CREATE TABLE measurement_y2007m12 () INHERITS (measurement);
CREATE TABLE measurement_y2008m01 () INHERITS (measurement);
```

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

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 INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
...
```
Чтобы приложение могло выполнять 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 выполняет триггеры, поэтому при использовании триггерного подхода она будет работать как обычно.

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

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

Партиционирование и исключение по ограничению

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

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

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

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

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

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

Обзор​

Доработки Pangolin​

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

Пример​

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

Ограничения​

Партиционирование с использованием наследования​

Пример​

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

Ограничения​

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

Партиционирование и исключение по ограничению​

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

Обзор

Доработки Pangolin

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

Пример

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

Ограничения

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

Пример

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

Ограничения

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

Партиционирование и исключение по ограничению

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