{"id":2764,"date":"2026-10-15T23:33:50","date_gmt":"2026-10-16T06:33:50","guid":{"rendered":"https:\/\/www.virendrachandak.com\/techtalk\/?p=2764"},"modified":"2026-10-15T23:34:02","modified_gmt":"2026-10-16T06:34:02","slug":"clickhouse-mergetree-explained","status":"publish","type":"post","link":"https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-mergetree-explained\/","title":{"rendered":"ClickHouse MergeTree: ORDER BY, Partitioning, and the Sparse Index"},"content":{"rendered":"\n

ClickHouse’s workhorse table engine is MergeTree<\/strong>. Almost every table uses it or one of its variants, and three things determine whether queries are fast or slow: ORDER BY<\/code>, PARTITION BY<\/code>, and the sparse primary index they produce.<\/p>\n\n\n\n

The previous post<\/a> covers what a MySQL developer needs to know before using ClickHouse; this one covers how its main table engine stores and reads data.<\/p>\n\n\n\n

Why it’s called MergeTree<\/h2>\n\n\n\n

Every insert creates a new immutable part<\/strong> \u2014 a self-contained directory of sorted, compressed column files on disk. ClickHouse never edits existing parts in place.<\/p>\n\n\n\n

Left unchecked, inserts would produce thousands of tiny parts. A background process continuously merges<\/strong> small parts into larger ones, keeping the part counts low and the data well-sorted. That merge process is the “merge” in MergeTree, and it follows the same log-structured design used in RocksDB and Cassandra.<\/p>\n\n\n\n

Two consequences fall straight out of this design:<\/p>\n\n\n\n

    \n
  • Batched inserts<\/strong> \u2014 One insert creates one part. Row-by-row inserts cause part explosion and eventually the Too many parts<\/code> error.<\/li>\n\n\n\n
  • Timing matters<\/strong> \u2014 Deduplication and aggregation in special engines happen during merges<\/em>, not at insert time.<\/li>\n<\/ul>\n\n\n\n

    ORDER BY: The sorting key<\/h2>\n\n\n\n

    ORDER BY<\/code> defines the physical sort order<\/strong> of rows within every part. It is not a secondary structure like a MySQL index \u2014 it is<\/em> the on-disk layout.<\/p>\n\n\n

    CREATE TABLE events\n(\n    tenant_id   UInt32,\n    event_type  LowCardinality(String<\/span>),\n    event_time  DateTime,\n    user_id     UInt64,\n    amount      Decimal(10<\/span>, 2<\/span>)\n)\nENGINE = MergeTree\nPARTITION BY toYYYYMM(event_time)\nORDER BY (tenant_id, event_type, event_time);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Because the data is physically sorted by (tenant_id, event_type, event_time)<\/code>, a query filtered on tenant_id<\/code> \u2014 or tenant_id<\/code> and event_type<\/code> \u2014 can skip straight to the relevant region of disk. A query filtered only on user_id<\/code> (which appears nowhere in the sorting key) must scan everything.<\/p>\n\n\n\n
    Three rules for choosing a good ORDER BY<\/code>:<\/p>\n\n\n\n
      \n
    1. Lead with the most frequently filtered columns.<\/strong> The sorting key works left-to-right, just like a composite index in MySQL.<\/li>\n\n\n\n
    2. Generally go from lower cardinality to higher cardinality.<\/strong> Putting tenant_id<\/code> (few values) before event_time<\/code> (many values) tends to keep similar rows adjacent, improving both index effectiveness and compression. However, actual query patterns matter more than cardinality alone, so optimize first for the columns that appear most often in the WHERE<\/code> clause.<\/li>\n\n\n\n
    3. Compression depends on sort order.<\/strong> Sorted, similar data compresses dramatically better \u2014 often 5-20x.<\/li>\n<\/ol>\n\n\n\n
      Choosing ORDER BY in practice<\/h3>\n\n\n\n
      The best ORDER BY<\/code> is usually neither the unique key nor the lowest-cardinality column, but the set of columns that most closely matches how the table is actually filtered. The useful question is not “what uniquely identifies a row?” but “what appears most often in the WHERE<\/code> clause?” In each pattern below, the sorting key simply follows the filters:<\/p>\n\n\n
      -- User activity: one tenant, then a time range\n-- WHERE tenant_id = ? AND event_time BETWEEN ...\nORDER BY (tenant_id, event_time)\n\n-- Event tracking: adds an event-type filter\n-- WHERE tenant_id = ? AND event_type = ? AND event_time BETWEEN ...\nORDER BY (tenant_id, event_type, event_time)\n\n-- User-centric access: one user, then a time range\n-- WHERE user_id = ? AND event_time BETWEEN ...\nORDER BY (user_id, event_time)<\/code><\/span><\/pre>\n\n\n
      The point is not uniqueness; it is letting the sparse index eliminate as much data as possible before anything is read from disk.<\/p>\n\n\n\n
      PRIMARY KEY vs ORDER BY<\/h2>\n\n\n\n
      Coming from MySQL, this is the part that trips people up. In ClickHouse:<\/p>\n\n\n\n
        \n
      • ORDER BY<\/code> controls how data is sorted on disk.<\/li>\n\n\n\n
      • PRIMARY KEY<\/code> controls what goes into the sparse index \u2014 and when it is not specified, it defaults to the ORDER BY<\/code><\/strong>. Most tables never declare PRIMARY KEY<\/code> at all.<\/li>\n\n\n\n
      • Neither one enforces uniqueness<\/strong>. There is no duplicate-key error.<\/li>\n<\/ul>\n\n\n\n
        The two are separated only to get a shorter index key while still sorting on more columns for compression:<\/p>\n\n\n
        PRIMARY KEY (tenant_id, event_type)\nORDER BY (tenant_id, event_type, event_time)<\/code><\/span><\/pre>\n\n\n
        The PRIMARY KEY<\/code> must always be a prefix of ORDER BY<\/code>.<\/p>\n\n\n\n
        The sparse index: Not a B-tree \u2014 a table of contents<\/h2>\n\n\n\n
        A MySQL B-tree points to individual rows<\/em>. ClickHouse’s primary index is coarser and far cheaper.<\/p>\n\n\n\n
        Each part is divided into granules<\/strong> \u2014 blocks of rows, 8,192 by default (index_granularity<\/code>). ClickHouse stores one index entry (a mark<\/strong>) for the first row of each granule. That means roughly one mark per 8,192 rows. On a billion-row table that’s about 120,000 marks, small enough to keep in memory.<\/p>\n\n\n\n
        It works like the table of contents in a book: it does not give the exact page a sentence is on, but it points to the right chapter<\/em>.<\/p>\n\n\n\n
        Take a query that filters on the sorting key:<\/p>\n\n\n
        SELECT * FROM events WHERE tenant_id = 42;<\/code><\/span><\/pre>\n\n\n
        ClickHouse resolves it in three steps:<\/p>\n\n\n\n
          \n
        1. Binary-searches the marks to find granules that might<\/em> contain tenant_id = 42<\/code>.<\/li>\n\n\n\n
        2. Reads only those granules off disk \u2014 and only the columns the query needs.<\/li>\n\n\n\n
        3. Scans a few thousand rows linearly to find exact matches.<\/li>\n<\/ol>\n\n\n\n
          This is why ClickHouse is well suited to “give me everything for tenant 42 in March” and poorly suited to “give me the one row where user_id = 999” if user_id<\/code> isn’t in the sorting key \u2014 the latter forces a full scan because the table of contents is useless for it.<\/p>\n\n\n\n
          When a filter column isn’t in ORDER BY<\/h3>\n\n\n\n
          A natural question for anyone coming from MySQL is what happens to a query like SELECT * FROM events WHERE user_id = 12345<\/code> when user_id<\/code> is not part of the sorting key. The sparse index cannot help: the marks describe only the sorting-key columns, so ClickHouse has to scan every candidate granule.<\/p>\n\n\n\n
          That is not automatically slow \u2014 ClickHouse is built for large analytical scans \u2014 but the engine loses its most effective skipping mechanism. When a frequently filtered column cannot reasonably go into ORDER BY<\/code>, data-skipping indexes (minmax<\/code>, set<\/code>, and bloom-filter) can prune granules on that column instead. They are a secondary optimization, worth adding only after the sorting key itself is right.<\/p>\n\n\n\n
          Granularity tuning<\/h3>\n\n\n\n
            \n
          • Smaller granules means a bigger, more precise index (better for point-ish lookups).<\/li>\n\n\n\n
          • Larger granules means a smaller index and less overhead (better for big scans).<\/li>\n<\/ul>\n\n\n\n
            The default of 8,192 is right for almost every table; it is best left alone without a measured reason to change it. Modern ClickHouse also uses adaptive index granularity<\/strong> by default: rather than fixing every granule at 8,192 rows, it creates marks more frequently when rows are physically large, keeping the amount of data behind each mark, and therefore memory usage, predictable.<\/p>\n\n\n\n
            How the pieces fit together<\/h2>\n\n\n\n
            The whole layout nests: a table splits into partitions, each partition into immutable parts, and each part holds rows sorted by ORDER BY<\/code> and grouped into granules that the sparse index marks:<\/p>\n\n\n
            Table\n|\n+-- Partition (202607)                -- one dir per PARTITION BY value\n|   +-- Part (immutable; rows sorted by ORDER BY)\n|   |   +-- Granule: rows 1-8192      -- 1 mark: ORDER BY value of row 1\n|   |   +-- Granule: rows 8193-16384  -- 1 mark: ORDER BY value of row 8193\n|   |   +-- ...\n|   +-- Part\n|\n+-- Partition (202608)\n    +-- Part<\/code><\/span><\/pre>\n\n\n
            Thinking in terms of partitions, parts, granules, and marks makes most MergeTree behavior easier to reason about.<\/p>\n\n\n\n
            PARTITION BY: For management, not speed<\/h2>\n\n\n\n
            PARTITION BY<\/code> splits the table into separate directories on disk \u2014 one per partition value. With PARTITION BY toYYYYMM(event_time)<\/code>, all of August 2026’s data lives in its own partition, separate from July’s.<\/p>\n\n\n\n
            The biggest misconception is that partitioning is a query-speed feature. It’s mostly a data-management<\/strong> feature:<\/p>\n\n\n\n
              \n
            • Instant bulk deletes.<\/strong> ALTER TABLE events DROP PARTITION '202601'<\/code> drops a whole month in milliseconds by deleting directories \u2014 no row-by-row mutation.<\/li>\n\n\n\n
            • TTL and tiered storage.<\/strong> Expire old partitions automatically, or move them to cheaper disks.<\/li>\n\n\n\n
            • Partition pruning.<\/strong> A query filtered on the partition key does skip irrelevant partitions \u2014 but this is a bonus, not the main event. The ORDER BY<\/code> does the heavy lifting for query speed.<\/li>\n<\/ul>\n\n\n\n
              The pitfall: over-partitioning<\/h3>\n\n\n\n
              Merges only happen within<\/em> a partition, and each partition needs its own parts. Partitioning by day on a high-traffic table produces thousands of tiny partitions, part explosion, and slow everything.<\/p>\n\n\n\n
              A workable guideline is a handful to a few hundred partitions<\/strong>, each holding a meaningful amount of data. Monthly is the safe default, and a high-cardinality column like user_id<\/code> should never be the partition key.<\/p>\n\n\n\n
              The MergeTree family: Engines that transform data during merges<\/h2>\n\n\n\n
              Plain MergeTree<\/code> just stores rows. Its siblings use the merge process to transform data, deduplicating or pre-aggregating by the ORDER BY<\/code> key:<\/p>\n\n\n\n
                \n
              • ReplacingMergeTree<\/code><\/strong> \u2014 keeps only the latest row per ORDER BY<\/code> key. The closest thing to an upsert, but deduplication happens during merges<\/em>, so a query needs FINAL<\/code> to guarantee it at read time.<\/li>\n\n\n\n
              • SummingMergeTree<\/code><\/strong> \u2014 collapses rows with the same key, summing the numeric columns. Great for counters.<\/li>\n\n\n\n
              • AggregatingMergeTree<\/code><\/strong> \u2014 stores partial aggregate states; used for incremental materialized-view rollups.<\/li>\n\n\n\n
              • CollapsingMergeTree<\/code> \/ VersionedCollapsingMergeTree<\/code><\/strong> \u2014 model updates and deletes via sign rows that cancel each other out.<\/li>\n<\/ul>\n\n\n\n
                For most tables, plain MergeTree<\/code> is the right choice; the variants earn their place only when a table needs deduplication (Replacing<\/code>), rollups (Aggregating<\/code>), running counters (Summing<\/code>), or sign-based collapsing (Collapsing<\/code>).<\/p>\n\n\n\n
                Inspecting the layout with system tables<\/h2>\n\n\n\n
                Everything above is observable. system.parts<\/code> shows exactly how a table is laid out \u2014 partitions, parts, row counts, and compressed vs. uncompressed size:<\/p>\n\n\n
                SELECT\n    partition,\n    count() AS<\/span> parts,\n    sum(rows) AS<\/span> rows,\n    formatReadableSize(sum(data_compressed_bytes))   AS<\/span> compressed,\n    formatReadableSize(sum(data_uncompressed_bytes)) AS<\/span> uncompressed\nFROM system.parts\nWHERE table = 'events'<\/span> AND<\/span> active = 1<\/span>\nGROUP BY partition\nORDER BY partition;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
                The parts<\/code> column is the one to watch: if a single partition has hundreds of active parts, inserts are too small or too frequent. Comparing compressed<\/code> to uncompressed<\/code> shows the ORDER BY<\/code> compression at work \u2014 a good sorting key routinely gives 5-20x.<\/p>\n\n\n\n
                To see how well a specific query uses the index, prefixing it with EXPLAIN indexes = 1<\/code> makes ClickHouse report how many granules it read versus the total \u2014 the truest measure of whether the ORDER BY<\/code> fits the query.<\/p>\n\n\n\n
                A MergeTree design checklist<\/h2>\n\n\n\n
                Before a table goes to production, a few questions catch most design mistakes:<\/p>\n\n\n\n
                  \n
                • Does ORDER BY<\/code> match the most common WHERE<\/code> clauses?<\/li>\n\n\n\n
                • Are the low-cardinality filter columns placed early in the key?<\/li>\n\n\n\n
                • Is PARTITION BY<\/code> chosen for lifecycle management rather than query speed?<\/li>\n\n\n\n
                • Will inserts arrive in reasonably sized batches?<\/li>\n\n\n\n
                • Will the number of partitions stay manageable over time?<\/li>\n\n\n\n
                • Do any frequent queries filter on columns absent from ORDER BY<\/code>?<\/li>\n<\/ul>\n\n\n\n
                  Clear answers to those questions usually mean the table design is on the right track.<\/p>\n\n\n\n
                  Putting it all together<\/h2>\n\n\n\n
                  A well-designed MergeTree table comes down to three decisions \u2014 ORDER BY<\/code>, PARTITION BY<\/code>, and insert batching \u2014 and together they decide how it lays out data on disk, how well it compresses, and how much work a query has to do. With those three right, the sparse index, compression, and background merges do the rest. The common failure modes are the mirror image: a sorting key that ignores the query patterns, partitioning by day, and single-row inserts.<\/p>\n\n","protected":false},"excerpt":{"rendered":"
                  MergeTree is the engine behind almost every ClickHouse table. A close look at what ORDER BY actually does, how PRIMARY KEY differs from it, how the sparse index turns range scans into fast lookups, and where partitioning fits in.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[174],"tags":[175,177,184,181,183,178,182,176],"class_list":["post-2764","post","type-post","status-publish","format-standard","hentry","category-clickhouse","tag-clickhouse","tag-data-engineering","tag-database-indexing","tag-database-performance","tag-mergetree","tag-olap","tag-partitioning","tag-sql"],"yoast_head":"\nClickHouse MergeTree: ORDER BY, Partitioning, and the Sparse Index - Virendra's TechTalk<\/title>\n<meta name=\"description\" content=\"How ClickHouse MergeTree really works: ORDER BY vs PRIMARY KEY, the sparse index, PARTITION BY, and the engine variants \u2014 plus how to design fast tables.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-mergetree-explained\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"ClickHouse MergeTree: ORDER BY, Partitioning, and the Sparse Index - Virendra's TechTalk\" \/>\n<meta property=\"og:description\" content=\"How ClickHouse MergeTree really works: ORDER BY vs PRIMARY KEY, the sparse index, PARTITION BY, and the engine variants \u2014 plus how to design fast tables.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-mergetree-explained\/\" \/>\n<meta property=\"og:site_name\" content=\"Virendra's TechTalk\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/virendrachandak\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/virendrachandak\" \/>\n<meta property=\"article:published_time\" content=\"2026-10-16T06:33:50+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-10-16T06:34:02+00:00\" \/>\n<meta name=\"author\" content=\"Virendra Chandak\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@virendrachandak\" \/>\n<meta name=\"twitter:site\" content=\"@virendrachandak\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Virendra Chandak\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/clickhouse-mergetree-explained\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/clickhouse-mergetree-explained\\\/\"},\"author\":{\"name\":\"Virendra Chandak\",\"@id\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/#\\\/schema\\\/person\\\/63f7ffa1ea125e32af9618d188349e17\"},\"headline\":\"ClickHouse MergeTree: ORDER BY, Partitioning, and the Sparse Index\",\"datePublished\":\"2026-10-16T06:33:50+00:00\",\"dateModified\":\"2026-10-16T06:34:02+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/clickhouse-mergetree-explained\\\/\"},\"wordCount\":1495,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/#\\\/schema\\\/person\\\/63f7ffa1ea125e32af9618d188349e17\"},\"keywords\":[\"ClickHouse\",\"Data Engineering\",\"Database Indexing\",\"Database Performance\",\"MergeTree\",\"OLAP\",\"Partitioning\",\"SQL\"],\"articleSection\":[\"Clickhouse\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/clickhouse-mergetree-explained\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/clickhouse-mergetree-explained\\\/\",\"url\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/clickhouse-mergetree-explained\\\/\",\"name\":\"ClickHouse MergeTree: ORDER BY, Partitioning, and the Sparse Index - Virendra's TechTalk\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/#website\"},\"datePublished\":\"2026-10-16T06:33:50+00:00\",\"dateModified\":\"2026-10-16T06:34:02+00:00\",\"description\":\"How ClickHouse MergeTree really works: ORDER BY vs PRIMARY KEY, the sparse index, PARTITION BY, and the engine variants \u2014 plus how to design fast tables.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/clickhouse-mergetree-explained\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/clickhouse-mergetree-explained\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/clickhouse-mergetree-explained\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"TechTalk\",\"item\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Clickhouse\",\"item\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/category\\\/clickhouse\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"ClickHouse MergeTree: ORDER BY, Partitioning, and the Sparse Index\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/#website\",\"url\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/\",\"name\":\"Virendra's TechTalk\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/#\\\/schema\\\/person\\\/63f7ffa1ea125e32af9618d188349e17\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/#\\\/schema\\\/person\\\/63f7ffa1ea125e32af9618d188349e17\",\"name\":\"Virendra Chandak\",\"logo\":{\"@id\":\"https:\\\/\\\/www.virendrachandak.com\\\/techtalk\\\/#\\\/schema\\\/person\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.virendrachandak.com\",\"https:\\\/\\\/www.facebook.com\\\/virendrachandak\",\"https:\\\/\\\/www.linkedin.com\\\/in\\\/virendrachandak\\\/\",\"https:\\\/\\\/x.com\\\/virendrachandak\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"ClickHouse MergeTree: ORDER BY, Partitioning, and the Sparse Index - Virendra's TechTalk","description":"How ClickHouse MergeTree really works: ORDER BY vs PRIMARY KEY, the sparse index, PARTITION BY, and the engine variants \u2014 plus how to design fast tables.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-mergetree-explained\/","og_locale":"en_US","og_type":"article","og_title":"ClickHouse MergeTree: ORDER BY, Partitioning, and the Sparse Index - Virendra's TechTalk","og_description":"How ClickHouse MergeTree really works: ORDER BY vs PRIMARY KEY, the sparse index, PARTITION BY, and the engine variants \u2014 plus how to design fast tables.","og_url":"https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-mergetree-explained\/","og_site_name":"Virendra's TechTalk","article_publisher":"https:\/\/www.facebook.com\/virendrachandak","article_author":"https:\/\/www.facebook.com\/virendrachandak","article_published_time":"2026-10-16T06:33:50+00:00","article_modified_time":"2026-10-16T06:34:02+00:00","author":"Virendra Chandak","twitter_card":"summary_large_image","twitter_creator":"@virendrachandak","twitter_site":"@virendrachandak","twitter_misc":{"Written by":"Virendra Chandak","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-mergetree-explained\/#article","isPartOf":{"@id":"https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-mergetree-explained\/"},"author":{"name":"Virendra Chandak","@id":"https:\/\/www.virendrachandak.com\/techtalk\/#\/schema\/person\/63f7ffa1ea125e32af9618d188349e17"},"headline":"ClickHouse MergeTree: ORDER BY, Partitioning, and the Sparse Index","datePublished":"2026-10-16T06:33:50+00:00","dateModified":"2026-10-16T06:34:02+00:00","mainEntityOfPage":{"@id":"https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-mergetree-explained\/"},"wordCount":1495,"commentCount":0,"publisher":{"@id":"https:\/\/www.virendrachandak.com\/techtalk\/#\/schema\/person\/63f7ffa1ea125e32af9618d188349e17"},"keywords":["ClickHouse","Data Engineering","Database Indexing","Database Performance","MergeTree","OLAP","Partitioning","SQL"],"articleSection":["Clickhouse"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-mergetree-explained\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-mergetree-explained\/","url":"https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-mergetree-explained\/","name":"ClickHouse MergeTree: ORDER BY, Partitioning, and the Sparse Index - Virendra's TechTalk","isPartOf":{"@id":"https:\/\/www.virendrachandak.com\/techtalk\/#website"},"datePublished":"2026-10-16T06:33:50+00:00","dateModified":"2026-10-16T06:34:02+00:00","description":"How ClickHouse MergeTree really works: ORDER BY vs PRIMARY KEY, the sparse index, PARTITION BY, and the engine variants \u2014 plus how to design fast tables.","breadcrumb":{"@id":"https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-mergetree-explained\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-mergetree-explained\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-mergetree-explained\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"TechTalk","item":"https:\/\/www.virendrachandak.com\/techtalk\/"},{"@type":"ListItem","position":2,"name":"Clickhouse","item":"https:\/\/www.virendrachandak.com\/techtalk\/category\/clickhouse\/"},{"@type":"ListItem","position":3,"name":"ClickHouse MergeTree: ORDER BY, Partitioning, and the Sparse Index"}]},{"@type":"WebSite","@id":"https:\/\/www.virendrachandak.com\/techtalk\/#website","url":"https:\/\/www.virendrachandak.com\/techtalk\/","name":"Virendra's TechTalk","description":"","publisher":{"@id":"https:\/\/www.virendrachandak.com\/techtalk\/#\/schema\/person\/63f7ffa1ea125e32af9618d188349e17"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.virendrachandak.com\/techtalk\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.virendrachandak.com\/techtalk\/#\/schema\/person\/63f7ffa1ea125e32af9618d188349e17","name":"Virendra Chandak","logo":{"@id":"https:\/\/www.virendrachandak.com\/techtalk\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/www.virendrachandak.com","https:\/\/www.facebook.com\/virendrachandak","https:\/\/www.linkedin.com\/in\/virendrachandak\/","https:\/\/x.com\/virendrachandak"]}]}},"jetpack_publicize_connections":[],"jetpack_shortlink":"https:\/\/wp.me\/p2vTtQ-IA","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":2762,"url":"https:\/\/www.virendrachandak.com\/techtalk\/clickhouse-for-mysql-developers\/","url_meta":{"origin":2764,"position":0},"title":"ClickHouse for MySQL Developers: What’s Different and Why","author":"Virendra Chandak","date":"August 18, 2026","format":false,"excerpt":"ClickHouse uses SQL and looks familiar, but it is not \"MySQL but faster.\" What a MySQL developer needs to know before using it \u2014 columnar storage, no enforced primary key, append-only writes, batch inserts, and denormalized joins \u2014 with runnable Docker examples.","rel":"","context":"In "Clickhouse"","block_context":{"text":"Clickhouse","link":"https:\/\/www.virendrachandak.com\/techtalk\/category\/clickhouse\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2769,"url":"https:\/\/www.virendrachandak.com\/techtalk\/what-is-clickhouse\/","url_meta":{"origin":2764,"position":1},"title":"What Is ClickHouse, and When Should You Use It?","author":"Virendra Chandak","date":"July 21, 2026","format":false,"excerpt":"ClickHouse is an open-source columnar database built for analytics at massive scale: fast aggregation and filtering over very large datasets. ClickHouse may look like a relational database, but it\u2019s engineered for a fundamentally different purpose than Postgres or MySQL, and treating it as a drop-in replacement is the most common\u2026","rel":"","context":"In "Clickhouse"","block_context":{"text":"Clickhouse","link":"https:\/\/www.virendrachandak.com\/techtalk\/category\/clickhouse\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1045,"url":"https:\/\/www.virendrachandak.com\/techtalk\/how-to-find-and-replace-data-in-mysql\/","url_meta":{"origin":2764,"position":2},"title":"How to Find and Replace Data in MySQL","author":"Virendra Chandak","date":"August 5, 2012","format":false,"excerpt":"Recently, while migrating my blog, I had to find all the occurrences of my old URL and replace it with my URL. One way of doing this was to get a database dump, open it in a text editor and the do a find replace and the import it back.\u2026","rel":"","context":"In "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/www.virendrachandak.com\/techtalk\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":802,"url":"https:\/\/www.virendrachandak.com\/techtalk\/voting-functionality-in-a-website\/","url_meta":{"origin":2764,"position":3},"title":"Voting Functionality in a website","author":"Virendra Chandak","date":"April 10, 2011","format":false,"excerpt":"In this post I will give the step by step explanation of how we can add Voting Functionality to a website. At the end of this article we will have a working sample voting application. The source code for the sample voting application can be downloaded from here. We will\u2026","rel":"","context":"In "Functionality"","block_context":{"text":"Functionality","link":"https:\/\/www.virendrachandak.com\/techtalk\/category\/functionality\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virendrachandak.com\/techtalk\/wp-content\/uploads\/2011\/10\/initial.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1968,"url":"https:\/\/www.virendrachandak.com\/techtalk\/creating-csv-file-using-php-and-mysql\/","url_meta":{"origin":2764,"position":4},"title":"How to create CSV file using PHP","author":"Virendra Chandak","date":"April 19, 2015","format":false,"excerpt":"CSV (comma-separated values) is one of the most popular methods for transferring tabular data between applications. Lot of applications want to export data in a CSV file. In this article we will see how we can create CSV file using PHP. We will also see how to automatically download the\u2026","rel":"","context":"In "PHP"","block_context":{"text":"PHP","link":"https:\/\/www.virendrachandak.com\/techtalk\/category\/php\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1199,"url":"https:\/\/www.virendrachandak.com\/techtalk\/sample-mysql-table\/","url_meta":{"origin":2764,"position":5},"title":"Sample MySQL table","author":"Virendra Chandak","date":"September 16, 2012","format":false,"excerpt":"For my MySQL posts here is a sample MySQL table that I would be using for my examples. I would refer my posts back to this for table structure and sample data. Table structure: +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ |\u2026","rel":"","context":"In "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/www.virendrachandak.com\/techtalk\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.virendrachandak.com\/techtalk\/wp-json\/wp\/v2\/posts\/2764","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.virendrachandak.com\/techtalk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.virendrachandak.com\/techtalk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.virendrachandak.com\/techtalk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.virendrachandak.com\/techtalk\/wp-json\/wp\/v2\/comments?post=2764"}],"version-history":[{"count":24,"href":"https:\/\/www.virendrachandak.com\/techtalk\/wp-json\/wp\/v2\/posts\/2764\/revisions"}],"predecessor-version":[{"id":2844,"href":"https:\/\/www.virendrachandak.com\/techtalk\/wp-json\/wp\/v2\/posts\/2764\/revisions\/2844"}],"wp:attachment":[{"href":"https:\/\/www.virendrachandak.com\/techtalk\/wp-json\/wp\/v2\/media?parent=2764"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.virendrachandak.com\/techtalk\/wp-json\/wp\/v2\/categories?post=2764"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.virendrachandak.com\/techtalk\/wp-json\/wp\/v2\/tags?post=2764"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}