Use Analytics Data Platform as the Foundational Architecture Pattern
Deterministic ADR derived from topology, simulation, and advisor intelligence for Analytics Data Platform. Traceable to YAML knowledge entities.
Context
Analytical queries (large scans, GROUP BY aggregations, time-range rollups) impose fundamentally different access patterns than OLTP writes. Running both on the same PostgreSQL primary leads to lock contention, I/O competition, and unpredictable query latency. As analytics query complexity grows, the only scalable solution is to route analytical workloads to a dedicated columnar store that is optimised for scan throughput, not point-lookup latency. Ingestion must be streaming (not batch ETL) to meet sub-minute freshness requirements at scale. Primary operational risks include: Kafka consumer group lag accumulation: slow ClickHouse insert throughput causes the analytics consumer to fall behind, increasing query staleness until the lag resolves; Hot partition on high-cardinality Kafka topic keys: skewed entity distribution routes disproportionate event volume to one partition, saturating that consumer; ClickHouse part merge pressure: extremely high insert rates fragment parts faster than the background merge process can consolidate them, degrading query performance.
Decision
We will adopt the **Analytics Data Platform** architecture pattern. This is a high-complexity architecture appropriate for teams at experienced backend team level or above. The advisor rates this pattern as 'advanced' operational maturity.
Rationale
An OLAP-oriented analytics architecture that ingests operational changes from PostgreSQL via WAL-based CDC into Kafka, then routes them to a columnar analytics store (ClickHouse or Snowflake) for product analytics, business intelligence, and operational reporting. The CQRS separation ensures analytical queries never degrade transactional write performance, and materialized views provide pre-aggregated query acceleration for the most expensive analytical patterns. The primary architectural strength is: Analytics-heavy workloads pre-compute expensive aggregations and joins into materialized views, reducing repeated full-scan query…. Analytics-heavy workloads pre-compute expensive aggregations and joins into materialized views, reducing repeated full-scan query cost from minutes per query to milliseconds per lookup. Key trade-off: Materialized views add write overhead (refresh cost) and storage overhead (duplicate data). Operational note: Refresh interval determines data freshness: every 1 hour is typical for BI dashboards. Evidence: Snowflake automatic clustering and materialized views reduce repeated full-scan aggregation by 10-100x. Core technology stack: postgresql, kafka, clickhouse.
Architectural Strengths
- ✓Analytics-heavy workloads pre-compute expensive aggregations and joins into materialized views, reducing repeated full-scan query…
- ✓ClickHouse's columnar storage engine, vectorized query execution, and MergeTree family of table engines are specifically designed…
- ✓Kafka is the standard downstream target for WAL-based CDC pipelines: Debezium captures database WAL records and publishes them to…
- ✓CQRS separates the write model (normalized, ACID) from the read model; materialized views implement the read model by…
Accepted Tradeoffs
- ⚠ClickHouse is optimised for high-throughput batch inserts; low-latency sub-millisecond inserts require insert buffer tuning and introduce eventual consistency lag
- ⚠CQRS separation means the analytics store is always eventually consistent with the OLTP source: query freshness depends on Kafka consumer lag and insert batch cadence
- ⚠Materialized views accelerate repeated expensive queries but require explicit refresh scheduling; stale views mislead dashboards if refresh is missed
- ⚠Kafka adds operational overhead (partition sizing, ISR configuration, retention policy) that must be maintained in addition to the analytics store
- ⚠Snowflake provides managed scaling at higher cost; ClickHouse provides raw throughput at higher operational complexity
Risks
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.
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.
A single consumer instance in a Kafka consumer group processes messages significantly slower than its peers, causing partition lag to accumulate on its assigned partitions and triggering consumer group rebalances that temporarily suspend all partition consumption.
Alternatives Considered
AI Retrieval-Augmented Generation Platform shares core technology (kafka, postgresql) with the chosen architecture but applies different structural patterns; Analytics Data Platform is a better fit for the identified workload profile.
API Gateway Platform shares core technology (kafka, postgresql) with the chosen architecture but applies different structural patterns; Analytics Data Platform is a better fit for the identified workload profile.
Audit and Compliance Platform shares core technology (clickhouse, kafka) with the chosen architecture but applies different structural patterns; Analytics Data Platform is a better fit for the identified workload profile.
Content Management Platform shares core technology (postgresql) with the chosen architecture but applies different structural patterns; Analytics Data Platform is a better fit for the identified workload profile.
Scaling Thresholds
Signals indicating the architecture is approaching its scaling limits:
Tier 1: Consumer Lag and Freshness Degradation
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
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
Tier 2: Hot Partition and Skewed Consumer Load
Signal: One Kafka partition offset growing significantly faster than others; one consumer instance CPU/network saturated while others are idle
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
Tier 3: ClickHouse Part Merge Backlog
Signal: ClickHouse system.parts shows parts_to_merge growing; SELECT queries showing slower p99 despite stable data volume; ClickHouse background merge thread CPU saturation
Evolution: Reduce insert frequency by increasing batch size; tune parts_to_delay_insert and parts_to_throw_insert; consider a Buffer table as an insert intermediary
Tier 4: Cross-Store Query Consistency Requirements
Signal: Business users reporting analytics figures inconsistent with OLTP dashboards; audit requirements necessitating exact match between operational and analytics figures
Evolution: Introduce event sourcing with snapshot consistency markers to align store states; or accept the eventual consistency model and document the staleness SLA explicitly in analytics tooling
Migration Path
Analytics queries running directly against PostgreSQL OLTP primary → Read replica serving analytics queries via polling ETL
OLTP query p99 degrading during analytics reporting windows; reporting queries showing wait events (LockTimeout, I/O wait) in pg_stat_activity
Polling ETL from read replica to analytics store → WAL CDC → Kafka → ClickHouse streaming ingestion
Sub-minute analytics freshness SLA required; ETL scheduling overhead growing; analytics volume exceeding what the read replica can serve under polling load
ClickHouse with raw event tables only → ClickHouse with materialized views and pre-aggregated summary tables
Dashboard query p95 > 5s on frequently accessed aggregation queries; analyst-driven queries competing with dashboard queries for ClickHouse CPU
Operational Requirements
- Minimum team maturity: Experienced Backend Team: This scenario has high operational complexity. It is recommended for Experienced Backend Team teams or higher.
- Runbooks and alerting for high-severity risks: 2 high-severity risks identified. Each requires a documented runbook, alerting threshold, and on-call response procedure before running in production.
- Event stream operations expertise: This architecture includes event stream infrastructure (Kafka, Kinesis, or similar). Operations requires consumer group management, partition assignment, dead-letter handling, and lag monitoring.
- Replica lag monitoring and lag-aware routing: Read replicas must be monitored for replication lag. The application router must include a max_lag_ms threshold; queries above that threshold must be redirected to the primary.