From Postgres to ClickHouse: Achieving Real-Time Aggregations with Materialized Views and Cutting Costs by 80%

Vasu Gupta8 Dec 2025

Problem

Wingify ingested real-time events through its in-house DACDN system, aggregated them in a separate Recording Pipeline and stored the result in a Postgres table for read queries. As data grew, query speed fell, especially for real-time analytics. Running two systems at scale was costly, and the same data sat in several places. Adding CPU and RAM gave diminishing returns, and sharding Postgres added complexity.

Example from the article: in the author's benchmark table, the 99th-percentile query latency in Postgres was 30–50 seconds.

Solution in brief

Write raw events into a ClickHouse table. A materialized view (MV) aggregates them on every insert into a second table built on the AggregatingMergeTree engine. Read queries go to that table, so the Recording Pipeline and Postgres can be switched off after a parity check.

Questions this case answers
  • How to serve low-latency aggregate queries over a fast-growing event stream when a row-store database and a separate write-side aggregation pipeline no longer scale
  • How to migrate a pre-aggregated reporting table to a new store and cut over reads without changing results
  • How to serve real-time aggregated analytics without a separate aggregation pipeline and a Postgres table that slows down as data grows
  • How to keep pre-aggregated tables for dashboards updated automatically as new events arrive

Tools and methods

  • ClickHouse materialized view — runs the aggregation on each insert and writes partial aggregates to a target table
  • AggregatingMergeTree — target table engine that merges stored aggregate states
  • PostgreSQL — the legacy aggregated store, used as the reference when validating results
Continue reading — it's free

Google or a one-time email code. No password.