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[...] = ...

This is an experimental feature that may change in backwards-incompatible ways in the future releases. Enable usage of the TimeSeries table engine with allow_experimental_time_series_table setting. Input the command set allow_experimental_time_series_table = 1.

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)]]

The keyword SAMPLES has an alias DATA which is kept for backwards compatibility.

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):

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:

Name	Type	Description
`metric_name`	`String`	The name of the metric
`tags`	`Map(String, String)`	Map of tags (labels) for the time series
`time_series`	`Array(Tuple(DateTime64(3), Float64))` by default	Array of (timestamp, value) pairs for a time series. The tuple’s timestamp and scalar element types can be derived from the samples `INNER COLUMNS` declaration (see Specifying outer columns)
`metric_family`	`String`	The name of the metric family (for metrics metadata)
`type`	`String`	The type of the metric (e.g. “counter”, “gauge”)
`unit`	`String`	The unit of the metric
`help`	`String`	The description of the metric

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:

Name	Mandatory?	Default type	Possible types	Description
`id`	[x]	`Tuple(UInt64, LowCardinality(UUID))`	any	Identifies a combination of a metric names and tags
`timestamp`	[x]	`DateTime64(3)`	`DateTime64(X)`	A time point
`value`	[x]	`Float64`	`Float32` or `Float64`	A value associated with the `timestamp`

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:

Name	Mandatory?	Default type	Possible types	Description
`id`	[x]	`Tuple(UInt64, LowCardinality(UUID))`	any (must match the type of `id` in the samples table)	An `id` identifies a combination of a metric name and tags. The DEFAULT expression specifies how to calculate such an identifier
`metric_name`	[x]	`LowCardinality(String)`	`String` or `LowCardinality(String)`	The name of a metric
`<tag_value_column>`	[ ]	`String`	`String` or `LowCardinality(String)` or `LowCardinality(Nullable(String))`	The value of a specific tag, the tag’s name and the name of a corresponding column are specified in the tags_to_columns setting
`tags`	[x]	`Map(LowCardinality(String), String)`	`Map(String, String)` or `Map(LowCardinality(String), String)` or `Map(LowCardinality(String), LowCardinality(String))`	Map of all the tags, including the tag `__name__` containing the name of a metric and including the tags with names enumerated in the tags_to_columns setting. Tables created by older versions of ClickHouse stored in this column only the tags without dedicated columns and without the metric name; reading handles both cases
`min_time`	[ ]	`Nullable(DateTime64(3))`	`DateTime64(X)` or `Nullable(DateTime64(X))`	Minimum timestamp of time series with that `id`. The column is created if store_min_time_and_max_time is `true`
`max_time`	[ ]	`Nullable(DateTime64(3))`	`DateTime64(X)` or `Nullable(DateTime64(X))`	Maximum timestamp of time series with that `id`. The column is created if store_min_time_and_max_time is `true`

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:

Name	Mandatory?	Default type	Possible types	Description
`metric_family_name`	[x]	`String`	`String` or `LowCardinality(String)`	The name of a metric family
`type`	[x]	`LowCardinality(String)`	`String` or `LowCardinality(String)`	The type of a metric family, one of “counter”, “gauge”, “summary”, “stateset”, “histogram”, “gaugehistogram”
`unit`	[x]	`LowCardinality(String)`	`String` or `LowCardinality(String)`	The unit used in a metric
`help`	[x]	`String`	`String` or `LowCardinality(String)`	The description of a metric

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.

In tables created by older versions of ClickHouse the tags column contains only the tags without dedicated columns and without the metric name, and the all_tags column is an ephemeral column which was filled on insertion with all the tags except the metric name.

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:

Name	Type	Default	Description
`id_generator`	Expression	depends on `id` type	Expression that computes the identifier (fingerprint) of a time series from its tags. If unset, the default expression for the `id` column is used. If the default expression for the `id` column is also unset then the expression is chosen automatically
`tags_to_columns`	Map	Map specifying which tags should be put to separate columns in the tags table. Syntax: `{'tag1': 'column1', 'tag2' : column2, ...}`
`use_all_tags_column_to_generate_id`	Bool	false	Obsolete setting, does nothing
`store_min_time_and_max_time`	Bool	true	If set to true then the table will store `min_time` and `max_time` for each time series
`aggregate_min_time_and_max_time`	Bool	true	When creating an inner target `tags` table, this flag enables using `SimpleAggregateFunction(min, Nullable(DateTime64(3)))` instead of just `Nullable(DateTime64(3))` as the type of the `min_time` column, and the same for the `max_time` column
`filter_by_min_time_and_max_time`	Bool	true	If set to true then the table will use the `min_time` and `max_time` columns for filtering time series
`samples_index_granularity`	UInt64	32768	Sets `index_granularity` of the inner samples table. When set explicitly, it overrides `index_granularity` from the engine declaration. Ignored for an external samples table and a non-MergeTree engine
`recent_samples_ttl_seconds`	UInt64	345600	Retention of the additional `recent samples` target table, which every inserted sample is written to as well. An inner recent samples table always gets `TTL toDateTime(timestamp) + toIntervalSecond(recent_samples_ttl_seconds)` derived from this setting (overriding any TTL from the engine declaration); an external recent samples table must retain at least this many seconds of data. Queries whose time range fits in the TTL window prefer the recent samples table to the main samples table (see the query-level setting `time_series_prefer_recent_samples_table`). The default is 4 days; the effective value is pinned into the table definition at CREATE time. Set to 0 to disable the recent samples table
`recent_samples_partition_by`	Expression	`toStartOfInterval(toDateTime(timestamp), toIntervalHour(5))`	Partition key of the inner `recent samples` table, for example `toStartOfHour(timestamp)`. When set explicitly, it overrides the partition key from the engine declaration; if neither is set, one partition per 5 hours is used. Ignored for an external recent samples table. Requires `recent_samples_ttl_seconds` to be non-zero
`recent_samples_index_granularity`	UInt64	8192	Sets `index_granularity` of the inner `recent samples` table. When set explicitly, it overrides `index_granularity` from the engine declaration. Ignored for an external recent samples table and a non-MergeTree engine. Requires `recent_samples_ttl_seconds` to be non-zero
`tags_index_granularity`	UInt64	8192	Sets `index_granularity` of the inner tags table. When set explicitly, it overrides `index_granularity` from the engine declaration. Ignored for an external tags table and a non-MergeTree engine

Functions

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

SQL Reference

Data Types

Engines

Functions

Formats

Interfaces

Settings

System Tables

Data Lakes

TimeSeries table engine

Syntax

Usage

Outer columns

Specifying outer columns

Target tables

Samples table

Recent samples table

Tags table

Metrics table

Creation

Creating a table AS existing table

Adjusting types of columns

The `id` column

The `tags` column

Table engines of inner target tables

External target tables

Altering settings

Settings

Functions

​Syntax

​Usage

​Outer columns

​Specifying outer columns

​Target tables

​Samples table

​Recent samples table

​Tags table

​Metrics table

​Creation

​Creating a table AS existing table

​Adjusting types of columns

​The id column

​The tags column

​Table engines of inner target tables

​External target tables

​Altering settings

​Settings

​Functions

Syntax

Usage

Outer columns

Specifying outer columns

Target tables

Samples table

Recent samples table

Tags table

Metrics table

Creation

Creating a table AS existing table

Adjusting types of columns

The `id` column

The `tags` column

Table engines of inner target tables

External target tables

Altering settings

Settings

Functions