Русский
Русский
English
Авторизация
Ip-адрес
Восстановление пароля
Регистрация
Статистика
Реклама

Сбис

Множественные источники данных в интерфейсе client-side SQL

25.05.2021 12:05:50 | Автор: admin

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

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

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

Постановка задачи

У нас есть два сервиса. Как бы может быть и больше, но, следуя предыдущей картинке, пусть для определенности это будутсервисы Звонков и Контактов.

Спасибо коллегам из CRM за интересную задачу.

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

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

Группировка нескольких звонков в одну записьГруппировка нескольких звонков в одну запись

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

Неудачное решение #1: "дай мне все"

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

Да и для сервиса задача "отдать все" совсем нелегка, если там данных за несколько лет работы. Но зачем нам "все", если пользовательдальше первой страницы листает очень редко?

Неудачное решение #2: "частый гребень"

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

А теперь давайте представим, что у нас все звонки (или очень много) оказались хронологически "над" первым же контактом - что будет? Абудут те же самые тормоза в интерфейсе, что и в предыдущем варианте.

Кроме того, мы илиотправим на сервис много запросов(на каждый из интервалов), чем создадим избыточную нагрузку. Или отправимодин запрос со списком всех интервалов, но он заведомо будет отрабатывать "долго".

Удачное решение #1: "чтение сегментами"

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

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

Например, в нашем примере получилось, что ключ времени "крайнего" звонка соответствует только 15 из 20 прочитанных контактов. Про порядок оставшихся 5 контактов и других звонков мы не можем ничего сказать, потому что "других звонков" как раз нету в обозримом пространстве - поэтомунарисовать их пока не можем.

Неудачное решение #3: "One Ring to rule them all"

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

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

Неудачное решение #4: "два ключа на server-side"

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

Поскольку у насstateless server-side БЛ, то либо мы их таки и не сохраним, или вынуждены будем городить где-тоотдельное хранилище состояний. Сделать это можно, но совсем не просто:

  • хранение сегментов должно происходить по уникальномуключу экземпляра-выборки-на-странице

  • необходимаполитика инвалидацииэтих данных со временем, чтобы память не "текла"

  • работа с этим хранилищем подразумеваетдополнительные издержки на сериализацию-пересылку-десериализацию

Удачное решение #2: "два ключа на client-side"

Собственно, а зачем нам уносить все это на сервер-сайд, если вседанные нам нужны только на клиенте?.. Давайте их там и оставим.

То есть ровно теданные, которые "не отрисовали" оставить хранитьсяна клиенте (например, прямо в памяти вкладки, даже не в localStorage), пока нам не понадобится их нарисовать.

В нашем предыдущем примере получится что-то вроде:

  • прочитали параллельно 20 контактов и 20 звонков

  • звонки "сгруппировали" в 5 записей

  • нарисовали 5 "групповых" звонков + 15 контактов

  • 5 ненарисованных конктактов оставили в хранилище

  • до 20 чего-то не хватает? запрашиваем! (контакты и звонки по 20, параллельно от своих "крайних" ключей)

  • "задача сведена к предыдущей", только у нас уже сразу 25 контактов на 20 звонков есть

Edge Cases

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

К счастью, такая ситуация достаточнопозитивно воспринимается в интерфейсе, поскольку мы показываем пользователю: "Эй, все хорошо, мы не умерли, не повисли, мы работаем!"

Подробнее..
Категории: Программирование , Алгоритмы , Sql , Блог компании тензор , Sql tips and tricks , Erp-системы , Сбис

SQL HowTo красивые отчеты по дырявым данным GROUPING SETS

28.07.2020 10:08:24 | Автор: admin
Для пользователя наш СБИС представляется единой системой управления бизнесом, но внутри состоит из множества взаимодействующих сервисов. И чем их становится больше тем выше вероятность возникновения каких-то неприятностей, которые необходимо вовремя отлавливать, исследовать и пресекать.

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


Для оперативного мониторинга использования ресурсов Linux-сервера в моменте существует утилита pidstat. То есть если пики нагрузки периодичны их можно высидеть прямо в консоли. Но мы-то хотим эти данные анализировать постфактум, пытаясь найти процесс, создавший максимальную нагрузку на ресурсы.

То есть хочется иметь возможность смотреть по ранее собранным данным разные красивые отчеты с группировкой и детализацией на интервале типа таких:



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

Сначала посмотрим, что за данные мы можем извлечь, если брать все по максимуму:

pidstat -rudw -lh 1
Time UID PID %usr %system %guest %CPU CPU minflt/s majflt/s VSZ RSS %MEM kB_rd/s kB_wr/s kB_ccwr/s cswch/s nvcswch/s Command
1594893415 0 1 0.00 13.08 0.00 13.08 52 0.00 0.00 197312 8512 0.00 0.00 0.00 0.00 0.00 7.48 /usr/lib/systemd/systemd --switched-root --system --deserialize 21
1594893415 0 9 0.00 0.93 0.00 0.93 40 0.00 0.00 0 0 0.00 0.00 0.00 0.00 350.47 0.00 rcu_sched
1594893415 0 13 0.00 0.00 0.00 0.00 1 0.00 0.00 0 0 0.00 0.00 0.00 0.00 1.87 0.00 migration/11.87

Все эти значения делятся на несколько классов. Некоторые из них меняются постоянно (активность CPU и диска), другие редко (выделение памяти), а Command не только редко меняется в рамках одного процесса, но еще и регулярно повторяется на разных PID.

Структура базы


Для простоты давайте ограничимся одной метрикой каждого класса, которые мы будем сохранять: %CPU, RSS и Command.

Раз мы заведомо знаем, что Command регулярно повторяется просто вынесем его в отдельную таблицу-словарь, где UUID-ключом будет выступать MD5-хэш:

CREATE TABLE diccmd(  cmd    uuid      PRIMARY KEY, data    varchar);

А для самих данных нам подойдет таблица такого вида:

CREATE TABLE pidstat(  host    uuid, tm    integer, pid    integer, cpu    smallint, rss    bigint, cmd    uuid);

Обращу внимание, что раз %CPU приходит к нам всегда с точностью 2 знаков после запятой и заведомо не превышает 100.00, то мы спокойно можем домножить его на 100 и положить в smallint. С одной стороны, это избавит нас от проблем точности учета при операциях, с другой все-таки лучше хранить только 2 байта по сравнению с 4 байтами real или 8 байтами double precision.
Подробнее о способах эффективной упаковки записей в PostgreSQL-хранилище можно прочитать в статье Экономим копеечку на больших объемах, а про увеличение пропускной способности базы на запись в Пишем на субсветовой: 1 host, 1 day, 1TB.

Бесплатное хранение NULL'ов


Чтобы сэкономить производительность дисковой подсистемы нашей базы и занимаемый базой объем, постараемся как можно больше данных представить в виде NULL их хранение практически бесплатно, поскольку занимает лишь бит в заголовке записи.
Подробнее с внутренней механикой представления записей в PostgreSQL можно ознакомиться в докладе Николая Шаплова на PGConf.Russia 2016 Что у него внутри: хранение данных на низком уровне. Конкретно хранению NULL посвящен слайд #16.
Снова внимательно посмотрим на виды наших данных:

  • CPU/DSK
    Меняется постоянно, но очень часто обращается в ноль так что выгодно писать в базу NULL вместо 0.
  • RSS/CMD
    Меняется достаточно редко поэтому будем писать NULL вместо повторов в рамках одного и того же PID.

Получается картинка вроде такой, если смотреть на нее в разрезе конкретного PID:



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

То есть у записи с заполненным значением CMD заполнено и значение RSS. Запомним этот момент, он нам еще пригодится.

Собираем красивый отчет


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

Но сделаем это сразу с минимальным использованием ресурсов примерно как в статье про SELF JOIN и оконные функции.

Использование входящих параметров


Чтобы не указывать значения параметров отчета (или $1/$2) в нескольких местах по ходу SQL-запроса, выделим CTE из единственного json-поля, в котором по ключам находятся эти самые параметры:

-- сохраняем параметры отчетаWITH args AS (  SELECT    json_object(      ARRAY[        'dtb'      , extract('epoch' from '2020-07-16 10:00'::timestamp(0)) -- переводим timestamp в integer      , 'dte'      , extract('epoch' from '2020-07-16 10:01'::timestamp(0))      , 'host'      , 'e828a54d-7e8a-43dd-b213-30c3201a6d8e' -- это у нас uuid      ]::text[]    ))

Извлекаем сырые данные


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

CREATE INDEX ON pidstat(host, tm);


-- извлекаем "сырые" данные, src AS (  SELECT    *  FROM    pidstat  WHERE    host = ((TABLE args) ->> 'host')::uuid AND    tm >= ((TABLE args) ->> 'dtb')::integer AND    tm <  ((TABLE args) ->> 'dte')::integer)

Группировка по ключу анализа


Для каждого найденного PID определим интервал его активности и возьмем CMD с первой записи на этом интервале.



Для этого воспользуемся уникализацией через DISTINCT ON и оконными функциями:

-- группировка по ключу анализа, pidtm AS (  SELECT DISTINCT ON(pid)    host  , pid  , cmd  , min(tm) OVER(w) tmb -- начало активности процесса на интервале  , max(tm) OVER(w) tme -- завершение активности  FROM    src  WINDOW    w AS(PARTITION BY pid)  ORDER BY    pid  , tm)

Границы активности процесса


Заметим, что относительно начала нашего интервала первой попавшейся записью может оказаться как та, которая уже имеет заполненное поле CMD (PID#1 на картинке выше), так и с NULL'ом, обозначающим продолжение заполненного выше по хронологии значения (PID#2).

Те из PID, которые остались без CMD в результате предыдущей операции, начались раньше начала нашего интервала значит, эти начала надо найти:



Поскольку мы точно знаем, что очередной сегмент активности начинается с заполненного значения CMD (а там и заполненный RSS, значит), тут нам поможет условный индекс:

CREATE INDEX ON pidstat(host, pid, tm DESC) WHERE cmd IS NOT NULL;


-- определяем начало активности каждого "неопределившегося" процесса, precmd AS (  SELECT    t.host  , t.pid  , c.tm  , c.rss  , c.cmd  FROM    pidtm t  , LATERAL(      SELECT        *      FROM        pidstat -- увы, SELF JOIN не избежать      WHERE        (host, pid) = (t.host, t.pid) AND        tm < t.tmb AND        cmd IS NOT NULL -- садимся на условный индекс      ORDER BY        tm DESC      LIMIT 1  ) c  WHERE    t.cmd IS NULL -- только для "неопределившихся")

Если мы хотим (а мы хотим) знать время окончания активности сегмента, то уже для каждого PID придется воспользоваться двухходовкой для определения нижней границы.
Аналогичную методику мы уже использовали в статье PostgreSQL Antipatterns: навигация по реестру.



-- определяем момент окончания активности сегмента, pstcmd AS (  SELECT    host  , pid  , c.tm  , NULL::bigint rss  , NULL::uuid cmd  FROM    pidtm t  , LATERAL(      SELECT        tm      FROM        pidstat      WHERE        (host, pid) = (t.host, t.pid) AND        tm > t.tme AND        tm < coalesce((          SELECT            tm          FROM            pidstat          WHERE            (host, pid) = (t.host, t.pid) AND            tm > t.tme AND            cmd IS NOT NULL          ORDER BY            tm          LIMIT 1        ), x'7fffffff'::integer) -- MAX_INT4      ORDER BY        tm DESC      LIMIT 1  ) c)

JSON-конвертация форматов записей


Замечу, что мы отбирали в precmd/pstcmd только те поля, которые влияют на последующие строки, а всякие CPU/DSK, которые меняются постоянно нет. Поэтому формат записей в исходной таблице и этих CTE у нас расходится. Не беда!

  • row_to_json превращаем каждую запись с полями в json-объект
  • array_agg собираем все записи в '{...}'::json[]
  • array_to_json преобразуем массив-из-JSON в JSON-массив '[...]'::json
  • json_populate_recordset генерируем из JSON-массива выборку заданной структуры

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

-- склеиваем все, uni AS (  TABLE srcUNION ALL  SELECT    *  FROM    json_populate_recordset( -- развернули в полный      NULL::pidstat    , (        SELECT          array_to_json(array_agg(row_to_json(t))) -- свернули сокращенный формат        FROM          (            TABLE precmd          UNION ALL            TABLE pstcmd          ) t      )    ))


Заполняем NULL-пропуски повторов

Воспользуемся моделью, рассмотренной в статье SQL HowTo: собираем цепочки с помощью window functions.
Сначала выделим группы повторов:

-- выделение групп, grp AS (  SELECT    *  , count(*) FILTER(WHERE cmd IS NOT NULL) OVER(w) grp  -- группы по CMD  , count(*) FILTER(WHERE rss IS NOT NULL) OVER(w) grpm -- группы по RSS  FROM    uni  WINDOW    w AS(PARTITION BY pid ORDER BY tm))

Причем по CMD и RSS группы будут независимы друг от друга, поэтому могут выглядеть примерно так:



Заполним пропуски по RSS и посчитаем продолжительность каждого отрезка, чтобы корректно учесть распределение нагрузки по времени:

-- заполняем пропуски, rst AS (  SELECT    *  , CASE      WHEN least(coalesce(lead(tm) OVER(w) - 1, tm), ((TABLE args) ->> 'dte')::integer - 1) >= greatest(tm, ((TABLE args) ->> 'dtb')::integer) THEN        least(coalesce(lead(tm) OVER(w) - 1, tm), ((TABLE args) ->> 'dte')::integer - 1) - greatest(tm, ((TABLE args) ->> 'dtb')::integer) + 1    END gln -- продолжительность сегмента от предыдущей записи или начала интервала  , first_value(rss) OVER(PARTITION BY pid, grpm ORDER BY tm) _rss -- заполнение пропусков по RSS  FROM    grp  WINDOW    w AS(PARTITION BY pid, grp ORDER BY tm))



Мультигруппировка с помощью GROUPING SETS


Поскольку мы хотим увидеть в результате одновременно и сводную информацию по всему процессу, и его детализацию по разным сегментам активности, воспользуемся группировкой сразу по нескольким наборам ключей с помощью GROUPING SETS:

-- мультигруппировка, gs AS (  SELECT    pid  , grp  , max(grp) qty -- количество сегментов активности по PID  , (array_agg(cmd ORDER BY tm) FILTER(WHERE cmd IS NOT NULL))[1] cmd -- "должен остаться только один"  , sum(cpu) cpu  , avg(_rss)::bigint rss  , min(tm) tmb  , max(tm) tme  , sum(gln) gln  FROM    rst  GROUP BY    GROUPING SETS((pid, grp), pid))


Вариант использования (array_agg(... ORDER BY ..) FILTER(WHERE ...))[1] позволяет нам прямо при группировке, без дополнительных телодвижений получить первое непустое (даже если оно не самое первое) значение из всего набора.
Вариант получения сразу нескольких разрезов целевой выборки очень удобен для формирования различных отчетов с детализацией, чтобы все детализирующие данные не надо было перестраивать, а чтобы в UI они попадали вместе с основной выборкой.

Словарь вместо JOIN


Создаем словарь CMD для всех найденных сегментов:
Подробнее про методику ословаривания можно прочесть в статье PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN.

-- словарь CMD, cmdhs AS (  SELECT    json_object(      array_agg(cmd)::text[]    , array_agg(data)    )  FROM    diccmd  WHERE    cmd = ANY(ARRAY(      SELECT DISTINCT        cmd      FROM        gs      WHERE        cmd IS NOT NULL    )))

А теперь используем его вместо JOIN, получая финальные красивые данные:

SELECT  pid, grp, CASE    WHEN grp IS NOT NULL THEN -- это "сегмент" активности      cmd  END cmd, (nullif(cpu::numeric / gln, 0))::numeric(32,2) cpu -- приводим CPU к "средней" нагрузке, nullif(rss, 0) rss, tmb -- верхняя граница активности, tme -- нижняя граница активности, gln -- продолжительность активности, CASE    WHEN grp IS NULL THEN -- это весь процесс      qty  END cnt, CASE    WHEN grp IS NOT NULL THEN      (TABLE cmdhs) ->> cmd::text -- извлекаем данные из словаря  END commandFROM  gsWHERE  grp IS NOT NULL OR -- это запись "сегмента"  qty > 1 -- или в процессе больше одного сегментаORDER BY  pid DESC, grp NULLS FIRST;



Напоследок убедимся, что весь наш запрос при выполнении оказался достаточно легковесным:


[посмотреть на explain.tensor.ru]

Всего 44ms и 33MB данных прочитано!
Подробнее..
Категории: Ненормальное программирование , Системное администрирование , Postgresql , Sql , Администрирование баз данных , Linux , Блог компании тензор , Dba , Sql tips and tricks , Null , Pidstat , Сбис , Json_populate_recordset , Array_to_json

SQL HowTo префиксный FTS-поиск с релевантностью по дате

18.12.2020 00:05:23 | Автор: admin
В нашем СБИС, как и в любой другой системе работы с документами, по мере накопления данных у пользователей возникает желание их "поискать".

Но, поскольку люди не компьютеры, то и ищут они примерно как "что-то там такое было от Иванова или от Ивановского нет, не то, раньше, еще раньше вот оно!"

То есть технически верное решение это префиксный полнотекстовый поиск с ранжированием результатов по дате.

Но разработчику это грозит жуткими проблемами ведь для FTS-поиска в PostgreSQL используются пространственные типы индексов GIN и GiST, которые не предусматривают подсовывания дополнительных данных, кроме текстового вектора.

Остается только грустно вычитывать все записи по совпадению префикса (тысячи их!) и сортировать или, наоборот, идти по индексу даты и фильтровать все встречающиеся записи на совпадение префикса, пока не найдем подходящие (как скоро найдется абракадабра?..).

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

Сначала сгенерируем наши тексты-на-дату:

CREATE TABLE corpus ASSELECT  id, dt, strFROM  (    SELECT      id::integer    , now()::date - (random() * 1e3)::integer dt -- дата где-то за последние 3 года    , (random() * 1e2 + 1)::integer len -- длина "текста" до 100    FROM      generate_series(1, 1e6) id -- 1M записей  ) X, LATERAL(    SELECT      string_agg(        CASE          WHEN random() < 1e-1 THEN ' ' -- 10% на пробел          ELSE chr((random() * 25 + ascii('a'))::integer)        END      , '') str    FROM      generate_series(1, len)  ) Y;

Наивный подход #1: gist + btree


Попробуем накатить индекс и для FTS, и для сортировки по дате вдруг да помогут:

CREATE INDEX ON corpus(dt);CREATE INDEX ON corpus USING gist(to_tsvector('simple', str));

Будем искать все документы, содержащие слова, начинающиеся на 'abc...'. И, для начала, проверим, что таких документов достаточно немного, и FTS-индекс используется нормально:

SELECT  *FROM  corpusWHERE  to_tsvector('simple', str) @@ to_tsquery('simple', 'abc:*');



Ну он, конечно, используется, но занимает это больше 8 секунд, что явно не то, что мы хотели бы потратить на поиск 126 записей.

Может, если добавить сортировку по дате и искать только последние 10 записей станет лучше?

SELECT  *FROM  corpusWHERE  to_tsvector('simple', str) @@ to_tsquery('simple', 'abc:*')ORDER BY  dt DESCLIMIT 10;



Но нет, просто сверху добавилась сортировка.

Наивный подход #2: btree_gist


Но ведь есть же отличное расширение btree_gist, которое позволяет подсунуть скалярное значение в GiST-индекс, что должно нам дать возможность сразу использовать индексную сортировку с помощью оператора расстояния <->, который можно использовать для kNN-поисков:

CREATE EXTENSION btree_gist;CREATE INDEX ON corpus USING gist(to_tsvector('simple', str), dt);


SELECT  *FROM  corpusWHERE  to_tsvector('simple', str) @@ to_tsquery('simple', 'abc:*')ORDER BY  dt <-> '2100-01-01'::date DESC -- сортировка по "расстоянию" от даты далеко в будущемLIMIT 10;



Увы, это не помогает примерно никак.

Геометрия в помощь!


Но отчаиваться рано! Посмотрим на список встроенных классов операторов GiST оператор расстояния <-> доступен только для геометрических circle_ops, point_ops, poly_ops, а с версии PostgreSQL 13 и для box_ops.

Так давайте попробуем перевести нашу задачу в плоскость нашим использующимся для поиска парам (слово, дата) присвоим координаты некоторых точек, чтобы префиксные слова и недалеко отстоящие даты находились как можно ближе:



Разбиваем текст на слова


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

Сформируем вспомогательную таблицу-словарь:

CREATE TABLE corpus_kw ASSELECT  id, dt, kwFROM  corpus, LATERAL (    SELECT      kw    FROM      regexp_split_to_table(lower(str), E'[^\\-a-zа-я0-9]+', 'i') kw    WHERE      length(kw) > 1  ) T;

В нашем примере на 1M текстов пришлось 4.8M слов.

Укладываем слова


Чтобы перевести слово в его координату, представим что это число, записанное в системе счисления с основанием 2^16 (ведь UNICODE-символы мы тоже хотим поддержать). Только записывать мы его будем начиная с фиксированной 47-й позиции:



Можно было бы начинать и с 63-й позиции, это даст нам значения чуть меньше 1E+308, предельных для double precision, но тогда возникнет переполнение при построении индекса.

Получается, что на координатной оси все слова окажутся упорядочены:



ALTER TABLE corpus_kw ADD COLUMN p point;UPDATE  corpus_kwSET  p = point(    (      SELECT        sum((2 ^ 16) ^ (64 - i) * ascii(substr(kw, i, 1)))      FROM        generate_series(1, length(kw)) i    )  , extract('epoch' from dt)  );CREATE INDEX ON corpus_kw USING gist(p);

Формируем поисковый запрос


WITH src AS (  SELECT    point(      ( -- копипасту можно вынести в функцию        SELECT          sum((2 ^ 16) ^ (48 - i) * ascii(substr(kw, i, 1)))        FROM          generate_series(1, length(kw)) i      )    , extract('epoch' from dt)    ) ps  FROM    (VALUES('abc', '2100-01-01'::date)) T(kw, dt) -- поисковый запрос)SELECT  *, src.ps <-> kw.p dFROM  corpus_kw kw, srcORDER BY  dLIMIT 10;



Теперь у нас на руках id искомых документов, уже отсортированных в нужном порядке и заняло это меньше 2ms, в 4000 раз быстрее!

Небольшая ложка дегтя


Оператор <-> ничего не знает про наше упорядочение по двум осям, поэтому искомые наши данные находятся лишь в одной из правых четвертей, в зависимости от необходимой сортировки по дате:



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

CREATE UNIQUE INDEX ON corpus(id);

Доработаем запрос:

WITH src AS (  SELECT    point(      (        SELECT          sum((2 ^ 16) ^ (48 - i) * ascii(substr(kw, i, 1)))        FROM          generate_series(1, length(kw)) i      )    , extract('epoch' from dt)    ) ps  FROM    (VALUES('abc', '2100-01-01'::date)) T(kw, dt) -- поисковый запрос), dc AS (  SELECT    (      SELECT        dc      FROM        corpus dc      WHERE        id = kw.id    )  FROM    corpus_kw kw  , src  WHERE    p[0] >= ps[0] AND -- kw >= ...    p[1] <= ps[1]     -- dt DESC  ORDER BY    src.ps <-> kw.p  LIMIT 10)SELECT  (dc).*FROM  dc;



Нам немного добавили возникшие InitPlan с вычислением константных x/y, но все равно мы уложились в те же 2 мс!

Ложка дегтя #2


Ничто не дается бесплатно:

SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) FROM pg_class WHERE relname LIKE 'corpus%';


corpus          | 242 MB -- исходный набор текстовcorpus_id_idx   |  21 MB -- это его PKcorpus_kw       | 705 MB -- ключевые слова с датамиcorpus_kw_p_idx | 403 MB -- GiST-индекс

242 MB текстов превратились в 1.1GB поискового индекса.

Но ведь в corpus_kw лежат дата и само слово, которые мы в самом-то поиске уже никак не использовали так давайте их удалим:

ALTER TABLE corpus_kw  DROP COLUMN kw, DROP COLUMN dt;VACUUM FULL corpus_kw;


corpus_kw       | 641 MB -- только id и point

Мелочь а приятно. Помогло не слишком сильно, но все-таки 10% объема удалось отыграть.
Подробнее..
Категории: Алгоритмы , Postgresql , Sql , Блог компании тензор , Sql tips and tricks , Erp-системы , Сбис , Fts , Gist , Fulltext search , Ordered fts , Knn-search

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

26.11.2020 10:08:34 | Автор: admin
Ровно год назад с рассказа о нашем сервисе визуализации планов запросов мы начали публикацию на Хабре серии статей, посвященных работе с PostgreSQL и его особенностям. Это уже пройденные нами грабли, интересные наработки, накопившиеся рекомендации, применяемые в разработке Тензора те вещи, которые помогают нам делать СБИС более эффективным.


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


И все их данные надо где-то хранить и эффективно извлекать. Поэтому еще в далеком 2012 году мы сделали ставку на PostgreSQL, и теперь это основное хранилище данных наших сервисов:

  • почти 9000 баз общим объемом 1PB
  • свыше 200TB данных клиентов
  • 1500 разработчиков работают с БД

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

Для удобства все статьи разбиты на несколько циклов:

  • Анализ запросов
    Наглядно демонстрируем все тайны EXPLAIN [ANALYZE].
  • SQL Antipatterns и оптимизация SQL
    Понимаем как [не] надо решать те или иные задачи в PostgreSQL и почему.
  • SQL HowTo
    Пробуем подходы к реализации сложных алгоритмов на SQL для развлечения и с пользой.
  • DBA
    Присматриваем за базой, чтобы ей легко дышалось.
  • Прикладные решения
    Решаем с помощью PostgreSQL конкретные бизнес-задачи.


Анализ запросов в PostgreSQL


Первое, чему необходимо научиться для разработки эффективных запросов это оценивать их ресурсоемкость. Для этого мы разработали и выложили в публичный доступ удобный сервис визуализации планов explain.tensor.ru.

26.11 О чем молчит EXPLAIN, и как его разговорить (+38, &check;128)
...
Классический вопрос, с которым разработчик приходит к своему DBA или владелец бизнеса к консультанту по PostgreSQL, почти всегда звучит одинаково: Почему запросы выполняются на базе так долго?

Но, как сказано в той же документации, Понимание плана это искусство, и чтобы овладеть им, нужен определённый опыт, Но можно обойтись и без него, если воспользоваться подходящим инструментом!

11.02 Массовая оптимизация запросов PostgreSQL (видео) (+28, &check;131)
...
В докладе представлены некоторые подходы, которые позволяют следить за производительностью SQL-запросов, когда их миллионы в сутки, а контролируемых серверов PostgreSQL сотни.

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

26.03 Рецепты для хворающих SQL-запросов (видео) (+23, &check;143)
...
Многие ситуации, которые делают запрос медленным и прожорливым по ресурсам, типичны и могут быть распознаны по структуре и данным плана.

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

05.06 Понимаем планы PostgreSQL-запросов еще удобнее (+25, &check;88)
...
Новые возможности explain.tensor.ru:

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

29.07 Вооруженным глазом: наглядно о проблемах PostgreSQL-запроса (+32, &check;69)
...
Сегодня мы научимся определять больные места навскидку в больших и сложных планах, лишь мельком взглянув на них вооруженным глазом. В этом нам помогут различные варианты визуализации: сокращенный текстовый вид, круговая диаграмма, плитка, диаграмма выполнения.

10.08 Правильно [c]читаем параллельные планы PostgreSQL (+17, &check;33)
...
Рассматриваем странности со временем исполнения узлов при активации параллельного выполнения.
В наш век закончившейся гонки мегагерцев и победивших многоядерных и многопроцессорных систем такое поведение является непозволительной роскошью и расточительностью. Поэтому, начиная с версии PostgreSQL 9.6, при отработке запроса часть операций может выполняться несколькими процессами одновременно.

03.09 PostgreSQL Query Profiler: как сопоставить план и запрос (видео) (+13, &check;59)
...
Какие соображения помогают нам превращать сложно читаемый кусок лога сервера в красиво оформленный запрос с контекстными подсказками по соответствующим узлам плана.

29.10 Анализируем слона по частям (+19, &check;24)
...
Очередные улучшения юзабилити explain.tensor.ru: гистограммы на узлах, полезная статистика для мега-планов, персональный архив и генеалогия планов.


SQL Antipatterns и оптимизация SQL


09.12 CTE x CTE (+8, &check;35)
...
JOIN нескольких CTE почти всегда зло. Небольшая заметка, как его можно избежать в конкретном примере.
Тут надо вспомнить, что CTE Scan является аналогом Seq Scan то есть никакой индексации, а только полный перебор.

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

10.12 вредные JOIN и OR (+20, &check;108)
...
Разбираем на примере конкретного запроса несколько методик оптимизации и учимся использовать ленивые вычисления в PostgreSQL:

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

11.12 статистика всему голова (+10, &check;54)
...
В столбце ratio как раз показывается отношение в разах между планировавшимся на основании статистики и фактически прочитанным количеством записей. Чем больше это значение тем хуже статистика отражает реальное положение дел в вашей таблице.

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

12.12 сизифов JOIN массивов (+14, &check;37)
...
Иногда возникает задача склеить внутри SQL-запроса из переданных в качестве параметров линейных массивов целостную выборку с теми же данными по столбцам.
Вспоминаем о расширенных возможностях работы с массивами:

  • WITH ORDINALITY
  • Multi-argument UNNEST

19.12 передача наборов и выборок в SQL (+8, &check;95)
...
Периодически у разработчика возникает необходимость передать в запрос набор параметров или даже целую выборку на вход. Иногда попадаются очень странные решения этой задачи.
Сравниваем разные варианты передачи данных в запрос:

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

24.12 обновляем большую таблицу под нагрузкой (+14, &check;126)
...
Как стоит поступить (а как точно не надо), если в многомиллионной активно используемой таблице PostgreSQL нужно обновить большое количество записей проинициализировать значение нового поля или скорректировать ошибки в существующих записях? А при этом сохранить свое время и не потерять деньги компании из-за простоя.
Почему один UPDATE и ORDER BY + LIMIT это печально для подобной задачи, а сегментное обновление и предварительно рассчитанные вычисления в самый раз.

20.01 редкая запись долетит до середины JOIN (+18, &check;119)
...
Если писать SQL-запросы без анализа алгоритма, который они должны реализовать, ни к чему хорошему с точки зрения производительности это обычно не приводит.

Такие запросы любят кушать процессорное время и активно почитывать данные практически на ровном месте. Причем, это вовсе не обязательно какие-то сложные запросы, наоборот чем проще он написан, тем больше шансов получить проблемы. А уж если в дело вступает оператор JOIN
Разбираем на моделях способы оптимизации JOIN + GROUP BY и JOIN + LIMIT с помощью CASE и LATERAL.

27.01 ударим словарем по тяжелому JOIN (+8, &check;107)
...
Итоговые выводы:

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

02.03 меняем данные в обход триггера (+24, &check;61)
...
Например, на таблице, в которой вам надо что-то поправить, висит злобный триггер ON UPDATE, переносящий все изменения в какие-нибудь агрегаты. А вам надо все пообновлять (новое поле проинициализировать, например) так аккуратно, чтобы эти агрегаты не затронулись.
Почему быстро отключить и снова включить триггер плохая идея. Как его обойти с помощью переменных сессии.

10.03 сказ об итеративной доработке поиска по названию (+17, &check;82)
...
Что вообще обычно подразумевает пользователь, когда говорит про быстрый поиск по названию? Почти никогда это не оказывается честный поиск по подстроке типа ... LIKE '%роза%' ведь тогда в результат попадают не только 'Розалия' и 'Магазин Роза', но и 'Гроза' и даже 'Дом Деда Мороза'.

Пользователь же подразумевает на бытовом уровне, что вы ему обеспечите поиск по началу слова в названии и покажете более релевантным то, что начинается на введенное. И сделаете это практически мгновенно при подстрочном вводе.
Как ищут строки: pg_trgm, FTS, text_pattern_ops, btree + UNION ALL. И как можно неаккуратно все разломать: пейджинг, подзапросы, DISTINCT.

12.03 сражаемся с ордами мертвецов (+32, &check;106)
...
Как оградить свои UPDATE'ы от лишней работы с диском и блокировок с помощью объединения операций и IS DISTINCT FROM.

31.03 вычисление условий в SQL (+26, &check;65)
...
SQL это не C++, и не JavaScript. Поэтому вычисление логических выражений происходит иначе.
  • ускоряем триггер за счет выноса проверки из функции в WHEN.
  • оптимизируем OR/AND-цепочку с помощью CASE
  • упрощаем написание сложных условий

27.04 навигация по реестру (+22, &check;74)
...
Все будет очень просто, на уровне Капитана Очевидность делаем просмотр реестра событий с сортировкой по времени.
  • плохо: считать сегменты на бизнес-логике
  • плохо: использовать LIMIT + OFFSET
  • хорошо: использовать курсоры, но делать это аккуратно

14.05 насколько глубока кроличья нора? пробежимся по иерархии (+19, &check;83)
...
В сложных ERP-системах многие сущности имеют иерархическую природу, когда однородные объекты выстраиваются в дерево отношений предок потомок это и организационная структура предприятия (все эти филиалы, отделы и рабочие группы), и каталог товаров, и участки работ, и география точек продаж, ...
Пишем сложный запрос, чтобы извлекать минимум данных при проходах по дереву.

24.06 подозрительные типы (+40, &check;60)
...
Типизация данных в PostgreSQL, при всей своей логичности, действительно преподносит порой очень странные сюрпризы. В этой статье мы постараемся прояснить некоторые их причуды, разобраться в причине их странного поведения и понять, как не столкнуться с проблемами в повседневной практике.

28.06 накручиваем себе проблемы (+21, &check;56)
...
Рассматриваем причины накрутки serial при ON CONFLICT и счетчика транзакций при ROLLBACK.

08.07 SELF JOIN vs WINDOW (+14, &check;32)
...
Ускоряем запрос в 100 раз с помощью оконных функций на примере мониторинга блокировок.

14.07 Unreal Features of Real Types, или Будьте осторожны с REAL (+9, &check;10)
...
Я решил бегло пробежаться по коду доступных мне SQL-запросов, чтобы посмотреть, насколько часто в них используется тип REAL. Достаточно часто используется, как оказалось, и не всегда разработчики понимают опасности, стоящие за ним. И это несмотря на то, что в Интернете и на Хабре достаточно много хороших статей про особенности хранения вещественных чисел в машинной памяти и о работе с ними. Поэтому в этой статье я постараюсь применить такие особенности к PostgreSQL, и попробую на пальцах рассмотреть связанные с ними неприятности, чтобы разработчикам SQL-запросов было легче избежать их.

04.08 Должен остаться только один! (+24, &check;80)
...
Сегодня на предельно простых примерах посмотрим, к чему это может приводить в контексте использования GROUP/DISTINCT и LIMIT вместе с ними.

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

20.08 уникальные идентификаторы (+21, &check;67)
...
Рассматриваем эффективность и проблемы различных способов получить уникальные идентификаторы в базе и их проблемы:

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

01.10 Бесконечность не предел!, или Немного о рекурсии (+18, &check;47)
...
Рекурсия очень мощный и удобный механизм, если над связанными данными делаются одни и те же действия вглубь. Но неконтролируемая рекурсия зло, которое может приводить или к бесконечному выполнению процесса, или (что случается чаще) к выжиранию всей доступной памяти.

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

07.10 убираем медленные и ненужные сортировки (+27, &check;91)
...
Просто так результат SQL-запроса возвращает записи в том порядке, который наиболее удобен серверу СУБД. Но человек гораздо лучше воспринимает хоть как-то упорядоченные данные это помогает быстро сравнивать соответствие различных датасетов.

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

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


10.11 работаем с отрезками в кровавом энтерпрайзе (+27, &check;64)
...
Давайте посмотрим, какие именно прикладные задачи и как можно решить с помощью PostgreSQL и сократить время анализа данных с нескольких секунд на бизнес-логике до десятков миллисекунд, умея эффективно применять следующие алгоритмы непосредственно внутри SQL-запроса:

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

18.11 DBA-детектив, или Три дела о потерянной производительности (видео) (+16, &check;45)
...
Дедукция и индукция помогут нам вычислить, что же все-таки хотел получить от СУБД разработчик, и почему это получилось не слишком оптимально. Итак, сегодня нас ждут:

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


SQL HowTo


30.12 рисуем морозные узоры на SQL (+24, &check;52)
...
Немного SQL-магии: математика, рекурсия, псевдографика.

13.01 собираем цепочки с помощью window functions (+11, &check;40)
...
Иногда при анализе данных возникает задача выделения цепочек в выборке то есть упорядоченных последовательностей записей, для каждой из которых выполняется некоторое условие.

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

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

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

31.01 пишем while-цикл прямо в запросе, или Элементарная трехходовка (+8, &check;97)
...
Периодически возникает задача поиска связанных данных по набору ключей, пока не наберем нужное суммарное количество записей.

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

19.06 1000 и один способ агрегации (+12, &check;74)
...
Рассмотрим некоторые способы, с помощью которых можно вычислить агрегаты в PostgreSQL или ускорить выполнение SQL-запроса.

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

28.07 красивые отчеты по дырявым данным GROUPING SETS (+8, &check;28)
...
В этой статье рассмотрим, как все это можно экономично расположить в БД, и как максимально эффективно собрать по этим данным отчет с помощью оконных функций и GROUPING SETS.

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

05.09 курсорный пейджинг с неподходящей сортировкой (+18, &check;64)
...
Пусть у нас есть реестр документов, с которым работают операторы или бухгалтеры. Традиционно, при подобном отображении используется или прямая (новые снизу) или обратная (новые сверху) сортировка по дате и порядковому идентификатору, назначаемому при создании документа ORDER BY dt, id или ORDER BY dt DESC, id DESC.

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

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

23.09 PostgreSQL 13: happy pagination WITH TIES (+40, &check;45)
...
Используем новые возможности PostgreSQL 13 для упрощения организации постраничной навигации по реестру.

19.10 ломаем мозг об дерево упорядочиваем иерархию с рекурсией и без (+16, &check;62)
...
чтобы для вывода упорядочить элементы дерева в соответствии с иерархией, уж точно придется воспользоваться рекурсией! Или нет? Давайте разберемся, а заодно решим на SQL пару комбинаторных задач.


DBA


20.12 вычищаем клон-записи из таблицы без PK (+13, &check;45)
...
Случаются ситуации, когда в таблицу без первичного ключа или какого-то другого уникального индекса по недосмотру попадают полные клоны уже существующих записей.

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

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

25.12 когда пасует VACUUM чистим таблицу вручную (+21, &check;98)
...
VACUUM может зачистить из таблицы в PostgreSQL только то, что никто не может увидеть то есть нет ни одного активного запроса, стартовавшего раньше, чем эти записи были изменены.

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

15.01 перенос значений SEQUENCE между базами PostgreSQL (+11, &check;43)
...
Как можно перенести в другую PostgreSQL-базу последнее назначавшееся значение автоинкремент-поля типа serial, если в таблице могли быть какие-то удаления, и просто подставить max(pk) уже не подходит?

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

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

19.03 грамотно организовываем синхронизации и импорты (+11, &check;48)
...
При сложной обработке больших наборов данных (разные ETL-процессы: импорты, конвертации и синхронизации с внешним источником) часто возникает необходимость временно запомнить, и сразу быстро обработать что-то объемное.

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

20.05 мониторинг базы PostgreSQL кто виноват, и что делать (+23, &check;100)
...
На что обращать внимание при мониторинге PostgreSQL-базы и как трактовать полученные данные:

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

27.05 в погоне за пролетающими блокировками (+18, &check;41)
...
Шансов поймать блокировки в моменте крайне мало, да и длиться они могут всего по несколько секунд, но ухудшая при этом плановое время выполнения запроса в десятки раз. А хочется-то не сидеть и ловить происходящее в онлайн-режиме, а в спокойной обстановке разобраться постфактум, кого из разработчиков покарать в чем именно была проблема кто, с кем и из-за какого ресурса базы вступил в конфликт.

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

Разве что короткую запись в логе:
process ... still waiting for ...
А давайте попробуем зацепиться именно за нее!

10.06 классифицируем ошибки из PostgreSQL-логов (+9, &check;38)
...
Если мы не хотим потом хвататься за голову, то возникающие в логах PostgreSQL ошибки недостаточно просто считать поштучно их надо аккуратно классифицировать. Но для этого нам придется решить нетривиальную задачу индексированного поиска регулярного выражения, наиболее подходящего для строки.

15.06 кто скрывается за блокировкой (+11, &check;48)
...
Научимся трактовать собранные блокировки и узнавать, кто именно может скрываться за конкретной матрицей конфликтов, и почему результат выглядит именно так.


Решения для PostgreSQL


09.01 БД мессенджера (ч.1): проектируем каркас базы (+3, &check;62)
...
Как можно перевести бизнес-требования в конкретные структуры данных на примере проектирования с нуля базы для мессенджера.

09.01 БД мессенджера (ч.2): секционируем наживую (+5, &check;67)
...
Мы удачно спроектировали структуру нашей PostgreSQL-базы для хранения переписки, прошел год, пользователи активно ее наполняют, вот в ней уже миллионы записей, и что-то все начало подтормаживать.

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

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

22.01 PubSub почти бесплатно: особенности NOTIFY в PostgreSQL (+20, &check;76)
...
Если ваши микросервисы уже используют общую базу PostgreSQL для хранения данных, или ей пользуются несколько экземпляров одного сервиса на разных серверах, можно относительно дешево получить возможность обмена сообщениями (PubSub) между ними без интеграции в архитектуру Redis, RabbitMQ-кластера или встройки в код приложения другой MQ-системы.

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

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

13.02 Фантастические advisory locks, и где они обитают (+11, &check;34)
...
В PostgreSQL существует очень удобный механизм рекомендательных блокировок, они же advisory locks. Мы в Тензоре используем их во многих местах системы, но мало кто детально понимает, как конкретно они работают, и какие проблемы можно получить при неправильном обращении.

13.04 Пишем в PostgreSQL на субсветовой: 1 host, 1 day, 1TB (+19, &check;78)
...
Рассматриваем традиционные подходы масштабирования производительности на конкретном примере:

  • Секционирование
  • Эволюция и рефакторинг БД
  • Размазываем пиковую нагрузку
  • Кэшируем, что можно
Терабайт-в-сутки только звучит страшно. Если вы все делаете правильно, то это всего лишь 2^40 байт / 86400 секунд = ~12.5MB/s, что держали даже настольные IDE-винты. :)

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

22.04 Экономим копеечку на больших объемах в PostgreSQL (+11, &check;44)
...
Продолжая тему записи больших потоков данных, поднятую предыдущей статьей про секционирование, в этой рассмотрим способы, которыми можно уменьшить физический размер хранимого в PostgreSQL, и их влияние на производительность сервера.

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

03.06 Как мы в СБИС автоматический расчет себестоимости делали (видео) (+7, &check;17)
...
Как суровую прагматику требований бизнеса перенести на разработку высоконагруженных сервисов, как бороться с конкурентным доступом к данным, как это все аккуратно обходить и при этом не отстрелить себе ногу.

17.08 У меня зазвонил телефон. Кто говорит?.. Поможет слон (+10, &check;29)
...
Автоматическое определение клиента и его региона по входящему телефонному звонку стало неотъемлемой частью любой развитой HelpDesk или CRM-системы. Только надо уметь делать это быстро тогда появляется масса возможностей.

25.08 Телепортация тонн данных в PostgreSQL (+11, &check;60)
...
Выжимаем максимум пропускной способности из PostgreSQL:
  • Как балансировать писателей и управлять соединениями на бизнес-логике?
  • Как настроить СУБД и ОС?
  • Как избавиться от блокировок?


Если вам интересна какая-то конкретная тема, которую мы еще не затронули напишите в комментариях. Вдруг мы с ней тоже уже сталкивались расскажем подробно.
Подробнее..
Категории: Postgresql , Sql , Администрирование баз данных , Блог компании тензор , Dba , Sql tips and tricks , Erp-системы , Сбис , Тензор

SQL HowTo рейтинг-за-интервал

11.01.2021 16:10:01 | Автор: admin

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

Но просто "самые" за весь доисторический период обычно неинтересны - продал ты 3 года назад вагон валенок, и теперь он у тебя в "самых" продажах вечно. Поэтому обычно хочется видеть"топ" на каком-то ограниченном последнем интервале- например, "за последний год" (точнее, за последние 12 календарных месяцев).

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

Структура хранения

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

То есть для решения задачи нам достаточно таблицы с единственным индексом (рассмотрим только вариант сортировки по сумме, для количества все будет аналогично):

CREATE TABLE item_stat(  item -- товар    integer, sum    numeric(32,2));CREATE INDEX ON item_stat(sum DESC);

Наполнять ее данными мы можем легко и просто -инкрементом в триггерепри проведении продажи. Но как все-таки сделать эффективное "вычитание" данных при завершении месяца?..

"Нужно больше золота"

Чтобы быстро что-то вычесть, нужно четко понимать, что именно.

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

CREATE TABLE item_stat(  interval_id -- 0 - текущие счетчики, 202001 - январь 2020, 202002 - февраль, ...    integer, item    integer, sum    numeric(32,2), UNIQUE(interval_id, item));CREATE INDEX ON item_stat(interval_id, sum DESC);

Момент обновления

Чтобы понять, что вот прямо сейчас надо "вычесть" какой-то месяц, достаточно оперироватьединственным дополнительным параметромтипа"месяц последней актуализации рейтинга продаж". Хранить его можно даже в служебной записи в этой же таблице (если это не помешает Foreign Key, который вы можете захотеть добавить на item):

INSERT INTO item_stat(  interval_id, item, sum)VALUES  (0, 0, 202012) -- служебный ключ (0, 0), значение - 2020'12 вместо суммыON CONFLICT(interval_id, item)  DO UPDATE SET    sum = EXCLUDED.sum; -- всегда заменяем значение

Теперь при операции над продажей (отгрузка/аннулирование) вызываем, можно асинхронно, инкремент/декремент сразудля двух записей - "годичной" и текущего месяца:

INSERT INTO item_stat(  interval_id, item, sum)VALUES  (202001, 1, 100) -- + в рейтинг за январь 2020, (     0, 1, 100) -- + в текущий рейтингON CONFLICT(interval_id, item)  DO UPDATE SET    sum = item_stat.sum + EXCLUDED.sum; -- всегда добавляем в сумму

Если текущиймесяц операции разошелся с месяцем из параметра,асинхронностартуем пересчет "годовых" значений, вычитая показатели за ставшие избыточными месяцы, и переактуализируем значение параметра:

-- "новый" месяц актуальностиWITH next AS (  SELECT 202101)-- предыдущий месяц актуальности, prev AS (  SELECT    sum::integer  FROM    item_stat  WHERE    (interval_id, item) = (0, 0))-- все продажи за период, ставший неактуальным, в разрезе товаров, diff AS (  SELECT    item  , sum(sum) sum  FROM    item_stat  WHERE    interval_id BETWEEN (TABLE prev) - 100 AND (TABLE next) - 100  GROUP BY    1)UPDATE  item_stat dstSET  sum = dst.sum - diff.sumFROM  diffWHERE  (dst.interval_id, dst.item) = (0, diff.item);UPDATE  item_statSET  sum = 202101WHERE  (interval_id, item) = (0, 0);

При построении отчета

Если текущий месяц совпадает с месяцем из параметра, то все значения в "годичном" интервале актуальны - просто выводим топ по индексу:

SELECT  *FROM  item_statWHERE  interval_id = 0 -- текущий "годичный" интервалORDER BY  sum DESCLIMIT 10;

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

Подробнее..
Категории: Postgresql , Sql , Администрирование баз данных , Блог компании тензор , Sql tips and tricks , Erp-системы , Рейтинг , Сбис , Интервал

Агрегаты в БД многомерные суперагрегаты

03.02.2021 10:04:43 | Автор: admin

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

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

Бизнес-требования

И швец, и жнец, и на дуде игрец...И швец, и жнец, и на дуде игрец...

  • уметь быстро получить информацию не толькопо товарам, но ипо складам

  • в том числе исводка-TOPпродаж товаров на интервале

  • в том числес фильтром по складу... или без

  • а ещеграфик динамики продажза месяц по дням... и за год по месяцам... и за все время по годам

  • ... ис любым из фильтровсклад/товар

  • ... и чтобывсе быстроработало!

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

  • появляетсяновый разрез агрегации- по складу

  • необходимаагрегация "без учета" или "по всем"- например, сводные продажи всех товаров склада или продажи товара по всем складам

  • нужныагрегаты для динамики(дневные/месячные/годовые) в разрезе любого фильтра

Структура новых агрегатов

Добавим в таблицу агрегатов новое поле -тип интервалаагрегации (D/M/Y) и новый аналитический разрез -склад.

CREATE TABLE agg(  it    -- товар    integer, wh    -- склад    integer, dt    -- дата продажи/начала кванта    date, quant -- тип кванта D/M/Y    "char", qty   -- количество    double precision);

Замечу, что тут для экономии размера данных мы использовали однобайтныйспецтип "char". Например, такой тип имеет полеrelkind(тип объекта) в системной таблицеpg_class.

Неудобный NULL и удобный ноль

Для аналитики "по всем" используемзначение = 0(не NULL) соответствующего разреза. Это позволит нам всегда передавать значения в один и тот же запрос, без изменения его модели на IS [NOT] NULL.

То есть для запроса "какие товары продавались лучше всего в таком-то месяце" будем использовать запрос вида:

SELECT  it, qtyFROM  aggWHERE  wh = $1::integer AND -- передадим 0 для разреза "по всем складам"  (quant, dt) = ($2::"char", $3::date) -- передадим 'M' для обращения к "месячному" агрегатуORDER BY  qty DESCLIMIT ...;

А если нам понадобится этот же рейтинг по конкретному складу, то простопередадим сюда же ID склада! Очевидно, для такого запроса подходящим будет индекс(quant, dt, wh, qty DESC).

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

Динамика в разрезе фильтра

Давайте теперь сконструируем запрос, который поможет нам нарисовать красивый график по динамике:

SELECT  dt, qtyFROM  aggWHERE  (quant, it, wh) = ($1::"char", $2::integer, $3::integer) AND  dt BETWEEN $4::date AND $5::date -- период графикаORDER BY  dt;

Индекс под него -(quant, it, wh, dt). Почему на первом месте именно quant? Потому что у него очень маленькая селективность, и индекс будет занимать меньше места.

Сборка агрегатов

Итак, вернемся к предыдущей статье и проблемам, которые мы успешно решали там - разделению кросс-блокировок между параллельно работающими потоками. В нынешней ситуации, разделив обработкупо ключу (it, wh), мы заведомо устраним конфликты между обработчиками.

Но возникнет небольшая проблема - давайте посмотрим, как именно эффективнее всего добиться формирования агрегатов:

При проходе по курсору над flow-таблицей мы формируемв памяти "дифф" для инкрементазаписей соответствующих агрегатов по обрабатываемому этим потоком ключу(it, wh)- сразу для каждого из типов интервалов.

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

Обходим блокировки

Единственная точка, в которой у нас могут пересечься два параллельно работающих потока, это формирование/обновление записи queue, соответствующей этому ключу "надагрегата" - например,(0, 0).

К счастью, это достаточно просто обходитсявставкой новой записи в queue, еслиpg_try_advisory_xact_lock(it, wh)для такого ключа вернула намFALSE. То есть да, в разрезе ключа распределения записи в очереди могут быть неуникальны. Но в этом нет ничего страшного, потому что они всего лишь выполняют функциюсигнализатора "во flow что-то может быть по этому ключу". И если нет - не страшно, при обработке этой записи очереди мы заглянем во flow, ничего не найдем, и спокойно завершим обработку.

Итого - мы получили в БД все нужные агрегаты во всех требуемых разрезах, которые помогут нам обеспечить быстрый показ отчета/графика в любой комбинации фильтров:

Подробнее..
Категории: Postgresql , Sql , Администрирование баз данных , Блог компании тензор , Erp-системы , Сбис , Агрегация

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru