08.09.2021 17:00
Kilor 18 6500 Источник

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

Сегодня на примере вполне реальной задачи рассмотрим такие возможности оператора INSERT ... ON CONFLICT.

Задача: мониторинг

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

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

А еще у нас есть интерфейс, который любит рисовать графики по этим данным. Но ему с каждого датчика достаточно одного отсчета в час: общего количества измерений qty и средней температуры.

Структура данных

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

Для этих целей будет достаточно одной общей таблицы:

CREATE TABLE metrics(
  id
    integer
, ts
    timestamp
, temp_val
    real
, qty
    integer
);

Договорились, что если в записи qty IS NULL, то это первичный "факт" от датчика, а qty IS NOT NULL - это уже сводный агрегат по нему за конкретный час.

Worker-агрегатор

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

INSERT INTO metrics(id, ts, temp_val, qty)
SELECT
  id
, date_trunc('hour', ts) ts
, avg(temp_val) temp_val
, count(*) qty
FROM
  metrics
WHERE
  qty IS NULL AND -- только "первичка"
  ts >= date_trunc('hour', now() - '1 hour'::interval)
GROUP BY
  1, 2;

Подробнее - в статье "Агрегаты в БД — зачем, как, а стоит ли?".

Понятно, что для такого запроса нам и индекс потребуется соответствующий:

CREATE INDEX ON metrics(ts)
  WHERE qty IS NULL; -- только для фактов

Решаем конфликты

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

CREATE UNIQUE INDEX ON metrics(id, ts)
  WHERE qty IS NOT NULL; -- только для агрегатов

Но теперь наш INSERT просто валится с исключением... Непорядок! Нам то ведь надо, чтобы данные обновлялись:

INSERT INTO metrics(id, ts, temp_val, qty)
  SELECT
    id
  , date_trunc('hour', ts)
  , avg(temp_val)
  , count(*)
  FROM
    metrics
  WHERE
    qty IS NULL AND
    ts >= date_trunc('hour', now() - '1 hour'::interval)
  GROUP BY
    1, 2
ON CONFLICT -- перехватываем конфликт
  (id, ts) WHERE qty IS NOT NULL
  DO UPDATE SET -- обновляем запись в новое состояние
    (qty, temp_val) = (EXCLUDED.qty, EXCLUDED.temp_val);

Боремся с "мертвецами"

Но при таком подходе каждая запись прошлого часа многократно UPDATE'ится, что приводит к "раздуванию" таблицы! Это можно легко увидеть, добавив к нашему запросу RETURNING *.

Подробнее - в статье "PostgreSQL Antipatterns: сражаемся с ордами «мертвецов»".

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

INSERT INTO metrics AS m(id, ts, temp_val, qty)
  SELECT
    id
  , date_trunc('hour', ts)
  , avg(temp_val)
  , count(*)
  FROM
    metrics
  WHERE -- #1 : условие отбора "первички"
    qty IS NULL AND
    ts >= date_trunc('hour', now() - '1 hour'::interval)
  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);

Обратите внимание, что нам пришлось дать нашей таблице алиас AS m, чтобы в дальнейшем обратиться к ней в условии по короткому имени.

Собственно, вот и все - наша задача решена "в один запрос" - правда, в нем получилось целых три WHERE.

Комментарии (18)


  1. zVadim
    08.09.2021 19:40
    #23461786
    +2

    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-запросами к ним наворотили вот это с первичкой и агрегатами в одной таблице. А что будет если в какой-то час этот скрипт не отработает, отдельный писать для агрегации прошлых интервалов?


    1. Kilor Автор
      08.09.2021 19:46
      #23461818

      Использование CTE и/или вложенных запросов позволяет сделать сколько угодно WHERE TRUE, но это неспортивно. К тому же у нас не было цели именно три их тут получить - каждый из них возник по определенной причине.

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


      1. v0rdych
        08.09.2021 22:18
        #23462370

        Проблему пропуска решает удаление/перемещение отработанного в другую таблицу. А агрегат дедать из всего, что есть.


        1. Kilor Автор
          08.09.2021 22:49
          #23462464

          Однако это утраивает нагрузку по записи "первички". В описанном варианте - только на вставку в саму таблицу она идет, а в этом - надо дополнительно удалить записи и вставить в архивную таблицу.


          1. edo1h
            09.09.2021 00:43
            #23462704

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


            1. Kilor Автор
              09.09.2021 00:53
              #23462708

              Таки да, и я уже писал про неплохое решение этой проблемы с помощью прокси-таблиц.

              Но "в среднем" читать, даже многократно, много дешевле, чем писать, даже однократно. Хотя "не читать" еще дешевле.


      1. zVadim
        08.09.2021 22:46
        #23462456
        +1

        Наличие хаба "Ненормальное программирование" снимает все вопросы к статье (сразу не обратил внимание). Знание таких возможностей PostgreSQL безусловно полезно.

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


        1. Kilor Автор
          08.09.2021 22:54
          #23462474

          Я имел в виду, что запрос для вставки в таблицу агрегатов будет выглядеть ровно так же, за исключением разве что WHERE в указании ON ONFLICT-индекса.


          1. zVadim
            09.09.2021 00:31
            #23462682
            +1

            К сожалению, странный подход с записью агрегатов в ту же таблицу сработал как красная тряпка, и увел фокус внимания от демонстрации возможностей PostgreSQL к вопросам проектирования БД. Моя "неочевидность" про структуру базы, а не про запросы - они предельно понятны. Спасибо за Ваш труд!


      1. rrrad
        08.09.2021 22:52
        #23462470
        -1

        С отдельной таблицей агрегатов будет на 1 where меньше. Впрочем, если использовать on conflict on constraint xxx, то и в текущей таблице будет на 1 where меньше (вот тут удобный момент, чтобы порекомендовать всегда давать имена индексам и constraint-ам явно, не полагаясь на то, что сгенерирует субд - это сильно облегчает последующие миграции, а также позволит использовать имя в on conflict).


  1. btyshkevich
    09.09.2021 10:05
    #23463354

    Понимаю запрос автора делать аггрегацию в бекграунде, вместо тяжелых 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к и более строк. Но это и правильно.


    1. Kilor Автор
      09.09.2021 10:08
      #23463364

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

      ... и ClickHouse. Последнее нам очень нравится и вообще вызывал дикий восторг, как инструмент для работы с большими массивами данных, но, если посчитать итоговую стоимость владения с учётом внедрения кластера, обучения и поддержки — лучше подумайте два раза, прежде чем тащить его в ваш стек. На наших объёмах данных вложенные усилия окупаются, но если бы мы были чуть меньше, то, наверное, экономика не сошлась бы.


      1. btyshkevich
        09.09.2021 10:41
        #23463478

        Конечно, ваши данные - вам виднее. На 10М строк в день уже можно, меньше - под вопросом.

        Так и образуются локальные минимумы - у кого-то есть опыт в PG, и ему нет смысла осваивать новые технологии. У других такого опыта нет, и им все равно куда вкладываться - в старый PG или новый КХ. Эволюционные процессы в действии, результат непредсказуем.

        Тем не менее использование транзакционной СУБД в качестве OLAP хранилища всегда будет считаться архитектурной недоработкой. Пусть не КХ, ставьте что-то другое с колонками, но не обычный PG. Может быть гринплюм, может нет. Но точно не эти извраты с on conflict и тремя where.


        1. Kilor Автор
          09.09.2021 11:13
          #23463650

           На 10М строк в день уже можно, меньше - под вопросом. ... Тем не менее использование транзакционной СУБД в качестве OLAP хранилища всегда будет считаться архитектурной недоработкой.

          Вот стоит у нас инстанс PG под базой мониторинга планов запросов со средней скоростью вставки 30K записей/сек - это плюс-минус миллиард записей в сутки, и пока попытки прикрутить Greenplum, Citus, CH как-то не показывают результаты лучше, но явно делают систему дороже в обслуживании.

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

          Мне ON CONFLICT в PG кажется гораздо меньшим извратом, чем вот такое построение агрегатов в CH, например:


          1. btyshkevich
            09.09.2021 12:10
            #23463978

            Картинка не вставилась - непонятно какого рода аггрегация вызвала неудовольство. Да, в КХ все делается через мерджи, в этом его суть. Отсюда эти суффиксы -State & - Merge. Непривычно поначалу, зато как размерешься становится хорошо.

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

            PG конечно хорош, особенно если в него погрузиться так глубоко, как вы смогли (судя по статьям). Однако КХ при 1/10 затрат на обучение сотрудника даёт как минимум сравнимый результат. Многие проблемы, описанные в ваших статьях, решаются "из коробки" стандартными способами.

            Подскажите, какие особые издержки (помимо необходимости изучить новый инструмент и поддерживать второй кластер) вы увидели в эксплуатации КХ?

            Миллиард в сутки - хорошая нагрузка, тут уже не обойтись одним сервером, и даже 3-мя, на каком угодно инструменте. Так что какого-то особого оверкила с КХ тут явно не будет. Но потратить неделю на обучение - таки да, придется.


            1. Kilor Автор
              09.09.2021 12:16
              #23464022

              Так поддержка второго кластера - и есть основные эксплуатационные издержки. А так пока нам и одного PG-сервера хватает.

              Я не говорю, что CH плох или PG идеален во всем, но у каждого инструмента есть свои плюсы и минусы, и они не всегда перекрывают саму необходимость использовать на один инструмент больше.


        1. Dansoid
          09.09.2021 22:43
          #23466772

          Тем не менее использование транзакционной СУБД в качестве OLAP хранилища всегда будет считаться архитектурной недоработкой

          Kaгбы SingleStore (бывший MemSQL) с вами не согласен. Да там тоже есть колоночное хранилище, но и табличное также. Запросы быстры как молния, а time series прикручен как само собой разумеющееся.

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


          1. btyshkevich
            10.09.2021 13:04
            #23468832

            в КХ тоже есть табличное - RockDB, sqlite, да и нодой PG кластера он может быть. Речь же не про это.

            Я говорю что OLAP задача связана с перевариванием огромного количества данных, и очень часто упирается в производительность диска. Значит надо паралелить и меньше читать. Но как читать меньше? Меньше строк и меньше столбцов. Если нет столбцовой организации, то на больших объемах придется бить на таблички и тормозить на джойнах.

            За MemSQL рад что он живет и развивается.