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;
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 |
|---|---|---|---|
Calendar |
The current calendar | VARCHAR |
System (locale) calendar |
TimeZone |
The current time zone | VARCHAR |
System (locale) timezone |
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 |
arrow_large_buffer_size |
If 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 |
If 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 |
true |
autoload_known_extensions |
Whether known extensions are allowed to be automatically loaded when a query depends on them | BOOLEAN |
true |
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, NULLS_LAST, NULLS_FIRST_ON_ASC_LAST_ON_DESC or NULLS_LAST_ON_ASC_FIRST_ON_DESC) |
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 |
disabled_filesystems |
Disable specific file systems preventing access (e.g., LocalFileSystem) | 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_fsst_vectors |
Allow scans on FSST compressed segments to emit compressed vectors to utilize late decompression | BOOLEAN |
false |
enable_http_metadata_cache |
Whether or not the global http metadata is used to cache HTTP metadata | BOOLEAN |
false |
enable_macro_dependencies |
Enable created MACROs to create dependencies on the referenced objects (such as tables) | BOOLEAN |
false |
enable_object_cache |
Whether or not object cache is used to cache e.g., Parquet metadata | BOOLEAN |
false |
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 |
extension_directory |
Set the directory to store extensions in | VARCHAR |
|
external_threads |
The number of external threads that work on DuckDB tasks. | BIGINT |
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 | UBIGINT |
30000 |
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 |
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). If the temp_directory is set to an existing directory, this option defaults to the available disk space on that drive. Otherwise, it defaults to 0 (implying that the temporary directory is not used). |
VARCHAR |
0 bytes |
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 |
password |
The password to use. Ignored for legacy compatibility. | VARCHAR |
NULL |
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 |
If 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_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 |
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 |
Local Configuration Options
| Name | Description | Type | Default value |
|---|---|---|---|
custom_profiling_settings |
Accepts a JSON enabling custom metrics |
VARCHAR |
{"RESULT_SET_SIZE": "true", "OPERATOR_TIMING": "true", "OPERATOR_ROWS_SCANNED": "true", "CUMULATIVE_ROWS_SCANNED": "true", "OPERATOR_CARDINALITY": "true", "OPERATOR_TYPE": "true", "CUMULATIVE_CARDINALITY": "true", "EXTRA_INFO": "true", "CPU_TIME": "true", "BLOCKED_THREAD_TIME": "true", "QUERY_NAME": "true"} |
enable_http_logging |
Enables HTTP logging | BOOLEAN |
false |
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 |
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 | BIGINT |
12 |
pivot_filter_threshold |
The threshold to switch from using filtered aggregates to LIST with a dedicated pivot operator | BIGINT |
20 |
pivot_limit |
The maximum number of pivot columns in a pivot statement | BIGINT |
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 |