Data Compression and Storage Optimization in ClickHouse with Spring Boot

December 01, 2025

ClickHouse provides built-in compression and optimization features that can reduce storage size and improve query speed. Let’s explore how to use them with Spring Boot.

1. Create Table with Compression

CREATE TABLE logs (
    id UUID,
    ts DateTime,
    message String CODEC(ZSTD(3))
) ENGINE = MergeTree()
ORDER BY (ts);

Here, ZSTD(3) applies Zstandard compression to the message column.

2. Optimize Table Storage

ALTER TABLE logs MODIFY COLUMN message String CODEC(LZ4HC(9));

Switch compression algorithms anytime to balance speed vs storage.

3. Insert Data with JdbcTemplate

jdbcTemplate.update(
    "INSERT INTO logs (id, ts, message) VALUES (?, ?, ?)",
    UUID.randomUUID(), LocalDateTime.now(), "System started"
);

4. Enable TTL for Old Data

CREATE TABLE sensor_data (
    device_id String,
    ts DateTime,
    value Float64
) ENGINE = MergeTree()
ORDER BY (device_id, ts)
TTL ts + INTERVAL 30 DAY DELETE;

This automatically removes records older than 30 days.

5. Optimize Storage Periodically

OPTIMIZE TABLE logs FINAL;

This command merges parts and ensures compression efficiency.

6. Query Data in Spring Boot

List<Map<String, Object>> logs = jdbcTemplate.queryForList(
    "SELECT id, ts, message FROM logs ORDER BY ts DESC LIMIT 10"
);