---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: cache_prewarm
excerpt: |
DuckDB Community Extensions
Prewarm data blocks into DuckDB's buffer pool or OS page cache for faster queries
extension:
name: cache_prewarm
description: Prewarm data blocks into DuckDB's buffer pool or OS page cache for faster queries
version: 0.3.0
language: C++
build: cmake
license: MIT
excluded_platforms: "wasm_mvp;wasm_eh;wasm_threads;windows_amd64_rtools;windows_amd64;windows_amd64_mingw"
maintainers:
- peterxcli
- dentiny
repo:
github: dentiny/duckdb-cache-prewarm
ref: c1ab04d975ccbbf6b037c4f11066f4e2d606cab8
docs:
hello_world: |
-- Prewarm a table into the buffer pool
SELECT prewarm('table_name');
-- With explicit mode
SELECT prewarm('table_name', 'buffer');
-- With size limit (human-readable format)
SELECT prewarm('table_name', 'buffer', '1GB');
-- With qualified table name
SELECT prewarm('my_schema.table_name');
-- Prewarm a remote file into the on-disk cache
SELECT prewarm_remote('https://example.com/data/file.parquet');
-- Prewarm with a glob pattern (multiple files)
SELECT prewarm_remote('https://example.com/data/*.parquet');
extended_description: |
A DuckDB extension that preloads table data blocks into the buffer pool or OS page cache,
inspired by PostgreSQL's pg_prewarm extension.
Prewarm Modes:
- `buffer` (default): Load blocks into buffer pool with pin/unpin
- `read`: Warm OS page cache without using buffer pool
- `prefetch`: Batch prefetch using OS hints
Remote Prewarm:
- `prewarm_remote` preloads remote files into the on-disk cache, requires `cache_httpfs` to be configured.
- Supports glob patterns and size limits.
Use cases include cold start optimization and predictable query latency.
extension_star_count: 11
extension_star_count_pretty: 11
extension_download_count: 16638
extension_download_count_pretty: 16.6k
image: '/images/community_extensions/social_preview/preview_community_extension_cache_prewarm.png'
layout: community_extension_doc
---
### Compatibility Matrix
This table covers all DuckDB releases that have not reached [end-of-life](https://duckdb.org/release_calendar), including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
| :--- | :---: | :---: | :---: | :---: | :---: |
| Linux AMD64 | ✅ | ❌ | ✅ | ✅ | ✅ |
| Linux ARM64 | ✅ | ❌ | ✅ | ✅ | ✅ |
| macOS AMD64 | ✅ | ❌ | ✅ | ✅ | ✅ |
| macOS ARM64 | ✅ | ❌ | ✅ | ✅ | ✅ |
| Windows AMD64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| Windows ARM64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| WebAssembly | ❌ | ❌ | ❌ | ❌ | ❌ |
### Installing and Loading
```sql
INSTALL {{ page.extension.name }} FROM community;
LOAD {{ page.extension.name }};
```
{% if page.docs.hello_world %}
### Example
```sql
{{ page.docs.hello_world }}```
{% endif %}
{% if page.docs.extended_description %}
### About {{ page.extension.name }}
{{ page.docs.extended_description }}
{% endif %}
### Added Functions
| function_name | function_type | description | comment | examples |
|-----------------------------------------------|---------------|------------------------------------------------------------------------------------------------------------------------------|---------|-------------------------------------------------------------------------------------|
| cache_httpfs_add_exclusion_regex | scalar | Adds a regular expression for remote paths that should bypass the cache. | NULL | [SELECT cache_httpfs_add_exclusion_regex('.*\.tmp$');] |
| cache_httpfs_cache_access_info_query | table | Returns hit, miss, byte, and latency statistics for each cache entity. | NULL | [SELECT * FROM cache_httpfs_cache_access_info_query();] |
| cache_httpfs_cache_status_query | table | Returns cached data entries with their local path, remote path, byte range, and cache type. | NULL | [SELECT * FROM cache_httpfs_cache_status_query();] |
| cache_httpfs_cleanup_dead_temp | scalar | Deletes stale temporary cache files and returns the number deleted. | NULL | [SELECT cache_httpfs_cleanup_dead_temp();] |
| cache_httpfs_clear_cache | scalar | Clears all data, metadata, file handle, glob, and profile caches. | NULL | [SELECT cache_httpfs_clear_cache();] |
| cache_httpfs_clear_cache_for_file | scalar | Clears cached entries for one remote file. | NULL | [SELECT cache_httpfs_clear_cache_for_file('s3://bucket/file.parquet');] |
| cache_httpfs_clear_file_handle_cache | scalar | Closes and clears cached file handles without affecting other caches. | NULL | [SELECT cache_httpfs_clear_file_handle_cache();] |
| cache_httpfs_clear_file_handle_cache_for_file | scalar | Closes and clears cached file handles for one remote file without affecting other caches. | NULL | [SELECT cache_httpfs_clear_file_handle_cache_for_file('s3://bucket/file.parquet');] |
| cache_httpfs_clear_profile | scalar | Clears cache profile statistics for the current connection. | NULL | [SELECT cache_httpfs_clear_profile();] |
| cache_httpfs_get_cache_config | table | Returns all current cache_httpfs configuration values. | NULL | [SELECT * FROM cache_httpfs_get_cache_config();] |
| cache_httpfs_get_cache_filesystems | table | Lists filesystem implementations currently wrapped by cache_httpfs. | NULL | [SELECT * FROM cache_httpfs_get_cache_filesystems();] |
| cache_httpfs_get_cache_type | table | Returns the active cache type and whether caching is enabled. | NULL | [SELECT * FROM cache_httpfs_get_cache_type();] |
| cache_httpfs_get_data_cache_config | table | Returns the current data cache configuration. | NULL | [SELECT * FROM cache_httpfs_get_data_cache_config();] |
| cache_httpfs_get_file_handle_cache_config | table | Returns the current file handle cache configuration. | NULL | [SELECT * FROM cache_httpfs_get_file_handle_cache_config();] |
| cache_httpfs_get_glob_cache_config | table | Returns the current glob cache configuration. | NULL | [SELECT * FROM cache_httpfs_get_glob_cache_config();] |
| cache_httpfs_get_metadata_cache_config | table | Returns the current metadata cache configuration. | NULL | [SELECT * FROM cache_httpfs_get_metadata_cache_config();] |
| cache_httpfs_get_ondisk_data_cache_size | scalar | Returns the total size in bytes of files in the configured on-disk cache directories. | NULL | [SELECT cache_httpfs_get_ondisk_data_cache_size();] |
| cache_httpfs_get_profile | scalar | Returns human-readable cache profile statistics for the current connection. | NULL | [SELECT cache_httpfs_get_profile();] |
| cache_httpfs_list_exclusion_regex | table | Lists the path exclusion regular expressions. | NULL | [SELECT * FROM cache_httpfs_list_exclusion_regex();] |
| cache_httpfs_list_registered_filesystems | table | Lists filesystem implementations registered with DuckDB. | NULL | [SELECT * FROM cache_httpfs_list_registered_filesystems();] |
| cache_httpfs_reset_exclusion_regex | scalar | Removes all path exclusion regular expressions. | NULL | [SELECT cache_httpfs_reset_exclusion_regex();] |
| cache_httpfs_wrap_cache_filesystem | scalar | Wraps a registered DuckDB filesystem with the cache filesystem. | NULL | [SELECT cache_httpfs_wrap_cache_filesystem('AzureBlobStorageFileSystem');] |
| prewarm | scalar | Preloads a local DuckDB table into the buffer pool or operating system page cache and returns the number of bytes prewarmed. | NULL | [SELECT prewarm('events');] |
| prewarm | scalar | Preloads a local DuckDB table into the buffer pool or operating system page cache and returns the number of bytes prewarmed. | NULL | [SELECT prewarm('events', 'buffer', '1GB');] |
| prewarm | scalar | Preloads a local DuckDB table into the buffer pool or operating system page cache and returns the number of bytes prewarmed. | NULL | [SELECT prewarm('events', 'buffer', 1000000);] |
| prewarm | scalar | Preloads a local DuckDB table into the buffer pool or operating system page cache and returns the number of bytes prewarmed. | NULL | [SELECT prewarm('events', 'prefetch');] |
| prewarm_remote | scalar | Preloads files matching a path or URL pattern into the cache_httpfs cache and returns the number of bytes prewarmed. | NULL | [SELECT prewarm_remote('https://example.com/data.parquet');] |
| prewarm_remote | scalar | Preloads files matching a path or URL pattern into the cache_httpfs cache and returns the number of bytes prewarmed. | NULL | [SELECT prewarm_remote('https://example.com/data.parquet', '100MB');] |
| prewarm_remote | scalar | Preloads files matching a path or URL pattern into the cache_httpfs cache and returns the number of bytes prewarmed. | NULL | [SELECT prewarm_remote('https://example.com/data.parquet', 1000000);] |
### Overloaded Functions
This extension does not add any function overloads.
### Added Types
This extension does not add any types.
### Added Settings
| name | description | input_type | scope | aliases | typed_value |
|-----------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------------|--------|---------|--------------------------------|
| cache_httpfs_cache_block_size | Block size for cache, applies to both in-memory cache filesystem and on-disk cache filesystem. It's worth noting for on-disk filesystem, all existing cache files are invalidated after config update. | UBIGINT | GLOBAL | [] | 524288 |
| cache_httpfs_cache_directories_config | Advanced configuration for on-disk cache. It supports multiple directories, separated by semicolons (';'). Cache blocks will be evenly distributed under different directories deterministically.Between different runs, it's expected to provide same cache directories, otherwise it's not guaranteed cache files still exist and accessible.Overrides 'cache_httpfs_cache_directory' if set. | VARCHAR | GLOBAL | [] | |
| cache_httpfs_cache_directory | The disk cache directory that stores cached data | VARCHAR | GLOBAL | [] | /tmp/duckdb_cache_httpfs_cache |
| cache_httpfs_clear_cache_on_write | Whether to clear cache entries on write operations. When enabled, write operations will invalidate cached metadata, file handles, and glob entries for the modified file, which could be expensive.Disabling this can improve write performance when many cache entries exist, but may lead to stale cache reads. By default disabled. | BOOLEAN | GLOBAL | [] | 0 |
| cache_httpfs_disk_cache_reader_enable_memory_cache | Whether enable process-wise read-through/write-through cache for disk cache reader. When enabled, local cache file will be accessed with direct IO. | BOOLEAN | GLOBAL | [] | 1 |
| cache_httpfs_disk_cache_reader_mem_cache_block_count | Max number of cache blocks for the read-through/write-through cache for disk cache reader. | UBIGINT | GLOBAL | [] | 256 |
| cache_httpfs_disk_cache_reader_mem_cache_timeout_millisec | Timeout in milliseconds for the read-through/write-through cache for disk cache reader. | UBIGINT | GLOBAL | [] | 1800000 |
| cache_httpfs_enable_cache_validation | Whether to enable cache validation using version tag and last modification timestamp. When enabled, cache entries are validated against the current file version tag and modification timestamp to ensure cache consistency. By default disabled. | BOOLEAN | GLOBAL | [] | 0 |
| cache_httpfs_enable_file_handle_cache | Whether file handle cache is enable for cache filesystem. By default enabled. | BOOLEAN | GLOBAL | [] | 1 |
| cache_httpfs_enable_glob_cache | Whether glob cache is enable for cache filesystem. By default enabled. | BOOLEAN | GLOBAL | [] | 1 |
| cache_httpfs_enable_metadata_cache | Whether metadata cache is enable for cache filesystem. By default enabled. | BOOLEAN | GLOBAL | [] | 1 |
| cache_httpfs_evict_policy | Eviction policy for on-disk cache cache blocks. By default it's creation timestamp based ('creation_timestamp'), which deletes all cache blocks created earlier than threshold. Other supported policy include 'lru_single_proc' (LRU forsingle process access), which performs LRU-based eviction, mainly made single processusage. | VARCHAR | GLOBAL | [] | creation_timestamp |
| cache_httpfs_file_handle_cache_entry_size | Max cache size for file handle cache. | UBIGINT | GLOBAL | [] | 250 |
| cache_httpfs_file_handle_cache_entry_timeout_millisec | Cache entry timeout in milliseconds for file handle cache. | UBIGINT | GLOBAL | [] | 3600000 |
| cache_httpfs_glob_cache_entry_size | Max cache size for glob cache. | UBIGINT | GLOBAL | [] | 64 |
| cache_httpfs_glob_cache_entry_timeout_millisec | Cache entry timeout in milliseconds for glob cache. | UBIGINT | GLOBAL | [] | 1800000 |
| cache_httpfs_ignore_sigpipe | Whether to ignore SIGPIPE for the extension. By default not ignored. Once ignored, it cannot be reverted. | BOOLEAN | GLOBAL | [] | 0 |
| cache_httpfs_in_mem_cache_block_timeout_millisec | Data block cache entry timeout in milliseconds. | UBIGINT | GLOBAL | [] | 3600000 |
| cache_httpfs_in_mem_cache_storage | Storage backend for in-memory data block cache. 'extension' (default) uses an extension-managed LRU bounded by `cache_httpfs_max_in_mem_cache_block_count` and `cache_httpfs_in_mem_cache_block_timeout_millisec`. 'object_cache' parks blocks in DuckDB's per-instance ObjectCache. Must be set before any cache access for the change to take effect. | VARCHAR | GLOBAL | [] | extension |
| cache_httpfs_max_fanout_subrequest | Cached httpfs performs parallel request by splittng them into small request, with request size decided by config [cache_httpfs_cache_block_size]. The setting limits the maximum request to issue for a single filesystem read request. 0 means no limit, by default we set no limit. | BIGINT | GLOBAL | [] | 0 |
| cache_httpfs_max_in_mem_cache_block_count | Max in-memory cache block count for in-memory caches for all cache filesystems, so users are able to configure the maximum memory consumption. It's worth noting it should be set only once before all filesystem access, otherwise there's no affect. | UBIGINT | GLOBAL | [] | 256 |
| cache_httpfs_metadata_cache_entry_size | Max cache size for metadata LRU cache. | UBIGINT | GLOBAL | [] | 250 |
| cache_httpfs_metadata_cache_entry_timeout_millisec | Cache entry timeout in milliseconds for metadata LRU cache. | UBIGINT | GLOBAL | [] | 3600000 |
| cache_httpfs_min_disk_bytes_for_cache | Min number of bytes on disk for the cache filesystem to enable on-disk cache; if left bytes is less than the threshold, LRU based cache file eviction will be performed.By default, 5% disk space will be reserved for other usage. When min disk bytes specified with a positive value, the default value will be overriden. | UBIGINT | GLOBAL | [] | 0 |
| cache_httpfs_parallel_read_mode | Thread model used for parallel cache read subrequests. 'internal_thread_pool' (default) spawns a private thread pool per read, keeping behaviour identical to the original implementation. 'duckdb_task_scheduler' submits tasks to DuckDB's global TaskScheduler so that 'SET threads = N' caps total parallelism (including cache reads) in one place. | VARCHAR | GLOBAL | [] | internal_thread_pool |
| cache_httpfs_profile_type | Profiling type for cached filesystem. There are two options available: `noop` and `temp`. `temp` option stores the latest IO operation profiling result, which potentially suffers concurrent updates. | VARCHAR | GLOBAL | [] | noop |
| cache_httpfs_type | Type for cached filesystem. Currently there're two types available, one is `in_mem`, another is `on_disk`. By default we use on-disk cache. Set to `noop` to disable, which behaves exactly same as httpfs extension. | VARCHAR | GLOBAL | [] | on_disk |
| enable_external_file_cache | Allow the database to cache external files like Parquet in memory. | BOOLEAN | GLOBAL | [] | false |
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |