PostgreSQL в «Тензоре» — публикации за год

Классический вопрос, с которым разработчик приходит к своему DBA или владелец бизнеса — к консультанту по PostgreSQL, почти всегда звучит одинаково: «Почему запросы выполняются на базе так долго?»
…
Но, как сказано в той же документации, «Понимание плана — это искусство, и чтобы овладеть им, нужен определённый опыт, …» Но можно обойтись и без него, если воспользоваться подходящим инструментом!

В докладе представлены некоторые подходы, которые позволяют следить за производительностью SQL-запросов, когда их миллионы в сутки, а контролируемых серверов PostgreSQL — сотни.

Какие технические решения позволяют нам эффективно обрабатывать такой объем информации, и как это облегчает жизнь обычного разработчика.

Многие ситуации, которые делают запрос медленным и «прожорливым» по ресурсам, типичны и могут быть распознаны по структуре и данным плана.

В этом случае каждому отдельному разработчику не придется искать вариант оптимизации самостоятельно, опираясь исключительно на свой опыт — мы можем ему подсказать, что тут происходит, в чем может быть причина, и как можно подойти к решению. Что мы и сделали.

• Поддержка плана вместе с запросом, в том числе в JSON/YAML-форматах
• Расширенная визуализация Planning/Execution Time и I/O Timing.
• Новые фичи из PostgreSQL 13: Planning buffers, Incremental Sort
• Улучшения UI/UX: скриншоттинг, рекомендации на узлах плана, удаление из архива.

Сегодня мы научимся определять больные места навскидку в больших и сложных планах, лишь мельком взглянув на них вооруженным глазом. В этом нам помогут различные варианты визуализации: сокращенный текстовый вид, круговая диаграмма, плитка, диаграмма выполнения.

В наш век закончившейся «гонки мегагерцев» и победивших многоядерных и многопроцессорных систем такое поведение является непозволительной роскошью и расточительностью. Поэтому, начиная с версии PostgreSQL 9.6, при отработке запроса часть операций может выполняться несколькими процессами одновременно.

Тут надо вспомнить, что CTE Scan является аналогом Seq Scan — то есть никакой индексации, а только полный перебор.
…
В данном случае нам еще сильно повезло, что для соединения был выбран Hash Join, а не Nested Loop, поскольку тогда мы получили бы не один-единственный проход CTE Scan, а 10K!

• оптимизируем JOIN + LIMIT 1
• BitmapOr vs UNION
• «прячем под CASE» сложные условия

В столбце ratio как раз показывается отношение «в разах» между планировавшимся на основании статистики и фактически прочитанным количеством записей. Чем больше это значение — тем хуже статистика отражает реальное положение дел в вашей таблице.

Приводит это к выбору неэффективного плана и, как следствие, дичайшей нагрузке на базу. Чтобы ее оперативно убрать, достаточно все-таки прислушаться к рекомендациям мануала и пройти ANALYZE по основным таблицам.

Иногда возникает задача «склеить» внутри SQL-запроса из переданных в качестве параметров линейных массивов целостную выборку с теми же данными «по столбцам».

• WITH ORDINALITY
• Multi-argument UNNEST

Периодически у разработчика возникает необходимость передать в запрос набор параметров или даже целую выборку «на вход». Иногда попадаются очень странные решения этой задачи.

• сериализованное представление массива/матрицы + unnest
• JSON + json_populate_recordset/json_to_recordset
• TEMPORARY TABLE
• переменные сессии

Как стоит поступить (а как точно не надо), если в «многомиллионной» активно используемой таблице PostgreSQL нужно обновить большое количество записей — проинициализировать значение нового поля или скорректировать ошибки в существующих записях? А при этом сохранить свое время и не потерять деньги компании из-за простоя.

Если писать SQL-запросы без анализа алгоритма, который они должны реализовать, ни к чему хорошему с точки зрения производительности это обычно не приводит.

Такие запросы любят «кушать» процессорное время и активно почитывать данные практически на ровном месте. Причем, это вовсе не обязательно какие-то сложные запросы, наоборот — чем проще он написан, тем больше шансов получить проблемы. А уж если в дело вступает оператор JOIN…

Итоговые выводы:

• если надо сделать JOIN с многократно повторяющимися записями — лучше использовать «ословаривание» таблицы
• если ваш словарь ожидаемо маленький и читать вы из него будете немного — можно использовать json[b]
• во всех остальных случаях hstore + array_agg(i::text) будет эффективнее

Например, на таблице, в которой вам надо что-то поправить, висит злобный триггер ON UPDATE, переносящий все изменения в какие-нибудь агрегаты. А вам надо все пообновлять (новое поле проинициализировать, например) так аккуратно, чтобы эти агрегаты не затронулись.

Что вообще обычно подразумевает пользователь, когда говорит про «быстрый» поиск по названию? Почти никогда это не оказывается «честный» поиск по подстроке типа ... LIKE '%роза%' — ведь тогда в результат попадают не только 'Розалия' и 'Магазин Роза', но и 'Гроза' и даже 'Дом Деда Мороза'.

Пользователь же подразумевает на бытовом уровне, что вы ему обеспечите поиск по началу слова в названии и покажете более релевантным то, что начинается на введенное. И сделаете это практически мгновенно — при подстрочном вводе.

SQL — это не C++, и не JavaScript. Поэтому вычисление логических выражений происходит иначе.

• ускоряем триггер за счет выноса проверки из функции в WHEN.
• оптимизируем OR/AND-цепочку с помощью CASE
• упрощаем написание сложных условий

Все будет очень просто, на уровне Капитана Очевидность — делаем просмотр реестра событий с сортировкой по времени.

• плохо: считать сегменты на бизнес-логике
• плохо: использовать LIMIT + OFFSET
• хорошо: использовать «курсоры», но делать это аккуратно

В сложных ERP-системах многие сущности имеют иерархическую природу, когда однородные объекты выстраиваются в дерево отношений «предок — потомок» — это и организационная структура предприятия (все эти филиалы, отделы и рабочие группы), и каталог товаров, и участки работ, и география точек продаж, ...

Типизация данных в PostgreSQL, при всей своей логичности, действительно преподносит порой очень странные сюрпризы. В этой статье мы постараемся прояснить некоторые их причуды, разобраться в причине их странного поведения и понять, как не столкнуться с проблемами в повседневной практике.

Я решил бегло пробежаться по коду доступных мне SQL-запросов, чтобы посмотреть, насколько часто в них используется тип REAL. Достаточно часто используется, как оказалось, и не всегда разработчики понимают опасности, стоящие за ним. И это несмотря на то, что в Интернете и на Хабре достаточно много хороших статей про особенности хранения вещественных чисел в машинной памяти и о работе с ними. Поэтому в этой статье я постараюсь применить такие особенности к PostgreSQL, и попробую «на пальцах» рассмотреть связанные с ними неприятности, чтобы разработчикам SQL-запросов было легче избежать их.

Сегодня на предельно простых примерах посмотрим, к чему это может приводить в контексте использования GROUP/DISTINCT и LIMIT вместе с ними.

Вот если вы написали в запросе «сначала соедини эти таблички, а потом выкинь все дубли, должен остаться только один экземпляр по каждому ключу» — именно так и будет работать, даже если соединение вовсе не было нужно.

• таблица счетчиков
• объект SEQUENCE
• псевдотип serial
• GENERATED-столбцы
• генерируемый UUID
• скрытые системные поля: tableoid/ctid/oid
• «честное» время clock_timestamp

Рекурсия — очень мощный и удобный механизм, если над связанными данными делаются одни и те же действия «вглубь». Но неконтролируемая рекурсия — зло, которое может приводить или к бесконечному выполнению процесса, или (что случается чаще) к «выжиранию» всей доступной памяти.

СУБД в этом отношении работают по тем же принципам — "сказали копать, я и копаю". Ваш запрос может не только затормозить соседние процессы, постоянно занимая ресурсы процессора, но и «уронить» всю базу целиком, «съев» всю доступную память. Поэтому защита от бесконечной рекурсии — обязанность самого разработчика.

«Просто так» результат SQL-запроса возвращает записи в том порядке, который наиболее удобен серверу СУБД. Но человек гораздо лучше воспринимает хоть как-то упорядоченные данные — это помогает быстро сравнивать соответствие различных датасетов.

Поэтому со временем у разработчика может выработаться рефлекс «Дай-ка я на всякий случай это вот отсортирую!»

• нехватка work_mem
• сортировка уже отсортированного
• вложенная отладочная сортировка
• Index Scan вместо сортировки
• UNION ALL вместо сортировки
• сортировки для оконных функций

Давайте посмотрим, какие именно прикладные задачи и как можно решить с помощью PostgreSQL и сократить время анализа данных с нескольких секунд на бизнес-логике до десятков миллисекунд, умея эффективно применять следующие алгоритмы непосредственно внутри SQL-запроса:

• поиск отрезков, пересекающих точку/интервал
• слияние отрезков по максимальному перекрытию
• подсчет количества отрезков в каждой точке

Дедукция и индукция помогут нам вычислить, что же все-таки хотел получить от СУБД разработчик, и почему это получилось не слишком оптимально. Итак, сегодня нас ждут:

• Дело о непростом пути вверх
  Разберем в live-видео на реальном примере некоторые из способов улучшения производительности иерархического запроса.
• Дело о худеющем запросе
  Увидим, как можно запрос упростить и ускорить в несколько раз, пошагово применяя стандартные методики.
• Дело о развесистой клюкве
  Восстановим структуру БД на основании единственного запроса с 11 JOIN и предложим альтернативный вариант решения на ней той же задачи.

Немного SQL-магии: математика, рекурсия, псевдографика.

Иногда при анализе данных возникает задача выделения «цепочек» в выборке — то есть упорядоченных последовательностей записей, для каждой из которых выполняется некоторое условие.

Традиционные решения предусматривают разные варианты «self join», когда выборка соединяется с собой же, либо использование некоторых фактов «за пределами данных» — например, что записи должны иметь строго определенный шаг (N+1, «за каждый день», ...).

Первый вариант зачастую приводит к квадратичной сложности алгоритма от количества записей, что недопустимо на больших выборках, а второй может легко «развалиться», если каких-то отсчетов в исходных данных вдруг не окажется.

Но эту задачу нам помогут эффективно решить оконные функции в PostgreSQL.

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

В статье рассмотрим реализацию на PostgreSQL «наивного» варианта решения такой задачи, «поумнее» и совсем сложный алгоритм «цикла» на SQL с условием выхода от найденных данных, который может быть полезен как для общего развития, так и для применения в других похожих случаях.

Рассмотрим некоторые способы, с помощью которых можно вычислить агрегаты в PostgreSQL или ускорить выполнение SQL-запроса.

• совместные агрегаты
• вложенные запросы
• FILTER-агрегаты
• агрегаты от условия
• агрегация в массив
• DISTINCT + OVER
• сложный агрегат с помощью рекурсии

В этой статье рассмотрим, как все это можно экономично расположить в БД, и как максимально эффективно собрать по этим данным отчет с помощью оконных функций и GROUPING SETS.

Чтобы сэкономить производительность дисковой подсистемы нашей базы и занимаемый базой объем, постараемся как можно больше данных представить в виде NULL — их хранение практически «бесплатно», поскольку занимает лишь бит в заголовке записи.

Пусть у нас есть реестр документов, с которым работают операторы или бухгалтеры. Традиционно, при подобном отображении используется или прямая (новые снизу) или обратная (новые сверху) сортировка по дате и порядковому идентификатору, назначаемому при создании документа — ORDER BY dt, id или ORDER BY dt DESC, id DESC.

Но что если пользователю зачем-то захотелось «нетипичного» — например, отсортировать одно поле «так», а другое «этак» — ORDER BY dt, id DESC? Но второй индекс мы создавать не хотим — ведь это замедление вставки и лишний объем в базе.

Можно ли решить эту задачу, эффективно используя только индекс (dt, id)?

… чтобы для вывода упорядочить элементы дерева в соответствии с иерархией, уж точно придется воспользоваться рекурсией! Или нет? Давайте разберемся, а заодно решим на SQL пару комбинаторных задач.

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

Например, пишутся в PostgreSQL COPY-потоком значения хронологической метрики, а потом внезапный сбой, и часть полностью идентичных данных приходит повторно.

Как избавить базу от ненужных клонов?

VACUUM может «зачистить» из таблицы в PostgreSQL только то, что никто не может увидеть — то есть нет ни одного активного запроса, стартовавшего раньше, чем эти записи были изменены.

А если такой неприятный тип (продолжительная OLAP-нагрузка на OLTP-базе) все же есть? Как почистить активно меняющуюся таблицу в окружении длинных запросов и не наступить на грабли?

Как можно перенести в другую PostgreSQL-базу последнее назначавшееся значение «автоинкремент»-поля типа serial, если в таблице могли быть какие-то удаления, и «просто подставить max(pk)» уже не подходит?

Регулярно сталкиваюсь с ситуацией, когда многие разработчики искренне полагают, что индекс в PostgreSQL — это такой швейцарский нож, который универсально помогает с любой проблемой производительности запроса. Достаточно добавить какой-нибудь новый индекс на таблицу или включить поле куда-нибудь в уже существующий, а дальше (магия-магия!) все запросы будут эффективно таким индексом пользоваться.

Во-первых, конечно, или не будут, или не эффективно, или не все. Во-вторых, лишние индексы только добавят проблем с производительностью при записи.

При сложной обработке больших наборов данных (разные ETL-процессы: импорты, конвертации и синхронизации с внешним источником) часто возникает необходимость временно «запомнить», и сразу быстро обработать что-то объемное.

Чтобы справиться с ними в PostgreSQL (да и не только в нем), можно использовать некоторые возможности для оптимизаций, которые позволят обработать все быстрее и с меньшим расходом ресурсов.

• состояние соединений
• блокировки
• transactions per second (TPS)
• количество операций над записями
• использование кэша данных
• самый длительный запрос/транзакция

Шансов поймать блокировки «в моменте» крайне мало, да и длиться они могут всего по несколько секунд, но ухудшая при этом плановое время выполнения запроса в десятки раз. А хочется-то не сидеть и ловить происходящее в онлайн-режиме, а в спокойной обстановке разобраться постфактум, кого из разработчиков покарать в чем именно была проблема — кто, с кем и из-за какого ресурса базы вступил в конфликт.

Но как? Ведь, в отличие от запроса с его планом, который позволяет детально понять, на что пошли ресурсы, и сколько времени это заняло, подобных наглядных следов блокировка не оставляет после себя…

Разве что короткую запись в логе:

process ... still waiting for ...

А давайте попробуем зацепиться именно за нее!

Если мы не хотим потом хвататься за голову, то возникающие в логах PostgreSQL ошибки недостаточно просто «считать поштучно» — их надо аккуратно классифицировать. Но для этого нам придется решить нетривиальную задачу индексированного поиска регулярного выражения, наиболее подходящего для строки.

Научимся трактовать собранные блокировки и узнавать, кто именно может скрываться за конкретной матрицей конфликтов, и почему результат выглядит именно так.

Как можно перевести бизнес-требования в конкретные структуры данных на примере проектирования «с нуля» базы для мессенджера.

Мы удачно спроектировали структуру нашей PostgreSQL-базы для хранения переписки, прошел год, пользователи активно ее наполняют, вот в ней уже миллионы записей, и… что-то все начало подтормаживать.

Дело в том, что с ростом объема таблицы растет и «глубина» индексов — хоть и логарифмически. Но со временем это заставляет сервер для выполнения тех же задач чтения/записи обрабатывать в разы больше страниц данных, чем в начале.

Вот тут на помощь и приходит секционирование. Мы рассмотрим не конкретные скрипты для реализации секционирования «в железе», а сам подход — что и как стоит «порезать на дольки», и к чему такое желание приводит.

Если ваши микросервисы уже используют общую базу PostgreSQL для хранения данных, или ей пользуются несколько экземпляров одного сервиса на разных серверах, можно относительно «дешево» получить возможность обмена сообщениями (PubSub) между ними без интеграции в архитектуру Redis, RabbitMQ-кластера или встройки в код приложения другой MQ-системы.

Для этого мы не будем писать сообщения в таблицы БД, поскольку это вызывает слишком большие накладные расходы сначала на запись передаваемого, а потом еще и на зачистку от уже прочитанного.

Передавать и получать данные мы станем с помощью механизма NOTIFY/LISTEN, а модельную реализацию соберем для Node.js.

В PostgreSQL существует очень удобный механизм рекомендательных блокировок, они же — advisory locks. Мы в «Тензоре» используем их во многих местах системы, но мало кто детально понимает, как конкретно они работают, и какие проблемы можно получить при неправильном обращении.

• Секционирование
• Эволюция и рефакторинг БД
• «Размазываем» пиковую нагрузку
• Кэшируем, что можно

«Терабайт-в-сутки» только звучит страшно. Если вы все делаете правильно, то это всего лишь 2^40 байт / 86400 секунд = ~12.5MB/s, что держали даже настольные IDE-винты. :)

А если серьезно, то даже при десятикратном «перекосе» нагрузки в течение суток, вы спокойно можете уложиться в возможности современных SSD.

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

Речь пойдет про настройки TOAST и выравнивание данных. «В среднем» эти способы позволят сэкономить не слишком много ресурсов, зато — вообще без модификации кода приложения.

Как суровую прагматику требований бизнеса перенести на разработку высоконагруженных сервисов, как бороться с конкурентным доступом к данным, как это все аккуратно обходить и при этом не «отстрелить себе ногу».

Автоматическое определение клиента и его региона по входящему телефонному звонку стало неотъемлемой частью любой развитой HelpDesk или CRM-системы. Только надо уметь делать это быстро — тогда появляется масса возможностей.

• Как балансировать «писателей» и управлять соединениями на бизнес-логике?
• Как настроить СУБД и ОС?
• Как избавиться от блокировок?