ClickHouse is a powerful columnar database designed for extremely fast analytics over massive datasets. However, like every technology optimized for a specific use case, it comes with trade-offs. In this post, we will explore some of the features of ClickHouse that might be seen as disadvantages by those used to traditional relational databases.
· The absence of full-fledged transactions.
· The inability to modify or delete data at high rates with low latency.
· The performance challenges of using a sparse index for point queries.
Understanding these limitations can help you to decide if ClickHouse is the right tool for your needs or if you need to plan around these issues.
No Full-Fledged Transactions
ClickHouse does not support ACID transactions (like commit/rollback in MySQL/PostgreSQL). In many traditional databases (like MySQL or PostgreSQL), transactions are used to guarantee that a series of operations are executed atomically (all or nothing). This means you can roll back multiple changes if something goes wrong.
ClickHouse does not offer full ACID (Atomicity, Consistency, Isolation, Durability) transactions. This means, you cannot start a transaction, perform multiple operations, and then commit or roll back all at once.
Always remember that, ClickHouse is optimized for read-heavy analytics, not OLTP-style operations (which need strict consistency and rollback).
Limited Support for Real-Time Updates or Deletes
In many OLTP RDBMS systems, you can update or delete records on the fly with low latency. But ClickHouse is not optimized for frequent row-level updates or deletes with low latency. The data is stored in columnar format, and records are appended in immutable parts (files). Updating or deleting rows requires rewriting those files, which is costly.
ClickHouse is optimized for fast inserts and read-heavy analytical workloads rather than rapid updates or deletes. It supports batch deletes and updates which are designed more for cleanup or regulatory compliance (e.g., GDPR) than for real-time modifications. This design is not a problem for systems that mostly append data, such as logging or analytics. But if your workload requires immediate, individual record updates or deletions, ClickHouse might not be ideal.
Sparse Index Limitations for Point Queries
ClickHouse uses sparse indexes, which makes it fast for large scans but slow for pinpointing single rows. Let me try to cover Sparse Index at high level.
A sparse index does not store an entry for every single row in a table. Instead, it stores information once per block of rows, for example, every 8,000 rows or so. This block size is configurable (via index_granularity in ClickHouse). The idea is that each index entry covers a "chunk" of data (called a granule), and ClickHouse uses the index to figure out which chunks might be relevant for a query.
What is Granule?
When you insert data into ClickHouse, it's stored in parts, which are further split into granules. A granule typically consists of a fixed number of rows (by default, 8192 rows). During query execution, ClickHouse reads and filters granules, not individual rows.
For example, assume we have a users table with 10 million rows, sorted by a column like created_date, but we’re querying it by id.
Here’s a simplified view of how the data might be stored with a sparse index:
Granule 1: rows 0-8191 Granule 2: rows 8192-16383 Granule 3: rows 16384-24575 ...
ClickHouse creates index entries only at the beginning of each granule, not for every row. Now when you execute a pinpoint query like below.
SELECT * FROM users WHERE id = 123456;
ClickHouse’s sparse index does not know the exact position of id = 123456. Instead, it can only say which granule contain this.
So what happens?
· ClickHouse looks at each granule’s metadata.
· It understand which granule might possibly contain id = 123456.
· It then scans those granule to check every row manually.
References
https://clickhouse.com/docs/about-us/distinctive-features
Previous Next Home
No comments:
Post a Comment