skip to content

In ClickHouse, what does the MergeTree engine give you that the Log and Memory engines do not?

level: juniorimportance: must knowfreq 72%

answer

  1. one engine family powers real analytics
  2. data lands as immutable sorted parts
  3. background threads combine parts later
  4. no index, no partitions, no merges
  5. RAM only, gone on restart

basics

~20 s

MergeTree stores rows as sorted, compressed, immutable parts merged in the background, and alone supports the sparse index, partitions, TTL and replication. Log engines are index-free append-only files; Memory keeps rows in RAM and loses them on restart.

solid answer

~50 s

In ClickHouse the `ENGINE` clause decides how the table physically works, and only the **MergeTree family** is built for analytics at scale. Each `INSERT` writes a new immutable *part*: per-column compressed files sorted by the table's `ORDER BY`. Background threads merge small parts into larger ones, LSM-style. That layout is what makes the sparse primary index, `PARTITION BY`, TTL, projections, skipping indices, mutations and replication possible. The **Log family** (`Log`, `TinyLog`, `StripeLog`) is append-only column files with no index, no partitions, no TTL and no merges — every read is a full scan, and it is meant for tiny throwaway or staging tables. **Memory** keeps uncompressed rows in RAM with no index and no persistence: fast for small temporary sets, gone on restart. So the practical rule is: use MergeTree (or a variant of it) for anything real, and treat Log and Memory as scratch space.

code

sql · 19 lines
sql
-- Durable analytics table: sorted parts, index, partitions, TTL
CREATE TABLE events
(
    ts       DateTime,
    user_id  UInt64,
    amount   Decimal(18, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (user_id, ts)
TTL ts + INTERVAL 90 DAY;

-- Scratch table: no index, no partitions, full scan only
CREATE TABLE staging_codes (code String)
ENGINE = TinyLog;

-- Temporary in-RAM set: lost on restart
CREATE TABLE session_ids (id UInt64)
ENGINE = Memory;

go deeper

for a junior

Be ready to name MergeTree as the default choice and say why: sorted immutable parts, an index, partitions, TTL, replication. Knowing that Memory data disappears on restart is expected.

for a middle

Explain the part lifecycle — insert writes a part, background threads merge parts, old parts are deleted — and connect it to why every MergeTree variant behaves eventually rather than immediately.

for a senior

Interviewers expect you to place the whole family: all variants share storage and differ only in what a merge does with rows sharing the sorting key. Be able to say where Log, Memory, Buffer and Distributed genuinely belong.

for a principal

Own the consequence that engine choice is baked into the table and changing it means a rewrite. Be able to argue when a scratch engine is acceptable in a pipeline and when it quietly becomes a production dependency nobody can restart.

## Why the engine clause matters in ClickHouse Every ClickHouse table declares an engine in its DDL, and unlike in most relational databases this is not a footnote — it decides the on-disk layout, whether an index exists at all, whether partitions, TTL, replication and background maintenance are available, and what concurrency the table supports. Choosing wrongly is not a tuning mistake, it is a design mistake you fix by recreating the table. ```sql CREATE TABLE events (ts DateTime, user_id UInt64, amount Decimal(18,2)) ENGINE = MergeTree ORDER BY (user_id, ts); ``` ## What MergeTree actually does A `MergeTree` table stores data as **parts**. An `INSERT` sorts its block of rows by the table's `ORDER BY` key and writes a new part — a directory holding one or more compressed files per column plus mark files. Parts are **immutable**: nothing is ever updated in place. Because many small parts are expensive to read, background threads continuously **merge** parts into fewer, larger, still-sorted parts, in the manner of an LSM tree. Merged-away parts are marked inactive and deleted after a grace period. This is the mechanism the whole family is named after, and it is the reason every MergeTree variant behaves *eventually* rather than at insert time. On top of that layout MergeTree offers the features analytics needs: a sparse primary index over the sorting key so scans read only the granules they must, `PARTITION BY` for coarse pruning and cheap partition drops, TTL expressions for expiry and tiering, data-skipping indices and projections, asynchronous mutations, and replication through the `Replicated*` variants (ClickHouse Cloud uses a `SharedMergeTree` variant that keeps data on shared object storage instead of copying parts between replicas). The family shares all of this and differs only in **what a merge does when two rows have the same sorting key**: plain `MergeTree` keeps both, `ReplacingMergeTree` keeps one, `SummingMergeTree` adds the numeric columns, `AggregatingMergeTree` combines aggregate states, `CollapsingMergeTree` and `VersionedCollapsingMergeTree` cancel state and cancel pairs, and `GraphiteMergeTree` rolls up metrics by age. ## The Log family `TinyLog`, `Log` and `StripeLog` write column data to files and stop there. There is no primary index, no partitioning, no TTL, no mutation support and no background merging, so every query is a full scan of everything ever inserted. `TinyLog` is the most minimal (one file per column, no marks, no parallel reading); `Log` adds a marks file so reads can be parallelised; `StripeLog` puts all columns in a single file, which suits many-tiny-tables situations. Writes take a lock, so concurrent writers serialise. These engines are appropriate for small scratch tables — an intermediate result of a few thousand rows, a lookup list you are about to load elsewhere, test fixtures. They are never the right answer for a fact table. ## Memory The `Memory` engine keeps rows in RAM, **uncompressed**, in insert order, with no index. Reads and writes are concurrent and very fast, but capacity is bounded by RAM and the data is gone when the server restarts or the table is dropped. Its real uses are temporary tables, data a client pushes for a single query, and tests. ## Engines that are not storage at all Several engines are routing or integration surfaces rather than places data lives: `Distributed` fans a query out to shards, `Merge` presents a read-only union over several existing tables (a classic name collision with `MergeTree` — they are unrelated), `Buffer` accumulates rows in RAM and flushes them into a destination table, `Kafka` and `S3` and `File` and `URL` read from external systems, `Null` discards everything, and `Dictionary`, `Set` and `Join` expose in-memory structures. Knowing that `Merge` and `MergeTree` are different things is a cheap way to sound like you have actually used the product. ## How to answer Say the default plainly: MergeTree for anything durable or large, a MergeTree *variant* when the merge itself should do modelling work for you, Log or Memory only for scratch. Then name the concrete capabilities the alternatives lack — index, partitions, TTL, replication, mutations, background merges — because that list is the substance of the question.

  • What is the difference between the Merge engine and the MergeTree engine?
    They are unrelated despite the names. `MergeTree` is a storage engine: it owns data on disk as sorted parts. `Merge` stores nothing at all — it is a virtual table that reads from a set of other tables matching a database and name pattern, presenting their union. You use `Merge` to query many similarly-shaped tables at once; you use `MergeTree` to actually hold the rows.
  • If MergeTree writes a new part per INSERT, why is inserting one row at a time a problem?
    Every insert creates a part with its own set of column files, so a stream of single-row inserts produces thousands of tiny parts. Queries then have to open and merge many parts, and the background merge pool falls behind; ClickHouse eventually delays and then rejects inserts when the active part count crosses its thresholds. The fix is batching — large, less frequent inserts, or asynchronous inserts that let the server batch for you.
  • Why can a table be replicated in ClickHouse only if it uses a MergeTree engine?
    Replication is built on the part lifecycle: replicas coordinate through a log of part-level operations, fetching parts from each other and agreeing on merges. Log and Memory engines have no parts and no merge lifecycle, so there is nothing to replicate at that granularity. That is why replication exists only as `Replicated*` variants of the MergeTree family.

MergeTree is a filing system where each delivery arrives as its own sorted folder and clerks quietly consolidate folders overnight; Log engines are a pile of loose pages with no order, and Memory is a whiteboard that gets wiped when the lights go out.

saying these in an interview costs you the question

  • Calls Memory engine just a faster MergeTree with the same features
  • Thinks Log engines support primary-key lookups or partitions
  • Says MergeTree deduplicates or merges rows at insert time
  • Confuses the Merge engine with the MergeTree engine
  • Proposes TinyLog for a multi-billion-row fact table

context

resolving links…