Real-time petabyte analytics without slowing your production DB
Separate transactions from analytics
The result: a dashboard query that should take 200ms takes 40 seconds as analytics scans 500M rows, crushing CPU and I/O. Users wait too long for query results, and in the end the entire SLA agreement hangs in the balance.
Keep PostgreSQL focused on transactional reads and writes, while OVHcloud Managed ClickHouse handles analytical queries. For real-time petabyte data analytics, S3-compatible object storage provides a scalable capacity layer beyond primary high-speed storage, allowing your analytical environment to grow without expanding the production database.
Performance and control at scale
![[object Object]](/_aiwf-assets/1c4b1724-8eb2-433e-817d-75cffa9e8203_ite_79aa0c4f-d7be53.svg){kind=small}
![[object Object]](/_aiwf-assets/1c4b1724-8eb2-433e-817d-75cffa9e8203_ite_2d4e9ac8-c53d5f.svg){kind=small}
![[object Object]](/_aiwf-assets/1c4b1724-8eb2-433e-817d-75cffa9e8203_ite_1f60228d-2c4451.svg){kind=small}
![[object Object]](/_aiwf-assets/1c4b1724-8eb2-433e-817d-75cffa9e8203_ite_0703c5fb-2398dd.svg){kind=small}
Why production database scaling falls short for analytical demand
Transactional database pressure
Transactional databases excel at short lookups, inserts, and updates. Real-time petabyte data analytics creates a different demand: broad scans, complex joins, and repeated aggregations that compete with production traffic for CPU, memory, and storage throughput. A production Public Cloud database instance is built for transactions, not analytics. When business teams start asking for real-time dashboards, fraud detection, or customer segmentation queries against terabytes of historical data… well, performance collapses.
Read replica limitations
Read replicas in Public Cloud Databases are the most common first response to analytics pressure on production databases. They offload read traffic from the primary node, but they don't fix the architectural mismatch.A read replica might reduce contention on your primary database, but when you run SELECT SUM(revenue), COUNT(*) FROM events WHERE date >= '2024-01-01' across 500M rows, the replica still reads and discards almost every row. These analytics queries compete with production transactions for CPU, memory, and I/O. App latency rises, SLAs are at risk, and real-time use cases like fraud detection or anomaly monitoring become impossible.
Durable workload separation
A durable approach separates the workloads. Apache Kafka streams committed events into a dedicated platform built for petabyte-scale analytics. Production databases continue serving transactions, while the analytical environment scales independently for data at scale, streaming analytics, artificial intelligence, and business intelligence.
Assign each workload to the right engine
They store data row by row, optimized for looking up a single customer record or updating an order status in milliseconds.
OLAP systems, by contrast, are designed to scan trillions of rows for complex aggregations. They use columnar storage, where each column is stored independently. This means when you query SUM(revenue) WHERE date > '2025-01-01', the database reads only the revenue and date columns, skipping everything else.So, for example in ClickHouse, operations run in vectorized execution. ClickHouse processes entire arrays of values at once rather than individual rows, which dramatically reduces CPU overhead and maximizes cache utilization.
No amount of data indexing, query optimization, or hardware upgrades will make a row-oriented database efficient at columnar scans. The architectures are fundamentally different.
Choose a loading pattern for each data source
Streaming
Deliver events continuously when operational dashboards and alerts depend on low-latency insight.
Batch processing
Use batch ETL with Airflow to schedule historical backfills, periodic exports, and bulk source loads.
Change Data Capture
Propagate committed database changes while limiting additional work on the transactional source.
Direct ingestion
Let applications write analytical events directly when your teams own and govern the event contract.
Separating OLTP from OLAP
When you query SUM(revenue) across 5 billion rows, it reads only the revenue column, skipping everything else. This reduces I/O by 90% or more and enables aggressive compression since values in a column are similar.
Vectorized execution is the second multiplier. Traditional databases process one row at a time. ClickHouse processes entire arrays of values at once, loading a chunk of the revenue column into CPU cache and applying operations across the entire vector volume in a single instruction. This dramatically reduces CPU overhead.
The combination enables sub-second queries on billions of rows. Columnar storage minimizes disk reads; vectorized execution minimizes CPU work. ClickHouse can aggregate 10 billion rows in under a second while a PostgreSQL instance takes minutes.
![[object Object]](/_aiwf-assets/76542f62-8c49-48e5-92ec-910825c8d69c_ima_e302634e-55d7e5.webp)
Design for freshness, speed, resilience, and governance
Batch ETL
Batch ETL is the simplest starting point.
Extract data from your production database on a schedule (hourly or daily), transform it with dbt, and load it into ClickHouse. Airflow orchestrates the pipeline, or cron jobs handle simple scripts.
This works when you don't need minute-by-minute freshness. Hourly dashboards are fine for many business cases, and batch processing is easier to debug and monitor.
The trade-off is the latency you create. Data is stale between batches, so real-time fraud detection or live personalization aren't possible. Batch ETL is right when you're starting with ClickHouse, lack streaming expertise, or tolerate hour-old data.
Real-time CDC
Change Data Capture (CDC) streams every insert, update, and delete from production to ClickHouse in near real-time. Debezium reads your database's transaction log (WAL for PostgreSQL, binlog for MySQL) and publishes changes to Apache Kafka. ClickHouse consumes from Kafka and inserts immediately.
CDC tools achieve sub-second to low-second latency, enabling true real-time data analytics: fraud detection that detects and blocks transactions as they happen, anomaly monitoring that alerts within seconds, personalization that reacts instantly to support user behavior.
Tiered storage to keep costs linear at petabyte scale
Storing petabytes on SSDs is prohibitively expensive. Tiered storage moves older data to cheaper object storage while keeping recent data on fast local disks.
For example, OVHcloud Managed ClickHouse implements this natively in tools with OVHcloud Object Storage (S3-compatible)* stream processing. Recent data (30–90 days) stays on stream on NVMe SSDs.
Older data automatically migrates to an OVHcloud Object Storage bucket at a fraction of the cost, still enabling fast queries since ClickHouse reads only needed columns. Costs for engineers stay linear as you scale: each additional terabyte costs the same at 10 TB or 100 TB.
Pre-aggregation at ingest time
Even with columnar data storage, scanning raw data every time is expensive.
Materialized views pre-compute aggregates at ingest time.
In ClickHouse, a materialized view is an active pipeline, not a passive snapshot. When you insert data, ClickHouse automatically evaluates the view's query and writes results to a separate table.
If you're ingesting 100,000 events per second with a view aggregating by minute and segment, ClickHouse computes those aggregates in real time.
Why ClickHouse is the right engine for real-time analytics
- ClickHouse achieves sub-second latency on queries scanning billions of rows thanks to columnar storage, vectorized execution, and aggressive compression. It can aggregate 10 billion rows in under a second while PostgreSQL takes minutes and other warehouses take seconds to tens of seconds.
- ClickHouse delivers stable, resource-based pricing with high concurrency and efficient scaling, ideal for interactive real-time analytics. Redshift is best for batch-oriented data warehousing with predictable workloads and existing AWS infrastructure. BigQuery excels at serverless analytics with sporadic queries and teams that want zero operational overhead.
- ClickHouse uses a SQL dialect that's 90% compatible with the standard SQL development experience, but key differences exist. Aggregate functions use MergeTree tables with specific engine syntax. Most SELECT queries translate directly, but complex JOINs and subqueries may need optimization.
