In this hands-on training, participants will learn how to integrate PostgreSQL with ClickHouse to build high-performance analytical systems. Participants will migrate schemas and queries from PostgreSQL to ClickHouse, redesign data models for analytical workloads, and apply ClickHouse features such as denormalization, efficient primary keys, and specialized functions. The session also introduces strategies for handling updates and deletes, implementing Change Data Capture (CDC) from PostgreSQL, and optimizing analytical queries using Materialized Views and Projections. Through a series of guided labs, learners will build a practical understanding of how PostgreSQL and ClickHouse can work together to support both transactional and analytical workloads.

By the end of this module, learners will be able to:

• Explain OLTP vs OLAP workload differences
• Redesign Postgres schemas for analytical use in ClickHouse
• Migrate Postgres queries to efficient ClickHouse patterns
• Choose appropriate engines and features to handle updates and deletes
• Design and operate Change Data Capture (CDC) into ClickHouse
• Optimize analytical workloads using Materialized Views and Projections

The material is presented in modules and will include:

• Module 1: Introduction
  • OLTP vs. OLAP
  • Common Anti-patterns
  • OLTP vs. OLAP in Action
  • LAB 1.1: Explore PostgreSQL Data from ClickHouse
  • LAB 1.2: Ingesting PostgreSQL Data into ClickHouse
• Module 2: Migrating Data
  • Data Types
  • Primary Keys and Indexes
  • Updates and Deletes
  • Denormalization
  • Partitioning
  • LAB 2.1: Improving ClickHouse Schema
  • LAB 2.2: Denormalize post_types
• Module 3: Migrating Queries
  • Query like Postgres
  • Using arrayFilter, argMax, and Combinators
  • JOINs
  • LAB 3.1: Rewriting queries
• Module 4: Change Data Capture (CDC)
  • ClickPipes CDC
  • Query Correctness
  • LAB 4.1: Synchronizing PostgreSQL and ClickHouse
• Module 5: Materialized Views and Projections
  • Refreshable Materialized Views
  • Incremental Materialized Views
  • Projections

Hosted by: Pablo Musa