5.9. Секционирование

5.9.1. Обзор

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

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

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

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

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

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

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

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

5.9.2. Реализация секционирования

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

1. Создайте "главную" таблицу, от которой будут унаследованы все секции.

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

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

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

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.

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

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

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

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

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

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

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

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

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 соответствующих секций.

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

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

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

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'
-- возможно, дополнительная подготовка данных
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_TIMESTAMP, нельзя оптимизировать, так как планировщик не знает, в какую секцию попадёт значение функции во время выполнения.

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

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