ClickHouse Time-Series Table Engine

Introduction

As you know, ClickHouse is an efficient OLAP database with an optimized storage engine called MergeTree. This storage engine is thoughtfully designed so that it can fulfil wide range of OLAP use-cases. One of the OLAP use-case where ClickHouse really shines is “Observability”. Interesting part of this requirement is the amount of observability data grows very fast, faster than any other component of the infrastructure.

ClickHouse has nearly become the de-facto standard for storing logs, traces and profiles. More and more companies are moving away from paid solutions like Datadog & adopting ClickHouse to stop worrying about high bills. Metrics is one of the use-case where one will find more ready-made open-source solutions like Prometheus & VictoriaMetrics to get adopted which support query languages like Promql & Metricsql.

ClickHouse recently started adding support as storage engine for prometheus data using Prometheus remote-read and remote-write. Although the pull request is still in development, but I want to give it a try and see how it works.

ClickHouse Time-Series Table Engine

In order to try it, I built the pull request branch and ran ClickHouse locally.

Table Structure

In order to find the table schema, I ran the default query to create a table of TimeSeries table engine.

:) CREATE TABLE tbl
ENGINE = TimeSeries

Ok.

:) SHOW TABLES

┌─name───────────────────────────────────────────────────┐
│ .inner_id.data.8dcdfe2c-6d23-40ff-95ab-ab2194632df8    │
│ .inner_id.metrics.8dcdfe2c-6d23-40ff-95ab-ab2194632df8 │
│ .inner_id.tags.8dcdfe2c-6d23-40ff-95ab-ab2194632df8    │
│ tbl                                                    │
└────────────────────────────────────────────────────────┘

It creates 4 tables:

• One table of TimeSeries engine.
• Two tables of ReplacingMergeTree engine.
• One table of MergeTree engine.

:) SELECT
    database,
    `table`,
    engine
FROM system.tables
WHERE database = 'default'

Row 1:
──────
database: default
table:    .inner_id.data.8dcdfe2c-6d23-40ff-95ab-ab2194632df8
engine:   MergeTree

Row 2:
──────
database: default
table:    .inner_id.metrics.8dcdfe2c-6d23-40ff-95ab-ab2194632df8
engine:   ReplacingMergeTree

Row 3:
──────
database: default
table:    .inner_id.tags.8dcdfe2c-6d23-40ff-95ab-ab2194632df8
engine:   ReplacingMergeTree

Row 4:
──────
database: default
table:    tbl
engine:   TimeSeries

Table Data

ClickHouse is keeping one table each for metrics, tags and data associated with a timeseries. Let peek into one row of each table.

Row 1:
──────
metric_family_name: avalanche_metric_mmmmm_0_0
type:               gauge
unit:
help:               A tasty metric morsel

Row 1:
──────
id:          8184567531674041610266329044151429916
metric_name: avalanche_metric_mmmmm_0_0
tags:        {'cycle_id':'0','instance':'192.168.1.120:9001','job':'avalanche','label_key_kkkkk_0':'label_val_vvvvv_0','label_key_kkkkk_1':'label_val_vvvvv_1','label_key_kkkkk_2':'label_val_vvvvv_2','label_key_kkkkk_3':'label_val_vvvvv_3','label_key_kkkkk_4':'label_val_vvvvv_4','label_key_kkkkk_5':'label_val_vvvvv_5','label_key_kkkkk_6':'label_val_vvvvv_6','label_key_kkkkk_7':'label_val_vvvvv_7','label_key_kkkkk_8':'label_val_vvvvv_8','label_key_kkkkk_9':'label_val_vvvvv_9','series_id':'3'}

Row 1:
──────
id:        1252532402811689293038598621726589
timestamp: 2024-06-09 11:47:28.306
value:     31