Вопросы по партиционированию

Здравствуйте.

Вопрос №1: автосоздание партиций

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

Нашел 2 варианта: внешним шедулером запускать create table либо непосредственно в триггерной процедуре. Наверное, лучше внешним, чтобы на нагруженной базе не анализировать постоянно метасхему. Да и стремно как то из триггера создавать таблицы - а если одновременно придет несколько транзакций - это что, будет несколько попыток создать новую партицию?

Самое интересное - как дать знать триггеру что появилась новая партиция. В энторнетах в примерах используют динамический SQL (через execute). Мне одному кажется, что это медленно? А что случится, если на таблице, в которую ежесекундно пишут несколько сот транзакций, изменить триггерную процедуру? Текущие транзакции откатятся? Или будут ждать обновления?

Вопрос №2: первичные ключи
Каким образом обеспечить уникальность первичного последовательного ключа для всех партиций? Вместо SERIAL/BIGSERIAL создать SEQUENCE имя_мастертаблицы и в партициях ставить у поля Id DEFAULT nextval('имя_мастертаблицы')? Или существуют другие практики?

Arm79
Нашел 2 варианта: внешним шедулером запускать create table либо непосредственно в триггерной процедуре.

можно написмать автосоздание триггером, но, при опасениях за скорость, вызывать его по шедъюлеру, заранее, вставляя нечто, что не пройдёт по констрайнтам. напрмире совать Null в not nullable. Если конечно триггер правильно написан

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

а её не надо "всё время" анализировать руками. надо обрабатывать попытку сходить мимо тазика. если это


есть одно но -- в головной таблице не должно быть записей вообще -- иначе динамическое создание партиции (и все последующие попытки) рискует встать в очередь за автовакуумом.

Arm79
Да и стремно как то из триггера создавать таблицы - а если одновременно придет несколько транзакций - это что, будет несколько попыток создать новую партицию?

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

ну и чтобы вот это всё не откатывать в случае отката вставляющей -- создание делается в автономии.

Arm79
Самое интересное - как дать знать триггеру что появилась новая партиция. В энторнетах в примерах используют динамический SQL (через execute). Мне одному кажется, что это медленно?

для 30+ партиций писать case when /elsif с полным текстом SQL в каждой ветке -- несколько накладно.
я пользуюсь динамикой.


мыслимы модификаторы пж--диалекта с INSERT по tableoid . почему бы их не реализовать -- я не догоняю.

Arm79
А что случится, если на таблице, в которую ежесекундно пишут несколько сот транзакций, изменить триггерную процедуру? Текущие транзакции откатятся? Или будут ждать обновления?

текущие транзакции завершатся со старым телом триггера. (по старой версии этой же записи в pg_proc).


но тут вопрос крайне интересный на предмет нет ли тут дырочки в acid -- я его сам ни разу до конца не думал.

(это самый быстрый способ изменить текущую логику. не требующий блокировки таблицы, в отличии от DROP TRIGGER/CREATE TRIGGER). что порождает известные сомнения в его кошерности

Arm79
Вопрос №2: первичные ключи Каким образом обеспечить уникальность первичного последовательного ключа для всех партиций? Вместо SERIAL/BIGSERIAL создать SEQUENCE имя_мастертаблицы и в партициях ставить у поля Id DEFAULT nextval('имя_мастертаблицы')? Или существуют другие практики?

если вы скажете предку serial -- то в детях оно само станет nextval..... (потому что serial -- это некствал + пара доп условий).

я видел [руками] еще и триггерное поддержание "центра" звизды (бессмысленной таблички пк-еев детей) -- исключительно с целью поддержания unique сквозь всю гирлянду партиций. стоит ли это делать в действительно сильно нагруженном случае -- вопрос открытый. но, на крайняк -- всегда можно будет отключить (очистить тело триггерной ф-ии триггера поддержания центра).

qwwq
Arm79 Нашел 2 варианта: внешним шедулером запускать create table либо непосредственно в триггерной процедуре. можно написмать автосоздание триггером, но, при опасениях за скорость, вызывать его по шедъюлеру, заранее, вставляя нечто, что не пройдёт по констрайнтам. напрмире совать Null в not nullable. Если конечно триггер правильно написан

Мысль была следующая: заранее создавать, скажем, на неделю вперед партиции, а в условиях - смотреть на дату. Поэтому констрейнт можно не анализировать. Если не совпадает, то и проблем не должно быть.

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

Это писать типа куда должно, и отлавливать исключение undefined_table?

qwwq
Arm79 Самое интересное - как дать знать триггеру что появилась новая партиция. В энторнетах в примерах используют динамический SQL (через execute). Мне одному кажется, что это медленно? для 30+ партиций писать case when /elsif с полным текстом SQL в каждой ветке -- несколько накладно. я пользуюсь динамикой.

Накладно писать, но работать будет быстрее, так? Или все-таки нет? Я, к сожалению, не знаю, медленнее динамический запрос в Pg. Тратится ли каждый раз время на его компиляцию и т.п.

qwwq
если вы скажете предку serial -- то в детях оно само станет nextval..... (потому что serial -- это некствал + пара доп условий). я видел [руками] еще и триггерное поддержание "центра" звизды (бессмысленной таблички пк-еев детей) -- исключительно с целью поддержания unique сквозь всю гирлянду партиций

Требование - уникальный Id во всех партициях мастер-таблицы (а не по всей БД). Поэтому если Id в мастер-таблице будет SERIAL, то в дочерних будет использоваться тот же sequence? Отлично.

Arm79
<> Требование - уникальный Id во всех партициях мастер-таблицы (а не по всей БД). Поэтому если Id в мастер-таблице будет SERIAL, то в дочерних будет использоваться тот же sequence? Отлично.

вы путаете "мастер таблица"/slave table -- это соотношение ведущий -- ведомый в случае FK

тут же предок и дети

в предка записи не попадают [....]. просто при запросе к предку без модификатора ONLY в итог выводится все записи детей (то же -- про update.).

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

а default не помешает запихать в разные партиции всё что угодно. это не identity

ну и т.п.

т.е. пока вижу полное отсутствие понимания наследования в пж. Просто поймите для себя, что оно сделано не для партицирования.
т.е. совсем.
данные, индексы и констрейнты отдельных таблиц -- это ИХ и ТОЛЬКО их данные, индексы и констрайнты
хотя констрайнты "наследуются" при пердложении create table ..... inherit (и поменять предка на предка, имеющего другой набор констрайнтов, не помеченных как NO INHERIT вы не сможете). но они работают только с данными текущей партиции.
партицирование -- та область, куда "наследование" оказалось возможным применить.

qwwq,

Концепция наследования таблиц для меня, безусловно, новая.

Вот поясните, если я в мастер таблице в поле Id укажу SERIAL, то в наследниках Id тоже будет SERIAL? Или просто Int? Если также будут SERIAL, то при вставке нескольких записей будут ли Id в разных партициях пересекаться?

Arm79
qwwq, Концепция наследования таблиц для меня, безусловно, новая. Вот поясните, если я в мастер таблице в поле Id укажу SERIAL, то в наследниках Id тоже будет SERIAL? Или просто Int? Если также будут SERIAL, то при вставке нескольких записей будут ли Id в разных партициях пересекаться?

Все, этот вопрос уже неактуален :-) добрался до компьютера и проверил, все работает как нужно. Id уникален, все партиции ссылаются на один и тот же сиквенс

Arm79

Arm79 qwwq, Концепция наследования таблиц для меня, безусловно, новая. Вот поясните, если я в мастер таблице в поле Id укажу SERIAL, то в наследниках Id тоже будет SERIAL? Или просто Int? Если также будут SERIAL, то при вставке нескольких записей будут ли Id в разных партициях пересекаться? Все, этот вопрос уже неактуален :-) добрался до компьютера и проверил, все работает как нужно. Id уникален, все партиции ссылаются на один и тот же сиквенс

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

Если у вас временнЫе факты -- обычно партицируют по времени, а не пк. там отсутствие реального общего уникъю -- оно смущает. уникъю не гарантировано, просто при генерации из сиквенса оно само--собой не нарушается.

Arm79,

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

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

Партиций планирую 30, из расчета максимум 4,5 млрд строк, максимальное кол-во транзакций на запись 2-2,5 тысячи в секунду. Основная нагрузка - инсерты, апдейты и куча селектов за текущий день (не аналитика, чистый OLTP). А другие партиции - нагрузка только на чтение, причем небольшая (по сравнению с текущим днем)

Текущих мощностей сервера должно хватить, но я на всякий случай еще горизонтальный шардинг смотрю, pl/proxy или postgresql-xl(xc) + pgbouncer (там тоже масса вопросов, но это потом, сначала доку почитаю)

Arm79
<> Партиций планирую 30

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

Arm79
<>куча селектов за текущий день (не аналитика, чистый OLTP). А другие партиции - нагрузка только на чтение, причем небольшая (по сравнению с текущим днем)

всё упрётся не в сложность а в разнообразность поисков, евпочя [число индексов по предыдущему п.]

Arm79
<> я на всякий случай еще горизонтальный шардинг смотрю, pl/proxy или postgresql-xl(xc) + pgbouncer (там тоже масса вопросов, но это потом, сначала доку почитаю)

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

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