Функции для управления гипертаблицами

Функции и представления представленные в данном разделе доступны в рамках поставляемого расширения TimescaleDB.

Функции

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

add_dimension()

Добавляет дополнительные измерения партиционирования в гипертаблицу TimescaleDB. Столбец, выбранный в качестве измерения, может использовать либо интервальное партиционирование (например, для второго временного партиционирования), либо партиционирование по хешу.

Внимание!

Команда add_dimension может быть выполнена только после того, как таблица была сконвертирована в гипертаблицу (с помощью команды create_hypertable), и только на пустой гипертаблице.

Пространственные разделы

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

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

Так как гибкие временные интервалы для управления размерами блоков уже используются, основная цель партиционирования по пространству – обеспечить распараллеливание между несколькими узлами данных (в случае распределенных гипертаблиц) или между несколькими дисками в течение одного и того же временного интервала (в случае развертываний на одном узле).

Распараллеливание запросов на несколько узлов данных

В распределенной гипертаблице партиционирование пространства позволяет распараллеливать вставки между узлами данных, даже если добавляемые строки имеют общие временные метки из одного и того же интервала времени. Таким образом, увеличивается скорость приема данных. Производительность также выигрывает при параллелизации выполнения запросов между узлами, особенно когда полные или частичные агрегации могут быть «вытеснены» на узлы данных (например, как в запросе на вычисление средней температуры: FROM conditions GROUP BY hour, location (где location - пространственная партиция)).

Распараллеливание чтения и записи на диск одного узла

Параллельный ввод-вывод может быть полезен в двух сценариях:

• два или более параллельных запроса должны иметь возможность параллельного чтения с разных дисков;
• один запрос должен иметь возможность параллельного чтения с нескольких дисков.

Таким образом, у пользователей, которым нужен параллельный ввод-вывод, есть два варианта:

• использовать конфигурацию RAID на нескольких физических дисках и предоставить гипертаблице доступ к одному логическому диску — через одно табличное пространство;
• создать отдельное табличное пространство в базе данных для каждого физического диска. TimescaleDB позволяет добавить несколько табличных пространств в одну гипертаблицу (хотя на самом деле блоки гипертаблицы разбросаны по табличным пространствам, связанным с этой гипертаблицей).

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

Тем не менее при использовании пространственных разделов рекомендуется использовать один пространственный раздел на диск. TimescaleDB не получает преимуществ от очень большого количества пространственных разделов (например, от количества уникальных элементов поля раздела). Большое количество таких разделов приводит как к более низкой балансировке нагрузки на раздел (при сопоставлении элементов с разделами применяется хеширование), так и к значительно увеличенной задержке планирования для некоторых типов запросов.

Синтаксис:

add_dimension(hypertable regclass, column_name name, number_partitions integer)
 RETURNS TABLE(dimension_id integer, schema_name name, table_name name, column_name name, created boolean)

Входные параметры:

Название параметра	Тип значения	Описание
hypertable	regclass	Гипертаблица, в которую требуется добавить измерение
tablespace	name	Имя табличного пространства, которое требуется присоединить
column_name	name	Столбец измерения

Дополнительные параметры:

Название параметра	Тип значения	Описание
number_partitions	integer	Количество хеш-разделов для column_name. Должно быть больше нуля
chunk_time_interval	anyelement	Интервал одного блока. Должен быть больше нуля
partitioning_func	regproc	Функция вычисления раздела для значения (подробнее смотрите инструкцию для create_hypertable)
if_not_exists	boolean	В случае true не выводить ошибку, если для этого столбца уже существует измерение. Вместо этого выводить уведомление. По умолчанию установлено значение false

Возвращаемые значения:

Название поля	Тип значения	Описание
dimension_id	integer	ID измерения во внутреннем каталоге «TimescaleDB catalog»
schema_name	name	Имя схемы гипертаблицы
table_name	name	Имя гипертаблицы
column_name	name	Имя столбца, по которому выполняется партиционирование
created	boolean	Если измерение было добавлено – значение true, если if_not_exists = true и измерение не было добавлено – false

Заметки об использовании:

При выполнении этой функции необходимо указать либо number_partitions, либо chunk_time_interval, чтобы показать, будет ли измерение использовать хеш или интервальное партиционирование.

chunk_time_interval должен быть указан следующим образом:

• если партиционируемый столбец - TIMESTAMP, TIMESTAMPTZ или DATE, то интервал должен быть указан либо как тип INTERVAL, либо как целочисленное значение в микросекундах;
• если столбец имеет какой-либо другой целочисленный тип, то интервал должен быть целым числом, отражающим лежащую в основе столбца семантику (например, chunk_time_interval должен быть задан в миллисекундах, если этот столбец является числом миллисекунд с эпохи UNIX).

Внимание!

Поддержка более чем одного дополнительного измерения в настоящее время находится в экспериментальной стадии. Для производственных задач пользователям рекомендуется использовать только одно и именно пространственное измерение.

Пример использования:

Преобразование таблицы conditions в гипертаблицу с помощью только временного партиционирования по столбцу time, а затем добавление дополнительного ключа партиционирования по location с четырьмя разделами:

SELECT create_hypertable('conditions', 'time');

SELECT add_dimension('conditions', 'location', number_partitions => 4);

Преобразование таблицы conditions в гипертаблицу с временным партиционированием по time и пространственным партиционированием (2 раздела) по location, а затем добавление двух дополнительных измерений:

SELECT create_hypertable('conditions', 'time', 'location', 2);

SELECT add_dimension('conditions', 'time_received', chunk_time_interval => INTERVAL '1 day');

SELECT add_dimension('conditions', 'device_id', number_partitions => 2);

SELECT add_dimension('conditions', 'device_id', number_partitions => 2, if_not_exists => true);

В конфигурации для распределенных гипертаблиц с кластером из одного узла доступа и двух узлов данных настройка узла доступа для обращения к двум узлам данных. Затем преобразование таблицы conditions в распределенную гипертаблицу только с временным партиционированием по столбцу time и добавление измерения пространственного партиционирования по location с двумя разделами (по числу подключенных узлов данных):

SELECT add_data_node('dn1', host => 'dn1.example.com');

SELECT add_data_node('dn2', host => 'dn2.example.com');

SELECT create_distributed_hypertable('conditions', 'time');

SELECT add_dimension('conditions', 'location', number_partitions => 2);

attach_tablespace()

Присоединяет табличное пространство (ТП) к гипертаблице и использует его для хранения блоков.

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

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

Синтаксис:

attach_tablespace(tablespace name, hypertable regclass, if_not_attached boolean DEFAULT false)
 RETURNS void

Входные параметры:

Название параметра	Тип значения	Описание
tablespace	name	Имя табличного пространства, которое требуется присоединить
hypertable	regclass	Гипертаблица, к которой требуется присоединить табличное пространство
if_not_attached	boolean	По умолчанию установлено значение false. В случае true не выводить ошибку, если указанное табличное пространство уже присоединено к этой таблице. Вместо этого выводиться уведомление

Перед прикреплением к гипертаблице табличные пространства должны быть созданы. После создания табличные пространства могут быть присоединены к нескольким гипертаблицам одновременно, чтобы совместно использовать дисковое хранилище. Связывание обычной таблицы с табличным пространством с помощью опции TABLESPACE для CREATE TABLE и последующий вызов create_hypertable будет иметь тот же эффект, что и вызов attach_tablespace сразу после create_hypertable.

Возвращаемое значение:

Отсутствует. В случае успешного выполнения функция вернет пустое значение.

Пример использования:

Прикрепить табличное пространство disk1 к гипертаблице conditions:

SELECT attach_tablespace('disk1', 'conditions');
SELECT attach_tablespace('disk2', 'conditions', if_not_attached => true);

Внимание!

Управление табличными пространствами на гипертаблицах в настоящее время является экспериментальной функцией.

create_hypertable()

Создает гипертаблицу TimescaleDB из таблицы PostgreSQL (заменяя последнюю), партиционированную по времени, и с возможностью партиционирования по одному или нескольким другим столбцам (например, по пространственному столбцу). Все действия, такие как ALTER TABLE, SELECT, по-прежнему будут работать на результирующей гипертаблице.

Синтаксис:

create_hypertable(relation regclass, time_column_name name)
  RETURNS TABLE(hypertable_id integer, schema_name name, table_name name, created boolean)

Входные параметры:

Название параметра	Тип значения	Описание
relation	regclass	Идентификатор таблицы, которую требуется конвертировать
time_column_name	name	Имя столбца, содержащего значения времени – первичного столбца партиционирования

Дополнительные параметры:

Название параметра	Тип значения	Описание
partitioning_column	name	Название дополнительного столбца партиционирования. Если указано, то также должен быть указан аргумент number_partitions
number_partitions	integer	Количество хеш-разделов для partitioning_column. Должно быть больше нуля
chunk_time_interval	anyelement	Интервал во времени события, который охватывает каждый блок. Должен быть больше нуля
create_default_indexes	boolean	Создавать ли индексы по умолчанию для столбцов времени и партиционирования. Значение по умолчанию – true
if_not_exists	boolean	Выводить ли предупреждение вместо исключения, если таблица уже преобразована в гипертаблицу. Значение по умолчанию – false
partitioning_func	regproc	Функция вычисления партиции для значения
associated_schema_name	name	Название схемы для внутренних таблиц гипертаблицы. По умолчанию используется _timescaledb_internal
associated_table_prefix	name	Префикс для внутренних имен блоков гипертаблицы. По умолчанию используется _hyper
migrate_data	boolean	Если true, то будет выполнена миграция существующих данных из реляционной таблицы в блоки новой гипертаблицы. Непустая таблица будет возвращать ошибку при попытке преобразования без этой опции. Перенос больших таблиц может занять значительное время. По умолчанию установлено значение false
time_partitioning_func	regproc	Функция для преобразования несовместимых значений первичного столбца времени в совместимые. Функция должна быть IMMUTABLE
replication_factor	integer	Если 1 или больше, будет создана распределенная гипертаблица. Значение по умолчанию - NULL. При создании распределенной гипертаблицы может быть удобнее использовать create_distributed_hypertable вместо create_hypertable
data_nodes	name	Набор узлов данных, которые будут использоваться для данной таблицы, если включено распределение. Не влияет на нераспределенные таблицы. Если узлы данных не указаны, распределенная гипертаблица будет использовать все узлы данных, известные этому экземпляру

Возвращаемые значения:

Название поля	Тип значения	Описание
hypertable_id	integer	ID гипертаблицы в TimescaleDB
schema_name	name	Название схемы таблицы, преобразованной в гипертаблицу
table_name	name	Название таблицы, преобразованной в гипертаблицу
created	boolean	Если измерение было добавлено – значение true, если if_not_exists = true и измерение не было добавлено – false

примечание

Команда SELECT * FROM create_hypertable(...) выведет результат в виде таблицы с заголовками столбцов.

Заметки об использовании:

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

Для более тонкой настройки формирования индекса и других аспектов гипертаблицы следуйте этим инструкциям по миграции.

В ходе преобразования обычной SQL-таблицы в гипертаблицу уделяйте особое внимание ограничениям. Гипертаблица может содержать внешние ключи для столбцов обычных таблиц SQL, но обратное не допускается. Ограничения UNIQUE и PRIMARY должны включать ключ партиционирования.

Когда параллельные транзакции одновременно попытаются вставить данные в таблицы, на которые ссылаются ограничения по внешнему ключу, и в саму преобразуемую таблицу, это, скорее всего, приведет к взаимной блокировке. Чтобы не допустить взаимной блокировки, можно вручную включить блокировку SHARE ROW EXCLUSIVE для таблиц, на которые ссылается преобразуемая таблица, а потом вызвать create_hypertable в той же транзакции. Соответствующий синтаксис приведен в документации PostgreSQL.

Типы данных:

Столбец time (время) поддерживает следующие типы данных: Timestamp (TIMESTAMP, TIMESTAMPTZ), DATE, Integer (SMALLINT, INT, BIGINT).

примечание

Разнообразие поддерживаемых типов в столбце time позволяет использовать значения, не основанные на времени, в качестве основного столбца партиционирования блока. Достаточно, чтобы эти значения поддерживали инкрементацию.

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

Величины chunk_time_interval должны быть выбраны следующим образом:

• для столбцов времени, имеющих типы TIMESTAMP или DATE, chunk_time_interval должен быть указан либо как величина типа interval, либо как целочисленное значение в микросекундах;
• для целочисленных типов значение chunk_time_interval должно быть задано явно, так как база данных иначе не имеет информацию о том, что представляет собой каждое целочисленное значение (секунда, миллисекунда, наносекунда и т. д.). Если столбец времени – это число миллисекунд с начала эпохи UNIX, и необходимо, чтобы каждый блок занимал 1 день, следует указать chunk_time_interval => 86400000.

В случае хеш-партиционирования (когда number_partitions больше нуля) можно дополнительно указать пользовательскую функцию партиционирования. Если пользовательская функция партиционирования не указана, используется функция партиционирования по умолчанию. Функция партиционирования по умолчанию вызывает внутреннюю хеш-функцию PostgreSQL для данного типа, если таковая существует. Таким образом, пользовательская функция партиционирования может использоваться для типов значений, которые не имеют собственной хеш-функции PostgreSQL. Функция партиционирования должна принимать один аргумент типа anyelement и возвращать хеш-значение в виде положительного целого числа. Обратите внимание, что это хеш-значение не является идентификатором раздела, а скорее позицией вставленного значения в пространстве ключей измерения, которое затем делится между разделами.

примечание

Столбец времени в increate_hypertable должен быть определен как NOT NULL. create_hypertable автоматически добавит это ограничение в таблицу, если оно не было указано при создании.

Примеры использования:

Преобразовать таблицу conditions в гипертаблицу с партиционированием только по времени (по столбцу time):

SELECT create_hypertable('conditions', 'time');

Преобразовать таблицу conditions в гипертаблицу, установить chunk_time_interval, равным 24 часам:

SELECT create_hypertable('conditions', 'time', chunk_time_interval => 86400000000);

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

Преобразовать таблицу conditions в гипертаблицу с партиционированием по времени по столбцу time и пространственным партиционированием (4 раздела) по location:

SELECT create_hypertable('conditions', 'time', 'location', 4);

То же самое, что и выше, но с использованием пользовательской функции партиционирования:

SELECT create_hypertable('conditions', 'time', 'location', 4, partitioning_func => 'location_hash');

Преобразовать таблицу conditions в гипертаблицу. Не возвращать предупреждение, если conditions уже являются гипертаблицей:

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

Выполнить партиционирование таблицы measurements по столбцу времени составного типа report с использованием функции временного партиционирования. Требуется неизменяемая функция, которая может преобразовать неподдерживаемое значение столбца в поддерживаемое.

CREATE TYPE report AS (reported timestamp with time zone, contents jsonb);

CREATE FUNCTION report_reported(report)
RETURNS timestamptz
LANGUAGE SQL
IMMUTABLE AS
'SELECT $1.reported';

SELECT create_hypertable('measurements', 'report', time_partitioning_func => 'report_reported');

Выполнить партиционирование таблицы events по времени на столбце типа jsonb (event), который является ключом верхнего уровня (started) и содержит временные отметки в формате ISO 8601:

CREATE FUNCTION event_started(jsonb)
RETURNS timestamptz
LANGUAGE SQL
IMMUTABLE AS
$func$SELECT ($1
->>
'started')::timestamptz$func$;

SELECT create_hypertable('events', 'event', time_partitioning_func => 'event_started');

Рекомендации:

Один из наиболее распространенных вопросов пользователей TimescaleDB связан с настройкой chunk_time_interval.

Временные интервалы:

Текущая версия TimescaleDB поддерживает как ручную, так и автоматическую адаптацию временных интервалов. С интервалами, заданными вручную, рекомендуется указать chunk_time_interval при создании гипертаблицы (значение по умолчанию - 1 неделя). Интервал, используемый для новых блоков, может быть изменен командой set_chunk_time_interval().

Ключевым свойством выбора временного интервала является то, что блок (включая индексы), принадлежащий самому последнему интервалу (или блоки, если используются пространственные разделы), должен помещаться в память. Таким образом, обычно рекомендуется установить интервал так, чтобы эти блоки занимали не более 25% объема основной памяти.

примечание

На этапе планирования убедитесь, что последние фрагменты из всех активных гипертаблиц будут помещаться в 25% основной памяти, а не по 25% на каждую гипертаблицу.

Для этого необходимо примерно понимать скорость поступления данных в систему. Например, если ежедневно происходит запись 2 ГБ данных при запасе памяти в объеме 64 ГБ, то временной интервал в неделю подойдет. Если пишется 10 ГБ в день на этой же машине, то было бы уместно установить временной интервал в один день. Кроме этого, интервал подойдет и при загрузке данных большими партиями, например, в случае массовой загрузки 70 ГБ данных раз в неделю, причем данные соответствуют записям в течение всей недели.

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

примечание

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

Пространственные разделы:

В большинстве случаев пользователям не рекомендуется использовать пространственные разделы.

Однако при создании распределенной гипертаблицы важно создать пространственное партиционирование. Редкие случаи, в которых пространственные разделы могут быть полезны для нераспределенных гипертаблиц, описаны в разделе add_dimension() этого документа.

CREATE INDEX()

CREATE INDEX ... WITH (timescaledb.transaction_per_chunk, ...);

Эта опция добавляет в CREATE INDEX возможность использования отдельной транзакции для каждого блока, в котором создается индекс, вместо того чтобы выполнять всю операцию в рамках одной транзакции для всей гипертаблицы. Это позволяет выполнять операции, которые должны производиться одновременно, в течение всего срока выполнения команды CREATE INDEX. Когда индекс создается на отдельном блоке, все происходит так, как если бы на этом блоке был вызван обычный CREATE INDEX, и другие блоки при этом не блокируются.

примечание

Эта версия CREATE INDEX может использоваться в качестве альтернативы команде CREATE INDEX CONCURRENTLY, которая в настоящее время для гипертаблиц не поддерживается.

Внимание!

Если операция завершится неудачно, индексы могут быть созданы не на всех блоках гипертаблицы. В этом случае индекс корневой таблицы гипертаблицы будет помечен как некорректный (это можно увидеть, выполнив \d+ на гипертаблице). Индекс все равно будет работать и будет создаваться на новых блоках. Если необходимо, чтобы все блоки точно были проиндексированы, удалите индекс и создайте его заново.

Пример использования:

Анонимный индекс:

CREATE INDEX ON conditions(time, device_id) WITH (timescaledb.transaction_per_chunk);

Другие методы индексации:

CREATE INDEX ON conditions(time, location) USING brin WITH (timescaledb.transaction_per_chunk);

detach_tablespace()

Отсоединить табличное пространство от одной или нескольких гипертаблиц. Это означает только то, что новые блоки больше не будут помещаться в отделенное табличное пространство. Это полезно, например, когда для табличного пространства мало места на диске и нужно предотвратить создание новых блоков в пространстве. Само отсоединенное табличное пространство и любые существующие фрагменты с данными на нем останутся неизменными и продолжат работать по-прежнему, в том числе будут доступны для запросов. Обратите внимание, что вновь вставленные строки данных все еще могут быть вставлены в существующий блок в отсоединенном табличном пространстве, поскольку существующие данные из отсоединенного табличного пространства не удаляются. Отсоединенное табличное пространство может быть присоединено обратно, чтобы продолжить размещать в нем блоки.

Синтаксис:

detach_tablespace(tablespace name, hypertable regclass DEFAULT NULL::regclass, if_attached boolean DEFAULT false)
 RETURNS integer

Входные параметры:

Название параметра	Тип значения	Описание
tablespace	name	Имя отделяемого табличного пространства

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

Дополнительные параметры:

Название параметра	Тип значения	Описание
hypertable	regclass	Гипертаблица, от которой требуется отсоединить табличное пространство
if_attached	boolean	В случае true, не выводить ошибку, если данное табличное пространство не присоединено к указанной таблице. Вместо этого выводить уведомление. По умолчанию установлено значение false

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

Возвращаемые значения:

Количество гипертаблиц, от которых табличное пространство было успешно отсоединено. Если табличное пространство не было присоединено ни к одной из доступных гипертаблиц, возвращается 0.

Примеры использования:

Отсоединить табличное пространство disk1 от гипертаблицы conditions:

SELECT detach_tablespace('disk1', 'conditions');SELECT detach_tablespace('disk2', 'conditions', if_attached => true);

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

SELECT detach_tablespace('disk1');

detach_tablespaces()

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

Синтаксис:

detach_tablespaces(hypertable regclass)
 RETURNS integer

Входные параметры:

Название параметра	Тип значения	Описание
hypertable	regclass	Гипертаблица, от которой требуется отсоединить табличные пространства

Возвращаемые значения:

Количество гипертаблиц, от которых табличное пространство было успешно отсоединено. Если табличное пространство не было присоединено ни к одной из доступных гипертаблиц, возвращается 0.

Пример использования:

Отсоединить все табличные пространства от гипертаблицы conditions:

SELECT detach_tablespaces('conditions');

drop_chunks()

Удаляет блоки данных (чанки), временной диапазон которых полностью находится до (или после) указанного времени. Показывает список блоков, которые были удалены, так же как и show_chunks.

Блоки ограничены временем начала и окончания, а время начала всегда предшествует времени окончания. Блок удаляется, если его время окончания старше временной метки older_than или его начальное время новее временной метки newer_than, если задано newer_than.

Внимание!

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

Синтаксис:

drop_chunks(relation regclass, older_than "any" DEFAULT NULL::unknown, newer_than "any" DEFAULT NULL::unknown, "verbose" boolean DEFAULT false, created_before "any" DEFAULT NULL::unknown, created_after "any" DEFAULT NULL::unknown)
 RETURNS SETOF text

Входные параметры:

Название параметра	Тип значения	Описание
relation	regclass	Гипертаблица или непрерывный агрегат, из которого следует удалить блоки
older_than		Точка отсчета во времени, все блоки старше которой следует удалить

Дополнительные параметры:

Название параметра	Тип значения	Описание
newer_than		Точка во времени, блоки новее которой следует удалить
verbose	boolean	Если true, выводить сообщения о прогрессе выполнения команды. По умолчанию установлено значение false
created_before		Удалить только те блоки, которые были созданы до указанной временной метки
created_after		Удалить только те блоки, которые были созданы после указанной временной метки

Параметры older_than и newer_than могут быть заданы двумя способами:

• INTERVAL: точка отсечения вычисляется как now () - older_than и аналогично now () - newer_than. Если указан INTERVAL и столбец времени не является TIMESTAMP, TIMESTAMPTZ или DATE, команда завершится с ошибкой;
• TIMESTAMP, DATE или INTEGER: точка отсечения явно задается как TIMESTAMP / TIMESTAMPTZ / DATE или как SMALLINT / INT / BIGINT. Выбранный вариант должен соответствовать типу столбца времени гипертаблицы.

Внимание!

При использовании только INTERVAL функция предполагает, что удаляется что-то из прошлого. Если необходимо удалить данные в будущем (например, ошибочные записи), используйте TIMESTAMP.

Когда используются оба аргумента, функция возвращает пересечение результирующих двух диапазонов. Например, newer_than => 4 months и older_than => 3 months удалит все полные блоки в промежутке от 3 до 4 месяцев назад. Аналогично, newer_than => '2017-01-01' и older_than => '2017-02-01' удалит все блоки между '2017-01-01' и '2017-02-01'. Указание параметров, которые не приводят к перекрывающемуся пересечению между двумя диапазонами, приведет к ошибке.

Возвращаемые значения:

Список имен удаленных блоков (chunks). Каждая строка содержит имя блока, который был успешно удален. Если ни один блок не был удален, возвращается пустой результат.

Примеры использования:

Удалить все блоки из таблицы conditions старше 3 месяцев:

SELECT drop_chunks('conditions', INTERVAL '3 months');

Пример вывода:

drop_chunks
----------------------------------------
_timescaledb_internal._hyper_3_5_chunk
_timescaledb_internal._hyper_3_6_chunk
_timescaledb_internal._hyper_3_7_chunk
_timescaledb_internal._hyper_3_8_chunk
_timescaledb_internal._hyper_3_9_chunk
(5 rows)

Удалить все блоки далее чем на 3 месяца в будущем из гипертаблицы conditions. Это полезно для исправления данных, поступающих с неправильными часами:

SELECT drop_chunks('conditions', newer_than => now() + interval '3 months');

Удалить все блоки старше 2017 года из гипертаблицы conditions:

SELECT drop_chunks('conditions', '2017-01-01'::date);

Удалить все блоки старше 2017 года из гипертаблицы conditions, с указанием времени в миллисекундах с эпохи UNIX:

SELECT drop_chunks('conditions', 1483228800000);

Удалить из гипертаблицы condtions все блоки старше 3 месяцев назад и новее 4 месяцев назад:

SELECT drop_chunks('conditions', older_than => interval '3 months', newer_than => interval '4 months')

set_chunk_time_interval()

Установить временной интервал блоков chunk_time_interval гипертаблицы. Новый интервал используется при создании новых блоков, но временные интервалы для существующих блоков не затрагиваются.

Синтаксис:

set_chunk_time_interval(hypertable regclass, chunk_time_interval anyelement, dimension_name name DEFAULT NULL::name)
 RETURNS void

Входные параметры:

Название параметра	Тип значения	Описание
hypertable	regclass	Гипертаблица, для которой следует обновить интервал
chunk_time_interval	anyelement	Интервал во времени события, который охватывает каждый новый блок. Должен быть больше нуля

Дополнительный параметр:

Название параметра	Тип значения	Описание
dimension_name	name	Имя измерения времени, для которого создаются разделы. Применимо только для гипертаблиц с несколькими измерениями времени

Допустимые типы для chunk_time_interval зависят от типа столбца времени гипертаблицы:

• TIMESTAMP, TIMESTAMPTZ, DATE: Указываемый chunk_time_interval должен быть либо типа INTERVAL (INTERVAL '1 day'), либо значением integer или bigint (в миллисекундах).
• INTEGER: Указываемый chunk_time_interval должен быть значением integer (smallint, int, bigint) и соответствовать семантике столбца времени в гипертаблице — быть в миллисекундах, если столбец в таблице хранит значения в миллисекундах (смотрите описание create_hypertable()).

Возвращаемое значение:

Отсутствует. В случае успешного выполнения функция вернет пустое значение.

Примеры использования:

Для столбца TIMESTAMP задать chunk_time_interval равным 24 часам:

SELECT set_chunk_time_interval('conditions', INTERVAL '24 hours');

SELECT set_chunk_time_interval('conditions', 86400000000);

Для столбца времени, выраженного как количество миллисекунд с начала эпохи UNIX, установить chunk_time_interval равным 24 часам:

SELECT set_chunk_time_interval('conditions', 86400000);

set_integer_now_func()

Эта функция актуальна только для гипертаблиц с целочисленными (в отличие от TIMESTAMP/TIMESTAMPTZ/DATE) значениями времени. Она задает функцию, которая возвращает текущее время в единицах столбца времени. Это необходимо для применения некоторых политик к таблицам с целочисленными значениями. В частности, многие политики применяются только к блокам определенного возраста, и функция, возвращающая текущее время, необходима для определения возраста фрагмента.

Синтаксис:

set_integer_now_func(hypertable regclass, integer_now_func regproc, replace_if_exists boolean DEFAULT false)
 RETURNS void

Входные параметры:

Название параметра	Тип значения	Описание
main_table	regclass	Гипертаблица, для которой необходимо задать функцию
integer_now_func	regproc	Функция, возвращающая значение текущего времени в тех же единицах, что и в столбце времени

Дополнительный параметр:

Название параметра	Тип значения	Описание
replace_if_exists	boolean	Переписывать ли предыдущую функцию, если она была задана ранее. По умолчанию установлено значение false

Возвращаемое значение:

Отсутствует. В случае успешного выполнения функция вернет пустое значение.

Пример использования:

Задать функцию конвертации времени для гипертаблицы, в которой столбец времени содержит данные в формате времени UNIX (количество секунд с момента начала эпохи UNIX, UTC).

CREATE OR REPLACE FUNCTION unix_now() returns BIGINT LANGUAGE SQL STABLE as $$ SELECT extract(epoch from now())::BIGINT $$;

SELECT set_integer_now_func('test_table_bigint', 'unix_now');

set_number_partitions()

Задает количество разделов (срезов) пространственного измерения на гипертаблице. Новое сегментирование применяется только к новым блокам.

Синтаксис:

set_number_partitions(hypertable regclass, number_partitions integer, dimension_name name DEFAULT NULL::name)
 RETURNS void

Входные параметры:

Название параметра	Тип значения	Описание
hypertable	regclass	Гипертаблица, для которой требуется обновить количество разделов
number_partitions	integer	Количество разделов для измерения. Должно быть больше 0 и меньше 32 768

Дополнительный параметр:

Название параметра	Тип значения	Описание
dimension_name	name	Имя пространственного измерения, для которого задается количество разделов

Имя dimension_name должно быть явно указано только в том случае, если гипертаблица имеет более одного пространственного измерения. В противном случае будет выдана ошибка.

Возвращаемое значение:

Отсутствует. В случае успешного выполнения функция вернет пустое значение.

Примеры использования:

Для таблицы с одним пространственным измерением:

SELECT set_number_partitions('conditions', 2);

Для таблицы с более чем одним пространственным измерением:

SELECT set_number_partitions('conditions', 2, 'device_id');

show_chunks()

Выводит список блоков, связанных с гипертаблицами.

Синтаксис:

show_chunks(relation regclass, older_than "any" DEFAULT NULL::unknown, newer_than "any" DEFAULT NULL::unknown, created_before "any" DEFAULT NULL::unknown, created_after "any" DEFAULT NULL::unknown)
 RETURNS SETOF regclass

Входные параметры:

Функция принимает следующие аргументы. Они семантически идентичны аргументам функции drop_chunks.

Название параметра	Тип значения	Описание
relation	regclass	Гипертаблица или непрерывный агрегат, из которого необходимо выбрать блоки. Если аргумент не задан, выводятся все блоки

Дополнительные параметры:

Название параметра	Тип значения	Описание
older_than		Точка отсчета во времени, все блоки старше которой следует вывести
newer_than		Точка во времени, блоки новее которой следует вывести
created_before		Вывести только те блоки, которые были созданы до указанной временной метки
created_after		Вывести только те блоки, которые были созданы после указанной временной метки

Параметры older_than и newer_than могут быть заданы двумя способами:

• INTERVAL: точка отсечения вычисляется как now () - older_than и аналогично now () - newer_than. Если указан INTERVAL и столбец времени не является TIMESTAMP, TIMESTAMPTZ или DATE, команда завершится с ошибкой;
• TIMESTAMP, DATE или INTEGER: точка отсечения явно задается как TIMESTAMP / TIMESTAMPTZ / DATE или как SMALLINT / INT / BIGINT.

Выбранный вариант должен соответствовать типу столбца времени гипертаблицы. Когда используются оба аргумента, функция возвращает пересечение результирующих двух диапазонов. Например, newer_than => 4 months и older_than => 3 months выведет все полные блоки в промежутке от 3 до 4 месяцев назад. Аналогично, newer_than => '2017-01-01' и older_than => '2017-02-01' удалит все блоки между '2017-01-01' и '2017-02-01'.

Указание параметров, которые не приводят к перекрывающемуся пересечению между двумя диапазонами, приведет к ошибке.

Примеры использования:

Вывести список всех блоков. Вернуть нуль, если не существует ни одной гипертаблицы:

SELECT show_chunks();

Ожидаемый результат:

show_chunks
---------------------------------------
_timescaledb_internal._hyper_1_10_chunk
_timescaledb_internal._hyper_1_11_chunk
_timescaledb_internal._hyper_1_12_chunk
_timescaledb_internal._hyper_1_13_chunk
_timescaledb_internal._hyper_1_14_chunk
_timescaledb_internal._hyper_1_15_chunk
_timescaledb_internal._hyper_1_16_chunk
_timescaledb_internal._hyper_1_17_chunk
_timescaledb_internal._hyper_1_18_chunk

Вывести список всех блоков, связанных с таблицей:

SELECT show_chunks('conditions');

Вывести все блоки старше 3 месяцев:

SELECT show_chunks(older_than => INTERVAL '3 months');

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

SELECT show_chunks(newer_than => now() + INTERVAL '3 months');

Вывести все блоки из таблицы conditions старше 3 месяцев:

SELECT show_chunks('conditions', older_than => INTERVAL '3 months');

Вывести все блоки из гипертаблицы conditions старше 2017 года:

SELECT show_chunks('conditions', older_than => DATE '2017-01-01');

Вывести все блоки новее 3 месяцев:

SELECT show_chunks(newer_than => INTERVAL '3 months');

Вывести все блоки старше 3 месяцев и новее 4 месяцев:

SELECT show_chunks(older_than => INTERVAL '3 months', newer_than => INTERVAL '4 months');

timescaledb_post_restore()

Выполняет необходимые операции после завершения процедуры восстановления базы данных. В частности, сбросить GUC timescaledb.restoring и перезапустить фоновые рабочие процессы. Изучите документацию TimescaleDB, раздел «backup/restore».

Синтаксис:

timescaledb_post_restore()
 RETURNS boolean

Пример использования:

SELECT timescaledb_post_restore();

timescaledb_pre_restore()

Выполнить операции, необходимые для начала восстановления базы данных с помощью pg_restore. В частности, изменить статус GUC timescaledb.restoring на on и остановить запущенные фоновые процессы, пока не будет выполнена функция timescaledb_post_restore. Изучите документацию TimescaleDB, раздел «backup/restore».

Внимание!

Запуск этой функции во время обновления версии Timescale может привести к непредвиденным проблемам для версий старше 1.7.1.

После запуска SELECT timescaledb_pre_restore() необходимо выполнить функцию timescaledb_post_restore для восстановления штатной работы базы данных.

Пример использования:

SELECT timescaledb_pre_restore();

Агрегатные функции

first()

Агрегат first позволяет получить значение одного столбца по порядку другого. Например, first (temperature, time) вернет самое раннее значение температуры, основанное на времени внутри агрегированной группы.

Входные параметры:

Название параметра	Тип значения	Описание
value	anyelement	Возвращаемые значения
time	timestamp / timestamptz / integer	Временная отметка, на которую ориентироваться

Пример использования:

Вывести самую раннюю температуру по device_id:

SELECT device_id, first(temp, time)

FROM metrics

GROUP BY device_id;

Заметки об использовании:

Команды last и first не используют индексы, а вместо этого выполняют последовательное сканирование своих групп. Они в основном используются для упорядоченного выбора в агрегате GROUP BY, а не как альтернатива ORDER BY time DESC LIMIT 1 для поиска последнего значения (второй вариант будет использовать индексы).

histogram()

Функция histogram() представляет распределение набора значений в виде массива сегментов одинаковой ширины. Она разбивает набор данных на заданное количество сегментов (nbuckets) в соответствии с указанными значениями min и max.

Возвращаемое значение – это массив из nbuckets+2 сегментов. В центральных nbuckets сегментах массива будут храниться значения в указанном диапазоне от минимального до максимального: в первом сегменте будут значения ниже min параметра, в последнем – выше или равные max. Каждый сегмент строго включает нижнее значение своего диапазона и строго не включает верхнее. Таким образом, значения, равные нижнему (min), включаются в сегмент, который начинается с верхнего (max), но значения, равные max, включаются в последний сегмент.

Входные параметры:

Название параметра	Описание
value	Набор значений для разбиения в гистограмму
min	Нижнее значение для разбиения на сегменты (строго включая)
max	Верхнее значение для разбиения на сегменты (строго не включая)
nbuckets	Целочисленное количество сегментов (разделов) гистограммы

Пример использования:

Простое разбиение данных о заряде аккумулятора из набора данных readings:

SELECT device_id, histogram(battery_level, 20, 60, 5) FROM readings GROUP BY device_id LIMIT 10;

Ожидаемый результат:

device_id   | histogram
------------+------------------------------
demo000000  | {0,0,0,7,215,206,572}
demo000001  | {0,12,173,112,99,145,459}
demo000002  | {0,0,187,167,68,229,349}
demo000003  | {197,209,127,221,106,112,28}
demo000004  | {0,0,0,0,0,39,961}
demo000005  | {12,225,171,122,233,80,157}
demo000006  | {0,78,176,170,8,40,528}
demo000007  | {0,0,0,126,239,245,390}
demo000008  | {0,0,311,345,116,228,0}
demo000009  | {295,92,105,50,8,8,442}

last()

Агрегат last позволяет получить значение одного столбца по порядку другого. Например, last (temperature, time) вернет самое позднее значение температуры, основанное на времени внутри агрегированной группы.

Входные параметры:

Название параметра	Тип значения	Описание
value	anyelement	Возвращаемые значения
time	timestamp / timestamptz / integer	Временная отметка, на которую ориентироваться

Пример использования:

Вывести температуру за каждые 5 минут для каждого устройства за прошедший день:

SELECT device_id, time_bucket('5 minutes', time) AS interval, last(temp, time) FROM metrics WHERE time > now () - INTERVAL '1 day' GROUP BY device_id, interval ORDER BY interval DESC;

Заметки об использовании:

Команды last и first не используют индексы, а вместо этого выполняют последовательное сканирование своих групп. Они в основном используются для упорядоченного выбора в агрегате GROUP BY, а не как альтернатива ORDER BY time DESC LIMIT 1 для поиска последнего значения (второй вариант будет использовать индексы).

time_bucket()

Это модернизированная версия стандартной функции ядра PostgreSQL date_trunc. Она допускает произвольные интервалы времени, а не только секунды, минуты, часы, как в случае date_trunc. Функция возвращает время первого временного сегмента.

Заметки об использовании:

Аргументы TIMESTAMPTZ разбиваются на сегменты времени по UTC. Как следствие, сегменты будут отсортированы с отсчетом от полуночи по UTC. Чтобы сегменты сортировались по местному времени, нужно преобразовать TIMESTAMPTZ в TIMESTAMP, чтобы преобразовать его в местное время, и после этого передавать time_bucket (смотрите пример ниже).

Обратите внимание, что количество данных во временном сегменте может различаться, если в сегмент попадает граница перехода на зимнее/летнее время. Например, если размер сегмента bucket_width составляет 2 часа, при попадании на границу перехода на зимнее/летнее время в сегмент попадет либо 3 часа, либо 1 час.

Входные параметры:

Название параметра	Тип значения	Описание
bucket_width	interval	Интервал времени СУБД, определяющий размер каждого сегмента
time	timestamp / timestamptz / date	Временная отметка для создания сегмента

Дополнительные параметры:

Название параметра	Тип значения	Описание
offset	interval	Интервал времени, на который необходимо сместить все сегменты
origin	timestamp / timestamptz / date	Сортировать сегменты относительно этой временной отметки

Входные параметры для времени в целочисленном формате:

Название параметра	Тип значения	Описание
time	integer	Временная отметка для создания сегмента

Дополнительный параметр:

Название параметра	Тип значения	Описание
offset	integer	Интервал времени, на который необходимо сместить все сегменты

Примеры использования:

Простой вывод средних значений за 5 минут:

SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu) FROM metrics GROUP BY five_min ORDER BY five_min DESC LIMIT 10;

Выводить время из центра сегмента, а не из его начала:

SELECT time_bucket('5 minutes', time) + '2.5 minutes' AS five_min, avg(cpu) FROM metrics GROUP BY five_min ORDER BY five_min DESC LIMIT 10;

Для округления сместить точку отсчета таким образом, чтобы центр сегмента попадал на 5 минут (и вывести время из центра сегмента):

SELECT time_bucket('5 minutes', time, '-2.5 minutes') + '2.5 minutes' AS five_min, avg(cpu) FROM metrics GROUP BY five_min ORDER BY five_min DESC LIMIT 10;

Для смещения точки отсчета можно использовать параметр origin (передается как timestamp, timestamptz или date). В примере ниже начало недели смещается на воскресенье (по умолчанию – понедельник):

SELECT time_bucket('1 week', timetz, TIMESTAMPTZ '2017-12-31') AS one_week, avg(cpu) FROM metrics GROUP BY one_week WHERE time > TIMESTAMPTZ '2017-12-01' AND time < TIMESTAMPTZ '2018-01-03' ORDER BY one_week DESC LIMIT 10;

Здесь значение параметра origin задано как 2017-12-31 - воскресенье в пределах анализируемого периода. Однако точка отсчета может быть и до начала периода, и в пределах периода, и после его окончания. Все сегменты рассчитываются относительно этой точки. Таким образом, в этом примере могло быть использовано любое другое воскресенье. Обратите внимание, что time < TIMESTAMPTZ '2018-01-03', поэтому в последнем сегменте будут данные только за 4 дня.

Разбиение по TIMESTAMPTZ в местном времени вместо UTC:

SELECT time_bucket(INTERVAL '2 hours', timetz::TIMESTAMP) AS five_min, avg(cpu) FROM metrics GROUP BY five_min ORDER BY five_min DESC LIMIT 10;

Заметьте, что преобразование в TIMESTAMP конвертирует время в местное время в соответствии с настройками часового пояса сервера.

Внимание!

Если осуществляется миграция с версии старше 1.0.0, то обратите внимание, что точка отсчета по умолчанию была изменена с 2000-01-01 (суббота) на 2000-01-03 (понедельник) при переходе с версии 0.12.1 на 1.0.0. Это приводит функцию time_bucket в соответствие со стандартом ISO, который диктует, что неделя начинается с понедельника. Это изменение может затронуть только запросы time_bucket, которые охватывают несколько дней. Чтобы воспроизвести старое поведение, можно передавать 2000-01-01 как параметр origin для time_bucket.

Представления

timescaledb_information.chunks

Выводит метаданные о блоках гипертаблиц.

В этом представлении отображаются метаданные для основного измерения времени блока. Для получения информации о вторичных измерениях гипертаблицы используется dimensions view.

Если основное измерение блока имеет временной тип данных, то задаются значения range_start и range_end. В противном случае, если основной тип измерения целочисленный, задаются значения range_start_integer и range_end_integer.

Возвращаемые значения:

Название поля	Тип значения	Описание
hypertable_schema	name	Имя схемы гипертаблицы
hypertable_name	name	Имя гипертаблицы
chunk_schema	name	Имя схемы блока
chunk_name	name	Имя блока
primary_dimension	name	Имя столбца первичного измерения
primary_dimension_type	regtype	Тип столбца первичного измерения
range_start	timestamp (0) with time zone	Начало диапазона для измерения блока
range_end	timestamp (0) with time zone	Конец диапазона для измерения блока
range_start_integer	bigint	Начало диапазона для измерения блока, если тип измерения целочисленный
range_end_integer	bigint	Конец диапазона для измерения блока, если тип измерения целочисленный
is_compressed	boolean	Вывод информации о том, сжаты ли данные в блоке. NULL для распределенных блоков. Для получения информации о статусе сжатия для распределенных блоков используется функция chunk_compression_stats()
chunk_tablespace	name	Табличное пространство блока
data_nodes	name	Узлы, на которые блок реплицируется. Это применимо только к блокам распределенных гипертаблиц

Пример использования:

Вывод информации о блоках гипертаблицы:

CREATE TABLESPACE tablespace1 location '/usr/local/pgsql/data1'

CREATE TABLE hyper_int (a_col integer, b_col integer, c integer);

SELECT table_name from create_hypertable('hyper_int', 'a_col', chunk_time_interval=> 10);

CREATE OR REPLACE FUNCTION integer_now_hyper_int() returns int LANGUAGE SQL STABLE as $$ SELECT coalesce(max(a_col), 0) FROM hyper_int $$;

SELECT set_integer_now_func('hyper_int', 'integer_now_hyper_int');

INSERT INTO hyper_int SELECT generate_series(1,5,1), 10, 50;

SELECT attach_tablespace('tablespace1', 'hyper_int');

INSERT INTO hyper_int VALUES( 25 , 14 , 20), ( 25, 15, 20), (25, 16, 20);

SELECT * FROM timescaledb_information.chunks WHERE hypertable_name = 'hyper_int';

-[ RECORD 1 ]----------+----------------------

hypertable_schema      | public

hypertable_name        | hyper_int
chunk_schema           | _timescaledb_internal
chunk_name             | _hyper_7_10_chunk
primary_dimension      | a_col
primary_dimension_type | integer

range_start            |
range_end              |
range_start_integer    | 0

range_end_integer      | 10

is_compressed          | f
chunk_tablespace       |
data_nodes             |

-[ RECORD 2 ]----------+----------------------

hypertable_schema      | public

hypertable_name        | hyper_int
chunk_schema           | _timescaledb_internal
chunk_name             | _hyper_7_11_chunk
primary_dimension      | a_col
primary_dimension_type | integer

range_start            |
range_end              |
range_start_integer    | 20

range_end_integer      | 30

is_compressed          | f
chunk_tablespace       | tablespace1
data_nodes             |

timescaledb_information.compression_settings

Выводит информацию о настройках сжатия для гипертаблиц.

Каждая строка представления содержит информацию об отдельных столбцах orderby и segmentby, используемых сжатием.

Возвращаемые значения:

Название поля	Тип значения	Описание
hypertable_schema	name	Имя схемы гипертаблицы
hypertable_name	name	Имя гипертаблицы
attname	name	Имя столбца, используемого для настроек сжатия
segmentby_column_index	smallint	Положение attname в списке compress_segmentby
orderby_column_index	smallint	Положение attname в списке compress_orderby
orderby_asc	boolean	Значение true, если сортировка по ASC, false, если по DESC
orderby_nullsfirst	boolean	Значение true, если значения NULL выводятся в начале, false, если в конце

Пример использования:

Вывод информации о настройках сжатия для гипертаблицы hypertab:

CREATE TABLE hypertab (a_col integer, b_col integer, c_col integer, d_col integer, e_col integer);

SELECT table_name FROM create_hypertable('hypertab', 'a_col');

ALTER TABLE hypertab SET (timescaledb.compress, timescaledb.compress_segmentby = 'a_col,b_col', timescaledb.compress_orderby = 'c_col desc, d_col asc nulls last');

SELECT * FROM timescaledb_information.compression_settings WHERE hypertable_name = 'hypertab';

hypertable_schema | hypertable_name | attname | segmentby_column_index | orderby_column_index | orderby_asc | orderby_nullsfirst
------------------+-----------------+---------+------------------------+----------------------+-------------+--------------------
public            | hypertab        | a_col   |                      1 |                      |             |
public            | hypertab        | b_col   |                      2 |                      |             |
public            | hypertab        | c_col   |                        |                    1 |           f |                  t
public            | hypertab        | d_col   |                        |                    2 |           t |                  f
(4 rows)

timescaledb_information.continuous_aggregates

Выводит метаданные и информацию о настройках для непрерывных агрегатов.

Возвращаемые значения:

Название поля	Тип значения	Описание
view_schema	name	Схема представления непрерывного агрегата
view_name	name	Заданное пользователем имя непрерывного агрегата
view_owner	name	Владелец непрерывного агрегата
materialized_only	boolean	Возвращать только материализованные данные
materialization_hypertable_schema	name	Схема внутренней таблицы материализации
materialization_hypertable_name	name	Имя внутренней таблицы материализации
view_definition	text	Запрос SELECT для представления непрерывного агрегата

Пример использования:

SELECT * FROM timescaledb_information.continuous_aggregates;

-[ RECORD 1 ]---------------------+-------------------------------------------------
hypertable_schema                 | public
hypertable_name                   | foo
view_schema                       | public
view_name                         | contagg_view
view_owner                        | postgres
materialized_only                 | f
materialization_hypertable_schema | _timescaledb_internal
materialization_hypertable_name   | _materialized_hypertable_2
view_definition                   | SELECT foo.a, +
                                  | COUNT(foo.b) AS countb +
                                  | FROM foo +
                                  | GROUP BY (time_bucket('1 day', foo.a)), foo.a;

timescaledb_information.data_nodes

Выводит информацию об узлах данных. Эта функция применима только для развертывания TimescaleDB на нескольких узлах.

Возвращаемые значения:

Название поля	Описание
node_name	Имя узла данных
owner	OID пользователя, добавившего узел данных
options	Опции, использованные при создании узла данных

Пример использования:

Вывод метаданных узлов данных:

SELECT * FROM timescaledb_information.data_nodes;

 node_name | owner    | options
-----------+----------+----------------------------------------
 dn1       | postgres | {host=localhost,port=15431,dbname=test}
 dn2       | postgres | {host=localhost,port=15432,dbname=test}
(2 rows)

timescaledb_information.dimensions

Выводит метаданные об измерениях гипертаблиц, возвращая одну строку метаданных для каждого измерения гипертаблицы. Например, для гипертаблицы, сегментированной по времени и пространству, будут возвращены две строки метаданных.

Столбец временного измерения должен иметь либо целочисленный тип данных (bigint, integer, smallint), либо временной (timestamptz, timestamp, date). Столбец time_interval определен для гипертаблиц, в которых используются временные типы данных. Для гипертаблиц, использующих целочисленные типы данных, определяются столбцы integer_interval и integer_now_func.

Для пространственных измерений возвращаются метаданные, указывающие количество num_partitions. Столбцы time_interval и integer_intervalcolumns неприменимы для пространственных измерений.

Возвращаемые значения:

Название поля	Тип значения	Описание
hypertable_schema	name	Имя схемы гипертаблицы
hypertable_name	name	Имя гипертаблицы
dimension_number	bigint	Номер измерения гипертаблицы, начиная с 1
column_name	name	Имя столбца, по которому было создано это измерение
Столбец_type	regtype	Тип столбца, по которому было создано это измерение
dimension_type	text	Временное ли это или пространственное измерение
time_interval	interval	Интервал времени для основного измерения, если тип столбца основан на временных типах данных СУБД
integer_interval	bigint	Целочисленный интервал для основного измерения, если тип данных столбца целочисленный
integer_now_func	name	Функция integer_now для основного измерения, если тип данных столбца целочисленный
num_partitions	smallint	Количество разделов измерения

Пример использования:

Создать гипертаблицу с разделением по времени и пространству:

CREATE TABLE dist_table(time timestamptz, device int, temp float);

SELECT create_hypertable('dist_table', 'time', 'device', chunk_time_interval=> INTERVAL '7 days', number_partitions=>3);

Вывод информации об измерениях гипертаблицы:

SELECT * from timescaledb_information.dimensions ORDER BY hypertable_name, dimension_number;

-[ RECORD 1 ]-----+-------------------------
hypertable_schema | public
hypertable_name   | dist_table
dimension_number  | 1
column_name       | time
column_type       | timestamp with time zone
dimension_type    | Time
time_interval     | 7 days
integer_interval  |
integer_now_func  |
num_partitions    |
-[ RECORD 2 ]-----+-------------------------
hypertable_schema | public
hypertable_name   | dist_table
dimension_number  | 2
column_name       | device
column_type       | integer
dimension_type    | Space
time_interval     |
integer_interval  |
integer_now_func  |
num_partitions    | 2

Вывод информации об измерениях гипертаблицы, имеющей два временных измерения:

CREATE TABLE hyper_2dim (a_col date, b_col timestamp, c_col integer);

SELECT table_name from create_hypertable('hyper_2dim', 'a_col');

SELECT add_dimension('hyper_2dim', 'b_col', chunk_time_interval=> '7 days');

SELECT * FROM timescaledb_information.dimensions WHERE hypertable_name = 'hyper_2dim';

-[ RECORD 1 ]-----+----------------------------
hypertable_schema | public
hypertable_name   | hyper_2dim
dimension_number  | 1
column_name       | a_col
column_type       | date
dimension_type    | Time
time_interval     | 7 days
integer_interval  |
integer_now_func  |
num_partitions    |
-[ RECORD 2 ]-----+----------------------------
hypertable_schema | public
hypertable_name   | hyper_2dim
dimension_number  | 2
column_name       | b_col
column_type       | timestamp without time zone
dimension_type    | Time
time_interval     | 7 days
integer_interval  |
integer_now_func  |
num_partitions    |

timescaledb_information.hypertables

Выводит метаданные о гипертаблицах.

Возвращаемые значения:

Название поля	Тип значения	Описание
hypertable_schema	name	Имя схемы гипертаблицы
hypertable_name	name	Имя гипертаблицы
owner	name	Владелец гипертаблицы
num_dimensions	smallint	Количество измерений
num_chunks	bigint	Количество блоков
compression_enabled	boolean	Включено ли сжатие или нет
is_distributed	boolean	Распределенная ли это гипертаблица или нет
replication_factor	smallint	Коэффициент репликации гипертаблицы
data_nodes	name	Узлы, на которые распределяется гипертаблицы
tablespaces	name	Табличные пространства, присоединенные к этой гипертаблице

Пример использования:

Вывод информации о гипертаблице:

CREATE TABLE dist_table(time timestamptz, device int, temp float);

SELECT create_distributed_hypertable('dist_table', 'time', 'device', replication_factor => 2);

SELECT * FROM timescaledb_information.hypertables WHERE hypertable_name = 'dist_table';

-[ RECORD 1 ]-------+-----------
hypertable_schema   | public
hypertable_name     | dist_table
owner               | postgres
num_dimensions      | 2
num_chunks          | 3
compression_enabled | f
is_distributed      | t
replication_factor  | 2
data_nodes          | {node_1, node_2}
tablespaces         |

timescaledb_information.jobs

Выводит информацию обо всех заданиях, зарегистрированных в среде автоматизации.

Возвращаемые значения:

Название поля	Тип значения	Описание
job_id	integer	ID фоновой задачи
application_name	name	Имя политики или заданного пользователем действия
schedule_interval	interval	Период выполнения задачи
max_runtime	interval	Максимальное время, которое задача может выполняться, прежде чем она будет прервана планировщиком
max_retries	integer	Количество повторных попыток выполнения задачи, если с первого раза она не завершится успешно
retry_period	interval	Период ожидания между повторными попытками выполнить задачу, если с первого раза она не завершится успешно
proc_schema	name	Имя схемы функции или процедуры, исполняемых задачей
proc_name	name	Имя функции или процедуры, исполняемых задачей
owner	name	Владелец задачи
scheduled	boolean	Если false, то задача исключена из планирования
config	jsonb	Конфигурация для данной задачи (передается функции при выполнении)
next_start	timestamptz	Время следующего запуска
hypertable_schema	name	Имя схемы гипертаблицы. Если это действие, заданное пользователем, значение будет NULL
hypertable_name	name	Имя гипертаблицы. Если это действие, заданное пользователем, значение будет NULL

Примеры использования:

Вывод информации о задачах:

SELECT * FROM timescaledb_information.jobs;
--Это задача, связанная с политикой обновления непрерывных агрегатов

job_id             | 1001
application_name   | Refresh Continuous Aggregate Policy [1001]
schedule_interval  | 01:00:00
max_runtime        | 00:00:00
max_retries        | -1
retry_period       | 01:00:00
proc_schema        | _timescaledb_internal
proc_name          | policy_refresh_continuous_aggregate
owner              | postgres
scheduled          | t
config             | {"start_offset": "20 days", "end_offset": "10 days", "mat_hypertable_id": 2}
next_start         | 2020-10-02 12:38:07.014042-04
hypertable_schema  | _timescaledb_internal
hypertable_name    | _materialized_hypertable_2

Найти все задания, связанные с политиками сжатия:

SELECT * FROM timescaledb_information.jobs where application_name like 'Compression%';

-[ RECORD 1 ]-----+--------------------------------------------------
job_id            | 1002
application_name  | Compression Policy [1002]
schedule_interval | 15 days 12:00:00
max_runtime       | 00:00:00
max_retries       | -1
retry_period      | 01:00:00
proc_schema       | _timescaledb_internal
proc_name         | policy_compression
owner             | postgres
scheduled         | t
config            | {"hypertable_id": 3, "compress_after": "60 days"}
next_start        | 2020-10-18 01:31:40.493764-04
hypertable_schema | public
hypertable_name   | conditions

Найти задачи, выполняемые с помощью определенных пользователем действий:

SELECT * FROM timescaledb_information.jobs where application_name like 'User-Define%';

-[ RECORD 1 ]-----+------------------------------
job_id            | 1003
application_name  | User-Defined Action [1003]
schedule_interval | 01:00:00
max_runtime       | 00:00:00
max_retries       | -1
retry_period      | 00:05:00
proc_schema       | public
proc_name         | custom_aggregation_func
owner             | postgres
scheduled         | t
config            | {"type": "function"}
next_start        | 2020-10-02 14:45:33.339885-04
hypertable_schema |
hypertable_name   |
-[ RECORD 2 ]-----+------------------------------
job_id            | 1004
application_name  | User-Defined Action [1004]
schedule_interval | 01:00:00
max_runtime       | 00:00:00
max_retries       | -1
retry_period      | 00:05:00
proc_schema       | public
proc_name         | custom_retention_func
owner             | postgres
scheduled         | t
config            | {"type": "function"}
next_start        | 2020-10-02 14:45:33.353733-04
hypertable_schema |
hypertable_name   |

timescaledb_information.job_stats

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

Возвращаемые значения:

Название поля	Тип значения	Описание
hypertable_schema	name	Имя схемы гипертаблицы
hypertable_name	name	Имя гипертаблицы
job_id	integer	ID фоновой задачи, созданной для исполнения политики
last_run_started_at	timestamptz	Время запуска последней задачи
last_successful_finish	timestamptz	Время последнего успешного завершения задачи
last_run_status	text	Успешно ли завершилась последняя задача
job_status	text	Статус задачи. Допустимы значения: Running (работает), Scheduled (запланировано) и Paused (приостановлено)
last_run_duration	interval	Срок выполнения последней задачи
next_scheduled_run	timestamptz	Время следующего запуска задачи
total_runs	bigint	Количество запусков задачи
total_successes	bigint	Количество успешных завершений
total_failures	bigint	Количество неудачных завершений

Примеры использования:

Вывести информацию об успешном и неудачном выполнении задач по конкретной гипертаблице:

SELECT job_id, total_runs, total_failures, total_successes FROM timescaledb_information.job_stats WHERE hypertable_name = 'test_table';

 job_id | total_runs | total_failures | total_successes
--------+------------+----------------+-----------------
 1001   | 1          | 0              | 1
 1004   | 1          | 0              | 1
(2 rows)

Вывести статистику по политикам, связанным с непрерывными агрегатами:

SELECT js.* FROM timescaledb_information.job_stats js, timescaledb_information.continuous_aggregates cagg WHERE cagg.view_name = 'max_mat_view_timestamp' and cagg.materialization_hypertable_name = js.hypertable_name;

-[ RECORD 1 ]----------+------------------------------
hypertable_schema      | _timescaledb_internal
hypertable_name        | _materialized_hypertable_2
job_id                 | 1001
last_run_started_at    | 2020-10-02 09:38:06.871953-04
last_successful_finish | 2020-10-02 09:38:06.932675-04
last_run_status        | Success
job_status             | Scheduled
last_run_duration      | 00:00:00.060722
next_scheduled_run     | 2020-10-02 10:38:06.932675-04
total_runs             | 1
total_successes        | 1
total_failures         | 0

Функции

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

get_telemetry_report()

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

Синтаксис:

get_telemetry_report()
 RETURNS jsonb

Примеры использования:

Если телеметрия включена, вывести отчет о телеметрии:

SELECT get_telemetry_report();

Если телеметрия отключена, вывести отчет о телеметрии локально:

SELECT get_telemetry_report(always_display_report := true);

chunk_compression_stats()

примечание

Функция, разработана сообществом.

Выводит статистику по сжатию для блоков гипертаблицы. Все размеры указаны в байтах.

Синтаксис:

chunk_compression_stats(hypertable regclass)
 RETURNS TABLE(chunk_schema name, chunk_name name, compression_status text, before_compression_table_bytes bigint, before_compression_index_bytes bigint, before_compression_toast_bytes bigint,
 before_compression_total_bytes bigint, after_compression_table_bytes bigint, after_compression_index_bytes bigint, after_compression_toast_bytes bigint, after_compression_total_bytes bigint,
node_name name)

Входные параметры:

Название параметра	Тип значения	Описание
hypertable	regclass	Имя гипертаблицы

Возвращаемые значения:

Название поля	Тип значения	Описание
chunk_schema	name	Имя схемы блока
chunk_name	name	Имя блока
number_compressed_chunks	integer	Количество блоков, испольуемых этой гипертаблицей, которые сейчас сжаты
before_compression_table_bytes	bigint	Размер кучи перед сжатием. Выводит NULL, если сжатие выключено
before_compression_index_bytes	bigint	Размер всех индексов до сжатия. Выводит NULL, если сжатие выключено
before_compression_toast_bytes	bigint	Размер таблицы TOAST до сжатия. Выводит NULL, если сжатие выключено
before_compression_total_bytes	bigint	Размер всей таблицы блока (таблица, индексы и TOAST) до сжатия. Выводит NULL, если сжатие выключено
after_compression_table_bytes	bigint	Размер кучи после сжатия. Выводит NULL, если сжатие выключено
after_compression_index_bytes	bigint	Размер всех индексов после сжатия. Выводит NULL, если сжатие выключено
after_compression_toast_bytes	bigint	Размер таблицы TOAST после сжатия. Выводит NULL, если сжатие выключено
after_compression_total_bytes	bigint	Размер всей таблицы блока (таблица, индексы и TOAST) после сжатия. Выводит NULL, если сжатие выключено
node_name	name	Узлы, на которых расположен блок (для распределенных гипертаблиц)

Пример использования:

SELECT * FROM hypertable_compression_stats('conditions');

-[ RECORD 1 ]------------------+------
total_chunks                   | 4
number_compressed_chunks       | 1
before_compression_table_bytes | 8192
before_compression_index_bytes | 32768
before_compression_toast_bytes | 0
before_compression_total_bytes | 40960
after_compression_table_bytes  | 8192
after_compression_index_bytes  | 32768
after_compression_toast_bytes  | 8192
after_compression_total_bytes  | 49152
node_name                      |

Функция pg_size_pretty может сконвертировать вывод в более читаемый формат.

SELECT pg_size_pretty(after_compression_total_bytes) as total FROM hypertable_compression_stats('conditions');

-[ RECORD 1 ]--+------
 total         | 48 kB

chunks_detailed_size()

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

Для распределенных гипертаблиц функция возвращает информацию о размере в виде отдельной строки на каждый узел.

Дополнительные метаданные по блокам можно вывести через представление timescaledb_information.chunks.

Синтаксис:

chunks_detailed_size(hypertable regclass)
 RETURNS TABLE(chunk_schema name, chunk_name name, table_bytes bigint, index_bytes bigint, toast_bytes bigint, total_bytes bigint, node_name name)

Входные параметры:

Название параметра	Тип значения	Описание
hypertable	regclass	Имя гипертаблицы

Возвращаемые значения:

Название поля	Тип значения	Описание
chunk_schema	name	Имя схемы блока
chunk_name	name	Имя блока
table_bytes	bigint	Пространство на диске, занятое таблицей блока
index_bytes	bigint	Пространство на диске, занятое индексами
toast_bytes	bigint	Пространство на диске, занятое таблицами TOAST
total_bytes	bigint	Пространство на диске, занятое блоком в целом, включая индексы и данные TOAST
node_name	name	Узел, данные по которому выводятся (для распределенных гипертаблиц)

Пример использования:

SELECT * FROM chunks_detailed_size('dist_table') ORDER BY chunk_name, node_name;

 chunk_schemac         | chunk_name            | table_bytes | index_bytes | toast_bytes | total_bytes | node_name
-----------------------+-----------------------+-------------+-------------+-------------+-------------+-----------------------
 _timescaledb_internal | _dist_hyper_1_1_chunk | 8192        | 32768       | 0           | 40960       | db_node1
 _timescaledb_internal | _dist_hyper_1_2_chunk | 8192        | 32768       | 0           | 40960       | db_node2
 _timescaledb_internal | _dist_hyper_1_3_chunk | 8192        | 32768       | 0           | 40960       | db_node3

hypertable_size()

Выводит общий размер гипертаблицы — сумму размеров самой таблицы, индексов в таблице и таблиц TOAST. Размер указывается в байтах. Результат эквивалентен суммированию столбца total_bytes из вывода функции hypertable_detailed_size.

Синтаксис:

hypertable_size(hypertable regclass)
 RETURNS bigint

Входные параметры:

Название параметра	Тип значения	Описание
hypertable	regclass	Гипертаблица, для которой необходимо вывести размер

Возвращаемые значения:

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

Пример использования:

Вывести информацию о размере гипертаблицы:

SELECT hypertable_size('devices') ;

 hypertable_size
-----------------
 73728

hypertable_detailed_size()

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

Синтаксис:

hypertable_detailed_size(hypertable regclass)
 RETURNS TABLE(table_bytes bigint, index_bytes bigint, toast_bytes bigint, total_bytes bigint, node_name name)

Входные параметры:

Название поля	Тип значения	Описание
hypertable	regclass	Гипертаблица, информацию о которой требуется вывести

Возвращаемые значения:

Название поля	Тип значения	Описание
table_bytes	bigint	Дисковое пространство, занимаемое основной таблицей (аналогично pg_relation_size(main_table))
index_bytes	bigint	Дисковое пространство, занимаемое индексами
toast_bytes	bigint	Дисковое пространство, занимаемое таблицами TOAST
total_bytes	bigint	Дисковое пространство, занимаемое гипертаблицей в совокупности, включая индексы и данные TOAST
node_name	name	Узел, для которого выводится информация (если гипертаблица распределенная)

Пример использования:

Вывести информацию о размере гипертаблицы (где disttable - распределенная гипертаблица):

SELECT * FROM hypertable_detailed_size('disttable') ORDER BY node_name;

 table_bytes | index_bytes | toast_bytes | total_bytes | node_name
-------------+-------------+-------------+-------------+-------------
 16384       | 32768      | 0            | 49152       | data_node_1
 8192        | 16384      | 0            | 24576       | data_node_2

hypertable_index_size()

Выводит размер индекса гипертаблицы. Размер указывается в байтах.

Синтаксис:

hypertable_index_size(index_name regclass)
 RETURNS bigint

Входные параметры:

Название параметра	Тип значения	Описание
index_name	regclass	Имя индекса гипертаблицы

Возвращаемые значения:

Объем дискового пространства, используемого индексом.

Пример использования:

Вывести размер конкретного индекса в гипертаблице.

\d conditions_table
Table "public.test_table"

 Column | Type                     | Collation | Nullable | Default
--------+--------------------------+-----------+----------+---------
 time  | timestamp with time zone  |           | not null |
 device | integer                  |           |          |
 volume | integer                  |           |          |

Indexes:
"second_index" btree ("time")
"test_table_time_idx" btree ("time" DESC)
"third_index" btree ("time")

SELECT hypertable_index_size('second_index');

hypertable_index_size
-----------------------
163840

SELECT pg_size_pretty(hypertable_index_size('second_index'));

pg_size_pretty
----------------
160 kB

show_tablespaces()

Выводит табличные пространства, связанные с данной гипертаблицей.

Синтаксис:

show_tablespaces(hypertable regclass)
 RETURNS SETOF name

Входные параметры:

Название параметра	Тип значения	Описание
hypertable	regclass	Гипертаблица, для которой требуется вывести связанные табличные пространства

Пример использования:

SELECT * FROM show_tablespaces('conditions');

show_tablespaces
------------------
disk1
disk2