Денормализованное хранение данных в PostgreSQL 9.2
Александр Коротков: Здравствуйте! Меня зовут Александр Коротков. Я представляю компанию "Интаро Софт". Мой доклад называется: "Денормализованное хранение данных в PostgreSQL 9.2". Из названия можно понять, что речь пойдет о различных новинках, о том, что нам предстоит увидеть в релизе 9.2 или в недавно появившихся расширениях (англ. extension).
Почему мы вообще используем денормализованное хранение данных? Почему мы в реляционных базах отходим от нормальных форм?
Краткий ответ на этот вопрос: потому же, почему развиваются различные новые MySQL-решения. Более развернутый ответ можно видеть на этом слайде. Если правильно использовать эти средства, будет большая производительность. Можно упростить SQL-запросы: исчезнут лишние JOIN. При хранении документов в базе получается меньше изменений в модели данных.
Хранение данных в массивах
Первое, о чем мы поговорим, будет хранение данных в массивах. Собственно, массивы в PostgreSQL есть давно, но в релизе 9.2 есть ряд новшеств, о которых я сегодня хочу рассказать.
Поговорим о преимуществах, которые дает хранение в массивах внутри базы.
Во-первых, исчезает лишний JOIN в SQL-запросе, поэтому можно извлечь необходимые данные быстрее.
Во-вторых, при использовании GIN и GiST индексов можно осуществлять быстрый поиск по значению массива.
В-третьих, SQL-запросы упрощаются.
Недостатки этого подхода
Как правило, если вы используете ORM, то готовой поддержки там нет. Ее может не быть даже в тех ORM, где поддерживаются массивы. Например, в Doctrine для PHP поддерживаются массивы, но они реализуются с точки зрения СУБД как строки. Преимущество массивов PostgreSQL использовать не удастся.
Еще один недостаток – это мультиверсионность. Если вы используете массив и хотите обновить в нем одно значение, у вас создается новая версия для кортежа целиком. Если у вас большие массивы или частые апдейты, то количество служебной информации (англ. overhead) тоже будет большим.
Вот пример того, как нам могут помочь массивы. В данном случае используется выборка из таблицы по тэгу. В этом примере у нас некая аудиозапись: есть отдельная таблица тэгов и отдельная таблица, реализующая связь типа "много ко многому".
Если посмотреть, каким будет план запроса, то здесь созданы все необходимые индексы.
В данном тестовом примере извлечение всех записей, связанных с определенным тэгом, заняло 200 миллисекунд. Если мы будем хранить тэги непосредственно в таблице записей, запрос существенно упростится.
Мы можем видеть, что время выполнения запроса тоже существенно сократилось, а его план упростился.
Коснусь новшества, которое, скорее всего, будет в PostgreSQL 9.2: будут реализованы массивы в качестве внешних ключей. Это востребованная возможность. На многих конференциях по PostgreSQL часто спрашивают: "Когда же, наконец, появится поддержка массивов в качестве внешних ключей, когда мы наконец перестанем писать для этой цели триггеры?"
Сейчас есть патч для PostgreSQL. Он уже в стадии "ready for commit". Это значит, что он успешно прошел стадию "review" и был одобрен. Есть определенная надежда на то, что эта возможность войдет в релиз 9.2.
Как этим пользоваться?
Немного о том, как этим пользоваться. Во внешних ключах добавилось ключевое слово EACH перед именем столбца. Оно как раз указывает, что это именно внешний ключ-массив, то есть каждый элемент массива ссылается на запись в другой таблице.
Добавились новые действия по обновлению или удалению той записи, на которую идет ссылка. Это EACH CASCADE. Это, соответственно, применение каскадного удаления или обновления к обновлениям массива.
EACH SET NULL – это установка значения массива в "null".
Небольшой пример
Предположим, у нас есть таблица film, которая представляет фильмы. Фильм связан с актером как "многое ко многому": один актер может играть в нескольких фильмах, и наоборот. В данном случае представлено описание такого внешнего ключа, в котором перед полем actor_ids идет ключевое слово "EACH". Действия по "ON DELETE" и "ON UPDATE" стоят с пометкой "EACH CASCADE" (происходит каскадное обновление и удаление).
Отдельная тема – это массивы и планировщик. Это связано с моим вкладом в развитие PostgreSQL.
Дело в том, что в случае массивов обычные скалярные операции сравнения (меньше/больше/равно) нетипичны для поиска. Мало кто использует эти операции для поиска в массивах. Более типичные операторы (может быть, их названия не очень привычны тем, кто не работал с PostgreSQL): поиск массивов, которые пересекаются с заданным, включают в себя заданный массив, или, наоборот, включены в заданный массив.
В отношении них планировщик оказывается "слеп", потому что для этих операций применяются константные оценки селективности. Грубо говоря, для оператора пересечения планировщик всегда считает, что мы извлечем 20 % строк – неважно, с каким массивом было пересечение. Это в некоторых случая существенно ухудшает работу.
Дальше я приведу конкретный пример. В версии 9.2 реализован сбор специфичной статистики для массивов. Она как раз "заточена" под эти операции, что дает нам более адекватные планы запросов.
Вот еще один небольшой пример. Здесь выполняется операция "JOIN" для двух таблиц. К массиву в одной из них применяется оператор пересечения с другим массивом.
Можно видеть, что до PostgreSQL 9.2 получался такой план. Он был основан на том, что мы извлечем много строк по нашей операции. Он планировал извлечь 2769 строк, а на самом деле извлек только 6. План был очень неоптимальным. Он был рассчитан на очень большое количество извлекаемых записей, а на самом деле их было мало. В итоге запрос работал порядка 50 секунд.
Посмотрим, что происходит в PostgreSQL 9.2 на том же запросе, на тех же данных – там уже другая оценка селективности. По оценке, будет извлечено 28 строк, а реально их 6. Это достаточно хорошо. Совершенно точные оценки селективности нам не нужны. Нужно было попасть в порядок, и мы попали.
Уже другой план, который состоит из Nested Index Scan. Время – всего 6 миллисекунд. От правильного плана может зависеть такая колоссальная разница. Это очень важно.
Как же это работает?
Собирается следующая статистика.
Это самые частые элементы, которые встречаются в массиве, и их частоты, а также гистограмма числа уникальных элементов. Можно посмотреть, какая статистика собрана, через системную вьюху pg_stats.
Немного про JSON
В PostgreSQL 9.2 появилась, наконец, встроенная в ядро поддержка JSON. Вообще с JSON вышла грустная история, потому что соответствующий проект был на "Google Summer of Code". Студент написал очень много кода, и еще больше тестов к нему. Но в итоге это сообщество никому не понравилось. Практически все было выброшено.
В версии 9.2 была реализована только самая базовая поддержка JSON.
Она состоит из типа JSON и функций row_to_json и array_to_json. К сожалению, хранить данные в типе JSON бесполезно, потому что извлекать из него данные нечем. Можно только собрать JSON. Остается только собирать в JSON ответы.
Благодаря встроенной поддержке можно собрать JSON-объект на стороне СУБД. Это позволит нам упростить обработку результатов. Со стороны приложения мы можем из JSON сделать соответствующий объект того языка программирования, на котором вы разрабатываете. В некоторых случаях это позволяет уменьшить либо размер ответа, либо число запросов. Можно не собирать нужный нам результат через несколько запросов, а собрать его сразу, одним запросом.
Небольшой пример. У нас есть таблица фильмов и актеров. Они связаны как "многое ко многому". В этом примере запроса мы через вложенный запрос собираем в массив связанных с фильмами актеров, а потом оборачиваем все это в JSON.
Можно посмотреть, какой получится результат. У нас есть все данные по фильму и собран в JSON массив актеров, которые в нем играют.
Помимо этого, существует еще такой модуль-расширение к PostgreSQL, как Pl/v8 (он сравнительно недавно появился). Он позволяет реализовать Javascript как процедурный язык для PostgreSQL. Он сделан на основе движка v8 от Google. Там можно производить любые манипуляции с JSON-данными.
Еще можно организовать индексирование для JSON-данных. Как это делается?
Мы с помощью языка Pl/v8 пишем функцию, которая извлекает те данные, по которым мы потом будем осуществлять поиск. Это могут быть как скалярные значения, так и массивы, о которых мы раньше говорили.
Потом строим индексное выражение для тех данных, которые извлекли (англ. expression index). Выполняем поиск с использованием этого выражения.
Вот небольшой пример.
Мы храним данные по фильмам. В данном случае это массив с именами участвующих в них актеров.
Пишем функцию. Функция тривиальная. Она просто по заданному ключу извлекает из структуры значения и в дальнейшем интерпретирует его как текстовый массив. Это важно: функция обязательно должна быть помечена как "IMMUTABLE", иначе мы не сможем построить по ней индекс. Свойство "IMMUTABLE" означает, что результат выполнения функции зависит только от входных параметров. Он не зависит от базы, и саму базу не меняет.
Аналогичная функция, которая извлекает значение из JSON в качестве вещественного числа.
Дальше мы можем построить соответствующие индексы для актеров, играющих в фильме, и для рентабельности фильма.
Можем потом сделать поисковый запрос, который использует эти выражения.
У этого запроса получится следующий план. Два Bitmap Index Scan по двум индексам. Затем выполняется объединение этих результатов.
Ограничения Pl/v8
JSON приходится хранить как текст. Нен никакой поддержки BSON и какой-то упрощенной сериализации/десериализации. Со встроенным в 9.2 типом JSON он тоже "не дружит", хотя можно надеяться, что это изменится до релиза. Каждый раз приходится заново парсить JSON в объект в хранимой функции.
Какого-то универсального индекса для JSON-документов нет. Например, для модуля расширения Hstore есть универсальный индекс. Но для JSON такого пока что нет.
Диапазонные типы
Еще одно новшество, о котором я хотел рассказать, это диапазонные типы.
Они представляют собой некий диапазон, заданный верхней и нижней границей. Поддерживаются различные виды такого рода интервалов: включающие в себя границы или не включающие, с бесконечностями и даже просто пустой интервал.
Где это можно применить?
Во-первых, это темпоральные данные. То есть данные, у которых есть некий период их актуальности. Во-вторых, это данные с точностью. Это актуально, например, в различных научных приложениях, где важно хранить не только измерение, но и его погрешность. Но чаще всего это именно темпоральные данные.
Отдельно стоит рассмотреть вопрос индексирования диапазонных типов. Здесь Btree поддерживает такие операции, как больше/меньше/равно. Для диапазонов они не очень полезны. Например, когда у нас есть диапазон, который хранит временную актуальность данных, мы, скорее всего, захотим выбрать какой-то временной срез.
С помощью операторов больше/меньше/равно мы этого сделать не сможем. Зато GiST индекс поддерживает те операторы, которые нужно: оператор пересечения с другим диапазоном и включение одного диапазона в другой.
Плюс к этому можно не хранить данные как диапазон, а просто строить индекс на соответствующем выражении. То есть в выражении собирать из двух значений диапазон и дальше уже строить на этом индекс.
Небольшой пример. Предположим, у нас есть некая таблица, которая хранит историю цен для определенных товаров. Как мы можем получить некий временной срез из этой таблицы?
Наиболее простой подход: использовать простые операции сравнения с временем актуальности. Время, с которого была актуальна данная запись, и время, по которое она была актуальна. Если мы не сделаем никаких индексов, у нас будет просто последовательное сканирование таблицы, которое работает довольно долго.
Люди, которые не знают о существовании диапазонов, или не знали до этого о модуле pg_temporal, скорее всего, построили бы двухколоночный индекс. Там были бы две колонки "Actual from" и "Actual to", которые задавали бы нам период.
Этот план получше, но все равно очень большая часть индекса была просканирована. Это очень неоптимальный способ, но людям, которые об этом не знают, часто кажется, что лучше нельзя – индекс построили и все, что нужно, в него включили. Собственно, все. На самом деле, это не так.
В данном случае можно построить GiST-индекс на выражении. Конструировать диапазон из этого периода актуальности данных. Строить соответствующий запрос, который включает в себя диапазон, на котором мы построили индекс. В этом случае у нас все будет выполняться в Index Scan 1, который работает гораздо быстрее.
Перспективы развития
Теперь поговорим о некоторых перспективах развития, которые можно выделить для таких способов денормализованного хранения данных в PostgreSQL.
Было бы неплохо сделать некий универсальный индекс для JSON, именно для хранения JSON-документов. Подобно тому, как для Hstore есть универсальный индекс, было бы здорово сделать то же самое и для JSON. Но здесь такие же проблемы, как с универсальным индексированием xml. Мы можем получить либо индекс, который не будет поддерживать то, что нам надо, либо слишком громоздкий индекс, который будет занимать очень много места.
Нужно найти какую-то "золотую середину". Может быть, можно предложить несколько вариантов.
Еще одной перспективной вещью является сбор статистики для таких расширений, как Hstore, для хранения JSON-данных и прочего. Как было показано на примере для массивов, сбор статистики для составления правильных планов очень важен. Было бы здорово, если бы для этих типов тоже был реализован сбор статистики.
Еще одна вещь, которая до сих пор не реализована: GiST-индексы для разных массивов, а не только для целочисленных. Для всевозможных массивов в PostgreSQL есть встроенные gin-индексы.
Есть также специальный модуль, который реализует разные интересные вещи именно для массивов целых чисел. В нем, в частности, есть GiST-индексы, но для других типов поддержки таких индексов нет. В принципе, это несложно сделать. Рано или поздно, я думаю, это будет реализовано.
На этом у меня все. Спасибо за внимание. Слушаю вопросы.
Вопросы и ответы
Реплика из зала: Здравствуйте, меня зовут Михаил. Я не очень понял, для чего вам нужна встроенная поддержка JSON в SQL-базе данных. Если для этого есть MySQL-решения, зачем вам индексы и все остальное "тянуть" в базу, которая уже сформировалась как решение? Вы хотите какую-то универсальность добавить. На мой взгляд, бессмысленную. Зачем?
Александр Коротков: Универсальность в этой базе во многом уже реализована. Просто идет развитие ее потенциала, потому что в PostgreSQL уже очень давно есть возможность добавлять свои типы, свои операторы, свои индексы. Например, поддержка JSON не требует каких-то особых изменений с точки зрения ядра.
Тот же Pl/v8 разработан как модуль для PostgreSQL. Он не затрагивает ядра и может дальше совершенствоваться в этом качестве. Поскольку это малозатратно, почему бы этого не сделать? Почему бы не иметь возможность смешивать преимущества реляционных баз и MySQL-решений в рамках одной СУБД?
Реплика из зала: Еще вопрос по поводу кастомных типов. Вы их тоже упомянули. Почему до сих пор (по крайней мере, в версии 9.1) нет простого решения для добавления и удаления значений кастомного типа?
Когда мы хотим сэмулировать ENUM, нет простого способа типа "remove from ENUM" (такое-то значение). Нужно что-то придумывать. Это нетривиальная задача, как в том же MySQL – встроенная поддержка ENUM. В PostgreSQL 9.1 нет этого.
Почему бы в эту сторону не двигаться, вместо того чтобы придумывать индексирование JSON?
Александр Коротков: В эту сторону тоже идет движение. Я, честно говоря, за проблемой ENUM не очень следил, но, насколько я знаю, в 9.2 есть улучшения, связанные с ENUM (именно с упрощением).
С композитными типами ситуация тоже меняется к лучшему. Насколько я знаю, кто-то работал над тем, чтобы можно было добавить или удалить поле из композитного типа, не пересобирая все значения композитного типа во всех таблицах. В этом направлении тоже работы ведутся.
Реплика из зала: Собственно, когда ждать релиза версии 9.2?
Александр Коротков: Ну, это точно неизвестно. Я думаю, в этом году он будет. Пока, к сожалению, это даже не beta. Последний коммитфест еще не завершен. Еще точно неизвестно, какие возможности войдут в релиз.
Реплика из зала: Сбор статистики по массивам реализован в момент вакуума или при старте?
Александр Коротков: В момент анализа, как и вся статистика.
Реплика из зала: Здравствуйте. Я заметил на планах запросов такую функцию – Index Only Scan.
Александр Коротков: Да.
Реплика из зала: Правильно я понимаю, что это новая функция 9.2?
Александр Коротков: Да.
Реплика из зала: Это тот самый "covering index"?
Александр Коротков: Да.
Реплика из зала: Войдет ли эта возможность в 9.2-релиз?
Александр Коротков: Однозначно войдет.
Реплика из зала: Вопрос такой. На слайде был показан поиск между from и till. Это будет именно индекс на поддержку "range tag", или это можно делать сейчас?
Александр Коротков: Не совсем понял вас.
Реплика из зала: Индекс ts_range связан с новым типом?
Александр Коротков: Да, это связанные вещи. Функция ts_range создает нам диапазон из верхней и нижней границы. Это как раз связано с диапазонами, которые в 9.2 появятся.
Но в прежних версиях это тоже можно делать с помощью модуля расширения pg_temporal. Роль диапазонов состояла в том, чтобы объединить все разные диапазоны, которые были реализованы в разных модулях к PostgreSQL. Есть модуль pg_temporal. Был еще ряд модулей, которые реализовывали, по сути, разные диапазоны. В 9.2 появилась универсальная поддержка для всех возможных диапазонов. Соответственно, и их индексирование.
Реплика из зала: Вопрос по поводу JSON. Я правильно понимаю, что сейчас делается поддержка собирания в JSON, а дальше будет работа по тому, чтобы работать со значениями изнутри JSON? JSON-объект сохраняется более-менее как строка, или это именно вложенный объект типа "ключ-значение"?
Александр Коротков: Именно как строка – да, вы правильно поняли. После довольно неудачного проекта Google Summer of Code по поддержке JSON разработчики поняли, что надо двигаться в этом направлении. Сделали хотя бы базовую поддержку в виде типа и возможности собирать значения из базы.
В дальнейшем планируется расширение возможностей. Я, правда, не знаю, в какую сторону. Возможно, этот модуль как-нибудь будет реализован скриптом.
Реплика из зала: Тип JSON есть или результат будет как строка?
Александр Коротков: Тип JSON есть, но он, по сути, является строкой, которая еще дополнительно валидируется.
Реплика из зала: Понятно. Был вопрос по поводу ENUM. Насколько я понимаю, проблема в удалении из ENUM. В принципе, можно удалять руками из pg-каталог, из таблицы ENUM-значения. Но проблема в том…
Александр Коротков: ...что есть такие значения в базе.
Реплика из зала: Да. Если значения в базе есть, вы просто с ней ничего дальше не сможете сделать. Ни "сдампить", ничего другого… Это ответ на предыдущий вопрос.
Александр Коротков: Все правильно. А в чем вопрос заключается?
Реплика из зала: Это не вопрос. Я, пользуясь случаем, поясняю, почему не сделали удаление.
Реплика из зала: Александр, может быть, у вас есть какая-то информация об улучшении поддержки XML?
Александр Коротков: Почему именно XML?
Реплика из зала: Он хранится, как и JSON, в виде строки. Улучшение в плане какого-то специального хранилища для него и индексации не ожидается?
Александр Коротков: Я могу ответить про индексацию. Был проект на Google Summer of Code 2011, связанный с индексацией. Я особо глубоко в него не вникал, но он заключается в том, что XML раскладывается по отдельным таблицам. По ним потом строятся индексы и какая-то функция, которая преобразует запрос к XML в запрос к этим таблицам.
Это сделано, по-моему, в виде модуля, который тоже от 9.2 зависит. Но более детально не могу сказать.
Реплика из зала: Понятно. Спасибо.
Реплика из зала: Александр, то, о чем вы рассказали, уже можно попробовать?
Александр Коротков: Да, можно. Из GIT делается клон от версии 9.2. Собираете ее. Чтобы попробовать внешние ключи на массивах, нужно еще "накатить" патч. Модуль v8 просто скачиваете – собираете к PostgreSQL и более "младшим" версиям PostgreSQL. По-моему, начиная с 9.1, он тоже собирается и работает. Все можно пробовать. Все открыто.
