A deep dive into the MergeTree engine - parts, granules, sparse indexes, merges, mutations, data skipping indices, compression codecs, TTL, and the key settings that control it all.

BigData Boutique blog

The MergeTree engine is ClickHouse's core storage engine that achieves exceptional analytical query performance through specific design choices. Data is stored in immutable parts created by each INSERT, organized into granules (default 8192 rows) that serve as the smallest read unit. A sparse index tracks granule boundaries rather than individual rows, keeping indexes small enough to fit in RAM. Background merges consolidate parts automatically, while mutations handle updates/deletes by rewriting affected parts. The engine supports data skipping indices for non-key columns, specialized compression codecs per column, and TTL rules for automated data lifecycle management including tiered storage and aggregation. Key operational concerns include batching inserts to avoid "too many parts" errors, choosing appropriate sorting keys that also improve compression, and using TTL instead of delete mutations for routine data retention.

ClickHouse Power Tips: Understanding the MergeTree Engine