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)

ComponentNameRequires restartDescriptionDefault
pgbouncerautodb_idle_timeout (https://www.pgbouncer.org/config.html)FALSEIf the automatically created (via “*”) database pools have been unused this many seconds, they are freed.3600
pgbouncerdefault_pool_size (https://www.pgbouncer.org/config.html)FALSEHow many server connections to allow per user/database pair.497
pgbouncerignore_startup_parameters (https://www.pgbouncer.org/config.html)FALSEIgnore parameters startup packets (e.g. options,extra_float_digits).client_encoding,datestyle,timezone,standard_conforming_strings,extra_float_digits
pgbouncerlog_connections (https://www.pgbouncer.org/config.html)FALSELog successful logins.0
pgbouncerlog_disconnections (https://www.pgbouncer.org/config.html)FALSELog disconnections with reasons.0
pgbouncerlog_stats (https://www.pgbouncer.org/config.html)FALSEWrite aggregated statistics into the log every stats_period.0
pgbouncermax_prepared_statements (https://www.pgbouncer.org/config.html)FALSENumber of prepared statements kept active on a single server connection250
pgbouncermin_pool_size (https://www.pgbouncer.org/config.html)FALSEAdd more server connections to the pool if below this number.0
pgbouncerpool_mode (https://www.pgbouncer.org/config.html)FALSESpecifies when a server connection can be reused by other clients.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
pgbouncerserver_lifetime (https://www.pgbouncer.org/config.html)FALSECloses an unused server connection (one not linked to a client connection) that has been connected for longer than this many seconds.600
pgbouncertrack_extra_parameters (https://www.pgbouncer.org/config.html)FALSEComma-separated list of additional startup parameters for PgBouncer to track per client and restore on server connect.IntervalStyle,default_transaction_read_only
postgresauto_explain.log_analyze (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-ANALYZE)FALSECauses EXPLAIN ANALYZE output, rather than just EXPLAIN output, to be printed when an execution plan is logged.Postgres default
postgresauto_explain.log_buffers (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-BUFFERS)FALSEControls whether buffer usage statistics are printed when an execution plan is logged.Postgres default
postgresauto_explain.log_format (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-FORMAT)FALSESelects the EXPLAIN output format to be used.Postgres default
postgresauto_explain.log_min_duration (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-MIN-DURATION)FALSEThe minimum statement execution time, in milliseconds, that will cause the statement’s plan to be logged.Postgres default
postgresauto_explain.log_nested_statements (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-NESTED-STATEMENTS)FALSECauses nested statements (statements executed inside a function) to be considered for logging.Postgres default
postgresauto_explain.log_timing (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-TIMING)FALSEControls whether per-node timing information is printed when an execution plan is logged.Postgres default
postgresauto_explain.log_triggers (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-TRIGGERS)FALSECauses trigger execution statistics to be included when an execution plan is logged.Postgres default
postgresauto_explain.log_verbose (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-LOG-VERBOSE)FALSEControls whether verbose details are printed when an execution plan is logged.Postgres default
postgresauto_explain.sample_rate (https://www.postgresql.org/docs/current/auto-explain.html#AUTO-EXPLAIN-CONFIGURATION-PARAMETERS-SAMPLE-RATE)FALSECauses auto_explain to only explain a fraction of the statements in each session.Postgres default
postgresautovacuum_analyze_scale_factor (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR)FALSESpecifies a fraction of the table size to add to autovacuum_analyze_threshold when deciding whether to trigger an ANALYZE.Postgres default
postgresautovacuum_freeze_max_age (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE)TRUESpecifies 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
postgresautovacuum_max_workers (https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-MAX-WORKERS)TRUESpecifies the maximum number of autovacuum processes (other than the autovacuum launcher) that may be running at any one time.Postgres default
postgresautovacuum_vacuum_cost_delay (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY)FALSESpecifies 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
postgresautovacuum_vacuum_cost_limit (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-LIMIT)FALSESpecifies the cost limit value that will be used in automatic VACUUM operations.Postgres default
postgresautovacuum_vacuum_insert_scale_factor (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-SCALE-FACTOR)FALSESpecifies a fraction of the table size to add to autovacuum_vacuum_insert_threshold when deciding whether to trigger a VACUUM.Postgres default
postgresautovacuum_vacuum_insert_threshold (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-THRESHOLD)FALSESpecifies the number of inserted tuples needed to trigger a VACUUM in any one table.Postgres default
postgresautovacuum_vacuum_scale_factor (https://www.postgresql.org/docs/17/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR)FALSESpecifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM.Postgres default
postgrescheckpoint_completion_target (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-CHECKPOINT-COMPLETION-TARGET)FALSESpecifies the target of checkpoint completion, as a fraction of total time between checkpoints.Postgres default
postgrescheckpoint_timeout (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-CHECKPOINT-TIMEOUT)FALSEMaximum time between automatic WAL checkpoints.Postgres default
postgrescheckpoint_warning (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-CHECKPOINT-WARNING)FALSEWrite 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
postgrescron.log_min_messages (https://github.com/citusdata/pg_cron)FALSEControls which message levels are written to the pg_cron background worker log.Postgres default
postgrescron.max_running_jobs (https://github.com/citusdata/pg_cron)TRUESets the maximum number of concurrently running pg_cron jobs.5
postgrescron.timezone (https://github.com/citusdata/pg_cron)TRUESets the time zone used by the pg_cron background worker for scheduling jobs.Postgres default
postgresdefault_statistics_target (https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET)FALSESets the default statistics target for table columns without a column-specific target set via ALTER TABLE SET STATISTICS.Postgres default
postgresdefault_text_search_config (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TEXT-SEARCH-CONFIG)FALSESelects 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
postgresdefault_toast_compression (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION)FALSESets the default TOAST compression method for values of compressible columns.Postgres default
postgresdefault_transaction_read_only (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TRANSACTION-READ-ONLY)FALSEA read-only SQL transaction cannot alter non-temporary tables.off
postgreshot_standby_feedback (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK)FALSESpecifies whether or not a hot standby will send feedback to the primary or upstream standby about queries currently executing on the standby.on
postgresidle_in_transaction_session_timeout (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT)FALSETerminate any session that has been idle within an open transaction for longer than the specified amount of time.Postgres default
postgresintervalstyle (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-INTERVALSTYLE)FALSESets the display format for interval value.Postgres default
postgresjit (https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-JIT)FALSEEnable JIT support.Postgres default
postgreslock_timeout (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT)FALSEAbort any statement that waits longer than the specified amount of time while attempting to acquire a lock.Postgres default
postgreslog_autovacuum_min_duration (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-AUTOVACUUM-MIN-DURATION)FALSECauses each action executed by autovacuum to be logged if it ran for at least the specified amount of time.Postgres default
postgreslog_connections (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-CONNECTIONS)FALSEOutputs a line to the server logs detailing each successful connection.Postgres default
postgreslog_disconnections (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-DISCONNECTIONS)FALSECauses session terminations to be logged. The log output provides information similar to log_connections, plus the duration of the session.Postgres default
postgreslog_duration (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-DURATION)FALSECauses the duration of every completed statement to be logged.Postgres default
postgreslog_lock_waits (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-LOCK-WAITS)FALSEControls whether a log message is produced when a session waits longer than deadlock_timeout to acquire a lock.on
postgreslog_min_duration_sample (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-MIN-DURATION-SAMPLE)FALSEAllows sampling the duration of completed statements that ran for at least the specified amount of time.Postgres default
postgreslog_min_duration_statement (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT)FALSECauses the duration of each completed statement to be logged if the statement ran for at least the specified amount of time.2s
postgreslog_min_messages (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-MIN-MESSAGES)FALSEControls which message levels are written to the server log.notice
postgreslog_rotation_size (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-ROTATION-SIZE)FALSEThis determines the maximum size of an individual log file.Postgres default
postgreslog_statement (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-STATEMENT)FALSEControls which SQL statements are logged.ddl
postgreslog_statement_sample_rate (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-STATEMENT-SAMPLE-RATE)FALSEDetermines the fraction of statements with duration exceeding log_min_duration_sample that will be logged.Postgres default
postgreslog_temp_files (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-TEMP-FILES)FALSEControls logging of temporary file names and sizes.10MB
postgreslog_transaction_sample_rate (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-TRANSACTION-SAMPLE-RATE)FALSESets the fraction of transactions whose statements are all logged, in addition to statements logged for other reasons.Postgres default
postgreslogical_decoding_work_mem (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-LOGICAL-DECODING-WORK-MEM)FALSESpecifies the maximum amount of memory to be used by logical decoding.Postgres default
postgresmaintenance_work_mem (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM)FALSESpecifies 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
postgresmax_connections (https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS)TRUEDetermines the maximum number of concurrent connections to the database server.500
postgresmax_files_per_process (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-FILES-PER-PROCESS)TRUESets the maximum number of simultaneously open files allowed to each server subprocess.10000
postgresmax_locks_per_transaction (https://www.postgresql.org/docs/current/runtime-config-locks.html#GUC-MAX-LOCKS-PER-TRANSACTION)TRUEControls the average number of object locks allocated for each transaction.Postgres default
postgresmax_logical_replication_workers (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-LOGICAL-REPLICATION-WORKERS)TRUESpecifies maximum number of logical replication workers.Postgres default
postgresmax_parallel_maintenance_workers (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PARALLEL-MAINTENANCE-WORKERS)FALSESets the maximum number of parallel workers that can be started by a single utility command.Postgres default
postgresmax_parallel_workers (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS)FALSESets the maximum number of workers that the cluster can support for parallel operations.NUM_CPUS
postgresmax_parallel_workers_per_gather (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS-PER-GATHER)FALSESets the maximum number of workers that can be started by a single Gather or Gather Merge node.NUM_CPUS
postgresmax_replication_slots (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-REPLICATION-SLOTS)TRUESpecifies the maximum number of replication slots that the server can support.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)FALSEDetermines how long the standby server should wait before canceling standby queries that conflict with about-to-be-applied WAL entries.Postgres default
postgresmax_standby_streaming_delay (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY)FALSEDetermines how long the standby server should wait before canceling standby queries that conflict with about-to-be-applied WAL entries.Postgres default
postgresmax_wal_senders (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-WAL-SENDERS)TRUESpecifies the maximum number of concurrent connections from standby servers or streaming base backup clients.10
postgresmax_wal_size (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-MAX-WAL-SIZE)FALSEMaximum size to let the WAL grow during automatic checkpoints.MIN(10GB, STORAGE_GB * 0.1)
postgresmax_worker_processes (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES)TRUESets the maximum number of background processes that the cluster can support.100
postgrespg_lake_iceberg.default_location_prefixFALSEThe object-store prefix used for new Iceberg tables.Postgres default
postgrespg_stat_statements.max (https://www.postgresql.org/docs/current/pgstatstatements.html#PGSTATSTATEMENTS-CONFIG-PARAMS)TRUEMaximum number of statements tracked.Postgres default
postgrespg_stat_statements.track (https://www.postgresql.org/docs/current/pgstatstatements.html#PGSTATSTATEMENTS-CONFIG-PARAMS)FALSEControl which statements should be tracked.Postgres default
postgrespg_stat_statements.track_utility (https://www.postgresql.org/docs/current/pgstatstatements.html#PGSTATSTATEMENTS-CONFIG-PARAMS)FALSEShould the utility commands be tracked. Utility commands are all those other than SELECT, INSERT, UPDATE, DELETE, and MERGE.Postgres default
postgrespgaudit.log (https://github.com/pgaudit/pgaudit#settings)FALSESpecifies which classes of statements are logged by session audit logging.Postgres default
postgrespgaudit.log_relation (https://github.com/pgaudit/pgaudit#settings)FALSESpecifies whether session audit logging creates a separate log entry for each relation referenced in a SELECT or DML statement.Postgres default
postgresrandom_page_cost (https://www.postgresql.org/docs/current/runtime-config-query.html)FALSESets the planner’s estimate of the cost of a non-sequentially-fetched disk page.1.1
postgressession_preload_libraries (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SESSION-PRELOAD-LIBRARIES)FALSESpecifies one or more shared libraries that are to be preloaded at connection start.Postgres default
postgresstatement_timeout (https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT)FALSEAbort any statement that takes more than the specified amount of time.Postgres default
postgresstrict.synchronous_commitFALSEWhen 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
postgressynchronous_commit (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT)FALSESpecifies how much WAL processing must complete before the database server returns a “success” indication to the client. See also Synchronous commit and durability.local
postgressyslog_split_messages (https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-SYSLOG-SPLIT-MESSAGES)FALSESplit messages sent to syslog by lines and to fit into 1024 bytesPostgres default
postgrestcp_keepalives_count (https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-TCP-KEEPALIVES-COUNT)FALSESpecifies the number of TCP keepalive messages that can be lost before the server’s connection to the client is considered dead.4
postgrestcp_keepalives_idle (https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-TCP-KEEPALIVES-IDLE)FALSESpecifies the amount of time with no network activity after which the operating system should send a TCP keepalive message to the client.2
postgrestcp_keepalives_interval (https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-TCP-KEEPALIVES-INTERVAL)FALSESpecifies the amount of time after which an unacknowledged TCP keepalive message to the client should be retransmitted.2
postgrestemp_file_limit (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-TEMP-FILE-LIMIT)FALSESpecifies 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)
postgrestrack_activity_query_size (https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE)TRUEMemory reserved to store the text of the currently executing command for each active session, for the pg_stat_activity.query field.Postgres default
postgrestrack_commit_timestamp (https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-TRACK-COMMIT-TIMESTAMP)TRUERecord commit time of transactions.Postgres default
postgreswal_compression (https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-COMPRESSION)FALSEEnables compression of full page images written to WAL using the specified method.Postgres default
postgreswal_keep_size (https://postgresqlco.nf/doc/en/param/wal_keep_size/)FALSESpecifies 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
postgreswal_sender_timeout (https://www.postgresql.org/docs/current/runtime-config-replication.html)FALSESets the maximum time to wait for WAL replication.Postgres default
postgreswork_mem (https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM)FALSESets 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_commit is on, remote_write, or remote_apply. With the default of local, the setting has no effect.
  • strict.synchronous_commit is on.

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:

ALTER POSTGRES INSTANCE production_instance
  SET POSTGRES_SETTINGS = '{
    "postgres:synchronous_commit": "on",
    "postgres:strict.synchronous_commit": "on"
  }';

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:

BEGIN;
SET LOCAL synchronous_commit = local;
-- writes that don't need standby acknowledgment
COMMIT;

To turn it off, set "postgres:strict.synchronous_commit": "off". For more about changing settings, see Custom configuration parameters.