Мой путь к секционированию в PostgreSQL



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

    Привет, Хабр! Меня зовут Алмаз и сейчас я хочу поделиться методом, который помог мне реализовать секционирование.

    Секционирование в PostgreSql


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

    Пример: у нас есть таблица “sales”, которая секционирована по интервалу один месяц, а эти секции могут быть разбиты на еще более мелкие подсекции по регионам.


    Схема секционированной таблицы “sales”

    Минусы этого подхода:

    — Усложняется структура базы данных. Каждая секция в определениях базы — это таблица, хоть и является частью одной логической сущности.
    — Преобразовать существующую таблицу в секционированную и наоборот нельзя.
    — Нет полной поддержки в версии Postgres 11.

    Плюсы:

    + Быстродействие. В определенных случаях мы можем работать с ограниченным набором секций, не перебирая всю таблицу, даже поиск по индексу для больших таблиц будет медленнее. Повышается доступность данных.
    + Массовая загрузка и удаление данных командами ATTACH/DETACH. Это избавляет нас от накладных расходов в виде VACUUM-а. что позволяет более эффективно сопровождать базу данных.
    + Возможность указать TABLESPACE для секции. Это дает нам возможность выносить данные в другие разделы, но все же мы работаем в рамках одного инстанса и метаданные главного каталога будут содержать информацию о секциях.(не путать с шардингом)

    2 пути к реализации секционирования в PostgreSql:



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

    Тут нужно отметить расширение разработанное компанией Postgres Professional pg_pathman, которое реализует секционирование, также через наследование таблиц.

     CREATE TABLE orders_y2010 (
          CHECK (log_date >= DATE '2010-01-01)
        ) INHERITS (orders);
    

    2. Декларативный подход (PARTITION)

    Таблица определяется как секционированная декларативно. Данное решение появилось в 10 версии PostgreSql.

    CREATE TABLE orders (log_date date not null, …) 
    PARTITION BY RANGE(log_date); 


    Я выбрал декларативный подход. Это дает большое преимущество — нативность, больше фич поддерживается ядром. Рассмотрим развитие PostgreSQL в данном направлении:


    Источник

    Но PostgreSql продолжает развиваться, и в 12 версии есть поддержка ссылок на секционированную таблицу. Это большой прорыв.

    Мой путь


    Учитывая вышесказанное, был написан скрипт на PL/pgSQL, который создает секционированную таблицу на основе существующей и “перекидывает” все ссылки на новую таблицу. Тем самым мы получаем секционированную таблицу на основе существующей и продолжаем работать с ней как с обычной таблицей.
    Скрипт не требует дополнительных зависимостей и выполняется в отдельной схеме, которую создает сам. Также записывает логи повтора и отмены действий. Данный скрипт решает две основные задачи: создает секционированную таблицу и реализует внешние ссылки на нее через констрейнт триггеры.

    Требование к скрипту: PostgreSql v.:11 и выше.

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

    1. Главный вызов — на этом этапе мы не меняем основную таблицу, но все необходимое для секционирования будет создано в отдельной схеме:

     call partition_run(); 


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

     call partition_run_jobs(); 


    Работа может быть запущена в несколько потоков. Оптимальное количество потоков близка к количеству секционируемых таблиц.

    Входные параметры для скрипта (_pt record)



    Скрипт изнутри, основные действия:

    — Создаем секционированную таблицу
     perform _partition_create_parent_table(_pt); 

    — Создаем секции
     perform _partition_create_child_tables(_pt); 

    — Копируем данные в секции
     perform _partition_copy_data(_pt); 

    — Добавим ограничения (job)
     perform _partition_add_constraints(_pt); 

    — Восстановим ссылки на внешние таблицы
     perform _partition_restore_referrences(_pt); 

    — Восстановим триггеры
     perform _partition_restore_triggers(_pt); 

    — Создаем событийный триггер
     perform _partition_def_tr_on_delete(_pt); 

    — Создаем индексы (job)
     perform _partition_create_index(_pt); 

    — Заменяем вьюхи, ссылки на секцию (job)
     perform _partition_replace_view(_pt); 


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


    Результат


    Что мы получили? Посмотрим на план запроса:

     EXPLAIN ANALYZE 
    select * from “sales” where dt BETWEEN '01.01.2019'::date and '14.01.2019'::date 




    Результат из секционированной таблицы мы получали быстрее и использовали меньше ресурсов нашего сервера по сравнению с запросом к обычной таблице.

    В данном примере обычная и секционированные таблицы находятся на одной базе и имеют около 200М записей. Это хороший результат, учитывая то, что мы, не переписывая прикладной код, получили ускорение. Запросы по другим индексам также работают хорошо, но следует помнить: всегда, когда мы можем определить секцию, результат будет в несколько раз быстрее, т.к. PostgreSql умеет отбрасывать лишние секции на этапе планирования запроса (set enable_partition_pruning to on).

    Итог


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

    PostgreSQL — самая современная в мире реляционная база данных с открытым исходным кодом!

    Всем спасибо!

    Ссылка на исходник

    БАРС Груп
    78,11
    Создаем технологии. Меняем жизнь.
    Поделиться публикацией

    Комментарии 20

      0
      Не будет ли вам трудно у «обычной» таблицы создать индекс по дате и провести тест еще разок? Если бы вы explain analyze приложили, было бы вообще шикарно.
        +1
        Analyze к «обычной» таблице
        image

        Analyze к секционированной
        image

        UPD: на предыдущем скрине, тест.сервер, там данные были удалены, приложил актуальный скрин
        Analyze к секционированной


        Надеюсь, на скринах будет видно
          0
          Замечательно видно что вы тестируете в неравных условиях. См. actual rows.
            0
            Все верно замечено, исправил.
              0
              Вот только разница в 22 раза это все равно очень много и не объясняется секционированием. Разные конфигурации бд?
                0
                Да, нужно ориентировать на цифры в статье см. таблицу в разделе Результат. Отклонения от этого объясняется разными конфигурациями сервера.
                  0
                  Цифры в статье тоже слишком большие, не должно быть такого прироста. Поэтому я и спрашиваю.
                    0
                    Секционирование не является решением, которая способна дать профит для любой базы. Могут быть случаи, когда это отразится плохо на производительности. Для принятия такого решения нужно оценить все риски, если вы не получаете желаемого результата, то возможно это не то что вам нужно.
                      0
                      Слишком общие не подкрепленные у вас заявление, ответ ради ответа. Я в курсе всех особенностей, а вот ваши цифры все равно выглядят завышеными.
            +1
            Сначала пришел в ужас от разницы в 220 раз, потом заметил, что во втором случае 0 строк, не пойдет)
          0
          в 12 версии есть поддержка ссылок на секционированную таблицу. Это большой прорыв

          Как оказалось, не такой уж и большой, т.к. в этом случае первичный ключ секционируемой таблицы должен содержать поле, по которому поисходит секционирование. А это очень печально.
            0
            Это справедливо и для 11 версии. Можете уточнить?
              0
              что уточнить? на собственном опыте проверили на PG 12.
              в PG 11 нельзя было создать первичный ключ без ключа секционирования, а PG 12 просто появилась возможность ссылаться на такой ключ (создавать внешний ключ).

              вот еще пруф fragland.dev/a-guide-to-table-partitioning-with-postgresql-12
                0

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

                  0
                  Сложно, когда в рабочей схеме БД решили сделать секционирование таблицы — придется менять не только эту таблицу, но и структуру таблиц, ссылающихся на нее (добавлять во внешние ключи поле, не относящееся к этим таблицам), менять логику их заполнения и обращения к ним.
                  Проще тогда попытаться сделать FK на триггерах…
                  Другими словами, мы отбросили эту идею в долгий ящик.
                    0

                    Спасибо за ответ. Я ещё не реализовал у себя нативную поддержку FK 12 версии. Использую решение на триггерах.

            +1
            Очень интересное решение, спасибо!
            Не увидел в коде поддержки DEFAULT партиций.
            Это в списке TODO или отстутствует по каким-то другим причинам?
              0
              DEFAULT партиция не создается, определяю MINVALUE/MAXVALUE как нижняя и верхняя граница.
              +1
              Как вы решили у себя проблему создания новых секций?
              Мы создали такой «job», на основе pg_cron, который 1 раз в день (у нас секционирование по 1 дню используется) добавляет новую секцию к таблице.
                0
                У нас еще нет этой проблемы, но cron выглядит как хорошее решение.

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

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