Пример использования триггеров в PostgreSQL

22 января 2020

Тут по работе возникла небольшая задачка с 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, СУБД.

Вы можете прислать свой комментарий мне на почту, или воспользоваться комментариями в Telegram-группе.

Записки программиста