Add cache_httpfs for Faster S3 Reads

[khalid244]

Summary

Add cache_httpfs DuckDB extension to cache S3 Parquet files in memory, improving query performance for Grafana dashboards with many panels.

Changes

// internal/database/duckdb.go

// Cache S3 Parquet files in memory
db.Exec("INSTALL cache_httpfs FROM community")
db.Exec("LOAD cache_httpfs")
db.Exec("SET cache_httpfs_type='in_memory'")

CTE/Subquery Performance Comparison

Run	With cache_httpfs	Without cache_httpfs	Improvement
1	268 ms	1,801 ms	6.7x faster
2	399 ms	4,124 ms	10.3x faster
3	527 ms	2,082 ms	4.0x faster
4	406 ms	2,612 ms	6.4x faster
5	492 ms	5,238 ms	10.6x faster
Average	418 ms	3,171 ms	7.6x faster
Total	2,092 ms	15,857 ms	-13,765 ms saved

Why This Helps

When a Grafana dashboard has queries with subqueries (CTEs), the same Parquet files are read multiple times. Without caching, each read downloads from S3. With cache_httpfs, the first read caches the data and subsequent reads are served from memory.

-- This query reads 'measurements' table twice
WITH top_regions AS (
  SELECT region FROM measurements WHERE time > X GROUP BY region LIMIT 50
)
SELECT * FROM measurements WHERE region IN (SELECT region FROM top_regions)

[xe-nvdk]

Great suggestion, @khalid244! The performance improvements you've measured (7.6x average speedup) are significant, especially for Grafana dashboards with CTEs/subqueries.

Analysis

Your proposed solution is technically sound. DuckDB's cache_httpfs extension specifically addresses the problem of repeated S3 reads in queries that access the same Parquet files multiple times.

Benefits:

• Significant performance improvement (5-10x faster)
• Reduced S3 data transfer costs
• Better user experience for Grafana dashboards
• Simple implementation (3 lines of code)

Considerations:

• Memory usage increases (cached Parquet files in RAM)
• Should be configurable (opt-in) rather than enabled by default
• Need to provide memory limit controls
• Users should understand the trade-offs

Recommended Implementation

This feature should be opt-in and configurable via arc.toml:

[query]
# Enable S3 file caching for faster repeated reads (useful for CTEs/subqueries)
# Trade-off: Increases memory usage, but can improve query time by 5-10x
enable_s3_cache = false  # Default: disabled

# Optional: Set memory limit for S3 cache (e.g., "1GB", "500MB")
s3_cache_memory_limit = ""  # Default: unlimited

Implementation in internal/database/duckdb.go:

func (d *DuckDB) setupExtensions(ctx context.Context) error {
    // ... existing extension setup ...

    if d.config.Query.EnableS3Cache {
        d.logger.Info().Msg("Installing cache_httpfs extension for S3 file caching")

        if _, err := d.db.ExecContext(ctx, "INSTALL cache_httpfs FROM community"); err != nil {
            d.logger.Warn().Err(err).Msg("Failed to install cache_httpfs extension")
            return nil  // Don't fail startup
        }

        if _, err := d.db.ExecContext(ctx, "LOAD cache_httpfs"); err != nil {
            d.logger.Warn().Err(err).Msg("Failed to load cache_httpfs extension")
            return nil
        }

        if _, err := d.db.ExecContext(ctx, "SET cache_httpfs_type='in_memory'"); err != nil {
            d.logger.Warn().Err(err).Msg("Failed to configure cache_httpfs")
            return nil
        }

        // Optional memory limit
        if d.config.Query.S3CacheMemoryLimit != "" {
            sql := fmt.Sprintf("SET cache_httpfs_memory_limit='%s'", d.config.Query.S3CacheMemoryLimit)
            if _, err := d.db.ExecContext(ctx, sql); err != nil {
                d.logger.Warn().Err(err).Msg("Failed to set cache_httpfs memory limit")
            }
        }

        d.logger.Info().Msg("S3 file caching enabled")
    }

    return nil
}

Would you like to submit a PR?

Since you've already done the performance testing and have a clear understanding of the benefits, this would be a great PR to contribute! The implementation would include:

1. Configuration in internal/config/config.go
2. Extension setup in internal/database/duckdb.go
3. Tests to verify the extension loads correctly
4. Documentation in arc.toml explaining the trade-offs

Alternatively, I can implement this based on your findings. Let me know your preference!

Questions

Before implementation, it would help to understand:

1. What DuckDB version are you testing with? (to verify cache_httpfs availability)
2. Did you test memory usage with the cache enabled?
3. What was the size of your test dataset?

This will help ensure the feature works well across different scenarios.

[khalid244]

Thanks for the questions! Here are the details:

1. DuckDB Version: v1.4.3 (tested on Arc)
2. Memory Testing: Yes, I tested memory usage with the cache enabled.
3. Dataset Size: 800 million records

I will prepare a PR for this. I don't think we should add s3_cache_memory_limit since cache_httpfs uses 128MB max and deletes the data after 1 hour. I will prepare a solution that uses disk and memory.

[khalid244]

With cache_httpfs we have two options:

Option A: In-memory storage - Data will be deleted after 1 hour. This will help with queries but only for a short time.

Option B: On-disk storage - Data will stay on disk for a longer time. This will be better for people who may have latency between S3 and Arc, but I think this may cause issues when delete operations happen.

Which road should we go with? What do you think?

[xe-nvdk]

I would go with option A.

Lets work on this without rushing it, lets aim to release this for 26.03.1, makes sense?

[khalid244]

Let's make In-memory in 26.02.1. And do heavy testing on On-disk to see if it will be worth to add in 26.03.1.

[xe-nvdk]

Thanks for the update, @khalid244! Let me share some thoughts on the disk caching option.

In-Memory (Option A) - Approved for 26.02.1

In-memory caching is a great fit for Arc because:

• Ephemeral by design: Cache disappears when the process restarts, no stale data concerns
• Self-cleaning: 1-hour TTL means automatic cleanup
• Bounded memory: 128MB max is reasonable and predictable
• No corruption risk: Nothing persisted to disk means nothing to corrupt

On-Disk (Option B) - Concerns

I have reservations about disk caching that I'd like to share:

1. Ephemeral Compute Philosophy

Arc is designed for environments where compute is 100% ephemeral - containers can be killed and restarted at any time (Kubernetes pods, Docker containers, serverless functions). Adding disk-based cache creates state that:

• Must survive restarts (or be rebuilt)
• Could become corrupted during unclean shutdowns
• Adds complexity around cache invalidation

2. Cache Corruption on Recovery

When Arc restarts after a crash or container restart:

• The disk cache may contain stale data from files that have since been compacted or deleted
• There's no mechanism to validate cache consistency with the actual S3 data
• Serving stale/corrupted data is worse than a cache miss

3. Delete Operation Consistency

As you mentioned, delete operations are a concern:

• When a user deletes data from S3, the disk cache still has the old data
• Queries could return "deleted" records until cache expires
• This violates Arc's data consistency guarantees

4. Disk Space Management

Unlike in-memory (bounded to 128MB), disk caching raises questions:

• How much disk space to allow?
• What happens when disk fills up?
• NVMe vs HDD performance differences
• Shared disk with data directory vs separate mount?

Recommendation

Let's focus on in-memory caching for 26.02.1 as agreed. For disk caching, I'd suggest we evaluate it carefully against our SSD Cache roadmap which already addresses local caching with:

• SQLite metadata for cache tracking
• LRU eviction policies
• Cache invalidation on delete/compaction
• Configurable size limits

The SSD Cache feature is designed from the ground up to handle the consistency and invalidation concerns. We might be able to leverage cache_httpfs as a lightweight alternative or use it alongside the SSD Cache for different use cases.

What do you think? Happy to discuss further!

[khalid244]

Thanks for the detailed breakdown! Let me address each concern about Option B (On-Disk):

1. Ephemeral Compute Philosophy

While Arc is designed for ephemeral compute, disk caching can still work in this model:

• Treat disk cache as purely opportunistic - if it exists, use it; if not, rebuild
• No requirement for cache to survive restarts - just a performance optimization when it does
• Same philosophy as in-memory, just with larger capacity potential
• Disk caching is especially helpful when RAM is limited - it essentially acts as extended memory, giving you the benefits of a larger cache without the RAM cost

2. Cache Corruption on Recovery

This is not a concern - disk cache will be cleared entirely on startup/recovery. No stale or corrupted data will ever be served.

3. Delete Operation Consistency

We have several options here:

• Subscribe to delete events and invalidate corresponding cache entries
• Use shorter TTL for disk cache compared to in-memory
• Validate cache files periodically (e.g., every hour) to ensure they haven't been deleted
• Disable disk caching entirely when DELETE operations are enabled

4. Disk Space Management

This is handled by cache_httpfs - it automatically deletes cache when there's disk pressure. We can also configure max size.

Here's the default config in cache_httpfs - as you can see there are several options we can test to see which will be best for us:

Setting	Default Value
cache_httpfs_type	on_disk
cache_httpfs_cache_block_size	524288 (512 KB)
cache_httpfs_cache_directory	/tmp/duckdb_cache_httpfs_cache
cache_httpfs_cache_directories_config	(empty)
cache_httpfs_clear_cache_on_write	0 (disabled)
cache_httpfs_disk_cache_reader_enable_memory_cache	0 (disabled)
cache_httpfs_disk_cache_reader_mem_cache_block_count	256
cache_httpfs_disk_cache_reader_mem_cache_timeout_millisec	1800000 (30 min)
cache_httpfs_enable_cache_validation	0 (disabled)
cache_httpfs_enable_file_handle_cache	1 (enabled)
cache_httpfs_enable_glob_cache	1 (enabled)
cache_httpfs_enable_metadata_cache	1 (enabled)
cache_httpfs_evict_policy	creation_timestamp
cache_httpfs_file_handle_cache_entry_size	250
cache_httpfs_file_handle_cache_entry_timeout_millisec	3600000 (1 hour)
cache_httpfs_glob_cache_entry_size	64
cache_httpfs_glob_cache_entry_timeout_millisec	1800000 (30 min)
cache_httpfs_ignore_sigpipe	0 (disabled)
cache_httpfs_in_mem_cache_block_timeout_millisec	3600000 (1 hour)
cache_httpfs_max_fanout_subrequest	0 (unlimited)
cache_httpfs_max_in_mem_cache_block_count	256
cache_httpfs_metadata_cache_entry_size	250
cache_httpfs_metadata_cache_entry_timeout_millisec	3600000 (1 hour)
cache_httpfs_min_disk_bytes_for_cache	0
cache_httpfs_profile_type	noop

That said, I agree these add complexity. Happy to proceed with in-memory for 26.02.1 and do heavy testing on disk caching to see if the benefits outweigh the added complexity for 26.03.1.