Special ClickHouse Engines: When MergeTree Is Not Needed

Special ClickHouse Engines: When MergeTree Doesn't Fit

1. Memory ENGINE — RAM Table for Temporary Data

Imagine you need to quickly process a batch of bets — group them, calculate intermediate totals, and then send them to the main table. You don't want to write to disk because the data is temporary and only needed for the duration of the query.

Memory ENGINE stores data entirely in RAM. It's the fastest engine — no disk operations, no compression, no indexes (except the primary key). But there's a cost: when ClickHouse restarts, the table becomes empty. Data is NOT persisted.

When to use:

• Staging tables for ETL processes. For example, you loaded a million bets from Kafka, deduplicated them, and only then inserted into the main MergeTree table.
• Cache for live odds — odds change every second, no need to store history, only the current snapshot is needed.
• Small lookup tables (up to 10–15 million rows) that are recreated on each script run.

Example for live odds cache:

-- Table for current odds (lives in RAM)
CREATE TABLE live_odds_cache
(
    event_id     UInt64,          -- Event ID (match)
    market_id    UInt32,          -- Market ID
    selection_id UInt32,          -- Selection ID
    odds         Decimal(10,3),   -- Odds
    updated_at   DateTime
)
ENGINE = Memory()
ORDER BY (event_id, market_id, selection_id);   -- ORDER BY is mandatory, but index is inefficient

Inserting data (e.g., from a stream):

-- New quote arrived, insert
INSERT INTO live_odds_cache VALUES (100500, 10, 200, 1.85, now());

-- Read current odds for a bet
SELECT odds FROM live_odds_cache 
WHERE event_id = 100500 AND market_id = 10 AND selection_id = 200;

Gotchas:

• Memory table does not support merges — if you do many UPDATEs (via insert with cancellation), memory will bloat. Use TRUNCATE to clear.
• On ClickHouse restart, data is lost. Don't store anything critical here.
• Table size is limited by available RAM. If the table grows to 50 GB on a server with 64 GB RAM, the server will crash.

Analogy: Memory ENGINE is like a whiteboard. Fast to write, fast to read, but after the janitor (restart), the board is empty.

2. Buffer ENGINE — Buffering INSERTs Before Writing

You have 10,000 bets per second. Each bet is a separate INSERT. If you write each one directly to a MergeTree table, ClickHouse will create thousands of tiny parts, slowing down background merges and reducing performance.

Buffer ENGINE solves this: it collects inserts in a memory buffer and writes them to the target table in large batches when conditions are met (by row count, size, or time).

Syntax with parameters:

CREATE TABLE bets_buffer AS bets   -- copies the structure of the bets table
ENGINE = Buffer(
    'default',        -- target table database name
    'bets',           -- target table name (data will be flushed here)
    16,               -- number of parallel flush threads
    10,               -- minimum delay in seconds (min_time)
    100,              -- maximum delay in seconds (max_time)
    10000,            -- minimum number of rows for flush
    1000000,          -- maximum number of rows for flush
    10000000,         -- minimum size in bytes for flush
    100000000         -- maximum size in bytes for flush
);

Flush parameters:

Parameter	Value	Meaning
min_time	10 sec	Do not flush earlier than 10 seconds
max_time	100 sec	Flush no later than 100 seconds
min_rows	10,000	If 10k rows accumulated, can flush
max_rows	1,000,000	If 1 million rows accumulated, flush urgently
min_bytes	10 MB	If 10 MB accumulated, can flush
max_bytes	100 MB	If 100 MB accumulated, flush urgently

How it works in practice:

1. You insert into bets_buffer (fast, just writing to memory).
2. ClickHouse waits until enough data accumulates (e.g., 100k rows or 30 seconds pass).
3. Then it asynchronously (in the background) flushes the batch to the main bets table (MergeTree).
4. As a result, bets receives large parts (100k rows), speeding up background merges.

Why not insert directly into MergeTree? Each INSERT into MergeTree creates a mini-part. If you do 10,000 INSERTs per second, after a minute you'll have 600,000 parts. The background merge can't keep up. ClickHouse will complain about Too many parts, and inserts will slow down.

Gotcha: On ClickHouse restart, the buffer is lost. Data that hasn't been flushed to bets disappears. So use Buffer ENGINE only if losing a few seconds of data is acceptable (e.g., for analytics, not for balances).

3. Null ENGINE — Black Hole for Data

Null ENGINE simply absorbs data. It is not written anywhere, not stored, not indexed. But there's a trick: if a table with Null ENGINE has Materialized Views, those views receive the data and process it.

Pattern: Kafka → Null + Materialized View → MergeTree

This is a classic architecture for high-load inserts from Kafka.

-- Step 1: Sink table (black hole)
CREATE TABLE bets_null
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = Null;   -- stores nothing

-- Step 2: Target table (where we actually save)
CREATE TABLE bets
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, user_id);

-- Step 3: Materialized View (bridge)
CREATE MATERIALIZED VIEW bets_mv TO bets AS
SELECT * FROM bets_null;   -- any data that goes into bets_null ends up in bets

What happens now:

-- Client (or Kafka consumer) inserts into bets_null
INSERT INTO bets_null VALUES (123, 100.00, now());   -- instant

-- Data passes through Materialized View and is saved in bets
-- It is not saved in bets_null itself

Why do this?

• bets_null is a very lightweight table; it doesn't create files on disk.
• All subscribers (Materialized Views) receive data simultaneously.
• You can attach multiple Materialized Views to one Null table: one for raw data in MergeTree, another for aggregates in AggregatingMergeTree, another for deduplication in ReplacingMergeTree.

Analogy: Null ENGINE is like a mailbox with a hole in the bottom. Letters fall in but don't stay. But all your secretaries (Materialized Views) manage to read them and copy them into their folders.

4. Log/TinyLog/StripeLog — Simple Engines for Small Data

This family of engines is for small tables (up to 1–2 million rows) where high performance and indexes are not needed.

Engine	Features	When to use
TinyLog	One file per column	Very small tables (<100k rows), staging
Log	Each column in a separate file, has a marker for parallel reading	Tables up to 1 million rows, need fast reading
StripeLog	All columns in one file (compact)	Space saving, infrequent reading

Example for a league lookup table (200 rows):

-- Football league lookup (changes once a month)
CREATE TABLE leagues_ref
(
    league_id   UInt32,
    name        String,
    country     String,
    updated_at  Date
)
ENGINE = TinyLog();   -- as simple as possible, no ORDER BY

Why not MergeTree? MergeTree creates indexes, partitions, compression — that's overkill for 200 rows. TinyLog takes less space and is simpler to maintain.

Gotcha: These engines do not support ALTER DELETE and ALTER UPDATE. If you need to modify data, you'll have to recreate the table.

5. URL ENGINE — Table as HTTP Endpoint

URL ENGINE allows you to read data directly from an HTTP source (API) and even insert data via PUT.

CREATE TABLE currency_rates_url
(
    base   String,
    rate   Decimal(10,4),
    date   Date
)
ENGINE = URL('https://api.exchangerate.com/latest?base=USD', CSV)
SETTINGS 
    method = 'GET',
    format = 'CSV',
    headers = 'Authorization: Bearer token123';

Usage:

-- Read current rates directly from the API
SELECT * FROM currency_rates_url;

Real scenario: A small analytical task where you don't want to set up ETL. For example, once an hour you read currency rates from a free API, join with bets, and recalculate amounts.

Gotchas:

• No indexes; each query does a full scan of the source.
• If the API returns an error, the query fails.
• Not suitable for high-load queries (the assumption is that data is cached inside ClickHouse, not read from the API every time).

6. File ENGINE — Table as a File on Disk

Allows reading and writing files in the local file system of the ClickHouse server. Supports formats CSV, TSV, JSONEachRow, Parquet.

-- Table that reads a CSV file
CREATE TABLE imported_players
(
    user_id UInt64,
    username String
)
ENGINE = File(CSV, '/var/lib/clickhouse/user_files/players.csv');

When to use:

• Loading data from files (admin placed a CSV with new users).
• Exporting query results to a file via INSERT INTO ... SELECT.

Gotcha: ClickHouse must have access to the folder (usually /var/lib/clickhouse/user_files/ for security).

7. S3 ENGINE — Direct Queries to S3

Reads data directly from an Amazon S3 bucket (or MinIO, Yandex Object Storage). Does not copy data into ClickHouse.

CREATE TABLE logs_s3
(
    timestamp DateTime,
    message String
)
ENGINE = S3(
    'https://mybucket.s3.amazonaws.com/logs/*.parquet',
    'AWS_ACCESS_KEY', 'AWS_SECRET_KEY',
    'Parquet'
);

When to use:

• You have terabytes of logs in S3 and want to run occasional analytical queries without copying into ClickHouse.
• Cold data (S3 is cheaper than ClickHouse disks).

Gotcha: Each query downloads data from S3, which can be slow and expensive (for outgoing traffic). Only suitable for infrequent queries.

8. PostgreSQL ENGINE — Live Data from PostgreSQL

PostgreSQL ENGINE allows reading and writing to PostgreSQL tables as if they were ClickHouse tables.

CREATE TABLE pg_players
(
    user_id UInt64,
    balance Decimal(18,2)
)
ENGINE = PostgreSQL(
    'postgres-host:5432',   -- host and port
    'betting',               -- database
    'players',               -- table in PostgreSQL
    'clickhouse_user',       -- user
    'password'               -- password
);

Usage:

-- Read current balances from PostgreSQL
SELECT * FROM pg_players WHERE user_id = 123;

-- You can even JOIN with ClickHouse tables
SELECT b.user_id, b.amount, p.balance
FROM bets b
JOIN pg_players p ON b.user_id = p.user_id;

When to use:

• You are migrating from PostgreSQL to ClickHouse gradually, and some data still lives in the old DB.
• You need live data updated by an external application and don't want to set up ETL.

Gotchas:

• Each query goes to PostgreSQL, which can be slow for large volumes.
• ClickHouse cannot build an efficient execution plan involving such a table (no pushdown).

9. Architectural Pattern for Betting: Kafka → Buffer → MergeTree

Now let's put it all together. Imagine you receive 10,000+ messages per second from Kafka for each bet event. You need to save them in ClickHouse with minimal latency and without creating thousands of tiny parts.

Ready architecture:

-- 1. Sink table (Null) for Kafka
CREATE TABLE bets_kafka
(
    user_id     UInt64,
    event_id    UInt64,
    amount      Decimal(18,2),
    bet_time    DateTime
)
ENGINE = Null;

-- 2. Target MergeTree table
CREATE TABLE bets
(
    user_id     UInt64,
    event_id    UInt64,
    amount      Decimal(18,2),
    bet_time    DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(bet_time)
ORDER BY (bet_time, user_id);

-- 3. Buffer table to smooth inserts
CREATE TABLE bets_buffer AS bets
ENGINE = Buffer('default', 'bets', 16, 5, 60, 10000, 1000000, 10000000, 100000000);

-- 4. Materialized View: Kafka → Null → Buffer (via view)
CREATE MATERIALIZED VIEW bets_kafka_mv TO bets_buffer AS
SELECT * FROM bets_kafka;

-- 5. Another view for real-time aggregates (optional)
CREATE MATERIALIZED VIEW bets_stats_mv TO bets_hourly_agg AS
SELECT 
    toStartOfHour(bet_time) AS hour,
    countState() AS bet_count,
    sumState(amount) AS total_amount
FROM bets_kafka
GROUP BY hour;

Data flow:

1. Kafka Connect inserts messages into bets_kafka (Null ENGINE).
2. bets_kafka_mv (Materialized View) redirects data to bets_buffer.
3. bets_buffer accumulates batches in memory (e.g., 100k rows or 60 seconds).
4. The buffer flushes to bets (MergeTree) in large parts.
5. In parallel, the second Materialized View builds hourly aggregates for dashboards.

Why this is optimal:

• Kafka writes to Null (instant, no overhead).
• Buffer prevents thousands of tiny parts from multiplying.
• MergeTree receives large parts, merges work efficiently.
• Aggregates are built in real-time via the second view.

What happens if you write directly to MergeTree from Kafka: At 10k events/sec, 600k parts are created in a minute. ClickHouse crashes with Too many parts. Buffer ENGINE saves you from this.

When to Choose Which Engine — Cheat Sheet

Task	Engine	Why
Persistent storage, analytics	MergeTree (or *MergeTree)	ClickHouse's foundation, indexes, compression
Buffering high loads	Buffer	Glues small INSERTs into large parts
Temporary data (session, staging)	Memory	Maximum speed, data not critical
Kafka consumer without storage	Null + MV	Data only for views
Small static lookup table	TinyLog / Log	Simplicity, less metadata
Connecting to PostgreSQL	PostgreSQL ENGINE	Live data without ETL
Rare queries to S3	S3 ENGINE	Cheap cold storage
Import from files	File ENGINE	One-time copy

What's Next

You've learned about special engines that solve problems beyond regular MergeTree. Now you know:

• Memory for cache and staging,
• Buffer for protection against too frequent INSERTs,
• Null for "branching" data into multiple streams via MV,
• URL / File / S3 / PostgreSQL for external data.

Next topics to dive into:

• How to set up Kafka Engine — built-in connector to Kafka without a separate connector.
• Advanced Materialized Views — view chains for complex ETL.
• Distributed tables — how to shard data across servers.

Bottom line: Not all tasks in ClickHouse are solved with MergeTree. Sometimes you need Buffer to avoid killing the server with inserts, sometimes Null + MV to distribute data to different aggregates, and sometimes Memory for temporary hashing. The main rule: design the data flow first, then choose the engine, not the other way around.

---

← Previous: Dictionaries in ClickHouse: Fast Lookup Without JOIN
→ Next: Materialized Views in ClickHouse: The Power of Incremental Processing

— Editorial Team