Snowflake Postgres Server Settings¶
The following table details the parameters that can be set for the Postgres server and PgBouncer components of Snowflake Postgres instances, including settings for supported extensions. Each setting’s name is hyperlinked to its Postgres, PgBouncer, or extension documentation. Snowflake-specific settings link to Snowflake Postgres documentation.
Where “Postgres default” appears in the Default column, Snowflake Postgres instances use the default value from Postgres, or from the extension for extension settings. 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)
becomes this to visit its Postgres 17 documentation:
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)
| Component | Name | Requires restart | Description | Default |
|---|---|---|---|---|
| pgbouncer | autodb_idle_timeout (https://www.pgbouncer.org/config.html) | FALSE | If the automatically created (via “*”) database pools have been unused this many seconds, they are freed. | 3600 |
| pgbouncer | default_pool_size (https://www.pgbouncer.org/config.html) | FALSE | How many server connections to allow per user/database pair. | 497 |
| pgbouncer | ignore_startup_parameters (https://www.pgbouncer.org/config.html) | FALSE | Ignore parameters startup packets (e.g. options,extra_float_digits). | client_encoding,datestyle,timezone,standard_conforming_strings,extra_float_digits |
| pgbouncer | log_connections (https://www.pgbouncer.org/config.html) | FALSE | Log successful logins. | 0 |
| pgbouncer | log_disconnections (https://www.pgbouncer.org/config.html) | FALSE | Log disconnections with reasons. | 0 |
| pgbouncer | log_stats (https://www.pgbouncer.org/config.html) | FALSE | Write aggregated statistics into the log every stats_period. | 0 |
| pgbouncer | max_prepared_statements (https://www.pgbouncer.org/config.html) | FALSE | Number of prepared statements kept active on a single server connection | 250 |
| pgbouncer | min_pool_size (https://www.pgbouncer.org/config.html) | FALSE | Add more server connections to the pool if below this number. | 0 |
| pgbouncer | pool_mode (https://www.pgbouncer.org/config.html) | FALSE | Specifies when a server connection can be reused by other clients. | transaction |
| pgbouncer | server_idle_timeout (https://www.pgbouncer.org/config.html) | FALSE | If a server connection has been idle for more than this many seconds, it will be closed. | 60 |
| pgbouncer | server_lifetime (https://www.pgbouncer.org/config.html) | FALSE | Closes an unused server connection (one not linked to a client connection) that has been connected for longer than this many seconds. | 600 |
| pgbouncer | track_extra_parameters (https://www.pgbouncer.org/config.html) | FALSE | Comma-separated list of additional startup parameters for PgBouncer to track per client and restore on server connect. | IntervalStyle,default_transaction_read_only |
| postgres | auto_explain.log_analyze (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-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 (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-BUFFERS) | FALSE | Controls whether buffer usage statistics are printed when an execution plan is logged. | Postgres default |
| postgres | auto_explain.log_format (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-FORMAT) | FALSE | Selects the EXPLAIN output format to be used. | Postgres default |
| postgres | auto_explain.log_min_duration (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-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 (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-NESTED-STATEMENTS) | FALSE | Causes nested statements (statements executed inside a function) to be considered for logging. | Postgres default |
| postgres | auto_explain.log_timing (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-TIMING) | FALSE | Controls whether per-node timing information is printed when an execution plan is logged. | Postgres default |
| postgres | auto_explain.log_triggers (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-TRIGGERS) | FALSE | Causes trigger execution statistics to be included when an execution plan is logged. | Postgres default |
| postgres | auto_explain.log_verbose (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-VERBOSE) | FALSE | Controls whether verbose details are printed when an execution plan is logged. | Postgres default |
| postgres | auto_explain.sample_rate (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-SAMPLE-RATE) | FALSE | Causes auto_explain to only explain a fraction of the statements in each session. | Postgres default |
| postgres | autovacuum_analyze_scale_factor (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-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 (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-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_max_workers (https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-MAX-WORKERS) | TRUE | Specifies the maximum number of autovacuum processes (other than the autovacuum launcher) that may be running at any one time. | Postgres default |
| postgres | autovacuum_vacuum_cost_delay (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-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 (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-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 (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-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 (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-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 (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-CHECKPOINT-COMPLETION-TARGET) | FALSE | Specifies the target of checkpoint completion, as a fraction of total time between checkpoints. | Postgres default |
| postgres | checkpoint_timeout (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-CHECKPOINT-TIMEOUT) | FALSE | Maximum time between automatic WAL checkpoints. | Postgres default |
| postgres | checkpoint_warning (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-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 | cron.log_min_messages (https://github.com/citusdata/pg_cron) | FALSE | Controls which message levels are written to the pg_cron background worker log. | Postgres default |
| postgres | cron.max_running_jobs (https://github.com/citusdata/pg_cron) | TRUE | Sets the maximum number of concurrently running pg_cron jobs. | 5 |
| postgres | cron.timezone (https://github.com/citusdata/pg_cron) | TRUE | Sets the time zone used by the pg_cron background worker for scheduling jobs. | Postgres default |
| postgres | default_statistics_target (https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-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_toast_compression (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION) | FALSE | Sets the default TOAST compression method for values of compressible columns. | Postgres default |
| postgres | default_transaction_read_only (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TRANSACTION-READ-ONLY) | FALSE | A read-only SQL transaction cannot alter non-temporary tables. | off |
| postgres | hot_standby_feedback (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-INTERVALSTYLE) | FALSE | Sets the display format for interval value. | Postgres default |
| postgres | jit (https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-JIT) | FALSE | Enable JIT support. | Postgres default |
| postgres | lock_timeout (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-CONNECTIONS) | FALSE | Outputs a line to the server logs detailing each successful connection. | Postgres default |
| postgres | log_disconnections (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-DURATION) | FALSE | Causes the duration of every completed statement to be logged. | Postgres default |
| postgres | log_lock_waits (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-MIN-MESSAGES) | FALSE | Controls which message levels are written to the server log. | notice |
| postgres | log_rotation_size (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-ROTATION-SIZE) | FALSE | This determines the maximum size of an individual log file. | Postgres default |
| postgres | log_statement (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-STATEMENT) | FALSE | Controls which SQL statements are logged. | ddl |
| postgres | log_statement_sample_rate (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-TEMP-FILES) | FALSE | Controls logging of temporary file names and sizes. | 10MB |
| postgres | log_transaction_sample_rate (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-LOGICAL-DECODING-WORK-MEM) | FALSE | Specifies the maximum amount of memory to be used by logical decoding. | Postgres default |
| postgres | maintenance_work_mem (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-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.04 |
| postgres | max_connections (https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS) | TRUE | Determines the maximum number of concurrent connections to the database server. | 500 |
| postgres | max_files_per_process (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-FILES-PER-PROCESS) | TRUE | Sets the maximum number of simultaneously open files allowed to each server subprocess. | 10000 |
| postgres | max_locks_per_transaction (https://www.postgresql.org/docs/current/runtime-config-locks.html#GUC-MAX-LOCKS-PER-TRANSACTION) | TRUE | Controls the average number of object locks allocated for each transaction. | Postgres default |
| postgres | max_logical_replication_workers (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-LOGICAL-REPLICATION-WORKERS) | TRUE | Specifies maximum number of logical replication workers. | Postgres default |
| postgres | max_parallel_maintenance_workers (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-REPLICATION-SLOTS) | TRUE | Specifies the maximum number of replication slots that the server can support. | 10 |
| postgres | max_slot_wal_keep_size (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-WAL-SENDERS) | TRUE | Specifies the maximum number of concurrent connections from standby servers or streaming base backup clients. | 10 |
| postgres | max_wal_size (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-MAX-WAL-SIZE) | FALSE | Maximum size to let the WAL grow during automatic checkpoints. | MIN(10GB, STORAGE_GB * 0.1) |
| postgres | max_worker_processes (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES) | TRUE | Sets the maximum number of background processes that the cluster can support. | 100 |
| postgres | pg_lake_iceberg.default_location_prefix | FALSE | The object-store prefix used for new Iceberg tables. | Postgres default |
| postgres | pg_stat_statements.max (https://www.postgresql.org/docs/current/pgstatstatements.html#PGSTATSTATEMENTS-CONFIG-PARAMS) | TRUE | Maximum number of statements tracked. | Postgres default |
| postgres | pg_stat_statements.track (https://www.postgresql.org/docs/current/pgstatstatements.html#PGSTATSTATEMENTS-CONFIG-PARAMS) | FALSE | Control which statements should be tracked. | Postgres default |
| postgres | pg_stat_statements.track_utility (https://www.postgresql.org/docs/current/pgstatstatements.html#PGSTATSTATEMENTS-CONFIG-PARAMS) | FALSE | Should the utility commands be tracked. Utility commands are all those other than SELECT, INSERT, UPDATE, DELETE, and MERGE. | Postgres default |
| postgres | pgaudit.log (https://github.com/pgaudit/pgaudit#settings) | FALSE | Specifies which classes of statements are logged by session audit logging. | Postgres default |
| postgres | pgaudit.log_relation (https://github.com/pgaudit/pgaudit#settings) | FALSE | Specifies whether session audit logging creates a separate log entry for each relation referenced in a SELECT or DML statement. | Postgres default |
| postgres | random_page_cost (https://www.postgresql.org/docs/current/runtime-config-query.html) | FALSE | Sets the planner’s estimate of the cost of a non-sequentially-fetched disk page. | 1.1 |
| postgres | session_preload_libraries (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SESSION-PRELOAD-LIBRARIES) | FALSE | Specifies one or more shared libraries that are to be preloaded at connection start. | Postgres default |
| postgres | statement_timeout (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT) | FALSE | Abort any statement that takes more than the specified amount of time. | Postgres default |
| postgres | strict.synchronous_commit | FALSE | When HA is enabled and synchronous_commit is set to a remote mode (on, remote_write, or remote_apply), keeps synchronous commit armed across failover and HA replacement, so commits do not return success from the primary alone while no standby can acknowledge them. | off |
| postgres | synchronous_commit (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT) | FALSE | Specifies how much WAL processing must complete before the database server returns a “success” indication to the client. See also Synchronous commit and durability. | local |
| postgres | syslog_split_messages (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-SYSLOG-SPLIT-MESSAGES) | FALSE | Split messages sent to syslog by lines and to fit into 1024 bytes | Postgres default |
| postgres | tcp_keepalives_count (https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-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 | tcp_keepalives_interval (https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-TCP-KEEPALIVES-INTERVAL) | FALSE | Specifies the amount of time after which an unacknowledged TCP keepalive message to the client should be retransmitted. | 2 |
| postgres | temp_file_limit (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-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 (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-TRACK-COMMIT-TIMESTAMP) | TRUE | Record commit time of transactions. | Postgres default |
| postgres | wal_compression (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-COMPRESSION) | FALSE | Enables compression of full page images written to WAL using the specified method. | Postgres default |
| postgres | wal_keep_size (https://postgresqlco.nf/doc/en/param/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 (https://www.postgresql.org/docs/current/runtime-config-replication.html) | FALSE | Sets the maximum time to wait for WAL replication. | Postgres default |
| postgres | work_mem (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-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) |
Snowflake-specific settings¶
These settings aren’t part of upstream Postgres. Snowflake Postgres adds them to control platform behavior.
Strict synchronous commit¶
When you use a remote synchronous_commit
mode (on, remote_write, or remote_apply), each commit waits until the HA standby has received, flushed, or applied it, depending on
the mode. Sometimes no standby is available, for example right after an HA failover or while Snowflake replaces the standby during
maintenance such as a resize. By default, Snowflake Postgres prioritizes availability during those windows: commits succeed from
the primary alone, and those commits are durable only on the primary until a standby is available again. Most workloads should keep this default.
Turn on strict.synchronous_commit when you want commits to succeed only after a standby meets the condition your synchronous_commit
mode requires. If no standby is available, commits block until a standby is available and meets that condition. Strict mode also changes
how Snowflake handles planned maintenance that replaces the HA standby, such as a compute or storage resize. With strict mode on, Snowflake
keeps the old standby in place until the new one has caught up to ensure that no transactions are lost should a failover occur during or
shortly after the HA switchover.
The setting takes effect only when all of the following are true:
- High availability is enabled (
HIGH_AVAILABILITY = TRUE). synchronous_commitison,remote_write, orremote_apply. With the default oflocal, the setting has no effect.strict.synchronous_commitison.
Warning
This setting prioritizes durability over write availability and can lead to prolonged periods in which the instance is read-only. After a failover, commits block until a replacement standby is streaming. Catching up a replacement standby on a large database can take a long time. If the cloud provider can’t provision the replacement standby, commits remain blocked until capacity becomes available.
The following example turns on remote synchronous commit with strict handling. Neither setting requires a restart:
synchronous_commit can also be changed for a single session with SET or a single transaction with SET LOCAL. Commits
that run with local or off don’t wait for a standby, even when strict.synchronous_commit is on. You can use this to keep
less critical writes durable on the primary only, while important commits still wait:
To turn it off, set "postgres:strict.synchronous_commit": "off". For more about changing settings, see
Custom configuration parameters.