How We Replaced Elasticsearch with ClickHouse for Faster, Cheaper Log Storage

Business Background

The previous log platform at the company relied on Elasticsearch. Rapid business growth introduced new requirements: more applications, higher write volume, and longer retention for compliance. Industry peers were migrating to ClickHouse for better write performance and compression, prompting an evaluation of ClickHouse hot‑cold storage as a replacement.

ClickHouse Overview

ClickHouse is a column‑oriented DBMS designed for online analytical processing (OLAP). Columnar storage provides at least a 100× speed boost for typical queries compared with row‑oriented storage.

ClickHouse also offers high compression ratios (lz4 ≈ 1:4, zstd ≈ 1:10).

Storage Strategy in ClickHouse

ClickHouse supports table‑level TTL and storage policies, allowing automatic data expiration, compaction, and movement between disks or volumes.

1) Configure Storage Policies

<path>/data1/ClickHouse/data/</path>
<storage_configuration>
  <disks>
    <hot>
      <path>/data1/ClickHouse/hot/</path>
    </hot>
    <cold>
      <path>/data2/ClickHouse/cold/</path>
    </cold>
  </disks>
  <policies>
    <ttl>
      <volumes>
        <hot><disk>hot</disk></hot>
        <cold><disk>cold</disk></cold>
        <arch><disk>arch</disk></arch>
      </volumes>
    </ttl>
  </policies>
</storage_configuration>

Key tags: <path>: default ClickHouse data directory. <storage_configuration>: container for disks and policies. <disks>: defines physical disks (hot, cold). <hot> and <cold>: custom labels for different storage tiers. <policies> and <ttl>: define the storage policy name used by tables. <volumes>: groups disks under a policy.

2) Create Tables

CREATE TABLE db_rdsauditlog_local ON CLUSTER auditlog (
  `check_rows` Int64,
  `client_ip` String,
  `db` String,
  `fail` Int64,
  `instance_id` String,
  `latency` Int64,
  `origin_time` DateTime('Asia/Shanghai'),
  `return_rows` Int64,
  `sql` String,
  `thread_id` Int64,
  `update_rows` Int64,
  `user` String,
  `tables` Array(String),
  `sqlhash` String,
  `sqlfingerprint` String,
  `sqltype` String,
  INDEX instance_sqltype (instance_id, sqltype) TYPE set(100) GRANULARITY 5,
  INDEX origintime_sqlhash (instance_id,sqlhash) TYPE set(100) GRANULARITY 5,
  INDEX origintime_instance_clientip (instance_id, client_ip) TYPE set(100) GRANULARITY 5
) ENGINE = MergeTree
PARTITION BY toYYYYMMDD(origin_time)
ORDER BY origin_time
TTL origin_time + INTERVAL 6 hour TO DISK 'cold'
SETTINGS storage_policy = 'ttl', index_granularity = 8192;

The table specifies a TTL that moves data to the cold disk after six hours and binds the table to the ttl storage policy.

Partition Strategy

ClickHouse allows a table‑level PARTITION BY clause. While fine‑grained (hourly) partitions are possible, the official recommendation is to avoid overly small partitions because they do not speed up queries and can cause management overhead.

Business Requirements

Support day‑level retention policies for each business domain.

Store data on different media according to its age.

Choose storage media that minimize cost while meeting performance needs.

Solution Design

1) Day‑Level Retention

Option 1:

PARTITION BY (application, environment, toYYYYMMDD(log_time))

. This created a huge number of partitions (one per app‑env per day), exceeding max_partitions_per_insert_block (default 100) and max_parts_in_total (default 100 000), leading to insert failures and compaction bottlenecks.

Option 2 (adopted):

PARTITION BY (toDate(log_time), log_save_time, oss_save_time)

. The log_save_time and oss_save_time fields store the retention period (in days) for hot and cold tiers, allowing flexible movement without exploding partition counts.

2) Moving Data Between Media

Option 1: Table‑level TTL. Abandoned because changing TTL forces a full reload of all parts, saturating I/O and making the cluster unresponsive.

Option 2 (adopted): Scheduled tasks that query system.parts and move partitions based on the three partition fields. Example command:

ALTER TABLE dw_log.tb_logs_local ON CLUSTER default MOVE PARTITION XXX TO DISK 'cold';

This approach handles dynamic retention adjustments while keeping the system responsive.

3) Archive Storage Medium

We evaluated two options:

JuiceFS + OSS – rejected due to metadata consistency risks (Redis loss) and performance limits of MySQL/TiKV for metadata.

ClickHouse native OSS (S3‑compatible) – selected. ClickHouse can mount an S3/OSS bucket as a disk directly.

Configuration for the three‑tier policy (hot, cold, arch) using OSS:

<storage_configuration>
  <disks>
    <hot>
      <path>/data1/ClickHouse/hot/data/</path>
      <move_factor>0.1</move_factor>
    </hot>
    <cold>
      <path>/data2/ClickHouse/cold/data/</path>
    </cold>
    <arch>
      <type>s3</type>
      <endpoint>http://log-sh.oss-cn-xx-internal.xxx.com/xxxxxxxxxx/</endpoint>
      <access_key_id>xxxxxxxx</access_key_id>
      <secret_access_key>xxxxxx</secret_access_key>
      <metadata_path>/data1/ClickHouse/disks/s3/</metadata_path>
      <cache_enabled>true</cache_enabled>
      <data_cache_enabled>true</data_cache_enabled>
      <cache_path>/data1/ClickHouse/disks/s3/cache/</cache_path>
    </arch>
  </disks>
  <policies>
    <ttl>
      <volumes>
        <hot><disk>hot</disk></hot>
        <cold><disk>cold</disk></cold>
        <arch><disk>arch</disk></arch>
      </volumes>
    </ttl>
  </policies>
</storage_configuration>

Performance tests showed OSS write speeds up to 7 GB/s and fast cluster restarts. Using OSS reduced archival storage cost by about 66 % compared with ESSD PL0.

Storage Architecture

The final architecture combines hot ESSD PL1, cold ESSD PL0, and archive OSS disks, leveraging cloud elasticity for scaling and cost efficiency.

Conclusion

The migration from Elasticsearch to ClickHouse introduced a three‑tier hot‑cold‑archive storage model, achieved higher write throughput, and cut overall storage expenses by more than 50 %. The solution balances performance, compliance, and cost while keeping operational complexity low.