Why We Switched from MongoDB to ClickHouse: Lessons from a Frontend Monitoring System

MongoDB limitations for large‑scale log storage

Sharding vs. time partitioning : MongoDB only supports sharding across clusters; it lacks native time‑based partitioning, making it hard to isolate recent logs.

Index overhead : Single‑field time indexes and compound indexes improve some queries but increase storage consumption and still cannot meet latency targets for billions of rows.

Unbounded queries : Without explicit time filters, queries scan the whole collection, leading to long execution times.

Data cleanup : Deleting old documents frees logical data but does not release disk space; space is reclaimed only when a collection is dropped.

Explain analysis : explain shows that the queries are inherently slow despite correct syntax.

ClickHouse columnar architecture

ClickHouse stores each column in a separate file stream, allowing queries to read only the columns referenced in the SELECT list. This reduces I/O and improves cache efficiency compared with row‑oriented databases (e.g., MySQL, MongoDB) that must read entire rows.

-- Row storage example (MySQL/MongoDB)
id:1, name:'叶小钗', year:21
id:2, name:'子慕',   year:20
id:3, name:'大卫',   year:19

-- Column storage in ClickHouse (each column stored separately)
id   => 1,2,3
name=> '叶小钗','子慕','大卫'
year=> 21,20,19

OLTP vs. OLAP

OLTP (Online Transaction Processing) focuses on write‑heavy workloads with strong consistency.

OLAP (Online Analytical Processing) focuses on read‑heavy analytical queries.

Traditional row‑oriented databases excel at OLTP. When analytical query volume grows, they become a bottleneck, motivating the use of an OLAP‑oriented system like ClickHouse.

ClickHouse advantages for log analytics

Performance : Columnar storage and aggressive compression yield query latencies orders of magnitude lower than MongoDB for aggregation‑heavy workloads.

Space efficiency : Dropping a partition releases the underlying files, instantly freeing disk space.

Time‑based partitioning : Tables can be partitioned by month using PARTITION BY toYYYYMM(time), enabling fast deletion of old data.

Example DDL for a partitioned log table:

CREATE TABLE logs (
    time   DateTime,
    status Int32,
    ...    -- other columns
) ENGINE = MergeTree
PARTITION BY toYYYYMM(time)
ORDER BY time;

To delete a month’s worth of logs:

ALTER TABLE logs DROP PARTITION '202104';

Operational considerations

ClickHouse implements the MySQL client protocol, but it does not support prepared statements and has type‑mapping quirks.

Most MySQL ORMs (e.g., Sequelize) cannot fully manage ClickHouse schemas; raw SQL or the HTTP interface is preferred.

A lightweight Node.js ORM was built for internal use (source: https://github.com/zimv/node-clickhouse-orm, npm version 2.0.0‑alpha). It wraps the HTTP API and provides basic CRUD helpers.

LightHouse is a browser‑only GUI for ClickHouse; it runs purely client‑side without a server component.

Migration outcome

After migrating log storage from MongoDB to ClickHouse, query latency dropped from several seconds to sub‑second levels, and storage usage decreased because partitions can be dropped and reclaimed. MongoDB remains in the architecture for metadata and frequently updated small datasets, while ClickHouse handles the bulk, read‑heavy log data.