⌘+k ctrl+k
1.3 (stable)
Search Shortcut cmd + k | ctrl + k
Configuration

DuckDB has a number of configuration options that can be used to change the behavior of the system.

The configuration options can be set using either the SET statement or the PRAGMA statement. They can be reset to their original values using the RESET statement.

The values of configuration options can be queried via the current_setting() scalar function or using the duckdb_settings() table function. For example:

SELECT current_setting('memory_limit') AS memlimit;

Or:

SELECT value AS memlimit
FROM duckdb_settings()
WHERE name = 'memory_limit';

Examples

Set the memory limit of the system to 10 GB.

SET memory_limit = '10GB';

Configure the system to use 1 thread.

SET threads TO 1;

Enable printing of a progress bar during long-running queries.

SET enable_progress_bar = true;

Set the default null order to NULLS LAST.

SET default_null_order = 'nulls_last';

Return the current value of a specific setting.

SELECT current_setting('threads') AS threads;
threads
10

Query a specific setting.

SELECT *
FROM duckdb_settings()
WHERE name = 'threads';
name value description input_type scope
threads 1 The number of total threads used by the system. BIGINT GLOBAL

Show a list of all available settings.

SELECT *
FROM duckdb_settings();

Reset the memory limit of the system back to the default.

RESET memory_limit;

Secrets Manager

DuckDB has a Secrets manager, which provides a unified user interface for secrets across all backends (e.g., AWS S3) that use them.

Configuration Reference

Configuration options come with different default scopes: GLOBAL and LOCAL. Below is a list of all available configuration options by scope.

Global Configuration Options

Name Description Type Default value
access_mode Access mode of the database (AUTOMATIC, READ_ONLY or READ_WRITE) VARCHAR automatic
allocator_background_threads Whether to enable the allocator background thread. BOOLEAN false
allocator_bulk_deallocation_flush_threshold If a bulk deallocation larger than this occurs, flush outstanding allocations. VARCHAR 512.0 MiB
allocator_flush_threshold Peak allocation threshold at which to flush the allocator after completing a task. VARCHAR 128.0 MiB
allow_community_extensions Allow to load community built extensions BOOLEAN true
allow_extensions_metadata_mismatch Allow to load extensions with not compatible metadata BOOLEAN false
allow_persistent_secrets Allow the creation of persistent secrets, that are stored and loaded on restarts BOOLEAN true
allow_unredacted_secrets Allow printing unredacted secrets BOOLEAN false
allow_unsigned_extensions Allow to load extensions with invalid or missing signatures BOOLEAN false
allowed_directories List of directories/prefixes that are ALWAYS allowed to be queried - even when enable_external_access is false VARCHAR[] []
allowed_paths List of files that are ALWAYS allowed to be queried - even when enable_external_access is false VARCHAR[] []
arrow_large_buffer_size Whether Arrow buffers for strings, blobs, uuids and bits should be exported using large buffers BOOLEAN false
arrow_lossless_conversion Whenever a DuckDB type does not have a clear native or canonical extension match in Arrow, export the types with a duckdb.type_name extension name. BOOLEAN false
arrow_output_list_view Whether export to Arrow format should use ListView as the physical layout for LIST columns BOOLEAN false
autoinstall_extension_repository Overrides the custom endpoint for extension installation on autoloading VARCHAR  
autoinstall_known_extensions Whether known extensions are allowed to be automatically installed when a query depends on them BOOLEAN false
autoload_known_extensions Whether known extensions are allowed to be automatically loaded when a query depends on them BOOLEAN false
binary_as_string In Parquet files, interpret binary data as a string. BOOLEAN  
ca_cert_file Path to a custom certificate file for self-signed certificates. VARCHAR  
catalog_error_max_schemas The maximum number of schemas the system will scan for "did you mean…" style errors in the catalog UBIGINT 100
checkpoint_threshold, wal_autocheckpoint The WAL size threshold at which to automatically trigger a checkpoint (e.g., 1GB) VARCHAR 16.0 MiB
custom_extension_repository Overrides the custom endpoint for remote extension installation VARCHAR  
custom_user_agent Metadata from DuckDB callers VARCHAR  
default_block_size The default block size for new duckdb database files (new as-in, they do not yet exist). UBIGINT 262144
default_collation The collation setting used when none is specified VARCHAR  
default_null_order, null_order NULL ordering used when none is specified (NULLS_FIRST or NULLS_LAST) VARCHAR NULLS_LAST
default_order The order type used when none is specified (ASC or DESC) VARCHAR ASC
default_secret_storage Allows switching the default storage for secrets VARCHAR local_file
disable_parquet_prefetching Disable the prefetching mechanism in Parquet BOOLEAN false
disabled_compression_methods Disable a specific set of compression methods (comma separated) VARCHAR  
disabled_filesystems Disable specific file systems preventing access (e.g., LocalFileSystem) VARCHAR  
disabled_log_types Sets the list of disabled loggers VARCHAR  
duckdb_api DuckDB API surface VARCHAR cli
enable_external_access Allow the database to access external state (through e.g., loading/installing modules, COPY TO/FROM, CSV readers, pandas replacement scans, etc) BOOLEAN true
enable_external_file_cache Allow the database to cache external files (e.g., Parquet) in memory. BOOLEAN true
enable_fsst_vectors Allow scans on FSST compressed segments to emit compressed vectors to utilize late decompression BOOLEAN false
enable_geoparquet_conversion Attempt to decode/encode geometry data in/as GeoParquet files if the spatial extension is present. BOOLEAN true
enable_http_metadata_cache Whether or not the global http metadata is used to cache HTTP metadata BOOLEAN false
enable_logging Enables the logger BOOLEAN 0
enable_macro_dependencies Enable created MACROs to create dependencies on the referenced objects (such as tables) BOOLEAN false
enable_object_cache [PLACEHOLDER] Legacy setting - does nothing BOOLEAN NULL
enable_server_cert_verification Enable server side certificate verification. BOOLEAN false
enable_view_dependencies Enable created VIEWs to create dependencies on the referenced objects (such as tables) BOOLEAN false
enabled_log_types Sets the list of enabled loggers VARCHAR  
extension_directory Set the directory to store extensions in VARCHAR  
external_threads The number of external threads that work on DuckDB tasks. UBIGINT 1
force_download Forces upfront download of file BOOLEAN false
http_keep_alive Keep alive connections. Setting this to false can help when running into connection failures BOOLEAN true
http_proxy_password Password for HTTP proxy VARCHAR  
http_proxy_username Username for HTTP proxy VARCHAR  
http_proxy HTTP proxy host VARCHAR  
http_retries HTTP retries on I/O error UBIGINT 3
http_retry_backoff Backoff factor for exponentially increasing retry wait time FLOAT 4
http_retry_wait_ms Time between retries UBIGINT 100
http_timeout HTTP timeout read/write/connection/retry (in seconds) UBIGINT 30
immediate_transaction_mode Whether transactions should be started lazily when needed, or immediately when BEGIN TRANSACTION is called BOOLEAN false
index_scan_max_count The maximum index scan count sets a threshold for index scans. If fewer than MAX(index_scan_max_count, index_scan_percentage * total_row_count) rows match, we perform an index scan instead of a table scan. UBIGINT 2048
index_scan_percentage The index scan percentage sets a threshold for index scans. If fewer than MAX(index_scan_max_count, index_scan_percentage * total_row_count) rows match, we perform an index scan instead of a table scan. DOUBLE 0.001
lock_configuration Whether or not the configuration can be altered BOOLEAN false
logging_level The log level which will be recorded in the log VARCHAR INFO
logging_mode Enables the logger VARCHAR LEVEL_ONLY
logging_storage Set the logging storage (memory/stdout/file) VARCHAR memory
max_memory, memory_limit The maximum memory of the system (e.g., 1GB) VARCHAR 80% of RAM
max_temp_directory_size The maximum amount of data stored inside the 'temp_directory' (when set) (e.g., 1GB) VARCHAR 90% of available disk space
max_vacuum_tasks The maximum vacuum tasks to schedule during a checkpoint. UBIGINT 100
old_implicit_casting Allow implicit casting to/from VARCHAR BOOLEAN false
parquet_metadata_cache Cache Parquet metadata - useful when reading the same files multiple times BOOLEAN false
password The password to use. Ignored for legacy compatibility. VARCHAR NULL
prefetch_all_parquet_files Use the prefetching mechanism for all types of parquet files BOOLEAN false
preserve_insertion_order Whether or not to preserve insertion order. If set to false the system is allowed to re-order any results that do not contain ORDER BY clauses. BOOLEAN true
produce_arrow_string_view Whether strings should be produced by DuckDB in Utf8View format instead of Utf8 BOOLEAN false
s3_access_key_id S3 Access Key ID VARCHAR  
s3_endpoint S3 Endpoint VARCHAR  
s3_kms_key_id S3 KMS Key ID VARCHAR  
s3_region S3 Region VARCHAR us-east-1
s3_secret_access_key S3 Access Key VARCHAR  
s3_session_token S3 Session Token VARCHAR  
s3_uploader_max_filesize S3 Uploader max filesize (between 50GB and 5TB) VARCHAR 800GB
s3_uploader_max_parts_per_file S3 Uploader max parts per file (between 1 and 10000) UBIGINT 10000
s3_uploader_thread_limit S3 Uploader global thread limit UBIGINT 50
s3_url_compatibility_mode Disable Globs and Query Parameters on S3 URLs BOOLEAN false
s3_url_style S3 URL style VARCHAR vhost
s3_use_ssl S3 use SSL BOOLEAN true
scheduler_process_partial Partially process tasks before rescheduling - allows for more scheduler fairness between separate queries BOOLEAN false
secret_directory Set the directory to which persistent secrets are stored VARCHAR ~/.duckdb/stored_secrets
storage_compatibility_version Serialize on checkpoint with compatibility for a given duckdb version VARCHAR v0.10.2
temp_directory Set the directory to which to write temp files VARCHAR ⟨database_name⟩.tmp or .tmp (in in-memory mode)
threads, worker_threads The number of total threads used by the system. BIGINT # CPU cores
username, user The username to use. Ignored for legacy compatibility. VARCHAR NULL
zstd_min_string_length The (average) length at which to enable ZSTD compression, defaults to 4096 UBIGINT 4096

Local Configuration Options

Name Description Type Default value
asof_loop_join_threshold The maximum number of rows we need on the left side of an ASOF join to use a nested loop join UBIGINT 64
custom_profiling_settings Accepts a JSON enabling custom metrics VARCHAR {"ROWS_RETURNED": "true", "LATENCY": "true", "RESULT_SET_SIZE": "true", "OPERATOR_TIMING": "true", "OPERATOR_ROWS_SCANNED": "true", "CUMULATIVE_ROWS_SCANNED": "true", "OPERATOR_CARDINALITY": "true", "OPERATOR_TYPE": "true", "OPERATOR_NAME": "true", "CPU_TIME": "true", "EXTRA_INFO": "true", "SYSTEM_PEAK_BUFFER_MEMORY": "true", "BLOCKED_THREAD_TIME": "true", "CUMULATIVE_CARDINALITY": "true", "SYSTEM_PEAK_TEMP_DIR_SIZE": "true", "QUERY_NAME": "true"}
disable_timestamptz_casts Disable casting from timestamp to timestamptz BOOLEAN false
dynamic_or_filter_threshold The maximum amount of OR filters we generate dynamically from a hash join UBIGINT 50
enable_http_logging Enables HTTP logging BOOLEAN true
enable_profiling Enables profiling, and sets the output format (JSON, QUERY_TREE, QUERY_TREE_OPTIMIZER) VARCHAR NULL
enable_progress_bar_print Controls the printing of the progress bar, when 'enable_progress_bar' is true BOOLEAN true
enable_progress_bar Enables the progress bar, printing progress to the terminal for long queries BOOLEAN true
errors_as_json Output error messages as structured JSON instead of as a raw string BOOLEAN false
explain_output Output of EXPLAIN statements (ALL, OPTIMIZED_ONLY, PHYSICAL_ONLY) VARCHAR physical_only
file_search_path A comma separated list of directories to search for input files VARCHAR  
home_directory Sets the home directory used by the system VARCHAR  
http_logging_output The file to which HTTP logging output should be saved, or empty to print to the terminal VARCHAR  
ieee_floating_point_ops Use IEE754-compliant floating point operations (returning NAN instead of errors/NULL). BOOLEAN true
integer_division Whether or not the / operator defaults to integer division, or to floating point division BOOLEAN false
lambda_syntax Configures the use of the deprecated single arrow operator (->) for lambda functions. VARCHAR DEFAULT
late_materialization_max_rows The maximum amount of rows in the LIMIT/SAMPLE for which we trigger late materialization UBIGINT 50
log_query_path Specifies the path to which queries should be logged (default: NULL, queries are not logged) VARCHAR NULL
max_expression_depth The maximum expression depth limit in the parser. WARNING: increasing this setting and using very deep expressions might lead to stack overflow errors. UBIGINT 1000
merge_join_threshold The number of rows we need on either table to choose a merge join UBIGINT 1000
nested_loop_join_threshold The number of rows we need on either table to choose a nested loop join UBIGINT 5
order_by_non_integer_literal Allow ordering by non-integer literals - ordering by such literals has no effect. BOOLEAN false
ordered_aggregate_threshold The number of rows to accumulate before sorting, used for tuning UBIGINT 262144
partitioned_write_flush_threshold The threshold in number of rows after which we flush a thread state when writing using PARTITION_BY UBIGINT 524288
partitioned_write_max_open_files The maximum amount of files the system can keep open before flushing to disk when writing using PARTITION_BY UBIGINT 100
perfect_ht_threshold Threshold in bytes for when to use a perfect hash table UBIGINT 12
pivot_filter_threshold The threshold to switch from using filtered aggregates to LIST with a dedicated pivot operator UBIGINT 20
pivot_limit The maximum number of pivot columns in a pivot statement UBIGINT 100000
prefer_range_joins Force use of range joins with mixed predicates BOOLEAN false
preserve_identifier_case Whether or not to preserve the identifier case, instead of always lowercasing all non-quoted identifiers BOOLEAN true
profile_output, profiling_output The file to which profile output should be saved, or empty to print to the terminal VARCHAR  
profiling_mode The profiling mode (STANDARD or DETAILED) VARCHAR NULL
progress_bar_time Sets the time (in milliseconds) how long a query needs to take before we start printing a progress bar BIGINT 2000
scalar_subquery_error_on_multiple_rows When a scalar subquery returns multiple rows - return a random row instead of returning an error. BOOLEAN true
schema Sets the default search schema. Equivalent to setting search_path to a single value. VARCHAR main
search_path Sets the default catalog search path as a comma-separated list of values VARCHAR  
streaming_buffer_size The maximum memory to buffer between fetching from a streaming result (e.g., 1GB) VARCHAR 976.5 KiB

Pages in This Section

About this page