The Postgres Developers guide to updates and deletes in ClickHouse

Learn how to handle updates and deletes in ClickHouse when coming from a Postgres background! This tutorial walks through working with CDC (Change Data Capture) data synced from Postgres to ClickHouse using ClickPipe. Unlike Postgres, which does updates and deletes in-place, ClickHouse uses versioned inserts with the ReplacingMergeTree engine. We'll explore how to properly query this data and avoid common pitfalls when counting records with updates and deletes.

• ReplacingMergeTree fundamentals - How ClickHouse handles duplicate entries and eventual consistency
• Working with CDC data - Understanding the pdb_is_deleted flag and pdb_version fields
• The FINAL modifier - When and why you need it for accurate results
• Query optimization techniques - Session-level FINAL settings, row policies, views, and refreshable materialized views

Starting with a Stack Overflow dataset synced from Postgres, we demonstrate why a simple SELECT COUNT(*) gives incorrect results and show multiple approaches to get accurate counts while balancing query performance and data freshness. Whether you're migrating from Postgres or working with CDC pipelines, this video provides practical techniques for effectively handling updates and deletes in ClickHouse.