Architecture Review: Analytics Data Platform

Message queue or event stream consumer processing rate falls below producer write rate, causing consumer lag to grow unboundedly: eventually leading to increased end-to-end latency, producer backpressure, data expiry, or queue resource exhaustion.

Affects 1 node. (Slow Consumer)

One partition (a database shard, a Kafka topic partition, a Redis hash slot) receives traffic so far above its peers that it saturates while the others sit idle. Aggregate capacity looks healthy, but the hot partition throttles or lags, and everything routed to it degrades. The cause is skew in how keys map to partitions, and the fix depends on whether the skew is spread across many keys or concentrated in one.

Affects 0 nodes

Seed 'Queue Consumer Backlog' identifies 4 metrics relevant to queue_backlog_accumulation.

Metrics to instrument: queue_depth, consumer_lag_seconds, consumer_throughput

Trigger: OLTP query p99 degrading during analytics reporting windows; reporting queries showing wait events (LockTimeout, I/O wait) in pg_stat_activity. Migrate from 'Analytics queries running directly against PostgreSQL OLTP primary' to 'Read replica serving analytics queries via polling ETL'. Polling ETL from a read replica is a practical first step. It separates analytics load from the primary without committing to Kafka infrastructure.

Read replica replication lag degrades freshness during heavy OLTP write periods; ETL polling creates a minimum latency floor; sub-minute freshness is not achievable

Trigger: Sub-minute analytics freshness SLA required; ETL scheduling overhead growing; analytics volume exceeding what the read replica can serve under polling load. Migrate from 'Polling ETL from read replica to analytics store' to 'WAL CDC → Kafka → ClickHouse streaming ingestion'. This migration delivers streaming freshness and isolates analytics infrastructure from the OLTP layer. Validate CDC slot monitoring and alerting before migrating high-volume tables.

CDC setup requires PostgreSQL logical replication slot: mandatory monitoring obligation from day one; ClickHouse operational model (parts, merges, insert buffering) requires learning investment

Simulation demonstrates critical degradation of redis, postgresql

Without a runbook, recovery from this failure mode will be ad-hoc

Simulation demonstrates critical degradation of postgresql

Without a runbook, recovery from this failure mode will be ad-hoc

Evolution from Unified OLTP + Analytics on PostgreSQL → Separated OLTP (PostgreSQL) + OLAP (ClickHouse/Snowflake)

Migration complexity: medium. Rollback: always.

Evolution from Single-Node PostgreSQL → Partitioned PostgreSQL

Migration complexity: high. Rollback: rarely.

Signal: Kafka consumer group lag (bytes or offsets) growing for the analytics topic group; ClickHouse dashboard timestamps falling behind wall clock by > 60s; ClickHouse insert throughput < Kafka produce rate

Bottleneck: ClickHouse insert throughput insufficient for Kafka produce rate. Evolution: Tune ClickHouse insert buffer size and async_insert settings; increase consumer parallelism up to the Kafka partition count; batch inserts into ClickHouse using the Buffer engine or materialized views with merge trees

Signal: One Kafka partition offset growing significantly faster than others; one consumer instance CPU/network saturated while others are idle

Bottleneck: Skewed partition key distribution: high-cardinality entity routing the same high-volume key to one partition. Evolution: Add a secondary hash suffix to the partition key to distribute load; increase topic partition count (note: keyed ordering breaks for existing messages); re-evaluate partition key selection based on actual cardinality measurements