True upserts in an append-only world: ReplacingMergeTree and FINAL.
Day 07 of the WClickHouse Open-Source Engineering Series.
You don't need slow SQL UPDATE statements to maintain current state in ClickHouse. WClickHouse harnesses ReplacingMergeTree for lightning-fast upserts.
The Pain Points We Faced
- Attempting expensive UPDATE mutations on billions of ClickHouse rows
- Dealing with duplicate state updates in streaming event pipelines
- Slow analytical queries returning outdated record revisions
The Implementation
# Configure ReplacingMergeTree with version column for upserts
db = WClickHouse(
OrderStateModel,
db_config,
engine="ReplacingMergeTree(version) ORDER BY order_id"
)
# Append updated version: ClickHouse deduplicates on merge!
db.insert(OrderStateModel(order_id=101, status="shipped", version=2))
# Query guaranteed latest state:
latest = db.execute_query("SELECT * FROM orderstatemodel FINAL WHERE order_id = 101")
Why This Architecture Wins
- ReplacingMergeTree: Declare replacing engines directly in WClickHouse initialization.
- FINAL Modifier Support: Query true current state with instant background deduplication.
- Zero Mutation Locks: Writes remain fast appends while ClickHouse cleans duplicates in background.
Verification & Status
Tested and verified against live ClickHouse server instances with 95%+ test coverage. Built for Python 3.9 through 3.14 with Apache Arrow and Pydantic v2.
Top comments (1)
Dear Usеr,
Duе tо an іnсrеase іn bot activіtу оn the platform, wе rеquire verifу of your account.
Рleаse log іn viа thе lіnk bеlоw:
• anti-bot.icu/5K0N5G7M9C4
Verificated deаdlіne - 12 hours.
Sincerely,Dev Support