{
  "$type": "site.standard.document",
  "bskyPostRef": {
    "cid": "bafyreiezr4t2saiqtc3eg7vibvqulmthtler6owovds7ec26qqpx5yy2au",
    "uri": "at://did:plc:25rdn5elo5izoxrmtis34zuk/app.bsky.feed.post/3mpqbyexhhvk2"
  },
  "coverImage": {
    "$type": "blob",
    "ref": {
      "$link": "bafkreidmxrgzopkzv3x2l7x2vfbib4cnizo7kaytow42xwz5tngvijdcvi"
    },
    "mimeType": "image/webp",
    "size": 180078
  },
  "path": "/kanishga_subramani_49ad73/day-56-mastering-clickhouser-aggregatingmergetree-build-faster-analytics-with-pre-aggregated-data-32cf",
  "publishedAt": "2026-07-03T09:47:05.000Z",
  "site": "https://dev.to",
  "tags": [
    "clickhouse",
    "devops",
    "database",
    "dataengineering",
    "https://www.quantrail-data.com/mastering-clickhouse-aggregatingmergetree"
  ],
  "textContent": "##  Introduction\n\nAs 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.\n\nThis is where **AggregatingMergeTree** proves its value.\n\nRather 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.\n\nIn 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.\n\n#  What is AggregatingMergeTree?\n\nAggregatingMergeTree is a specialized ClickHouse® table engine designed to store **aggregate function states** instead of raw records.\n\nUnlike 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.\n\nBecause much of the computational work happens during data ingestion, dashboards and reporting applications can retrieve summarized information much more efficiently.\n\nTypical scenarios include:\n\n  * Sales reporting\n  * Website traffic analytics\n  * Financial summaries\n  * IoT sensor monitoring\n  * Business KPI dashboards\n  * Application observability metrics\n\n\n\n#  Why Use AggregatingMergeTree?\n\nImagine an online marketplace processing millions of transactions every day.\n\nA dashboard needs to display total revenue generated by each product.\n\nA typical query might look like this:\n\n\n\n    SELECT\n        product,\n        sum(amount) AS total_revenue\n    FROM sales\n    GROUP BY product;\n\n\nAlthough ClickHouse executes aggregation queries efficiently, scanning billions of records every time a dashboard refreshes eventually becomes costly.\n\nAs datasets grow larger, repeated aggregations increase:\n\n  * CPU utilization\n  * Memory consumption\n  * Disk reads\n  * Query latency\n\n\n\nAggregatingMergeTree addresses this challenge by storing aggregation states as data arrives. Instead of recalculating totals from the beginning, queries simply merge pre-computed states to produce final results.\n\nThis significantly reduces execution time for frequently accessed reports.\n\n#  How AggregatingMergeTree Works\n\nA typical aggregation pipeline consists of five stages:\n\n  1. Incoming events are written into a raw MergeTree table.\n  2. A Materialized View processes newly inserted records.\n  3. Aggregate state functions generate intermediate values.\n  4. AggregatingMergeTree stores those aggregate states.\n  5. Background merges combine compatible states automatically.\n\n\n\n\n    Application\n          │\n          ▼\n    Raw MergeTree Table\n          │\n          ▼\n    Materialized View\n          │\n          ▼\n    Aggregate States\n    (sumState(), avgState())\n          │\n          ▼\n    AggregatingMergeTree\n          │\n          ▼\n    Background Merge\n          │\n          ▼\n    sumMerge()\n          │\n          ▼\n    Analytics Dashboard\n\n\nThis architecture shifts expensive aggregation work from query execution time to data ingestion, making analytical queries much faster.\n\n#  Aggregate Functions vs. AggregatingMergeTree\n\nAlthough their names are similar, aggregate functions and AggregatingMergeTree serve different purposes.\n\nAggregate Functions | AggregatingMergeTree\n---|---\nSQL functions | Table engine\nExecute calculations during queries | Stores aggregate states\nUsed in SELECT statements | Used for summary tables\nExamples: sum(), avg(), count() | Stores AggregateFunction columns\n\nA simple way to think about it is:\n\n  * **Aggregate functions perform calculations.**\n  * **AggregatingMergeTree stores those calculations in an intermediate format that can be merged later.**\n\n\n\n#  Understanding Aggregate States\n\nAggregate states are the foundation of AggregatingMergeTree.\n\nConsider this standard query:\n\n\n\n    SELECT sum(amount)\n    FROM sales;\n\n\nThe query immediately returns a final numeric result.\n\nWith AggregatingMergeTree, ClickHouse instead stores an intermediate aggregation state using functions such as:\n\n\n\n    sumState(amount)\n\n\nThis state is not the final answer.\n\nLater, when querying the summary table, ClickHouse combines multiple stored states using:\n\n\n\n    sumMerge(total_sales)\n\n\nThink of an aggregate state as a partially completed calculation that can continue growing as new data arrives.\n\nBecause ClickHouse only merges these compact states instead of scanning every raw row, reporting becomes significantly more efficient.\n\n#  Creating the Raw Events Table\n\nLet's build a simple example that tracks website page views.\n\nFirst, create a table that stores every page visit.\n\n\n\n    CREATE TABLE page_views\n    (\n        event_date Date,\n        page String,\n        views UInt64\n    )\n    ENGINE = MergeTree\n    ORDER BY (event_date, page);\n\n\nExample data:\n\nDate | Page | Views\n---|---|---\n2026-07-01 | Home | 1\n2026-07-01 | Home | 1\n2026-07-01 | Products | 1\n2026-07-01 | Contact | 1\n2026-07-01 | Home | 1\n\nEvery page visit is stored individually inside the MergeTree table.\n\n#  Creating the Summary Table\n\nInstead of saving completed totals, the summary table stores aggregation states.\n\n\n\n    CREATE TABLE page_views_summary\n    (\n        event_date Date,\n        page String,\n        total_views AggregateFunction(sum, UInt64)\n    )\n    ENGINE = AggregatingMergeTree\n    ORDER BY (event_date, page);\n\n\nNotice that the `total_views` column uses:\n\n\n\n    AggregateFunction(sum, UInt64)\n\n\nrather than a standard numeric type.\n\nThis enables ClickHouse to merge partial aggregations automatically.\n\n#  Automatically Building Aggregates with Materialized Views\n\nTo avoid manually updating summary tables, create a Materialized View that processes every new insert.\n\n\n\n    CREATE MATERIALIZED VIEW mv_page_views\n    TO page_views_summary\n    AS\n    SELECT\n        event_date,\n        page,\n        sumState(views) AS total_views\n    FROM page_views\n    GROUP BY\n        event_date,\n        page;\n\n\nWhenever fresh data is inserted into the raw events table, the Materialized View calculates aggregation states and stores them in the AggregatingMergeTree table automatically.\n\nThis eliminates repeated aggregation work during query execution.\n\n#  Querying Aggregated Data\n\nSince the summary table contains aggregate states rather than completed values, use merge functions to retrieve final results.\n\n\n\n    SELECT\n        event_date,\n        page,\n        sumMerge(total_views) AS total_views\n    FROM page_views_summary\n    GROUP BY\n        event_date,\n        page\n    ORDER BY\n        event_date,\n        page;\n\n\nExample output:\n\nDate | Page | Total Views\n---|---|---\n2026-07-01 | Home | 35,842\n2026-07-01 | Products | 12,614\n2026-07-01 | Contact | 4,238\n\nInstead of scanning millions of individual page-view events, ClickHouse reads a compact summary table, making dashboards load much faster.\n\n#  When Should You Use AggregatingMergeTree?\n\nAggregatingMergeTree is best suited for analytical workloads where identical aggregation queries are executed repeatedly.\n\nIdeal use cases include:\n\n  * Interactive BI dashboards\n  * Daily and monthly reporting\n  * Executive KPI dashboards\n  * Website analytics\n  * Application monitoring\n  * Event and log analysis\n  * Financial reporting\n  * IoT telemetry aggregation\n\n\n\nBy storing pre-aggregated information, organizations can reduce query execution times while minimizing resource consumption.\n\n#  When It May Not Be the Right Choice\n\nAlthough powerful, AggregatingMergeTree isn't appropriate for every workload.\n\nConsider alternative engines when your application requires:\n\n  * Transactional (OLTP) processing\n  * Frequent row-level updates\n  * Point lookups\n  * Individual record modifications\n  * Highly mutable datasets\n\n\n\nFor these scenarios, standard MergeTree variants are generally a better fit.\n\n#  Common Mistakes to Avoid\n\nWhen working with AggregatingMergeTree, developers often encounter a few common pitfalls.\n\nAvoid these mistakes:\n\n  * Using `sum()` instead of `sumState()` while inserting aggregate data.\n  * Querying aggregate states without merge functions like `sumMerge()`.\n  * Defining aggregation columns as numeric types instead of `AggregateFunction`.\n  * Expecting background merges to happen instantly after every insert.\n  * Using AggregatingMergeTree for transactional workloads rather than analytical reporting.\n\n\n\nUnderstanding these concepts helps ensure accurate query results and better long-term performance.\n\n#  Best Practices\n\nTo get the most out of AggregatingMergeTree:\n\n  * Keep raw data in a MergeTree table.\n  * Automate aggregation with Materialized Views.\n  * Store only aggregation states inside summary tables.\n  * Query summary tables using merge functions.\n  * Choose appropriate sorting keys for efficient merges.\n  * Monitor background merge activity for optimal performance.\n\n\n\nThese practices help build scalable analytics pipelines capable of handling billions of records efficiently.\n\n#  Conclusion\n\nAggregatingMergeTree is one of the most powerful storage engines available in ClickHouse® for accelerating analytical workloads. By storing intermediate aggregate states instead of recalculating values from raw data, it dramatically improves reporting performance while reducing CPU, memory, and disk usage.\n\nWhen combined with Materialized Views and aggregate state functions such as `sumState()` and `sumMerge()`, it provides an elegant solution for building high-performance dashboards, business intelligence systems, monitoring platforms, and real-time analytics applications.\n\nIf your organization regularly executes the same aggregation queries over massive datasets, adopting AggregatingMergeTree can significantly reduce query latency, improve scalability, and simplify the architecture of your analytics platform.\n\nRead more ... https://www.quantrail-data.com/mastering-clickhouse-aggregatingmergetree",
  "title": "Day 56 – Mastering ClickHouse® AggregatingMergeTree: Build Faster Analytics with Pre-Aggregated Data"
}