DuckDB configuration parameters

home_directory

Sets the home directory used by the system

 — 

allocator_background_threads

Enables the allocator background threads

false

allocator_bulk_deallocation_flush_threshold

Flushes outstanding allocations if a bulk deallocation is larger than this value

512.0 MiB

allocator_flush_threshold

Peak allocation threshold to flush the allocator after completing a task

128.0 MiB

block_allocator_memory

Physical memory that the block allocator is allowed to use (this memory is never freed and cannot be reduced)

0 bytes

external_threads

Number of external threads that work on DuckDB tasks

1

max_memory

Maximum memory allocated to the system

12.5 GiB

max_temp_directory_size

Maximum amount of data stored inside temp_directory (if set)

90% of available disk space

memory_limit

Alias for max_memory

12.5 GiB

pin_threads

Specifies whether to pin threads to cores (defaults to auto when there are more than 64 cores)

auto

streaming_buffer_size

Maximum memory to buffer between fetching from a streaming result

976.5 KiB

temp_directory

Directory to write temporary files

.tmp

threads

Number of total threads used by the system

22

worker_threads

Alias for threads

22

write_buffer_row_group_memory_limit

Maximum data (in bytes) to buffer in row groups prior to flushing them together. When either this limit or write_buffer_row_group_count is reached, the data is flushed to disk. Defaults to 20% of memory limit divided by thread count

111.3 MiB

allow_persistent_secrets

Allows the creation of persistent secrets which are stored and loaded on restarts

true

allow_unredacted_secrets

Allows printing unredacted secrets

false

allowed_configs

List of configuration options that are always allowed to be changed — even when lock_configuration=true

 — 

allowed_directories

List of directories/prefixes that are always allowed to be queried — even when enable_external_access=false

 — 

allowed_paths

List of files that are always allowed to be queried — even when enable_external_access=false

 — 

default_secret_storage

Allows switching the default storage for secrets

local_file

disabled_filesystems

Disables specific file systems by preventing access to them (e.g. LocalFileSystem)

 — 

http_proxy_password

Password for HTTP proxy

 — 

http_proxy_username

Username for HTTP proxy

 — 

lock_configuration

Defines whether configurations can be altered

false

password

Password to use. Ignored for legacy compatibility

 — 

secret_directory

Directory where persistent secrets are stored

/var/lib/duckdb/stored_secrets

temp_file_encryption

Encrypts all temporary files if the corresponding database is encrypted

false

user

Username to use. Ignored for legacy compatibility

 — 

username

Username to use. Ignored for legacy compatibility

 — 

enable_http_logging

Deprecated. Enables HTTP logging

true

enable_http_metadata_cache

Defines whether the global HTTP metadata is used to cache HTTP metadata

false

http_proxy

HTTP proxy host (if not specified, defaults to the HTTP_PROXY environment variable)

 — 

allow_extensions_metadata_mismatch

Allows loading extensions with incompatible metadata

false

allow_parser_override_extension

Allows extensions to override the current parser

DEFAULT

allow_unsigned_extensions

Allows loading extensions with invalid or missing signatures

true

autoinstall_extension_repository

Overrides the custom endpoint for extension installation on autoloading

 — 

autoinstall_known_extensions

Allows automatic installation of extensions when a query depends on them

true

autoload_known_extensions

Allows automatic loading of extensions when a query depends on them

true

custom_extension_repository

Overrides the custom endpoint for remote extension installation

 — 

extension_directories

Directories to store extensions

 — 

extension_directory

Directory to store extensions

/usr/lib/duckdb/extensions

arrow_large_buffer_size

Specifies whether Arrow buffers for the STRING, BLOB, UUID, and BIT types should be exported using large buffers

false

arrow_lossless_conversion

If a DuckDB type does not have a clear native or canonical extension match in Arrow, export such types with a duckdb.type_name extension name

false

arrow_output_list_view

Defines whether an export to Arrow format should use ListView as the physical layout for LIST columns

false

arrow_output_version

Defines whether the STRING type should be produced by DuckDB in the Utf8View format instead of Utf8

1.0

binary_as_string

In Parquet files, interpret binary data as a string

false

disable_parquet_prefetching

Disables the prefetching mechanism in Parquet

false

disabled_compression_methods

Disables a specific set of compression methods (comma-separated)

 — 

enable_external_file_cache

Allows the database to cache external files (e.g., Parquet) in memory

true

enable_fsst_vectors

Allows scans on FSST-compressed segments to emit compressed vectors to utilize late decompression

false

enable_geoparquet_conversion

Attempts to decode/encode geometry data as GeoParquet files if a spatial extension is present

true

file_search_path

A comma-separated list of directories to search for input files

 — 

ignore_unknown_crs

Ignores unknown Coordinate Reference Systems (CRS) when creating geometry types or importing geospatial data

false

parquet_metadata_cache

Allows Parquet metadata caching, which is useful when reading the same files multiple times

false

prefetch_all_parquet_files

Uses the prefetching mechanism for all types of Parquet files

false

produce_arrow_string_view

Defines whether the Arrow STRING type should be produced by DuckDB in the Utf8View format instead of Utf8

false

validate_external_file_cache

VALIDATE_ALL

zstd_min_string_length

Average length to enable ZSTD compression

4096

auto_checkpoint_skip_wal_threshold

Once the specified WAL write size is reached, DuckDB will skip writing to the WAL and only checkpoint. Skipping writing to the WAL means concurrent commits are blocked during the checkpoints

100000

checkpoint_threshold

WAL size threshold to automatically trigger a checkpoint

16.0 MiB

default_block_size

Default block size for new DuckDB database files

262144

max_vacuum_tasks

Maximum vacuum tasks to schedule during a checkpoint

100

partitioned_write_flush_threshold

Maximum number of rows after which DuckDB will flush a thread state when writing using PARTITION_BY

524288

partitioned_write_max_open_files

Maximum number of files the system can keep open before flushing to disk when writing using PARTITION_BY

100

storage_block_prefetch

REMOTE_ONLY

wal_autocheckpoint

WAL size threshold to automatically trigger a checkpoint

16.0 MiB

wal_autocheckpoint_entries

Number of WAL entries that trigger an automatic checkpoint (0 disables the mechanism)

0

write_buffer_row_group_count

Number of row groups to buffer in bulk ingestion prior to flushing them together. Lowering this value can reduce memory consumption

5

Calendar

Current calendar

gregorian

current_transaction_invalidation_policy

STANDARD_POLICY

default_collation

Default collation to use

 — 

default_null_order

NULL ordering (NULLS_FIRST or NULLS_LAST)

NULLS_LAST

default_order

Default ordering (ASCENDING or DESCENDING)

ASCENDING

deprecated_using_key_syntax

Defines the use of the deprecated union syntax for USING KEY CTEs

DEFAULT

disable_timestamptz_casts

Disable casting from timestamp to timestamptz (with time zone)

false

enable_macro_dependencies

Enables MACROs to create dependencies on the referenced objects (such as tables)

false

enable_view_dependencies

Enables VIEWs to create dependencies on the referenced objects (such as tables)

false

ieee_floating_point_ops

Enforces the use of IEE754-compliant floating point operations (returning NaN rather than errors or NULL)

true

immediate_transaction_mode

Defines whether transactions should be started lazily when needed or immediately when BEGIN TRANSACTION is called

false

integer_division

Defines whether the / operator defaults to integer division, or to the floating point division

false

lambda_syntax

Configures the use of the deprecated single arrow operator (->) for lambda functions

DEFAULT

max_expression_depth

Maximum expression depth limit in the parser. Increasing this parameter and using very deep expressions might lead to stack overflow errors

1000

null_order

Specifies NULL ordering (NULLS_FIRST or NULLS_LAST)

NULLS_LAST

old_implicit_casting

Allows implicit casting to/from VARCHAR

false

order_by_non_integer_literal

Enables ordering by non-integer literals

false

preserve_identifier_case

Defines whether to preserve the identifier case, instead of always lowercasing all non-quoted identifiers

true

scalar_subquery_error_on_multiple_rows

When a scalar subquery returns multiple rows, DuckDB returns a random row instead of returning an error

true

schema

Sets the default search schema. Equivalent to setting search_path to a single value

main

search_path

Default catalog search path as a comma-separated list of values

 — 

TimeZone

Current time zone

Europe/Moscow

asof_loop_join_threshold

Maximum number of rows required on the left side of an ASOF join to use a nested loop join

64

dynamic_or_filter_threshold

Maximum amount of OR filters generated dynamically from a hash join

50

enable_caching_operators

Enables caching operators that cache intermediate results

true

enable_object_cache

Legacy setting. Does not affect the behavior

false

index_scan_max_count

Maximum index scan count sets a threshold for index scans. If the number of matched rows is less than max(index_scan_max_count, index_scan_percentage * total_row_count), an index scan is performed instead of a table scan

2048

index_scan_percentage

If the number of matched rows is less than max(index_scan_max_count, index_scan_percentage * total_row_count), an index scan is performed instead of a table scan

0.001

late_materialization_max_rows

Maximum number of rows in LIMIT/SAMPLE to trigger late materialization

50

merge_join_threshold

Maximum number of rows on either table to choose a merge join

1000

nested_loop_join_threshold

Maximum number of rows on either table to choose a nested loop join

5

ordered_aggregate_threshold

Number of rows to accumulate before sorting

262144

perfect_ht_threshold

Threshold in bytes for when to use a perfect hash table

12

pivot_filter_threshold

Threshold to switch from using filtered aggregates to LIST with a dedicated pivot operator

20

pivot_limit

Maximum number of pivot columns in a pivot statement

100000

prefer_range_joins

Forces the use of range joins with mixed predicates

false

preserve_insertion_order

Defines whether to preserve the insertion order. If set to false, the system is allowed to reorder any results that do not contain the ORDER BY clause

true

scheduler_process_partial

Enables partial tasks processing before rescheduling. This allows for more scheduler fairness between separate queries

false

catalog_error_max_schemas

Maximum number of schemas the system will scan for "did you mean…​" errors in the catalog

100

custom_profiling_settings

Accepts a JSON enabling custom metrics

disabled_log_types

A list of disabled loggers

 — 

enable_logging

Enables the logger

true

enable_profiling

Enables profiling and sets the output format (JSON, QUERY_TREE, QUERY_TREE_OPTIMIZER, NO_OUTPUT, HTML, or GRAPHVIZ)

 — 

enable_progress_bar

Enables the progress bar, printing progress to the terminal for long queries

true

enable_progress_bar_print

Controls the printing of the progress bar (if enable_progress_bar=true)

true

enabled_log_types

A list of enabled loggers

 — 

errors_as_json

Outputs error messages as structured JSON instead of raw strings

false

explain_output

Outputs EXPLAIN statements (ALL, OPTIMIZED_ONLY, PHYSICAL_ONLY)

PHYSICAL_ONLY

log_query_path

Specifies the path to which queries should be logged. By default, queries are not logged

 — 

logging_level

Specifies the minimum level of events to record in the log

WARNING

logging_mode

Defines which types of log messages are logged

LEVEL_ONLY

logging_storage

Set the logging storage

shell_log_storage

profile_output

File to which profile output should be saved. If not set, the profile output is printed to the terminal

 — 

profiling_coverage

Profiling coverage (SELECT or ALL)

SELECT

profiling_mode

Profiling mode (STANDARD or DETAILED)

 — 

profiling_output

The file to which profile output should be saved, or empty to print to the terminal

 — 

progress_bar_time

Minimum time (in milliseconds) a query needs before DuckDB starts printing a progress bar

2000

warnings_as_errors

Escalate all warnings to errors

false

debug_asof_iejoin

Forces the use of IEJoin to implement ASOF joins

false

debug_checkpoint_abort

Triggers an abort while checkpointing for testing purposes

NONE

debug_checkpoint_sleep_ms

Time to sleep before a checkpoint

0

debug_eviction_queue_sleep_micro_seconds

Time for an eviction queue to sleep before acquiring shared ownership of block memory

0

debug_force_external

Forces out-of-core computation for operators that support it, used for testing

false

debug_force_no_cross_product

Disables cross-product generation when hyper graph isn’t connected, used for testing

false

debug_physical_table_scan_execution_strategy

Forces the use of a given strategy for executing physical table scans

DEFAULT

debug_skip_checkpoint_on_commit

Skips checkpointing on commits

false

debug_verify_blocks

Verifies block metadata during checkpointing

false

debug_verify_vector

Enables vector verification

NONE

debug_window_mode

Switch window mode to use

WINDOW

disabled_optimizers

Disables a specific set of optimizers (comma-separated)

 — 

experimental_metadata_reuse

Re-uses row group and table metadata during checkpointing

true

force_bitpacking_mode

Forces a specific bitpacking mode

AUTO

force_compression

Forces a specific compression method to be used

auto

force_mbedtls_unsafe

Enables mbedtls for encryption (unsafe to use)

false

geometry_minimum_shredding_size

Minimum size of a row group to enable GEOMETRY shredding, or set to -1 to disable entirely. Defaults to 25% of a row group

30000

variant_minimum_shredding_size

Minimum size of a row group to enable VARIANT shredding, or set to -1 to disable entirely. Defaults to 25% of a row group

30000

access_mode

Sets the access mode for a database (AUTOMATIC, READ_ONLY, or READ_WRITE)

AUTOMATIC

enable_external_access

Allows the database to access external state (for example, through loading/installing modules, COPY TO/FROM, CSV readers, pandas replacement scans, etc.)

true

storage_compatibility_version

Performs serialization on a checkpoint with compatibility for a given DuckDB version

v0.10.2

disable_database_invalidation

Disables invalidating a database instance when encountering a fatal error. Should be used with care, as DuckDB cannot guarantee correct behavior after a fatal error

false

duckdb_api

DuckDB API surface

cli

custom_user_agent

Metadata from DuckDB callers

 — 

allow_community_extensions

Allows loading community-built extensions

true

vacuum_rebuild_indexes

Allows VACUUM to compact row groups on tables with bound ART indexes, rebuilding the indexes afterward. Tables with a row count exceeding this threshold are skipped. Use 0 to disable the feature

0

Vault/OpenBao secret storage

Allows storing DuckDB persistent secrets in Vault/OpenBao storage

 — 

HDFS/Ozone/Hive Metastore

Enables the ADO HDFS extension and its bundled Hadoop runtime

 — 

Additional extensions

Additional extensions to install and load during DuckDB initialization

 — 

DUCKDB_HOME

DuckDB home directory

/usr/lib/duckdb

GLOBAL_CONFIG

DuckDB global configuration file

/etc/duckdb/conf/duckdb-conf.sql

DUCKDB_EXTENSION_DIRECTORY

DuckDB extension directory

/usr/lib/duckdb/extensions

DUCKDB_HDFS_RUNTIME

DuckDB HDFS runtime

/usr/lib/duckdb/extensions/v1.5.3/linux_amd64/hdfs-runtime

JAVA_HOME

JAVA_HOME directory used by DuckDB

/usr/lib/duckdb/extensions/v1.5.3/linux_amd64/hdfs-runtime/jre

HADOOP_CONF_DIR

Hadoop configuration directory

/usr/lib/duckdb/extensions/v1.5.3/linux_amd64/hdfs-runtime/conf

CLASSPATH

DuckDB classpath

/usr/lib/duckdb/extensions/v1.5.3/linux_amd64/hdfs-runtime/conf:/usr/lib/duckdb/extensions/v1.5.3/linux_amd64/hdfs-runtime/jars/*

LD_LIBRARY_PATH

Loader library path

/usr/lib/duckdb/extensions/v1.5.3/linux_amd64/hdfs-runtime/lib:/usr/lib/duckdb/extensions/v1.5.3/linux_amd64/hdfs-runtime/jre/lib:/usr/lib/duckdb/extensions/v1.5.3/linux_amd64/hdfs-runtime/jre/lib/server

Additional key-value settings

Additional DuckDB settings in the key=value format, which ADO converts into configuration settings

 — 

Custom SQL configuration

Additional DuckDB SQL expressions that must be executed when initializing the DuckDB CLI

 —