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)]]
[METRICS db.metrics_table_name | [METRICS INNER COLUMNS (...)] [METRICS 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, time_series) 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, time_series) 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, time_series, 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 time_series column can be listed explicitly in a CREATE TABLE statement to override its default Array(Tuple(DateTime64(3), Float64)) type. ClickHouse extracts the timestamp and scalar types from the tuple and propagates them to the inner samples table:

CREATE TABLE my_table (time_series 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(DoubleDelta, ZSTD(1)), value Float32 CODEC(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 metrics, 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(DoubleDelta, ZSTD(1)) and value CODEC(ZSTD(3)). Near-monotonic timestamps barely compress under generic codecs and can otherwise dominate the on-disk size of the samples table. 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.

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:

Metrics table

The metrics table contains some information about metrics been collected, the types of those metrics and their descriptions.

The metrics 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),
    `time_series` Array(Tuple(DateTime64(3), Float64)),
    `metric_family` String,
    `type` String,
    `unit` String,
    `help` String
)
ENGINE = TimeSeries
SETTINGS recent_samples_ttl_seconds = 345600
SAMPLES INNER COLUMNS
(
    `id` Tuple(UInt64, LowCardinality(UUID)),
    `timestamp` DateTime64(3) CODEC(DoubleDelta, ZSTD(1)),
    `value` Float64 CODEC(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(DoubleDelta, ZSTD(1)),
    `value` Float64 CODEC(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)))
)
TAGS INNER ENGINE = AggregatingMergeTree PRIMARY KEY metric_name ORDER BY (metric_name, id) SETTINGS allow_dimensions_outside_sorting_key = 1, index_granularity = 8192
METRICS INNER COLUMNS
(
    `metric_family_name` String,
    `type` LowCardinality(String),
    `unit` LowCardinality(String),
    `help` String
)
METRICS INNER ENGINE = ReplacingMergeTree ORDER BY metric_family_name

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.

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.metrics.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(DoubleDelta, ZSTD(1)),
    `value` Float64 CODEC(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(DoubleDelta, ZSTD(1)),
    `value` Float64 CODEC(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)))
)
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.metrics.xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`
(
    `metric_family_name` String,
    `type` LowCardinality(String),
    `unit` LowCardinality(String),
    `help` String
)
ENGINE = ReplacingMergeTree
ORDER BY metric_family_name
SETTINGS index_granularity = 8192

Creating a table AS existing table

Statement CREATE TABLE new_table AS existing_table copies from the existing_table:

• SETTINGS
• INNER COLUMNS for each kind
• INNER ENGINE for each kind

The statement is not allowed if the existing_table has external targets. The outer column list is regenerated and not copied.

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(DoubleDelta, ZSTD(1)), value Float32 CODEC(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 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 metrics 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
METRICS 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 metrics_for_my_table ...

CREATE TABLE my_table ENGINE=TimeSeries SAMPLES samples_for_my_table TAGS tags_for_my_table METRICS metrics_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 Metrics table for the type constraints). Type mismatches are reported at CREATE time.

The id-generator expression for an external tags target is resolved at INSERT time in the following order: the id_generator setting (if set), then the DEFAULT declared on the external table’s id column (if any), then the canonical generator derived from the id type. The setting therefore overrides whatever DEFAULT is declared on the external table — 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;

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 because they are baked into the schema of the inner tables at CREATE time.

Settings

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

Functions

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

• timeSeriesSamples
• timeSeriesTags
• timeSeriesMetrics