Хранение и обработка временных рядов в TimescaleDB

28 апреля 2021

TimescaleDB — это расширение PostgreSQL для работы с временными рядами (time series). Временные ряды можно хранить в PostgreSQL и просто так, но TimescaleDB обеспечивает большую производительность на том же железе. Также расширение предлагает ряд удобных фичей, специфичных для тайм-серий.

Установка

Для установки TimescaleDB на Ubuntu Linux 20.04 LTS необходимо выполнить следующие шаги:

echo "deb http://apt.postgresql.org/pub/repos/apt/ "⏎
"$(lsb_release -c -s)-pgdg main" | \
sudo tee /etc/apt/sources.list.d/pgdg.list

wget -q -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
sudo apt-key add -

sudo apt update

sudo add-apt-repository ppa:timescale/timescaledb-ppa
sudo apt update

sudo apt install timescaledb-2-postgresql-13
sudo timescaledb-tune --quiet --yes

Последняя команда редактирует файл postgresql.conf. Она добавляет динамическую библиотеку timescaledb в shared_preload_libraries, а также меняет параметры по умолчанию на более подходящие для используемого железа. Если вы хотите проконтролировать, что там меняет программа, запустите ее без флагов.

Далее говорим:

sudo service postgresql restart

Остается разве что создать пользователя и базу данных для экспериментов:

$ sudo -u postgres psql
> CREATE DATABASE test_database;
> CREATE USER test_user WITH password 'qwerty';
> GRANT ALL ON DATABASE test_database TO test_user;
> \q

Подключаемся к новой базе данных и ставим расширение:

$ psql -h localhost test_database test_user
> CREATE EXTENSION IF NOT EXISTS timescaledb;

На этом шаге TimescaleDB сообщит, что собирает анонимную статистику о том, как им пользуются. Это можно отключить, дописав в postgresql.conf:

timescaledb.telemetry_level=off

… и снова сделав postgresql restart.

Поздравляю, установка завершена! Информацию по установке TimescaleDB на другие ОС и дистрибутивы Linux можно найти в документации. Инструкция по сборке расширения из исходников доступна на GitHub.

Гипертаблицы

Главная фича TimescaleDB — это гипертаблицы (hypertables). Создание гипертаблицы выглядит, как создание обычной таблицы, за которой следует вызов create_hypertable:

CREATE TABLE conditions(
tstamp timestamptz NOT NULL,
device VARCHAR(32) NOT NULL,
temperature FLOAT NOT NULL);

SELECT create_hypertable(
'conditions', 'tstamp',
chunk_time_interval => INTERVAL '1 day'
);

-- альтернативный вариант, с partitioning_column:
SELECT create_hypertable(
'conditions', 'tstamp',
partitioning_column => 'device',
number_partitions => 8,
chunk_time_interval => INTERVAL '1 day');

Гипертаблица — это таблица, партицированная по заданному столбцу, в данном случае tstamp. То есть, физически таблица будет представлять собой N таблиц, в каждой из которых хранятся данные за заданный интервал времени. Здесь это один день. Если данных много, их можно дополнительно партицировать по хэшу от заданного столбца, как во втором примере. Здесь от значения в столбце device берется хэш, от которого берется остаток от деления на 8. Результат вместе с tstamp определяет партицию (chunk в терминологии TimescaleDB), куда попадут данные.

Fun fact! Столбец tstamp может быть не только timestamptz, но и датой или произвольным целочисленным типом. В частности, он может быть монотонно возрастающим уникальным id.

Партицирование имеет следующие плюсы. Во-первых, каждая партиция имеет собственный индекс(ы). Индекс меньше, чем был бы индекс на одну большую таблицу. Природа временных рядов такова, что данные не удаляются и не изменяются, только дописываются в конец таблицы. За счет этого наиболее свежая партиция хранит «горячие» данные. К ним чаще всего обращаются и только они могут обновляться. Обновлять маленький индекс, который, возможно, даже целиком влезает в память, намного дешевле, чем индекс на одну большую таблицу. Во-вторых, когда мы хотим удалить старые данные, мы просто удаляем старые партиции. Это намного более дешевая операция (по сути, удаление файла), чем удалять отдельные строки в большой таблице. Кроме того, мы сразу избавляемся от проблем с VACUUM, присущих PostgreSQL.

Работа с гипертаблицей происходит так же, как и с любой другой таблицей. Например, так можно заполнить ее случайными данными:

INSERT INTO conditions
SELECT
tstamp, 'device-' || (random()*30)::INT, random()*80 - 40
FROM
generate_series(
NOW() - INTERVAL '90 days',
NOW(),
'1 min'
) AS tstamp;

Посмотреть, на какие партиции нарезана гипертаблица, можно так:

SELECT show_chunks('conditions');

А вот пример запроса на чтение:

SELECT * FROM conditions WHERE tstamp > now() - INTERVAL '30 minutes';

При помощи EXPLAIN можно убедиться, что данный запрос обращается только к одной из партиций.

Помимо INSERT и SELECT также работают любые другие запросы — UPDATE, DELETE, ALTER TABLE, все что угодно. Вы можете использовать произвольные типы, индексы и расширения для PostgreSQL. Например, вы можете положить рядом с временными рядами какие-то геоданные, а затем проиндексировать их при помощи PostGIS.

Основная сложность с гипертаблицами — это правильно определить размер партиции. Рекомендуется выбирать его так, чтобы суммарный размер всех «горячих» партиций составлял не более 25% от доступной оперативной памяти.

Сжатие

«Холодные» партиции не обновляются. По мере старения данных обращение к ним происходит все реже. TimescaleDB умеет переводить такие данные из строкового представления в колоночное, что в свою очередь позволяет произвести эффективное сжатие:

ALTER TABLE conditions SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'device'
);

SELECT add_compression_policy(
'conditions',
compress_after => INTERVAL '1 day');

Здесь мы говорим сжимать партиции, которые старше суток. Сегментирование данных при переводе в колоночное представление осуществляется по столбцу device.

Проверить, насколько хорошо жмутся данные, можно таким запросом:

SELECT * FROM chunk_compression_stats('conditions');

Пример ответа:

В данном случае партиция была сжата в 2.4 раза! Есть, правда, и ограничения. На момент написания этих строк TimescaleDB не умел модифицировать сжатые таблицы. Но когда вы будете читать статью, ситуация может уже поменяться. Текущие возможности и ограничения описаны в документации. Там же более подробно описано, как выбирать segmentby (или orderby) или как можно временно распаковать и модифицировать данные, а затем запаковать обратно.

Непрерывные агрегаты

Непрерывные агрегаты (continuous aggregates) похожи на MATERIALIZED VIEW в PostgreSQL. Только они сами знают, когда себя обновлять, и обновление происходит на основе только тех данных, которые изменились. Раньше мы делали нечто подобное на триггерах. Идея непрерывных агрегатов такая же, только реализация удобнее.

Пример создания непрерывного агрегата:

CREATE MATERIALIZED VIEW conditions_summary_hourly
WITH (timescaledb.continuous) AS
SELECT device,
time_bucket(INTERVAL '1 hour', tstamp) AS bucket,
AVG(temperature),
MAX(temperature),
MIN(temperature)
FROM conditions
GROUP BY device, bucket;

SELECT add_continuous_aggregate_policy('conditions_summary_hourly',
start_offset => INTERVAL '3 hours',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 hour');

По первой части все понятно — мы считаем среднюю, максимальную и минимальную температуру за часовые интервалы. Во второй части мы говорим, как часто необходимо обновлять агрегат. В данном случае он будет обновляться каждый час, используя информацию за последние три часа не считая последнего часа. Для корректной работы агрегатов общий размер временного окна (в данном случае, два часа) должен быть по крайней мере в два раза больше bucket (в данном случае, один час).

Значение start_offset можно указать NULL. Тогда данные в агрегатах будут автоматически подчищаться по мере удаления партиций с данными. См ниже про политики хранения данных. Когда start_offset отличен от NULL, агрегаты живут своей жизнью.

Пример запроса к агрегату:

SELECT bucket, avg
FROM conditions_summary_hourly
WHERE device = 'device-1' AND bucket >= now() - INTERVAL '5 hours'
ORDER BY bucket;

Интересная особенность агрегатов заключается в том, что при чтении из них подмешиваются и «горячие» данные. В этом можно убедиться либо при помощи EXPLAIN, либо дописав в conditions новых данных и повторив предыдущий запрос. Таким образом, наш VIEW всегда актуален.

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

Дополнение: TimescaleDB ≥ 2.8.0 поддерживает бакеты переменной длины. В том числе, есть поддержка часовых поясов.

Политики хранения данных

Политики хранения данных (data retention policies) — это такая фича в TimescaleDB для автоматического удаления старых данных. Например, мы можем сказать, что нам не нужны данные старше 90 дней:

SELECT add_retention_policy('conditions', INTERVAL '90 days');

… а потом передумать:

SELECT remove_retention_policy('conditions');

Политики хранения данных применимы и для непрерывных агрегатов. Как отмечалось в предыдущем разделе, если мы создадим агрегат с start_offset отличным от NULL, данные будут агрегироваться бесконечно. Исправить это можно так:

SELECT add_retention_policy(
'conditions_summary_daily',
drop_after => INTERVAL '120 days');

Подробности ищите в документации.

Заключение

Только что мы рассмотрели основные возможности TimescaleDB. Помимо них также предлагаются специализированные оконные функции, возможность распределять гипертаблицы по нескольким физическим серверам и запускать периодические задачи, как по cron’у. Вы знаете, где искать подробности. UPD: В TimescaleDB 2.2 появилась реализация index skip scan, притом она работает как для гипертаблиц, так и для обычных.

Удобство TimescaleDB примерно такое же, как у полнотекстового поиска в PostgreSQL или PostGIS. Вы храните все в надежной РСУБД без риска, что завтра данные немного разъедутся или что вам придется вручную писать LEFT OUTER JOIN на языке Go. Вы используете всем знакомый SQL и проверенные драйверы. Вместо N разных систем вы используете одну, которую понятно как настраивать, бэкапить, мониторить, и где искать людей, умеющих с ней работать. И так далее, и тому подобное.

TimescaleDB распространяется под свободной лицензией, основное ограничение которой заключается в том, что вы не можете делать TimescaleDB-as-a-Service. Это связано с тем, что такой сервис предоставляет компания Timescale. Если вы не хотите платить за сервис, то можете поднять TimescaleDB на своем железе или в облаке совершенно бесплатно.

Дополнение: Также вас могут заинтересовать статьи Тонкости реализации непрерывных агрегатов в TimescaleDB и Практический пример использования pgvector.

Метки: Linux, PostgreSQL, СУБД.

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

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