Секционирование в PostgreSQL 10 и не только


    У многих достижений версии PostgreSQL 10 прописка в разделе Секционирование (Partitioning). И это справедливо: очевидно, что при переходе от 9.6 к 10 произошел мощный технологический скачок. В предыдущих версиях секции строили и управляли ими, теми средствами, что уже имелись: механизмом наследования со всеми его ограничениями и неудобствами.


    В версии 10 перешли к более специализированным механизмам и более привычному (в том числе для пользователей Oracle, а с этим приходится считаться) синтаксису. Этот скачок при переходе от 10 к версии 11 должен был подкрепиться важными дополнениями, которые должны расширить функциональность и улучшить производительность операций, использующих секционирование. Но из итогов последнего комитфеста (он закончился 8 апреля) видно, что не все задуманное удалось довести до рабочего состояния, а значит не все попадет в версию 11.


    К тому же последние пару лет параллельно велись разработки модуля pg_pathman в Postgres Professional. Некоторые важные возможности пересеклись, некоторые остались уникальны для PostgreSQL и pg_pathman (который работает с ванильной версией, то есть PostgreSQL 10 + pg_pathman дает уже вполне впечатляющую сумму функциональности). Об этом будет отдельная статья. Замечания, относящиеся к версии 11 и к pg_pathman для удобства выделены курсивом.


    Эта статья представляет собой переработанные и дополненные фрагменты книжки Nouveaulités de PostgreSQL 10. (с) Dalibo, перевод с французского Игоря Лёвшина (оригинал). Примеры из книги проверены, иногда адаптированы и локализованы для большей наглядности.


    Старый подход к секционированию


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


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


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


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


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


    Новый подход к секционированию


    В версии 10 появилась новая система секционирования, основанная на инфраструктуре, уже существовавшей в PostgreSQL.


    Целью разработчиков было упрощение создания и администрирования секционированных таблиц. К уже существующим SQL-операторам (например CREATE TABLE и ALTER TABLE) добавлены специальные предложения для создания, присоединения/отсоединения секций. Вот список изменений.


    • Создание и администрирование секций упростилось благодаря интеграции в ядро СУБД
    • Триггеры больше не нужны. Теперь:
      • вставка убыстрилась
      • появилось автоматическое перенаправление вставляемых данных в правильную секцию
      • генерируется ошибка в случае направления в неподходящую секцию
    • Теперь при работе с секциями:
      • можно присоединять/отсоединять секции
      • есть явные ограничения целостности секций
      • возможно секционирование по выражению в ключе разбиения
      • можно создавать подсекции
    • Изменения в системном каталоге:
      • новые столбцы в pg_class
      • новый каталог pg_partitioned_table

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


    Каталог pg_class был модифицирован и теперь содержит такую информацию :


    • является ли таблица секцией (если да, то: relispartition = 't')
    • секционирована ли таблица (если да, то: relkind = 'p') или это обычная таблица (relkind = 't')
    • внутреннее представление границ секций (relpartbound)

    Каталог pg_partitioned_table содержит следующие столбцы :


    Столбец Содержимое
    partrelid OID записи в pg_class для этой секционированной таблицы
    partstrat Стратегия секционирования: l = по списку (BY LIST), r = по диапазону (BY RANGE)
    partnatts Число столбцов в ключе разбиения
    partattrs Массив длины partnatts, указывающий, какие столбцы таблицы входят в ключ разбиения.*)
    partclass Для каждого столбца в ключе разбиения этот массив содержит OID применяемых классов операторов.
    partcollation Для каждого столбца в ключе разбиения этот массив содержит OID правила сортировки**)
    partexprs Деревья выражений для частей ключа разбиения.***)

    *) Например, значения 1 и 3 будут означать, что ключ разбиения составляют первый и третий столбцы таблицы. Ноль в этом массиве означает, что соответствующей частью ключа разбиения является выражение, а не ссылка на отдельный столбец.
    **) Для секционирования либо 0, если тип данных этого столбца не сортируемый.
    ***) Деревья выражений (в представлении nodeToString()) для частей ключа разбиения, что не являющихся простыми ссылками на столбцы. Этот список содержит один элемент для каждого нулевого значения в partattrs. Значением может быть NULL, если все части ключа разбиения являются простыми указаниями столбцов.


    Виды секционирования


    • по списку
    • по диапазону
    • по хешу
    • по выражению
    • по составному ключу
    • с подсекциями

    PostgreSQL 10 поддерживает все, кроме третьего. Мы сейчас остановимся на двух первых.


    Секционирование по списку значений


    Создадим основную таблицу и ее секции :


    habr_10=# CREATE TABLE parti_1(c1 integer, c2 text) PARTITION BY LIST (c1);
    CREATE TABLE
    
    habr_10=# CREATE TABLE parti_1_a PARTITION OF parti_1 FOR VALUES IN (1, 2, 3);
    CREATE TABLE
    
    habr_10=# CREATE TABLE parti_1_b PARTITION OF parti_1 FOR VALUES IN (4, 5);
    CREATE TABLE

    Мы можем отсоединять и подсоединять секции :


    Отсоединим :


    habr_10=# ALTER TABLE parti_1 DETACH PARTITION parti_1_a;

    Теперь это независимая таблица. Присоединим ее обратно :


    habr_10=# ALTER TABLE parti_1 ATTACH PARTITION parti_1_a FOR VALUES IN (1, 2, 3);

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


    habr_10=# INSERT INTO parti_1 VALUES (0);
    ERROR:  no PARTITION OF relation "parti_1" found for row
    DETAIL:  Partition key of the failing row contains (c1) = (0).
    
    habr_10=# INSERT INTO parti_1 VALUES (6);
    ERROR:  no PARTITION OF relation "parti_1" found for row
    DETAIL:  Partition key of the failing row contains (c1) = (6).

    Теперь введем корректные данные :


    habr_10=# INSERT INTO parti_1 VALUES (1);
    INSERT 0 1
    
    habr_10=# INSERT INTO parti_1 VALUES (2);
    INSERT 0 1
    
    habr_10=# INSERT INTO parti_1 VALUES (5);
    INSERT 0 1
    
    habr_10=# SELECT * FROM parti_1_a;
     c1 | c2 
    ----+----
      1 | 
      2 | 
    (2 rows)
    
    habr_10=# SELECT * FROM parti_1_b;
     c1 | c2 
    ----+----
      5 | 
    (1 row)

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


    habr_10=# SELECT * FROM ONLY parti_1;
     c1 | c2 
    ----+----
    (0 rows)

    Секционирование по диапазону


    Создадим основную таблицу и одну секцию :


    habr_10=# CREATE TABLE parti_2(c1 integer, c2 text) PARTITION BY RANGE (c1);
    CREATE TABLE
    
    habr_10=# CREATE TABLE parti_2_1 PARTITION OF parti_2 FOR VALUES FROM (1) to (100);
    CREATE TABLE

    Заполним секцию данными :


    habr_10=# INSERT INTO parti_2 VALUES (0);
    ERROR:  no PARTITION OF relation "parti_2" found for row
    DETAIL:  Partition key of the failing row contains (c1) = (0).
    
    habr_10=# INSERT INTO parti_2 VALUES (1);
    INSERT 0 1
    
    habr_10=# INSERT INTO parti_2 VALUES (2);
    INSERT 0 1
    
    habr_10=# INSERT INTO parti_2 VALUES (5);
    INSERT 0 1
    
    habr_10=# INSERT INTO parti_2 VALUES (101);
    ERROR:  no PARTITION OF relation "parti_2" found for row
    DETAIL:  Partition key of the failing row contains (c1) = (101).

    Если нет ни одной секции, соответствующей ключу разбиения, генерируется ошибка.


    Составной ключ разбиения


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


    habr_10=# CREATE TABLE parti_3(c1 integer, c2 text, c3 date)
        PARTITION BY RANGE (c1, c3);

    Добавим секцию, используя составной ключ разбиения :


    habr_10=# CREATE TABLE parti_3_a PARTITION of parti_3 FOR VALUES
        FROM (1,'2017-08-10') TO (100, '2017-08-11');

    Проиллюстрируем совместное использование разных табличных пространств на примере ниже. Начнем с создания табличных пространств. Напомним, что каталог табличного пространства должен существовать, быть пустым и принадлежать пользователю ОС, под которым запущен Postgres. Например:


    test#mkdir /tmp/tablespaces/ts0
    
    test#sudo chown postgres /tmp/tablespaces/ts0

    Теперь :


    habr_10=# CREATE TABLESPACE ts0 LOCATION '/tmp/tablespaces/ts0';
    CREATE TABLESPACE
    
    habr_10=# CREATE TABLESPACE ts1 LOCATION '/tmp/tablespaces/ts1';
    CREATE TABLESPACE
    
    habr_10=# CREATE TABLESPACE ts2 LOCATION '/tmp/tablespaces/ts2';
    CREATE TABLESPACE
    
    habr_10=# CREATE TABLESPACE ts3 LOCATION '/tmp/tablespaces/ts3';
    CREATE TABLESPACE

    Создадим секционированную таблицу и две секции :


    habr_10=# DROP TABLE parti_3;
    DROP TABLE

    (Секции удаляются каскадно)


    habr_10=# CREATE TABLE parti_3(c1 integer, c2 text, c3 date not null)
           PARTITION BY RANGE (c1, c3);
    CREATE TABLE
    
    habr_10=# CREATE TABLE parti_3_1 PARTITION OF parti_3
           FOR VALUES FROM (1,'2017-08-10') TO (100, '2017-08-11')
           TABLESPACE ts1;
    CREATE TABLE
    
    habr_10=# CREATE TABLE parti_3_2 PARTITION OF parti_3
           FOR VALUES FROM (100,'2017-08-11') TO (200, '2017-08-12')
           TABLESPACE ts2;
    CREATE TABLE

    Если величины не выходят за границы секций:


    habr_10=# INSERT INTO parti_3 VALUES (1, 'test', '2017-08-10');
    INSERT 0 1
    
    habr_10=# INSERT INTO parti_3 VALUES (150, 'test2', '2017-08-11');        
    INSERT 0 1

    Если величина c1 слишком мала :


    habr_10=# INSERT INTO parti_3 VALUES (0, 'test', '2017-08-10');
    ERROR:  no partition of relation "parti_3" found for row
    DETAIL : Partition key of the failing row contains (c1, c3) = (0, 2017-08-10).

    Если величина c3 (столбец с типом данных date) предшествует нижней границе временного диапазона :


    habr_10=# INSERT INTO parti_3 VALUES (1, 'test', '2017-08-09');
    ERROR:  no partition of relation "parti_3" found for row
    DETAIL : Partition key of the failing row contains (c1, c3) = (1, 2017-08-09).

    Специальные величины MINVALUE и MAXVALUE позволяют не указывать величину одной из границ. Например, секции parti_3_0 и parti_3_3 можно объявить как показано ниже и успешно вставить строки, которые выше вызвали сообщение об ошибке.


    Внимание: некоторые онлайн-статьи, которые были опубликованы до выпуска версии beta3, упоминают специальное значение UNBOUNDED, которое потом было заменено MINVALUE и MAXVALUE.


    habr_10=# CREATE TABLE parti_3_0 PARTITION OF parti_3
           FOR VALUES FROM (MINVALUE, MINVALUE) TO (1,'2017-08-10')
           TABLESPACE ts0;
    
    habr_10=# CREATE TABLE parti_3_3 PARTITION OF parti_3
           FOR VALUES FROM (200,'2017-08-12') TO (MAXVALUE, MAXVALUE)
           TABLESPACE ts3;

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


    habr_10=# ANALYZE parti_3;
    ANALYZE
    
    habr_10=# SELECT relname,relispartition,relkind,reltuples
               FROM pg_class WHERE relname LIKE 'parti_3%';
       relname    | relispartition | relkind | reltuples 
     -------------+----------------+---------+-----------
     parti_3      | f              | p       |         0
     parti_3_1    | t              | r       |         1
     parti_3_2    | t              | r       |         1
    (5 rows)

    Секции по умолчанию и автоматическое создание секций


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


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


    Надо иметь в виду, что в PostgreSQL 10 нет секционирования по хешу. Этот серьезный недостаток поправили в 11: соответствующий патч прошел комитфест, так что остается ждать выхода PostgreSQL 11. Если секционирование по хешу необходимо или желательно прямо сейчас, можно воспользоваться pg_pathman.


    Производительность вставки


    Таблица no_parti — несекционированная таблица. Cоздаем ее следующим образом :


    CREATE TABLE no_parti (c1 integer, c2 text);
    
    INSERT INTO no_parti SELECT i, 'something'
      FROM generate_series(0, 9999999) i;
    Time: 10097.098 ms (00:10.097)

    Таблицу parti_new создаем с помощью новой функциональности версии PostgreSQL 10 :


    CREATE TABLE parti_new (c1 integer, c2 text) PARTITION BY RANGE (c1);
    CREATE TABLE parti_new_1 PARTITION OF parti_new FOR VALUES FROM (      0) TO ( 1000000);
    CREATE TABLE parti_new_2 PARTITION OF parti_new FOR VALUES FROM (1000000) TO ( 2000000);
    CREATE TABLE parti_new_3 PARTITION OF parti_new FOR VALUES FROM (2000000) TO ( 3000000);
    CREATE TABLE parti_new_4 PARTITION OF parti_new FOR VALUES FROM (3000000) TO ( 4000000);
    CREATE TABLE parti_new_5 PARTITION OF parti_new FOR VALUES FROM (4000000) TO ( 5000000);
    CREATE TABLE parti_new_6 PARTITION OF parti_new FOR VALUES FROM (5000000) TO ( 6000000);
    CREATE TABLE parti_new_7 PARTITION OF parti_new FOR VALUES FROM (6000000) TO ( 7000000);
    CREATE TABLE parti_new_8 PARTITION OF parti_new FOR VALUES FROM (7000000) TO ( 8000000);
    CREATE TABLE parti_new_9 PARTITION OF parti_new FOR VALUES FROM (8000000) TO ( 9000000);
    CREATE TABLE parti_new_0 PARTITION OF parti_new FOR VALUES FROM (9000000) TO (10000000);
    
    INSERT INTO parti_new SELECT i, 'something'
      FROM generate_series(0, 9999999) i;
    Time: 11448.867 ms (00:11.449)

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


    CREATE TABLE parti_old (c1 integer, c2 text);
    CREATE TABLE parti_old_1 (CHECK (c1 BETWEEN       0 AND  1000000)) INHERITS (parti_old);
    CREATE TABLE parti_old_2 (CHECK (c1 BETWEEN 1000000 AND  2000000)) INHERITS (parti_old);
    CREATE TABLE parti_old_3 (CHECK (c1 BETWEEN 2000000 AND  3000000)) INHERITS (parti_old);
    CREATE TABLE parti_old_4 (CHECK (c1 BETWEEN 3000000 AND  4000000)) INHERITS (parti_old);
    CREATE TABLE parti_old_5 (CHECK (c1 BETWEEN 4000000 AND  5000000)) INHERITS (parti_old);
    CREATE TABLE parti_old_6 (CHECK (c1 BETWEEN 5000000 AND  6000000)) INHERITS (parti_old);
    CREATE TABLE parti_old_7 (CHECK (c1 BETWEEN 6000000 AND  7000000)) INHERITS (parti_old);
    CREATE TABLE parti_old_8 (CHECK (c1 BETWEEN 7000000 AND  8000000)) INHERITS (parti_old);
    CREATE TABLE parti_old_9 (CHECK (c1 BETWEEN 8000000 AND  9000000)) INHERITS (parti_old);
    CREATE TABLE parti_old_0 (CHECK (c1 BETWEEN 9000000 AND 10000000)) INHERITS (parti_old);
    
    CREATE OR REPLACE FUNCTION insert_into() RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $FUNC$
    BEGIN
      IF NEW.c1    BETWEEN       0 AND  1000000 THEN
        INSERT INTO parti_old_1 VALUES (NEW.*);
      ELSIF NEW.c1 BETWEEN 1000000 AND  2000000 THEN
        INSERT INTO parti_old_2 VALUES (NEW.*);
      ELSIF NEW.c1 BETWEEN 2000000 AND  3000000 THEN
        INSERT INTO parti_old_3 VALUES (NEW.*);
      ELSIF NEW.c1 BETWEEN 3000000 AND  4000000 THEN
        INSERT INTO parti_old_4 VALUES (NEW.*);
      ELSIF NEW.c1 BETWEEN 4000000 AND  5000000 THEN
        INSERT INTO parti_old_5 VALUES (NEW.*);
      ELSIF NEW.c1 BETWEEN 5000000 AND  6000000 THEN
        INSERT INTO parti_old_6 VALUES (NEW.*);
      ELSIF NEW.c1 BETWEEN 6000000 AND  7000000 THEN
        INSERT INTO parti_old_7 VALUES (NEW.*);
      ELSIF NEW.c1 BETWEEN 7000000 AND  8000000 THEN
        INSERT INTO parti_old_8 VALUES (NEW.*);
      ELSIF NEW.c1 BETWEEN 8000000 AND  9000000 THEN
        INSERT INTO parti_old_9 VALUES (NEW.*);
      ELSIF NEW.c1 BETWEEN 9000000 AND 10000000 THEN
        INSERT INTO parti_old_0 VALUES (NEW.*);
      END IF;
      RETURN NULL;
    END;
    $FUNC$;
    
    CREATE TRIGGER tr_insert_parti_old BEFORE INSERT ON parti_old
      FOR EACH ROW EXECUTE PROCEDURE insert_into();
    
    INSERT INTO parti_old SELECT i, 'something'
      FROM generate_series(0, 9999999) i;
    Time: 125351.918 ms (02:05.352)

    Мы видим, что скорость вставки при новом подходе к секционированию на порядок выше.


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


    Что касается производительности чтения, то на этапе 10 -> 11 произошли принципиальные изменения: появилась возможность эффективно исключать из плана секции, в которых заведомо нет данных (partition pruning). Это можно будет делать и на этапе исполнения, когда заранее не известно условие попадания в ту или иную секцию. Так случается, например, в случаях подзапроса.


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



    Сравнение старого и нового секционирования на примерах


    Разберемся в различиях подхода к секционированию в версиях 9.6 и 10.


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


    Команды для создания таких таблиц в 9.6 :


    CREATE TABLE meteo (
       t_id serial,
       place text NOT NULL,
       hour_mesure timestamp DEFAULT now(),
       temperature real NOT NULL
    );
    
    CREATE TABLE meteo_moscow_201709 (
       CHECK ( place = 'Moscow'
               AND hour_mesure >= TIMESTAMP '2017-09-01 00:00:00'
           AND hour_mesure < TIMESTAMP '2017-10-01 00:00:00' )
    ) INHERITS (meteo);
    CREATE TABLE meteo_moscow_201710 (
       CHECK ( place = 'Moscow'
               AND hour_mesure >= TIMESTAMP '2017-10-01 00:00:00'
           AND hour_mesure < TIMESTAMP '2017-11-01 00:00:00' )
    ) INHERITS (meteo);
    CREATE TABLE meteo_sochi_201709 (
       CHECK ( place = 'Sochi'
               AND hour_mesure >= TIMESTAMP '2017-09-01 00:00:00'
           AND hour_mesure < TIMESTAMP '2017-10-01 00:00:00' )
    ) INHERITS (meteo);
    CREATE TABLE meteo_sochi_201710 (
       CHECK ( place = 'Sochi'
               AND hour_mesure >= TIMESTAMP '2017-10-01 00:00:00'
           AND hour_mesure < TIMESTAMP '2017-11-01 00:00:00' )
    ) INHERITS (meteo);
    CREATE TABLE meteo_magadan_201709 (
       CHECK ( place = 'Magadan'
               AND hour_mesure >= TIMESTAMP '2017-09-01 00:00:00'
           AND hour_mesure < TIMESTAMP '2017-10-01 00:00:00' )
    ) INHERITS (meteo);
    CREATE TABLE meteo_magadan_201710 (
       CHECK ( place = 'Magadan'
               AND hour_mesure >= TIMESTAMP '2017-10-01 00:00:00'
           AND hour_mesure < TIMESTAMP '2017-11-01 00:00:00' )
    ) INHERITS (meteo);
    
    CREATE OR REPLACE FUNCTION meteo_insert_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
        IF ( NEW.place = 'Moscow' ) THEN
          IF ( NEW.hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND
               NEW.hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) THEN
              INSERT INTO meteo_moscow_201709 VALUES (NEW.*);
          ELSIF ( NEW.hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND
                  NEW.hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) THEN
              INSERT INTO meteo_moscow_201710 VALUES (NEW.*);
          ELSE
            RAISE EXCEPTION 'Date does not fit meteo_insert_trigger(Moscow)';
          END IF;
        ELSIF ( NEW.place = 'Sochi' ) THEN
          IF ( NEW.hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND
               NEW.hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) THEN
              INSERT INTO meteo_sochi_201709 VALUES (NEW.*);
          ELSIF ( NEW.hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND
                  NEW.hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) THEN
              INSERT INTO meteo_sochi_201710 VALUES (NEW.*);
          ELSE
            RAISE EXCEPTION 'Date does not fit meteo_insert_trigger(Sochi)';
          END IF;
        ELSIF ( NEW.place = 'Magadan' ) THEN
          IF ( NEW.hour_mesure >= TIMESTAMP '2017-09-01 00:00:00' AND
               NEW.hour_mesure < TIMESTAMP '2017-10-01 00:00:00' ) THEN
              INSERT INTO meteo_magadan_201709 VALUES (NEW.*);
          ELSIF ( NEW.hour_mesure >= TIMESTAMP '2017-10-01 00:00:00' AND
                  NEW.hour_mesure < TIMESTAMP '2017-11-01 00:00:00' ) THEN
              INSERT INTO meteo_magadan_201710 VALUES (NEW.*);
          ELSE
            RAISE EXCEPTION 'Date does not fit meteo_insert_trigger(Magadan)';
          END IF;
        ELSE
            RAISE EXCEPTION 'Date does not fit meteo_insert_trigger() !';
        END IF;
        RETURN NULL;
    END;
    $$
    LANGUAGE plpgsql;
    
    CREATE TRIGGER insert_meteo_trigger
        BEFORE INSERT ON meteo
        FOR EACH ROW EXECUTE PROCEDURE meteo_insert_trigger();

    Команды для создания таких таблиц в 10 ;


    CREATE TABLE meteo (
       t_id integer GENERATED BY DEFAULT AS IDENTITY,
       place text NOT NULL,
       hour_mesure timestamp DEFAULT now(),
       temperature real NOT NULL
     ) PARTITION BY RANGE (place, hour_mesure);
    
    CREATE TABLE meteo_moscow_201709 PARTITION of meteo FOR VALUES
       FROM ('Moscow', '2017-09-01 00:00:00') TO ('Moscow', '2017-10-01 00:00:00');
    CREATE TABLE meteo_moscow_201710 PARTITION of meteo FOR VALUES
       FROM ('Moscow', '2017-10-01 00:00:00') TO ('Moscow', '2017-11-01 00:00:00');
    CREATE TABLE meteo_sochi_201709 PARTITION of meteo FOR VALUES
       FROM ('Sochi', '2017-09-01 00:00:00') TO ('Sochi', '2017-10-01 00:00:00');
    CREATE TABLE meteo_sochi_201710 PARTITION of meteo FOR VALUES
       FROM ('Sochi', '2017-10-01 00:00:00') TO ('Sochi', '2017-11-01 00:00:00');
    CREATE TABLE meteo_paris_201709 PARTITION of meteo FOR VALUES
       FROM ('Magadan', '2017-09-01 00:00:00') TO ('Magadan', '2017-10-01 00:00:00');
    CREATE TABLE meteo_paris_201710 PARTITION of meteo FOR VALUES
       FROM ('Magadan', '2017-10-01 00:00:00') TO ('Magadan', '2017-11-01 00:00:00');

    Заметим, что декларативный синтаксис версии 10 намного проще синтаксиса 9.6. В ней, как видно из примера, наиболее утомительная часть работы это создание триггерных функций.


    (В pg_pathman разработчики пошли еще дальше. Там доступны функции, которые разрезают существующие секции, объединяют соседние и многие другие.)


    Вот пример функции, создающей случайные строки в таблице :


    CREATE OR REPLACE FUNCTION populate_meteo()
    RETURNS TEXT AS $$
    DECLARE
       placex text[] := '{}';
       v_place text;
       v_hour timestamp;
       v_temperature real;
       v_nb_insertions integer := 500000;
       v_insertion integer;
    BEGIN
       placex[0]='Moscow';
       placex[1]='Sochi';
       placex[2]='Magadan';
       FOR v_insertion IN 1 .. v_nb_insertions LOOP
          v_place=placex[floor((random()*3))::int];
          v_hour='2017-09-01'::timestamp
                       + make_interval(days => floor((random()*60))::int,
                                  secs => floor((random()*86400))::int);
          v_temperature:=round(((random()*14))::numeric+10,2);
          IF EXTRACT(MONTH FROM v_hour) = 10 THEN
              v_temperature:=v_temperature-4;
          END IF;
          IF EXTRACT(HOUR FROM v_hour) <= 9
             OR EXTRACT(HOUR FROM v_hour) >= 20 THEN
              v_temperature:=v_temperature-5;
          ELSEIF EXTRACT(HOUR FROM v_hour) >= 12
             AND EXTRACT(HOUR FROM v_hour) <= 17 THEN
              v_temperature:=v_temperature+5;
          END IF;
          INSERT INTO meteo (place,hour_mesure,temperature)
            VALUES (v_place,v_hour,v_temperature);
       END LOOP;
       RETURN v_nb_insertions||' mesures de température insérées';
    END;
    $$
    LANGUAGE plpgsql;

    Вставим строки в таблицы meteo из habr_9_6 и habr_10 :


    habr_9_6=# EXPLAIN ANALYSE SELECT populate_meteo();
                                             QUERY PLAN                                          
    ------------------------------------------------------------------------------------------
     Result  (cost=0.00..0.26 rows=1 width=32) (actual time=33315.067..33315.068 rows=1 loops=1)
     Planning time: 0.034 ms
     Execution time: 33315.084 ms
    (3 rows)
    
    habr_10=# EXPLAIN ANALYSE SELECT populate_meteo();
                                             QUERY PLAN                                       
    
    ---------------------------------------------------------------------------------------
     Result  (cost=0.00..0.26 rows=1 width=32) (actual time=14976.438..14976.438 rows=1 loops=
    1)
     Planning time: 0.016 ms
     Execution time: 14976.499 ms
    (3 rows)

    Мы видим, что в версии 10 данные вставляются вдвое быстрее.


    Управление всем семейством секций сразу


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


    habr_9_6=# SELECT 'VACUUM ANALYZE '||relname AS operation
      FROM pg_stat_user_tables WHERE relname LIKE 'meteo_%';
                 operation              
    ------------------------------------
     VACUUM ANALYZE meteo_moscow_201709
     VACUUM ANALYZE meteo_moscow_201710
     VACUUM ANALYZE meteo_sochi_201709
     VACUUM ANALYZE meteo_sochi_201710
     VACUUM ANALYZE meteo_paris_201709
     VACUUM ANALYZE meteo_paris_201710
    (6 rows)
    
    habr_9_6=# \gexec
    VACUUM
    VACUUM
    VACUUM
    VACUUM
    VACUUM
    VACUUM

    В версии 10 для того, чтобы произвести VACUUM и ANALYSE всех секций, достаточно обратиться только к родительской таблице :


    habr_10=# VACUUM ANALYZE meteo;
    VACUUM
    
    habr_10=# SELECT now() AS date,relname,last_vacuum,last_analyze
      FROM pg_stat_user_tables WHERE relname LIKE 'meteo_sochi%';
    -[ RECORD 1 ]+------------------------------
    date         | 2018-04-06 23:38:48.59511+03
    relname      | meteo_sochi_201709
    last_vacuum  | 2018-04-06 23:37:05.931573+03
    last_analyze | 2018-04-06 23:37:05.958845+03
    -[ RECORD 2 ]+------------------------------
    date         | 2018-04-06 23:38:48.59511+03
    relname      | meteo_sochi_201710
    last_vacuum  | 2018-04-06 23:37:05.973254+03
    last_analyze | 2018-04-06 23:37:06.002487+03


    Ограничения версии 10


    Index


    В версии 10 создание индекса на секционированной таблице по-прежнему невозможно :


    habr_10=# CREATE INDEX meteo_hour_mesure_idx ON meteo (hour_mesure);
    ERROR:  cannot create index on partitioned table "meteo"

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


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


    В то же время, как и в версии 9.6, можно создавать индексы на отдельных секциях :


    habr_10=# CREATE INDEX meteo_moscow_201710_hour_idx
      ON meteo_moscow_201710 (hour_mesure);
    CREATE INDEX

    В версии 11 можно ограничение уникальности будет работать (в случае когда ограничение наложено на столбец с ключом разбиения). Это большое достижение, и оно дает возможность ссылаться на секционированную таблицу из внешнего ключа другой таблицы (FOREIGN KEY)


    Обновление данных


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


    habr_10=# UPDATE meteo SET place='Sochi' WHERE place='Moscow';
    ERROR:  new row for relation "meteo_moscow_201709" violates partition constraint
    DETAIL : Failing row contains (5, Sochi, 2017-09-15 05:09:23, 9.43).

    В версии 11 можно смело обновлять значение ключа разбиения: строка автоматически переместится в нужную секцию.


    Вставка данных за пределами границ секций


    При декларативном секционировании версии 10 можно объявлять границы :


    CREATE TABLE meteo_moscow_ancienne PARTITION of meteo FOR VALUES
       FROM ('Moscow', MINVALUE) TO ('Moscow', '2017-09-01 00:00:00');
    CREATE TABLE meteo_sochi_ancienne PARTITION of meteo FOR VALUES
       FROM ('Sochi', MINVALUE) TO ('Sochi', '2017-09-01 00:00:00');
    CREATE TABLE meteo_paris_ancienne PARTITION of meteo FOR VALUES
       FROM ('Magadan', MINVALUE) TO ('Magadan', '2017-09-01 00:00:00');

    Другие ограничения


    • Родительская таблица не может содержать данных
    • В секциях не может быть дополнительных относительно родительской таблицы столбцов
    • Множественное наследование не допускается
    • Значения NULL в секциях допускаются только в том случае, если они допускаются в секционированной (родительской) таблице
    • Секции, не принадлежащие данному экземпляру СУБД, не поддерживаются (но можно присоединять секцию как FDW — CREATE FOREIGN TABLE ... PARTITION OF ...).
    • В случае присоединения секции
      • необходима проверка ограничений целостности (приводящей к блокировке всей секции)
      • требуется идентичное родительской таблице ограничение CHECK.

    Эти ограничения актуальны и для версии 11.


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


    [postgres-слоны Натальи Лёвшиной]

    • +22
    • 4,6k
    • 6
    Postgres Professional 219,66
    Российский вендор PostgreSQL
    Поделиться публикацией
    Комментарии 6
    • +2
      А не существует способа автоматического создания секции в Postgresql 10 если данные идут в несуществующую секцию?
      • 0
        Пока только в pg_pathman.
        • +1
          в 11-й будет секция по умолчанию для тех записей, что не попадают. но не автоматическое создание секций.
      • +1
        Появится ли поддержка INSERT… ON CONFLICT DO UPDATE?
      • 0
        Все хорошо и красиво пока на таблице, которую надо секционировать не висит 10 индексов часть из которых уникальные. Даже не беря в учет первичные ключи. Сомнительное удовольствие создавать руками весь набор ключей для секции, вместо того чтоб унаследовать это все у мастер таблицы. Сложность написания триггера тоже как то сильно преувеличена. Возможность не думать о создании ключей, кратно перекрывает удобство не писать триггер. А отсутствие первичных ключей у секций, вообще ставит под вопрос полезность такого секционирования в 10-ке по крайней мере.

        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

        Самое читаемое