What’s the right database pattern for continuous ingestion (Kafka/CDC) plus fast analytics queries?

Most engineering teams discover the limits of their existing database pattern the hard way: Kafka (or CDC) is happily streaming events in 24/7, but the system that’s supposed to power real-time analytics dashboards, investigations, or AI features starts to sag under the load. Queries slow down, concurrency drops, and costs climb just as the business wants more data and faster answers.

Quick Answer: For continuous ingestion from Kafka or CDC plus fast analytics, you want a dedicated OLAP engine—typically ClickHouse—as the “speed layer” behind your streaming pipeline. In practice, that means: use Kafka/CDC for transport and change capture, stage and batch events, and land them into a columnar OLAP store optimized for millisecond queries over billions of rows, not into the transactional database or message bus itself.

Why This Matters

The wrong database pattern forces a tradeoff between ingestion and insight. If you optimize for ingest (e.g., log everything into Kafka or a row-store), analytics queries become slow or painfully expensive. If you optimize for queries (e.g., denormalized warehouse tables updated nightly), you lose the real-time edge that made streaming attractive in the first place.

The right pattern—streaming into a column-oriented OLAP database—lets you keep both:

• Continuous ingestion at high throughput.
• Millisecond-latency queries over fresh data.
• Cost-effective storage at terabyte to petabyte scale.

Key Benefits:

• Consistent real-time performance: Columnar storage, vectorized execution, and compression let you analyze billions of rows with millisecond results, even as ingest continues.
• Operational safety under heavy ingest: Batching and MergeTree-based storage in ClickHouse keep “too many parts” errors at bay and maintain healthy merges.
• Flexible deployment: You can run as managed ClickHouse Cloud, OSS ClickHouse, or ClickHouse Local for local analytics, depending on your environment and constraints.

Core Concepts & Key Points

Concept	Definition	Why it's important
Speed layer OLAP store	A dedicated analytical database (e.g., ClickHouse) fed by Kafka/CDC for real-time querying.	Decouples ingest from analytics so you can scale streaming and queries independently while delivering sub-second dashboards.
Columnar storage & vectorized execution	Data stored by column and processed in CPU-efficient batches instead of row-by-row.	Enables scanning and aggregating billions of rows with best-in-class compression and millisecond response for OLAP workloads.
Batching & MergeTree parts	The pattern of buffering rows and inserting in chunks into ClickHouse’s MergeTree tables.	Proper batching (1,000–100,000+ rows/insert) prevents part explosion, keeps merges healthy, and avoids “Too many parts” failures.

How It Works (Step-by-Step)

At a high level, the right pattern looks like this:

Kafka/CDC → (Optional stream processor) → ClickHouse (MergeTree) → Dashboards / APIs / AI features

1. Capture changes with Kafka or CDC

You start by continuously ingesting events:

• Kafka topics for application events, logs, metrics, user interactions, etc.
• CDC streams from OLTP databases (e.g., Debezium, cloud CDC) into Kafka topics.

In this stage, Kafka/CDC is only responsible for delivery and durability, not analytics.

Typical setup:

• One topic per logical stream (e.g., events_pageviews, orders_cdc).
• Compact schemas using Avro/Protobuf with a schema registry (in Kafka, especially with JDBC connectors, a schema is strongly recommended instead of ad-hoc JSON/CSV).

2. Transform & batch for OLAP ingestion

Next, you shape events into a form that’s efficient for ClickHouse:

• Optional stream processor: Kafka Connect, Flink, Kafka Streams, or a lightweight consumer that:
  • Flattens nested JSON.
  • Resolves schemas.
  • Adds derived columns (e.g., normalized timestamps, geohash, precomputed dimensions).
• Batching: Buffer records into micro-batches (e.g., 1,000–100,000+ rows) before inserting into ClickHouse.

In ClickHouse, ingestion is cheap and fast when you:

• Use INSERT in batches instead of single-row inserts.
• Target MergeTree-family tables (e.g., MergeTree, ReplacingMergeTree, ReplicatedMergeTree).
• Avoid high-cardinality partitioning on things like user IDs or request IDs.

A typical batched insert from a Kafka consumer might look like:

INSERT INTO events
FORMAT JSONEachRow
{"event_time": "2026-04-12T10:00:00Z", "user_id": 123, "path": "/home", "latency_ms": 12}
{"event_time": "2026-04-12T10:00:00Z", "user_id": 456, "path": "/search", "latency_ms": 37}
...

In ClickHouse Cloud or managed pipelines, you can also lean on connector/engine options, but the principle remains: batch events into sizeable inserts.

3. Store in a columnar OLAP engine for fast queries

Once batched into ClickHouse, queries become straightforward SQL:

• ClickHouse stores data column-by-column.
• Compression drastically reduces storage footprint and I/O.
• Vectorized execution uses CPU efficiently for aggregates, filters, and joins.

A basic table for clickstream or CDC-like data might be:

CREATE TABLE events
(
    event_time  DateTime,
    user_id     UInt64,
    path        String,
    latency_ms  UInt32,
    payload     String
)
ENGINE = MergeTree
PARTITION BY toDate(event_time)
ORDER BY (event_time, user_id);

Key choices here:

• PARTITION BY toDate(event_time): Lifecycle & retention-oriented. Low cardinality (≤365 partitions/year) and easy to drop by day.
• ORDER BY (event_time, user_id): Speeds ranges on time plus common filters on user_id.

From here, analytics becomes:

-- Millisecond-level dashboards
SELECT
    toStartOfMinute(event_time) AS minute,
    path,
    count(*) AS hits,
    quantile(0.95)(latency_ms) AS p95_latency
FROM events
WHERE event_time >= now() - INTERVAL 15 MINUTE
GROUP BY minute, path
ORDER BY minute DESC
LIMIT 100;

You can run this over billions of rows and still get millisecond results, even as new data continues to arrive from Kafka/CDC.

Common Mistakes to Avoid

• Using Kafka or the OLTP DB as your analytics engine:
  Kafka is a transport, not a query engine. OLTP stores are optimized for transactions, not wide scans. Avoid building dashboards directly on top of either; put ClickHouse in the middle as the OLAP speed layer.

• Tiny, frequent inserts causing “too many parts”:
  Inserting a handful of rows per message leads to a huge number of small parts in MergeTree tables. This stresses merges and can trigger “Too many parts” errors. Instead, batch to at least 1,000 rows per insert—ideally 10,000–100,000—and monitor system.parts for part counts per partition.

• Over-partitioning for performance:
  Partitioning in ClickHouse is primarily for data management and retention, not a universal speed hack. High-cardinality partitions (e.g., per user, per device) will create operational pain: too many partitions and slow merges. Prefer time-based partitioning with low cardinality (e.g., by day or month) and use ORDER BY + filters for performance.

• Ignoring system tables when tuning ingestion:
  If you don’t look at system.parts, system.merges, and system.query_log, you’re flying blind. Use them to verify that batching, merges, and queries behave as expected under sustained streaming load.

Real-World Example

I once helped migrate a high-cardinality observability backend from a mix of Elasticsearch and an OLTP replica to ClickHouse. The environment:

• Ingest: 3–5 million log/metric events per second via Kafka.
• Old pattern: Kafka → stream processor → Elasticsearch + row-store; dashboards became sluggish at ~30 days retention.
• Goal: Sub-second investigative queries on 90 days of data, without runaway storage and cluster costs.

We re-architected around the pattern described above:

1. Kafka as the ingest backbone: Topics per service or log type, with schemas defined in a registry.
2. Stream processors to batch: A small fleet of consumers aggregated ~50,000 rows per insert into ClickHouse, instead of single writes.
3. ClickHouse as the speed layer:
   • MergeTree tables, partitioned by day (toDate(timestamp)), ordered by (timestamp, service, level).
   • No per-tenant or per-service partitions to keep partition count manageable.
4. Operational guardrails:
   • Monitored system.parts to keep the number of active parts per partition in the low thousands, not tens of thousands.
   • Watched system.merges for long-running or stuck merges.
   • Used system.query_log to identify heavy queries and tune indexes / ORDER BY clauses.

Within a few weeks:

• Queries like “top error messages last 5 minutes for service X” were consistently under 100 ms, even at tens of billions of rows.
• We extended retention to 90 days without adding linearly more hardware, thanks to compression and columnar scans.
• Dashboards stopped breaking every time ingest spiked; merges stayed healthy because batching was tuned properly.

Pro Tip: In ClickHouse Cloud, use cluster-wide system tables (or clusterAllReplicas) to inspect system.parts and system.merges across all replicas. That’s how you catch a single node with a part explosion or stuck merges before it impacts your real-time analytics.

Summary

The right database pattern for continuous ingestion from Kafka/CDC plus fast analytics is a streaming + OLAP architecture:

• Kafka/CDC for capturing and transporting events in real time.
• A thin transformation and batching layer to shape data for OLAP ingest.
• ClickHouse as the column-oriented analytical engine that can query billions of rows with millisecond latency.

This pattern separates concerns: ingest and durability live in Kafka/CDC, while ClickHouse handles what it’s built for—blazing-fast analytics at scale, with practical levers (batching, partitioning, system tables) to keep operations healthy.

Next Step

Get Started