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

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

Сегодня поговорим о самых простых, но важных, возможностях команды SELECT, наиболее часто используемой при работе с базами данных - формировании выборок (VALUES), их ограничении (LIMIT/OFFSET/FETCH), фильтрации (WHERE/HAVING), сортировке (ORDER BY), уникализации (DISTINCT) и группировке (GROUP BY).

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

Этим постом я запускаю публикацию расширенных транскриптов лекционного курса "PostgreSQL для начинающих", подготовленного мной в рамках "Школы backend-разработчика" в "Тензоре".

В программе: рассказ об основах SQL, возможностях простых и сложных SELECT, анализ производительности запросов, разбор [не]эффективного применения индексов и особенностей работы транзакций и блокировок в этой СУБД.

Курс не претендует на лавры "войти в айти", поэтому подразумевает наличие у слушателя опыта программирования или работы с другими СУБД, и, главное, желания самостоятельно изучать тему работы с PostgreSQL глубже.

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

Периодически сталкиваюсь с однотипными задачами вида "показать TOP-N позиций на каждом из вложенных интервалов некоторого периода".

Это может быть "5 лучших по успеваемости студентов в каждом семестре за последний учебный год", или "помесячная динамика позиции 10 наиболее продающихся товаров", или, как у нас в сервисе визуализации PostgreSQL-планов explain.tensor.ru, "3 наиболее активных страны за каждый день":

Недавно попался на глаза примерно такой кусок запроса, и тут прекрасно примерно все:

• множество чтений из CTE (хоть и единственной записи, но все же);

• извлечение по каждому ключу текста с раскастовкой в jsonb;

• извлечение каждого отдельного json-ключа в каждое отдельное одноименное поле;

• "ручное" преобразование текстового представления массива в json в текстовое представление PostgreSQL.

А как - правильно?

В иерархических структурах регулярно возникает потребность определить ближайшего общего предка в дереве, он же наименьший общий предок (Lowest (Least) Common Ancestor).

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

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

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

При разработке приложений регулярно возникает задача кэширования каких-то данных, которые из хранилища должны читаться много чаще, чем писаться. Давайте рассмотрим на примере простого теста, когда и на каком механизме эффективнее организовать его для JavaScript-приложения - на Map или на Object.

Решим сегодня простую, казалось бы, задачу: как на PostgreSQL можно в строке провести замены по набору пар строк. То есть в исходной строке 'abcdaaabbbcccdcba' заменить, например, 'а' -> 'x', 'bb' -> 'y', 'ccc' -> 'z' и получить 'xbcdxxxybzdcbx'.

Фактически, мы попробуем создать аналог str_replace или strtr.

В большинстве учетных систем, типа нашего СБИС, рано или поздно возникает проблема быстрого отображения реестра, в который по просьбам бизнес‑пользователей накручено несколько комбинируемых фильтров с очень редкой выборкой, ну никак не ложащихся в вашу красивую структуру базы данных и индексов базовой таблицы реестра — что‑нибудь типа "список продаж покупателям, чей день рождения выпадает на 29 февраля".

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

На примере простой задачи клонирования ключей объекта посмотрим, есть ли реальные альтернативы по производительности столь презираемой JavaScript-разработчиками функции eval().

Подобная задача возникает, если оригинальное значение ключа надо оставить у объекта, а как-то обработанное - положить рядом в новый соответствующий ключ. То есть, для начала, из {"a" : 1, "b" : 2} надо получить {"a" : 1, "a-copy" : 1, "b" : 2, "b-copy" : 2}.

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

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

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

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

Если не видели дайджест за прошлый год — время наверстать упущенное!

Достаточно часто при проектировании схемы БД возникает задача сохранить по основной сущности некоторый набор простых второстепенных данных.

Например, это могут быть ФИО сотрудников, принимающих участие во встрече, список приложенных к сообщению файлов или перечень отгружаемых по документу позиций.

Во всех этих случаях мы заранее понимаем, что список этот меняется редко и ни индексировать эти данные, ни искать по ним, ни извлекать отдельно от основной сущности (встречи, сообщения или документа), мы не захотим.

Давайте посмотрим, какие варианты хранения таких данных мы можем использовать в PostgreSQL, и какой из них окажется в разы более эффективным.

Представим, что у вас есть некоторая табличка статистики, куда вы периодически скидываете таймстамп последнего "текущего" состояния в паре координат - например, (ID организации, ID сотрудника).

Как больно наступить на грабли в совсем простом, казалось бы, запросе?

Наверняка, многие из вас пользуются explain.tensor.ru - нашим сервисом визуализации PostgreSQL-планов или уже даже развернули его на своей площадке. Но визуализация конкретного плана - это лишь небольшая помощь разработчику, поэтому в "Тензоре" мы создали сервис, который позволяет увидеть сразу многие аспекты работы сервера: медленные или гигантские запросы, возникающие блокировки и ошибки, частоту и результаты проходов [auto]VACUUM/ANALYZE.

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

В прошлых частях цикла мы:

- рассмотрели базовые концепты работы с многопоточностью в JavaScript на примере среды Node.js;

- научились формировать общую очередь и каналы обмена данными и сигналами, чтобы более эффективно управлять загрузкой потоков;

- использовали разделяемую память и Atomics-операции как самое быстрое средство обмена большими блоками данных;

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

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

В данном случае эффективность - это не про максимально возможную скорость обработки каждой отдельной задачи, а про сбалансированное использование аппаратных ресурсов с учетом тех ограничений, на которые мы готовы пойти. Особенно актуально это для различных "облачных" размещений, где оплата идет за фактически потребленные CPU и RAM.

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

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

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

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

Но тут возникает две проблемы:

1. как эффективно доставить данные в обрабатывающий поток

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

В этом нам как раз и помогут два рассматриваемых в этой статье концепта работы с многопоточностью: разделяемая (shared) память и потокобезопасные (thread-safe, Atomics) операции над ней.

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

Но вот неприятность: такой алгоритм очень неравномерно нагружает потоки и не полностью утилизирует их возможности - пока кто-то простаивает, другой уже копит очередь. Как это можно обойти?