---
layout: docu
redirect_from:
- /docs/configuration
- /docs/configuration/
- /docs/sql/configuration
- /docs/sql/configuration/
- /docs/configuration/overview
title: 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]({% link docs/stable/sql/statements/set.md %}) or the [`PRAGMA` statement]({% link docs/stable/configuration/pragmas.md %}).
They can be reset to their original values using the [`RESET` statement]({% link docs/stable/sql/statements/set.md %}#reset).
The values of configuration options can be queried via the [`current_setting()` scalar function]({% link docs/stable/sql/functions/utility.md %}) or using the [`duckdb_settings()` table function]({% link docs/stable/sql/meta/duckdb_table_functions.md %}#duckdb_settings). For example:
```sql
SELECT current_setting('memory_limit') AS memlimit;
```
Or:
```sql
SELECT value AS memlimit
FROM duckdb_settings()
WHERE name = 'memory_limit';
```
## Examples
Set the memory limit of the system to 10 GB.
```sql
SET memory_limit = '10GB';
```
Configure the system to use 1 thread.
```sql
SET threads TO 1;
```
Enable printing of a progress bar during long-running queries.
```sql
SET enable_progress_bar = true;
```
Set the default null order to `NULLS LAST`.
```sql
SET default_null_order = 'nulls_last';
```
Return the current value of a specific setting.
```sql
SELECT current_setting('threads') AS threads;
```
| threads |
|--------:|
| 10 |
Query a specific setting.
```sql
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.
```sql
SELECT *
FROM duckdb_settings();
```
Reset the memory limit of the system back to the default.
```sql
RESET memory_limit;
```
## Secrets Manager
DuckDB has a [Secrets manager]({% link docs/stable/sql/statements/create_secret.md %}), which provides a unified user interface for secrets across all backends (e.g., AWS S3) that use them.
## Configuration Reference
<!-- This section is generated by scripts/generate_config_docs.py -->
Configuration options come with different default [scopes]({% link docs/stable/sql/statements/set.md %}#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` |
| `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` | `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` 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_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_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` |
| `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 |
|----|--------|--|---|
| `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", "CUMULATIVE_CARDINALITY": "true", "EXTRA_INFO": "true", "CPU_TIME": "true", "BLOCKE...` |
| `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` | `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` |
| `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` |