The Dual-Engine Analytics Design:

In modern industrial and resource companies, tens of thousands of telemetry data points from global production facilities, programmable logic controllers (PLCs), and IoT gateways are generated every second. Traditional relational databases and conventional data warehouse setups cannot handle this load: aggregation queries over historical periods block operational dashboards, write operations accumulate in buffers, and hardware costs for monolithic storage appliances scale exponentially.

The architectural answer to this dilemma lies in the deliberate separation based on query and data characteristics. By orchestrating TimescaleDB for complex relational time series analyses and ClickHouse for massively parallel columnar aggregations on the ayedo Managed Kubernetes platform, a highly elastic dual-engine architecture emerges—operated on European bare-metal storage with guaranteed sub-second response times.

1. The Problem: The Limits of Monolithic Database Systems

Attempting to consolidate high-frequency sensor data streams and analytical queries into a universal standard database creates significant operational bottlenecks:

• 1. I/O Saturation with Massive Write Loads: Traditional relational databases write data row-by-row and maintain complex indexes synchronously. With continuous ingest rates of hundreds of gigabytes per day, I/O throughput collapses, leading to backlogs in upstream streaming pipelines.
• 2. Blocking of Relational Queries by Global Aggregations: When operational control centers perform time-critical status queries for specific machines while data engineers simultaneously calculate historical year-over-year comparisons over billions of data points, shared buffer pools and transaction locks collapse.
• 3. Cost Explosion Due to Inefficient Storage Compression: Unstructured time series data occupy immense storage space on standard file systems. Without specialized column compression and automated partition pruning, costs for fast NVMe storage arrays explode.

2. The Solution: The Decoupled Dual-Engine Architecture

ayedo implements a specialized data storage layer on Kubernetes , which declaratively splits and persists incoming Apache Kafka streams based on access patterns.

+——————————————————————————-+ | Apache Kafka / Event-Streaming Backbone (Sensor & Telemetry Data) | +—————————————+—————————————+ | +——————–+——————–+ | | v v +————————————+ +————————————+ | Pod: TimescaleDB (Hypertables) | | Pod: ClickHouse Cluster | | - Relational Metadata Coupling | | - Columnar Engine | | - Point Queries & Status Lookups | | - Massively Parallel Aggregations | | - Hybrid Chunk Management | | - Up to 90% Data Compression | +——————+—————–+ +——————+—————–+ | | +——————–+——————–+ | (CSI Storage Interface) v +——————————————————————————-+ | Highly Available Ceph NVMe Storage Pool (ayedo Managed Infrastructure) | +——————————————————————————-+

• 1. Relational Hypertables Routing via TimescaleDB: Time series closely related to relational master data (e.g., machine types, maintenance histories, shift schedules) are directed into partitioned hypertables. TimescaleDB enables complex SQL joins with minimal latency overhead for operational control center dashboards.
• 2. Massively Parallel Vectorization via ClickHouse: High-frequency, unstructured measurements are ingested columnar into ClickHouse. Through vectorization and hardware-near SIMD instruction sets, ClickHouse executes aggregation queries over terabytes of data in mere milliseconds—with a compression rate of up to 90% compared to raw formats.
• 3. Dynamic Storage Tiering on Ceph: The persistent volumes of both database engines are managed via the Ceph-CSI driver. Hot, current ingest data remains on high-performance NVMe pools, while historical chunks are automatically migrated to more cost-effective capacity pools according to defined retention policies.

3. Strategic and Economic Value

Establishing the dual-engine analytics design transforms unstructured data volumes into a highly performant, economically predictable competitive advantage:

• Drastic TCO Reduction through Specialized Compression: The column-based compression of ClickHouse and automatic chunk-tiering reduce physical storage requirements by up to 80% compared to traditional relational systems.
• Guaranteed SLA Compliance for Manufacturing Decisions: Aggregation and ad-hoc queries are reliably answered within sub-second ranges. Plant managers and automated quality controls receive real-time insights without latency delays.
• 100% GDPR , NIS-2, and BSI-C5 Compliance: All database instances and storage volumes run isolated on European bare-metal or private cloud infrastructure. There is no uncontrolled data transfer to external cloud analytics SaaS services.
• No Egress Fees and Unrestricted Portability: By avoiding proprietary hyperscaler databases (such as BigQuery or Redshift), variable data egress fees and vendor lock-ins are completely eliminated.

Conclusion

Industrial data analytics in the terabyte range requires specialized tools instead of monolithic compromises. By precisely combining TimescaleDB and ClickHouse on a managed Kubernetes platform, ayedo demonstrates that maximum query speed, optimal storage efficiency, and strict data sovereignty perfectly interlock—planned, scalable, and future-proof.

FAQ: Practical Questions about the Dual-Engine Analytics Design

Why not rely solely on ClickHouse if it is so performant in aggregations?

ClickHouse is excellent for columnar mass data and read aggregations but not designed for transactional consistency (ACID guarantees) or complex relational joins with deeply nested master data. TimescaleDB fills this gap by combining full PostgreSQL with time-series-specific hypertable partitioning. The coexistence of both systems combines relational flexibility with extreme aggregation performance.

How is data consistency ensured between Kafka, TimescaleDB, and ClickHouse?

Apache Kafka acts as a central, persistent message log. Custom containerized consumer pipelines read the data streams in parallel and idempotently from the topics. If one of the database instances is temporarily blocked due to maintenance or rebalancing, the data remains buffered in the Kafka offset and is caught up without data loss once availability is restored.

What operational effort is required to manage two database clusters on Kubernetes?

Through Kubernetes operators (such as the ClickHouse Operator from Altinity and the CloudNativePG/Timescale-Operator), administrative tasks like automated backups, node failover, rolling upgrades, and storage expansions are declaratively controlled via GitOps. This reduces the manual operational effort for the internal IT team to the level of a fully managed cloud service.