Тут по работе возникла небольшая задачка с PostgreSQL . Интересна задача тем, что в ней достаточно оправдано использование триггеров. Как показывает опыт, не каждый разработчик знаком со «столь продвинутыми» возможностями постгреса. Поэтому мне показалось, что будет неплохой идеей написать про задачу и ее решение.
Формулировка задачи
В упрощенной форме задача формулируется так. Есть таблица, в которой хранятся временные метки и некое число, соответствующее временной метке:
Данных не очень много, десятки записей за сутки. Поэтому их не страшно хранить в PostgreSQL, а не специализированной time seties базе данных. На самом деле, помимо временной метки и значения, каждая запись содержит и другие данные. Но в рамках данной статьи они нас не интересуют. Считается, что временные метки не повторяются, поэтому их допустимо использовать в качестве первичного ключа.
Необходимо сделать следующее. Для каждого дня берется три интервала времени — с 00:00 до 07:59, с 08:00 до 15:59 и 16:00 до 23:59. Необходимо посчитать какие-то агрегаты за эти интервалы. В рамках поста мы будем считать среднее. Выборка агрегатов должна производиться как можно быстрее, поскольку предполагается, что к ним будет много запросов на чтение.
Решение в лоб
Неплохая идея при решении любой задачи — найти простейшее решение, а затем, если нужно, подумать над оптимизацией.
Заполним таблицу случайными данными:
SELECT tstamp , FLOOR ( random ( ) * 100 )
FROM generate_series (
‘2020-01-01 00:00:00’ :: TIMESTAMP ,
‘2020-01-04 23:00:00’ :: TIMESTAMP ,
‘1 hour’ ) AS tstamp;
… и напишем решение в лоб:
tstamp :: DATE +
concat (
FLOOR ( EXTRACT ( HOUR FROM tstamp ) / 8 ) * 8 ,
‘:00:00’
) :: TIME AS d ,
avg ( val ) :: FLOAT
FROM data_raw
GROUP BY d
ORDER BY d;
Пример результата:
———————+———
2020-01-01 00:00:00 | 50.125
2020-01-01 08:00:00 | 56.125
2020-01-01 16:00:00 | 62.625
2020-01-02 00:00:00 | 51.5
2020-01-02 08:00:00 | 54.25
2020-01-02 16:00:00 | 57.625
2020-01-03 00:00:00 | 45.625
2020-01-03 08:00:00 | 27
2020-01-03 16:00:00 | 44.25
2020-01-04 00:00:00 | 47.75
2020-01-04 08:00:00 | 53.875
2020-01-04 16:00:00 | 53.5
(12 rows)
Разумеется, решение неэффективно, потому что каждый раз выполняется полное сканирование таблицы c группировками, сортировкой и всеми расчетами, см вывод EXPLAIN . Теперь нам нужно получить такой же результат, только быстрее.
Решение на materialized view
Первое, что приходит на ум — это воспользоваться materialized view :
SELECT
tstamp :: DATE +
concat (
FLOOR ( EXTRACT ( HOUR FROM tstamp ) / 8 ) * 8 ,
‘:00:00’
) :: TIME AS d ,
avg ( val )
FROM data_raw
GROUP BY d
ORDER BY d;
— уникальный индекс необходим для использования
— команды refresh materialized view concurrently.
— без concurrently вьюха будет блокироваться на время обновления
CREATE UNIQUE INDEX data_view_idx ON data_view ( d ) ;
Теперь закэшированные агрегаты можно читать прямо из data_view. Главное — не забывать делать:
… после записи очередной пачки данных в data_raw.
Такое решение, в общем-то, имеет полное право на жизнь, но не лишено недостатков. Дело в том, что при каждом рефреше materialized view пересчитывается целиком. Со временем, когда data_raw подрастет, это может стать проблемой.
Решение на триггерах
Триггеры позволяют получить такую же materialized view, только умнее. При каждой записи в data_raw триггер будет обновлять ровно одну строчку нашей самодельной вьюхи.
Итак, создадим новую таблицу:
tstamp TIMESTAMP PRIMARY KEY ,
total BIGINT NOT NULL ,
num INT NOT NULL
) ;
Напишем небольшой триггер на PL/pgSQL :
BEGIN
INSERT INTO data_agg ( tstamp , total , num ) VALUES (
NEW . tstamp :: DATE + concat (
FLOOR ( EXTRACT ( HOUR FROM NEW . tstamp ) / 8 ) * 8 ,
‘:00:00’
) :: TIME , NEW . val , 1
) ON CONFLICT ( tstamp ) DO
UPDATE SET total = data_agg . total + EXCLUDED . total ,
num = data_agg . num + 1 ;
RETURN NULL ;
END
$$ LANGUAGE ‘plpgsql’ ;
… и повесим его на INSERT в таблицу data_raw:
AFTER INSERT ON data_raw
FOR EACH ROW EXECUTE FUNCTION update_data_agg ( ) ;
Триггер представляет собой не более, чем UPSERT в табличку data_agg. Неявный аргумент триггера с именем new представляет собой record, который INSERT’ится в таблицу data_raw. Если бы мы писали триггер на UPDATE, он имел бы два неявных аргумента — new и old .
Чтобы триггер сработал для данных, уже записанных в data_raw, сделаем так:
DELETE FROM data_raw;
COPY data_raw FROM ‘/tmp/data_raw.dat’ ;
Проверяем:
FROM data_agg ORDER BY tstamp;
… и видим такой результат:
———————+———
2020-01-01 00:00:00 | 50.125
2020-01-01 08:00:00 | 56.125
2020-01-01 16:00:00 | 62.625
2020-01-02 00:00:00 | 51.5
2020-01-02 08:00:00 | 54.25
2020-01-02 16:00:00 | 57.625
2020-01-03 00:00:00 | 45.625
2020-01-03 08:00:00 | 27
2020-01-03 16:00:00 | 44.25
2020-01-04 00:00:00 | 47.75
2020-01-04 08:00:00 | 53.875
2020-01-04 16:00:00 | 53.5
(12 rows)
Легко убедиться, что он совпадает с результатом, полученным ранее.
Можно также подстелить себе соломки и запретить операции UPDATE, DELETE и TRUNCATE на таблице data_raw:
BEGIN
RAISE ‘UPDATE / DELETE / TRUNCATE are not allowed on data_raw,’
‘ because triggers are involved!’ ;
RETURN NULL ;
END
$$ LANGUAGE ‘plpgsql’ ;
CREATE TRIGGER data_raw_ins_only BEFORE UPDATE OR DELETE OR TRUNCATE
ON data_raw EXECUTE FUNCTION data_raw_insert_only ( ) ;
Если этого не сделать, то выполнение UPDATE, DELETE и TRUNCATE над таблицей data_raw (например, руками через psql) приведет к тому, что таблица data_agg перестанет быть консистентной.
Заключение
Само собой разумеется, у задачи нет единственного правильного решения. Например, data_raw можно было бы вообще не хранить. Но я предпочитаю всегда хранить сырые данные. Опыт показывает, что рано или поздно заказчик попросит посчитать не только среднее, но также минимумы и максимумы.
Иногда триггеры критикуют за то, что якобы из-за них часть логики оказывается в приложении, а часть — в СУБД, за счет чего становится сложнее поддерживать код. Я не особо убежден, что это такая уж большая проблема. В конце концов, в нормальных проектах схема базы данных вместе со всеми триггерами хранится в том же репозитории, что и код приложения. Ведь кто как не приложение выполняет миграцию схему базы данных ?
Реализация всей логики на стороне приложения тоже имеет право на жизнь. Но платой за такой подход является увеличение сетевого трафика и реализация своих джоинов, группировок, и вот этого всего. Разумеется, такие реализации редко обходятся без багов. По моим представлениям, данный подход оправдан крайне редко. Например, если требуется произвести тяжелые вычисления, и хочется разгрузить СУБД.
Дополнение: Позже я узнал, что описанный в данном посте подход более удобно реализован в расширении TimescaleDB . Нужно однако учитывать, что реализация непрерывных агрегатов в TimescaleDB имеет пару особенностей, о которых более подробно рассказано здесь .
Дополнение: В продолжение темы триггеров вас могут заинтересовать посты Тонкости использования NOTIFY/LISTEN в PostgreSQL и Как сломать ссылочную целостность в PostgreSQL .
