FINAL headache
ReplacingMergeTree removes duplicates only when background merges get to them.
Until then, every query that forgets FINAL can count the same row twice.
The numbers in this table are wrong — some records are duplicated!
You should use the FINAL modifier.
SELECT … FROM orders FINALNo other database makes me do that. What a hassle.
…and every dashboard, BI tool and ad-hoc query has to remember it too.
…
FINAL once and a report silently double-counts.FINAL deduplicates at query time, adding latency and memory pressure.OPTIMIZE … FINAL rewrites data and is no fix for live tables.
UpsertMergeTree skips superseded rows on ordinary reads. Analysts write the
same SELECT they would anywhere else and get one current row per key.
SELECT … FROM orders -- 1042 · shipped
Skip the PostgreSQL-to-ClickHouse CDC pipeline. UpsertMergeTree returns the latest
row for each key without FINAL, and MemCache keeps hot state in RAM —
in one ClickHouse®-compatible server, soon as a turnkey AWS Marketplace AMI.
CREATE TABLE agent_session_state
(
tenant_id UInt64,
trace_id UInt64,
version UInt64,
status String,
tokens_used UInt32
)
ENGINE = UpsertMergeTree(version)
PARTITION BY tenant_id
ORDER BY (tenant_id, trace_id)
UNIQUE KEY (tenant_id, trace_id);
INSERT INTO agent_session_state VALUES (1, 42, 1, 'RUNNING', 120);
INSERT INTO agent_session_state VALUES (1, 42, 2, 'COMPLETED', 480);
-- Latest row per key, no FINAL keyword
SELECT status, tokens_used FROM agent_session_state
WHERE tenant_id = 1 AND trace_id = 42;
-- 'COMPLETED', 480CREATE TABLE agent_live_state
(
tenant_id UInt64,
agent_id UInt64,
version UInt64,
status String
)
ENGINE = MemCache(version)
KEYS (tenant_id, agent_id)
INDEX (tenant_id)
SETTINGS max_memory_bytes = 1073741824, persist_mode = 'sync';
INSERT INTO agent_live_state VALUES (1, 7, 1, 'PLANNING');
INSERT INTO agent_live_state VALUES (1, 7, 2, 'RUNNING');
INSERT INTO agent_live_state VALUES (1, 9, 1, 'IDLE');
-- Exact lookup on the full key, served from RAM
SELECT status FROM agent_live_state
WHERE tenant_id = 1 AND agent_id = 7;
-- 'RUNNING'
-- Every agent of one tenant, through INDEX (tenant_id)
SELECT agent_id, status FROM agent_live_state
WHERE tenant_id = 1 ORDER BY agent_id;
-- (7, 'RUNNING'), (9, 'IDLE')Agents overwrite the same keys all day — session status, balances, positions, prices. UpsertCH stores those writes where they can be read back as current state and aggregated with ClickHouse® SQL, without a second database.
UpsertMergeTree
Latest-row storage with a UNIQUE KEY and a version column. Ordinary
SELECT queries skip superseded rows within each partition, so reads see
the winning row without FINAL or waiting for background merges.
MemCache
A bounded latest-state cache with composite KEYS, declared lookup
INDEX clauses and version-based replacement. Disk persistence is on by
default; persist_mode='none' makes it memory-only.
| Concern | ReplacingMergeTree + FINAL | UpsertCH |
|---|---|---|
| Reading the latest state | SELECT … FINAL merges at query time | Plain SELECT sees winning rows |
| Hot point lookups | Sparse index reads whole granules | MemCache key lookups from RAM |
| System footprint | Redis/Postgres + Kafka/CDC + ClickHouse® | One server process |
Fewer moving parts between an agent's write and the dashboard that reads it.
We publish what each build has actually passed. Performance benchmarks will be published with reproducible scripts alongside the AWS Marketplace AMI.
Stateless engine tests passed on the release binary — 0 failures, 0 skips.
Clean Ubuntu install, start and graceful restart checks, with SHA-256 verified binaries.
MemCache winners verified to persist across an actual server restart.
Installed and smoke-tested on Amazon EC2 with Ubuntu 26.04 LTS, including a booted AMI.
UpsertMergeTree
LIMIT queries can still be slow. Deduplication is partition-local. SQL
BACKUP/RESTORE and CHECK TABLE are not supported for
UpsertMergeTree yet. Large-scale performance figures are not yet certified.
Track step state (pending → running → completed), aggregate token usage for billing, and store MCP server state next to the trace history you analyze.
Keep the latest quote and position per symbol current, compute drawdown from the same tables, and backtest over historical ticks in one engine.
Maintain wallet balances and turnover, deduplicate replayed stream messages by key and version, and tag suspicious activity as it happens.
Converge out-of-order webhooks and crawler updates by version so pricing and bidding agents always read the newest state.
The AWS Marketplace image is in development. These are its design requirements; the listing will document each one when it goes live.
Instances run in your AWS account. Your data stays on your EBS volumes; nothing is sent to us.
The OS lives on the root volume and data on a dedicated GP3 volume at /data/clickhouse, so upgrading means launching a new AMI and re-attaching the data volume.
No SSH keys, shell history or default passwords in the image. The initial database password is generated at first boot from the EC2 instance ID.
Instance metadata is accessed with IMDSv2 session tokens, including first-boot setup and licensing checks.
Ubuntu LTS with security-only unattended upgrades, while the engine packages are held so OS updates never replace them.
Requires x86-64-v3 with PCLMUL — for example r6i, r7i, m7i, c6i or t3. Graviton/ARM is not supported yet.
Planned AWS Marketplace pricing. The software fee is billed hourly on your AWS invoice, on top of standard EC2 infrastructure charges.
7-day free trial on AWS Marketplace. EC2 infrastructure costs apply.
c6i.xlarge / r6i.xlarge)Pay as you go through AWS billing. No long-term commitment.
| Instance | Software / hr |
|---|---|
r6i.xlarge · 4 vCPU · 32 GB | $0.35 |
r6i.2xlarge · 8 vCPU · 64 GB | $0.65 |
r6i.4xlarge · 16 vCPU · 128 GB | $1.20 |
Annual contracts through AWS Marketplace Private Offers.
Open source & IP notice: This product includes proprietary C++ storage engine
modifications (UpsertMergeTree and MemCache) built upon open-source software
licensed under the Apache License, Version 2.0.
Trademarks: ClickHouse® is a registered trademark of ClickHouse, Inc. UpsertCH is developed independently and is not affiliated with, endorsed by, or sponsored by ClickHouse, Inc. Amazon Web Services, AWS, and EC2 are trademarks of Amazon.com, Inc. or its affiliates.