Request a Demo
4.3. Configuration Parameters
Prev UpChapter 4. pgpro_axeHome Next

4.3. Configuration Parameters #

4.3.1. Security
4.3.2. Resource Management
4.3.3. Advanced
4.3.4. Storage
4.3.5. For Developers

You can configure pgpro_axe parameters in the postgresql.conf file.

duckdb.force_execution #

Forces queries to use DuckDB execution. This is necessary when accessing only Postgres Pro tables in a query. As soon as you use a DuckDB-only feature, DuckDB execution is used automatically.

DuckDB-only features include reading from DuckDB tables, using DuckDB functions (e.g., read_parquet) or COPY to a remote storage (e.g., s3://).

Default value: false.

Access: All users.

duckdb.default_collation #

Specifies the default collation to use for DuckDB string operations and sorting. This parameter allows configuring locale-specific string comparison behavior.

Example 4.41. 

  • "en_us"

  • "de_de"

  • "C"


Default value: "" — uses DuckDB default.

Access: Superuser-only.

4.3.1. Security #

duckdb.postgres_role #

Specifies the Postgres Pro role that is allowed to use DuckDB execution and manage secrets. If the specified role does not exist when running CREATE EXTENSION pgpro_axe, this role is created automatically.

Default value: "" — superusers only.

Access: Requires a restart.

duckdb.disabled_filesystems #

Disables storages for pgpro_axe.

Possible values:

  • LocalFileSystem: Disable local storages.

  • S3FileSystem: Disable S3 storages.

  • HTTPFileSystem: Disable network storages.

By default, the local storage is disabled:

Example 4.42. 

  postgres=> show duckdb.disabled_filesystems;
  duckdb.disabled_filesystems
  -----------------------------
  LocalFileSystem
  (1 row)

To specify multiple storages, separate their names with commas without spaces:

Example 4.43. 

  postgres=# ALTER SYSTEM SET duckdb.disabled_filesystems TO 'LocalFileSystem,HTTPFileSystem';
  ALTER SYSTEM
  -- after server reboot --
  postgres=# show duckdb.disabled_filesystems;
    duckdb.disabled_filesystems
  --------------------------------
  LocalFileSystem,HTTPFileSystem
  (1 row)

Access: Superuser-only.

duckdb.autoinstall_known_extensions #

Specifies whether known extensions can be automatically installed when a DuckDB query depends on them.

Possible values:

  • true

  • false

By default, only superusers can execute duckdb.install_extension and duckdb.autoload_extension. This prevents other users from installing extensions that can have security implications or interfere with database operation.

Users can only use extensions that DuckDB marked as auto-installable. To restrict the use of these extensions to a specific list of allowed extensions, set duckdb.autoinstall_known_extensions to false.

Default value: true.

Access: Superuser-only.

duckdb.autoload_known_extensions #

Specifies whether known extensions can be automatically loaded when a DuckDB query depends on them.

Default value: true.

Access: Superuser-only

duckdb.allow_community_extensions #

Specifies whether community extensions can be installed.

Default value: false.

Access: Superuser-only.

duckdb.unsafe_allow_execution_inside_functions #

Allows DuckDB execution inside Postgres Pro functions.

Important

This feature can cause Postgres Pro AXE to crash and is disabled by default.

Default value: false.

Access: Superuser-only.

duckdb.enable_external_access #

Allows DuckDB to access external resources (e.g., HTTP, S3).

Important

This feature can cause Postgres Pro AXE to crash and is disabled by default.

Default value: true.

Access: Superuser-only.

4.3.2. Resource Management #

Since any connection that uses DuckDB has its own DuckDB instance, these parameters are per-connection. When using pgpro_axe in many concurrent connections, it is recommended to specify lower values for certain parameters as compared to their defaults.

duckdb.max_memory / duckdb.memory_limit #

The maximum memory that Postgres Pro can use to process a single query, in megabytes. You can configure this parameter for each session. When set to zero, 80% of RAM is used.

Example 4.44. 

  • 4096 (4GB)

  • 8192 (8GB)

  • 1024 (1GB)


Default value: 4096 (4GB).

Access: Superuser-only.

duckdb.threads / duckdb.worker_threads #

The maximum number of threads (CPU cores) that Postgres Pro can use to process a single query. You can configure this parameter for each session.

Default value: -1 — the value is equal to the number of CPU cores.

Access: Superuser-only.

duckdb.max_workers_per_postgres_scan #

The maximum number of Postgres Pro workers used for a single Postgres Pro scan. This parameter is similar to max_parallel_workers_per_gather.

Default value: 2.

Access: All users.

duckdb.threads_for_postgres_scan #

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

Default value: 2.

Access: All users.

4.3.3. Advanced #

duckdb.convert_unsupported_numeric_to_double #

Converts NUMERIC types with unsupported precision/scale to DOUBLE instead of throwing an error.

Possible values:

  • true

  • false

DuckDB supports NUMERIC/DECIMAL with precision 1-38 and scale 0-38 (where scale ≤ precision). For NUMERICs outside these limits:

  • If true is specified, unsupported NUMERICs are converted to DOUBLE with possible precision loss.

  • If false is specified, unsupported NUMERICs cause an error.

Default value: false.

Access: All users.

duckdb.unsafe_allow_mixed_transactions #

Allows mixed transactions between DuckDB and Postgres Pro.

Important

This parameter enables transactions that modify data in both databases, but it may cause consistency issues and is not recommended for production use.

Default value: false.

Access: All users.

4.3.4. Storage #

duckdb.temporary_directory #

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

Default value: "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 the DuckDB temporary directory. This parameter allows preventing runaway queries from consuming all available disk space.

Example 4.45. 

  • "10GB"

  • "500MB"

  • "2TB"


Default value: "" — no limit is enforced.

Access: Superuser-only.

duckdb.extension_directory #

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

Default value: "DataDir/pg_duckdb/extensions".

Access: Superuser-only.

4.3.5. For Developers #

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 value: false.

Access: Superuser-only.

duckdb.log_pg_explain #

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

Default value: false.

Access: All users.

Prev Up Next
4.2. Supported Extensions Home 4.4. Transactions
epub pdf