标签：#ClickHouse

Momentic, the company behind an AI-driven software testing platform, recently rearchitected its caching system to handle over 2 million queries per day across 20 billion total entries, while maintaining an average response latency of around 250 ms. This improvement was made possible by transitioning from PostgreSQL to the column-oriented database ClickHouse. By Sergio De Simone

Introduction As data volumes continue to grow, running aggregation queries directly on raw datasets becomes increasingly expensive. Business dashboards, analytics platforms, and reporting systems often execute the same calculations repeatedly—such as total sales, daily active users, page views, or revenue trends. While ClickHouse® is designed to process analytical workloads at remarkable speed, repeatedly scanning billions of records still consumes valuable CPU, memory, and storage resources. This is where AggregatingMergeTree proves its value. Rather than calculating aggregates every time a query is executed, AggregatingMergeTree stores intermediate aggregation states that are merged automatically in the background. This approach allows analytical queries to read compact, pre-aggregated datasets, resulting in dramatically faster response times and reduced infrastructure costs. In this guide, you'll learn how AggregatingMergeTree works, why aggregate states matter, how to build an automated aggregation pipeline using Materialized Views, and when this engine is the right choice for your ClickHouse® workloads. What is AggregatingMergeTree? AggregatingMergeTree is a specialized ClickHouse® table engine designed to store aggregate function states instead of raw records. Unlike the standard MergeTree engine, which stores every inserted row, AggregatingMergeTree keeps partially aggregated values that ClickHouse combines during background merge operations. This significantly reduces the amount of data that must be processed when generating analytical reports. Because much of the computational work happens during data ingestion, dashboards and reporting applications can retrieve summarized information much more efficiently. Typical scenarios include: Sales reporting Website traffic analytics Financial summaries IoT sensor monitoring Business KPI dashboards Application observability metrics Why Use AggregatingMergeTree? Imagine an online marketplace processing millions of tr

Introduction As applications grow, traditional relational databases such as MySQL may struggle with analytical workloads involving millions of records and complex aggregations. While MySQL excels at Online Transaction Processing (OLTP), ClickHouse® is purpose-built for Online Analytical Processing (OLAP), enabling lightning-fast analytical queries on massive datasets. Migrating data from MySQL to ClickHouse® allows organizations to build high-performance reporting systems, dashboards, and real-time analytics without impacting transactional workloads. In this guide, you'll learn several approaches to migrate data from MySQL to ClickHouse®, along with their advantages, limitations, and ideal use cases. Why Migrate from MySQL to ClickHouse®? MySQL and ClickHouse® are designed for different workloads. Feature MySQL ClickHouse® Storage Model Row-based Columnar Best For Transactions (OLTP) Analytics (OLAP) Query Speed Fast for row lookups Extremely fast for large scans Aggregation Performance Moderate Extremely fast Scalability Primarily Vertical Optimized for analytical scaling Typical Use Cases Applications and transactional systems Reporting, dashboards, and analytics Migrating from MySQL to ClickHouse® makes sense when: Analytical queries are becoming slow in MySQL. You need real-time dashboards over large datasets. Reporting queries are impacting your production database. You regularly process millions or billions of rows. Migration Architecture MySQL │ ▼ Export / Synchronization │ ▼ Data Transformation │ ▼ ClickHouse® │ ▼ Dashboards / Analytics Migration Methods There are multiple ways to migrate data depending on your requirements. Method 1: CSV Export and Import (Recommended for Beginners) This is the simplest approach for performing a one-time migration of historical data. Step 1: Export Data from MySQL Run the following command inside MySQL: SELECT * INTO OUTFILE '/tmp/employees.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' \n ' FROM employ

Denormalization has been the standard approach to analytical data modeling for good reason. Moving joins, lookups, and business rules out of query time and into ingestion gives you the fastest possible reads for a known access pattern. For most of the past decade, it was often the practical default for latency-sensitive analytics. Earlier columnar engines and distributed query processors could execute joins, but many workloads paid for them through higher latency, higher compute cost, spill-to-disk, or distributed coordination overhead. That constraint has loosened. Modern columnar databases with advanced join algorithms have reduced the cost of runtime joins enough that normalization is now a genuinely viable option for many analytical workloads. Denormalization still delivers faster reads, but normalization can bring operational benefits: simpler pipelines, flexible schemas, and cleaner governance. Engineers can now make the decision based on their actual workload characteristics, rather than being forced into one approach by engine limitations. This guide is a decision framework for making that choice in ClickHouse. It starts with why denormalization became the default, explains what has changed in join performance, then compares the tradeoffs on both sides so you can decide where to denormalize, where to join, and where to use ClickHouse primitives that bridge the gap. For a broader evaluation framework covering latency, concurrency, ingest throughput, SQL flexibility, and cost across real-time OLAP options, see our guide to choosing a database for real-time analytics in 2026 . For a deeper comparison of how ClickHouse executes star schema joins against Druid, Pinot, and cloud DWHs, see our star schema and fast joins guide . TL;DR Denormalization and normalization are both valid modeling strategies. The right choice depends on your workload. Denormalization's tradeoffs are primarily operational : pipeline complexity, write-path overhead, data freshness lag, back

Introduction When a query runs in ClickHouse®, the database does much more than simply read data and return results. Before execution begins, ClickHouse® parses the SQL statement, analyzes it, applies optimizations, and builds an execution plan that determines the most efficient way to process the query. Understanding query execution plans is one of the most valuable skills for anyone working with ClickHouse®. They provide visibility into how queries are executed, helping you identify bottlenecks, validate optimization efforts, and troubleshoot performance issues. In this article, we'll explore how ClickHouse® generates execution plans, the different EXPLAIN modes, and how to interpret them for better query optimization. Why Query Execution Plans Matter A SQL query defines what data you want, but it doesn't explain how the database retrieves it. Consider the following query: SELECT country , count () FROM events GROUP BY country ; Although the query looks simple, ClickHouse® must determine: Which data parts to read Whether primary indexes can reduce the scan If data skipping indexes can be used How aggregation should be performed Whether parallel execution is possible How intermediate results should be merged A query execution plan provides answers to these questions, making it an essential tool for performance tuning. The ClickHouse Query Lifecycle Every query passes through several stages before producing results. The lifecycle typically looks like this: SQL Query │ ▼ Parser │ ▼ Analyzer │ ▼ Optimizer │ ▼ Query Plan │ ▼ Execution Pipeline │ ▼ Results Each stage plays an important role: Parser validates SQL syntax. Analyzer resolves tables, columns, and expressions. Optimizer applies query optimizations. Query Plan determines the logical execution steps. Pipeline distributes work across multiple threads. Execution processes the data and returns the results. Understanding this workflow makes execution plans much easier to interpret. Introducing the EXPLAIN Statement

ClickHouse HTTP API: A Complete Beginner's Guide Introduction When most people think about interacting with a database, they usually imagine connecting through a database client or application. However, ClickHouse also provides a simple and powerful HTTP API that allows you to query and manage your database using standard HTTP requests. The ClickHouse HTTP API provides a universal interface for communicating with your ClickHouse server. Since almost every programming language and automation tool supports HTTP, it becomes an excellent choice for integrations, monitoring, scripting, and lightweight applications. In this guide, you'll learn what the ClickHouse HTTP API is, why it's useful, and how to perform common database operations using simple HTTP requests. What Is the ClickHouse HTTP API? The ClickHouse HTTP API is a built-in interface that enables clients to communicate with a ClickHouse server using the HTTP protocol. Instead of connecting through the native TCP protocol, you simply send HTTP requests and receive responses in formats such as JSON, CSV, TSV, XML, or plain text. The HTTP interface is: Language agnostic Easy to integrate with web applications Firewall friendly Simple to test using tools like cURL, Postman, or a web browser Because of its simplicity, the HTTP API is widely used for automation, dashboards, data pipelines, and monitoring systems. Why Use the HTTP API? The ClickHouse HTTP API offers several advantages: No dedicated database driver is required. Works with virtually every programming language. Easy integration with REST-based applications. Supports multiple output formats. Ideal for automation and scripting. Perfect for cloud-native applications and microservices. Common Operations Using the HTTP API, you can: Execute SQL queries Insert data Create and modify tables Retrieve query results Export data in different formats Automate database operations Authentication Options ClickHouse supports multiple authentication methods when using th

Time-series data is one of the most common types of data generated by modern applications. Every log entry, API request, metric, transaction, sensor reading, or user interaction is recorded with a timestamp, making time the primary dimension for analysis. As organizations collect billions of these records, efficiently storing and querying them becomes increasingly challenging. This is where ClickHouse® excels. Although ClickHouse is not a dedicated time-series database, its columnar storage architecture, vectorized query execution, high compression ratios, and massively parallel processing make it an excellent choice for time-series analytics at scale. It is capable of ingesting large volumes of data while delivering analytical queries in milliseconds. The article begins by explaining the fundamentals of time-series data and highlighting common real-world use cases such as application monitoring, IoT sensor data, financial market analysis, server metrics, user activity tracking, and business analytics. These workloads typically involve continuous data ingestion, time-based filtering, aggregations, and trend analysis. One of ClickHouse's biggest strengths is its optimization for analytical workloads. Since data is stored column-wise rather than row-wise, only the required columns are read during query execution. Combined with compression and vectorized processing, this significantly reduces I/O and improves query performance over massive datasets. The article also demonstrates how to create an optimized table for time-series workloads using the MergeTree engine. Proper partitioning by month and ordering data by dimensions and timestamps help ClickHouse prune unnecessary partitions and efficiently locate relevant data during queries. Several practical SQL examples are covered, including: Filtering records within a specific time range Aggregating metrics by hour, day, week, or month Calculating averages, sums, minimums, and maximums Grouping events over time Working wi

One thing that confused me when I first started learning ClickHouse was the word FINAL . Because eventually you'll come across both: SELECT * FROM events FINAL ; and: OPTIMIZE TABLE events FINAL ; At first glance, they sound like they should do roughly the same thing. After all, both contain the word FINAL . But they actually solve two completely different problems. One affects query results. The other affects how data is physically stored. Understanding this distinction can save a lot of confusion when working with MergeTree tables. Why This Confusion Happens Most people encounter FINAL while working with engines like: ReplacingMergeTree SummingMergeTree AggregatingMergeTree Sooner or later they notice something like: SELECT * FROM users ; returns duplicate versions of rows. Then they discover: SELECT * FROM users FINAL ; and suddenly the results look correct. Naturally, many people assume: FINAL merges the table. But that's not exactly what is happening. What SELECT FINAL Actually Does When you run: SELECT * FROM users FINAL ; ClickHouse applies merge logic during query execution. Think of it as: "Show me what the table would look like if all relevant merges had already happened." The important part: It only affects the query result. After the query finishes: parts remain unchanged storage remains unchanged nothing is rewritten on disk The merge logic happens temporarily while the query is running. Once the query completes, the table is exactly as it was before. What OPTIMIZE FINAL Actually Does Now let's look at: OPTIMIZE TABLE users FINAL ; This is a completely different operation. Instead of modifying query results, ClickHouse physically merges parts on disk. The operation: rewrites data merges eligible parts removes obsolete versions creates larger merged parts Unlike SELECT FINAL , the effects remain after the command completes. This is a storage operation, not a query operation. The Simplest Way to Remember It Whenever I think about these commands, I use a v

CSV files are one of the most common formats for storing and exchanging data. Whether you’re working with logs, analytics data, application exports, or reports, there will likely come a time when you need to load CSV data into ClickHouse®. The good news is that ClickHouse® makes CSV ingestion straightforward and efficient. In this guide, you’ll learn how to create a table, prepare a CSV file, load CSV data into ClickHouse®, and verify that the data has been imported successfully. Why Use CSV Files with ClickHouse®? CSV (Comma-Separated Values) files are simple, portable, and supported by virtually every data platform. Common use cases include: Importing exported application data Loading historical datasets Migrating data from other databases Testing analytics workloads Sharing data between systems Because ClickHouse® is designed for high-performance analytics, it can efficiently process and query large CSV datasets once they are loaded into a table. Sample CSV File Let’s assume we have a file named employees.csv with the following contents: id,name,department,salary 1,Alice,Engineering,75000 2,Bob,Marketing,60000 3,Charlie,Finance,70000 This simple dataset will help demonstrate how to load CSV data into ClickHouse®. Step 1: Create a Table in ClickHouse® Before importing data, create a table that matches the structure of the CSV file. CREATE TABLE employees ( id UInt32, name String, department String, salary UInt32 ) ENGINE = MergeTree() ORDER BY id; This table contains four columns that correspond directly to the columns in our CSV file. Step 2: Load CSV Data into ClickHouse® There are several ways to import CSV data, but one of the most common methods is using the ClickHouse® client. Run the following command: clickhouse-client --query=" INSERT INTO employees FORMAT CSVWithNames" < employees.csv The CSVWithNames format tells ClickHouse® that the first row contains column headers. After executing the command, ClickHouse® will read the CSV file and insert the records

Cloudflare recently described how a slowdown in its billing pipeline was traced to contention inside the query planning stage of ClickHouse. The team profiled the bottleneck and patched ClickHouse to replace an exclusive lock with a shared lock, drop the per-query copy of the parts list, and improve part filtering. By Renato Losio

The database provider is eyeing a public debut within the next few years.