Optimizing ClickHouse Performance in Spring Boot

November 12, 2025

This guide shows how to optimize ClickHouse performance when integrating with Spring Boot for fast queries and efficient resource use.

1. Use Connection Pooling

Add HikariCP for efficient connection reuse.

spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=30000

2. Optimize JDBC Settings

Enable compression and keep-alive.

spring.datasource.url=jdbc:clickhouse://localhost:8123/default?compress=1&tcp_keep_alive=1

3. Reduce Data Transfer

Always select only required columns.

@Query(value = "SELECT product, SUM(amount) FROM sales GROUP BY product", nativeQuery = true)
List<Object[]> productSummary();

4. Pagination for Large Data

Never fetch everything in one go.

@Query(value = "SELECT * FROM sales ORDER BY id LIMIT :limit OFFSET :offset", nativeQuery = true)
List<Sale> findPaged(@Param("limit") int limit, @Param("offset") int offset);

5. Use Aggregations Instead of Raw Reads

ClickHouse is built for analytics. Favor SUM(), AVG(), COUNT(), etc. instead of fetching raw data.

6. Index and MergeTree

When creating tables, always define sorting keys to improve query performance.

CREATE TABLE sales (
  id UInt32,
  product String,
  amount Float32,
  region String
) ENGINE = MergeTree()
ORDER BY (region, product);

7. Caching Results

For repeated queries, add caching with Spring Cache or Redis.

@Cacheable("salesByRegion")
public List<Object[]> salesByRegion() {
  return repo.salesByRegion();
}