Комментарии 18
WITH current_hour as
(
SELECT *
FROM
metrics
WHERE -- #1.1: условие отбора певого часа
ts > ...
)
INSERT INTO metrics AS m(id, ts, temp_val, qty)
SELECT
id
, date_trunc('hour', ts)
, avg(temp_val)
, count(*)
FROM
current_hour
WHERE -- #1.2 : условие отбора "первички"
qty IS NULL AND
GROUP BY
1, 2
ON CONFLICT
(id, ts) WHERE qty IS NOT NULL -- #2 : условие UNIQUE-индекса "агрегатов"
DO UPDATE SET
(temp_val, qty) = (EXCLUDED.temp_val, EXCLUDED.qty)
WHERE -- #3 : условие обновления записи
(m.temp_val, m.qty) IS DISTINCT FROM (EXCLUDED.temp_val, EXCLUDED.qty);Вот таким незатейливым образом с использованием CTE можно получить целых 4 WHERE...
Вот только как быть с посылом из начала статьи "..вместо сложной логики..." ? Вместо простого разделения на две таблицы с очевидными SQL-запросами к ним наворотили вот это с первичкой и агрегатами в одной таблице. А что будет если в какой-то час этот скрипт не отработает, отдельный писать для агрегации прошлых интервалов?
Использование CTE и/или вложенных запросов позволяет сделать сколько угодно WHERE TRUE, но это неспортивно. К тому же у нас не было цели именно три их тут получить - каждый из них возник по определенной причине.
Кстати, разделение на 2 таблицы ровно никак не решит ни "неочевидности" запросов, ни проблемы пропуска интервала при неотработке скрипта (для этого стоит отдельно хранить последний обрабатывавшийся час).
Проблему пропуска решает удаление/перемещение отработанного в другую таблицу. А агрегат дедать из всего, что есть.
Однако это утраивает нагрузку по записи "первички". В описанном варианте - только на вставку в саму таблицу она идет, а в этом - надо дополнительно удалить записи и вставить в архивную таблицу.
зато вы постоянно обрабатываете 100500 записей за последние час-два, даже если ничего не поменялось.
Таки да, и я уже писал про неплохое решение этой проблемы с помощью прокси-таблиц.
Но "в среднем" читать, даже многократно, много дешевле, чем писать, даже однократно. Хотя "не читать" еще дешевле.
Наличие хаба "Ненормальное программирование" снимает все вопросы к статье (сразу не обратил внимание). Знание таких возможностей PostgreSQL безусловно полезно.
Но у такой организации данных в базе могу придумать только два оправдания: демо-пример для статьи или легаси. Вторая таблица может решить проблему "неочевидности" одним своим наименованием)
С отдельной таблицей агрегатов будет на 1 where меньше. Впрочем, если использовать on conflict on constraint xxx, то и в текущей таблице будет на 1 where меньше (вот тут удобный момент, чтобы порекомендовать всегда давать имена индексам и constraint-ам явно, не полагаясь на то, что сгенерирует субд - это сильно облегчает последующие миграции, а также позволит использовать имя в on conflict).
Понимаю запрос автора делать аггрегацию в бекграунде, вместо тяжелых join/group by в рамках выполнения API запроса. Однако предложенные решения выглядят очень неизящно. Особенно с соседнего пригорка под названием Clickhouse. Там это штатная задачка решаемая через Materialized View with AggreragingMergeThree.
Во время мерджей отдельных блоков вставок, КХ соединит все аггрегации вместе как надо.
create materialized view metrics ENGINE = AggregatingMergeTree order by id as
SELECT id , toStartOfDay(ts) ts, avgState(temp_val) temp_val, countState() qty
FROM metrics
GROUP BY id, ts;
Это все. Остается только выборка аггрегатов с суффиксом Merge в рамках API, отдающего данные. Там останется финальный group by, но строк для него будет немного и работать он будет быстро.
Правда придется научиться постить события от датчиков большими блоками, по 5-10к и более строк. Но это и правильно.
Просто процитирую коллег, имеющих больший опыт его использования:
... и ClickHouse. Последнее нам очень нравится и вообще вызывал дикий восторг, как инструмент для работы с большими массивами данных, но, если посчитать итоговую стоимость владения с учётом внедрения кластера, обучения и поддержки — лучше подумайте два раза, прежде чем тащить его в ваш стек. На наших объёмах данных вложенные усилия окупаются, но если бы мы были чуть меньше, то, наверное, экономика не сошлась бы.
Конечно, ваши данные - вам виднее. На 10М строк в день уже можно, меньше - под вопросом.
Так и образуются локальные минимумы - у кого-то есть опыт в PG, и ему нет смысла осваивать новые технологии. У других такого опыта нет, и им все равно куда вкладываться - в старый PG или новый КХ. Эволюционные процессы в действии, результат непредсказуем.
Тем не менее использование транзакционной СУБД в качестве OLAP хранилища всегда будет считаться архитектурной недоработкой. Пусть не КХ, ставьте что-то другое с колонками, но не обычный PG. Может быть гринплюм, может нет. Но точно не эти извраты с on conflict и тремя where.
На 10М строк в день уже можно, меньше - под вопросом. ... Тем не менее использование транзакционной СУБД в качестве OLAP хранилища всегда будет считаться архитектурной недоработкой.
Вот стоит у нас инстанс PG под базой мониторинга планов запросов со средней скоростью вставки 30K записей/сек - это плюс-минус миллиард записей в сутки, и пока попытки прикрутить Greenplum, Citus, CH как-то не показывают результаты лучше, но явно делают систему дороже в обслуживании.
А столь категоричные заявления про "архитектурные недоработки" напоминают желание использовать реактивный самолет вместо такси, чтобы как можно быстрее добраться на другой конец города. Вроде бы и правда "быстрее", но сопутствующие издержки съедают весь профит.
Мне ON CONFLICT в PG кажется гораздо меньшим извратом, чем вот такое построение агрегатов в CH, например:
Картинка не вставилась - непонятно какого рода аггрегация вызвала неудовольство. Да, в КХ все делается через мерджи, в этом его суть. Отсюда эти суффиксы -State & - Merge. Непривычно поначалу, зато как размерешься становится хорошо.
Реактивный самолет - плохое сравнение. Можно вернуть шарик сравнив грузовик с лимузином с мягкими креслами, телевизором и джакузи, но я не буду. Конечно мы строим аналогию по грузовикам. И они бывают разными - от карьерного самосвала типа Белаза, до Камазов и даже Газельки - тут уже трудно найти правильную аналогию. И незачем.
PG конечно хорош, особенно если в него погрузиться так глубоко, как вы смогли (судя по статьям). Однако КХ при 1/10 затрат на обучение сотрудника даёт как минимум сравнимый результат. Многие проблемы, описанные в ваших статьях, решаются "из коробки" стандартными способами.
Подскажите, какие особые издержки (помимо необходимости изучить новый инструмент и поддерживать второй кластер) вы увидели в эксплуатации КХ?
Миллиард в сутки - хорошая нагрузка, тут уже не обойтись одним сервером, и даже 3-мя, на каком угодно инструменте. Так что какого-то особого оверкила с КХ тут явно не будет. Но потратить неделю на обучение - таки да, придется.
Так поддержка второго кластера - и есть основные эксплуатационные издержки. А так пока нам и одного PG-сервера хватает.
Я не говорю, что CH плох или PG идеален во всем, но у каждого инструмента есть свои плюсы и минусы, и они не всегда перекрывают саму необходимость использовать на один инструмент больше.
Тем не менее использование транзакционной СУБД в качестве OLAP хранилища всегда будет считаться архитектурной недоработкой
Kaгбы SingleStore (бывший MemSQL) с вами не согласен. Да там тоже есть колоночное хранилище, но и табличное также. Запросы быстры как молния, а time series прикручен как само собой разумеющееся.
В чем с вами согласен, так что PG это не та база данных на которой это надо ганять. Параллелизация запросов там желает лучших времен.
в КХ тоже есть табличное - RockDB, sqlite, да и нодой PG кластера он может быть. Речь же не про это.
Я говорю что OLAP задача связана с перевариванием огромного количества данных, и очень часто упирается в производительность диска. Значит надо паралелить и меньше читать. Но как читать меньше? Меньше строк и меньше столбцов. Если нет столбцовой организации, то на больших объемах придется бить на таблички и тормозить на джойнах.
За MemSQL рад что он живет и развивается.
SQL HowTo: три WHERE в одном запросе