In this post, I will introduce ClickHouse, provide a brief history, and highlight its key features.
ClickHouse is a high-performance, column-oriented SQL database management system (DBMS) designed for online analytical processing (OLAP). It is particularly well-suited for running complex analytical queries that scan billions or even trillions of rows efficiently.
The name "ClickHouse" is a combination of two concepts:
· Clickstream: Refers to the type of event-based data ClickHouse was originally designed to process at Yandex, a major Russian technology company.
· Data Warehouse: Reflects its purpose of storing and analyzing large volumes of structured data.
ClickHouse is available both as a freely available open-source database system, and a fully managed cloud service for ease of deployment and scaling.
· Open Source: Yes, ClickHouse is open source. You can explore the source code here, https://github.com/ClickHouse/ClickHouse
· Cloud Offering: Yes, ClickHouse also offers a cloud-hosted version. You can learn more about it here https://clickhouse.com/cloud
1. Brief History About Clickhouse
ClickHouse was originally developed by Yandex, the largest technology company in Russia, to power its web analytics platform called Yandex.Metrica. At the time of writing this post, Yandex.Metrica is the 4th largest web analytics platform in the world. You can see real-time usage statistics here: https://w3techs.com/technologies/overview/traffic_analysis
ClickHouse was designed to handle massive amounts of data. For example, Yandex.Metrica uses ClickHouse to manage over 13 trillion records and process more than 20 billion events every day. It allows users to generate custom reports in real-time, directly from raw, non-aggregated data.
Here’s a quick timeline of ClickHouse’s development:
· 2010: ClickHouse started as an internal experimental project within Yandex.Metrica, Yandex’s free web analytics service.
· 2012: It was deployed in production at Yandex.Metrica, where it began handling billions of events per day.
· 2016: ClickHouse was open-sourced, making its powerful analytics capabilities available to the global developer community.
· 2021: ClickHouse Inc. was founded to offer commercial support, managed cloud services, and further product development.
2. ClickHouse is a Column-Oriented Database
Databases can store data in two main ways: row-oriented or column-oriented. Understanding this difference is key to know How ClickHouse is so fast for analytical queries.
2.1 Row-Oriented Databases
In row-oriented databases (like MySQL or PostgreSQL), each row of a table is stored together on disk. This layout makes it very efficient to fetch an entire row at once.
For example, if you're looking up a customer by ID and want all their details, row-based storage works well because all columns for that customer are grouped together.
But there's a downside, if your query only needs a few columns (like just a count or a filter on one field), the database still needs to load all the other columns too, because the data is read in blocks (e.g., 4 KB at a time), and each block contains entire rows, not individual columns. So even unused data gets read into memory.
2.2 Column-Oriented Databases (like ClickHouse)
ClickHouse, on the other hand, is a column-oriented database. This means that it stores each column's values together, instead of grouping them by row. So if you have a table with 10 columns, all the values of column A are stored together, then all of column B, and so on.
This Column layout is especially good for analytics, because:
· You often only need a few columns (e.g., “average sales by region”)
· Only those columns are read from disk into memory
· You avoid reading unnecessary data, making queries much faster
However, reading full rows is a bit slower in column databases, since the row’s values are scattered across different column files.
For a deeper dive, you can refer following post.
https://self-learning-java-tutorial.blogspot.com/2024/03/column-vs-row-oriented-databases.html
2.3 Example table: Orders
Imagine you have a table like this:
|
OrderId |
CustomerName |
Product |
Quantity |
Price |
|
101 |
Ram |
Laptop |
1 |
1000 |
|
102 |
Krishna |
Phone |
2 |
600 |
|
103 |
Chamu |
Monitor |
1 |
200 |
Row-Oriented Database (e.g., MySQL, PostgreSQL)
In row-oriented databases, each entire row is stored together on disk like this.
Row 1: 101, Ram, Laptop, 1, 1000 Row 2: 102, Krishna, Phone, 2, 600 Row 3: 103, Chamu, Monitor, 1, 200
If you query:
SELECT OrderID, CustomerName FROM Orders WHERE Quantity > 1;
Even though you only need two columns, the system still reads the entire rows from disk, including the unused Product, Price, etc., because all columns are grouped together.
Column-Oriented Database (e.g., ClickHouse)
In a column-oriented database, data is stored by column, like this:
Column OrderID: 101, 102, 103
Column CustomerName: Ram, Krishna, Chamu
Column Product: Laptop, Phone, Monitor
Column Quantity: 1, 2, 1
Column Price: 1000, 600, 200
Each column is stored separately, often in compressed binary files. These files are organized in blocks (Block is the smallest chunks read from disk to memory).
For example,
|
Column |
Stored In |
|
OrderID |
/data/Orders/OrderID.bin |
|
CustomerName |
/data/Orders/CustomerName.bin |
|
Product |
/data/Orders/Product.bin |
|
Quantity |
/data/Orders/Quantity.bin |
|
Price |
/data/Orders/Price.bin |
Each of these files may be split into multiple compressed blocks, such as 64 KB or 256 KB segments, optimized for fast sequential reads.
If you run the same query:
SELECT OrderID, CustomerName FROM Orders WHERE Quantity > 1;
ClickHouse will:
· Only read the Quantity, OrderID, and CustomerName columns
· Completely skip loading Product and Price
· Read only the necessary column blocks, making the query much faster
References
https://clickhouse.com/docs/about-us/history
Previous Next Home
No comments:
Post a Comment