SQL HowTo: три WHERE в одном запросе

При реализации некоторых прикладных задач в рамках экосистемы СБИС случается сталкиваться с неочевидными возможностями 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.