UpsertCH SQL guide

Overview

UpsertCH adds two table engines to a ClickHouse® 26.10 server:

Requirements

• Linux amd64.
• A CPU with x86-64-v3 plus PCLMUL. On AWS, t3, c6i, m7i, r6i and r7i qualify. ARM / Graviton instances are not supported.

Getting UpsertCH

UpsertCH will be distributed as an AWS Marketplace AMI with the engines preinstalled, running in your own AWS account. The listing is not live yet; see planned pricing.

UpsertMergeTree

Columnar, disk-backed tables with one visible winning row per UNIQUE KEY within each partition. Ordinary reads see the winners without FINAL or waiting for background merges; superseded physical rows are reclaimed later by maintenance.

CREATE TABLE player_state
(
    tenant_id UInt64,
    player_id UInt64,
    version   UInt64,
    status    String
)
ENGINE = UpsertMergeTree(version)
PARTITION BY tenant_id
ORDER BY (tenant_id, player_id)
UNIQUE KEY (tenant_id, player_id);

INSERT INTO player_state VALUES (1, 42, 1, 'old');
INSERT INTO player_state VALUES (1, 42, 2, 'new');
INSERT INTO player_state VALUES (1, 42, 1, 'late old value');

SELECT tenant_id, player_id, version, status
FROM player_state
WHERE tenant_id = 1 AND player_id = 42;
-- (1, 42, 2, 'new'): the late lower version does not replace the winner

Both ORDER BY and a nonempty UNIQUE KEY are required. The unique key picks the rows to replace; the sorting key controls physical order and pruning, and the two need not match. No experimental setting is needed to create the table.

Versions and replacement

• The optional engine argument names one stored, non-null UInt64, DateTime or DateTime64 column (MATERIALIZED allowed; Nullable, LowCardinality and ALIAS not).
• A higher version wins. Equal versions fall back to later commit order, then row position within the block. Without a version argument, commit order decides.
• Every insert is a complete replacement row — omitted columns take their defaults, not the previous values.
• Unique-key columns are plain column identifiers of non-null integer, String, FixedString or tuple types built from those.

Partitions and uniqueness

• Uniqueness is per partition. The same key in two partitions gives two visible rows, even with FINAL.
• Route every version of a logical key to the same partition — partitioning by an update timestamp defeats this.
• UNIQUE KEY chooses a winner; it does not reject duplicates as a constraint violation.

Writes and durability

• Inserts are synchronous, even when async_insert is requested, and success means a durable commit (the engine enforces fsync_after_insert and fsync_part_directory on one local disk).
• A large statement can commit in several units; an error can leave earlier units committed. It is not one transaction with its materialized views.
• Retry with the same version and payload. insert_deduplication_token is supported.

Deletes, TTL and schema changes

DELETE FROM player_state WHERE tenant_id = 1 AND player_id = 42;

• Deletes are synchronous and remove the version floor, so a later insert can recreate the key with a lower version. They do not propagate to materialized-view targets.
• TTL … DELETE, selected column and skipping-index changes, partition operations and projections are supported. TTL runs in background maintenance.
• Partial-column updates are not supported.

MemCache

MemCache is an alias of the OverwriteCache engine — same implementation, arguments, settings and persistence format. It keeps one winning row per composite key in RAM and serves exact key and lookup-index reads.

CREATE TABLE latest_cache
(
    tenant_id UInt64,
    id        UInt64,
    version   UInt64,
    value     String
)
ENGINE = MemCache(version)
KEYS (tenant_id, id)
INDEX (tenant_id)
SETTINGS max_memory_bytes = 1073741824, persist_mode = 'sync';

INSERT INTO latest_cache VALUES (1, 42, 1, 'old');
INSERT INTO latest_cache VALUES (1, 42, 2, 'new');
INSERT INTO latest_cache VALUES (1, 42, 1, 'late old value');

-- Complete primary key
SELECT value FROM latest_cache WHERE tenant_id = 1 AND id = 42;
-- 'new'

-- Declared lookup index on tenant_id
SELECT id, value FROM latest_cache WHERE tenant_id = 1 ORDER BY id;
-- (42, 'new')

One version-column argument and a nonempty KEYS tuple are required, and the version column cannot also be a key. OverwriteCache(version) selects the same engine.

Winner selection

• A higher version replaces the stored row; a lower version is ignored.
• Equal versions compare equal_version_tiebreak_columns in order; on a full tie the existing row stays.
• Each insert is a whole replacement row. There is no partial UPDATE or compare-and-swap.

Lookup indexes and functions

Every query must constrain all key columns with = or IN, or use a supported predicate on a declared lookup index. Unrestricted and partial-key scans are rejected. Index columns must belong to KEYS, and a composite index does not create indexes on its individual columns.

ALTER TABLE latest_cache ADD INDEX (id);
SELECT tenant_id, value FROM latest_cache WHERE id = 42;
ALTER TABLE latest_cache DROP INDEX (id);

-- Scalar lookup: NULL for a missing key
SELECT overwriteCacheGetOrNull(
    'latest_cache', 'value', toUInt64(1), toUInt64(42));
-- 'new'

overwriteCacheGet returns the type's default for a missing key instead of NULL. Both functions reject tables with an effective row policy — use SELECT there.

Persistence and memory

A clean shutdown drains the persistence queue; on attach, segments are replayed and indexes rebuilt in memory. Persistence is local only — no replication or failover.

Deletes

DELETE FROM latest_cache WHERE tenant_id = 1 AND id = 42;

Deletes are synchronous, follow the same lookup restrictions as SELECT, and remove the version floor. They do not propagate to materialized-view targets.

MemCache → UpsertMergeTree pipeline

A materialized view can forward every insert from a hot MemCache table to a durable UpsertMergeTree table. Carry deletions as versioned data (for example an is_deleted flag) because engine deletes are not forwarded.

CREATE TABLE live_state (id UInt64, version UInt64, value String, is_deleted UInt8)
ENGINE = MemCache(version) KEYS (id)
SETTINGS max_memory_bytes = 1073741824, persist_mode = 'sync';

CREATE TABLE durable_state (id UInt64, version UInt64, value String, is_deleted UInt8)
ENGINE = UpsertMergeTree(version) ORDER BY id UNIQUE KEY id;

CREATE MATERIALIZED VIEW live_state_to_durable TO durable_state AS
SELECT id, version, value, is_deleted FROM live_state;

INSERT INTO live_state VALUES (42, 1, 'old', 0);
INSERT INTO live_state VALUES (42, 2, 'new', 0);
INSERT INTO live_state VALUES (42, 3, '', 1);  -- logical delete

SELECT count() FROM durable_state WHERE id = 42 AND is_deleted = 0;
-- 0

• Give each logical change a higher version. On equal versions the engines differ: MemCache keeps the existing row, UpsertMergeTree takes the later commit.
• Filter is_deleted when reading, not inside the view, or the target never receives the deletion.
• There is no joint commit across the two tables; keep view errors visible and plan reconciliation.

Verify the engines

SELECT name FROM system.table_engines
WHERE name IN ('MemCache', 'OverwriteCache', 'UpsertMergeTree')
ORDER BY name;

Compatibility & limits

• SHOW CREATE TABLE keeps the name MemCache; system.tables.engine reports OverwriteCache. Older servers that only know OverwriteCache cannot attach metadata naming MemCache.
• There is no replicated UpsertMergeTree variant.
• SQL BACKUP/RESTORE, CHECK TABLE, sampling, streaming and background disk moves are gated for UpsertMergeTree. Back up a stopped service's data, metadata, configuration and executable together.
• Queries still use MergeTree parts, indexes and maintenance; part count and cold index I/O can affect latency. No fixed OLTP latency is guaranteed.

Summarized from the engine reference guides for source commit 30dd63133fd1f32acb1b76ad4272f36fbd136246 · Last updated 2026-11-06