Комментарии 18
От имени тысяч пользователей, прочитавших этот пост и особенно от тех из них, кто добавил в избранное: большое спасибо, Егор!
Ждём продолжения!
Разрешите уточнить.
Отметим, что TOAST работает только для таблиц, но не для индексов. Это накладывает ограничение на размер индексируемых ключей.
1. Означает ли это, что b-tree индексы на NUMERIC типы полей работать не будут? Например, есть колонка NUMERIC(30, 10) и необходимо, например сортировать по этой колонке или найти все значения, которые больше/меньше некого порогового значения. Будет ли PostgreSQL использовать индекс в этом случае? Или же PostgreSQL даже не позволит такой индекс создать?
Предположу, что здесь все сильно зависит от заданного размера NUMERIC (количество знаков)
2. Следующий вопрос, вытекающий из первого — пусть поле NUMERIC очень большое или присутствует text поле. Для text видимо нужно использовать индекс типа GIN, а для NUMERIC?
3. Насколько сильно TOAST-таблицы влияют на производительность PostgreSQL? Кейс из реального проекта — есть колонка NUMERIC (рейтинг поста) и text — текст поста. Можно ли провести аналогию и сказать, что наличие TOAST добавляет +1 JOIN к запросу на поиск постов?
4. UPD: Как быть с JSONB типом данных? Создается ли для него TOAST или присутствуют ограничения, чтобы данные помещались на одну страницу?
Буду рад, если мои вопросы помогут дополнить саму статью, потому что очень интересен вопрос производительности при наличии TOAST. Спасибо
1. Btree-индексы работать будут — до тех пор, пока значения не слишком длинные. При существующем индексе слишком длинные значения просто не получится вставить (ну или потом не получится создать индекс). Будет ошибка типа такой:
ERROR: index row size ... exceeds maximum 2712 for index "..."
HINT: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.
2. Какой индекс использовать в случае длинных значений — зависит от того, что нужно от индекса. HINT в сообщении об ошибке как раз отвечает на этот вопрос: надо выкрутиться так, чтобы индексировалось не все значение, а только часть.
Для numeric… Ну как вариант: можно создать частичный btree-индекс (WHERE n < что-то-большое-но-не-слишком). Тогда длинные значения просто не будут индексироваться.
3. Само по себе наличие TOAST на производительность не влияет, пока значения не становятся длинными. Например, если мы храним в столбце text строки длиной не более 100, то text так же эффективен, как и varchar(100). (Про это Депеш хорошо писал.)
Но если есть длинные значения, то да, можно считать, что для них добавляется соединение. Это, правда, почти ничего не говорит о производительности — надо замерять в конкретном случае.
4. JSONB конечно же умеет тоститься, большие json-ы хранить можно. Но для поиска по json имеет смысл создавать GIN-индекс, а соответствующие классы операторов а) индексируют не сам json, а отдельные ключи и значения из него, и б) умеют за кадром превращать длинные значения в хеш-коды.
1. Btree-индексы работать будут — до тех пор, пока значения не слишком длинные. При существующем индексе слишком длинные значения просто не получится вставить (ну или потом не получится создать индекс).
То есть получаем очень интересные грабли — создаем большой NUMERIC и вешаем на него b-tree индекс (как есть, не частичный). На text и JSONB, наверное, такой индекс создавать никогда не будут, но на NUMERIC думаю можно так ошибиться. И все работает до некого момента, когда по бизнес-причинам рейтинги не начнут превышать указанное максимальное значение. И тогда внезапно перестанет работать вставка новых данных (или обновление существующих данных).
Поясните, кстати, тогда, пожалуйста, для каких ситуаций справедливо это утверждение.
Отметим, что TOAST работает только для таблиц, но не для индексов. Это накладывает ограничение на размер индексируемых ключей.
Получается, его надо рассматривать не как «индексируем колонку, для которой существует TOAST», а так:
«Индексируем любую колонку достаточно большого размера. В какой-то момент из-за размера упираемся в ограничение Values larger than 1/3 of a buffer page cannot be indexed. Это ограничение обходится для данных с помощью TOAST технологии, но эта технология неприменима к индексам типа b-tree»
Кажется, прописав условие для себя, я его понял. Поправьте, пожалуйста, если что-то не так.
При этом, насколько я понял, обсуждаемое ограничение распространяется только на индексы типа b-tree? У индексов типа GIN, GIST подобных ограничений нет?
Почему меня так заинтересовал этот вопрос про ограничение — получается, что можно ни с того ни с сего «упереться в ограничение размера индекса», если не знать обсуждаемые нюансы и не продумывать структуру индексов для таблиц с большим размером полей. И получить достаточно серьезные проблемы на продакшене, потому что вставка или обновление данных будут заблокированы. А удаление индекса существенно может замедлить существующие запросы. Придется с даунтаймом сервиса срочно перестраивать индексы или создавать новые. Если я все правильно понял.
Так что не переживайте, с numeric-ом это чисто теоретические грабли.
Насчет TOAST для таблиц и индексов попробую пояснить.
Любая строка, будь то в таблице или в индексе, не может быть больше размера страницы (обычно ограничение еще более жесткое). С табличными строками база умеет разбираться самостоятельно, с помощью TOAST. С индексными строками — не умеет, для индексов нет автоматического TOAST-а.
Это означает, что если разработчик индекса (или класса операторов) не предпринял специальных мер, то попытка вставить в индекс длинное значение приведет к ошибке. Что мы и видим на примере btree.
Но вообще никто не мешает разработчику эти меры предпринять. Возьмем GIN: там классы операторов для работы с JSONB автоматически заменяют длинные значения на (короткие) хеши. Плюс в том, что можно проиндексировать значения любой длины. Минус в том, что поиск по хешу всегда допускает ложные срабатывания, из-за чего найденное приходится перепроверять, а это уменьшает эффективность.
Для других индексов и для других классов операторов это может работать иначе. То есть средства борьбы с длинными значениями могут быть разными и на разных уровнях.
И да, прежде чем индексировать потенциально длинные столбцы, неплохо познакомиться с этими нюансами.
Запоздало подтверждаю, что статья и серия в целом прекрасны.
Но есть один вопрос. Возможно я объяснения не заметил в других статьях. Почему "строки"? Это ведь "tuples", то есть кортежи? Тем более что вы тут (и постгрес в исходных кодах) используете термин "отношения".
Спасибо.
По-английски tuples и row versions (в контексте Постгреса, конечно) используются как синонимы. Соответственно по-русски я предпочитаю версия строки (или просто строка, если речь не о версионности), потому что такой термин не требует специального объяснения. В то время как кортеж — просто неудачное (на мой взгляд) заимствование из реляционной теории.
Использование слова отношение я тоже не понимаю и употребляю его только от безысходности. Ну просто нет другого слова, что ж поделать.
Я порылся в старых словарях, и выходит, что отношение и кортеж — термины реляционной алгебры. Кроме того, кортеж упоминается еще в советских учебниках по комбинаторике как упорядоченный набор элементов. Словом, это вполне себе устоявшийся термин.
Ряд (строка в вашем варианте) и столбец — термины уже из SQL. Лично мне строка слегка режет глаз, т.к. слово это обычно не употребляется не для описания таблиц, а именно строк, например, текста. Насколько понимаю, table/row/column в SQL попали для того, чтобы потенциальным пользователям-непрограммистам было легче освоить язык (см. "Early History of SQL" самого Чемберлейна).
Впрочем, это так, жалобы на ровном месте, про переводы же терминов можно спорить вечно. У вас замечательные статьи! :-)
Я порылся в старых словарях, и выходит, что отношение и кортеж — термины реляционной алгебры. Кроме того, кортеж упоминается еще в советских учебниках по комбинаторике как упорядоченный набор элементов. Словом, это вполне себе устоявшийся термин.
Ряд (строка в вашем варианте) и столбец — термины уже из SQL. Лично мне строка слегка режет глаз, т.к. слово это обычно не употребляется не для описания таблиц, а именно строк, например, текста. Насколько понимаю, table/row/column в SQL попали для того, чтобы потенциальным пользователям-непрограммистам было легче освоить язык (см. "Early History of SQL" самого Чемберлейна).
Полностью со всем согласен! Но дальше мы из одних и тех же посылок делаем разные выводы (:
Мне кажется, что если говорить о реляционной теории (где множества, нет неопределенных значений и вообще все хорошо), то надо использовать отношение/кортеж, как там и принято. А если говорить о практике, например о SQL (где мультимножества, null и т. д.) или тем более о физическом представлении (как в этой статье), то надо использовать таблица/строка. Ряд — ну не знаю, кажется, в последнее время этот термин вышел из употребления.
Кстати, ранней историей SQL я в свое (тогда еще оракловое) время интересовался, и написал по горячим следам несколько постов в блоге. Но снаружи не публиковал нигде, больше для себя, чтобы разобраться.
О, да у вас там целое исследование :-) Очень интересно.
Меня та история всегда улыбала и заставляла задуматься о том, как иной раз все спонтанно складывается в жизни. Что бы там не говорил Кодд, и как бы IBM не старалась, выиграла совсем другая компания и совсем не реляционная алгебра. С IBM это вообще до смешного много раз случалось.
Я в том смысле, что SQL, конечно, вдохновляется теорией, но достаточно неаккуратно ее воспроизводит. Но поезд уже ушел, всем все равно, а других такого рода языков на горизонтах не видно.
Ну да, что выросло — то выросло. Другие языки есть (например), но чтобы сейчас подвинуть глыбу SQL, этого явно недостаточно.
MVCC-2. Слои, файлы, страницы