Принципы работы партицирование в postgres

Партицирование нужно для быстрого удаления, перемещения партиций в отдельный tablespace (архивация), а не для ускорения select

если мы разбиваем табличку например на 3 части например по category_id, а записи ищем по полю title, то...

... то вы не по тому полю партиционируете :)

Партицирование нужно для быстрого удаления, перемещения партиций в отдельный tablespace (архивация), а не для ускорения select

Кто тебя этому научил?

Это ответ ТС на его юзкейс, который его только замедлит.

Зачем же ты даёшь заведомо неверный ответ, который его только замедлит? Судя по вопросу ТСа, он вполне догадывается о том, для чего ему надо партиционирование, как оно работает и что он сделал не так (и вообще непонятно даже зачем спрашивает, когда и так всё понятно), а ты отгружаешь в комменте заведомую неправду. И это при том, что тред читает не только ТС, среди посетителей ЛОРа ведь могут оказаться даже несовершеннолетние, которые могут принять твои слова за чистую монету. Даже не знаю, что с тобой делать - может подзабанить для профилактики?

Замедлит не ТС, а его запросы. Пруфы, бенчмарки в тред, где партиционирование ускоряет выборки из таблиц, которые до этого имели индексы по полю, по которому проводится партиционирование, именно в PostgreSQL. Или сам самозабанься.

Поиск по индексу не бесплатный и если можно индекс сделать меньше, то будет быстрее работать.
Индексы по таблицам-партициям будут небольшие для каждой таблицы.

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

Или если таблицы резать на части, то запросы делать нужно по тому принципу, по которому они разрезаны?

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

Пруфы, бенчмарки в тред, где партиционирование ускоряет выборки из таблиц, которые до этого имели индексы по полю, по которому проводится партиционирование, именно в PostgreSQL.

Воинствующее невежество, ЛОЛ

Не пойму, почему партицирование быстрее?

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

Cмысл слова «быстрее» в данном контексте надо понимать как «быстрее в некоторых существенных случаях». Собственно, это достаточно понятными словами изложено на страничке https://www.postgresql.org/docs/current/static/ddl-partitioning.html:

5.10.1. Overview

Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits:

• Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. The partitioning substitutes for leading columns of indexes, reducing index size and making it more likely that the heavily-used parts of the indexes fit in memory.
• When queries or updates access a large percentage of a single partition, performance can be improved by taking advantage of sequential scan of that partition instead of using an index and random access reads scattered across the whole table.
• Bulk loads and deletes can be accomplished by adding or removing partitions, if that requirement is planned into the partitioning design. Doing ALTER TABLE DETACH PARTITION or dropping an individual partition using DROP TABLE is far faster than a bulk operation. These commands also entirely avoid the VACUUM overhead caused by a bulk DELETE.
• Seldom-used data can be migrated to cheaper and slower storage media.

The benefits will normally be worthwhile only when a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the application, although a rule of thumb is that the size of the table should exceed the physical memory of the database server.

...

Собственно, это достаточно понятными словами изложено на страничке

Или вот то же самое по-русски: https://postgrespro.ru/docs/postgresql/10/ddl-partitioning#DDL-PARTITIONING-O...

Воинствующее невежество, ЛОЛ

Самокритика? Я удвою вот это:

бенчмарки в тред, где партиционирование ускоряет выборки из таблиц, которые до этого имели индексы по полю, по которому проводится партиционирование, именно в PostgreSQL.

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

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

Да, но неплохо бы не только молиться на то, что там написано. ;)

быстрее в некоторых существенных случаях

Это вопрос оценки. Мне вот кажется, что процент этих самых случаев довольно мал.

Query performance can be improved dramatically in certain situations,

Меня всегда восхищала эта фраза (кочующая из версии в версию) --- я подозреваю, что это тонкий английский юмор (одно из словарных определений «драматично» --- «трагически»). И именно так улучшается производительность в большинстве случаев использования partitioning.

when most of the heavily accessed rows of the table are in a single partition or a small number of partitions

Да, безусловно, только вот в большинстве ситуаций доступ всё равно идёт по индексу/ам, и «выигрыш» зависит от распределения rows в heap.

When queries or updates access a large percentage of a single partition,

Безусловно. Вы часто это делаете?

А вот всё остальное уже про maintenance.

быстрее в некоторых существенных случаях

Это вопрос оценки. Мне вот кажется, что процент этих самых случаев довольно мал.
...
... Безусловно. Вы часто это делаете?

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

В общем-то, хранение в БД и обработка каких-то данных, привязанных к временным периодам, это довольно типичный случай - и здесь секционирование таблиц (которое «partitioning») может реально ускорить работу. Возьмите, к примеру, базы данных разных финансовых организаций...

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

Я удвою вот это:

бенчмарки в тред

Невероятный успех темы - сразу двое забаненных в яндексе в одном и том же треде!

Невероятный успех темы - сразу двое забаненных в яндексе в одном и том же треде!

Ты не болтай, а покажи конкретные benchmarks.

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

Нет, по моей логике, «there's no free lunch». У партиционирования в PostgreSQL есть (немалая) стоимость в плане налагаемых им на модель данных ограничений. К тому же, его планирование и реализация отнимают время (совсем не бесплатное). И за выигрыш в производительности, например, в 5%, расплачиваться таким образом не стоит.

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

Когда партиционирование нередко не очень-то помогает (а то и мешает), внезапно. ;)

и здесь секционирование таблиц (которое «partitioning») может реально ускорить работу. Возьмите, к примеру, базы данных разных финансовых организаций...

Да брал уже... вы пробовали полноценно сравнивать в более-менее новых версиях PostgreSQL (начиная с 9.6)?

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

Если бы «никто» --- проблем было бы меньше. Да Вы хоть на это обсуждение посмотрите... Вообще, его нередко советуют так, как будто это magic fairy dust.

Да брал уже...

И каков результат? На огромных массивах данных оно просто обречено давать существенное улучшение производительности (предполагается, что партиционирование соответствует юзкейсам, а не перпендикулярно им как в случае ТСа)

Тут просто запросов много разных, по разным полям. А партицировать по нескольким полям можно?)

А партицировать по нескольким полям можно?)

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

Тут просто запросов много разных, по разным полям.

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

И каков результат?

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

На огромных массивах данных оно просто обречено давать существенное улучшение производительности

На каких это основаниях оно обязано давать улучшение производительности вообще?

Вот неспроста же кто-то спрашивал:

Пруфы, бенчмарки в тред, где партиционирование ускоряет выборки из таблиц, которые до этого имели индексы по полю, по которому проводится партиционирование, именно в PostgreSQL.

И тут уже цитировали документацию:

The partitioning substitutes for leading columns of indexes, reducing index size and making it more likely that the heavily-used parts of the indexes fit in memory.

Так вот, у простых, наивных людей возникает естественный вопрос к разработчикам такой СУБД: как это вам удалось реализовать такой механизм индексации, который работает хуже (substitutes for leading columns of indexes), чем жалкое отсечение (кстати, в версиях до 11, линейным поиском!) по списку constraints?!

Можете на него ответить?

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

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

На каких это основаниях оно обязано давать улучшение производительности вообще?

На том основании, что существенно уменьшается объем I/O. Например, выше же предлагалось посмотреть на данные финансовых организаций. Они могут веками копить каждую копеечную транзакцию, но эта информация им не нужна всё время. Год прошел, выпустили отчеты, построили какие-то статистические таблицы для последующего сравнительного анализа (чтобы аналитика потом не ходила каждый раз по транзакциям всего года) - всё, про партицию за этот год можно забыть, она там осталась для всяких исключительных случаев.

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

Вот неспроста же кто-то спрашивал:

Пруфы, бенчмарки в тред

Если человек просит бенчмарков, которыми завален весь интернет - это уже многое говорит о его интеллекте. Думаю, с такими нам тут обсуждать нечего.

Так вот, у простых, наивных людей возникает естественный вопрос к разработчикам такой СУБД: как это вам удалось реализовать такой механизм индексации, который работает хуже (substitutes for leading columns of indexes), чем жалкое отсечение (кстати, в версиях до 11, линейным поиском!) по списку constraints?!

Да их пока Убер в жопу не клюнул, они походу и не подозревали, что кто-то серьёзно может пытаться пользоваться их БДой. Сейчас вон гонят мажорную версию за версией. Я так понимаю, что там не только архитектурная проблема, которая на каждое обращение вынуждает сервер пересчитывать все имеющеиеся констрейнты чтобы определить какие партиции или для каких партиций индексы надо вытаскивать, но и оптимизатор не совсем готов прозрачному использованию партиционированных таблиц. На уровне приложения, однако, КМК, это должно решаться разрешением партиций на уровне приложения и построением запросов к индивидуальным партициям (приложение же обычно знает как партиционированы его данные, а если нет - то такой функционал нетрудно добавить)

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

Для этого существуют индексы. И профита от них в реальности обычно побольше, чем от заменяющих их костылей.

На том основании, что существенно уменьшается объем I/O.

По сравнению с чем? С неидексированными таблицами --- да, но тут обсуждается не это.

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

Вы что, всерьёз считаете, что executor PostgreSQL совсем дурной, и вычитывает гигабайты ненужных байтов индексов? ;)

Если человек просит бенчмарков, которыми завален весь интернет - это уже многое говорит о его интеллекте. Думаю, с такими нам тут обсуждать нечего.

Если человек не показывает ни одного бенчмарка, которыми, по его уверждению «завален весь интернет», может, ему на самом деле и показать-то нечего?

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

Да вы что? На эту историю все так среагировали, как мне кажется, в основном потому, что программисты «Убер»-а пытались прикрыть свою бездарность, наезжая на используемую технологию (ну надо же на зеркало-то попенять).

Сейчас вон гонят мажорную версию за версией.

О чём вы? Версии выходят ровно в том же темпе, что и раньше.

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

Да, архитектурные проблемы там есть... Только всё это «в огроде бузина», а на заданный вопрос не отвечает.

Я вам осторожно намекну, что в СУБД самого распространённого сейчас, пожалуй, дизайна (index-organized tables по умолчанию (или вовсе без вариантов) + хранение версий записей в каком-то отдельном месте (а не в таблицах/индексах, как в PostgreSQL)) партиционирование производительность запросов почти всегда снижает. Можете объяснить, почему?

Для этого существуют индексы. И профита от них в реальности обычно побольше, чем от заменяющих их костылей.

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

По сравнению с чем? С неидексированными таблицами --- да, но тут обсуждается не это.

По сравнению с непартиционированными таблицами.

Вы что, всерьёз считаете, что executor PostgreSQL совсем дурной, и вычитывает гигабайты ненужных байтов индексов? ;)

Понятия не имею. У него что есть индекс на индекс из которого он знает какую именно часть индекса надо вычитывать?

Если человек не показывает ни одного бенчмарка, которыми, по его уверждению «завален весь интернет», может, ему на самом деле и показать-то нечего?

Конечно мне нечего показать. А тот, кто хочет бенчмарков - идёт в интернет и смотрит что ему показывают те, кому есть что показать.

Да вы что? На эту историю все так среагировали, как мне кажется, в основном потому, что программисты «Убер»-а пытались прикрыть свою бездарность, наезжая на используемую технологию (ну надо же на зеркало-то попенять).

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

О чём вы? Версии выходят ровно в том же темпе, что и раньше.

PostgreSQL 9.0 вышел где-тов 2005м году

PostgreSQL 9.0 вышел в сентябре 2010го года

Убер опубликовал свою статью в июле 2016го

PostgreSQL 10.0 вышел в октябре 2017го года анонсируя логическую репликацию, декларативное партиционирование таблиц - то, за что их обсирал Убер.

Прямо сейчас пилят 11ю версию, в которой всё, что выпустили в 10й вроде как должно заработать более-менее :)

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

Да, архитектурные проблемы там есть... Только всё это «в огроде бузина», а на заданный вопрос не отвечает.

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

Я вам осторожно намекну, что в СУБД самого распространённого сейчас, пожалуй, дизайна (index-organized tables по умолчанию (или вовсе без вариантов) + хранение версий записей в каком-то отдельном месте (а не в таблицах/индексах, как в PostgreSQL)) партиционирование производительность запросов почти всегда снижает. Можете объяснить, почему?

Я не могу этого объяснить, я из параллельной реальности - у нас тут редко используют IOT, а для хранения версий записей используют журнал отката даже в постгресе (Write-Ahead Log).

Индекс тоже занимает место, при партиционировании фулл-скан по отдельной партиции может оказаться дешевле скана индекса

Наконец-то целых два отчасти правильных довода. Но, если кому-то в основном нужен fullscan партиции, у него уже не (нормальное) OLTP, а в DWH это, ну... как повезёт (все данные (при partitioning по дате) за достаточно большой период, без каких-либо дополнительных фильтров, IMHO, выбираются редко).

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

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

Если условие партиционирования отвечает твоим требованиям, то и (основной) индекс не придётся сканировать,

Скорее всего, придётся всё равно (обычно выборки захватывают небольшую часть partition); впрочем, YMMV.

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

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

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

Вот-вот.

Понятия не имею. У него что есть индекс на индекс из которого он знает какую именно часть индекса надо вычитывать?

Вы уверены, что знаете, как работает обычный индекс? ;)

Конечно мне нечего показать.

Значит, это не довод.

А тот, кто хочет бенчмарков - идёт в интернет и смотрит что ему показывают те, кому есть что показать.

А дело-то в том, что всё, что я там видел --- это случаи, когда кому-то очень хочется показать «преимущества» партиционирования (что ни говорите, а статью/blog/whatever писать-то надо ;) ), но, внезапно!, в процессе демонстрации получается, что как-то оно не очень, а далее начинаются передёргивания («я не стал делать индексы на непартиционированной таблице, потому что...» / «а теперь поразитесь производительности запросов (но только тех, где нужен fullscan конкретной partition) и т.д. и т.п.).

Ну не знаю, возможно вы такой охуенный гений,

Нет, я просто разбираюсь в архитектуре PostgreSQL.

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

Не беспокойтесь, им тоже не удалось.

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

Да вы что? А есть что предъявить a) реализованного и b) что и так не было в разработке до этой статьи? Proof links?

PostgreSQL 10.0 вышел в октябре 2017го года анонсируя логическую репликацию, декларативное партиционирование таблиц - то, за что их обсирал Убер.

Которые были в разработке задолго до этой статьи (более того, параллельно разрабатывали даже несколько реализаций).

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

Вот даты major releases PostgreSQL:

• 10 — 2017-10-05
• 9.6 — 2016-09-29
• 9.5 — 2016-01-07
• 9.4 — 2014-12-18
• 9.3 — 2013-09-09
• 9.2 — 2012-09-10
• 9.1 — 2011-09-12

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

А был вопрос? Вы там вбросили ничем не подпёртое утверждение,

Вот пример предложений, которые в русском языке являются вопросительными:

возникает естественный вопрос к разработчикам такой СУБД: как это вам удалось реализовать такой механизм индексации, который работает хуже (substitutes for leading columns of indexes), чем жалкое отсечение (кстати, в версиях до 11, линейным поиском!) по списку constraints?!

И в нём на это намекает не только вопросительный знак, но и даже употребление словосочетания «возникает естественный вопрос». ;)

А если серьёзнее, вопрос был задан потому, что это именно то, что подразумевают те, кто утверждает, что «партционирование обязано выигрывать».

а для хранения версий записей используют журнал отката даже в постгресе (Write-Ahead Log).

И опять же, не пишите о том, в чём не разбираетесь — версии записей PostgreSQL хранит прямо в таблицах и индексах (из-за этого и происходит bloat, нужен VACUUM и т.д.).

И опять же, не пишите о том, в чём не разбираетесь — версии записей PostgreSQL хранит прямо в таблицах и индексах (из-за этого и происходит bloat, нужен VACUUM и т.д.).

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

Теперь вопрос. Каким образом это снижает производительность запросов на партиционированной таблице по сравнению не-партиционированной?

версии записей PostgreSQL хранит прямо в таблицах и индексах (из-за этого и происходит bloat, нужен VACUUM и т.д.).

Теперь вопрос. Каким образом это снижает производительность запросов на партиционированной таблице по сравнению не-партиционированной?

Эээ... никак? ;) Потому что, как я уже писал ранее:

в СУБД самого распространённого сейчас, пожалуй, дизайна (index-organized tables по умолчанию (или вовсе без вариантов) + хранение версий записей в каком-то отдельном месте (а не в таблицах/индексах, как в PostgreSQL)) партиционирование производительность запросов почти всегда снижает.

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

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

Ну и отлично. Хоть я и не вижу как write amplification повысит производительность запросов, но раз вы не считаете это фактором, ограничивающим применение партиционирования, то непонятно нафига вы его вообще упомянули. По сути я от вас так и не увидел что конкретно в постгрессе на ваш взгляд могло бы свести к нулю выигрыш от партиционирования.

Хоть я и не вижу как write amplification повысит производительность запросов, но раз вы не считаете это фактором, ограничивающим применение партиционирования, то непонятно нафига вы его вообще упомянули.

Как это вы умудряетесь вычитывать в моих сообщениях то, что строго противоречит тому, что я написал? ;)

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

Да то же самое, что приводит партиционирование к проигрышу во всех остальных СУБД! Т.е. если бы архитектура PostgreSQL не отличалась от «обычной» в двух указанных отношениях, никакого выигрыша партиционирование не давало бы и в нём.

Т.е. если бы:

1. В PostgreSQL были бы index-organized tables (только или по умолчанию), а не heaps, как сейчас.
2. Версии записей хранились бы в каком-то отдельном месте (а не в таблицах/индексах, как сейчас).

Как это вы умудряетесь вычитывать в моих сообщениях то, что строго противоречит тому, что я написал? ;)

Сам удивляюсь :) Вот вы написали: «это одна из особенностей архитектуры, которая, наоборот, может дать возможность получить хоть какой-то выигрыш (именно в производительности запросов) от партиционирования.» И пусть я тут не согласен, КМК в любом случае влияние этого фактора на производительность в ту или иную сторону должно быть незначительно пока у вас не апдейтятся ключевые поля в большом количестве строк.

Да то же самое, что приводит партиционирование к проигрышу во всех остальных СУБД! Т.е. если бы архитектура PostgreSQL не отличалась от «обычной» в двух указанных отношениях, никакого выигрыша партиционирование не давало бы и в нём.

В «других СУБД» (достаю из под стола белую коробку с тщательно стёртой надписью «Oraclэ») проигрыша нет, сплошной выигрыш.

если бы: В PostgreSQL были бы index-organized tables (только или по умолчанию), а не heaps, как сейчас.

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

если бы: Версии записей хранились бы в каком-то отдельном месте (а не в таблицах/индексах, как сейчас).

Это не вы случайно написали про эиу особенность, что это «„это одна из особенностей архитектуры, которая, наоборот, может дать возможность получить хоть какой-то выигрыш“???

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

Почему? Поясните, пожалуйста.

В «других СУБД» (достаю из под стола белую коробку с тщательно стёртой надписью «Oraclэ») проигрыша нет, сплошной выигрыш.

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

Кстати, если я правильно слышал, версии там хранятся в rollback segment, а IOT не обязательны / не рекомендуются (кажется, может IOT только про PRIMARY KEY), правильно?

И, ради любопытства, какие именно архитектурные дефекты дают в Oracle эффект «проигрыша нет, сплошной выигрыш» от партиционирования? ;)

В общем случае это не осмысленно держать все таблицы в виде IOT. На практике я этого не вижу даже там, где они поддерживаются.

Ну а вот в других СУБД (по памяти):

• MySQL — в InnoDB только IOT, без вариантов. Версии в rollback segment.
• MS SQL Server — IOT по умолчанию (best practice), по любому индексу, использовать heap-ы не рекомендуется. Версии в tempdb.
• SQLite — только IOT по Primary Key, без вариантов. «Версии» в WAL (если используется).
• IBM DB2 — IOT нет, но пытается сохранять корреляцию index/heap при INSERT-ах. Про версии не помню.

Лучше иметь мелкие индексы, покрывающие основные юзкейсы.

Судя по списку выше, с вами многие не согласны. Впрочем, it depends.

Это не вы случайно написали про эиу особенность,

Да, писал. И?

Почему? Поясните, пожалуйста.

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

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

А что, есть ещё какие-то БД???

Кстати, если я правильно слышал, версии там хранятся в rollback segment, а IOT не обязательны / не рекомендуются (кажется, может IOT только про PRIMARY KEY), правильно?

Да, версии там хранятся в undo/rollback и журнале. ХЗ насчет primary key - IOT таблица должна же все поля в индексе иметь, а индекс может сканироваться и не по первому полю, другая БД как минимум это умеет. Но это было должно стоить ресурсов. И я не помню случаев когда такое случалось на практике.

И, ради любопытства, какие именно архитектурные дефекты дают в Oracle эффект «проигрыша нет, сплошной выигрыш» от партиционирования? ;)

Это дефекты не в Oracle, а дефекты в этом грёбаном мире, где всё меряется баблом. Обслуживание партиций довольно дорого, поэтому применяется только там, где необходимо. Там, где партиционирован - ие необходимо, там по определению от него сплошной выигрыш :)

В общем случае это не осмысленно держать все таблицы в виде IOT. На практике я этого не вижу даже там, где они поддерживаются.

Ну а вот в других СУБД (по памяти):

Это что сейчас было? Список СУБД, которые умеют в IOT? Речь о том, что само по себе IOT в общем случае не нужно. То, что партиционируется, зачастую будет иметь кучу деталей, которые нужны далеко не всем функциональным элементам системы. Мы просто храним данные и создаём те индексы, которые реально нужны для 99% запросов, поступающих от системы. Неиндексированных полей там обычно в разы больше.

Судя по списку выше, с вами многие не согласны. Впрочем, it depends.

Это список БД, которые поддерживают IOT. Я не говорю, что IOT совершенно бесполезно - наверное кому-то оо и в самом деле приносит пользу. Я просто не видел такого юзкейса. В любом случае к партишенгу оно всё сбоку.

Это не вы случайно написали про эиу особенность,

Да, писал. И?

Так это баг или фича? :)

sorry my touchpad.. Эта гнида пераставляет курсор вопреки моему желанию. Где заметил - исправил, но кусок пропустил. Ещё раз сорри.

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

Индекс тоже занимает место, при партиционировании фулл-скан по отдельной партиции может оказаться дешевле скана индекса

Наконец-то целых два отчасти правильных довода. Но, если кому-то в основном нужен fullscan партиции, у него уже не (нормальное) OLTP, а в DWH это, ну... как повезёт (все данные (при partitioning по дате) за достаточно большой период, без каких-либо дополнительных фильтров, IMHO, выбираются редко).
...

Так в этом-то вся и фишка... ) Cекционирование по своей сути не предназначено для работы с OLTP-данными. Это может дать выгоду там, где хранятся какие-то статичные/архивные данные и где нужно ускорить выполнение определенного набора запросов за счет использования «sequential scan» вместо использования индексов. Это вроде очевидно любому грамотному DBA/программисту БД, который, естественно, перед такой реорганизацией данных проведет соответствующие тесты и примет решение: нужно ему это или нет.

Ну а то, что на свете полно горе «спецов» (именно в кавычках), которые трудятся в стиле «слышал звон, да не понял, где он» - это не проблема разработчиков PostgreSQL. Это проблема дефицита действительно грамотных админов и программистов.

Ну вот есть СУБД-шка PostgreSQL с какой-то своей конкретной внутренней архитектурой и реализацией. Естественно, там есть и недостатки. Ну вот сделали в этой СУБД-шке доработку - добавили «Partitioning» и внятно указали, где это может пригодится. В чем проблема? Не нужно - не используйте...

Вы же критику-то развели абсолютно не по делу. Нигде в документации вам не навязывают использование «partitioning» и не обещают супер-ускорения во всех случаях.

... хотя и для OLTP можно придумать варианты, где использование «Partitioning» будет весьма разумным в плане ускорения запросов. На эту тему нельзя рассуждать абстрактно - здесь всё очень сильно зависит от конкретного приложения и схемы БД.

Почему что? Если нет апдейтов ключевых полей, то этот функционал никак не проявляется.

Если мы обсуждаем место хранения версий записей, то нет, проявляется, даже если нет «апдейтов ключевых полей».

Т.к. для heap-а это важно только отчасти, когда для HOT-updates есть место (т.е. их выигрыш тут только в том, что они делают single-page vacuuming, быстрее удаляя dead rows).

А так любой UPDATE создаёт версию записи где-то (в той же странице или в другой), которая записывается в него же. Т.е. если вы делаете «UPDATE t SET x = x + 1;» для «чистой» (без dead rows), плотно упакованной таблицы, её размер внезапно увеличивается вдвое.

А для индекса row version в случае HOT-update просто не создаётся.

А вот при INSERT-ах, если в heap есть «дыры» (свободное место), то записи пойдут туда (что может нарушить «естественную» корреляцию положения rows в heap с их положением в каком-то индексе). Т.е. плюс партиционирования в PostgreSQL тут в том, что оно локализует этот эффект в пределах секции.

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

Для heap это, опять-таки, связано только с интенсивностью UPDATE-ов. Т.е. если в той же странице есть место, запись туда и попадёт, используется HOT или нет.

А что, есть ещё какие-то БД???

Нда. Парафразируя Edsger W. Dijkstra:

It is practically impossible to teach good database practices to programmers that have had a prior exposure to Oracle: as potential database programmers they are mentally mutilated beyond hope of regeneration. ;)

Это дефекты не в Oracle, а дефекты в этом грёбаном мире, где всё меряется баблом.

Нет, это «дефекты» в реальности, где всё обусловлено причинно-следственными связями. ;) Ладно, я понял, что «дефект» Oracle состоит в неиспользовании IOT по умолчанию (возможно, в связи с ограничениями реализации).

Это что сейчас было? Список СУБД, которые умеют в IOT?

Нет, это сейчас был список СУБД, которые не умеют без IOT! То есть heaps в них тупо нет, или же (для MS SQL), если кто-то их использует, профессионалы обычно смотрят на него, как на идиота.

Речь о том, что само по себе IOT в общем случае не нужно.
Я просто не видел такого юзкейса.

Да вы что? А вот распространённая практика как бы намекает нам... ;)

В любом случае к партишенгу оно всё сбоку.

Нет, как раз IOT непосредственно связаны с partitioning. Понимаете, в IOT rows просто не могут храниться где попало, т.к. сама таблица — это b-tree.

Так это баг или фича? :)

Это особенность. У такой архитектуры есть свои преимущества, также, как у альтернативной — свои недостатки.

Так в этом-то вся и фишка... ) Cекционирование по своей сути не предназначено для работы с OLTP-данными.

Ничего подобного, это как раз одно из его назначений.

Это может дать выгоду там, где хранятся какие-то статичные/архивные данные и где нужно ускорить выполнение определенного набора запросов за счет использования «sequential scan» вместо использования индексов.

Ещё раз, партиционирование — это не про ускорение запросов, это про maintenance.

Это вроде очевидно любому грамотному DBA/программисту БД, который, естественно, перед такой реорганизацией данных проведет соответствующие тесты и примет решение: нужно ему это или нет.

Что ж вам-то не «очевидно», зачем оно вообще нужно? ;)

Вы же критику-то развели абсолютно не по делу.

Да вы что? То есть, никто здесь не утверждал, что партиционирование «обязано» выигрывать в производительности SELECT-ов, и т.д. и т.п., мне показалось?

Нигде в документации вам не навязывают использование «partitioning» и не обещают супер-ускорения во всех случаях.

Да, но, вообще-то, на «огромных» таблицах его зачастую стоит использовать, но совсем не для «ускорения»!

хотя и для OLTP можно придумать варианты, где использование «Partitioning» будет весьма разумным в плане ускорения запросов.

Трудно придумать обратное :) Если OLTP система со страшной скоростью собирает какие-то данные, то не просто так же она это делает - ими же как-то надо будет пользоваться потом :)

А так любой UPDATE создаёт версию записи где-то (в той же странице или в другой), которая записывается в него же. Т.е. если вы делаете «UPDATE t SET x = x + 1;» для «чистой» (без dead rows), плотно упакованной таблицы, её размер внезапно увеличивается вдвое.

Так, вот это я упустил.

А для индекса row version в случае HOT-update просто не создаётся.

Создаётся, в документе от Уберов показаны несколько вхождений одной и той же строки в индексе после апдейта: https://eng.uber.com/mysql-migration/

Нет, это сейчас был список СУБД, которые не умеют без IOT! То есть heaps в них тупо нет, или же (для MS SQL), если кто-то их использует, профессионалы обычно смотрят на него, как на идиота.

А что мы понимаем под «heap»? Мне казалось вы этим словом обзываете rollback сегмент, но в с писке для MySQL было явно указано, что он там есть, но почему-то надо использовать IOT. Похоже, я что-то упускаю в вашей цепочке расуждений.

Да вы что? А вот распространённая практика как бы намекает нам... ;)

Вполне возможно, что эта практика широко распространена только в вас лично.

Нет, как раз IOT непосредственно связаны с partitioning. Понимаете, в IOT rows просто не могут храниться где попало, т.к. сама таблица — это b-tree.

это b-tree, побитое на блоки, которые хранятся как попало в пределах табличного пространства. Так что в общем случае смысла в IOT не прослеживается.

Так это баг или фича? :)

Это особенность. У такой архитектуры есть свои преимущества, также, как у альтернативной — свои недостатки.

Я пока вижу только недостатки, хотя и не влияющие непосредственно на выгодность/невыгодность партиционирования

Ещё раз, партиционирование — это не про ускорение запросов, это про maintenance.

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

Что ж вам-то не «очевидно», зачем оно вообще нужно? ;)

Мне-то как раз очевидно - я воспринимаю то, что изложено в документации, без излишней гиперболизации и, соответственно, не вижу поводов для критики в адрес разработчиков PostgreSQL :)

Да вы что? То есть, никто здесь не утверждал, что партиционирование «обязано» выигрывать в производительности SELECT-ов, и т.д. и т.п., мне показалось?

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

Да, но, вообще-то, на «огромных» таблицах его зачастую стоит использовать, но совсем не для «ускорения»!

А я разве утверждал обратное? Иногда стоит использовать ради преимуществ «maintenance», иногда - ради ускорения конкретных запросов (ну вот потребует начальство, чтобы баланс считался за месяц супер бысто и всё тут), а иногда это дает преимущества и там и там. Но обсуждаем-то мы здесь вопросы, связанные с ускорением выполнения запросов, поэтому не надо уводить обсуждение в другую сторону.

Трудно придумать обратное :) Если OLTP система со страшной скоростью собирает какие-то данные, то не просто так же она это делает - ими же как-то надо будет пользоваться потом :)

Ну почему же трудно? :) Иногда в OLTP хранятся только свежие данные, а потом они перекочёвывают в архив.

Ну почему же трудно? :) Иногда в OLTP хранятся только свежие данные, а потом они перекочёвывают в архив.

Что потом происходит с данными, ставшими ненужными - дело десятое. Если они совсем ни для чего не нужны их можно просто удалять, если есть экстремально редкие юзкейсы когда эти данные могут быть востребованы - хранить их офф-лайн. Речь о том, что когда активно используемых данных много - вопрос о партиционировании встанет вне зависимости от характера приложения - будь это OLTP или наоборот Data Warehouse.

Создаётся, в документе от Уберов показаны несколько вхождений одной и той же строки в индексе после апдейта: https://eng.uber.com/mysql-migration/

Как я уже писал, тому, что там пишут «Уберы», верить не стоит. У них вообще нет ничего про HOT, кстати. Я имел в виду, что в индексе в случае HOT-update ничего не создаётся.

А что мы понимаем под «heap»?

Под heap (или «heap table») мы понимаем «обычную» таблицу — неупорядоченную «кучу» rows.

Мне казалось вы этим словом обзываете rollback сегмент, но в списке для MySQL было явно указано, что он там есть, но почему-то надо использовать IOT.

Конечно, нет, не rollback segment. А «надо» использовать IOT просто потому, что других видов «таблиц» в MySQL тупо нет!

Вполне возможно, что эта практика широко распространена только в вас лично.

То есть, архитектуру (и best pracite) в приведённых СУБД вы нагло проигнорировали, аргументов не будет?

это b-tree, побитое на блоки, которые хранятся как попало в пределах табличного пространства. Так что в общем случае смысла в IOT не прослеживается.

Да-да, идите, расскажите разработчикам ранее перечисленных СУБД, какие они идиоты. ;) Блоки-то хранятся вовсе не «как попало», а уж записи-то в них и подавно.

Я пока вижу только недостатки, хотя и не влияющие непосредственно на выгодность/невыгодность партиционирования

Не забудьте помолиться на Oracle. ;)

Как я уже писал, тому, что там пишут «Уберы», верить не стоит.

И я не вижу чтобы этот факт опровергался в ответе Постгреса.

У них вообще нет ничего про HOT, кстати. Я имел в виду, что в индексе в случае HOT-update ничего не создаётся.

Дааа? А про что же они пишут, учитывая вашу терминологию:

Под heap (или «heap table») мы понимаем «обычную» таблицу — неупорядоченную «кучу» rows.

Ну и сами подумайте, где будет хранить версии строк IOT? Ровно там же, где и не-IOT.

То есть, архитектуру (и best pracite) в приведённых СУБД вы нагло проигнорировали, аргументов не будет?

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

Да-да, идите, расскажите разработчикам ранее перечисленных СУБД, какие они идиоты. ;) Блоки-то хранятся вовсе не «как попало», а уж записи-то в них и подавно.

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

Не забудьте помолиться на Oracle. ;)

Можно подумать, что партиционирование - это фича только Oracle.

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

Ok.

Да, показалось.

ORLY? Вот кто-то пишет:

Партицирование нужно для быстрого удаления, перемещения партиций в отдельный tablespace (архивация), а не для ускорения select

На что получает:

Зачем же ты даёшь заведомо неверный ответ, который его только замедлит?

И, вообще:

На огромных массивах данных оно просто обречено давать существенное улучшение производительности
В «других СУБД» (достаю из под стола белую коробку с тщательно стёртой надписью «Oraclэ») проигрыша нет, сплошной выигрыш.

Показалось?!

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

И именно вот это (уже несколько дней подряд!) тут предлагают продемонстрировать! А вместо этого слышно только «теоретизирование».

Ну и сами подумайте, где будет хранить версии строк IOT? Ровно там же, где и не-IOT.

Мне не нужно «думать», я знаю, где они хранятся. И место хранения версий строк и использование IOT — это два (более-менее) независимых архитектурных решения!

Ссылок на документацию производителей этих БД, предлагающих некие best practices в тред не поступало.

Какие вам ещё нужны ссылки про MySQL и sqlite, если там возможности сделать не-IOT таблицу просто нет?! Может, хватит придуриваться?

А про MS SQL, например, вот (и то же самое --- в десятках других мест, just google it): https://technet.microsoft.com/en-us/library/jj835095(v=sql.110).aspx

With few exceptions, every table should have a clustered index defined on the column, or columns, that offer the following...

Clustered index = IOT, для ясности.

В чем вы видите проблему с архитектурой этих БД вы не раскрыли.

Ровно наоборот, их архитектура делает партционирование для ускорения SELECT ненужным костылём (сколько это можно повторять?).

Ровно так же хранятся, как и табличные блоки.

Неправильно. Почитайте же хоть-что про индексы, сколько можно уже...

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

Это ваша проблема, нет?

И место хранения версий строк и использование IOT — это два (более-менее) независимых архитектурных решения!

Да. И что делает постгрес непригодным для партиционирования в отсутствие IOT?

Какие вам ещё нужны ссылки про MySQL и sqlite, если там возможности сделать не-IOT таблицу просто нет?!

Это хорошо или плохо?

Clustered index = IOT, для ясности.

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

Ровно наоборот, их архитектура делает партционирование для ускорения SELECT ненужным костылём (сколько это можно повторять?).

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

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

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

Ровно так же хранятся, как и табличные блоки.

Неправильно. Почитайте же хоть-что про индексы, сколько можно уже...

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

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

Это ваша проблема, нет?

Нет, не только. Это ещё и ваша проблема: ведь это вы пока не смогли донести до меня свою мысль.