Использование timescaledb

Евгения Кузина

Обзор

Расширение timescaledb предоставляет дополнительные возможности для обработки данных временных рядов. Оно позволяет выполнять масштабируемые вставки и сложные запросы, а также повышает производительность запросов над данными временных рядов.

Пакет, требуемый для установки timescaledb, поставляется с ADPG. Чтобы использовать timescaledb, необходимо только выполнить команду CREATE EXTENSION в той базе данных, в которую необходимо поставить расширение:

CREATE EXTENSION timescaledb;
ПРИМЕЧАНИЕ
Если расширение timescaledb создано в базе данных template1, используемой в качестве темплейта по умолчанию, во всех вновь создаваемых базах данных будет установлено это расширение.

ADPG использует версию timescaledb 2.14.2. Чтобы это проверить, можно выполнить следующий запрос:

SELECT extversion FROM pg_extension
    WHERE extname = 'timescaledb';

Результат:

 extversion
------------
 2.14.2

Расширение timescaledb добавляет в базу данных ряд функций, представлений и таблиц. За дополнительной информации об API timescaledb обратитесь к статье TimescaleDB API reference.

Вы можете использовать команду psql \dn для просмотра списка схем, созданных timescaledb.

Результат:

           List of schemas
           Name           |  Owner
--------------------------+----------
 _timescaledb_cache       | postgres
 _timescaledb_catalog     | postgres
 _timescaledb_config      | postgres
 _timescaledb_debug       | postgres
 _timescaledb_functions   | postgres
 _timescaledb_internal    | postgres
 public                   | postgres
 timescaledb_experimental | postgres
 timescaledb_information  | postgres

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

Расширение timescaledb предоставляет гипертаблицы, которые являются абстракцией высокого уровня, расширяющей традиционные таблицы PostgreSQL для более эффективной работы с временными данными. Любую операцию, которая производится с обычными таблицами PostgreSQL, можно выполнить с гипертаблицами. Гипертаблица упрощает организацию и извлечение информации о временных рядах, предоставляя встроенное секционирование на основе временных интервалов. timescaledb разбивает гипертаблицы на фрагменты (chunks).

Создание пустой гипертаблицы

Чтобы сгенерировать гипертаблицу, необходимо создать обычную таблицу и передать её в качестве параметра функции create_hypertable:

CREATE TABLE base_table(
    id uuid,
    time_column timestamptz not null default clock_timestamp(),
    data_column1 int default random()*1E5,
    data_column2 int default random()*1E5
);

SELECT create_hypertable('base_table', by_range('time_column'));

Результат:

create_hypertable
-------------------
 (7,t)

Число из вывода (в данном случае 7) будет использоваться далее в именах фрагментов, принадлежащих этой гипертаблице.

Функция create_hypertable имеет два обязательных параметра:

  • relation — идентификатор таблицы для преобразования в гипертаблицу. Параметр relation должен быть типа regclass ('base_table' в примере выше).

  • dimension — должен быть типа dimension_info. Для создания экземпляра dimension_info необходимо вызвать функцию by_range или by_hash. Этот параметр определяет условия, в соответствии с которыми таблица будет разделена на фрагменты (by_range('time_column') в примере выше).

Заполните гипертаблицу данными:

INSERT INTO base_table(id, time_column) SELECT gen_random_uuid(), generate_series(
now() - INTERVAL '6 months',
now(),
INTERVAL '1 minute'
);

Используйте метакоманду psql \d+ для отображения информации о гипертаблице base_table:

\d+ base_table

Результат:

    Column    |           Type           | Collation | Nullable |                     Default                      | Storage | Compression | Stats target | Description
--------------+--------------------------+-----------+----------+--------------------------------------------------+---------+-------------+--------------+-------------
 id           | uuid                     |           |          |                                                  | plain   |             |              |
 time_column  | timestamp with time zone |           | not null | clock_timestamp()                                | plain   |             |              |
 data_column1 | integer                  |           |          | (random() * '100000'::numeric::double precision) | plain   |             |              |
 data_column2 | integer                  |           |          | (random() * '100000'::numeric::double precision) | plain   |             |              |
Indexes:
    "base_table_time_column_idx" btree (time_column DESC)
Triggers:
    ts_insert_blocker BEFORE INSERT ON base_table FOR EACH ROW EXECUTE FUNCTION _timescaledb_functions.insert_blocker()
Child tables: _timescaledb_internal._hyper_7_57_chunk,
              _timescaledb_internal._hyper_7_58_chunk,
              _timescaledb_internal._hyper_7_59_chunk,
              _timescaledb_internal._hyper_7_60_chunk,
              _timescaledb_internal._hyper_7_61_chunk,
              _timescaledb_internal._hyper_7_62_chunk,
              _timescaledb_internal._hyper_7_63_chunk,
              _timescaledb_internal._hyper_7_64_chunk,
              _timescaledb_internal._hyper_7_65_chunk,
              _timescaledb_internal._hyper_7_66_chunk,
              _timescaledb_internal._hyper_7_67_chunk,
              _timescaledb_internal._hyper_7_68_chunk,
              _timescaledb_internal._hyper_7_69_chunk,
              _timescaledb_internal._hyper_7_70_chunk,
              _timescaledb_internal._hyper_7_71_chunk,
              _timescaledb_internal._hyper_7_72_chunk,
              _timescaledb_internal._hyper_7_73_chunk,
              _timescaledb_internal._hyper_7_74_chunk,
              _timescaledb_internal._hyper_7_75_chunk,
              _timescaledb_internal._hyper_7_76_chunk,
              _timescaledb_internal._hyper_7_77_chunk,
              _timescaledb_internal._hyper_7_78_chunk,
              _timescaledb_internal._hyper_7_79_chunk,
              _timescaledb_internal._hyper_7_80_chunk,
              _timescaledb_internal._hyper_7_81_chunk,
              _timescaledb_internal._hyper_7_82_chunk,
              _timescaledb_internal._hyper_7_83_chunk,
              _timescaledb_internal._hyper_7_84_chunk
Access method: heap

В выводе вы можете увидеть, что для base_table создано 28 фрагментов.

Также можно указать интервал для фрагментов. Для этого передайте интервал в качестве параметра в функцию by_range. Его можно задать в миллисекундах:

CREATE TABLE table1 (LIKE base_table);
SELECT create_hypertable('table1', by_range('time_column', 86400000000));

А также можно использовать ключевое слово INTERVAL:

CREATE TABLE table2 (LIKE base_table);
SELECT create_hypertable('table2', by_range('time_column', INTERVAL '1 day'));

При создании гипертаблицы timescaledb генерирует индексы по столбцам секционирования. Параметр create_default_indexes, управляющий этим поведением, является необязательным параметром, его значение по умолчанию — true. Вы можете отменить создание индексов, установив его значение равным false:

CREATE TABLE table3 (LIKE base_table);
SELECT create_hypertable('table3', by_range('time_column'), create_default_indexes=> false);

Если вы попытаетесь вызвать функцию create_hypertable для таблицы, которая уже является гипертаблицей, возникнет ошибка: ERROR: table "base_table" is already a hypertable. Вы можете установить необязательный параметр if_not_exists в true, чтобы вывести предупреждение вместо генерации ошибки:

SELECT create_hypertable('base_table', by_range('time_column'), if_not_exists=> true);

Результат:

NOTICE:  table "base_table" is already a hypertable, skipping
 create_hypertable
-------------------
 (7,f)
(1 row)

Преобразование таблицы с данными в гипертаблицу

Чтобы создать гипертаблицу из таблицы с данными, укажите необязательный параметр — migrate_data. Код ниже создает новую таблицу и заполняет её данными:

CREATE FUNCTION return_location() RETURNS text AS $$
BEGIN
    RETURN 'location ' || floor(random() * 5+1 ):: text;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE "conditions"(
    time         TIMESTAMP WITH TIME ZONE NOT NULL,
    location    TEXT DEFAULT return_location(),
    temperature  NUMERIC,
    humidity     NUMERIC
);

INSERT INTO conditions(time, temperature, humidity) SELECT generate_series(
    now() - INTERVAL '6 months',
    now(),
    INTERVAL '1 minute'
    )as time,
    round(random()*100) as temperature,
    round(random()*100) as humidity;

Преобразуйте conditions в гипертаблицу, передав параметр migrate_data:

SELECT create_hypertable('conditions', 'time', migrate_data => true);

Результат:

NOTICE:  migrating data to chunks
DETAIL:  Migration might take a while depending on the amount of data.
    create_hypertable
--------------------------
 (14,public,conditions,t)

Если таблица для преобразования содержит данные, но параметр migrate_data не установлен или равен false, возникнет ошибка:

ERROR:  table "conditions" is not empty
HINT:  You can migrate data by specifying 'migrate_data => true' when calling this function.

Компрессия

Расширение timescaledb позволяет сжимать данные временных рядов и значительно уменьшать размер фрагмента. При включении компрессии данные в гипертаблице сжимаются фрагмент за фрагментом. При сжатии фрагмента несколько записей объединяются в одну строку. Это уменьшает объем необходимого дискового пространства, поскольку одна строка занимает меньше места, чем несколько строк. Для получения дополнительной информации об этом функционале см. About compression.

Чтобы включить компрессию, выполните следующие действия.

  1. Используйте команду ALTER TABLE, чтобы включить компрессию и указать её параметры:

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

  2. Вызовите функцию add_compression_policy, чтобы добавить политику компрессии. Следующий код сжимает фрагменты, которые старше 1 часа:

    SELECT add_compression_policy('conditions', INTERVAL '1 hour');

Также существует возможность распаковать определенный фрагмент с помощью функции decompress_chunk, например, чтобы максимизировать производительность, если предстоит выполнить большое количество различных операций DML. Можно использовать представление timescaledb_information.chunks, чтобы определить имя фрагмента и передать его в качестве параметра decompress_chunk.

SELECT decompress_chunk('_timescaledb_internal._hyper_14_376_chunk');

Используйте функцию remove_compression_policy для удаления компрессии:

SELECT remove_compression_policy('conditions');

Использование гиперфункций timescaledb для анализа данных

Расширение timescaledb предоставляет гиперфункции для анализа данных. В этом разделе рассматриваются некоторые из них. Примеры в этом разделе используют таблицу conditions, создание которой описано выше.

first и last

Функции first и last позволяют получить первое или последнее значение одного столбца среди значений, упорядоченных по другому столбцу. Следующий пример возвращает самое раннее значение температуры на основе времени из столбца time в каждой группе значений, указанных в столбце location:

SELECT location, first(temperature, time) FROM conditions
    GROUP BY location ORDER BY location;

Результат:

  location  | first
------------+-------
 location 1 |    29
 location 2 |    30
 location 3 |    43
 location 4 |    87
 location 5 |    95

Приведенный ниже код возвращает последнее значение влажности на основе времени из столбца time в каждой группе значений, указанных в столбце location:

SELECT location, last(humidity, time) FROM conditions
    GROUP BY location ORDER BY location;

Результат:

  location  | last
------------+------
 location 1 |   15
 location 2 |   69
 location 3 |   47
 location 4 |   98
 location 5 |   69

time_bucket

Функция time_bucket позволяет объединять данные в интервалы времени (time buckets).

Следующий запрос вычисляет среднюю температуру за день для определенного местоположения:

SELECT time_bucket('1 day', time) AS bucket_time, AVG(temperature) AS avg_temperature
    FROM conditions WHERE location = 'location 3'
    GROUP BY bucket_time ORDER BY bucket_time
    LIMIT 5;

Результат:

      bucket_time       |   avg_temperature
------------------------+---------------------
 2024-09-02 00:00:00+00 | 49.0534759358288770
 2024-09-03 00:00:00+00 | 49.6323529411764706
 2024-09-04 00:00:00+00 | 49.0774410774410774
 2024-09-05 00:00:00+00 | 47.7973856209150327
 2024-09-06 00:00:00+00 | 51.7582417582417582

histogram

Функция histogram вычисляет, сколько раз значение попадает в указанный диапазон. Например, запрос ниже разбивает диапазон влажности 0-100 на 10 диапазонов и возвращает, сколько раз значение влажности попадает в каждый диапазон для каждого местоположения.

SELECT location, histogram(humidity, 0, 100, 10)
    FROM conditions GROUP BY location ORDER BY location;

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

  location  |                         histogram
------------+-----------------------------------------------------------
 location 1 | {0,4903,5316,5124,5226,5200,5219,5386,5173,5206,5172,278}
 location 2 | {0,4830,5338,5343,5150,5222,5189,5154,5333,5230,5275,267}
 location 3 | {0,4949,5264,5217,5216,5194,5278,5077,5214,5190,5246,268}
 location 4 | {0,4922,5075,5192,5134,5329,5083,5349,5302,5128,5262,277}
 location 5 | {0,4892,5224,5104,5290,5050,5291,5179,5140,5191,5320,260}

Значения, равные минимальному значению диапазона, включаются в него, а значения, равные максимальному значению, относятся к следующему диапазону. Тестовые данные примера содержат 1350 значений влажности, которые равны 100. В результате последний (двенадцатый) диапазон содержит это значение, распределенное по местоположениям (location 1 — 278, location 2 — 267, location 3 — 268, location 4 — 277, location 1 — 260).

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней