---
title: "ClickHouse Integration with Amazon S3: Patterns & Reality"
excerpt: "Boost ClickHouse integration with Amazon S3 using real cloud architecture patterns and proven performance strategies."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-01-20 00:00:00"
publishedOn: "2026-01-20 00:00:00"
updatedOn: "2026-01-20 00:00:00"
status: "published"
---

Your S3-to-ClickHouse® integration looked elegant in the design doc. Data lake queries without loading data. Cheap cold storage for historical analytics. Separation of storage and compute. The proof-of-concept queried a few Parquet files beautifully.

Then you deployed to production.

Now queries that scanned 100 files take 30 seconds. Part count explodes during batch ingestion from S3. Cache hit rates hover at 5%. AWS bills show massive data transfer through NAT gateways you didn't know existed. S3 returns 503 SlowDown errors during peak ingestion hours.

The problem isn't that S3 and ClickHouse are incompatible. **Most teams fundamentally misunderstand that S3 integration has three completely different architectural patterns—data lake queries, batch ingestion, and tiered storage—each with distinct performance characteristics and failure modes.**

ClickHouse integration with Amazon S3 serves three purposes: **querying data lake files directly without loading them**, **using S3 as batch ingestion source for MergeTree tables**, and **S3-backed MergeTree for hot/cold tiered storage**. Each pattern solves different problems and breaks in different ways.

The choice isn't about S3 features or ClickHouse capabilities—it's about **which query latency you can tolerate and whether your data access patterns match S3's strengths**.

## **The Three Integration Patterns (And Which Problems They Actually Solve)**

Before choosing functions or configuring storage policies, understand that each S3 integration pattern serves fundamentally different use cases. Each of these patterns interacts differently with your underlying [database](https://www.oracle.com/database/what-is-database/) architecture and determines how efficiently data can be queried, ingested, and stored.

### **s3() function: Query data lake files directly**

The `s3()` table function creates a virtual table over files in S3 with wildcard support:

SELECT date, count() AS events  
FROM s3(  
  'https://bucket.s3.amazonaws.com/events/date=2026-01-\*/part-\*.parquet',  
  'Parquet'  
)  
WHERE event\_type \= 'purchase'  
GROUP BY date;

**What this enables:** Ad-hoc exploration, validation before loading, one-time backfills, data export.

**What this doesn't provide:** Primary indexes, query caching, ClickHouse-optimized storage, materialized views.

**When this works:** Exploratory analytics, infrequent historical queries, ETL validation.

**When this breaks:** Sub-second latency requirements, high concurrency, production dashboards.

### **S3 as batch ingestion source for MergeTree**

**Pattern: Read from S3, write to MergeTree for serving:**

INSERT INTO events\_mt  
SELECT \* FROM s3('https://bucket.s3.amazonaws.com/raw/\*.parquet', 'Parquet');

**This treats S3 as the ingestion channel, not the query layer.** Data lands in S3 (from Firehose, EMR, Spark), ClickHouse ingests in batches, queries hit optimized MergeTree.

**Benefits:** MergeTree provides indexes and caching, S3 provides durable replayable source, decouple production from consumption, and simplifies the interaction between your ClickHouse cluster and any [downstream system](https://medium.com/@ogunodabas/downstream-upstream-system-c1dc6cf4b59e) consuming analytics results.

**When this works:** Batch analytics pipelines, daily/hourly ETL, data lake → warehouse pattern.

### **S3-backed MergeTree: Tiered storage architecture**

**ClickHouse stores MergeTree data parts directly in S3** via storage configuration. Recent data on local NVMe, older data in S3 with local cache.

**Benefits:** 95% cost reduction vs EBS, unlimited storage capacity, maintains MergeTree advantages, and supports additional optimizations such as [projections](https://www.tinybird.co/blog/projections) for more efficient query planning.

**Trade-offs:** Higher query latency (mitigated by cache), requires local metadata storage, more complex operations.

**When this works:** Large datasets with temporal access patterns, cost-sensitive workloads, long retention.

## **Querying S3 Data Directly: When Files Replace Tables**

For data lake queries without loading data, understand how ClickHouse actually reads from S3.

### **s3Cluster() for parallel processing**

**Cluster-parallel query:**

SELECT count()  
FROM s3Cluster(  
  'production\_cluster',  
  's3://bucket/events/\*.parquet',  
  'Parquet'  
);

**How s3Cluster() parallelizes:** Coordinator lists S3 objects, assigns files to workers dynamically. **Each file becomes a unit of work.**

**Critical limitation: Parallelism ceiling \= number of files.** With 3 nodes and 2 files, one node sits idle. **For effective cluster utilization, you need more files than nodes.**

**One customer:** "1,000-node cluster querying 50 large Parquet files. Cluster utilization: 5%. Repartitioned to 5,000 smaller files, utilization jumped to 85%."

### **Format choice matters: Parquet vs CSV vs JSON**

**Parquet advantages:**

* Columnar format aligns with ClickHouse internals  
* 50-70% smaller than JSON  
* Supports predicate pushdown (skip row groups)  
* Fast deserialization vs text parsing

**CSV/JSON disadvantages:**

* Sequential parsing required  
* No column pruning  
* Text parsing CPU-intensive  
* Larger files \= more bandwidth

**For S3 data lakes queried by ClickHouse, Parquet with Snappy or ZSTD compression provides the best balance.**

### **Why this pattern breaks at scale**

**The fundamental problem: S3 queries lack optimizations that make ClickHouse fast.**

**Missing from s3() queries:** No primary index, no sparse index, no query result cache, no materialized views.

**Performance comparison:**

**s3() function (100M rows, 1GB Parquet):**

* Cold query: 8-12 seconds  
* Repeat query: 8-12 seconds (no cache)  
* With filters: 8-12 seconds (full scan)

**MergeTree (100M rows):**

* Cold query: 1-2 seconds  
* Repeat query: 50-200ms (cached)  
* Indexed filters: 100-500ms (granule skipping)

**For production analytics with latency requirements, s3() is a tool for ingestion and exploration, not serving.**

## **Performance Reality: Where S3 Integration Actually Breaks**

**Theoretical S3-ClickHouse integration looks simple. Production reality involves network topology, S3 API limits, and file layout, all of which are common challenges in modern [cloud computing](https://www.ibm.com/think/topics/cloud-computing) environments.**

### **Same-region requirement and VPC endpoint necessity**

**Network topology determines performance more than ClickHouse configuration.**

**Same region:**

* ClickHouse us-east-1 → S3 us-east-1: 5-15ms latency  
* ClickHouse us-east-1 → S3 eu-west-1: 80-150ms latency \+ cross-region costs

**VPC endpoint requirement:**

**Without VPC endpoint:**

ClickHouse (private subnet) → NAT Gateway → Internet Gateway → S3  
Cost: $0.045/GB (NAT) \+ $0.045/GB (processing) \= $0.09/GB

**With VPC endpoint (Gateway Endpoint for S3):**

ClickHouse (private subnet) → VPC Endpoint → S3 (AWS backbone)  
Cost: Zero additional charges

**For 10TB/month S3 traffic: $900 saved monthly with VPC endpoints.**

### **Prefix design and S3 request rate limits**

**S3 per-prefix limits:**

* **3,500 PUT/COPY/POST/DELETE per second**  
* **5,500 GET/HEAD per second**

**"Prefix" \= everything before final `/` in object key.**

**The hotspot problem:**

All writes to `/clickhouse/`:

10 nodes × 500 PUT/s \= 5,000 PUT/s attempted  
S3 limit: 3,500 PUT/s per prefix  
Result: 503 SlowDown errors, throttling

**Solution: Distribute across prefixes:**

/clickhouse/shard-0/  
/clickhouse/shard-1/  
/clickhouse/shard-2/

**Now each prefix gets independent 3,500 PUT/s limit.**

### **Small files vs large files: The parallelization trap**

**Conventional wisdom: "More files \= better parallelism."** This is half-true.

**File-level parallelism:**

* 1,000 files across 100 nodes \= 10 files/node (good)  
* 10 files across 100 nodes \= 90 nodes idle (poor)

**But: Many tiny files create API overhead:**

* 10,000 files × 1MB \= 10,000 GET requests  
* 100 files × 100MB \= 100 GET requests

**Each S3 GET has \~10-30ms latency overhead.** For 10GB:

* 10,000 small files: 10,000 × 15ms \= 150s API latency  
* 100 large files: 100 × 15ms \= 1.5s API latency

**Optimal balance:**

* **File size: 50-500 MB**  
* **File count: 5-10x node count**

## **Batch Ingestion from S3: Avoiding Part Explosions**

Reading from S3 and writing to MergeTree seems straightforward. **The problem is how ClickHouse creates data parts during ingestion.**

### **INSERT INTO MergeTree FROM s3(): The mechanics**

**What happens:**

1. Read blocks from S3  
2. Parse and decode (CPU-intensive)  
3. Form insert blocks grouped by partition  
4. Sort by ORDER BY key  
5. Compress and write parts  
6. Background merges consolidate parts

**Each insert creates new parts. Too many inserts \= part explosion.**

### **Block sizing and merge saturation**

**Two settings control block formation:**

**`min_insert_block_size_rows`:** Minimum rows per insert block (default: 1M)  
 **`min_insert_block_size_bytes`:** Minimum bytes in memory (default: 256MB)

**Small blocks create many parts:**

\-- BAD: Creates 1,000 tiny parts  
INSERT INTO events SELECT \* FROM s3(...)  
SETTINGS min\_insert\_block\_size\_rows \= 1000;

**Large blocks create fewer parts:**

\-- GOOD: Creates \~10 larger parts  
INSERT INTO events SELECT \* FROM s3(...)  
SETTINGS min\_insert\_block\_size\_rows \= 10000000;

**Part count monitoring:**

SELECT table, count() AS parts  
FROM system.parts  
WHERE active  
GROUP BY table  
HAVING parts \> 1000;

**If parts \> 1,000:** Background merges can't keep up.

**Symptoms:** Queries slow down, `Too many parts` errors, high disk I/O, memory pressure.

**Solution: Increase block size, reduce insert frequency, batch more data per insert.**

## **Download and Upload Tuning: Buffer Sizes and Multipart**

Beneath high-level queries, ClickHouse makes thousands of S3 API calls. **Tuning these calls matters at scale.**

### **max\_download\_buffer\_size and parallelization thresholds**

**ClickHouse downloads S3 files in parallel byte ranges** when files are large enough.

**Key settings:**

* **`max_download_threads`:** Parallel download threads (default: 4\)  
* **`max_download_buffer_size`:** Buffer size for ranges (default: 10MB)

**Parallelization threshold: File must be \> `2 × max_download_buffer_size` for parallel download.**

**With 10MB buffer:**

* 15MB file: Single thread (too small)  
* 50MB file: 2-4 parallel ranges  
* 500MB file: Max parallel ranges

**Counter-intuitive tuning: Increasing buffer size can improve performance by reducing API calls:**

SET max\_download\_buffer\_size \= 52428800;  \-- 50MB

**Now files under 100MB download in single thread. Fewer API calls, lower latency overhead.**

**When to increase buffer:** Many medium files (20-100MB), high latency to S3, S3 returning 503 SlowDown.

**When to decrease buffer:** Very large files benefit from more parallelism, low latency environment.

### **Multipart upload configuration for writes**

**Writing to S3 uses multipart uploads** for large objects.

**Key settings:**

* **`s3_max_single_part_upload_size`:** Max for single PUT (default: 32MB)  
* **`s3_min_upload_part_size`:** Min per multipart part (default: 16MB)

**Why multipart matters:** Retrying 5GB failed upload wastes time. Multipart retries only failed parts (16MB chunks). Parallel part uploads improve throughput.

### **Rate limiting to avoid 503 SlowDown responses**

**S3 returns `503 SlowDown` when request rate exceeds prefix capacity.**

**ClickHouse provides client-side rate limiting:**

SET s3\_max\_get\_rps \= 1000;  \-- Limit GET/s  
SET s3\_max\_put\_rps \= 500;   \-- Limit PUT/s

**When to use:** Batch jobs that would overwhelm S3, shared buckets, cost control.

## **Cache Architecture: Mark Cache, Filesystem Cache, and Disk Cache**

**S3-backed ClickHouse relies on multiple caching layers.** Understanding which cache helps what is critical.

### **Three caching layers and what each accelerates**

**1\. Mark cache and uncompressed cache (MergeTree internals):**

* Stores index marks for granule skipping  
* Stores decompressed column blocks  
* **Works with:** S3-backed MergeTree  
* **Accelerates:** Repeated queries, indexed lookups

**2\. Filesystem cache (for s3() queries):**

* Caches downloaded file content from `s3()` function  
* Enabled per-query: `enable_filesystem_cache=1`  
* **Validates with ETag** (no stale data)  
* **Works with:** Ad-hoc S3 queries, not S3-backed tables

**3\. Disk cache (for S3 storage disk):**

* LRU cache wrapping S3 disk in storage config  
* Automatically caches parts read from S3  
* **Works with:** S3-backed MergeTree tables  
* **Accelerates:** Recently-accessed parts

**Common confusion: Expecting disk cache to help s3() queries.** It doesn't. **Filesystem cache and disk cache are separate.**

### **LRU disk cache for S3-backed MergeTree**

**Disk cache uses Least Recently Used eviction:**

**Cache at max capacity:**

1. Query needs part not in cache  
2. Download from S3  
3. Evict least recently used parts  
4. Store new part in cache

**Tuning cache size:**

\<max\_size\>500Gi\</max\_size\>

**Cache sizing strategy:**

* **Working set fits:** Size to hold frequently queried data  
* **Working set exceeds:** Size to reduce average latency to acceptable threshold

**Monitor cache hit rate:**

SELECT  
    sum(size) / (SELECT sum(bytes\_on\_disk) FROM system.parts) AS ratio  
FROM system.filesystem\_cache;

**Target: 80%+ hit rate for good performance.**

## **Security and Cost Optimization**

Production S3 integration requires secure authentication and cost control.

### **IAM roles vs hardcoded credentials**

**Never hardcode AWS credentials.**

**Use IAM roles with `use_environment_credentials`:**

\<s3\_disk\>  
  \<endpoint\>https://bucket.s3.region.amazonaws.com/\</endpoint\>  
  \<use\_environment\_credentials\>true\</use\_environment\_credentials\>  
\</s3\_disk\>

**ClickHouse obtains credentials from:** EC2 instance metadata, ECS task role, EKS service account, environment variables.

**Benefits:** No credential rotation in config, centralized access control, audit trail, least privilege.

### **SSE-KMS encryption and request quota implications**

**S3 encryption options:**

**SSE-S3 (default):** Zero performance impact, zero cost  
 **SSE-KMS:** Every operation requires KMS API call

**KMS has request quotas:**

* Decrypt: 5,500 requests/second (shared pool)  
* GenerateDataKey: 5,500 requests/second

**With SSE-KMS, S3 throughput can be limited by KMS quotas.** If ClickHouse makes 10,000 GET/s to S3, you need 10,000 KMS Decrypt/s.

**KMS quota exceeded \= throttling \= slow queries.**

**For high-throughput ClickHouse, SSE-S3 is typically sufficient.** Use SSE-KMS only when compliance requires it.

## **How Tinybird Simplifies S3 Integration**

Everything discussed—storage policies, cache tuning, part explosions, download buffer sizing, prefix design—requires deep ClickHouse and AWS expertise.

**Tinybird eliminates this complexity for S3-to-analytics pipelines.**

### **Native S3 connectors without storage configuration**

**No disk definitions. No storage policies. No cache configuration.**

Tinybird provides native S3 Data Sources with automatic file discovery, intelligent batching for optimal part creation, managed cache without tuning, multi-format support.

**You point to S3 path and format. Tinybird handles ingestion optimization.**

One customer: "Spent two weeks tuning ClickHouse storage config for S3—cache sizes, move policies, block settings. Tried Tinybird, uploaded S3 configuration, data queryable in 5 minutes. Zero tuning."

### **Automatic batch optimization and merge management**

**Traditional S3-ClickHouse ingestion requires:** Manual batch size tuning, part count monitoring, merge queue management, memory allocation for parallel inserts.

**Tinybird handles automatically:** Optimal batch sizing based on data volume, merge management without part explosions, memory allocation without tuning, incremental ingestion for append-only patterns.

### **No cache tuning or part explosion monitoring**

**S3-backed ClickHouse operational burden:** Monitor cache hit rates, tune cache sizes, watch part counts, adjust block sizes, debug slow queries from cache misses.

**Tinybird operational model:** No cache configuration exposed, automatic optimization based on query patterns, zero part count monitoring, consistent query performance, automatic cache warm-up.

### **SQL to API from S3 data instantly**

After solving S3 ingestion, traditional ClickHouse still requires API layer, authentication, caching, deployment infrastructure.

**Tinybird transforms SQL into production APIs:**

SELECT  
    toStartOfHour(event\_time) AS hour,  
    event\_type,  
    count() AS event\_count  
FROM s3\_events  
WHERE event\_time \>= now() \- INTERVAL {{Int32(hours, 24)}} HOUR  
  AND user\_id \= {{String(user\_id, required=True)}}  
GROUP BY hour, event\_type

**Becomes:**

GET /api/v0/pipes/events.json?hours=24\&user\_id=abc123

**Built-in:** Type-safe parameters, authentication, caching, monitoring, sub-100ms latency.

### **Real production example: E-commerce analytics on S3**

One e-commerce company with 500GB daily S3 data dumps:

**Before Tinybird:** ClickHouse cluster with S3 tiered storage, 2 engineers managing storage policies, cache tuning, part count monitoring, batch job scheduling, custom API layer (3,000+ lines), query latency 2-8s.

**After Tinybird:** S3 Data Sources with automatic ingestion, zero storage configuration, zero cache tuning, APIs auto-generated, query latency 100-400ms.

**Engineering time recovered: 12+ hours/week.** Infrastructure cost reduction: 35%.

The fundamental insight: **Most teams don't want to operate S3-ClickHouse integration—they want analytics from S3 data.** Tinybird provides the latter without requiring you to become an expert in storage policies, cache tuning, or merge management.

This approach also enables advanced analytics use cases such as [real-time personalization](https://www.tinybird.co/blog/real-time-personalization), where S3 data streams can drive dynamic user experiences and contextual decisions without heavy infrastructure overhead.

## **Choose Based on Query Patterns and Latency Requirements**

ClickHouse integration with Amazon S3 works. Companies query petabytes in S3 data lakes and serve production analytics from S3-backed storage.

But **"works" and "performs predictably" are different things.**

**s3() function complexity:** No indexes, no caching, full scans, unpredictable latency, format and file layout critical.

**Batch ingestion complexity:** Block size tuning, part explosion prevention, merge saturation management, memory allocation.

**S3-backed storage complexity:** Storage policies, cache configuration, metadata persistence, network topology optimization.

**Traditional path:** Hire engineers with deep ClickHouse and AWS expertise. Tune storage configs. Monitor cache hit rates. Debug part explosions. Accept operational burden.

**Tinybird path:** Native S3 connectors with automatic optimization. Zero configuration. Zero tuning. SQL to API. Production analytics without S3 integration expertise.

For teams building S3-to-ClickHouse pipelines: **choose based on latency requirements and operational capacity**. Tuning download buffers and storage policies? Or shipping analytics features customers use?

The choice is yours. For most teams, mastering ClickHouse S3 storage internals isn't the goal—delivering fast [real-time analytics](https://www.tinybird.co/blog/real-time-analytics-a-definitive-guide) from S3 data is.