Документация по PostgreSQL 8.2devel
PrevFast BackwardChapter 5. Определение данныхFast ForwardNext

5.9. Разделение

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

5.9.1. Обзор

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

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

В настоящий момент, 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_y2004m02 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2004m03 ( ) INHERITS (measurement);
..
CREATE TABLE measurement_y2005m11 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2005m12 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2006m01 ( ) INHERITS (measurement);

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

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

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

    CREATE TABLE measurement_y2004m02 (
    CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2004m03 (
    CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) INHERITS (measurement);
...
CREATE TABLE measurement_y2005m11 (
    CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2005m12 (
    CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m01 (
    CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
) INHERITS (measurement);

  4. Предположительно нам также понадобятся индексы для ключевых колонок: 

    CREATE INDEX measurement_y2004m02_logdate ON measurement_y2004m02 (logdate);
CREATE INDEX measurement_y2004m03_logdate ON measurement_y2004m03 (logdate);
...
CREATE INDEX measurement_y2005m11_logdate ON measurement_y2005m11 (logdate);
CREATE INDEX measurement_y2005m12_logdate ON measurement_y2005m12 (logdate);
CREATE INDEX measurement_y2006m01_logdate ON measurement_y2006m01 (logdate);

    На данном этапе мы не добавляем других индексов.

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

    CREATE OR REPLACE RULE measurement_current_partition AS
ON INSERT TO measurement
DO INSTEAD
    INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );

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

    CREATE RULE measurement_insert_y2004m02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
DO INSTEAD
    INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );
...
CREATE RULE measurement_insert_y2005m12 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
DO INSTEAD
    INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );
CREATE RULE measurement_insert_y2006m01 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
DO INSTEAD
    INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );

    Заметим, что выражение WHERE в каждом правиле точно совпадает с ограничением целостности CHECK в соответствующем разделе.

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

    ALTER TABLE measurement_y2003mm02 NO INHERIT measurement

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

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

    CREATE TABLE measurement_y2006m02 (LIKE measurement WITH DEFAULTS);
\COPY measurement_y2006m02 FROM 'measurement_y2006m02'
UPDATE ...
ALTER TABLE measurement_y2006m02 ADD CONSTRAINT y2006m02 CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' );
ALTER TABLE measurement_y2006m02 INHERIT MEASUREMENT;

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

Не забывайте о следующих предупреждениях:

Разделение может быть слито, используя представление с UNION ALL: 

CREATE VIEW measurement AS
          SELECT * FROM measurement_y2004m02
UNION ALL SELECT * FROM measurement_y2004m03
...
UNION ALL SELECT * FROM measurement_y2005m11
UNION ALL SELECT * FROM measurement_y2005m12
UNION ALL SELECT * FROM measurement_y2006m01;

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

5.9.3. Разделение и исключение ограничений целостности

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

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

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

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

SET constraint_exclusion = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-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 >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2004m02 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2004m03 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
...
         ->  Seq Scan on measurement_y2005m12 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2006m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)

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

SET constraint_exclusion = on;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-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 >= '2006-01-01'::date)
         ->  Seq Scan on measurement_y2006m01 measurement  (cost=0.00..30.38 rows=543 width=0)
               Filter: (logdate >= '2006-01-01'::date)

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

Помните о следующих предупреждениях:

PrevHomeNext
НаследованиеUpДругие объекты базы данных