- PVSM.RU - https://www.pvsm.ru -
В первой части [1] мы рассказали об основных нововведениях и изменениях в PostgreSQL 11. На этот раз чуть подробнее обсудим некоторые моменты в формате вопрос/ответ, которые поднимались на meetup.
Наиболее удобный способ — создать временную таблицу, туда заводить копии данных, и потом уже использовать в процедуре.
Не для всех рабочих нагрузок подходит модель с хранением старых версий записей в самой таблице. Во всех остальных субд (версионниках) они хранятся в 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 нельзя подключить.
Движки хотят сделать подключаемыми по трём направлениям:
Postgres Pro ведёт переговоры с EnterpriseDB, как сделать API для подключения всего этого.
Foreign key внутри PostgreSQL реализован триггерами. Вы можете написать свой триггер, который будет реализовывать какую угодно функциональность. Все возможные ограничения нужно делать в триггере. Логику в триггерах держать особо не надо, а вот всё проверять — необходимо.
Postgres Pro планирует сделать PostgreSQL более оптимизированным для облака, в частности, реализовать динамические изменения shares buffers, уменьшить количетсво параметров, требующих перезапуска PostgreSQL. Сами строить облако не собираются.
Лучше несколько SSD. Чем больше возможностей для распараллеливания предоставляет аппаратура, тем лучше. Если у вас один диск, мало памяти и один процессор, то распараллеливание вам не поможет. Но у SSD есть особенность: они начинают тормозить, если занято больше 80 % объёма. Поэтому не забывайте настраивать trim, иначе предел в 80 % наступит где-то на 50 %.
Если пользоваться spell или snowball, то достаточно подменить словарь стоп-слов. Беда в том, что если вы добавили стоп-слово, то индексировать смысла нет. Это можно делать неспешно. Стоп-слово будет выкидываться из запроса и никогда не искаться. А если вы убрали стоп-слово, то нигде в коллекции его нет и нужно переиндексировать. Проблема не в словаре, а в том, что вы уже им воспользовались и сохранили знания.
Также во многих случаях можно пользоваться малоизвестной функцией ts_rewrite, которая позволяет заменить кусок запроса на другой запрос. Например, когда утонула подводная лодка «Курск» все кинулись искать про неё информацию. Федор Сигаев в то время работал в рамблере, и при запросе «Курск» выдавалась информация о городе. Они оперативно сделали подмену: по этому слову выдавать информацию о подводной лодке. Но тогда стали ругаться пользователи, которых интересовал сам населённый пункт. Не знаю, сообразили они или нет, но надо было вводить «город Курск». Такие подмены и позволяет делать ts_rewrite. Кроме того, функцию можно использовать для гладкого перехода в период изменения словарей.
Конечно, изменение парсера и словарей — задачи сложные. Языки с разным алфавитом, вроде русского и английского, уживаются хорошо. Гораздо хуже сейчас индексируются, скажем, франко-английские тексты. Бывает непонятно, к какому языку относится слово, которое пишется одинаково, но в одном языке оно является стоп-словом, а в другом нет. Сейчас Postgres Pro работает над тонкой настройкой словарей, которая позволит описывать более сложные конфигурации.
Вполне себе дружат. Правда, если в покрывающем индексе обновляется хоть одно поле, то индекс поведёт себя как обычный, будет заменено всё.
PostgreSQL хранит знания о таблицах не в системном каталоге, но есть патч, переносящий знание в системный каталог. Поэтому с этим патчем можно использовать временные таблицы. Но тогда возникает другая проблема: на stand by нет транзакций. Для работы с временной таблицей придётся использоваться дважды виртуальные transaction id, которые относятся только ко временным таблицам, а не к основным, которые приходят с мастера. И когда вы смотрите на 32-битное чиcло, то это будут два разных числа.
Также в Postgres Pro есть модуль pg_variables, который работает и на stand by. Это не совсем временная таблица, но нужную функциональность изобразить можно.
У Postgres Pro было несколько попыток его внедрения. Сейчас вы можете ввести cluster table index, и таблица будет в том же порядке. Мучились с тем, как поддерживать таблицу в кластеризованном состоянии. Пробовали разные подходы, но неизменно вставка в такую таблицу стоила очень дорого. А это никому не интересно. Поэтому пока сделан вывод, что двигаться надо именно к Index Organized Tables.
Обычно рекомендуют задавать 1 — 5 %. Но это совершенно необязательно. Для маленьких таблиц, у которых, несмотря на изменения, в среднем остается одно и то же распределение, можно задавать большое значение. Если таблица большая и пополняется редко, но метко, с сильным изменением распределения, то придется изобретать что-то другое. Всё зависит от распределения ваших данных.
В Oracle при сложных запросах приходится периодически помогать хинтами, потому что возникают внезапные full scan. В Postgres Pro хинты есть, достаточно капризные, но завести их можно. Однако в обычном PostgreSQL хинтов нет и вряд ли они появятся. Если у вас появляются встроенные хинты, то пользователи, столкнувшись с проблемой оптимизатора, вставляют хинты, успокаиваются и не сообщают о проблеме. Развитие оптимизатора останавливается.
К слову, у оптимизатора PostgreSQL есть проблема. Когда он оценивает выборку из таблицы, даже для более или менее разумного количества, то угадывает с некоторой ошибкой. Потом он начинает соединять, полученный результат соединяется ещё с чем-то, ошибка накапливается, и на третьем-четвертом уровне PostgreSQL очень сильно промахивается.
Есть такая настройка — join collapse limit. PostgreSQL сортирует JOIN для более эффективного использования, но по умолчанию лимит на сортировку равен 8. Если JOIN подряд больше 8, то система не будет их сортировать и возникнет зависимость от порядка JOIN в запросе.
Ещё есть генетический оптимизатор с различными параметрами. Можно в сессии включить различные установки и более или менее расписать, как должен выполняться запрос. Используя такой порядок, с помощью скобок можно задать выключение каких-то операций, того же sec scan. Другой вариант — вставлять в функции определенные параметры. В каком-то смысле это тоже хинты. Не сильно удобно, но хоть что-то.
Автор: tspavel
Источник [2]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/postgresql/285136
Ссылки в тексте:
[1] первой части: https://habr.com/company/raiffeisenbank/blog/414031/
[2] Источник: https://habr.com/post/416187/?utm_campaign=416187
Нажмите здесь для печати.