Главная » Хабрахабр » По следам meetup «Новые возможности PostgreSQL 11» (часть 2)

По следам meetup «Новые возможности PostgreSQL 11» (часть 2)

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

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

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

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

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

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

Были подключаемые индексы, которые пользовались общим WAL. Чего в никогда PostgreSQL не было, так это подключаемых движков. Например, executor не отключаем, но в нем уже можно использовать кастомные ноды, которые вы сами запрограммируете. В PostgreSQL много чего можно подключить и мало что можно подменить на лету. Вы можете написать свой и использовать 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 очень сильно промахивается.

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

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


Оставить комментарий

Ваш email нигде не будет показан
Обязательные для заполнения поля помечены *

*

x

Ещё Hi-Tech Интересное!

[Перевод] Введение в ptrace или инъекция кода в sshd ради веселья

Конечно, это несколько искусственная задача, так как есть множество других, более эффективных, способов достичь желаемого (и с гораздо меньшей вероятностью получить SEGV), однако, мне показалось клёвым сделать именно так. Цель, которой я задался, была весьма проста: узнать введённый в sshd ...

Дайджест свежих материалов из мира фронтенда за последнюю неделю №339 (12 — 18 ноября 2018)

Предлагаем вашему вниманию подборку с ссылками на новые материалы из области фронтенда и около него.     Медиа    |    Веб-разработка    |    CSS    |    Javascript    |    Браузеры    |    Занимательное Медиа • Подкаст «Frontend Weekend» #79 – Олег Поляков об основании CodeDojo и о том, как это стало основным местом работы• Подкаст «Пятиминутка React» ...