Skip to content

settings.md

Latest commit

 

History

History
188 lines (107 loc) · 7.65 KB

settings.md

File metadata and controls

188 lines (107 loc) · 7.65 KB

Settings

pg_duckdb settings are configured in the postgresql.conf file. Many of these settings are used to configure specific DuckDB settings. If there's a setting from DuckDB that you'd like to see added, please open an issue or pull request.

General

duckdb.force_execution

Forces queries to use DuckDB execution. This is only necessary when accessing only Postgres tables in a query. As soon as you use a DuckDB-only feature, DuckDB execution will be used automatically. DuckDB-only features include reading from DuckDB/MotherDuck tables, using DuckDB functions (like read_parquet), or COPY to remote storage (e.g., s3://).

  • Default: false
  • Access: General

duckdb.default_collation

Sets the default collation to use for DuckDB string operations and sorting. This allows you to configure locale-specific string comparison behavior.

  • Examples: "en_us", "de_de", "C"
  • Default: "" (empty - uses DuckDB default)
  • Access: Superuser-only

Security

duckdb.postgres_role

Specifies the Postgres role that is allowed to use DuckDB execution, manage secrets, and create MotherDuck tables. Defaults to superusers only. If this is configured, but the role does not exist when running CREATE EXTENSION pg_duckdb, it will be created automatically. This role will have access to DuckDB secrets and data in MotherDuck.

  • Default: ""
  • Access: Requires restart

duckdb.disabled_filesystems

Disables specific file systems, preventing access for all users, including superusers. For non-superusers who are not members of both the pg_read_server_files and pg_write_server_files roles, the LocalFileSystem is always disabled. If you add LocalFileSystem to this setting, superusers will also be unable to access the local file system through DuckDB.

  • Default: ""
  • Access: Superuser-only

duckdb.autoinstall_known_extensions

Determines whether known extensions are allowed to be automatically installed when a DuckDB query depends on them.

  • Default: true
  • Access: Superuser-only

duckdb.autoload_known_extensions

Determines whether known extensions are allowed to be automatically loaded when a DuckDB query depends on them.

  • Default: true
  • Access: Superuser-only

duckdb.allow_community_extensions

Determines whether community extensions can be installed.

  • Default: false
  • Access: Superuser-only

duckdb.unsafe_allow_execution_inside_function

Allows DuckDB execution inside PostgreSQL functions. This feature can cause crashes in rare cases and is disabled by default. Use with caution.

  • Default: false
  • Access: Superuser-only

duckdb.enable_external_access (Experimental)

Allows DuckDB to access external resources (e.g., HTTP, S3). This setting is not yet well-tested, and disabling it may break unintended pg_duckdb functionality.

  • Default: true
  • Access: Superuser-only

duckdb.azure_transport_option_type

Sets the azure_transport_option_type for DuckDB Azure extension. Can be used to workaround issue #882 by setting it to 'curl'. Only affects connections when the Azure extension is loaded.

  • Default: "" (empty string)
  • Access: Superuser-only

Resource Management

Since any connection that uses DuckDB will have its own DuckDB instance, these settings are per-connection. When using pg_duckdb in many concurrent connections, it can be a good idea to set some of these more conservatively than their defaults.

duckdb.max_memory / duckdb.memory_limit

The maximum memory DuckDB can use within a single Postgres connection, comparable to Postgres's work_mem setting. The value is specified in megabytes. When set to 0, this will use DuckDB's default, which is 80% of RAM.

  • Examples: 4096 (4GB), 8192 (8GB), 1024 (1GB)
  • Default: 4096 (4GB)
  • Access: Superuser-only

duckdb.threads / duckdb.worker_threads

The maximum number of DuckDB threads per Postgres connection. A value of -1 uses DuckDB's default, which is the number of CPU cores on the machine.

  • Default: -1
  • Access: Superuser-only

duckdb.max_workers_per_postgres_scan

The maximum number of PostgreSQL workers used for a single Postgres scan, similar to Postgres's max_parallel_workers_per_gather setting.

  • Default: 2
  • Access: General

duckdb.threads_for_postgres_scan

The maximum number of DuckDB threads used for a single Postgres scan. This setting controls parallelism within DuckDB when scanning PostgreSQL tables.

  • Default: 2
  • Access: General

MotherDuck

duckdb.force_motherduck_views

Forces all views to be created in MotherDuck, even if they don't use MotherDuck tables. This setting is useful when you want to ensure all views are stored in the cloud database for consistency.

  • Default: false
  • Access: General

duckdb.motherduck_session_hint

The session hint to use for MotherDuck connections. This setting allows you to provide additional connection parameters or hints for MotherDuck integration.

  • Default: "" (empty string)
  • Access: General

Advanced Usage

duckdb.convert_unsupported_numeric_to_double

Converts NUMERIC types with unsupported precision/scale to DOUBLE instead of throwing an error. DuckDB supports NUMERIC/DECIMAL with precision 1-38 and scale 0-38 (where scale ≤ precision). For NUMERICs outside these limits, this setting controls the behavior.

  • When true: Unsupported NUMERICs are converted to DOUBLE (may cause precision loss).

  • When false: Unsupported NUMERICs cause an error.

  • Default: false

  • Access: General

duckdb.unsafe_allow_mixed_transactions

Allows mixed transactions between DuckDB and PostgreSQL. This experimental setting enables transactions that modify data in both databases, but it may cause consistency issues and is not recommended for production use.

  • Default: false
  • Access: General

File System and Storage

duckdb.temporary_directory

Sets the directory where DuckDB writes temporary files. By default, DuckDB uses a directory under the PostgreSQL data directory (DataDir/pg_duckdb/temp). This can be useful for pointing to faster storage (e.g., an SSD) or managing disk space more effectively.

  • Default: "DataDir/pg_duckdb/temp"
  • Access: Superuser-only

duckdb.max_temp_directory_size / duckdb.max_temporary_directory_size

The maximum amount of data that can be stored in DuckDB's temporary directory. This setting helps prevent runaway queries from consuming all available disk space. When set to an empty string, no limit is enforced.

  • Examples: "10GB", "500MB", "2TB"
  • Default: "" (no limit)
  • Access: Superuser-only

duckdb.extension_directory

Sets the directory where DuckDB stores its extensions. By default, extensions are stored under the PostgreSQL data directory (DataDir/pg_duckdb/extensions). This is useful for managing extension storage or sharing extensions across multiple PostgreSQL instances.

  • Default: "DataDir/pg_duckdb/extensions"
  • Access: Superuser-only

Developer Settings

duckdb.allow_unsigned_extensions

Allows DuckDB to load extensions with invalid or missing signatures. This is mostly useful for the development of DuckDB extensions.

  • Default: false
  • Access: Superuser-only

duckdb.log_pg_explain

Logs the EXPLAIN plan of a PostgreSQL scan at the NOTICE log level. This is useful for debugging query execution and understanding how DuckDB interacts with PostgreSQL tables.

  • Default: false
  • Access: General