Pull to refresh

Comments 31

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

Для attach partition нужно заранее создать check constraint. Тогда не требуется длительных блокировок для проверки значений. Описано в документации.

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

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

Да, всё так. Далее по пунктам :)

Для attach partition нужно заранее создать check constraint. Тогда не требуется длительных блокировок для проверки значений. Описано в документации.

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

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

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

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

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

Позвольте поясню.

Декларативное партицирование упомянул, но не рассматривал в данной статье

Это был ответ против аргумента с блокировками:

при добавлении и удалении партиций будет простой в работе таблицы из-за ACCESS EXCLUSIVE LOCK (начиная с PostgreSQL 12, режим блокировки более щадящий: можно использовать SELECT, но только без FOR UPDATE/SHARE);

То есть с точки зрения блокировок они равнозначны.

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

Тут вот такой пример.

select *
  from small_table s
  left join large_table l
         on l.id = s.large_table_id
 where s.(...)

При этом primary key (large_table.id). Баз партицирования план будет nested loops join и работать за миллисекнды, потому что в s мало строк, тем более после where. После партицирования large_table (хоть по l.id, хоть иначе) этот запрос умрёт, потому что nested loops по индексу уже не будет, а если и будет - по всем партициям сразу. Но всё будет работать сносно при использовании pg_pathman.

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

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

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

Сам так не делал, и подозреваю, что это не всегда применимо из-за зависимостей исходной таблицы.

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

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

Пользовался для в основном англоязычными источниками, ни в одном не было sectioned table / sectioning и во всех было partitioned table / partitioning :) . Вполне естественно для меня использовать партицирование, а не секционирование.

почему не рассмотрено партицирирование через PARTITION BY RANGE

В статье как раз рассмотрено партицирование по диапазонам. Или вы о чём-то другом спрашиваете?

PARTITION BY относится к декларативному партицированию, которое не рассматривается в статье, т.к. тему "давайте спроектируем базу/таблицу" не планировалась. Изначально хотели рассказать, как в компании решали сложные кейсы через партицирование с конкретными примерами, но в одну статью всё не поместилось.

Одним из вариантов решения такой проблемы в PostgreSQL является партицирование.

А какие есть еще варианты, помимо самого трудозатратного? И в каких случаях создание партиций оправдано?

Например, такие:

  • перейти на более мощное железо

  • распределить данные по другим таблицам (нормализовать данные)

  • очистить старые данные и/или перенести их в долговременное хранилище

  • проверить наличие необходимых индексов

  • проверить, давно ли выполнялся автовакуум

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

Докину еще реплицирование на отдельную машину для аналитики, постоянный перерасчет накапливаемых данных и/или типовых выполняемых запросов, денормализацию (если это оправдано) для ускорения запросов, переписывание запросов с использованием временных таблиц (бывает куда быстрее посчитать нечто в 2-3 этапа, чем одним запросом).

А что с индексами, у каждой партиции могут быть свои независимые индексы или можно только один большой на мастер?

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

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

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

или можно только один большой на мастер?

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

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

А для чего нужно указывать чек:

CHECK (created_at >= '2021-03-01'::DATE AND created_at < '2021-04-01'::DATE)

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

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

И всё-таки он ради constraint exlusion лишних партиций.

Точно.

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

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

В каких случаях партицирование не поможет либо никак не повлияет?

- время создания бэкапа;

- время восстановления из бэкапа;

- место на диске.

Партицирование в связке с внешними таблицами как раз помогает решить все эти вопросы.

Уточните, что вы имеете ввиду?

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

У нас в базе есть таблица, в которой сохраняются события перед отправкой в очередь, чтобы гарантировать, что они будут отправлены только при успешном коммтте транзакции (a.k.a. transactional outbox pattern). Проблема с такой таблицей в том, что она непрерывно растет и нуждается в периодической очистке, поскольку обработанные события хранить не обязательно.

Чтобы сделать очистку безболезненной, я придумал такое решение (но ещё не реализовывал на практике; любопытно услышать стороннее мнение, какие могут быть подводные камни, которые я не учел):

  1. Делим таблицу на три партиции - A, B, C

  2. Ключом партиционирования делаем (в качестве примера) текущий час, разделенный на 8 (с округлением вниз до целого) . Т.е. данные будут циклически попадать в одну из партиций A, B или C, в зависимости от времени суток. Размер можно подобрать в зависимости от нагрузки, важно, чтобы протяженность окна одной партиции была заведомо длиннее самой долгой транзакции, возможной в системе. Чтобы спалось спокойнее, можно брать хоть остаток от деления текущего месяца на 3 - принцип не меняется, только увеличивается объем хранимых данных.

  3. По расписанию, вскоре после того, как текущей становится партиция C, партиция А очищается (через truncate), когда активной становится партиция A, чистим B, когда активной становится B, чистим C.

  4. Перед очисткой убеждаемся, что в партиции не осталось необработанных строк. Для обработчика уже существует partial index по условию processed=false, поэтому такой запрос нересурсозатратный. Если строки найдены, копируем их в отдельную таблицу. Ввжно, что наличие "старых" необработанных строк - признак аварии, в норме их быть не должно вовсе. Поэтому таблица с копиями бесконтрольно разрастаться не будет. После устранения аварии и переотправки событий она будет очищаться.

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

Может быть, я переизобрел какой-то известный велосипед? Что думаете?

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

ОМГ, зачем индексы добавлять в триггере? Потом решите добавить индекс на таблицу, а триггер забудете обновить. Может лучше как-то так?

-- создание таблицы сразу со всеми индексами как на основной таблице
CREATE TABLE bigtable_y2021m03 (LIKE bigtable including all)
  INHERITS (bigtable);
  
-- добавление check constrains отдельно
ALTER TABLE   bigtable_y2021m03 
  ADD CHECK 
  (created_at >= '2021-03-01'::DATE AND created_at < '2021-04-01'::DATE);

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

SELECT 'GRANT ' || privilege_type || ' ON public.bigtable_y2021m03 ' || ' TO  ' || grantee || ';' 
   FROM information_schema.role_table_grants WHERE table_schema = 'public' AND table_name = 'bigtable';

`INCLUDING ALL` можно заменить на `INCLUDING INDEXES` если надо копировать только индексы.

Ох, давайте разбираться по порядку:

  1. Оператор LIKE предназначен несколько для других целей. В его обязанности входит здесь и сейчас создать новую несвязанную с базовой пустую таблицу с такой же структурой, как и базовая таблица. Причём для LIKE под структурой таблицы понимается только кол-во полей, имя поля и типа поля. Всё остальное: дефолтные значения полей, описания/комментарии полей, индексы, констрэйнты и прочее - не копируются в новую таблицу. При желании их можно скопировать через INCLUDING. Мы к этому ещё вернёмся :)

  2. Зачем отдельно добавлять CHECK, когда это можно было сделать в одном запросе:
    CREATE TABLE bigtable_y2021m03 (
    LIKE bigtable including all,
    CHECK (created_at >= '2021-03-01'::DATE
    AND created_at < '2021-04-01'::DATE)
    ) INHERITS (bigtable);

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

  4. LIKE, вызванный одновременно с INHERITS, сгенерит нотисы/замечания о том, что поля, скопированные через LIKE, объединяются со полями, наследованными через INHERITS. Не критично, но в то же время неприятно.

  5. Создание таблицы с LIKE bigtable INCLUDING INDEXES ведёт к желаемому эффекту: созданию индексов. Однако, имена индексов будут заданы плюс-минус произвольно (читай: далеко не всегда можно предсказать, какое имя будет у индекса). Если имя индекса не критично, тогда да, можно использовать указанный вариант. Но если имена индексов контролируются и в дальнейшем планируется выполнять манипуляции с ними, INHERITS лучше не совмещать с LIKE.

  6. Использование LIKE bigtable INCLUDING ALL вообще опасная вещь. Например, в случае если в мастер-таблице заданы констрэйнты, то INHERITS более чем достаточно. Однако, если хотя бы один констрэйнт задан флагом NO INHERIT, то оператор INHERIT не будет его наследовать, но благодаря оператору LIKE с INCLUDING ALL или INCLUDING CONSTRAINTS скопирует его в новую наследуемую таблицу. В итоге получаем, что в наследство от мастер-таблицы получили то, что не должно было наследоваться.

  7. И напоследок: партицирующая функция будет выполнятся от имени пользователя-владельца базы (если не задано иное). Владельцу базы дополнительных прав раздавать не нужно. Однако, если в базу ходят и другие пользователи, то бэст-практисом является не явная раздача прав на вновь создаваемые объекты, а настройка DEFAULT PRIVILEGES, на основе которых постгрес самостоятельно выдаст необходимые права на новые объекты.

Спасибо за развернутый ответ!

И да, INHERITS не наследует индексы, поэтому помимо добавления самих индексов, нужно будет поправить партицирующую функцию, это не такая уж и сложная операция.

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

Использование LIKE bigtable INCLUDING ALL вообще опасная вещь.

Да, возможно. Но использование INCLUDING INDEXESвроде как ничего поломать не должно? С тем что "имена индексов контролируются и в дальнейшем планируется выполнять манипуляции с ними" спорить не буду, возможно есть какие-то сценарии где нужно именно имя индекса, и нельзя посмотреть его перед операцией. Но я с такими не сталкивался.

Однако, если в базу ходят и другие пользователи, то бэст-практисом является не явная раздача прав на вновь создаваемые объекты, а настройка DEFAULT PRIVILEGES, на основе которых постгрес самостоятельно выдаст необходимые права на новые объекты.

Не всегда нужно выдавать права на все новые объекты. Вроде DEFAULT PRIVILEGES настраивается только на схему? Иногда всякие complience (и здравый смысл) запрещают выдачу прав по умолчанию на все новые объекты, но при этом, например было получено одобрение на выдачу прав на определенные таблицы, не содержащие защищаемых данных (реальный кейс).

Статья рассматривает случай, когда поле, по которому выполняется деление на партиции, статическое и не изменяется во времени. А как изменится обслуживание, если поле партиционирования изменяемое, и данные могут мигрировать из одной партиции в другую? Поскольку всё обслуживание тут фактически "вручную", создаётся впечатление, что получится совершенно неподъёмный монстр...

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

Более быстрый вариант, но с даунтаймом:

  • запретить запись в партицированную таблицу

  • изменить партицирующую функцию на соответствие новым критериям партицирования

  • перенести данные из старых партиций в новые путём запроса на вставку в мастер-таблицу (сработает триггер, который вставит данные в новые партиции)

  • удалить старые партиции

  • разрешить запись

Более долгий, но без даунтайма:

  • отключить триггер на вставку данных

  • перенести данные из партиций в мастер-таблицу

  • удалить старые партиции

  • изменить партицирующую функцию на соответствие новым критериям партицирования

  • подключить триггер обратно

  • перенести данные из мастер-таблицы по партициям путём вставки в мастер-таблицу (сработает триггер, который вставит данные в новые партиции)

  • транкейтим мастер-таблицу для очистки места

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

o_O Это у Постгресса такие ограничения? Я уж не говорю о том, что на дате-времени список критериев партиционирования не остановился. Пример... ну, скажем, партиционирование по типу клиента (обычный, VIP, Gold VIP, юрлицо, корпорант, партнёр, ...).

Или это просто рекомендации, от которых можно смело отмахнуться и сделать так, как надо мне, а не серверу?

Sign up to leave a comment.