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

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

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

SELECT current_setting('memory_limit') AS memlimit;

Or:

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

Examples

Set the memory limit of the system to 10 GB.

SET memory_limit = '10GB';

Configure the system to use 1 thread.

SET threads TO 1;

Turn logging on and set the logging level to debug. For additional details on logging levels, see Log Level.

In the CLI client, the default log storage (shell_log_storage) prints logs to the console but cannot be queried, so we switch to the memory log storage first:

SET logging_storage = 'memory';
SET enable_logging = true;
SET logging_level = 'debug';

Write a single log message with the debug level and a connection scope:

SELECT write_log('A new client has connected.', level := 'debug', scope := 'connection');

Write a single log message with a debug level and a connection scope and a custom log_type:

SELECT write_log(
        'A new duck has connected to the lake.', 
        level := 'debug', 
        scope := 'connection', 
        log_type := 'duckdb.docs.example.quack'
    );

Check logs with the DEBUG log level:

SELECT * FROM duckdb_logs WHERE log_level = 'DEBUG';

Check logs with the QueryLog type:

SELECT * FROM duckdb_logs WHERE type = 'QueryLog';

Check current logging settings:

SELECT * FROM duckdb_settings() WHERE name LIKE '%logging%';

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;

Query a specific setting:

SELECT *
FROM duckdb_settings()
WHERE name = 'threads';

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.

Schema and Search Path

The schema and search_path configuration options both control the search path: the ordered list of schemas that DuckDB consults to resolve unqualified table, view, and function names. Despite the difference in their descriptions, both options operate on this same search path:

• search_path sets the entire search path as a comma-separated list of entries.
• schema is a convenience option that sets the search path to a single entry.

Each entry in the search path is a schema. An entry may be qualified with a catalog (attached database) name in the form catalog.schema to select a schema in a specific database; an unqualified entry refers to a schema in the current catalog. This is why the descriptions mention both schemas and catalogs: the path is a list of schemas, whose entries may optionally be catalog-qualified.

Asynchronous I/O Settings

New Asynchronous I/O, along with the settings that tune it, was introduced in DuckDB 2.0.

The async_threads and read_ahead_depth settings control the size of the asynchronous thread pool and the read-ahead budget used when reading from object storage and HTTP servers. They are listed in the reference below and documented in more detail on the I/O page, together with the related http_client_pool_capacity setting.

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

Local Configuration Options

Deprecated Configuration Options

The following configuration options are deprecated and will be removed in a future release.