Snowflake Postgres 服务器设置

下表详细列出了可为 Snowflake Postgres 实例的 Postgres服务器组件设置的参数。每个设置的名称均链接至其对应的 Postgres 文档。

Where “Postgres default” appears in the Default column, Snowflake Postgres instances use the default value from Postgres. This can vary 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 (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM)

若需查看其 Postgres 17 的文档，可修改为：

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

组件名称需要重新启动描述默认值
pgbouncerautodb_idle_timeout (https://www.pgbouncer.org/config.html)FALSE如果自动创建（通过“*”）的数据库连接池闲置超过此秒数，将被释放。3600
pgbouncerdefault_pool_size (https://www.pgbouncer.org/config.html)FALSE每个用户/数据库对允许的最大服务器连接数。497
pgbouncerignore_startup_parameters (https://www.pgbouncer.org/config.html)FALSE忽略启动包中的参数（例如 options、extra_float_digits）。client_encoding,datestyle,timezone,standard_conforming_strings,extra_float_digits
pgbouncermax_prepared_statements (https://www.pgbouncer.org/config.html)FALSE单个服务器连接上保持活跃状态的预处理语句数量250
pgbouncerpool_mode (https://www.pgbouncer.org/config.html)FALSE指定服务器连接何时可被其他客户端重复使用。transaction
pgbouncerserver_idle_timeout (https://www.pgbouncer.org/config.html)FALSEIf a server connection has been idle for more than this many seconds, it will be closed.60
postgresauto_explain.log_analyze (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-ANALYZE)FALSE当执行计划被记录时，输出 EXPLAIN ANALYZE 内容而非仅 EXPLAIN 输出。Postgres 默认值
postgresauto_explain.log_buffers (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-BUFFERS)FALSE控制在记录执行计划时是否打印缓冲区使用统计信息。Postgres 默认值
postgresauto_explain.log_format (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-FORMAT)FALSE选择要使用的 EXPLAIN 输出格式。Postgres 默认值
postgresauto_explain.log_min_duration (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-MIN-DURATION)FALSE触发语句计划记录的最小语句执行时间（单位为毫秒）。Postgres 默认值
postgresauto_explain.log_nested_statements (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-NESTED-STATEMENTS)FALSE使嵌套语句（在函数内部执行的语句）可被纳入记录范围。Postgres 默认值
postgresauto_explain.log_timing (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-TIMING)FALSE控制在记录执行计划时是否打印各节点的时间信息。Postgres 默认值
postgresauto_explain.log_triggers (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-TRIGGERS)FALSE在记录执行计划时包含触发器执行统计信息。Postgres 默认值
postgresauto_explain.log_verbose (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-VERBOSE)FALSE控制在记录执行计划时是否打印详细细节。Postgres 默认值
postgresauto_explain.sample_rate (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-SAMPLE-RATE)FALSE使 auto_explain 仅解析每个会话中部分语句的执行计划。Postgres 默认值
postgresautovacuum_analyze_scale_factor (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR)FALSE指定决定是否触发 ANALYZE 时，在 autovacuum_analyze_threshold 基础上附加的表大小比例系数。Postgres 默认值
postgresautovacuum_freeze_max_age (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE)TRUE指定表的交易 ID 在达到此最大年龄（以交易次数计）前，必须强制执行 VACUUM 操作以防止表内交易 ID 回卷。Postgres 默认值
postgresautovacuum_vacuum_cost_delay (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY)FALSE指定在自动 VACUUM 操作中使用的成本延迟值。如果指定为 -1，则将使用常规的 vacuum_cost_delay 值。Postgres 默认值
postgresautovacuum_vacuum_cost_limit (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-LIMIT)FALSE指定在自动 VACUUM 操作中使用的成本限制值。Postgres 默认值
postgresautovacuum_vacuum_insert_scale_factor (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-SCALE-FACTOR)FALSE指定决定是否触发 VACUUM 时，在 autovacuum_vacuum_insert_threshold 基础上附加的表大小比例系数。Postgres 默认值
postgresautovacuum_vacuum_insert_threshold (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD)FALSE指定单个表中触发 VACUUM 所需的插入元组数量。Postgres 默认值
postgresautovacuum_vacuum_scale_factor (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR)FALSE指定决定是否触发 VACUUM 时，在 autovacuum_vacuum_threshold 基础上附加的表大小比例系数。Postgres 默认值
postgrescheckpoint_completion_targetFALSE指定检查点完成目标，以检查点间隔总时长的比例表示。Postgres 默认值
postgrescheckpoint_timeout (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-CHECKPOINT-TIMEOUT)FALSE自动 WAL 检查点之间的最长时间间隔。Postgres 默认值
postgrescheckpoint_warning (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-CHECKPOINT-WARNING)FALSE如果因 WAL 分段文件填满而触发的检查点间隔短于此设定时间，则向服务器日志写入警告信息。Postgres 默认值
postgresdefault_statistics_target (https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET)FALSE为未通过 ALTER TABLE SET STATISTICS 设置列特定目标的表列设定默认统计信息目标值。Postgres 默认值
postgresdefault_text_search_config (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TEXT-SEARCH-CONFIG)FALSE选择文本搜索函数中未显式指定配置实参的变体所使用的文本搜索配置。Postgres 默认值
postgresdefault_transaction_read_only (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TRANSACTION-READ-ONLY)FALSE只读 SQL 事务无法修改非临时表。off
postgreshot_standby_feedback (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK)FALSE指定热备节点是否向主节点或上游备节点反馈当前在备节点上执行的查询信息。日期
postgresidle_in_transaction_session_timeout (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT)FALSE终止任何在开启事务后闲置时间超过指定时长的会话。Postgres 默认值
postgresintervalstyle (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-INTERVALSTYLE)FALSE设置时间间隔值的显示格式。Postgres 默认值
postgresjit (https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-JIT)FALSE启用 JIT 支持。Postgres 默认值
postgreslock_timeout (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT)FALSE中止任何在尝试获取锁时等待时间超过指定时长的语句。Postgres 默认值
postgreslog_autovacuum_min_duration (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-AUTOVACUUM-MIN-DURATION)FALSE如果自动清理操作的执行时间达到或超过指定时长，则记录该操作的日志。Postgres 默认值
postgreslog_connections (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-CONNECTIONS)FALSE在服务器日志中输出详细记录每个成功连接的行。Postgres 默认值
postgreslog_destination (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-DESTINATION)FALSE设置期望的日志输出目标。syslog,stderr
postgreslog_disconnections (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-DISCONNECTIONS)FALSE记录会话终止事件。日志输出提供类似于 log_connections 的信息，并附加会话持续时间。Postgres 默认值
postgreslog_duration (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-DURATION)FALSE记录每个已完成语句的执行时长。Postgres 默认值
postgreslog_line_prefix (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-LINE-PREFIX)FALSE指定一个 printf 风格的字符串，该字符串将输出在每个日志行的开头。[%p][%b][%v][%x] %q[user=%u,db=%d,app=%a]
postgreslog_lock_waits (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-LOCK-WAITS)FALSE控制当会话等待获取锁的时间超过 deadlock_timeout 时是否生成日志消息。日期
postgreslog_min_duration_sample (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-MIN-DURATION-SAMPLE)FALSE允许对执行时间至少达到指定时长的已完成语句进行时长抽样记录。Postgres 默认值
postgreslog_min_duration_statement (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT)FALSE如果语句执行时间达到或超过指定时长，则记录该语句的持续时间。2s
postgreslog_min_messages (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-MIN-MESSAGES)FALSE控制哪些消息级别会被写入服务器日志。notice
postgreslog_rotation_size (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-ROTATION-SIZE)FALSE此参数决定单个日志文件的最大大小。Postgres 默认值
postgreslog_statement (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-STATEMENT)FALSE控制哪些 SQL 语句会被记录。ddl
postgreslog_statement_sample_rate (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-STATEMENT-SAMPLE-RATE)FALSE确定执行时长超过 log_min_duration_sample 的语句中将被记录的比例。Postgres 默认值
postgreslog_temp_files (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-TEMP-FILES)FALSE控制临时文件名和大小的日志记录。10MB
postgreslog_transaction_sample_rate (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-TRANSACTION-SAMPLE-RATE)FALSE设置除了因其他原因被记录的语句外，其所有语句均被记录日志的事务的比例。Postgres 默认值
postgreslogical_decoding_work_mem (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-LOGICAL-DECODING-WORK-MEM)FALSE指定逻辑解码可使用的最大内存量。Postgres 默认值
postgresmaintenance_work_mem (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM)FALSE指定维护操作（如 VACUUM、CREATE INDEX 和 ALTER TABLE ADD FOREIGN KEY）可使用的最大内存量。TOTAL_MEMORY * 0.4
postgresmax_connections (https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS)TRUE确定可并发连接到数据库服务器的最大数量。500
postgresmax_locks_per_transaction (https://www.postgresql.org/docs/current/runtime-config-locks.html#GUC-MAX-LOCKS-PER-TRANSACTION)TRUE控制为每个事务分配的对象锁的平均数量。Postgres 默认值
postgresmax_logical_replication_workers (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-LOGICAL-REPLICATION-WORKERS)TRUE指定逻辑复制工作器的最大数量。Postgres 默认值
postgresmax_parallel_maintenance_workers (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PARALLEL-MAINTENANCE-WORKERS)FALSE设置单个实用程序命令可启动的最大并行工作线程数量。Postgres 默认值
postgresmax_parallel_workers (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS)FALSE设置集群可为并行操作支持的最大工作线程数量。NUM_CPUS
postgresmax_parallel_workers_per_gather (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS-PER-GATHER)FALSE设置单个 Gather 或 Gather Merge 节点可启动的最大工作线程数量。NUM_CPUS
postgresmax_replication_slots (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-REPLICATION-SLOTS)TRUE指定服务器可支持的最大复制槽数量。10
postgresmax_slot_wal_keep_size (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE)FALSESpecifies 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
postgresmax_standby_archive_delay (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-STANDBY-ARCHIVE-DELAY)FALSE决定备用服务器在取消与即将应用的 WAL 条目冲突的备用查询前应等待多长时间。Postgres 默认值
postgresmax_standby_streaming_delay (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY)FALSE决定备用服务器在取消与即将应用的 WAL 条目冲突的备用查询前应等待多长时间。Postgres 默认值
postgresmax_wal_senders (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-WAL-SENDERS)TRUE指定来自备用服务器或流式基础备份客户端的最大并发连接数。10
postgresmax_wal_size (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-MAX-WAL-SIZE)FALSE在自动检查点期间允许 WAL 增长的最大大小。MIN(10GB, STORAGE_GB * 0.1)
postgresmax_worker_processes (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES)TRUE设置集群可支持的后台进程最大数量。100
postgrespg_stat_statements.max (https://www.postgresql.org/docs/current/pgstatstatements.html#PGSTATSTATEMENTS-CONFIG-PARAMS)TRUE跟踪的语句最大数量。Postgres 默认值
postgrespg_stat_statements.track (https://www.postgresql.org/docs/current/pgstatstatements.html#PGSTATSTATEMENTS-CONFIG-PARAMS)FALSE控制应跟踪哪些语句。Postgres 默认值
postgrespg_stat_statements.track_utility (https://www.postgresql.org/docs/current/pgstatstatements.html#PGSTATSTATEMENTS-CONFIG-PARAMS)FALSE是否应跟踪实用程序命令。实用程序命令指除 SELECT、INSERT、UPDATE、DELETE 和 MERGE 之外的所有命令。Postgres 默认值
postgresrandom_page_cost (https://www.postgresql.org/docs/current/runtime-config-query.html)FALSE设置规划器对非顺序读取磁盘页成本的估计值。1.1
postgressession_preload_libraries (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SESSION-PRELOAD-LIBRARIES)FALSE指定一个或多个在连接启动时要预加载的共享库。Postgres 默认值
postgresstatement_timeout (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT)FALSE中止任何执行时间超过指定时长的语句。Postgres 默认值
postgressynchronous_commit (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT)FALSE指定数据库服务器在向客户端返回“成功”指示前，必须完成多少 WAL 处理。local
postgressyslog_split_messages (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-SYSLOG-SPLIT-MESSAGES)FALSE将发送至 syslog 的消息按行拆分并适配到 1024 字节以内Postgres 默认值
postgrestcp_keepalives_count (https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-TCP-KEEPALIVES-COUNT)FALSE指定在认为服务器到客户端的连接失效前，可丢失的 TCP 保持活动消息数量。4
postgrestcp_keepalives_idle (https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-TCP-KEEPALIVES-IDLE)FALSE指定无网络活动多长时间后，操作系统应向客户端发送 TCP 保持活动消息。2
postgrestemp_file_limit (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-TEMP-FILE-LIMIT)FALSE指定单个进程可用于临时文件（如排序和哈希临时文件，或游标持有存储文件）的最大磁盘空间量。MIN(2000GB, STORAGE_GB * 0.25)
postgrestrack_activity_query_size (https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE)TRUE为每个活跃会话保留用于存储当前执行命令文本的内存大小（用于 pg_stat_activity.query 字段）。Postgres 默认值
postgrestrack_commit_timestamp (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-TRACK-COMMIT-TIMESTAMP)TRUE记录事务的提交时间。Postgres 默认值
postgreswal_keep_size (https://postgresqlco.nf/doc/en/param/wal_keep_size/)FALSE指定为流式复制而保留在 pg_wal 目录中的历史 WAL 文件最小大小（以备备用服务器需要获取）。Postgres 默认值
postgreswal_sender_timeout (https://www.postgresql.org/docs/current/runtime-config-replication.html)FALSE设置等待 WAL 复制的最大时间。Postgres 默认值
postgreswork_mem (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM)FALSE设置查询操作（如排序或哈希表）在写入临时磁盘文件前可使用的基础最大内存量。(TOTAL_MEMORY * 0.75)/ (NUM_CORES * 8)