Configuration

Goose 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 goose_settings() table function. For example:

SELECT current_setting('memory_limit') AS memlimit;

Or:

SELECT value AS memlimit
FROM goose_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.

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 := 'goose.docs.example.quack'
    );

Check logs with the DEBUG log level:

SELECT * FROM goose_logs WHERE log_level = 'DEBUG';

Check logs with the QueryLog type:

SELECT * FROM goose_logs WHERE type = 'QueryLog';

Check current logging settings:

SELECT * FROM goose_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;

threads
10

Query a specific setting:

SELECT *
FROM goose_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:

SELECT *
FROM goose_settings();

Reset the memory limit of the system back to the default:

RESET memory_limit;

Secrets Manager

Goose has a Secrets manager, which provides a unified user interface for secrets across all backends (e.g., AWS S3) that use them.

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

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_asterisks_in_http_paths	Allow '*' character in URLs users can query	BOOLEAN	false
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_parser_override_extension	Allow extensions to override the current parser	VARCHAR	DEFAULT
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 Goose type does not have a clear native or canonical extension match in Arrow, export the types with a goose.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
arrow_output_version	Whether strings should be produced by Goose in Utf8View format instead of Utf8	VARCHAR	1.0
asof_loop_join_threshold	The maximum number of rows we need on the left side of an ASOF join to use a nested loop join	UBIGINT	64
auto_checkpoint_skip_wal_threshold	The estimated WAL write size at which point we will skip writing to the WAL and only checkpoint. Skipping writing to the WAL means concurrent commits are blocked while the checkpoint is happening.	UBIGINT	100000
auto_fallback_to_full_download	Allows automatically falling back to full file downloads when possible.	BOOLEAN	true
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	false
block_allocator_memory	Physical memory that the block allocator is allowed to use (this memory is never freed and cannot be reduced).	VARCHAR	0 bytes
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 Goose callers	VARCHAR
default_block_size	The default block size for new goose 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	ASCENDING
default_secret_storage	Allows switching the default storage for secrets	VARCHAR	local_file
deprecated_using_key_syntax	Configures the use of the deprecated union syntax for USING KEY CTEs.	VARCHAR	DEFAULT
disable_database_invalidation	Disables invalidating the database instance when encountering a fatal error. Should be used with great care, as Goose cannot guarantee correct behavior after a fatal error.	BOOLEAN	false
disable_parquet_prefetching	Disable the prefetching mechanism in Parquet	BOOLEAN	false
disable_timestamptz_casts	Disable casting from timestamp to timestamptz	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
goose_api	Goose API surface	VARCHAR	cli
dynamic_or_filter_threshold	The maximum amount of OR filters we generate dynamically from a hash join	UBIGINT	50
enable_curl_server_cert_verification	Enable server side certificate verification for CURL backend.	BOOLEAN	true
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_external_file_cache	Allow the database to cache external files (e.g., Parquet) in memory.	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_global_s3_configuration	Automatically fetch AWS credentials from environment variables.	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	1
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	false
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
errors_as_json	Output error messages as structured JSON instead of as a raw string	BOOLEAN	false
experimental_metadata_reuse	EXPERIMENTAL: Re-use row group and table metadata when checkpointing.	BOOLEAN	true
explain_output	Output of EXPLAIN statements (ALL, OPTIMIZED_ONLY, PHYSICAL_ONLY)	VARCHAR	PHYSICAL_ONLY
extension_directories	Set the directories to store extensions in	VARCHAR[]	[]
extension_directory	Set the directory to store extensions in	VARCHAR
external_threads	The number of external threads that work on Goose tasks.	UBIGINT	1
file_search_path	A comma separated list of directories to search for input files	VARCHAR
force_download	Forces upfront download of file	BOOLEAN	false
force_mbedtls_unsafe	Enable mbedtls for encryption (WARNING: unsafe to use)	BOOLEAN	false
force_variant_shredding	Forces the VARIANT shredding that happens at checkpoint to use the provided schema for the shredding.	VARCHAR	INVALID
geometry_minimum_shredding_size	Minimum size of a rowgroup to enable GEOMETRY shredding, or set to -1 to disable entirely. Defaults to 1/4th of a rowgroup	BIGINT	30000
home_directory	Sets the home directory used by the system	VARCHAR
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
httpfs_client_implementation	Select which is the HTTPUtil implementation to be used	VARCHAR	default
ieee_floating_point_ops	Use IEE754-compliant floating point operations (returning NAN instead of errors/NULL).	BOOLEAN	true
ignore_unknown_crs	Ignore unknown Coordinate Reference Systems (CRS) when creating geometry types or importing geospatial data.	BOOLEAN	false
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
integer_division	Whether or not the / operator defaults to integer division, or to floating point division	BOOLEAN	false
lambda_syntax	Configures the use of the deprecated single arrow operator (->) for lambda functions.	VARCHAR	DEFAULT
late_materialization_max_rows	The maximum amount of rows in the LIMIT/SAMPLE for which we trigger late materialization	UBIGINT	50
lock_configuration	Whether or not the configuration can be altered	BOOLEAN	false
log_query_path	Specifies the path to which queries should be logged (default: NULL, queries are not logged)	VARCHAR
logging_level	The log level which will be recorded in the log	VARCHAR	WARNING
logging_mode	Determines which types of log messages are logged	VARCHAR	LEVEL_ONLY
logging_storage	Set the logging storage (memory/stdout/file/<custom>)	VARCHAR	shell_log_storage
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
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
merge_http_secret_into_s3_request	Merges http secret params into S3 requests	BOOLEAN	true
merge_join_threshold	The maximum number of rows on either table to choose a merge join	UBIGINT	1000
nested_loop_join_threshold	The maximum number of rows on either table to choose a nested loop join	UBIGINT	5
old_implicit_casting	Allow implicit casting to/from VARCHAR	BOOLEAN	false
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
parquet_metadata_cache	Cache Parquet metadata - useful when reading the same files multiple times	BOOLEAN	false
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
password	The password to use. Ignored for legacy compatibility.	VARCHAR
perfect_ht_threshold	Threshold in bytes for when to use a perfect hash table	UBIGINT	12
pin_threads	Whether to pin threads to cores (Linux only, default AUTO: on when there are more than 64 cores)	VARCHAR	auto
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
prefetch_all_parquet_files	Use the prefetching mechanism for all types of parquet files	BOOLEAN	false
preserve_identifier_case	Whether or not to preserve the identifier case, instead of always lowercasing all non-quoted identifiers	BOOLEAN	true
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 Arrow strings should be produced by Goose in Utf8View format instead of Utf8	BOOLEAN	false
s3_access_key_id	S3 Access Key ID	VARCHAR	NULL
s3_allow_recursive_globbing	Whether globs on S3-like storage are optimized with recursive strategy (alterative is listing)	BOOLEAN	true
s3_endpoint	S3 Endpoint	VARCHAR	NULL
s3_kms_key_id	S3 KMS Key ID	VARCHAR	NULL
s3_region	S3 Region	VARCHAR	NULL
s3_requester_pays	S3 use requester pays mode	BOOLEAN	false
s3_secret_access_key	S3 Access Key	VARCHAR	NULL
s3_session_token	S3 Session Token	VARCHAR	NULL
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
s3_version_id_pinning	Pin S3 reads to a specific object version for consistency	BOOLEAN	false
scalar_subquery_error_on_multiple_rows	When a scalar subquery returns multiple rows - return a random row instead of returning an error.	BOOLEAN	true
scheduler_process_partial	Partially process tasks before rescheduling - allows for more scheduler fairness between separate queries	BOOLEAN	false
secret_directory	Set the directory to which persistent secrets are stored	VARCHAR	~/.goose/stored_secrets
storage_block_prefetch	In which scenarios to use storage block prefetching	VARCHAR	REMOTE_ONLY
storage_compatibility_version	Serialize on checkpoint with compatibility for a given goose 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)
temp_file_encryption	Encrypt all temporary files if database is encrypted	BOOLEAN	false
threads, worker_threads	The number of total threads used by the system.	BIGINT	# CPU cores
unsafe_disable_etag_checks	Disable checks on ETag consistency	BOOLEAN	false
user, username	The username to use. Ignored for legacy compatibility.	VARCHAR
validate_external_file_cache	Cache validation mode: VALIDATE_ALL (default, validate all cache entries), VALIDATE_REMOTE (validate only remote cache entries), or NO_VALIDATION (disable cache validation).	VARCHAR	VALIDATE_ALL
variant_minimum_shredding_size	Minimum size of a rowgroup to enable VARIANT shredding, or set to -1 to disable entirely. Defaults to 1/4th of a rowgroup	BIGINT	30000
wal_autocheckpoint_entries	Trigger automatic checkpoint when WAL entry count reaches or exceeds N (0 = disabled)	UBIGINT	0
warnings_as_errors	Escalate all warnings to errors.	BOOLEAN	false
write_buffer_row_group_count	The amount of row groups to buffer in bulk ingestion prior to flushing them together. Reducing this setting can reduce memory consumption.	UBIGINT	5
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	{"ATTACH_LOAD_STORAGE_LATENCY": "true", "ATTACH_REPLAY_WAL_LATENCY": "true", "BLOCKED_THREAD_TIME": "true", "CHECKPOINT_LATENCY": "true", "COMMIT_LOCAL_STORAGE_LATENCY": "true", "CPU_TIME": "true", "CUMULATIVE_CARDINALITY": "true", "CUMULATIVE_ROWS_SCANNED": "true", "EXTRA_INFO": "true", "LATENCY": "true", "OPERATOR_CARDINALITY": "true", "OPERATOR_NAME": "true", "OPERATOR_ROWS_SCANNED": "true", "OPERATOR_TIMING": "true", "OPERATOR_TYPE": "true", "QUERY_NAME": "true", "RESULT_SET_SIZE": "true", "ROWS_RETURNED": "true", "SYSTEM_PEAK_BUFFER_MEMORY": "true", "SYSTEM_PEAK_TEMP_DIR_SIZE": "true", "TOTAL_BYTES_READ": "true", "TOTAL_BYTES_WRITTEN": "true", "TOTAL_MEMORY_ALLOCATED": "true", "WAITING_TO_ATTACH_LATENCY": "true", "WAL_REPLAY_ENTRY_COUNT": "true", "WRITE_TO_WAL_LATENCY": "true"}
enable_http_logging	(deprecated) Enables HTTP logging	BOOLEAN	true
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
http_logging_output	(deprecated) The file to which HTTP logging output should be saved, or empty to print to the terminal	VARCHAR
profile_output, profiling_output	The file to which profile output should be saved, or empty to print to the terminal	VARCHAR
profiling_coverage	The profiling coverage (SELECT or ALL)	VARCHAR	SELECT
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
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