← Back to the section

Let's say you already know why ClickHouse is useful: fast analytics, aggregations over millions of rows that PostgreSQL takes tens of seconds to compute. Now the practical question: how do you connect it to a Spring Boot service, how does data get in, and how do you read it back out?

Let's walk through it step by step: connecting, writing, reading, and testing.

Connecting: a second DataSource alongside PostgreSQL

Most services already have PostgreSQL as their primary database. ClickHouse is a second store that lives alongside it, not instead of it. Spring Boot lets you keep several DataSources at once.

The official driver is com.clickhouse:clickhouse-jdbc. It works over the ClickHouse HTTP interface (port 8123), not the native protocol. That's convenient: firewall rules are simpler, and no special client is needed.

Configuration:

@Configuration
public class ClickHouseConfig {

    @Bean
    @ConfigurationProperties("app.clickhouse")
    DataSourceProperties clickHouseDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    DataSource clickHouseDataSource() {
        var hikari = clickHouseDataSourceProperties()
            .initializeDataSourceBuilder()
            .type(HikariDataSource.class)
            .build();
        hikari.setMaximumPoolSize(5);
        return hikari;
    }

    @Bean
    JdbcClient clickHouseJdbcClient(@Qualifier("clickHouseDataSource") DataSource dataSource) {
        return JdbcClient.create(dataSource);
    }
}

app:
  clickhouse:
    url: jdbc:clickhouse://clickhouse.internal:8123/analytics
    username: app_analytics
    password: ${CLICKHOUSE_PASSWORD}

A few details that matter here:

  • The pool is small — at most 5 connections. Analytical queries are heavy and run rarely; 50 parallel aggregations can bring the server down.
  • The primary PostgreSQL DataSource stays exactly as it is. @Transactional and jOOQ keep working with it, never noticing ClickHouse.
  • ClickHouse has no transactions, so the new client needs no transactional wrapping at all.

Writing: why not to write directly from the service

The first idea is usually this: after saving an order, add a line clickHouse.insert(...). It looks simple — but it's a double write to two different systems with no guarantees. If ClickHouse is unavailable, the business operation breaks. If the app crashes between the two writes, data is lost or drifts out of sync. On top of that, single INSERTs into ClickHouse quickly lead to the TOO_MANY_PARTS error — the engine can't merge the small data chunks fast enough.

The reliable path looks different:

PostgreSQL (outbox in the same transaction) → Kafka → batch consumer → ClickHouse

The service writes only to PostgreSQL. The event goes to Kafka through the outbox. A separate consumer accumulates events and inserts them in batches. Each step is independent: if ClickHouse goes down, Kafka simply waits. When it comes back, the consumer resumes from where it left off.

Batch consumer: accumulate and insert

@Component
@RequiredArgsConstructor
public class OrderEventsClickHouseSink {

    private final JdbcClient clickHouseJdbcClient;

    @KafkaListener(topics = "order-events", batch = "true",
                   containerFactory = "batchContainerFactory")
    public void consume(List<ConsumerRecord<String, OrderEventPayload>> records,
                        Acknowledgment ack) {
        insertBatch(records.stream().map(r -> r.value()).toList());
        ack.acknowledge();
    }

    private void insertBatch(List<OrderEventPayload> events) {
        var sql = """
            INSERT INTO order_events
                (event_id, event_time, event_type, region, customer_id, order_id, amount)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            """;
        clickHouseJdbcClient.sql(sql)
            .params(events.stream().map(this::toRow).toList())
            .batchUpdate();
    }
}

What you tune on the Kafka side: max.poll.records in the thousands (batch size), fetch.max.wait.ms — hundreds of milliseconds (so you don't hit ClickHouse too often). Commit the offset only after a successful insert. If something goes wrong, the consumer re-reads the same batch.

Async insert as a simpler option

If the data flow is small, you can enable async insert on the ClickHouse side: the server itself accumulates small inserts in a buffer and flushes them as a batch. It's turned on via async_insert=1 in the connection settings or in the query itself — the application stops having to worry about accumulation.

The downside: the buffer lives in the memory of the ClickHouse server. If it crashes before the flush, data is lost. For metrics and counters that's acceptable. For money-related events it isn't — there you need the Kafka pipeline.

Kafka engine inside ClickHouse

A third option is to drop the Java consumer entirely: ClickHouse can read from Kafka itself via ENGINE = Kafka plus a materialized view. Data lands in the table directly, with no intermediate code.

The price: all the logic (mapping, retries, alerts) moves into ClickHouse DDL. The Java team doesn't see it and doesn't control it. This option works well when a separate team owns the analytics layer. If the service is responsible for its own data, an explicit Java consumer is clearer.

Idempotency: what to do about repeated inserts

Kafka delivers events at least once. After a failure the consumer re-reads part of the batch, and the same rows get inserted again. There are two ways to protect yourself.

Block-level deduplication. Replicated tables remember the hashes of the last inserted batches. If the same batch arrives again, ClickHouse simply ignores it. This works exactly in the case of "crashed between the INSERT and the offset commit, then rebuilt the same batch from the same events".

ReplacingMergeTree by event id. The ReplacingMergeTree ORDER BY event_id engine collapses rows with the same key during background merges, keeping the latest version. Duplicates will land in the table but disappear on merge. Before the merge, uniq(event_id) will count correctly, count() won't.

Which one to pick depends on the task. Financial reports usually need both. For product analytics ReplacingMergeTree is often enough.

Reading: a separate repository for analytics

Analytical queries are separated from the rest of the code into a dedicated repository with explicit DTOs. This is the familiar read-side pattern: one layer writes, another reads.

@Repository
@RequiredArgsConstructor
public class RevenueViewRepository {

    private final JdbcClient clickHouseJdbcClient;

    public List<RevenueByRegionRow> revenueByRegion(LocalDate from, LocalDate to) {
        return clickHouseJdbcClient.sql("""
                SELECT region, sumMerge(revenue) AS revenue, uniqMerge(orders) AS orders
                FROM revenue_by_region_daily
                WHERE day BETWEEN ? AND ?
                GROUP BY region
                ORDER BY revenue DESC
                """)
            .params(from, to)
            .query((rs, i) -> new RevenueByRegionRow(
                rs.getString("region"),
                rs.getBigDecimal("revenue"),
                rs.getLong("orders")))
            .list();
    }
}

public record RevenueByRegionRow(String region, BigDecimal revenue, long orders) {}

A few hygiene rules:

  • The app_analytics user for the reading service is read-only. A different pipeline user writes to ClickHouse.
  • Set max_execution_time and max_memory_usage on the user profile: a random heavy query must not take down the cluster.
  • ClickHouse lags behind PostgreSQL by seconds to minutes. That's normal, but the API contract must be honest: "the report is current as of the last sync", not "real-time data".

For analytical queries with sumIf, argMax, FINAL functions, code generation doesn't help — you write the SQL by hand via JdbcClient and map the result into explicit DTOs.

CDC as an alternative to outbox

The outbox pipeline works well for domain events ("order paid", "goods shipped"). But sometimes you need not an event but a mirror of a table — for example, a copy of the product catalog or a snapshot of order state.

Change Data Capture (CDC) via Debezium fits here: the tool reads the PostgreSQL change log (WAL) and publishes changes to Kafka. From there it's the same pipeline: Kafka → consumer → ClickHouse. The service itself doesn't change at all — CDC captures changes at the database level.

In ClickHouse such data is usually stored in ReplacingMergeTree by primary key with a version from the LSN or an updated_at field. When a row is updated in PostgreSQL, a new version arrives in ClickHouse, and the merge keeps the current one.

Testing with Testcontainers

Testcontainers supports ClickHouse and starts a real server in a Docker container right inside the test:

@Testcontainers
class RevenueViewRepositoryTest {

    @Container
    static ClickHouseContainer clickHouse =
        new ClickHouseContainer("clickhouse/clickhouse-server:24.8");

    @Test
    void aggregatesRevenueByRegion() {
        insertTestEvents();
        var rows = repository.revenueByRegion(
            LocalDate.of(2026, 5, 1), LocalDate.of(2026, 5, 31));
        assertThat(rows).extracting(RevenueByRegionRow::region)
            .containsExactly("msk", "spb");
    }
}

You create the schema with the same DDL scripts used in production.

An important nuance for ReplacingMergeTree tables: the background merge is not deterministic in a test. If the test checks the "latest version" after an update, you need to either read through FINAL (as the production code does) or explicitly run OPTIMIZE TABLE ... FINAL before the check. Don't rely on the merge happening on its own.

In short

  • The driver is com.clickhouse:clickhouse-jdbc, working over HTTP (port 8123).
  • ClickHouse is connected as a second DataSource; the primary PostgreSQL and @Transactional are untouched.
  • Don't write to ClickHouse directly from a handler: a double write is unreliable and single INSERTs hurt the engine.
  • The reliable path: PostgreSQL → outbox → Kafka → batch consumer → ClickHouse.
  • For small flows, async insert on the server side; for financial data, Kafka only.
  • Idempotency: block deduplication + ReplacingMergeTree by id.
  • Reading — a separate repository with explicit DTOs; write the SQL by hand, no code generation needed.
  • For mirroring tables (not events), use CDC via Debezium instead of outbox.
  • Tests — Testcontainers + real DDL; for ReplacingMergeTree, force the merge before the check.
  • Modeling and queries — the table schemas this pipeline writes into.
  • Operations — what to monitor on the pipeline from the ClickHouse side.
  • Distributed patterns — outbox, idempotency, and eventual consistency in general terms.
  • Kafka in production — consumers, event accumulation, dead-letter queues.