Configuration

SereneDB has a number of configuration options that can be used to change the behavior of the system. The defaults listed here are a server's; serened shell keeps DuckDB's defaults for some options, such as integer_division (false), preserve_identifier_case (true) and the default schema (main).

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 the pg_settings view. For example:

SQL

SELECT current_setting('memory_limit') AS memlimit;

 memlimit
----------
 (varies)

Or:

SQL

SELECT setting AS memlimitFROM pg_settingsWHERE name = 'memory_limit';

Examples

Set the memory limit of the system to 10 GB.

SQL

SET memory_limit = '10GB';

Configure the system to use 1 thread.

SQL

SET threads TO 1;

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

SQL

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

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

SQL

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

 write_log
-----------
 NULL

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

SQL

SELECT write_log(        'A new client has connected to SereneDB.',        level := 'debug',        scope := 'connection',        log_type := 'serenedb.docs.example'    );

 write_log
-----------
 NULL

Check logs with the DEBUG log level:

SQL

SELECT * FROM sdb_logs WHERE log_level = 'DEBUG';

Check logs with the QueryLog type:

SQL

SELECT * FROM sdb_logs WHERE type = 'QueryLog';

Check current logging settings:

SQL

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

Enable printing of a progress bar during long-running queries:

SQL

SET enable_progress_bar = true;

Set the default null order to NULLS LAST:

SQL

SET default_null_order = 'nulls_last';

Return the current value of a specific setting:

SQL

SELECT current_setting('threads') AS threads;

 threads
----------
 (varies)

Query a specific setting:

SQL

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

Show a list of all available settings:

SQL

SELECT *FROM sdb_settings();

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

SQL

RESET memory_limit;

Secrets Manager

SereneDB 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. Each scope is listed below, grouped by the part of the system the option affects.

To search the reference rather than read it, query the catalog — every option on this page is a setting object:

sql

SELECT name, summary FROM sdb_docs.objects
WHERE kind = 'setting' AND name LIKE 'compaction%';

Global Configuration Options

Search and indexing

Background maintenance of inverted indexes, and the knobs the search and vector operators read.

Name	Description	Type	Default value
cleanup_interval_step	Number of commit ticks between background unreferenced-file cleanup passes for newly created inverted indexes. Per-index WITH (cleanup_interval_step = ...) overrides. 0 disables cleanup. Default: 1.	UINTEGER	1
compaction_floor_segment_bytes	Inverted-index segments below this size count as equal-sized for compaction candidate selection. Per-index WITH (compaction_floor_segment_bytes = ...) overrides. Default 2097152 (2MB).	UBIGINT	2097152
compaction_interval	Background compaction interval (ms) for newly created inverted indexes. Per-index WITH (compaction_interval = ...) overrides. 0 disables the compaction task. Default: 1000.	UINTEGER	1000
compaction_max_segments	Maximum inverted-index segments merged by one background compaction. Per-index WITH (compaction_max_segments = ...) overrides. Default 10.	UINTEGER	10
compaction_max_segments_bytes	Byte budget of one background inverted-index compaction (the tier target size). Per-index WITH (compaction_max_segments_bytes = ...) overrides. Default 5368709120 (5GB).	UBIGINT	5368709120
refresh_interval	Background refresh interval (ms) for newly created inverted indexes. Per-index WITH (refresh_interval = ...) overrides. 0 disables the refresh task. Default: 1000.	UINTEGER	1000
reindex_interval	Periodic reindex interval (ms) for newly created VIEW-backed inverted indexes: every tick runs the ordinary REINDEX road (up_to_date / delta / rebuild) as the index owner. Per-index WITH (reindex_interval = ...) overrides; ALTER INDEX SET retunes a live loop. 0 disables the task. Hint-less iceberg sources refresh from the background only when the GLOBAL unsafe_enable_version_guessing is set. Default: 0.	UINTEGER	0
sdb_disable_top_k_optimization	When true, the optimizer skips pulling ORDER BY <scorer>(...) DESC LIMIT k into the inverted-index scan, so block-max score pruning never engages. Default: false (optimization on).	BOOLEAN	off
sdb_hnsw_ef_search	Search-time beam width (ef) for HNSW vector indexes. Higher values improve recall at the cost of latency. The beam is also the result ceiling: a value below the query's LIMIT returns fewer rows than asked for. Default 64.	INTEGER	64
sdb_ivf_max_search_fanout	Maximum number of IVF centroid-tree children expanded per node while descending to the probed clusters. Decouples the descent width from sdb_ivf_search_nprobe: lower values cut centroid work on deep (multi-level) trees at some recall cost. The width applies per node and so compounds over the tree's levels; it is raised when smaller than the width whose compounded value reaches sdb_ivf_search_nprobe, so the descent can always supply the requested number of clusters. Default 16.	INTEGER	16
sdb_ivf_posting_size	Target IVF posting-list size (leaf cap t), captured into the index config at CREATE INDEX. Smaller values force deeper multi-level centroid trees (useful for testing). Default 1024.	INTEGER	1024
sdb_ivf_sample_factor	Fraction of rows used to train the IVF centroid tree (sample_size = sample_factor * N), captured into the index config at CREATE INDEX. Any value in (0, 1]. Default 0.2.	DOUBLE	0.2
sdb_ivf_search_nprobe	Number of IVF cluster lists scanned per vector-similarity query. Higher values improve recall at the cost of latency. Default 8.	INTEGER	8
sdb_levenshtein_max_terms	The maximum number of dictionary terms a fuzzy predicate (ts_levenshtein) expands to per index segment. Terms closest to the query survive; the rest neither match nor contribute to scoring. Higher values improve recall on wide expansions at the cost of per-query work. 0 removes the cap, so every term within the edit distance matches. Default 50.	INTEGER	50
sdb_pattern_cache_size	Bytes of compiled search patterns (ts_regexp, ts_like, LIKE and fused alternations over an inverted index) kept across queries, so a pattern is compiled once per server rather than once per query. The least recently used patterns are dropped first; a pattern in use by a running query stays alive until it finishes. 0, the default, keeps nothing: each query compiles its own patterns. Server-global.	UBIGINT	0
sdb_rerank_factor	Multiplier applied to LIMIT k to size the candidate pool re-scored with exact distances for a quantized IVF vector-similarity query (pool = ceil(sdb_rerank_factor * k)). Higher values improve recall at the cost of latency; 0 disables reranking (top-k picked by the approximate quantized distance). Fractional values are allowed, but a nonzero factor below 1 is rejected because the pool must cover k. Default 4. Unquantized (quant = 'none') indexes never rerank, regardless of this setting.	DOUBLE	4
segment_docs_max	Document count at which an inverted-index segment writer rolls over to a new on-disk segment. Per-index WITH (segment_docs_max = ...) overrides. 0 = unlimited (memory limit governs).	UINTEGER	0
segment_memory_max	In-memory bytes an inverted-index or search-table segment writer fills before rolling over to a new on-disk segment (also the CREATE INDEX backfill commit cadence, and half of it is the write buffer a serial search-table statement fills before it starts feeding the index as it goes). Per-object WITH (segment_memory_max = ...) overrides. Default 268435456 (256MB). See Write memory.	UBIGINT	268435456

AI functions

Secrets, retries and quotas of the AI functions.

Name	Description	Type	Default value
sdb_ai_allow_insecure_endpoint	When false, AI functions refuse a secret whose base_url sends requests over plain http:// to a host other than localhost, 127.0.0.0/8 or ::1, because the prompts and the API key would travel unencrypted. Default: false.	BOOLEAN	false
sdb_ai_embedding_default_secret	Name of the openai secret used by ai_embed and ai_similarity when the call does not pass secret_name. Default: '' (no default).	VARCHAR
sdb_ai_embedding_max_batch_size	Maximum number of texts ai_embed and ai_similarity send in one embeddings request. Default: 100.	UINTEGER	100
sdb_ai_max_api_calls_per_query	Maximum number of AI provider requests a single query may send. 0 = unlimited. Default: 0.	UINTEGER	0
sdb_ai_max_concurrent_requests	Maximum number of AI provider requests a query has in flight, across all of its AI calls and threads. Requests run on DuckDB's async I/O threads (async_threads) and on the threads that evaluate the calls. Default: 16.	UINTEGER	16
sdb_ai_max_output_tokens_per_query	Maximum number of output tokens, as reported by the provider, a single query may consume; checked before each request, so requests already in flight may exceed it. 0 = unlimited. Default: 0.	UINTEGER	0
sdb_ai_max_retries	How many times an AI provider request is retried after a connection error or HTTP 408, 429, 5xx or 529. Default: 3.	UINTEGER	3
sdb_ai_request_timeout	Timeout of a single AI provider request, in seconds. Default: 120.	UINTEGER	120
sdb_ai_retry_initial_delay_ms	Delay before the first AI provider retry, in milliseconds; each further retry doubles it, up to 60 seconds. A Retry-After response header overrides it, up to 60 seconds. Default: 500.	UINTEGER	500
sdb_ai_system_one_default_secret	Name of the typesafe secret used by ai_system_one when the call does not pass secret_name. Default: '' (no default).	VARCHAR
sdb_ai_text_default_secret	Name of the openai secret used by ai_generate, ai_classify, ai_classify_labels, ai_extract, ai_filter, ai_translate, ai_redact, ai_score, ai_rerank, ai_agg and ai_summarize_agg when the call does not pass secret_name. Default: '' (no default).	VARCHAR
sdb_ai_throw_on_error	When true, a row whose AI function request fails fails the query; when false, that row returns NULL. Authentication, not-found, validation (422) and exhausted-quota (429 insufficient_quota) errors always fail the query. Default: true.	BOOLEAN	true
sdb_ai_throw_on_quota_exceeded	When true, exceeding sdb_ai_max_api_calls_per_query or sdb_ai_max_output_tokens_per_query fails the query; when false, the remaining rows return NULL. Default: true.	BOOLEAN	true

Memory and threads

How much memory the engine may use and how many threads it runs.

Name	Description	Type	Default value
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
async_threads	The number of total async threads used by the system for tasks like I/O; a session or transaction value caps the async threads its queries use (see SET).	BIGINT	64
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
max_memory	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
memory_limit	Alias of max_memory.	VARCHAR	80% of RAM
temp_file_encryption	Encrypt all temporary files if database is encrypted	BOOLEAN	false
threads	The number of total threads used by the system; a session or transaction value caps the threads its queries use (see SET).	BIGINT	the number of CPU cores
worker_threads	Alias of threads.	BIGINT	the number of CPU cores
write_buffer_row_group_memory_limit	The maximum data to buffer in row groups (in bytes) to buffer prior to flushing them together. When either this limit is reached, or write_buffer_row_group_count is reached, we flush the data to disk. Defaults to 20% of memory limit divided by thread count.	VARCHAR	925.0 MiB

Storage and checkpointing

Durability, the write-ahead log, and how rows are laid out on disk.

Name	Description	Type	Default value
checkpoint_on_detach	Override checkpoint behavior when detaching a database. ENABLED always checkpoints, DISABLED never checkpoints, DEFAULT defers to the global checkpoint_on_shutdown setting.	VARCHAR	DEFAULT
checkpoint_threshold	The WAL size threshold at which to automatically trigger a checkpoint (e.g. 1GB).	VARCHAR	16.0 MiB
default_block_size	The default block size for new duckdb database files (new as-in, they do not yet exist).	UBIGINT	262144
disabled_compression_methods	Disable a specific set of compression methods (comma separated)	VARCHAR
enable_optimistic_write	Whether or not to optimistically write large appends to disk before committing. Disable this to keep bulk appends in memory.	BOOLEAN	true
fsync_mode	How to synchronize database file changes to stable storage (STANDARD, NONE or FULL). NONE may cause database corruption after an operating system or hardware crash.	VARCHAR	STANDARD
initial_column_segment_size	The initial memory (in bytes) reserved for the first transient column segment. Must be a power of two. Internally, we subtract the block header size (typically 8 bytes) for segments with or exceeding 1024 bytes. Subsequent segments double in size until reaching the block size.	UBIGINT	2048
recovery_replay_depth	Maximum WAL chunks in flight per inverted index during recovery replay (the prefetch window; bounds replay memory). 0 = auto (4 x cpu threads).	UINTEGER	0
row_group_size	Default columnstore row-group size for newly created inverted indexes. Per-index WITH (row_group_size = ...) overrides it. Reads from existing indexes are unaffected. Default: 122'880.	UINTEGER	122880
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 duckdb version	VARCHAR	latest
wal_autocheckpoint	Alias of checkpoint_threshold.	VARCHAR	16.0 MiB
wal_autocheckpoint_entries	Trigger automatic checkpoint when WAL entry count reaches or exceeds N (0 = disabled)	UBIGINT	0
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

Query execution and the optimizer

Planning, rewriting and the shape of query output.

Name	Description	Type	Default value
approximate_join_order_threshold	The minimum number of tables in a join to determine the optimal join order approximately instead of exactly.	UBIGINT	12
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
default_null_order	NULL ordering used when none is specified (NULLS_FIRST or NULLS_LAST).	VARCHAR	NULLS_LAST
delim_join_as_cte	Rewrite delim joins to materialized CTEs during dependent join flattening.	BOOLEAN	on
disabled_optimizers	DEBUG SETTING: disable a specific set of optimizers (comma separated).	VARCHAR
dynamic_or_filter_threshold	The maximum amount of OR filters we generate dynamically from a hash join	UBIGINT	50
enable_caching_operators	Enables caching operators that cache intermediate results.	BOOLEAN	on
enable_optimizer	Whether or not query optimization is enabled.	BOOLEAN	on
explain_output	Output of EXPLAIN statements (ALL, OPTIMIZED_ONLY, PHYSICAL_ONLY)	VARCHAR	PHYSICAL_ONLY
explain_output_format	Column shape of EXPLAIN output (DUCKDB_NATIVE = native two-column key/value, PG = single QUERY PLAN column with one row per line).	VARCHAR	PG
late_materialization_max_rows	The maximum amount of rows in the LIMIT/SAMPLE for which we trigger late materialization	UBIGINT	50
match_recognize_max_states	The maximum number of pattern states MATCH_RECOGNIZE explores for a single match before it refuses the pattern (0 = no limit).	UBIGINT	1000000
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_streaming_buffer_size	The maximum number of bytes a streaming query result buffers (e.g. 1GB).	VARCHAR	10.0 MiB
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
null_order	Alias of default_null_order.	VARCHAR	NULLS_LAST
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
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
prefer_range_joins	Force use of range joins with mixed predicates	BOOLEAN	false
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
read_ahead_depth	The number of scan jobs prefetched ahead of decoding (-1 = automatic, bounded by a memory budget; 0 = disabled).	BIGINT	-1
scalar_subquery_error_on_multiple_rows	When a scalar subquery returns multiple rows - return a random row instead of returning an error.	BOOLEAN	true
sdb_job_max_depth	Maximum number of job runs in one chain of EXECUTE JOB calls, where a job's body executes another job; a run that would go deeper fails. Default 16. Server-global.	UINTEGER	16
standard_vector_size	The compiled-in STANDARD_VECTOR_SIZE (read-only).	UBIGINT	2048

Session and PostgreSQL compatibility

Wire-protocol and session state that PostgreSQL clients read or set.

Name	Description	Type	Default value
access_mode	Access mode of the database (AUTOMATIC, READ_ONLY or READ_WRITE)	VARCHAR	automatic
bytea_output	Sets the output format for bytea.	VARCHAR	hex
Calendar	The current calendar	VARCHAR	System (locale) calendar
client_encoding	Sets the client's character set encoding.	VARCHAR	UTF8
client_min_messages	Sets the message levels that are sent to the client.	VARCHAR	notice
current_transaction_invalidation_policy	Which types of exceptions invalidate the database for the current transaction	VARCHAR	STANDARD_POLICY
custom_user_agent	Metadata from SereneDB callers	VARCHAR
DateStyle	Sets the display format for date and time values.	VARCHAR	ISO, MDY
default_transaction_invalidation_policy	When to invalidate transactions when errors occur (SYNTACTIC_ERRORS_DO_NOT_INVALIDATE, i.e. parser and binder exceptions do not invalidate, or ALL_ERRORS_INVALIDATE_TRANSACTION).	VARCHAR	ALL_ERRORS_INVALIDATE_TRANSACTION
default_transaction_isolation	Sets the transaction isolation level of each new transaction.	VARCHAR	repeatable read
default_transaction_read_only	Sets the default read-only status of new transactions.	BOOLEAN	off
dialect_compatibility_mode	Enable SQL dialect compatibility for a certain engine (e.g. SET dialect_compatibility_mode='spark').	VARCHAR	NONE
duckdb_api	SereneDB API surface	VARCHAR	cli
error_on_division_by_zero	Throw an error instead of returning NULL when dividing by zero.	BOOLEAN	true
errors_as_json	Output error messages as structured JSON instead of as a raw string	BOOLEAN	false
extra_float_digits	Sets the number of digits displayed for floating-point values.	INTEGER	1
immediate_transaction_mode	Whether transactions should be started lazily when needed, or immediately when BEGIN TRANSACTION is called	BOOLEAN	false
in_hot_standby	Shows whether hot standby is currently active.	BOOLEAN	off
integer_datetimes	Shows whether datetimes are integer based.	BOOLEAN	on
IntervalStyle	Sets the display format for interval values.	VARCHAR	postgres
lock_configuration	Whether or not configurations can be altered	BOOLEAN	false
max_execution_time	The maximum execution time per query in milliseconds (0 = no limit).	BIGINT	0
pg_idle_in_transaction_timeout_millis	Postgres idle in transaction timeout in milliseconds to set on scan connections.	UINTEGER
regex_match_operator_semantics	Configures whether regex match operators use partial or full string matching.	VARCHAR	partial
role	Sets the current role.	VARCHAR
server_encoding	Shows the server (database) character set encoding.	VARCHAR	UTF8
server_version	Shows the server version.	VARCHAR	18.3
server_version_num	Shows the server version as an integer.	INTEGER
show_behavior	Configures how SHOW resolves a bare identifier: auto describes a table if one exists, else a setting (deprecated); table always describes a table; setting always describes a setting.	VARCHAR	SETTING
standard_conforming_strings	Causes '...' strings to treat backslashes literally.	BOOLEAN	on
statement_timeout	Aborts any statement that takes more than the specified amount of time (milliseconds without a unit; units us, ms, s, min, h, d) with SQLSTATE 57014, as in PostgreSQL. 0 disables the timeout. Sets max_execution_time for the same scope.	VARCHAR	0
table_function_identifier_conversion	Configures the use of deprecated implicit conversion of unbound identifiers to strings in table function arguments.	VARCHAR	DISABLE_IMPLICIT_STRING
TimeZone	The current time zone	VARCHAR	System (locale) timezone

Logical replication

Server-wide settings of subscriptions. Only a superuser may set them, and a change applies to running subscriptions too.

Name	Description	Type	Default value
max_sync_workers_per_subscription	How many tables one subscription copies at the same time during its initial synchronization, each over its own publisher connection (a walsender on the publisher).	UINTEGER	2
wal_receiver_status_interval	How often a subscription reports its position to the publisher (seconds without a unit; units us, ms, s, min, h, d). 0 disables the periodic reports.	VARCHAR	10s
wal_receiver_timeout	A subscription whose publisher sends nothing for this long drops the connection and reconnects (milliseconds without a unit). 0 disables the timeout.	VARCHAR	1min
wal_retrieve_retry_interval	How long a failed subscription waits before it reconnects (milliseconds without a unit).	VARCHAR	5s

Security

Authentication, authorization and what the server is allowed to reach.

Name	Description	Type	Default value
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_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
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_macro_dependencies	Enable created MACROs to create dependencies on the referenced objects (such as tables)	BOOLEAN	true
hba	Host-based authentication ruleset (pg_hba.conf syntax) as a single string: one rule per line, first match wins, applied to connections opened after the SET. An un-removable local + loopback trust rule is force-prepended so a bad ruleset cannot lock you out; an empty value restores the default (unix trust + SCRAM for TCP). Server-global.	VARCHAR
scram_iterations	Sets the iteration count for SCRAM secret generation.	INTEGER	4096
secret_directory	Set the directory to which persistent secrets are stored	VARCHAR	<datadir>/secrets

Logging, metrics and profiling

What the server records about itself.

Name	Description	Type	Default value
enable_logging	Enables the logger	BOOLEAN	1
legacy_metrics_format	When enabled, profiling output uses the legacy flat format instead of the current grouped format.	BOOLEAN	off
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	INFO
logging_mode	Determines which types of log messages are logged	VARCHAR	LEVEL_ONLY
logging_storage	Set the logging storage (memory/stdout/file/<custom>)	VARCHAR	stdout
sdb_job_history_size	Number of recent job runs, across all jobs, that sdb_job_runs() returns; the oldest run is dropped first. 0 keeps none. Default 1024. Server-global.	UBIGINT	1024

Files, object stores and caching

Local paths, remote object stores and the external file cache.

Name	Description	Type	Default value
ca_cert_file	Path to a custom certificate file for self-signed certificates.	VARCHAR
cache_local_files	Whether the external file cache also caches local files (remote files are always cached).	BOOLEAN	off
enable_fsst_vectors	Allow scans on FSST compressed segments to emit compressed vectors to utilize late decompression	BOOLEAN	false
enable_server_cert_verification	Enable server side certificate verification.	BOOLEAN	false
extension_directory	Set the directory to store extensions in	VARCHAR	<datadir>/extensions
external_file_cache_local_max_block_size	Maximum size in bytes of an external file cache block for local (non-remote) files. Larger reads are split into blocks of this size, which are fetched in parallel.	UBIGINT	16384
external_file_cache_remote_max_block_size	Maximum size in bytes of an external file cache block for remote files (e.g. HTTP/S3). Larger reads are split into blocks of this size, which are fetched in parallel.	UBIGINT	2097152
external_file_cache_remote_min_block_size	Reads of remote files are rounded out to aligned blocks of this size, so tiny reads do not each cost a request.	UBIGINT	4096
external_file_cache_spill	Whether evicted external file cache blocks of remote files spill to the temporary directory instead of being dropped, so they are re-read from there rather than re-fetched.	BOOLEAN	false
extra_http_headers	Extra HTTP headers added to every request.	MAP(VARCHAR, VARCHAR)	{}
force_download	Forces upfront download of file	BOOLEAN	false
gcs_user_project	GCS billing project for Requester Pays.	VARCHAR
hf_max_per_page	Debug option to limit number of items returned in list requests.	UBIGINT	0
home_directory	Sets the home directory used by the system	VARCHAR
http_client_pool_capacity	HTTP client pool capacity, derived from the CPU and async thread counts unless explicitly set.	BIGINT	128
http_keep_alive	Keep alive connections. Setting this to false can help when running into connection failures	BOOLEAN	true
http_proxy	HTTP proxy host	VARCHAR
http_proxy_password	Password for HTTP proxy	VARCHAR
http_proxy_username	Username for HTTP proxy	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
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_list_concurrency	Maximum number of concurrent LIST requests issued while expanding an S3 glob. The window is also bounded by async_threads + 1 and shrinks automatically when the server throttles.	UBIGINT	256
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_url_compatibility_mode	Disable Globs and Query Parameters on S3 URLs	BOOLEAN	false
s3_url_scheme_aliases	Additional URL schemes routed to the S3-compatible filesystem (e.g. ['oss', 'cos']). Can only be set globally; secrets for aliased schemes need an explicit SCOPE.	VARCHAR[]	[]
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
temp_directory	Set the directory to which to write temp files	VARCHAR	<datadir>/tmp

Database connectors

Settings for the PostgreSQL and ClickHouse connectors.

Name	Description	Type	Default value
pg_array_as_varchar	Read Postgres arrays as varchar - enables reading mixed dimensional arrays.	BOOLEAN	off
pg_connection_cache	Whether or not to use the connection pooling. This option is deprecated, instead to disable the connection pooling use "SET pg_pool_max_connections=0" for newly attached Postgres databases and "FROM postgres_configure_pool(catalog_name='my_attached_postgres_db', max_connections=0)" for already attached Postgres databases.	BOOLEAN	on
pg_connection_limit	The maximum amount of concurrent Postgres connections. This option is deprecated, instead use "SET pg_pool_max_connections = 42" for newly attached Postgres databases and "FROM postgres_configure_pool(catalog_name='my_attached_postgres_db', max_connections=42)" for already attached Postgres databases.	UBIGINT	32
pg_experimental_filter_pushdown	Whether or not to use filter pushdown.	BOOLEAN	on
pg_null_byte_replacement	When writing NULL bytes to Postgres, replace them with the given character.	VARCHAR
pg_oauth_token	OAuth bearer token for PostgreSQL OAUTHBEARER authentication. Takes priority over the PGOAUTHTOKEN environment variable.	VARCHAR
pg_order_pushdown	Push ORDER BY and LIMIT clauses to Postgres (default: true).	BOOLEAN	on
pg_pages_per_task	The amount of pages per task.	UBIGINT	1000
pg_statement_timeout_millis	Postgres statement timeout in milliseconds to set on scan connections.	UINTEGER
pg_use_binary_copy	Whether or not to use BINARY copy to read data.	BOOLEAN	on
pg_use_ctid_scan	Whether or not to parallelize scanning using table ctids.	BOOLEAN	on
pg_use_text_protocol	Whether or not to use TEXT protocol to read data. This is slower, but provides better compatibility with non-Postgres systems.	BOOLEAN	off

File formats

Defaults for the CSV, Parquet, JSON, Arrow and Iceberg readers and writers.

Name	Description	Type	Default value
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 SereneDB type does not have a clear native or canonical extension match in Arrow, export the types with a duckdb.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 SereneDB in Utf8View format instead of Utf8	VARCHAR	1.0
binary_as_string	In Parquet files, interpret binary data as a string.	BOOLEAN	false
copy_csv_header_default	Whether COPY ... TO a CSV file writes a header line when HEADER is not specified.	BOOLEAN	off
parquet_metadata_cache	Cache Parquet metadata - useful when reading the same files multiple times	BOOLEAN	false
parquet_prefetch_column_gap	Byte gap under which Parquet prefetch I/O ranges are coalesced (NULL lets the cost model adapt it).	UBIGINT
produce_arrow_string_view	Whether Arrow strings should be produced by SereneDB in Utf8View format instead of Utf8	BOOLEAN	false

Extensions

Installing and loading extensions.

Name	Description	Type	Default value
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	false
autoload_known_extensions	Whether known extensions are allowed to be automatically loaded when a query depends on them	BOOLEAN	false
custom_extension_repository	Overrides the custom endpoint for remote extension installation	VARCHAR
extension_directories	Set the directories to store extensions in	VARCHAR[]	[]

Debugging and unsafe options

Not for production. These exist to reproduce and diagnose engine behaviour, and several can corrupt data.

Name	Description	Type	Default value
disable_database_invalidation	Disables invalidating the database instance when encountering a fatal error. Should be used with great care, as SereneDB cannot guarantee correct behavior after a fatal error.	BOOLEAN	true
disable_parquet_prefetching	Disable the prefetching mechanism in Parquet	BOOLEAN	false
disable_timestamptz_casts	Disable casting from timestamp to timestamptz	BOOLEAN	false
enable_view_dependencies	Enable created VIEWs to create dependencies on the referenced objects (such as tables)	BOOLEAN	true
force_download_threshold	Forces upfront download of files smaller than the given size in bytes	UBIGINT	0
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
legacy_disable_null_type	When enabled, prevent the NULL type from leaving the binder (< v2.0 default behavior).	BOOLEAN	off
old_implicit_casting	Allow implicit casting to/from VARCHAR	BOOLEAN	false
unsafe_disable_etag_checks	Disable checks on ETag consistency	BOOLEAN	false

Other

Everything that does not fall into the groups above.

Name	Description	Type	Default value
__delta_only_variant_encoding_enabled	Enables the Parquet reader to identify a Variant structurally.	BOOLEAN	false
allowed_configs	List of configuration options that are ALWAYS allowed to be changed - even when lock_configuration is true	VARCHAR[]	[]
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[]	[]
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
catalog_error_max_schemas	The maximum number of schemas the system will scan for "did you mean..." style errors in the catalog	UBIGINT	100
default_collation	The collation setting used when none is specified	VARCHAR
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
disabled_filesystems	Disable specific file systems preventing access (e.g., LocalFileSystem)	VARCHAR
disabled_log_types	Sets the list of disabled loggers	VARCHAR
enable_curl_server_cert_verification	Enable server side certificate verification for CURL backend.	BOOLEAN	true
enable_external_file_cache	Allow the database to cache external files (e.g., Parquet) in memory.	BOOLEAN	true
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
enabled_log_types	Sets the list of enabled loggers	VARCHAR
experimental_metadata_reuse	EXPERIMENTAL: Re-use row group and table metadata when checkpointing.	BOOLEAN	true
external_threads	The number of external threads that work on SereneDB tasks.	UBIGINT	0
file_search_path	A comma separated list of directories to search for input files	VARCHAR
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
httpfs_connection_caching	Enable connection caching for HTTP requests	BOOLEAN	true
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
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	true
lambda_syntax	Configures the use of the deprecated single arrow operator (->) for lambda functions.	VARCHAR	ENABLE_SINGLE_ARROW
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
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
preserve_identifier_case	Whether or not to preserve the identifier case, instead of always lowercasing all non-quoted identifiers	BOOLEAN	false
scheduler_process_partial	Partially process tasks before rescheduling - allows for more scheduler fairness between separate queries	BOOLEAN	true
vacuum_rebuild_indexes	(Experimental) Allow 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. 0 = disabled.	UBIGINT	0
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
warnings_as_errors	Escalate all warnings to errors.	BOOLEAN	false
zstd_min_string_length	The (average) length at which to enable ZSTD compression, defaults to 4096	UBIGINT	4096

Local Configuration Options

Memory and threads

How much memory the engine may use and how many threads it runs.

Name	Description	Type	Default value
operator_memory_limit	The maximum memory for query intermediates (sorts, hash tables) per connection (e.g. 256MB).	VARCHAR

Query execution and the optimizer

Planning, rewriting and the shape of query output.

Name	Description	Type	Default value
filter_reorder	How far filter reordering may move conditions that can fail (never, safe, fast or always)	VARCHAR	fast

Session and PostgreSQL compatibility

Wire-protocol and session state that PostgreSQL clients read or set.

Name	Description	Type	Default value
application_name	Sets the application name to be reported in statistics and logs.	VARCHAR
is_superuser	Shows whether the current session's user is a superuser.	BOOLEAN	on
search_path	Sets the default catalog search path as a comma-separated list of values	VARCHAR	"$user", public
session_authorization	Sets the current session's user name.	VARCHAR
session_replication_role	Sets the session's behavior for triggers, as in PostgreSQL: origin (the default) and local fire the triggers enabled with ENABLE TRIGGER, replica fires only those enabled with ENABLE REPLICA TRIGGER and skips foreign key checks; ENABLE ALWAYS triggers fire in every role. Only superusers may change it. The apply worker of a subscription runs as replica.	VARCHAR	origin
transaction_isolation	Sets the current transaction's isolation level.	VARCHAR	repeatable read

Logging, metrics and profiling

What the server records about itself.

Name	Description	Type	Default value
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	Enables the progress bar, printing progress to the terminal for long queries	BOOLEAN	true
enable_progress_bar_print	Controls the printing of the progress bar, when 'enable_progress_bar' is true	BOOLEAN	false
http_logging_output	(deprecated) The file to which HTTP logging output should be saved, or empty to print to the terminal	VARCHAR
profile_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
profiling_output	Alias of profile_output.	VARCHAR
profiling_renderer_settings	A map of settings passed to the renderer of the profiler output (e.g. {'max_extra_lines': 100}) - settings not recognized by the active renderer are ignored.	MAP(VARCHAR, VARCHAR)	{}
progress_bar_time	Sets the time (in milliseconds) how long a query needs to take before we start printing a progress bar	BIGINT	2000
tracked_metrics	A list of metric glob patterns to enable for collection (e.g. ['query.', 'optimizer.']).	VARCHAR[]	[*]

Other

Everything that does not fall into the groups above.

Name	Description	Type	Default value
schema	Sets the default search schema. Equivalent to setting search_path to a single value.	VARCHAR	public
streaming_buffer_size	The maximum memory to buffer between fetching from a streaming result (e.g., 1GB)	VARCHAR	976.5 KiB