postgresql-triggers/

Тут по работе возникла небольшая задачка с PostgreSQL . Интересна задача тем, что в ней достаточно оправдано использование триггеров. Как показывает опыт, не каждый разработчик знаком со «столь продвинутыми» возможностями постгреса. Поэтому мне показалось, что будет неплохой идеей написать про задачу и ее решение.

Формулировка задачи

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

CREATE TABLE data_raw ( tstamp TIMESTAMP PRIMARY KEY , val INT NOT NULL ) ;

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

Необходимо сделать следующее. Для каждого дня берется три интервала времени — с 00:00 до 07:59, с 08:00 до 15:59 и 16:00 до 23:59. Необходимо посчитать какие-то агрегаты за эти интервалы. В рамках поста мы будем считать среднее. Выборка агрегатов должна производиться как можно быстрее, поскольку предполагается, что к ним будет много запросов на чтение.

Решение в лоб

Неплохая идея при решении любой задачи — найти простейшее решение, а затем, если нужно, подумать над оптимизацией.

Заполним таблицу случайными данными:

INSERT INTO data_raw ( tstamp , val )
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;

… и напишем решение в лоб:

SELECT
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;

Пример результата:

d | avg
———————+———
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 :

CREATE MATERIALIZED VIEW data_view AS
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. Главное — не забывать делать:

REFRESH MATERIALIZED VIEW CONCURRENTLY data_view;

… после записи очередной пачки данных в data_raw.

Такое решение, в общем-то, имеет полное право на жизнь, но не лишено недостатков. Дело в том, что при каждом рефреше materialized view пересчитывается целиком. Со временем, когда data_raw подрастет, это может стать проблемой.

Решение на триггерах

Триггеры позволяют получить такую же materialized view, только умнее. При каждой записи в data_raw триггер будет обновлять ровно одну строчку нашей самодельной вьюхи.

Итак, создадим новую таблицу:

CREATE TABLE data_agg (
tstamp TIMESTAMP PRIMARY KEY ,
total BIGINT NOT NULL ,
num INT NOT NULL
) ;

Напишем небольшой триггер на PL/pgSQL :

CREATE OR REPLACE FUNCTION update_data_agg ( ) RETURNS TRIGGER AS $$
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:

CREATE TRIGGER data_agg_trigger
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, сделаем так:

COPY data_raw TO ‘/tmp/data_raw.dat’ ;
DELETE FROM data_raw;
COPY data_raw FROM ‘/tmp/data_raw.dat’ ;

Проверяем:

SELECT tstamp , total:: FLOAT / num AS avg
FROM data_agg ORDER BY tstamp;

… и видим такой результат:

tstamp | avg
———————+———
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:

CREATE OR REPLACE FUNCTION data_raw_insert_only ( ) RETURNS TRIGGER AS $$
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 .

Похожая запись

PostgreSQL

РЕМОНТКА