Configuration
Configuration Reference
<!-- This section is generated by scripts/generate_config_docs.py -->  
Configuration options come with different default [scopes](#docs:sql:statements:set::scopes): `GLOBAL` and `LOCAL`. Below is a list of all available configuration options by scope.  
#### Global Configuration Options {#docs:configuration:overview::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 {#docs:configuration:overview::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`                                                                                                                                                                                                                                                                                                            |