Партиционирование в PostgreSQL

Материал из Национальной библиотеки им. Н. Э. Баумана
Последнее изменение этой страницы: 10:09, 15 сентября 2018.
PostgreSQL
Postgresql-logo.png
Разработчики: сообщество PostgreSQL
Выпущена: 8 July 1996 года; 22 years ago (1996-07-08)
Постоянный выпуск: 10.5 (9 августа 2018)
Состояние разработки: активное
Написана на: Си
Операционная система: Кросс-платформенное
Локализация: Английский язык
Тип ПО: реляционная СУБД
Веб-сайт www.postgresql.org

Партиционирование в PostgreSQL — это раз­би­е­ние таб­лиц, содер­жа­щих боль­шое коли­че­ство запи­сей, на логи­че­ские части для повышения скорости и удобства выполнения запросов.

Обзор

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

  • Скорость выполнения запросов может возрасти в некоторых ситуациях, особенно если большинство запрашиваемых строк находится в одной партиции или в небольшом их числе.
  • Когда происходит чтение или обновление большого числа строк внутри одной партиции, скорость запроса может быть увеличена за счёт последовательного сканирования партиции вместо доступа по индексу или случайного доступа к данным по всей таблице.
  • Добавление и удаление одновременно большого массива данных может быть выполнено с помощью добавления или удаления партиций, если это было учтено при проектировании. Применение ALTER TABLE DETACH PARTITION или удаление отдельной партиции при помощи DROP TABLE работают быстрее, чем удаление строк по отдельности. Кроме того, эти команды позволяют избежать переполнения VACUUM, вызванного множеством DELETE.
  • Редко используемые данные могут быть перенесены в более дешевые и медленные хранилища.
Пример партиционированной таблицы[Источник 1]

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

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

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

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

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

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

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

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

Невозможно превратить обычную таблицу в партиционированную или наоборот. Тем не менее, можно добавить обычную или партиционированную таблицу, содержащую данные, как партицию в партиционированную таблицу. Также можно удалить партицию из патиционированной таблицы, превратив её в обычную таблицу. Для этого обратитесь к подкомандам ATTACH PARTITION и DETACH PARTITION в разделе ALTER TABLE. [Источник 4]

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

  • Ограничения CHECK и NOT NULL всегда наследуются всеми партициями. Ограничения CHECK, помеченные как NO INHERIT, не могут быть использованы в партиционированных таблицах.
  • Можно использовать ONLY для добавления или удаления ограничения в партиционированной таблице, только если она не одержит партиций. Если у таблицы есть партиции, то использование ONLY для добавления или удаления ограничения вызовет ошибку. Вместо этого можно добавлять и удалять ограничения, не представленные в родительской таблице, напрямую в партиции. Так как партиционированные таблицы не содержат данные напрямую, попытки использовать TRUNCATE ONLY на партиционированной таблице будут всегда возвращать ошибку.
  • Партиции не могут иметь колонки, не представленные в родительской таблице. Нельзя добавлять колонки при создании партиции через CREATE TABLE, а также после использования ALTER TABLE. Таблицы могут быть добавлены как партиции с помощью ALTER TABLE ... ATTACH PARTITION при условии, что их колонки полностью совпадают с родительскими, включая колонки OID.
  • Нельзя удалить ограничение NOT NULL в колонке партиции, если ограничение представлено в родительской таблице.

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

Пример

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

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

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

Для использования декларативного партиционирования понадобятся следующие шаги:

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

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

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

Убедитесь, что параметр constraint_exclusion в postgresql.conf не отключён. Если он отключён, то запросы оптимизированы не будут.

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

Управление партициями

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

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

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

Ограничения

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

  • Невозможно создать автоматически индексы соответствия для всех партиций. Индексы должны быть добавлены к каждой партиции отдельной командой. Это также означает, что нельзя задавать первичные ключи, уникальные ограничения и исключающие ограничения, охватывающие все партиции. Можно задавать ограничения только для каждой отдельной партиции отдельно.
  • Так как первичные ключи в партиционированных таблицах не поддерживаются, не поддерживаются и внешние ключи к партиционированным таблицам и внешние ключи из партиционированных таблиц к любым другим.
  • Использование условия ON CONFLICT с партиционированными таблицами вызывает ошибку, потому что уникальные или исключающие условия могут быть созданы только для индивидуальных партиций. Не поддерживается условие однозначности (или исключающих ограничений) на всю иерархию партиционирования.
  • UPDATE, перемещающий строку из одной партиции в другую, вызывает ошибку, потому что новое значение строки не может соответствовать неявным ограничениям исходной партиции.
  • Если необходимо, триггеры на строки должны быть определены для конкретных партиций, а не для партиционированной таблицы целиком. [Источник 7]

Реализация партиционирования через наследование

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

  • Партиционирование предписывает, что все партиции должны иметь одинаковый набор колонок как у родителя, но наследование позволяет детям иметь дополнительные колонки, отсутствующие у родителя.
  • При наследовании таблиц возможно множественное наследование.
  • Декларативное партиционирование ограничено партиционированием по списку или по интервалам, в то время как наследование таблиц позволяет разделять данные способом, который определяет пользователь. (Заметим, однако, что если исключение по ограничению не сможет эффективно отбирать партиции, то скорость выполнения запросов будет сильно хуже.)
  • Некоторые операции требуют более строгую блокировку при декларативном партиционировании, чем при наследовании таблиц. Например, добавление или удаление партиции в или из партиционированной таблицы требует блокировки ACCESS EXCLUSIVE на родительской таблице, тогда как достаточно блокировки SHARE UPDATE EXCLUSIVE при обычном наследовании.

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

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

Создайте "основную" (англ. "master") таблицу, от которой будут наследоваться все партиции. Эта таблица не будет содержать данные. Для неё не нужно создавать ограничений, если вы не хотите, чтобы они не были применены ко всем партициям. Также нет смысла определять для неё любые индексы или уникальные ограничения. В нашем примере основная таблица — это таблица измерений, определённая выше.

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

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

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

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

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

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

Мы хотим, чтобы наше приложение выполняло запрос 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 каждой партиции.

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

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

Другой подход для направления данных в нужную партицию — задать правила (англ. "rules") для основной таблицы. Например:

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

Правило несёт некоторую дополнительную нагрузку по сравнению с триггером, но она одна на один запрос вместо одной на строку, так что этот метод может быть полезен при массовой вставке (англ. "bulk-insert"). В большинстве случаев, триггер лучше по скорости.

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

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

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


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

Последующие поддержка партиций

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

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'
-- possibly some other data preparation work
ALTER TABLE measurement_y2008m02 INHERIT measurement;

Замечания

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

  • Не существует автоматического способа проверить, что все CHECK ограничения взаимно не пересекаются. Безопаснее написать код, который генерирует партиции и создаёт и/или изменяет ассоциированные объекты, вместо того, чтобы описывать каждый вручную.
  • Схемы, представленные здесь, предполагают, что ключ партиционирования никогда не изменяется или по крайней мере не изменяется настолько, что потребуется переместить данные в новую партицию. UPDATE, который попытается это сделать, вызовет ошибку из-за исключения CHECK. Если вам надо обрабатывать подобные случаи, вы можете поместить подходящие триггеры на обновление в партиции, но это усложнит управление всей структурой.
  • Если вы используете команды VACUUM или ANALYZE, не забывайте применять их к каждой партиции отдельно. Команды вида:
        ANALYZE measurement;
    
    обрабатывают только главную таблицу (англ. "master table")
  • Запросы с INSERT с условием ON CONFLICT, вероятно, не будут работать так, как ожидается. Условие ON CONFLICT применяется только в единичных случаях нарушения в конкретном указанном отношении, но не в дочерних отношениях.
  • Триггеры или правила нужды для определения строки в нужную партицию, если приложение явно не задаёт схему партиционирования. Триггеры, возможно, будет сложнее написать, и они будут работать медленнее по сравнению со встроенным распределением кортежей при декларативном партиционировании. [Источник 8]

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

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

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

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

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

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 находятся константы (или ). Например, сравнение с изменяемой функцией (англ. "non-immutable function"), такой как CURRENT_TIMESTAMP, не может быть оптимизировано, потому что планировщик не знает, к какой партиции потребуется обратиться в результате выполнения функции.
  • Ограничения на партиции должны быть достаточно простыми, чтобы планировщик мог без проблем отбрасывать ненужные. Используйте простые проверки на эквивалентность для партиционирования по списку, или простые проверки на вхождения в интервалы для партиционированию по интервалам, как показано в предыдущих примерах. Есть хорошее эмпирическое правило, по которому ограничения на партиции должны состоять только из сравнений партиционированных колонок и констант с использованием операторов, индексируемых в B-дереве. Это применимо даже к партиционированным таблицам, потому что ключами партиционирования могут быть только колонки, индексируемые в B-дереве (Это не проблема, если вы используете декларативное партиционирование, так как планировщик достаточно легко обрабатывает автоматически сгенерированные ограничения.)
  • Все ограничения во всех партициях главной таблицы (англ. "master table") проверяются при процедуре исключения по ограничению, так что большое число партиций, вероятно, значительно увеличит время выполнения запроса. Партиционирование с использованием представленных выше техник хорошо работает с чуть больше чем сотней партиций. Не пытайтесь использовать много тысяч партиций. [Источник 9]

Сравнение обычной и партицированной таблицы

Для начала, скачаем и запустим Docker-контейнер с postgres

docker pull postgres:latest
docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres

Переходим в консоль контейнера

docker exec -it some-postgres bash

Меняем пользователя на postgres

su postgres

Далее для работы потребуются две утилиты — psql и pgbench.

С помощью первой утилиты можно выполнять запросы к БД прямо из консоли. С помощью второй — тестировать производительность запросов.

Инициализируем pgbench следующей командой:

pbgench -i postgres -s 600

Создастся таблица pgbench_accounts, содержащая 60000000 записей, которая в дальнейшем будет партиционирована.

Запустим тест на производительность в цикле. Каждые 10 секунд отчёт будет сохраняться в файл. Для этого потребуется следующий shell-скрипт, который назовём logging.sh:

while true
do
    date
    pgbench -T 10 -c 2 postgres
done 2>&1 | tee pgbench.log

Необходимо скопировать лог-файл из контейнера на основную систему для дальнейшего анализа.

docker cp some-postgres:/var/lib/postgresql/pgbench.log ~/pgbench_log_without_part.log

Создание партиций в psql:

do $$
declare
    i int4;
    aid_min INT4;
    aid_max INT4;
begin
    for i in 1..67
    loop
        aid_min := (i - 1) * 1000000 + 1;
        aid_max := i * 1000000;
        execute format('CREATE TABLE pgbench_accounts_p_%s ( like pgbench_accounts including all )', i );
        execute format('ALTER TABLE pgbench_accounts_p_%s inherit pgbench_accounts', i);
        execute format('ALTER TABLE pgbench_accounts_p_%s add constraint partitioning_check check ( aid >= %s AND aid <= %s )', i, aid_min, aid_max );
    end loop;
end;
$$;

Создадутся 67 таблиц, наследованных от pgbench_accounts, которые будут использованы под партиции.

Проверка, что ограничения установлены корректно:

explain analyze select * from pgbench_accounts where aid = 1234567;

Если всё сделано правильно, появится такое сообщение:

 Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.56..8.58 rows=1 width=97) (actual time=3.706..3.717 rows=1 loops=1)
   Index Cond: (aid = 1234567)
 Planning time: 0.933 ms
 Execution time: 3.815 ms
(4 rows)

 Append  (cost=0.56..16.74 rows=2 width=224) (actual time=0.023..0.052 rows=1 loops=1)
   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.56..8.58 rows=1 width=97) (actual time=0.015..0.023 rows=1 loops=1)
         Index Cond: (aid = 1234567)
   ->  Index Scan using pgbench_accounts_p_2_pkey on pgbench_accounts_p_2  (cost=0.14..8.16 rows=1 width=352) (actual time=0.009..0.013 rows=0 loops=1)
         Index Cond: (aid = 1234567)
 Planning time: 8.986 ms
 Execution time: 0.111 ms
(7 rows)

Создание функции выбора партиции и триггера операции вставки в psql:

create function partition_for_accounts() returns trigger as $$
DECLARE
    v_parition_name text;
BEGIN
    v_parition_name := format( 'pgbench_accounts_p_%s', 1 + ( NEW.aid - 1 ) / 1000000 );
    execute 'INSERT INTO ' || v_parition_name || ' VALUES ( ($1).* )' USING NEW;
    return NULL;
END;
$$ LANGUAGE plpgsql;

create trigger partition_users before insert on pgbench_accounts for each row execute procedure partition_for_accounts();

С помощью psql создадим sql-скрипт, который будет перемещать данные последовательно по 1000 строк из основной таблицы в партиционированную. Параллельно запустим скрипт logging.sh, который будет замерять производительность. Таким образом моделируется добавление партиции к уже существующей огромной таблице без прерывания работоспособности БД. [Источник 10]

\pset format unaligned
\pset tuples_only true
\o /tmp/run.batch.migration.sql
SELECT
    format(
        'with x as (DELETE FROM ONLY pgbench_accounts WHERE aid >= %s AND aid <= %s returning *) INSERT INTO pgbench_accounts_p_%s SELECT * FROM x;',
        i,
        i + 999,
        ( i - 1 ) / 1000000 + 1
    )
FROM
    generate_series( 1, 66660000, 1000 ) i;
\o

Запускаем перемещение данных:

psql -d postgres -f /tmp/run.batch.migration.sql

В другом окне запускаем logging.sh, предварительно удалив предыдущий лог-файл pgbench.log:

rm pgbench.log 
./logging.sh

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

select count(*) from only pgbench_accounts;

Должно появиться сообщение, что в таблице 0 записей.

 count 
-------
     0
(1 row)

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

Распределение среднего числа запросов к БД за одну секунду

Как видно, среднее число запросов сократилось после партиционирования приблизительно в два раза, с 500 до 250 транзакций в секунду.. Данный результат можно объяснить тем, что сама БД имеет небольшой объём (порядка 11 Гб), поэтому прироста производительности не наблюдается. Эти данные получены на тесте-аналоге TPC-B, встроенном в pgbench.

Наибольший прирост производительности достигается тогда, когда таблица индексов не влезает в оперативную память, и запросы выполняются в основном к маленькому подмножеству партиций. Основной таблица при этом содержит миллиарды записей. [Источник 11]

Видеоинструкция

Список использованных источников

  1. Партиционирование в PostgreSQL – Что? Зачем? Как? [Электронный ресурс]: Habr.com / Дата обращения: 10.09.2018. Режим доступа: https://habr.com/post/273933/
  2. Table Partitioning Overview [Электронный ресурс]: PostgreSQL.org / Дата обращения: 10.09.2018. Режим доступа: https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-OVERVIEW
  3. PostgreSQL: CREATE TABLE [Электронный ресурс]: PostgreSQL.org / Дата обращения: 10.09.2018. Режим доступа: https://www.postgresql.org/docs/postgrespro/10/sql-createtable.html
  4. PostgreSQL: ALTER TABLE [Электронный ресурс]: PostgreSQL.org / Дата обращения: 10.09.2018. Режим доступа: https://www.postgresql.org/docs/postgrespro/10/sql-altertable.html
  5. PostgreSQL: DDL INHERIT [Электронный ресурс]: PostgreSQL.org / Дата обращения: 10.09.2018. Режим доступа: https://www.postgresql.org/docs/postgrespro/10/ddl-inherit.html
  6. PostgreSQL: CREATE FOREIGN TABLE [Электронный ресурс]: PostgreSQL.org / Дата обращения: 10.09.2018. Режим доступа: https://www.postgresql.org/docs/postgrespro/10/sql-createforeigntable.html
  7. PostgreSQL: Declarative Partitioning [Электронный ресурс]: PostgreSQL.org / Дата обращения: 10.09.2018. Режим доступа: https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE
  8. PostgreSQL: Implementation Using Inheritance [Электронный ресурс]: PostgreSQL.org / Дата обращения: 10.09.2018. Режим доступа: https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-IMPLEMENTATION-INHERITANCE
  9. PostgreSQL: Partitioning and Constraint Exclusion [Электронный ресурс]: PostgreSQL.org / Дата обращения: 10.09.2018. Режим доступа: https://www.postgresql.org/docs/10/static/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION
  10. Partitioning – what? why? how? [Электронный ресурс]: Blog Depesz.com / Дата обращения: 10.09.2018. Режим доступа: https://www.depesz.com/2015/06/07/partitioning-what-why-how/
  11. How to scale PostgreSQL 10 using table inheritance and declarative partitioning [Электронный ресурс]: Blog Timescale.com / Дата обращения: 10.09.2018. Режим доступа: https://blog.timescale.com/scaling-partitioning-data-postgresql-10-explained-cd48a712a9a1