Problem
Analytics dashboards were running queries against the operational database. As data volume grew, these queries caused read contention that degraded application performance. The team needed a purpose-built analytics layer that could serve dashboard queries with sub-second latency over fresh data (seconds-to-minutes lag) without touching the operational database at query time.
Architecture
Change Data Capture — Debezium captures row-level changes from Postgres via the WAL (logical replication). Changes are published to Kafka topics partitioned by table and primary key. Debezium runs as a Kafka Connect connector on a dedicated cluster.
Stream Processing — Apache Flink consumes from Kafka. Flink jobs handle deduplication, schema normalization, join enrichment (e.g., denormalizing user attributes onto event records), and aggregation. Jobs are deployed with checkpointing to S3 for fault tolerance.
OLAP Layer — Processed records are ingested into Apache Pinot via Kafka-based real-time ingestion. Pinot's segment structure enables sub-second query response on hundreds of millions of rows. Star-schema table design with pre-computed aggregations in Pinot indexes.
Query Layer — Grafana queries Pinot directly via a JDBC connector. For custom dashboards, an internal service exposes a REST API over Pinot queries with result caching.
Tradeoffs
Debezium vs polling — WAL-based CDC is low-latency and low-overhead on the source database but requires Postgres superuser permissions and careful management of replication slot lag. Polling is simpler to operate but adds load on the source.
Flink complexity — Flink's exactly-once semantics are powerful but the operational complexity is real. Managing savepoints, handling schema evolution in state, and debugging backpressure require significant expertise. For simpler transformations, Kafka Streams would be sufficient.
Pinot vs ClickHouse — We evaluated ClickHouse and found better community support, but chose Pinot for its native Kafka ingestion and upsert support, which was important for our CDC-based design.
Lessons Learned
Schema evolution is the hardest operational problem in streaming pipelines. Adding a column to the source database requires coordinated changes across Debezium config, Flink job, and Pinot schema. Automating this with schema registry (Confluent or Apicurio) from the start would save significant operational effort.
Pinot's upsert mode has performance implications at high update rates. Append-only designs where possible are significantly more efficient.