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

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

Секционирование в 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 — самая современная в мире реляционная база данных с открытым исходным кодом!

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

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

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


  1. Kwisatz
    24.12.2019 09:54

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


    1. kibermat Автор
      24.12.2019 10:17

      Analyze к «обычной» таблице
      image

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

      Надеюсь, на скринах будет видно


      1. Melkij
        24.12.2019 10:26

        Замечательно видно что вы тестируете в неравных условиях. См. actual rows.


        1. kibermat Автор
          24.12.2019 10:47

          Все верно замечено, исправил.


          1. Kwisatz
            24.12.2019 15:31

            Вот только разница в 22 раза это все равно очень много и не объясняется секционированием. Разные конфигурации бд?


            1. kibermat Автор
              24.12.2019 17:18

              Да, нужно ориентировать на цифры в статье см. таблицу в разделе Результат. Отклонения от этого объясняется разными конфигурациями сервера.


              1. Kwisatz
                24.12.2019 18:23

                Цифры в статье тоже слишком большие, не должно быть такого прироста. Поэтому я и спрашиваю.


                1. kibermat Автор
                  25.12.2019 09:17

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


                  1. Kwisatz
                    25.12.2019 10:48

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


      1. Kwisatz
        24.12.2019 10:30
        +1

        Сначала пришел в ужас от разницы в 220 раз, потом заметил, что во втором случае 0 строк, не пойдет)


  1. kirovilya
    24.12.2019 11:58

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

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


    1. kibermat Автор
      24.12.2019 12:10

      Это справедливо и для 11 версии. Можете уточнить?


      1. kirovilya
        24.12.2019 13:15

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

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


        1. kibermat Автор
          24.12.2019 13:29

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


          1. kirovilya
            24.12.2019 13:34

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


            1. kibermat Автор
              24.12.2019 13:40

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


  1. grufos
    26.12.2019 15:35
    +1

    Очень интересное решение, спасибо!
    Не увидел в коде поддержки DEFAULT партиций.
    Это в списке TODO или отстутствует по каким-то другим причинам?


    1. kibermat Автор
      26.12.2019 16:43

      DEFAULT партиция не создается, определяю MINVALUE/MAXVALUE как нижняя и верхняя граница.


  1. grufos
    26.12.2019 18:20

    Как вы решили у себя проблему создания новых секций?
    Мы создали такой «job», на основе pg_cron, который 1 раз в день (у нас секционирование по 1 дню используется) добавляет новую секцию к таблице.


    1. kibermat Автор
      27.12.2019 14:23

      У нас еще нет этой проблемы, но cron выглядит как хорошее решение.