ClickHouse S3 Integration: Object Storage

Tip: S3 Table Function

SELECT * FROM s3('https://bucket.s3.amazonaws.com/data.csv', 'CSV', 'id UInt64, name String');

Gotcha: S3 Storage Policy

<storage_configuration>
    <disks>
        <s3>
            <type>s3</type>
            <endpoint>https://bucket.s3.amazonaws.com/</endpoint>
        </s3>
    </disks>
</storage_configuration>

Tip: Cold Data to S3

ALTER TABLE events MOVE PARTITION 202301 TO DISK 's3';

Gotcha: S3 Performance

S3 reads are slower than local disk. Use for archival data only.

Tip: Tiered Storage

Hot data on SSD, warm on HDD, cold on S3.

Gotcha: S3 Costs

Frequent S3 reads can be expensive. Cache frequently accessed data locally.

Tip: Order of Columns in ORDER BY Matters Massively

ClickHouse's primary key is defined by ORDER BY. Put high-cardinality columns first for better data skipping. ORDER BY (timestamp, user_id) is very different from ORDER BY (user_id, timestamp) in query performance.

Tip: Use LowCardinality for Enum-Like Strings

Strings like status, country, browser benefit from LowCardinality(String) — it's stored as a dictionary internally, reducing storage 10x and speeding up scans.

Gotcha: Mutations Are Heavy

ALTER TABLE ... UPDATE and DELETE in ClickHouse create new parts instead of modifying in place. A single mutation on a large table can take hours and block merges. Design for append-only from day one.

Senior Insight

ClickHouse's S3 integration enables direct querying of data stored in S3 buckets. I use this for cost-effective storage of infrequently accessed data. The S3 table engine reads Parquet, CSV, JSON, and other formats directly from S3. For a data lake architecture, I store raw logs on S3 in Parquet format and query them directly from ClickHouse without loading into ClickHouse storage. The query performance is about 3-5x slower than local storage, but the storage cost is 10x cheaper.

Source: ClickHouse Blog (https://clickhouse.com/blog), Altinity Blog (https://altinity.com/blog), Altinity Knowledge Base (https://kb.altinity.com/)