В первой части мы рассказали об основных нововведениях и изменениях в PostgreSQL 11. На этот раз чуть подробнее обсудим некоторые моменты в формате вопрос/ответ, которые поднимались на meetup.

Какой оптимальный способ передачи большого массива данных как набора входных параметров для хранимой процедуры на PL/pgSQL?

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

Внешние движки (zheap) и разработка in-memory PostgreSQL

Не для всех рабочих нагрузок подходит модель с хранением старых версий записей в самой таблице. Во всех остальных субд (версионниках) они хранятся в undo-журнале. Можно спорить о целесообразности, но суть в том, что вам нужно где-то хранить старые записи. Если у них срок жизни небольшой и к ним редко кто-то обращается, то хранить в самой таблице вредно. Внешний движок zheap PostgreSQL— это попытка EnterpriseDB сделать движок таблиц для PostgreSQL с undo log. Работает, хотя есть ещё что улучшить.

Кто работает с Ms. SQL в режиме SNAPSHOT Isolation Level, знает, что у неё есть tempdb, куда она складывает старые версии, и оснащена вполне себе взрослым vacuum для чистки tempdb. С другой стороны, сообщество просит создать in-memory таблицы в PostgreSQL. Это можно сделать достаточно легко: tmpfs, и всё. В PostgreSQL Pro даже выпустили первую пилотную версию, можно попробовать.

Чего в никогда PostgreSQL не было, так это подключаемых движков. Были подключаемые индексы, которые пользовались общим WAL. В PostgreSQL много чего можно подключить и мало что можно подменить на лету. Например, executor не отключаем, но в нем уже можно использовать кастомные ноды, которые вы сами запрограммируете. Оптимизаторы в PostgreSQL целиком подключаемы. Вы можете написать свой и использовать PostgreSQL как интерпретатор ваших запросов. Парсер SQL нельзя подключить.

Движки хотят сделать подключаемыми по трём направлениям:

• движок с undo log
• in-memory
• колоночное хранилище для OLAP-запросов

Postgres Pro ведёт переговоры с EnterpriseDB, как сделать API для подключения всего этого.

Про foreign key

Foreign key внутри PostgreSQL реализован триггерами. Вы можете написать свой триггер, который будет реализовывать какую угодно функциональность. Все возможные ограничения нужно делать в триггере. Логику в триггерах держать особо не надо, а вот всё проверять — необходимо.

Планирует Postgres Pro сделать SaaS или PaaS?

Postgres Pro планирует сделать PostgreSQL более оптимизированным для облака, в частности, реализовать динамические изменения shares buffers, уменьшить количетсво параметров, требующих перезапуска PostgreSQL. Сами строить облако не собираются.

Как нужно настроить диск, чтобы параллельное индексирование работало быстрее? Что лучше, несколько HDD или один SSD?

Лучше несколько SSD. Чем больше возможностей для распараллеливания предоставляет аппаратура, тем лучше. Если у вас один диск, мало памяти и один процессор, то распараллеливание вам не поможет. Но у SSD есть особенность: они начинают тормозить, если занято больше 80 % объёма. Поэтому не забывайте настраивать trim, иначе предел в 80 % наступит где-то на 50 %.

Управление словарём и добавление слов при полнотекстовом поиске

Если пользоваться spell или snowball, то достаточно подменить словарь стоп-слов. Беда в том, что если вы добавили стоп-слово, то индексировать смысла нет. Это можно делать неспешно. Стоп-слово будет выкидываться из запроса и никогда не искаться. А если вы убрали стоп-слово, то нигде в коллекции его нет и нужно переиндексировать. Проблема не в словаре, а в том, что вы уже им воспользовались и сохранили знания.

Также во многих случаях можно пользоваться малоизвестной функцией ts_rewrite, которая позволяет заменить кусок запроса на другой запрос. Например, когда утонула подводная лодка «Курск» все кинулись искать про неё информацию. Федор Сигаев в то время работал в рамблере, и при запросе «Курск» выдавалась информация о городе. Они оперативно сделали подмену: по этому слову выдавать информацию о подводной лодке. Но тогда стали ругаться пользователи, которых интересовал сам населённый пункт. Не знаю, сообразили они или нет, но надо было вводить «город Курск». Такие подмены и позволяет делать ts_rewrite. Кроме того, функцию можно использовать для гладкого перехода в период изменения словарей.

Конечно, изменение парсера и словарей — задачи сложные. Языки с разным алфавитом, вроде русского и английского, уживаются хорошо. Гораздо хуже сейчас индексируются, скажем, франко-английские тексты. Бывает непонятно, к какому языку относится слово, которое пишется одинаково, но в одном языке оно является стоп-словом, а в другом нет. Сейчас Postgres Pro работает над тонкой настройкой словарей, которая позволит описывать более сложные конфигурации.

Покрывающие индексы и hot update

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

Невозможность создания временных таблиц при выполнении запросов на standby

PostgreSQL хранит знания о таблицах не в системном каталоге, но есть патч, переносящий знание в системный каталог. Поэтому с этим патчем можно использовать временные таблицы. Но тогда возникает другая проблема: на stand by нет транзакций. Для работы с временной таблицей придётся использоваться дважды виртуальные transaction id, которые относятся только ко временным таблицам, а не к основным, которые приходят с мастера. И когда вы смотрите на 32-битное чиcло, то это будут два разных числа.

Также в Postgres Pro есть модуль pg_variables, который работает и на stand by. Это не совсем временная таблица, но нужную функциональность изобразить можно.

Внедрение кластерного индекса

У Postgres Pro было несколько попыток его внедрения. Сейчас вы можете ввести cluster table index, и таблица будет в том же порядке. Мучились с тем, как поддерживать таблицу в кластеризованном состоянии. Пробовали разные подходы, но неизменно вставка в такую таблицу стоила очень дорого. А это никому не интересно. Поэтому пока сделан вывод, что двигаться надо именно к Index Organized Tables.

Рекомендуемое значение autovacuum scale factor

Обычно рекомендуют задавать 1 — 5 %. Но это совершенно необязательно. Для маленьких таблиц, у которых, несмотря на изменения, в среднем остается одно и то же распределение, можно задавать большое значение. Если таблица большая и пополняется редко, но метко, с сильным изменением распределения, то придется изобретать что-то другое. Всё зависит от распределения ваших данных.

Хинты в сложных запросах

В Oracle при сложных запросах приходится периодически помогать хинтами, потому что возникают внезапные full scan. В Postgres Pro хинты есть, достаточно капризные, но завести их можно. Однако в обычном PostgreSQL хинтов нет и вряд ли они появятся. Если у вас появляются встроенные хинты, то пользователи, столкнувшись с проблемой оптимизатора, вставляют хинты, успокаиваются и не сообщают о проблеме. Развитие оптимизатора останавливается.

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

Есть такая настройка — join collapse limit. PostgreSQL сортирует JOIN для более эффективного использования, но по умолчанию лимит на сортировку равен 8. Если JOIN подряд больше 8, то система не будет их сортировать и возникнет зависимость от порядка JOIN в запросе.

Ещё есть генетический оптимизатор с различными параметрами. Можно в сессии включить различные установки и более или менее расписать, как должен выполняться запрос. Используя такой порядок, с помощью скобок можно задать выключение каких-то операций, того же sec scan. Другой вариант — вставлять в функции определенные параметры. В каком-то смысле это тоже хинты. Не сильно удобно, но хоть что-то.