Snowflake Postgres Server Settings¶

The table below details the parameters that can be set for the Postgres server component of Snowflake Postgres instances. Each setting’s name is hyperlinked to its Postgres documentation.

Where “Postgres default” appears in the Default column, Snowflake Postgres instances use the default value from Postgres. This can very by major version.

See Creating a Snowflake Postgres Instance for details on setting values for these Postgres server settings when creating Snowflake Postgres instances.

Tip

To see a parameter’s documentation for a specific major version change the word “current” in the hyperlink address to the target major version. For example, this hyperlink address for the postgres:work_mem setting:

https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM

becomes this to visit its Postgres 17 documentation:

https://www.postgresql.org/docs/17/runtime-config-resource.html#GUC-WORK-MEM

Component	Name	Requires restart	Description	Default
pgbouncer	autodb_idle_timeout	FALSE	If the automatically created (via “*”) database pools have been unused this many seconds, they are freed.	3600
pgbouncer	default_pool_size	FALSE	How many server connections to allow per user/database pair.	497
pgbouncer	ignore_startup_parameters	FALSE	Ignore parameters startup packets (e.g. options,extra_float_digits).	client_encoding,datestyle,timezone,standard_conforming_strings,extra_float_digits
pgbouncer	max_prepared_statements	FALSE	Number of prepared statements kept active on a single server connection	250
pgbouncer	pool_mode	FALSE	Specifies when a server connection can be reused by other clients.	transaction
pgbouncer	server_idle_timeout	If a server connection has been idle more than this many seconds it will be closed.	FALSE	60
postgres	auto_explain.log_analyze	FALSE	Causes EXPLAIN ANALYZE output, rather than just EXPLAIN output, to be printed when an execution plan is logged.	Postgres default
postgres	auto_explain.log_buffers	FALSE	Controls whether buffer usage statistics are printed when an execution plan is logged.	Postgres default
postgres	auto_explain.log_format	FALSE	Selects the EXPLAIN output format to be used.	Postgres default
postgres	auto_explain.log_min_duration	FALSE	The minimum statement execution time, in milliseconds, that will cause the statement’s plan to be logged.	Postgres default
postgres	auto_explain.log_nested_statements	FALSE	Causes nested statements (statements executed inside a function) to be considered for logging.	Postgres default
postgres	auto_explain.log_timing	FALSE	Controls whether per-node timing information is printed when an execution plan is logged.	Postgres default
postgres	auto_explain.log_triggers	FALSE	Causes trigger execution statistics to be included when an execution plan is logged.	Postgres default
postgres	auto_explain.log_verbose	FALSE	Controls whether verbose details are printed when an execution plan is logged.	Postgres default
postgres	auto_explain.sample_rate	FALSE	Causes auto_explain to only explain a fraction of the statements in each session.	Postgres default
postgres	autovacuum_analyze_scale_factor	FALSE	Specifies a fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger an ANALYZE.	Postgres default
postgres	autovacuum_freeze_max_age	TRUE	Specifies the maximum age (in transactions) that a table’s transaction ID can attain before a VACUUM operation is forced to prevent transaction ID wraparound within the table.	Postgres default
postgres	autovacuum_vacuum_cost_delay	FALSE	Specifies the cost delay value that will be used in automatic VACUUM operations. If -1 is specified, the regular vacuum_cost_delay value will be used.	Postgres default
postgres	autovacuum_vacuum_cost_limit	FALSE	Specifies the cost limit value that will be used in automatic VACUUM operations.	Postgres default
postgres	autovacuum_vacuum_insert_scale_factor	FALSE	Specifies a fraction of the table size to add to autovacuum_vacuum_insert_threshold when deciding whether to trigger a VACUUM.	Postgres default
postgres	autovacuum_vacuum_insert_threshold	FALSE	Specifies the number of inserted tuples needed to trigger a VACUUM in any one table.	Postgres default
postgres	autovacuum_vacuum_scale_factor	FALSE	Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM.	Postgres default
postgres	checkpoint_completion_target	FALSE	Specifies the target of checkpoint completion, as a fraction of total time between checkpoints.	Postgres default
postgres	checkpoint_timeout	FALSE	Maximum time between automatic WAL checkpoints.	Postgres default
postgres	checkpoint_warning	FALSE	Write a message to the server log if checkpoints caused by the filling of WAL segment files happen closer together than this amount of time.	Postgres default
postgres	default_statistics_target	FALSE	Sets the default statistics target for table columns without a column-specific target set via ALTER TABLE SET STATISTICS.	Postgres default
postgres	default_text_search_config	FALSE	Selects the text search configuration that is used by those variants of the text search functions that do not have an explicit argument specifying the configuration.	Postgres default
postgres	default_transaction_read_only	FALSE	A read-only SQL transaction cannot alter non-temporary tables.	off
postgres	hot_standby_feedback	FALSE	Specifies whether or not a hot standby will send feedback to the primary or upstream standby about queries currently executing on the standby.	on
postgres	idle_in_transaction_session_timeout	FALSE	Terminate any session that has been idle within an open transaction for longer than the specified amount of time.	Postgres default
postgres	intervalstyle	FALSE	Sets the display format for interval value.	Postgres default
postgres	jit	FALSE	Enable JIT support.	Postgres default
postgres	lock_timeout	FALSE	Abort any statement that waits longer than the specified amount of time while attempting to acquire a lock.	Postgres default
postgres	log_autovacuum_min_duration	FALSE	Causes each action executed by autovacuum to be logged if it ran for at least the specified amount of time.	Postgres default
postgres	log_connections	FALSE	Outputs a line to the server logs detailing each successful connection.	Postgres default
postgres	log_destination	FALSE	Sets the desired log destinations.	syslog,stderr
postgres	log_disconnections	FALSE	Causes session terminations to be logged. The log output provides information similar to log_connections, plus the duration of the session.	Postgres default
postgres	log_duration	FALSE	Causes the duration of every completed statement to be logged.	Postgres default
postgres	log_line_prefix	FALSE	Specifies a printf-style string that is output at the beginning of each log line.	[%p][%b][%v][%x] %q[user=%u,db=%d,app=%a]
postgres	log_lock_waits	FALSE	Controls whether a log message is produced when a session waits longer than deadlock_timeout to acquire a lock.	on
postgres	log_min_duration_sample	FALSE	Allows sampling the duration of completed statements that ran for at least the specified amount of time.	Postgres default
postgres	log_min_duration_statement	FALSE	Causes the duration of each completed statement to be logged if the statement ran for at least the specified amount of time.	2s
postgres	log_min_messages	FALSE	Controls which message levels are written to the server log.	notice
postgres	log_rotation_size	FALSE	This determines the maximum size of an individual log file.	Postgres default
postgres	log_statement	FALSE	Controls which SQL statements are logged.	ddl
postgres	log_statement_sample_rate	FALSE	Determines the fraction of statements with duration exceeding log_min_duration_sample that will be logged.	Postgres default
postgres	log_temp_files	FALSE	Controls logging of temporary file names and sizes.	10MB
postgres	log_transaction_sample_rate	FALSE	Sets the fraction of transactions whose statements are all logged, in addition to statements logged for other reasons.	Postgres default
postgres	logical_decoding_work_mem	FALSE	Specifies the maximum amount of memory to be used by logical decoding.	Postgres default
postgres	maintenance_work_mem	FALSE	Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.	TOTAL_MEMORY * 0.4
postgres	max_connections	TRUE	Determines the maximum number of concurrent connections to the database server.	500
postgres	max_locks_per_transaction	TRUE	Controls the average number of object locks allocated for each transaction.	Postgres default
postgres	max_logical_replication_workers	TRUE	Specifies maximum number of logical replication workers.	Postgres default
postgres	max_parallel_maintenance_workers	FALSE	Sets the maximum number of parallel workers that can be started by a single utility command.	Postgres default
postgres	max_parallel_workers	FALSE	Sets the maximum number of workers that the cluster can support for parallel operations.	NUM_CPUS
postgres	max_parallel_workers_per_gather	FALSE	Sets the maximum number of workers that can be started by a single Gather or Gather Merge node.	NUM_CPUS
postgres	max_replication_slots	TRUE	Specifies the maximum number of replication slots that the server can support.	10
postgres	max_slot_wal_keep_size	FALSE	Specifies the maximum size of WAL files that replication slots are allowed to retain in the `pg_wal` directory at checkpoint time.	STORAGE_GB * 0.1
postgres	max_standby_archive_delay	FALSE	Determines how long the standby server should wait before canceling standby queries that conflict with about-to-be-applied WAL entries.	Postgres default
postgres	max_standby_streaming_delay	FALSE	Determines how long the standby server should wait before canceling standby queries that conflict with about-to-be-applied WAL entries.	Postgres default
postgres	max_wal_senders	TRUE	Specifies the maximum number of concurrent connections from standby servers or streaming base backup clients.	10
postgres	max_wal_size	FALSE	Maximum size to let the WAL grow during automatic checkpoints.	MIN(10GB, STORAGE_GB * 0.1)
postgres	max_worker_processes	TRUE	Sets the maximum number of background processes that the cluster can support.	100
postgres	pg_stat_statements.max	TRUE	Maximum number of statements tracked.	Postgres default
postgres	pg_stat_statements.track	FALSE	Control which statements should be tracked.	Postgres default
postgres	pg_stat_statements.track_utility	FALSE	Should the utility commands be tracked. Utility commands are all those other than SELECT, INSERT, UPDATE, DELETE, and MERGE.	Postgres default
postgres	random_page_cost	FALSE	Sets the planner’s estimate of the cost of a non-sequentially-fetched disk page.	1.1
postgres	session_preload_libraries	FALSE	Specifies one or more shared libraries that are to be preloaded at connection start.	Postgres default
postgres	statement_timeout	FALSE	Abort any statement that takes more than the specified amount of time.	Postgres default
postgres	synchronous_commit	FALSE	Specifies how much WAL processing must complete before the database server returns a “success” indication to the client.	local
postgres	syslog_split_messages	FALSE	Split messages sent to syslog by lines and to fit into 1024 bytes	Postgres default
postgres	tcp_keepalives_count	FALSE	Specifies the number of TCP keepalive messages that can be lost before the server’s connection to the client is considered dead.	4
postgres	tcp_keepalives_idle	FALSE	Specifies the amount of time with no network activity after which the operating system should send a TCP keepalive message to the client.	2
postgres	temp_file_limit	FALSE	Specifies the maximum amount of disk space that a process can use for temporary files, such as sort and hash temporary files, or the storage file for a held cursor.	MIN(2000GB, STORAGE_GB * 0.25)
postgres	track_activity_query_size	TRUE	Memory reserved to store the text of the currently executing command for each active session, for the pg_stat_activity.query field.	Postgres default
postgres	track_commit_timestamp	TRUE	Record commit time of transactions.	Postgres default
postgres	wal_keep_size	FALSE	Specifies the minimum size of past WAL files kept in the pg_wal directory, in case a standby server needs to fetch them for streaming replication.	Postgres default
postgres	wal_sender_timeout	FALSE	Sets the maximum time to wait for WAL replication.	Postgres default
postgres	work_mem	FALSE	Sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files.	(TOTAL_MEMORY * 0.75)/ (NUM_CORES * 8)