Архитектура ИС: PostgreSQL, Node.js и highload
Мы заканчиваем мини-серию статей о работе с агрегатами в PostgreSQL:
- эффективная обработка потока «фактов»
И сегодня поговорим о том, как можно снизить суммарные задержки на вставку множества изменений в таблицы агрегатов за счет использования промежуточных таблиц и внешней обработки.
В прошлой статье мини-цикла о работе с агрегатами я рассказывал, как организовать эффективное многопоточное преобразование потока первичных данных в данные агрегированные. Там мы рассматривали задачу "свертки" продаж в агрегаты вида товар/дата/кол-во.
Сегодня мы рассмотрим более сложный вариант, который зачастую начинается со слов "А заказчик захотел…" и приводит нас к иерархичным агрегатам в нескольких одновременных разрезах, которые позволяют нам в СБИС практически мгновенно строить оперативные отчеты в подсистемах организации торговли, бухгалтерского учета и даже управления активными продажами.
Предположим, вам надо обработать на PostgreSQL большое (не, не так... БОЛЬШОЕ) количество записей, чтобы посчитать какие-нибудь агрегаты. В предыдущей статье были разобраны различные варианты, как это можно организовать, а в этой посмотрим, как при этом особо никого не заблокировать, включая "набегающий поток" данных.
Например, это может быть пересчет остатков и ведение сводных продаж по товарам при их постоянных отгрузках, или агрегация сальдо и оборотов по бухгалтерским счетам, при массовых изменениях проводок, или что-то еще... В любой управленческой системе подобных задач наберется горка, и СБИС тоже не является исключением.
Но у всех этих ситуаций есть общий момент - количество изменений сильно больше количества целевых агрегатов. Например: тысячи товаров, по каждому десятки тысяч отгрузок в день.
С течением жизни приложения в его БД накапливается все больше данных. Десктопное оно, SaaS или даже мобильное - неважно, в современном мире почти каждый что-то хранит "у себя".
Если это какая-то локальная утилита - не страшно, само ее существование у пользователя достаточно ограничено. Но если это что-то вроде нашего СБИС, который накапливает и помогает анализировать операции за все время существования бизнеса, то, по мере его роста, не только операций становится больше, но и понимания, какие именно сводные отчеты помогают в оперативном управлении.
Вот про то, как сделать такие отчеты быстрыми, какие бывают способы их реализации и встречаются "грабли" на этом пути, сегодня и поговорим.
Для многих систем характерен паттерн постоянного накопления данных с течением времени. Причем основная их масса больше никогда не изменяется - то есть они пишутся в режиме append-only.
Это не только различного рода логи и метрики оборудования, но и такие, казалось бы, несвязанные вещи, как переписка между пользователями или комментарии к новостям.
Около года назад я уже писал про модель организации секционирования таких данных и вызываемые этим каскадные изменения в структуре БД. А сегодня на примере нашего сервиса анализа логов PostgreSQL-серверов разберем особенности обслуживания организованных так баз, и как грамотный подход (и немного ночной работы) может сократить затраты на инфраструктуру в разы.
Одним из наиболее частых требований-"хотелок" бизнеса является построение всяких разных рейтингов - "самые оборотистые клиенты", "самые продаваемые позиции", "самые активные сотрудники", … - любимая тема разных дашбордов.
Традиционно, есть два подхода к этой задаче: запрос по требованию по "сырым" данным или предварительная агрегация. И если "просто посчитать" такой отчет по первичке - упражнение для SQL-новичка, но очень "тяжелое" для производительности СУБД, то вариант сделать так, чтобы он строился практически мгновенно при большом количестве активных аккаунтов независимых бизнесов, как у нас в СБИС, без необходимости пересчитывать агрегированную статистику каждый день судорожно по всем клиентам - интересная задача.
'Buffers: shared hit=123 read=456, local hit=789' мы хотим как можно быстрее получить JSON такого формата:{
"shared-hit" : 123
, "shared-read" : 456
, "local-hit" : 789
}
CREATE TABLE task AS
SELECT
id
, (random() * 100)::integer person -- всего 100 сотрудников
, least(trunc(-ln(random()) / ln(2)), 10)::integer priority -- каждый следующий приоритет в 2 раза менее вероятен
FROM
generate_series(1, 1e5) id; -- 100K задач
CREATE INDEX ON task(person, priority);
EXISTS — вот с самого простого варианта и начнем:SELECT
*
FROM
generate_series(0, 99) pid
WHERE
EXISTS(
SELECT
NULL
FROM
task
WHERE
person = pid AND
priority = 10
);
EXPLAIN, но с некоторыми странными узлами — вы знаете, куда идти.
EXPLAIN [ANALYZE].
JOIN и предложим альтернативный вариант решения на ней той же задачи.
«Отличие enterprise [решения] от всего остального — он всегда идёт от запросов бизнеса и решает какую-то бизнес-задачу.» [src]Вот и давайте посмотрим, какие именно прикладные задачи и как можно решить с помощью PostgreSQL и сократить время анализа данных с нескольких секунд на бизнес-логике до десятков миллисекунд, умея эффективно применять следующие алгоритмы непосредственно внутри SQL-запроса:
EXPLAIN (ANALYZE, BUFFERS) ... — любимый инструмент познания особенностей работы этой СУБД, то новые полезные «фишки» нашего сервиса визуализации и анализа планов explain.tensor.ru наверняка пригодятся вам в этом нелегком деле.
Программист ставит себе на тумбочку перед сном два стакана. Один с водой — на случай, если захочет ночью пить. А второй пустой — на случай, если не захочет.Давайте разбираться — когда сортировка в запросе точно не нужна и несет с собой потерю производительности, когда от нее можно относительно дешево избавиться, а когда сделать из нескольких — одну.
WITH RECURSIVE появилась еще в незапамятные времена версии 8.4, но до сих пор можно регулярно встретить потенциально-уязвимые «беззащитные» запросы. Как избавить себя от проблем подобного рода?WITH TIES из стандарта SQL:2008:OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }
ORDER BY dt, id или ORDER BY dt DESC, id DESC.ORDER BY dt, id DESC? Но второй индекс мы создавать не хотим — ведь это замедление вставки и лишний объем в базе.(dt, id)?