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 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 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 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 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:
- Read blocks from S3
- Parse and decode (CPU-intensive)
- Form insert blocks grouped by partition
- Sort by ORDER BY key
- Compress and write parts
- 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:
- Query needs part not in cache
- Download from S3
- Evict least recently used parts
- 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, 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 from S3 data is.