> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > A table engine storing time series, i.e. a set of values associated with timestamps and tags (or labels). # TimeSeries table engine export const PrivatePreviewBadge = () => { return
{'Private preview'}
; }; A table engine storing time series, i.e. a set of values associated with timestamps and tags (or labels): ```sql theme={null} metric_name1[tag1=value1, tag2=value2, ...] = {timestamp1: value1, timestamp2: value2, ...} metric_name2[...] = ... ``` This is a private preview feature that may change in backwards-incompatible ways in the future releases. Enable usage of the TimeSeries table engine with the `enable_time_series_table` setting. Input the command `set enable_time_series_table = 1`. The `TimeSeries` table engine is available in ClickHouse Cloud as a private preview feature. The services that take part in the private preview already have the `enable_time_series_table` setting configured. Other ClickHouse Cloud services do not have this configuration, and you cannot enable the engine yourself on such a service.

Syntax

```sql theme={null} 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)]] ``` The keyword `SAMPLES` has an alias `DATA`, and the keyword `METRIC FAMILIES` has an alias `METRICS`, both are kept for backwards compatibility. The definition of a table of a [version](#schema-versioning) before 4 is written with `METRICS`, so that an older server can read it.

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): ```sql theme={null} 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](/concepts/features/interfaces/prometheus#remote-write) * [prometheus remote-read](/concepts/features/interfaces/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](#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 | | `samples` | `Array(Tuple(DateTime64(3), Float64))` by default | Array of (timestamp, value) pairs for a time series. The tuple's timestamp and value element types can be derived from the samples `INNER COLUMNS` declaration (see [Specifying outer columns](#specifying-outer-columns)). The column is named `time_series` in tables of [version](#schema-versioning) 2 and earlier | | `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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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](/reference/statements/create/view#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](#samples-table), [tags](#tags-table), and [metric families](#metric-families-table), and an optional [recent samples](#recent-samples-table) target table which is enabled by default (see the [recent\_samples\_ttl\_seconds](#settings) 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(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](#adjusting-column-types).

Recent samples table

The *recent samples* table is optional and enabled by default (see the [recent\_samples\_ttl\_seconds](#settings) 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](#samples-table) 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](#settings) 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](#samples-table) 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 | | `` | \[ ] | `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](#settings) 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](#settings) 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](#settings) 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](#settings) is `true` | New inner tags tables of [version](#schema-versioning) 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: | Name | Mandatory? | Default type | Possible types | Description | | - | - | - | - | - | | `metric_family` | \[x] | `String` | `String` or `LowCardinality(String)` | The name of a metric family. In tables of versions before 6 this column is named `metric_family_name` (see [Version history](#version-history)) | | `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 ```sql theme={null} CREATE TABLE my_table ENGINE=TimeSeries ``` will actually create the following table (you can see that by executing `SHOW CREATE TABLE my_table`): ```sql theme={null} 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 = 7, 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](#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: ```sql theme={null} 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 ``` ```sql theme={null} 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 ``` ```sql theme={null} 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 ``` ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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](#tags-table) 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](#samples-table) table uses [MergeTree](/reference/engines/table-engines/mergetree-family/mergetree); * the [recent samples](#recent-samples-table) table uses [MergeTree](/reference/engines/table-engines/mergetree-family/mergetree) partitioned by 5-hour buckets (see the [recent\_samples\_partition\_by](#settings) setting) with a `TTL` derived from the [recent\_samples\_ttl\_seconds](#settings) setting and with `ttl_only_drop_parts` enabled, so expired parts are dropped as a whole; * the [tags](#tags-table) table uses [AggregatingMergeTree](/reference/engines/table-engines/mergetree-family/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`. If these columns aren't stored (see the `store_min_time_and_max_time` setting), most duplicates don't even reach the table: the deduplication cache of the `TimeSeries` table skips the time series written recently (see the `tags_deduplication_cache_expiration_seconds` setting); * the [metric families](#metric-families-table) table uses [ReplacingMergeTree](/reference/engines/table-engines/mergetree-family/replacingmergetree) because the same data is often inserted multiple times to this table so we need a way to remove duplicates. Most duplicates don't even reach the table: the deduplication cache of the `TimeSeries` table skips the metric families written recently (see the `metric_families_deduplication_cache_expiration_seconds` setting). 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: ```sql theme={null} CREATE TABLE my_table ENGINE=TimeSeries SAMPLES ENGINE=ReplicatedMergeTree RECENT SAMPLES ENGINE=ReplicatedMergeTree TAGS ENGINE=ReplicatedAggregatingMergeTree METRIC FAMILIES ENGINE=ReplicatedReplacingMergeTree ``` The [tags](#tags-table) table keeps the tag columns (and the `tags` Map) outside its sorting key, which `AggregatingMergeTree` rejects by default (see [`allow_dimensions_outside_sorting_key`](/reference/engines/table-engines/mergetree-family/aggregatingmergetree)). 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](#external-target-tables) aggregating tags table you must set it yourself.

Retention

By default the [samples](#samples-table) table has no `TTL`, so samples are kept forever. To delete old samples, declare a `TTL` in the engine of the inner samples table: ```sql theme={null} CREATE TABLE my_table ENGINE=TimeSeries SETTINGS recent_samples_partition_by = 'toStartOfInterval(toDateTime(timestamp, ''UTC''), toIntervalHour(5))' SAMPLES INNER ENGINE = MergeTree PARTITION BY toDate(timestamp, 'UTC') ORDER BY (id, timestamp) TTL toDateTime(timestamp, 'UTC') + INTERVAL 30 DAY SETTINGS ttl_only_drop_parts = 1 ``` With `ttl_only_drop_parts` a part is dropped as a whole when all its samples are expired, instead of being rewritten. Partitioning by the UTC day keeps the samples of different days in different parts, so each day is dropped once its newest sample expires. The [recent\_samples\_partition\_by](#settings) setting keeps the default 5-hour partitions of the recent samples table, but computes them in UTC too. A partition key that depends on the server time zone can make a query skip parts with matching samples after that time zone changes. The [recent samples](#recent-samples-table) table keeps its own `TTL` set by the [recent\_samples\_ttl\_seconds](#settings) setting. Keep the `TTL` of the samples table longer than that, otherwise a query whose time range fits in the recent samples window can return samples which are already deleted from the samples table. By default the [tags](#tags-table) table has no `TTL`: a time series stays in it after all its samples are deleted.

External target tables

It's possible to make a `TimeSeries` table use a manually created table: ```sql theme={null} 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](#recent-samples-table) 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](#settings) seconds of data, which is the user's responsibility. The external tables' column types (`id`, `timestamp`, `value`, and the ``s listed in [`tags_to_columns`](#settings)) must match what the `TimeSeries` table would otherwise generate internally (see [Samples table](#samples-table), [Tags table](#tags-table), and [Metric families table](#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`](#settings) and [`id_generator`](#settings) settings at `CREATE` time (from [version](#schema-versioning) 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](#id-column) for details.

Altering settings

The following settings can be changed after `CREATE`: * `id_generator` * `filter_by_min_time_and_max_time` * `metric_families_deduplication_cache_size_bytes`, `metric_families_deduplication_cache_expiration_seconds` * `tags_deduplication_cache_size_bytes`, `tags_deduplication_cache_expiration_seconds` ```sql theme={null} 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 MODIFY SETTING metric_families_deduplication_cache_expiration_seconds = 600; 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. Changing a setting of a deduplication cache replaces the cache with an empty one, so the next insert writes all its rows again. Setting the size or the expiration to 0 disables the cache. 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](#schema-versioning)).

Settings

Here is a list of settings which can be specified while defining a `TimeSeries` table: | Name | Type | Default | Description | | - | - | - | - | | `version` | UInt64 | 7 | The version of the table: it identifies the set of the target tables and their structure. The version is pinned automatically when a table is created and can't be changed afterwards, normally it should be omitted in the `CREATE TABLE` query (see [Schema versioning](#schema-versioning)) | | `id_type` | Data type | depends on the `id` column | The type of the `id` column of the target tables. Normally the type is declared in the `INNER COLUMNS` clauses of the inner tables or in an [external](#external-target-tables) tags table; the setting is recorded automatically at `CREATE` time if the type isn't kept in the definition otherwise: if the tags target is an external table, or if the `id_generator` setting is set. The setting can also be specified explicitly instead of `TAGS INNER COLUMNS (id )`. Requires `version` to be at least 2 | | `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. For an external tags table the setting is recorded automatically at `CREATE` time if `version` is at least 2 (see [External target tables](#external-target-tables)) | | `tags_to_columns` | Map | {} | Map specifying which tags should be put to separate columns in the [tags](#tags-table) 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 | | `metric_families_deduplication_cache_size_bytes` | UInt64 | 10485760 | Maximum size in bytes of the deduplication cache of the [metric families](#metric-families-table) table. The cache remembers the descriptions of the metric families written recently, so they aren't written again with every insert. When the cache is full, the entries used only once are evicted first, then the least recently used ones (SLRU). Set to 0 to disable the cache, see also `metric_families_deduplication_cache_expiration_seconds` | | `metric_families_deduplication_cache_expiration_seconds` | UInt64 | 3600 | Time after which an entry of the deduplication cache of the [metric families](#metric-families-table) table expires, counted from the moment the metric family was written. So every metric family is written again at least once per this period, which limits any difference between the cache and the table. The cache is local to the server and cleared by `TRUNCATE TABLE` executed on it or by `SYSTEM DROP TIME SERIES CACHES`. Set to 0 to disable the cache | | `tags_deduplication_cache_size_bytes` | UInt64 | 104857600 | Maximum size in bytes of the deduplication cache of the [tags](#tags-table) table. The cache remembers the time series written recently, so their tags aren't written again with every insert. When the cache is full, the entries used only once are evicted first, then the least recently used ones (SLRU). The cache is used only when `store_min_time_and_max_time` is disabled, because otherwise every insert changes `min_time` and `max_time`: the default value is ignored then, and an explicit non-zero value is rejected. Set to 0 to disable the cache, see also `tags_deduplication_cache_expiration_seconds` | | `tags_deduplication_cache_expiration_seconds` | UInt64 | 3600 | Time after which an entry of the deduplication cache of the [tags](#tags-table) table expires, counted from the moment the time series was written. So every time series is written again at least once per this period, which limits any difference between the cache and the table. The cache is local to the server and cleared by `TRUNCATE TABLE` executed on it or by `SYSTEM DROP TIME SERIES CACHES`. Used only when `store_min_time_and_max_time` is disabled, see `tags_deduplication_cache_size_bytes`. Set to 0 to disable the cache | | `samples_index_granularity` | UInt64 | 32768 | Sets `index_granularity` of the inner [samples](#samples-table) 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](#tags-table) table. When set explicitly, it overrides `index_granularity` from the engine declaration. Ignored for an external tags table and a non-MergeTree engine |

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](#settings) 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 7) - 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](#version-history)). `CREATE TABLE ... AS other_table` doesn't copy the version of the other table, see [Creating a table AS existing table](#create-as). 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](/reference/functions/table-functions/prometheusQuery), [prometheusQueryRange](/reference/functions/table-functions/prometheusQueryRange), and [timeSeriesSelector](/reference/functions/table-functions/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

| Version | Changes | | - | - | | 0 | Tables created before the `version` setting was introduced, including "prealpha" tables (which declared the columns of the target tables as [outer columns](#outer-columns)) and tables without the [recent samples](#recent-samples-table) table | | 1 | The `version` setting was introduced | | 2 | The [`id_type`](#settings) setting was introduced: a table with an external tags table records the type of the `id` column in `id_type` and the expression generating identifiers in [`id_generator`](#settings), so its definition doesn't depend on the external table. `id_type` is also recorded when `id_generator` is set (see [The `id` column](#id-column)) | | 3 | The outer column `time_series` was renamed to `samples` (see [Outer columns](#outer-columns)). Tables of earlier versions keep the old name of the column, and the [prometheusQuery](/reference/functions/table-functions/prometheusQuery) and [prometheusQueryRange](/reference/functions/table-functions/prometheusQueryRange) table functions return the column under the name the table uses. The stored data didn't change | | 4 | The `metrics` target table was renamed to `metric families`: the inner table is named `.inner_id.metricfamilies.` instead of `.inner_id.metrics.`, and the definition is written with the keyword `METRIC FAMILIES` instead of `METRICS`. The stored data didn't change | | 5 | New inner tags tables with a `MergeTree` family engine get a `keyValuePairs` text index on the `tags` map by default (see [Tags table](#tags-table)) | | 6 | The column `metric_family_name` of the [metric families](#metric-families-table) table was renamed to `metric_family`, the name of the corresponding outer column. Tables of earlier versions keep the old name of the column, and the [timeSeriesMetricFamilies](/reference/functions/table-functions/timeSeriesMetricFamilies) table function returns the column under the name the table uses. An external metric families table must name the column the way the version of the `TimeSeries` table does | | 7 | The deduplication caches of the [metric families](#metric-families-table) and [tags](#tags-table) tables were introduced together with their settings (see [`metric_families_deduplication_cache_expiration_seconds`](#settings) and [`tags_deduplication_cache_expiration_seconds`](#settings)). Tables of earlier versions don't use the caches. The stored data didn't change |

Functions

Here is a list of functions supporting a `TimeSeries` table as an argument: * [timeSeriesSamples](/reference/functions/table-functions/timeSeriesSamples) * [timeSeriesTags](/reference/functions/table-functions/timeSeriesTags) * [timeSeriesMetricFamilies](/reference/functions/table-functions/timeSeriesMetricFamilies)