Добрый день, есть такой кейс, что таблица разрослась до больших

размеров (700Гб+) и необходимо ее раздробить на более мелки таблицы по годам. В общем начал смотреть в сторону партицированния, но понял, что это не решит нашу проблему. Из этого возникает следующий вопрос, раздробить то таблицу по годам можно, а как сделать так чтоб для приложения это было максимально прозрачно, может какие функции придется добавить или еще чего?

нет. тут только партиционироваие . иначе прозрачно не выйдет

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

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

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

в контексті основної табблиці зменьшить якщо правильно налашутвати партицювання. У нас на проєкті кейс з партиціями аналітики по місяцям, теж немало даних але все живенько +- якщо забагато для БД вцілому - дивись на шардування на інші бази даних

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

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

Какая проблема? Какое ещё "восстановление"?! У меня впечатление, что Вы начитались какой ерунды, и теперь за это ответит ваша БД. :( Почитайте лучше документацию, а там уже примете решение, нужно это Вам вообще или нет...

Тут решается проблема, чтоб сократить время восстановления из дампа БД в случае если все разворотит к....Если мы будем восстанавливать полный дамп, то это займет большое кол-во времени.

> чтоб сократить время восстановления из дампа БД Сколько же там "восстанавливаются" БД из дампов при таких размерах (просто любопытно)? > если все разворотит к.... Что "разворотит", в каком случае, вследствие чего? Вы можете выражаться яснее? ;(

``` Что "разворотит", в каком случае, вследствие чего? Вы можете выражаться яснее? ;( ``` Здесь чисто предостережение, а так например кривые миграции или что-то произойдет с ФС на сервере. Полная БД весит больше 1Тб на сколько мне известно, так как к этой БД доступа нет, то и проверить время восстановления я не могу. Так же как там снимается бэкап мне не известно. Единственное это есть предположение, что будет восстанавливаться больше недели подобный дамп. Порезанная версия дампа, восстанавливается около 2-х суток.

> например кривые миграции или что-то произойдет с ФС на сервере. Т.е., disaster — дампы тут вообще ни при чём (восстановили backup / переключились на реплику и работаете дальше). > Единственное это есть предположение, что будет восстанавливаться больше недели подобный дамп. Какая Вам вообще разница, сколько он будет восстанавливаться? Я так и не понял, зачем бы тут вообще дампы, а уж "пляски" для того, чтоб их было быстрее снимать/восстанавливать (и всё равно это занимает "около 2-х суток"!) — это что-то крайне странное... нет?

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

1) pg_dump — и правда довольно сложная и неудобная утилита. Просто неиспользуйте её для этого! Есть pg_basebackup, есть ещё лучшые сторонние решэния: pg_probackup, wal-g, etc — не надо идти сложным путём и мучить pg_dump. 2) В большынстве случаев на современном жэлезе pg_dump восстановит тэрабайт существенно менее, чем за день. Есть, конечно, варианты всяких там масс gin/gist индэксов, которые могут работать гораздо дольшэ... Но это редкость. Как у вас там неделя получилась? 3) Ну, нет у вас реальных данных — ну, нагенерите себе тэстовую среду с примерно тем жэ распределением всех возможных ключей. Тэрабайт тэстовых данных — ну, не такой объём, чтобы не поднять это на тэстовой машынке всё-таки. Чего гадать лишний раз.

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

Оно у всех примерно такое же (у некоторых — ещё и похуже), и так должно быть. У того, кто этого не понимает (не понимает, почему это так) — проблемы с основами CS.

> В большынстве случаев на современном жэлезе pg_dump восстановит тэрабайт существенно менее, чем за день. Или не восстановит (а восстановит за пару недель) — и такие примеры тут приводили неоднократно, я так помню. "Радость" дампов в том, что предсказать это в общем случае невозможно.

>проблемы с основами CS. Или у тебя — проблемы с практическим применением твоих обшырных теоретических познаний.

1) Спасибо, изучу другие утилиты. 2) Железо кусок г.... с которым приходится работать и ничего изменить нельзя. 3) Угу, буду пробовать. Я вообще не претендую на звание DBA и не стою даже близко, поэтому и пришел за уточнениями в чат. Спасибо!)

Это ерунда, разумеется. Может, расскажете мне что-то конкретное на эту тему, а?

Так ужэ говорил: https://t.me/pgsql/480286 Никакой особой магии в разыменовании таблиц по каким-то большым подблокам нет, его, конечно, можно сделать не менее эффективным, чем наш чегодняшний поиск по btree.

1) Разумеется, можэт -— особенно учитывая, что у нас таблицы обозначаются 32-битными цэлыми. 2) И, разумеется, любой алгоритм поиска как таковой — можно реализовать независимо от ого, будут у нас как-то названы отдельные части таблицы или не будут. Точно такжэ можно запихать все данные в один индэкс (по всем партицыями) или, наоборот, поделить все данные и индэксы по каким-то частям одной таблицы — независимо от наличия или отсутствия партицыонирования.

> Разумеется, можэт -— особенно учитывая, что у нас таблицы обозначаются 32-битными цэлыми. Разумеется, нет. Понятно, думать Вы не стали, а жаль. > особенно учитывая, что у нас таблицы обозначаются 32-битными цэлыми. Причём тут это вообще?! > И, разумеется, любой алгоритм поиска как таковой Которой в примере, о котором я советовал подумать, кардинально разный — но кого это волнует, правда (формулу оценки такой эффективности для сферического коня в вакууме Вы уже разработали, я смотрю)? ;) > Точно такжэ можно запихать все данные в один индэкс Причём тут какие-то "запихивания"?! Методы поиска (доступа) к rows в b-tree и в партиционированной таблице существенно разные (несмотря на то, что "из космоса" они очень похожи) — вот о чём стоит подумать... А ещё стоило бы подумать о том, что либо все (как учёные, так и разработчики всех СУБД) в этом не разбираются... либо только Вы. :(

> к rows в b-tree и в партиционированной таблице существенно разные Это личные персональные проблемы postgresql, а не какая-то мировая константа.

Нет, это именно что мировая константа! Или же Вы мне быстро, решительно покажете алгоритмы, которые позволяют сделать на каких-то структурах, подходящих для партиционирования, то же, что делает b-tree, и к тому же хотя бы потенциально имеющие не худшую (не студенческо-асимптотическую, а реальную!) производительность, чем эта структура данных (и алгоритмы её обработки).

>на каких-то структурах, подходящих для партиционирования, то же, что делает b-tree, Это, ВНЕЗАПНО, b-tree.

Внезапно, нет. Суть партиционирования в том, чтоб его (и вообще какой-то единой структуры данных) там не было — это не очевидно? (Иначе то, что по сути является разнообразной "сборкой мусора", никуда не денется, и линейно масштабироваться не будет.) И да, партиционирование — это конкретный метод физического хранения (много таблиц). Можете подробно рассказать (без избыточного handwaving), как в нём эффективно применить для поиска именно b-tree? Если попробуете, то в процессе станет очевидно, почему это не получится, между прочим.

>Иначе то, что по сути является разнообразной "сборкой мусора", никуда не денется, и линейно масштабироваться не будет. Этот вопрос — насколько нам требуется разного рода housekeeping — зависит от структуры хранения и применяемых алгоритмов, и можэт быть одинаково решён с партицыями и без. А, ну да... Конкретная схема партицыонирования несколько препятствует всем другим реализацыям физической группировки страниц. Впрочем, дажэ не полностью их запрещает. Потому некоторые варианты структуры хранения после реализацыя партицыонирования перестанут быть доступными, это да. Но это... Ну, в любом случае двух структур хранения в одних данных не будет. >Можете подробно рассказать (без избыточного handwaving), как в нём эффективно применить для поиска именно b-tree? Нельзя. Я дважды пытался начать это писать — и понял, что тупо переписываю основные принцыпы работы btree. Безсмысленное занятие. Лучшэ ты приведи пример, где btree нельзя построить на партицыонированной таблицэ — тогда я покажу, где ты ошыбся в рассчётах. PS И да, btree можэт быть как один на все партицыи, так и не один (с чем-то вроде начала ключа btree в виде какой-то другой структуры — tree или, как в постгресе, линейной). Он можэт как иметь ключ партицыонирования в начале и группировку страниц btree по партицыям (тогда любимый всеми mass delete делается несколько проще), так и не иметь этого (и тогда алгоритмы housekeeping становятся несколько сложнее, но всё равно вполне реализауемыми). И это всё, на самом деле, независит от того, есть ли у нас партицыи или эти куски ключа для физической группировки не образуют таблицы — а являются более мелкими сущностями.

> зависит от структуры хранения и применяемых алгоритмов Вот именно. Кстати, именно в применяемых PostgerSQL для выбора partitions структурах housekeeping тупо нет (потому что они фиксированные для текущего состояния схемы partitions — когда она меняется (attach / detach), они тупо перестраиваются, насколько я помню). > и можэт быть одинаково решён с партицыями и без. Соответственно, совсем одинаково решён он быть не может (см. выше). ;) > Конкретная схема партицыонирования несколько препятствует всем другим реализацыям физической группировки страниц. И если это всё ещё "классическое" partitioning, то эта схема статична (в отличие от b-tree — что скорее уж минус, чем плюс). > Лучшэ ты приведи пример, где btree нельзя построить на партицыонированной таблицэ Это какой-то неясный вопрос. Я писал о классическом partitioning — вот есть у нас статичное разбиение на 100000 таблиц (допустим, по RANGE (id); в каждой (или не каждой) может и быть индекс по Id — это уж как хотите), а задача состоит в обеспечении не менее эффективного поиска по WHERE id = $1 (ну и по WHERE id >= $1 AND id < $2 ), чем это делало бы b-tree на одной таблице с точно теми же данными. Так вот нужно найти эффективные алгоритмы и структуры данных, эту задачу решающие. Вы об этом писали (если нет, тогда мы вообще обсуждаем неизвестно что)? > И это всё, на самом деле, независит от того ... а являются более мелкими сущностями. Зависит. И да, такие схемы (с т.н. "tablets", для динамического partitioning и/или sharding) в распределённых СУБД, насколько я помню) тоже существуют, но "чистый" overhead у них ещё выше (а существуют потому, что там он "отбивается", кажется).

>Так вот нужно найти эффективные алгоритмы и структуры данных, эту задачу решающие. btree на весь датасет, разумеется. В смысле — один на все таблицы. Дажэ пресловутое удаление партицый из этого индэкса будет быстрым — lock таблицы, очистка одной записи из верхней или второй сверху страницы. И дажэ остальной уборки мусора можно избежать — если поделить и сгруппировать страницы "точно принадлежащие одной партицыи" — то большынство удалённых страниц после того обновления индэкса — можно смело массово очищать просто по факту принадлежности к соответствующим партицыям.

> они тупо перестраиваются, насколько я помню). Прямо вся pg_partitioned_table? Вряд ли. Ну и, в любом случае — это проблемы текущей реализацыи pg (которых много).

Нет, прямо вся структура данных в RAM (отсортированный список), которая используется для поиска нужных partitions, я имел в виду.

"Разумеется" не достаточно — опишите подробнее. А то у меня ощущение, что тут и просто глобальный index on (id) может подразумеваться — а он существенно проиграет даже тем структурам, что сейчас есть в PostgreSQL (просто за счёт разнообразных overheads). А если это b-tree именно по ключу id со значениями OID таблиц, тогда тут получается "лишняя" страница и переключение на поиск в другом b-tree (когда сначала найдена таблица, придётся работать уже с её индексом, начиная с корня). > удаление партицый из этого индэкса будет быстрым — lock таблицы, очистка одной записи из верхней или второй сверху страницы. Ничего себе! И вообще все (включая читателей) должны быть как минимум блокированы при этом, что ли (c одним b-tree такого и близко нет, например)?! > можно смело массово очищать Сразу? Т.е. от detach всё просто останавливается до конца очистки? В общем, это не подробное описание... но проблемы будут у любой подобной схемы, вот в чём суть.

>А то у меня ощущение, что тут и просто глобальный index on (id) может подразумеваться Именно он и подразумевается. > он существенно проиграет даже тем структурам, что сейчас есть в PostgreSQL (просто за счёт разнообразных overheads). С чего бы? id файла с таблицэй там, по сути, один на страницу, поиск по этому id — уж никак не дольшэ, чем все сегодняшние развлечения с поиском файла в fs дажэ в рамках одной таблицы (напомню, что у неё сейчас до 30k файлов можэт быть в основном heap). >А если это b-tree именно по ключу id со значениями OID Нет, я это не имел в виду. >должны быть как минимум блокированы при этом, что ли Если вы любите именно удаление партицый как сделано сейчас — то да. Конечно. Как сейчас и происходит. Если нет — то пожалуйста, можэте организовывать в любой вообще последовательности. Можэте крайний xmax записать в индэкс, отметив, что это для всей ветки. Можэте эту страницу делить на до/после. >Т.е. от detach всё просто останавливается до конца очистки? Почему останавливаются? Что туда не надо ходить — все и так видят, а уж очищать можно хоть вакуумом, хоть при поиск новой свободной страницы... > но проблемы будут у любой подобной схемы, вот в чём суть. Реальная схема — практически независит от того, будет таблица партицыонирована или нет. У нас в любом случае данные получаются указателем на указатели указателей, и их в любом случае можно тасовать достаточно произвольно — независимо от того, названы какие-то диапазоны отдельными именами и видны ли они как "отдельная таблица" в схеме или нет.

> С чего бы? С того бы, что длину значения (ctid) придётся увеличивать, конец истории. > id файла с таблицэй там, по сути, один на страницу По какой это "сути"? Это self-balancing tree — как "записи лягут", так и будет. > поиск по этому id — уж никак не дольшэ Дольше, см. выше (чуда не произойдёт). > чем все сегодняшние развлечения с поиском файла в fs Так-так... это тут откуда взялось?! ;) Это уже (ортогональное) изменение структуры хранения, зачем Вы всё в кучу мешаете? > дажэ в рамках одной таблицы (напомню, что у неё сейчас до 30k файлов можэт быть в основном heap). Кстати: у Вас есть benchmarks, которые доказывают, что в современных OS/FS это вообще сколько-нибудь существенно? > Если вы любите именно удаление партицый как сделано сейчас — то да. Конечно. Как сейчас и происходит. DETACH PARTITION partition_name CONCURRENTLY; намекает нам, что нет, не сделано и не происходит. ;) > Можэте эту страницу делить на до/после. Не могу, см. выше. > Что туда не надо ходить — все и так видят Вот это опять смутное описание, не более того. :( > Реальная схема — практически независит от того, будет таблица партицыонирована или нет. Ещё как зависит. > У нас в любом случае данные получаются указателем на указатели указателей А вот с существующей реализацией — не получаются (всё обычные таблицы, без лишнего уровня indirection).

>С того бы, что длину значения (ctid) придётся увеличивать, конец истории. Не увеличивать, а переделать нафиг, понятное дело. >По какой это "сути"? Мы всё ещё обсуждаем ситуацыю, когда ключ партицыонирования находится в начале ключа btree. Потому — ссылки на две соседние партицыи могут находиться только по одному пути от корня к листьям. >Дольше, см. выше (чуда не произойдёт). Не дольшэ, поскольку в идеальном случае длина ссылки id соответствует длине таблицы, независимо от её расположэния. >, которые доказывают, что в современных OS/FS это вообще сколько-нибудь существенно? Я совершэнно уверен в обратном — поскольку при скорости tuple deforming постгреса затормозить его такой мелочью не слишком реально. >что нет, не сделано и не происходит. ;) Насколько я понимаю — он просто ждёт пока можно получить блокировку. >Вот это опять смутное описание, не более того. :( У вас-то вообще никакого нет — только ссылки на неназванные авторитеты и какие-то попытки описать что-то, опираясь на существующую реализацыю в постгресе.

> Не увеличивать, а переделать нафиг, понятное дело. И как, конкретно? > Мы всё ещё обсуждаем ситуацыю, когда ключ партицыонирования находится в начале ключа btree. Да. Это значит, что его суффикс (у которого могут быть миллиарды значений для данного значения partition key) чаще всего решает, где находятся записи. > могут находиться только по одному пути от корня к листьям. Разумеется, нет — большинство из них будут находиться в разных листах (и internal nodes). Ну и, с другой стороны, в каких-то листах значения из разных partitions наверняка будут смешаны. > Не дольшэ, поскольку в идеальном случае длина ссылки id соответствует длине таблицы, независимо от её расположэния. Так вот сейчас это не так — существующее решение просто компактнее, и то, что описано выше, ему неизбежно проиграет. > Я совершэнно уверен в обратном Значит, это "мимо" > при скорости tuple deforming постгреса. Вы сейчас всерьёз сравнили производительность перекладывания байтов в памяти с производительностью [потенциально] дискового доступа, мне не показалось?! > он просто ждёт пока можно получить блокировку. Ага, но другие транзакции не блокирует (аналогично другим CONCURRENTLY). > У вас-то вообще никакого нет Что Вы несёте, я извиняюсь?! Откройте, наконец, любой "фундаментальный" учебник по проектированию СУБД, и читайте оттуда про это хоть главами подряд (всё это давно изучено в "академии", разжёвано в статьях и учебниках... а то и уже забыто теоретиками, за давностью лет ;) ). > и какие-то попытки описать что-то, опираясь на существующую реализацыю в постгресе. И да — Вы мне что, можете показать существенно лучшую существующую реализацию в какой-то СУБД (а не вот эти смутные прожекты)?