5.9. Разбиение

5.9.1. Обзор

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

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

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

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

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

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

В настоящий момент, PostgreSQL поддерживает разбиение через наследование таблиц. Каждое разбиение должно быть создано как таблица-потомок одной родительской таблицы. Родительская таблица сама обычно пуста; она существует только для предоставления самого набора данных. Перед тем как пытаться заняться разбиением, вы должны хорошо понимать наследование (см. Section 5.8).

В PostgreSQL могут быть реализованы следующие виды разбиений:

5.9.2. Реализация разбиений

Чтобы создать разбитую таблицу, сделайте следующее:

1. Создайте "мастер"-таблицу, от которой будут наследовать все разбиения.

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

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

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

3. Добавьте табличные ограничения к разбитым таблицам, чтобы определить разрешённые ключевые значения в каждом разбиении.

   Вот типичные примеры:

   CHECK ( x = 1 )
   CHECK ( country IN ( '0xfordshire', 'Buckinghamshire', 'Warwickshire' ))
   CHECK ( outletID >= 100 AND outletID < 200 )

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

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

   Это неправильно, потому что непонятно к какому разбиению будет относиться значение 200.

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

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

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

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

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

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

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

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

1. Мастер-таблицей будет measurement, объявленная выше.

2. Далее, мы создаём одно разбиение для каждого активного месяца:

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

   Каждый из этих разбиений является полноценной таблицей со своими собственными правами, но все они наследуют описания от таблицы measurement.

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

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

   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 для соответствующего разбиения.

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

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

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

5.9.3. Управление разбиениеями

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

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

DROP TABLE measurement_y2006m02;

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

Другой пример, когда часто более предпочтительно удалить разбиение из разбитой (мастер) таблицы, но оставить доступ к нему как к обычной таблице со своими правами:

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

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

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

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

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

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

5.9.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 во всех запросах, даже простых, что не даёт преимуществ.

5.9.5. Альтернативнные методы разбиения

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

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

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

Разбиения также могут быть выведены в определённом порядке с помошью представления с UNION ALL вместо табличного наследования. Например,

CREATE VIEW measurement AS
          SELECT * FROM measurement_y2006m02
UNION ALL SELECT * FROM measurement_y2006m03
...
UNION ALL SELECT * FROM measurement_y2007m11
UNION ALL SELECT * FROM measurement_y2007m12
UNION ALL SELECT * FROM measurement_y2008m01;

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

5.9.6. Предостережения

При использовании разбиений имейте в виду следующие предостережения:

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

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

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

  ANALYZE measurement;

  обработает только мастер-таблицу.

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

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

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

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