Люди добрые, возник такой вопрос, насколько этот констрейнт/пк плохая идея

для партицирования (при помощи pg_partman) ? Заранее спасибо.

domain_id в ключи держать не хочется, поскольку есть вероятность, что в одном и том же таймстемпе может быть 2 записи с 1 domain_id, и общий инкримент это решает, но не хочется лишний раз нарваться на проблемы со вставкой (aws aurora, 10ки тыщ/сек вставок)
(BRIN возможно тут и не нужен, но пока не мешает, таблица 1+ лярд строк)

CREATE SCHEMA IF NOT EXISTS project;

-- add pg_partman extension
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;

CREATE TABLE project.entries (
id BIGINT GENERATED BY DEFAULT AS IDENTITY,

domain_id INT,
created_at TIMESTAMP NOT NULL,

CONSTRAINT pk_project_entries PRIMARY KEY (created_at, id)
) PARTITION BY RANGE (created_at);

CREATE INDEX idx_entries_created_at ON project.entries(created_at);
CREATE INDEX idx_entries_created_at_brin ON project.entries USING brin(created_at);
CREATE INDEX idx_entries_market ON project.entries(market);

-- enable pg_partman on entries
SELECT partman.create_parent(p_parent_table => 'project.entries',
p_control => 'created_at',
p_type => 'native',
p_interval=> 'weekly',
p_premake => 7,
p_start_partition => '2018-07-01');

> (BRIN возможно тут и не нужен, но пока не мешает, таблица 1+ лярд строк) Мешает-мешает. Оба эти индекса совершенно лишние: CREATE INDEX idx_entries_created_at ON project.entries(created_at); CREATE INDEX idx_entries_created_at_brin ON project.entries USING brin(created_at); И снижают производительность UPDATEs раза в полтора, грубо говоря (и это не считая дополнительных затрат на планирование и того риска, что однажды планировщик какой-то из них всё-таки выберет), не давая ничего взамен. А по самому вопросу — какая версия PostgreSQL, сколько partitions получится, какого каждая будет размера, собираетесь ли как-то удалять / убирать (detach) их в будущем?

замечание хорошее, но апдейтов нет, брин скорее и так будет дропнут 13я на Авроре, штук их 52 (недели) * колво лет (до 10), многие будут маленькие, вот только за последние годы по несколько миллионов строк(до 10М прогнозирую) ожидается на партицию не, хотим хранить все, поэтому никаких манипуляций с партициями в планах нет.

> 13я на Авроре "Аврора" — это вообще не PostgreSQL (оно только с виду похоже), я правильно помню? В сторону: неплохо бы проекту PostgreSQL с этим что-то сделать — "тратят" нашу популярность / репутацию на то, чтобы продавать свои (кривые) forks и аналоги, никакой совести нет. ;) > замечание хорошее, но апдейтов нет INSERT это всё тоже касается. Т.е., если там столько записей — просто тратите "тонны" места совершенно впустую на эти два индекса. Вы понимаете, что PRIMARY KEY (created_at, id) полностью покрывает project.entries(created_at)? > штук их 52 (недели) * колво лет (до 10), многие будут маленькие, Значит, это слишком мелкие partitions. > вот только за последние годы по несколько миллионов строк(до 10М прогнозирую) ожидается на партицию Несколько попугаев миллионов строк — это не размер, размеры измеряются в гигабайтах, например. ;) Но, скорее всего, это слишком мало, опять-таки. Может, помесячные попробовать? Ну а "удачность" принципа разбиения в таком случае зависит от того, какие запросы используются.

> "Аврора" — это вообще не PostgreSQL все же постгрес, но со стореджем от AWS, привет бесконечный диск, копии и ресайз машины за деньги > INSERT это всё тоже касается справедливо, да, я говорил что БРИН там так, потестить) > Вы понимаете, что PRIMARY KEY (created_at, id) полностью покрывает project.entries(created_at)? понимаю, но я и спрашивал насчет уникальности для праймари) потому что created_at никак не уникален и повторы будут 100% уникальность достиг при помощи id но мне кажется это плохой подход и я это хотел спросить в чате) > Значит, это слишком мелкие partitions. не знаю что сказать, возможно и так и проще взять месячный интервал, но с SET enable_partition_pruning =on; проблем (вроде как) не должно быть и скедулер будет смотреть в правильные места и отсекать пустые партиции > зависит от того, какие запросы используются. банальный селект по времени из диапазона + domain_id, поэтому и хочется партицирования чтобы смотреть только в те места, где лежат данные за это время, а потом из них брать уже по domain_id про популярность: ну бизнес жесток, а мы просто рабы у ноутов :(

> все же постгрес, но со стореджем от AWS "Amazon Aurora PostgreSQL is a fully managed, PostgreSQL-compatible, and ACID-compliant relational database engine..." То есть нет. Это closed-source fork (и неизвестно, сколько они там накосили при замене storage). А "шишки" от этого почему-то иногда падают на проект PostgreSQL, понимаете (ухудшая не только их репутацию почему-то)? Ну и да, много было бы у них клиентов для "диск, копии и ресайз машины за деньги", если бы всем не казалось, что это почти что PostgreSQL? ;) > и повторы будут 100% уникальность достиг при помощи id но мне кажется это плохой подход и я это хотел спросить в чате Но только Вы можете знать, какой там настоящий уникальный ключ. См. http://www.databasesoup.com/2015/03/primary-keyvil-reprised.html , в общем. > проблем (вроде как) не должно быть и скедулер будет смотреть в правильные места и отсекать пустые партиции "Choosing the target number of partitions that the table should be divided into is also a critical decision to make. ... Too many partitions can mean longer query planning times and higher memory consumption during both query planning and execution, as further described below " ну и так далее по тексту. Ну и да, в non-aligned c partitions запросах никакой pruning просто невозможен, а это для простых запросов — O(n) там, где раньше было O(1), где n — кол-во partitions. > банальный селект по времени из диапазона + domain_id Т.е. created_at всегда есть в запросах? Тогда повезло, казалось бы.

> PostgreSQL-compatible да, так моя фраза была бы вернее, если оно ведет себя как постгрес и крякает как постгрес то это постгрес :) > Но только Вы можете знать, какой там настоящий уникальный ключ вот по это причине и хотел узнать, на сколько у меня хорошее решение с ТОЧНО уникальным ключом и может быть есть другой способ это решить меньшей болью, не более) > Ну и да, в non-aligned c partitions как я понимаю у меня красиво aligned по времени, но...откуда эта цитата вообще? тут? https://www.postgresql.org/docs/10/ddl-partitioning.html > created_at всегда есть в запросах? Тогда повезло, казалось бы. ага, это и радует

> оно ведет себя как постгрес и крякает как постгрес то это постгрес Только не совсем, и в их косяках проект не виноват. В общем, всё это не очень приятно. > вот по это причине и хотел узнать, на сколько у меня хорошее решение Вы не поняли... это можете знать вы и только вы! Я не просто так дал ссылку, между прочим. > откуда эта цитата вообще? Это цитата из меня (и "общих знаний о природе", т.е. о том, как это всё работает). ;) Смотрите: Append (cost=0.14..658.02 rows=264 width=6020) (actual time=1.817..1.857 rows=0 loops=1) Buffers: shared hit=528 -> Index Scan using transactions_2000_01_col2_idx on transactions_2000_01 (cost=0.14..2.44 rows=1 width=6020) (actual time=0.019..0.019 rows=0 loops=1) Index Cond: (col2 = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a10'::uuid) Buffers: shared hit=2 -> Index Scan using transactions_2000_02_col2_idx on transactions_2000_02 (cost=0.14..2.44 rows=1 width=6020) (actual time=0.005..0.005 rows=0 loops=1) Index Cond: (col2 = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a10'::uuid) Buffers: shared hit=2 ... и сотни аналогичных узлов... -> Index Scan using transactions_2021_12_col2_idx on transactions_2021_12 (cost=0.14..2.44 rows=1 width=6020) (actual time=0.006..0.006 rows=0 loops=1) Index Cond: (col2 = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a10'::uuid) Buffers: shared hit=2 Planning Time: 9.733 ms Execution Time: 1.915 ms Т.е. запрос по полю, которое не входит в ключ партиционирования, тут минимум в 200 раз медленнее, чем до него. И сделать с этим ничего нельзя. Также обратите внимание на "прекрасное" время планирования (особенно по сравнению с временем выполнения).

это даже с enable_partition_pruning ?

> Также обратите внимание на "прекрасное" время планирования (особенно по сравнению с временем выполнения). Но это же весьма синтетические результаты. Понятно, что в общем случае так оно и будет — planning time будет больше, чем execution time, но при этом не стоит забывать и о прочих факторах: количество партиций, их размерность (в строках), частоту повторения значения в поле, не входящем в ключ партициционирования В моем случае (PG 10.9.6, 124 партиции, суммарно ~1.5 миллиарда записей, партиции помесячные, значение в среднем появляется около 50 раз в рамках партиции) при поиске по индексированному полю, не входящему в ключ партиционирования, время планирования в 4-12 раз меньше, чем время выполнения

очень похоже на мой случай, а не подскажете как у вас ПК выглядит? просто таймстемп или что-то еще?

left_val := ext.get_timestamp(date_trunc('month', now()::date + interval '1 month'),0)::timestamp; right_val := ext.get_timestamp(date_trunc('month', now()::date + interval '2 month'),0)::timestamp; create_table := 'CREATE TABLE ' || prefix || quote_ident(tablename_var) ||' PARTITION OF v083.p_log FOR VALUES FROM (' || quote_literal(left_val) || ') TO (' || quote_literal(right_val) || ')'; Ну и дальше через execute

спасибо

Естественно. Как Вы предлагаете это делать, хотя бы теоретически?

для меня странно, что ПГ трогает все партиции, но очевидно вопрос скрыт в запросе и схеме, поэтому я и удивлен

> Но это же весьма синтетические результаты. Да, это синтетические результаты, конечно. Но сам принцип от этого менее реальным не становится (а эти результаты его просто наглядно демонстрируют). В реальности разница обычно меньше, но именно потому, что выполнение дольше. > В моем случае (PG 10.9.6, 124 партиции, суммарно ~1.5 миллиарда записей, партиции помесячные, значение в среднем появляется около 50 раз в рамках партиции) при поиске по индексированному полю, не входящему в ключ партиционирования, время планирования в 4-12 раз меньше, чем время выполнения Во-первых, версии 10.9.6 у vanilla PostgreSQL не существует. ;) Во-вторых, partition pruning в v10 — одно название, грубо говоря. В-третьих, если бы partitions там не было (и если бессовестно считать, что партиционированная и непартиционированная таблицы в плане bloat и т.п. обслуживания одинаково хороши), то время планирования было бы где-то в 200-600 раз меньше, чем время выполнения (и оба они были бы куда меньше, чем у вас сейчас).

Эээ... любая СУБД в этой ситуации будет трогать все partitions. Хрустальных шаров у них нет, откуда им знать, в какой/каких из partitions находятся нужные rows? Т.е. объясните мне, почему Вас это удивляет? ;)

либо у нас разница в терминологии, либо я не понимаю где тут неправда https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITION-PRUNING возможно ваще трогать это смотреть метадату, а моё трогать это листать страницы в партиции

Да причём тут "терминология"? Вы понимаете, как это работает в принципе? > возможно ваще трогать это смотреть метадату, а моё трогать это листать страницы в партиции Неважно. Вы можете ответить на этот вопрос: "откуда им знать, в какой/каких из partitions находятся нужные rows?".

партиция Х содержит все от А до Б, мой запрос попадает в промежуток А+1 и Б-2, зачем мне смотреть партицию М где данные от Г до Д

А, Вы не поняли пример и ситуацию, кажется. Вот тут https://t.me/pgsql/346629 показан запрос SELECT ... FROM transactions WHERE col2 = <some_uuid>;, а сама таблица партиционирована по датам. > мой запрос попадает в промежуток А+1 и Б-2 Т.е. никуда он не попадает, вот и всё. Каждая partition потенциально содержит нужные rows, и узнать об этом СУБД никак не может.

да, я не понял ситуацию) в вашем случае без шансов значит, в моём все лучше, к счатью > Т.е. никуда он не попадает, вот и всё. а тут я опять не соглашусь, но видно я о своем случае, а вы о своем

Так это я всё писал на эту тему: >> Ну и да, в non-aligned c partitions > как я понимаю у меня красиво aligned по времени, но...откуда эта цитата вообще? Под aligned имеется в виду, что [почти] все запросы "красиво" соотносятся с ключом партиционирования (т.е. в их WHERE есть ключ партиционирования, и данные им нужны всего из нескольких partitions). Если у Вас так — то да, это aligned (т.е. повезло).

да, у меня именно так (причем "почти" строго выполняется)

Время планирования (и количество используемых locks), тем не менее, всё ещё зависят от количества partitons. Т.е. не стоит использовать сотни, если для обеспечения адекватного maintenance достаточно десятков.

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

Одно дело хранить, а другое — обрабатывать при выполнении каждого запроса. Если каждый в итоге выбирает мало partitions — это может быть почти незаметно (т.к. сам выбор partition при pruning — O(log n), и в итоге блокируется только то, что нужно). Но с DDL, кажется, до сих пор всё не так хорошо. И autovaccum нужно будет анализировать немного больше, ну и т.п.

если под Н у нас колво партиций, то для меньше 1000 мне кажется можно не сравнивать) речь о меньше чем 0.1мс идёт

Да, оно. И да, речь где-то о таких цифрах (но отличие в несколько раз всё равно будет). ;) Так что эффект на практике зависит от TPS (для 10K/s простых запросов по PK это может быть существенно, а в остальных случаях — скорее всего, нет).

буду проверять)