A table engine storing time series, i.e. a set of values associated with timestamps and tags (or labels):

metric_name1[tag1=value1, tag2=value2, ...] = {timestamp1: value1, timestamp2: value2, ...}
metric_name2[...] = ...

Syntax

CREATE TABLE name [(columns)] ENGINE=TimeSeries
[SETTINGS var1=value1, ...]
[SAMPLES db.samples_table_name | [SAMPLES INNER COLUMNS (...)] [SAMPLES INNER ENGINE engine(arguments)]]
[RECENT SAMPLES db.recent_samples_table_name | [RECENT SAMPLES INNER COLUMNS (...)] [RECENT SAMPLES INNER ENGINE engine(arguments)]]
[TAGS db.tags_table_name | [TAGS INNER COLUMNS (...)] [TAGS INNER ENGINE engine(arguments)]]
[METRIC FAMILIES db.metric_families_table_name | [METRIC FAMILIES INNER COLUMNS (...)] [METRIC FAMILIES INNER ENGINE engine(arguments)]]

Usage

It’s easier to start with everything set by default (it’s allowed to create a TimeSeries table without specifying a list of columns):

CREATE TABLE my_table ENGINE=TimeSeries

Then this table can be used with the following protocols (a port must be assigned in the server configuration):

• prometheus remote-write
• prometheus remote-read

Outer columns

Columns of a TimeSeries table are generated automatically. These are outer columns, they store no data, they just provide interface for SELECT/INSERT. Actual data is stored in target tables. Here is the list of the outer columns:

Example:

INSERT INTO my_table (metric_name, tags, samples) VALUES
    ('cpu_usage', {'job': 'node_exporter', 'instance': 'host1:9100'},
     [(toDateTime64('2024-01-01 00:00:00', 3), 0.5), (toDateTime64('2024-01-01 00:01:00', 3), 0.7)])

metric_name is allowed to be empty on insertion, that means the metric name is specified in tags under __name__, for example:

INSERT INTO my_table (tags, samples) VALUES
    ({'__name__': 'cpu_usage', 'job': 'test'},
     [(toDateTime64('2024-01-01 00:00:00', 3), 0.5)])

To insert metrics metadata, insert into the metric_family, type, unit, and help columns:

INSERT INTO my_table (metric_name, tags, samples, metric_family, type, unit, help) VALUES
    ('http_requests_total', {'method': 'GET'}, [(now64(), 100.0)],
     'http_requests_total', 'counter', 'requests', 'Total HTTP requests')

Specifying outer columns

The outer samples column can be listed explicitly in a CREATE TABLE statement to override its default Array(Tuple(DateTime64(3), Float64)) type (its old name time_series is accepted too). ClickHouse extracts the timestamp and value types from the tuple and propagates them to the inner samples table:

CREATE TABLE my_table (samples Array(Tuple(UInt32, Float32))) ENGINE=TimeSeries

This is equivalent to declaring the timestamp and value column types in the samples INNER COLUMNS clause directly:

CREATE TABLE my_table ENGINE=TimeSeries
SAMPLES INNER COLUMNS (timestamp UInt32 CODEC(Delta, T64, ZSTD(3)), value Float32 CODEC(ALP, ZSTD(3)))

If both forms are used in the same CREATE TABLE statement, the declared types must match.

Target tables

A TimeSeries table doesn’t have its own data, everything is stored in its target tables. This is similar to how a materialized view works, with the difference that a materialized view has one target table whereas a TimeSeries table has three mandatory target tables named samples, tags, and metric families, and an optional recent samples target table which is enabled by default (see the recent_samples_ttl_seconds setting).

The target tables can be either specified explicitly in the CREATE TABLE query or the TimeSeries table engine can generate inner target tables automatically.

Rows inserted into a TimeSeries table are transformed, split into blocks, and inserted in these target tables.

The target tables are the following:

Samples table

The samples table contains time series associated with some identifier.

The samples table must have columns:

Columns the engine creates itself get time-series compression codecs: timestamp CODEC(Delta, T64, ZSTD(3)) and value CODEC(ALP, ZSTD(3)). Near-monotonic timestamps barely compress under generic codecs and can otherwise dominate the on-disk size of the samples table. The engine enables ALP for its inner samples and recent samples tables without requiring enable_alp_codec to be set. See also Adjusting types of columns.

Recent samples table

The recent samples table is optional and enabled by default (see the recent_samples_ttl_seconds setting; setting it to zero disables the table). It contains a copy of the samples newer than the TTL defined by that setting, and it must have the same columns as the samples table. The generated timestamp column uses CODEC(Delta, T64, ZSTD(3)), and the generated value column uses CODEC(ALP, ZSTD(3)).

Every inserted sample is written both to the samples table and to the recent samples table. Queries whose time range fits in the TTL window read from the recent samples table instead of the main samples table because it’s much smaller (this can be disabled with the query-level setting time_series_prefer_recent_samples_table).

The TTL of the inner recent samples table is always derived from the recent_samples_ttl_seconds setting.

Tags table

The tags table contains identifiers calculated for each combination of a metric name and tags.

The tags table must have columns:

New inner tags tables of version 5 and later with a MergeTree family engine have an inverted text index on tags: INDEX tags_idx tags TYPE text(tokenizer = 'keyValuePairs'). It accelerates exact label matches such as {job="api"} in PromQL by looking up the key and value together. Comparisons with an empty string also match missing labels and do not use this index.

Explicit indexes declared in TAGS INNER COLUMNS replace the default index. Existing tables and external tags tables keep their indexes; add and materialize the index on their tags target table to enable it.

Metric families table

The metric families table contains some information about the metric families being collected, the types of those metric families and their descriptions. A metric family is a group of metrics with the same name (the __name__ tag) and the same type, for example a histogram is a metric family which consists of multiple metrics.

The metric families table must have columns:

Creation

There are multiple ways to create a table with the TimeSeries table engine. The simplest statement

CREATE TABLE my_table ENGINE=TimeSeries

will actually create the following table (you can see that by executing SHOW CREATE TABLE my_table):

CREATE TABLE my_table
(
    `metric_name` String,
    `tags` Map(String, String),
    `samples` Array(Tuple(DateTime64(3), Float64)),
    `metric_family` String,
    `type` String,
    `unit` String,
    `help` String
)
ENGINE = TimeSeries
SETTINGS version = 6, recent_samples_ttl_seconds = 345600
SAMPLES INNER COLUMNS
(
    `id` Tuple(UInt64, LowCardinality(UUID)),
    `timestamp` DateTime64(3) CODEC(Delta, T64, ZSTD(3)),
    `value` Float64 CODEC(ALP, ZSTD(3))
)
SAMPLES INNER ENGINE = MergeTree ORDER BY (id, timestamp) SETTINGS index_granularity = 32768
RECENT SAMPLES INNER COLUMNS
(
    `id` Tuple(UInt64, UUID),
    `timestamp` DateTime64(3) CODEC(Delta, T64, ZSTD(3)),
    `value` Float64 CODEC(ALP, ZSTD(3))
)
RECENT SAMPLES INNER ENGINE = MergeTree PARTITION BY toStartOfInterval(toDateTime(timestamp), toIntervalHour(5)) ORDER BY (id, timestamp) TTL toDateTime(timestamp) + toIntervalSecond(345600) SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1
TAGS INNER COLUMNS
(
    `id` Tuple(UInt64, LowCardinality(UUID)) DEFAULT tuple(sipHash64(metric_name), toLowCardinality(reinterpretAsUUID(sipHash128(tags)))),
    `metric_name` LowCardinality(String),
    `tags` Map(LowCardinality(String), String),
    `min_time` SimpleAggregateFunction(min, Nullable(DateTime64(3))),
    `max_time` SimpleAggregateFunction(max, Nullable(DateTime64(3))),
    INDEX tags_idx tags TYPE text(tokenizer = 'keyValuePairs') GRANULARITY 100000000
)
TAGS INNER ENGINE = AggregatingMergeTree PRIMARY KEY metric_name ORDER BY (metric_name, id) SETTINGS allow_dimensions_outside_sorting_key = 1, index_granularity = 8192
METRIC FAMILIES INNER COLUMNS
(
    `metric_family` String,
    `type` LowCardinality(String),
    `unit` LowCardinality(String),
    `help` String
)
METRIC FAMILIES INNER ENGINE = ReplacingMergeTree ORDER BY metric_family

So the columns were generated automatically and also there are four inner target tables with their own column definitions stored in the INNER COLUMNS clauses. The recent_samples_ttl_seconds setting was written into the SETTINGS clause with its default value: the setting defines the TTL of the recent samples table, so its effective value is fixed at creation. Also the latest schema version was pinned into the version setting (see Schema versioning).

Inner target tables have names like .inner_id.samples.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, .inner_id.recentsamples.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, .inner_id.tags.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, .inner_id.metricfamilies.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx and each target table has its own set of columns:

CREATE TABLE default.`.inner_id.samples.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`
(
    `id` Tuple(UInt64, LowCardinality(UUID)),
    `timestamp` DateTime64(3) CODEC(Delta(8), T64, ZSTD(3)),
    `value` Float64 CODEC(ALP, ZSTD(3))
)
ENGINE = MergeTree
ORDER BY (id, timestamp)
SETTINGS index_granularity = 32768

CREATE TABLE default.`.inner_id.recentsamples.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`
(
    `id` Tuple(UInt64, UUID),
    `timestamp` DateTime64(3) CODEC(Delta(8), T64, ZSTD(3)),
    `value` Float64 CODEC(ALP, ZSTD(3))
)
ENGINE = MergeTree
PARTITION BY toStartOfInterval(toDateTime(timestamp), toIntervalHour(5))
ORDER BY (id, timestamp)
TTL toDateTime(timestamp) + toIntervalSecond(345600)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1

CREATE TABLE default.`.inner_id.tags.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`
(
    `id` Tuple(UInt64, LowCardinality(UUID)) DEFAULT tuple(sipHash64(metric_name), toLowCardinality(reinterpretAsUUID(sipHash128(tags)))),
    `metric_name` LowCardinality(String),
    `tags` Map(LowCardinality(String), String),
    `min_time` SimpleAggregateFunction(min, Nullable(DateTime64(3))),
    `max_time` SimpleAggregateFunction(max, Nullable(DateTime64(3))),
    INDEX tags_idx tags TYPE text(tokenizer = 'keyValuePairs') GRANULARITY 100000000
)
ENGINE = AggregatingMergeTree
PRIMARY KEY metric_name
ORDER BY (metric_name, id)
SETTINGS allow_dimensions_outside_sorting_key = 1, index_granularity = 8192

CREATE TABLE default.`.inner_id.metricfamilies.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`
(
    `metric_family` String,
    `type` LowCardinality(String),
    `unit` LowCardinality(String),
    `help` String
)
ENGINE = ReplacingMergeTree
ORDER BY metric_family
SETTINGS index_granularity = 8192

Creating a table AS existing table

Statement CREATE TABLE new_table AS existing_table creates a TimeSeries table configured like existing_table, which must be a TimeSeries table. The external targets of existing_table are not copied: the statement must declare those targets itself.

The statement copies from existing_table:

• the SETTINGS clause, except version: the new table always gets the latest version. Settings written in the statement itself are merged with the copied ones by name, so a written setting wins over the copied one, and name = DEFAULT resets a copied setting to its default value;
• the INNER COLUMNS and INNER ENGINE clauses of each inner table. Customized columns (e.g. extra columns, columns with a codec or a DEFAULT expression) and customized engine parts (e.g. an engine with arguments, a custom sorting key or engine setting) are kept, the other columns and engine parts are adjusted to the settings of the new table, so that e.g. tags_to_columns, aggregate_min_time_and_max_time or tags_index_granularity written in the statement take effect.

The types of the id, timestamp and value columns and the replication type of the inner engines (MergeTree, ReplicatedMergeTree or SharedMergeTree) are taken from existing_table too, unless the statement declares them itself. The outer column list is regenerated and not copied.

A table created by an older version of ClickHouse can be used as existing_table: the new table gets the current structure, e.g. the current id type and default identifier expression, and the customized parts copied from existing_table are adjusted to it.

Adjusting types of columns

You can adjust the types of columns in the inner target tables using the INNER COLUMNS clause. For example, to store timestamps in microseconds and values as Float32 use:

CREATE TABLE my_table ENGINE=TimeSeries
SAMPLES INNER COLUMNS (timestamp DateTime64(6) CODEC(Delta, T64, ZSTD(3)), value Float32 CODEC(ALP, ZSTD(3)))

Specifying inner columns without codecs means using the default codec for them:

CREATE TABLE my_table ENGINE=TimeSeries
SAMPLES INNER COLUMNS (timestamp DateTime64(6), value Float32)

The id column

The id column contains identifiers, every identifier is calculated for a combination of a metric name and tags. The type and the DEFAULT expression used to generate identifiers can be customized via the TAGS INNER COLUMNS clause:

CREATE TABLE my_table ENGINE=TimeSeries
TAGS INNER COLUMNS (id UInt64 DEFAULT sipHash64(tags))

The id column can be of any comparable non-Nullable type. The id types declared in the samples and tags inner tables must match.

If no DEFAULT expression is given for the id column and the id_generator setting is not set, ClickHouse will choose the DEFAULT expression automatically based on the id type, but only if the id type is one of UUID, UInt64, UInt128, FixedString(16), the same types wrapped in LowCardinality, or a tuple of two of those types. For such a tuple the automatically chosen expression calculates a hash of the metric name in the first component and a hash of all the tags in the second component.

A LowCardinality identifier type, e.g. Tuple(UInt64, LowCardinality(UUID)), keeps the identifiers dictionary-encoded: the samples table stores small per-block dictionaries with dictionary indexes instead of repeating the full identifier in every row, which reduces the amount of data read by queries.

The id_generator setting offers the same customization without using the INNER COLUMNS clause:

CREATE TABLE my_table ENGINE=TimeSeries
SETTINGS id_generator = 'sipHash64(tags)'

If the setting is set, it’s used to generate id even if the column’s DEFAULT contains a different expression.

The type of the id column can also be specified in the id_type setting instead of the INNER COLUMNS clause:

CREATE TABLE my_table ENGINE=TimeSeries
SETTINGS id_type = 'UInt64', id_generator = 'sipHash64(tags)'

When the id_generator setting is set, the id_type setting is recorded automatically at CREATE time, so the definition keeps the type the expression was written for.

The tags column

The tags column contains all the tags of a time series, including the __name__ tag with the name of a metric.

The tags_to_columns setting allows to specify that a specific tag should also be stored in a separate column in addition to the map inside the tags column:

CREATE TABLE my_table
ENGINE = TimeSeries
SETTINGS tags_to_columns = {'instance': 'instance', 'job': 'job'}

This statement will add columns instance and job to the inner tags target table. The values of the tags instance and job will be stored both in those columns and in the tags column.

Table engines of inner target tables

By default inner target tables use the following table engines:

• the samples table uses MergeTree;
• the recent samples table uses MergeTree partitioned by 5-hour buckets (see the recent_samples_partition_by setting) with a TTL derived from the recent_samples_ttl_seconds setting and with ttl_only_drop_parts enabled, so expired parts are dropped as a whole;
• the tags table uses AggregatingMergeTree because the same data is often inserted multiple times to this table so we need a way to remove duplicates, and also because it’s required to do aggregation for columns min_time and max_time;
• the metric families table uses ReplacingMergeTree because the same data is often inserted multiple times to this table so we need a way to remove duplicates.

The engine family of the generated inner tables follows the default_table_engine query-level setting: with default_table_engine = ReplicatedMergeTree or SharedMergeTree the inner tables use the corresponding Replicated or Shared engines. With default_table_engine = None (or any other value) the engines of the inner tables must be specified explicitly.

All the inner tables must have the same replication type: if one of them is replicated (or shared), the other inner tables must be replicated (or shared) too, otherwise their contents would diverge between replicas. For example, declaring SAMPLES INNER ENGINE = ReplicatedMergeTree(...) requires the other inner engines to be replicated as well - either declared explicitly or generated with default_table_engine = ReplicatedMergeTree.

Other table engines also can be used for inner target tables if it’s specified so:

CREATE TABLE my_table ENGINE=TimeSeries
SAMPLES ENGINE=ReplicatedMergeTree
RECENT SAMPLES ENGINE=ReplicatedMergeTree
TAGS ENGINE=ReplicatedAggregatingMergeTree
METRIC FAMILIES ENGINE=ReplicatedReplacingMergeTree

The tags table keeps the tag columns (and the tags Map) outside its sorting key, which AggregatingMergeTree rejects by default (see allow_dimensions_outside_sorting_key). This is safe here because those columns are functionally dependent on id, which is part of the sorting key, so all rows that a background merge collapses together share the same values. When the inner tags table is generated or its engine is specified inline as above, TimeSeries sets allow_dimensions_outside_sorting_key = 1 on it automatically; for a manually created external aggregating tags table you must set it yourself.

External target tables

It’s possible to make a TimeSeries table use a manually created table:

CREATE TABLE samples_for_my_table
(
    `id` UUID,
    `timestamp` DateTime64(3),
    `value` Float64
)
ENGINE = MergeTree
ORDER BY (id, timestamp);

CREATE TABLE tags_for_my_table ...

CREATE TABLE metric_families_for_my_table ...

CREATE TABLE my_table ENGINE=TimeSeries SAMPLES samples_for_my_table TAGS tags_for_my_table METRIC FAMILIES metric_families_for_my_table;

An external table can also be used as the recent samples target (the RECENT SAMPLES my_recent_samples_table clause). Such a table must have the same columns as an external samples table, and it must retain at least recent_samples_ttl_seconds seconds of data, which is the user’s responsibility.

The external tables’ column types (id, timestamp, value, and the <tag_value_column>s listed in tags_to_columns) must match what the TimeSeries table would otherwise generate internally (see Samples table, Tags table, and Metric families table for the type constraints). Type mismatches are reported at CREATE time.

The type of the id column of an external tags table and the expression generating identifiers are recorded in the id_type and id_generator settings at CREATE time (from version 2), so the definition of the TimeSeries table keeps them: for example, CREATE TABLE ... AS my_table reads the id type from the definition of my_table without reading its external target tables. If the id_generator setting isn’t specified, it’s set to the DEFAULT declared on the external table’s id column (if any), otherwise to the canonical generator derived from the id type. The recorded expression is used to generate id even if the DEFAULT of the external table changes later — see The id column for details.

Altering settings

Two settings can be changed after CREATE:

• id_generator
• filter_by_min_time_and_max_time

ALTER TABLE my_table MODIFY SETTING id_generator = 'sipHash64(tags)';
ALTER TABLE my_table MODIFY SETTING filter_by_min_time_and_max_time = 0;
ALTER TABLE my_table RESET SETTING filter_by_min_time_and_max_time;

Note that changing id_generator while data is already in the tags table can produce different IDs for the same metric+tag combination — old rows keep their old IDs, new rows use the new generator.

The other settings can’t be changed with ALTER ... MODIFY SETTING: most of them are baked into the schema of the inner tables at CREATE time, and the version setting is pinned automatically at CREATE time and identifies the schema itself (see Schema versioning).

Settings

Here is a list of settings which can be specified while defining a TimeSeries table:

Schema versioning

The TimeSeries table engine and the PromQL execution layer are under active development: the set of the target tables and their structure can change between ClickHouse versions. To make such changes detectable, every TimeSeries table stores its version in the version setting. The version is pinned automatically into the CREATE query when a table is created - its value is the latest version known to the server (currently 6) - persists in the table metadata, and can’t be changed by ALTER. Tables created before the setting was introduced are considered as version 0. Normally the setting should just be omitted in the CREATE TABLE query - then the table gets the latest version. An explicit version is accepted if the server supports that version; then the table is defined the way that version does it (see Version history). CREATE TABLE ... AS other_table doesn’t copy the version of the other table, see Creating a table AS existing table.

A server supports a range of versions, and the minimum version can differ for reading with SELECT, for writing with INSERT or the Prometheus remote-write protocol, and for evaluating PromQL (the prometheusQuery, prometheusQueryRange, and timeSeriesSelector table functions, the promql dialect, and the Prometheus HTTP query API):

• If the version of a TimeSeries table is too old for PromQL, PromQL queries over it are rejected. The exception suggests to re-create the table: create a new TimeSeries table, copy the data with an INSERT ... SELECT query, and replace the old table with the new one.
• If the version is too old to write into, INSERT queries and the Prometheus remote-write protocol are rejected, while SELECT queries still work.
• If the version is too old for the server at all, every query over the table (except SHOW CREATE TABLE, DETACH and DROP) is rejected.

Version history

Functions

Here is a list of functions supporting a TimeSeries table as an argument:

• timeSeriesSamples
• timeSeriesTags
• timeSeriesMetricFamilies

Syntax

ENGINE = TimeSeries()