Разбираемся с partitions в PostgreSQL 9

[Hixon10]

А не пробовали смотреть на pg_pathman? Не подошло?

[Envek]

Я пробовал пару лет назад. Хорошая штука — никаких триггеров — создал вызовами его функций партиции и в ORM ничего менять не надо: вставляй в неё, читай из неё. Данные из корневой таблицы в партиции можно переливать в фоне по ночам.

Помнится, требовались пляски для обновления расширения (на первых порах было очень много детских болячек, но они очень оперативно правились): скомпилировать и установить новую версию расширения в постгрес, перезапустить СУБД, вызвать UPDATE EXTENSION, молиться. Может, с тех пор лучше стало — больше не работал.

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

[AlexeySoshin]

Смотрели, но в итоге решили не использовать, по причинам, которые уже так же описал Envek
В нашем случае основной проблемой было так же накатывать custom extension.
В 2016ом у нас это делал Chef скрипт, который был общий для всех микросервисов. Плюс, поскольку мы работаем с Amazon RDS, там кажется были какие-то свои еще тонкости с установкой.

[bigtrot]

Не плохо бы было добавить:
1. про настройку constraint_exclusion (enum)
2. что делаете с устаревшими партициями
3. наложить запрет на вставку в родительскую таблицу, это поможет выявлять ситуации когда не совсем корректно настроена вставка.

[AlexeySoshin]

1. Спасибо, добавил еще пункт о constraint_exclusion
2. Это отдельная довольно большая тема, которая сильно зависит от бизнес логики. Оптимально конечно когда спустя некоторое время такая партиция становится не нужна, и ее можно легко дропнуть. Но бывает так не всегда.
3. Сильно зависит от имплементации. Если делать "под ORM", тогда вставка нужна. Если же в таблицу пишут ручками, можно конечно и перестраховаться.

[kirill_petrov]

Спасибо за статью, всегда раз, когда коллеги делятся своими решениями. Тоже сталкивался с проблемой автопартицирования примерно полгода назад. Вы изучали другие практики? Подобных решений много в том числе и на русскоязычном сегменте, вот, например, из 2015 года: www.youtube.com/watch?v=ECkQtFnOxuA

Мне нравится, что создание партиций в вашем подходе идемпотентно, и race condition тут:

-- create partition, if necessary
 IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname = partition_name) THEN
   PERFORM create_new_partition(TG_TABLE_NAME, NEW.tenant_id, partition_date, partition_name);
 END IF; 

ничего страшного не делает.

В моем случае идут bulk insert-ы и триггер создавал неприемлемый overhead по performance. Плюс требовалось двух уровневое партицирование, сначала разрезать по диапазону одной колонке, потом по второй. Поэтому сравнивайте, на сколько вы «просядите» с триггером или без.

Хочу отметить, что для декларативного партицирования, которая появилась в 10-ке, в 11-ой версии обещают Runtime partition pruning (https://commitfest.postgresql.org/17/1330/). Так же для декларативное партицирования обещают unique index (https://commitfest.postgresql.org/17/1452/). Будет ли поддерживаться эти фичи, для legacy-партицирования через наследования нужно еще проверить, поэтому для новой разработки, я бы не рекомендовал масштабироваться подобным образом.

[AlexeySoshin]

Одно из первых правил, котором учат DBA — при bulk'ах отключайте trigger'ы. К тому же наше решение со вставкой и удалением строк явно не оптимально по производительности. Зато очень легко в поддержке.

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