RDS for PostgreSQL DB instance parameter list

Working with parameters on your RDS for PostgreSQL DB instance

In some cases, you might create an RDS for PostgreSQL DB instance without specifying a custom parameter group. If so, your DB instance is created using the default parameter group for the version of PostgreSQL that you choose. For example, suppose that you create an RDS for PostgreSQL DB instance using PostgreSQL 13.3. In this case, the DB instance is created using the values in the parameter group for PostgreSQL 13 releases, default.postgres13.

You can also create your own custom DB parameter group. You need to do this if you want to modify any settings for the RDS for PostgreSQL DB instance from their default values. To learn how, see Parameter groups for Amazon RDS.

You can track the settings on your RDS for PostgreSQL DB instance in several different ways. You can use the AWS Management Console, the AWS CLI, or the Amazon RDS API. You can also query the values from the PostgreSQL pg_settings table of your instance, as shown following.


SELECT name, setting, boot_val, reset_val, unit
 FROM pg_settings
 ORDER BY name;

To learn more about the values returned from this query, see pg_settings in the PostgreSQL documentation.

Be especially careful when changing the settings for max_connections and shared_buffers on your RDS for PostgreSQL DB instance. For example, suppose that you modify settings for max_connections or shared_buffers and you use values that are too high for your actual workload. In this case, your RDS for PostgreSQL DB instance won't start. If this happens, you see an error such as the following in the postgres.log.


2018-09-18 21:13:15 UTC::@:[8097]:FATAL:  could not map anonymous shared memory: Cannot allocate memory
2018-09-18 21:13:15 UTC::@:[8097]:HINT:  This error usually means that PostgreSQL's request for a shared memory segment
exceeded available memory or swap space. To reduce the request size (currently 3514134274048 bytes), reduce 
PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.

However, you can't change any values of the settings contained in the default RDS for PostgreSQL DB parameter groups. To change settings for any parameters, first create a custom DB parameter group. Then change the settings in that custom group, and then apply the custom parameter group to your RDS for PostgreSQL DB instance. To learn more, see Parameter groups for Amazon RDS.

There are two types of parameters in RDS for PostgreSQL.

Static parameters – Static parameters require that the RDS for PostgreSQL DB instance be rebooted after a change so that the new value can take effect.
Dynamic parameters – Dynamic parameters don't require a reboot after changing their settings.

Note

If your RDS for PostgreSQL DB instance is using your own custom DB parameter group, you can change the values of dynamic parameters on the running DB instance. You can do this by using the AWS Management Console, the AWS CLI, or the Amazon RDS API.

If you have privileges to do so, you can also change parameter values by using the ALTER DATABASE, ALTER ROLE, and SET commands.

RDS for PostgreSQL DB instance parameter list

The following table lists some (but not all) parameters available in an RDS for PostgreSQL DB instance. To view all available parameters, you use the describe-db-parameters AWS CLI command. For example, to get the list of all parameters available in the default parameter group for RDS for PostgreSQL version 13, run the following.


aws rds describe-db-parameters --db-parameter-group-name default.postgres13

You can also use the Console. Choose Parameter groups from the Amazon RDS menu, and then choose the parameter group from those available in your AWS Region.

Parameter name	Apply_Type	Description
`application_name`	Dynamic	Sets the application name to be reported in statistics and logs.
`archive_command`	Dynamic	Sets the shell command that will be called to archive a WAL file.
`array_nulls`	Dynamic	Enables input of NULL elements in arrays.
`authentication_timeout`	Dynamic	Sets the maximum allowed time to complete client authentication.
`autovacuum`	Dynamic	Starts the autovacuum subprocess.
`autovacuum_analyze_scale_factor`	Dynamic	Number of tuple inserts, updates, or deletes before analyze as a fraction of reltuples.
`autovacuum_analyze_threshold`	Dynamic	Minimum number of tuple inserts, updates, or deletes before analyze.
`autovacuum_freeze_max_age`	Static	Age at which to autovacuum a table to prevent transaction ID wraparound.
`autovacuum_naptime`	Dynamic	Time to sleep between autovacuum runs.
`autovacuum_max_workers`	Static	Sets the maximum number of simultaneously running autovacuum worker processes.
`autovacuum_vacuum_cost_delay`	Dynamic	Vacuum cost delay, in milliseconds, for autovacuum.
`autovacuum_vacuum_cost_limit`	Dynamic	Vacuum cost amount available before napping, for autovacuum.
`autovacuum_vacuum_scale_factor`	Dynamic	Number of tuple updates or deletes before vacuum as a fraction of reltuples.
`autovacuum_vacuum_threshold`	Dynamic	Minimum number of tuple updates or deletes before vacuum.
`backslash_quote`	Dynamic	Sets whether a backslash (\) is allowed in string literals.
`bgwriter_delay`	Dynamic	Background writer sleep time between rounds.
`bgwriter_lru_maxpages`	Dynamic	Background writer maximum number of LRU pages to flush per round.
`bgwriter_lru_multiplier`	Dynamic	Multiple of the average buffer usage to free per round.
`bytea_output`	Dynamic	Sets the output format for bytes.
`check_function_bodies`	Dynamic	Checks function bodies during CREATE FUNCTION.
`checkpoint_completion_target`	Dynamic	Time spent flushing dirty buffers during checkpoint, as a fraction of the checkpoint interval.
`checkpoint_segments`	Dynamic	Sets the maximum distance in log segments between automatic write-ahead log (WAL) checkpoints.
`checkpoint_timeout`	Dynamic	Sets the maximum time between automatic WAL checkpoints.
`checkpoint_warning`	Dynamic	Enables warnings if checkpoint segments are filled more frequently than this.
`client_connection_check_interval`	Dynamic	Sets the time interval between checks for disconnection while running queries.
`client_encoding`	Dynamic	Sets the client's character set encoding.
`client_min_messages`	Dynamic	Sets the message levels that are sent to the client.
`commit_delay`	Dynamic	Sets the delay in microseconds between transaction commit and flushing WAL to disk.
`commit_siblings`	Dynamic	Sets the minimum concurrent open transactions before performing commit_delay.
`constraint_exclusion`	Dynamic	Enables the planner to use constraints to optimize queries.
`cpu_index_tuple_cost`	Dynamic	Sets the planner's estimate of the cost of processing each index entry during an index scan.
`cpu_operator_cost`	Dynamic	Sets the planner's estimate of the cost of processing each operator or function call.
`cpu_tuple_cost`	Dynamic	Sets the planner's estimate of the cost of processing each tuple (row).
`cursor_tuple_fraction`	Dynamic	Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.
`datestyle`	Dynamic	Sets the display format for date and time values.
`deadlock_timeout`	Dynamic	Sets the time to wait on a lock before checking for deadlock.
`debug_pretty_print`	Dynamic	Indents parse and plan tree displays.
`debug_print_parse`	Dynamic	Logs each query's parse tree.
`debug_print_plan`	Dynamic	Logs each query's execution plan.
`debug_print_rewritten`	Dynamic	Logs each query's rewritten parse tree.
`default_statistics_target`	Dynamic	Sets the default statistics target.
`default_tablespace`	Dynamic	Sets the default tablespace to create tables and indexes in.
`default_transaction_deferrable`	Dynamic	Sets the default deferrable status of new transactions.
`default_transaction_isolation`	Dynamic	Sets the transaction isolation level of each new transaction.
`default_transaction_read_only`	Dynamic	Sets the default read-only status of new transactions.
`default_with_oids`	Dynamic	Creates new tables with object IDs (OIDs) by default.
`effective_cache_size`	Dynamic	Sets the planner's assumption about the size of the disk cache.
`effective_io_concurrency`	Dynamic	Number of simultaneous requests that can be handled efficiently by the disk subsystem.
`enable_bitmapscan`	Dynamic	Enables the planner's use of bitmap-scan plans.
`enable_hashagg`	Dynamic	Enables the planner's use of hashed aggregation plans.
`enable_hashjoin`	Dynamic	Enables the planner's use of hash join plans.
`enable_indexscan`	Dynamic	Enables the planner's use of index-scan plans.
`enable_material`	Dynamic	Enables the planner's use of materialization.
`enable_mergejoin`	Dynamic	Enables the planner's use of merge join plans.
`enable_nestloop`	Dynamic	Enables the planner's use of nested-loop join plans.
`enable_seqscan`	Dynamic	Enables the planner's use of sequential-scan plans.
`enable_sort`	Dynamic	Enables the planner's use of explicit sort steps.
`enable_tidscan`	Dynamic	Enables the planner's use of TID scan plans.
`escape_string_warning`	Dynamic	Warns about backslash (\) escapes in ordinary string literals.
`extra_float_digits`	Dynamic	Sets the number of digits displayed for floating-point values.
`from_collapse_limit`	Dynamic	Sets the FROM-list size beyond which subqueries are not collapsed.
`fsync`	Dynamic	Forces synchronization of updates to disk.
`full_page_writes`	Dynamic	Writes full pages to WAL when first modified after a checkpoint.
`geqo`	Dynamic	Enables genetic query optimization.
`geqo_effort`	Dynamic	GEQO: effort is used to set the default for other GEQO parameters.
`geqo_generations`	Dynamic	GEQO: number of iterations of the algorithm.
`geqo_pool_size`	Dynamic	GEQO: number of individuals in the population.
`geqo_seed`	Dynamic	GEQO: seed for random path selection.
`geqo_selection_bias`	Dynamic	GEQO: selective pressure within the population.
`geqo_threshold`	Dynamic	Sets the threshold of FROM items beyond which GEQO is used.
`gin_fuzzy_search_limit`	Dynamic	Sets the maximum allowed result for exact search by GIN.
`hot_standby_feedback`	Dynamic	Determines whether a hot standby sends feedback messages to the primary or upstream standby.
`intervalstyle`	Dynamic	Sets the display format for interval values.
`join_collapse_limit`	Dynamic	Sets the FROM-list size beyond which JOIN constructs are not flattened.
`lc_messages`	Dynamic	Sets the language in which messages are displayed.
`lc_monetary`	Dynamic	Sets the locale for formatting monetary amounts.
`lc_numeric`	Dynamic	Sets the locale for formatting numbers.
`lc_time`	Dynamic	Sets the locale for formatting date and time values.
`log_autovacuum_min_duration`	Dynamic	Sets the minimum running time above which autovacuum actions will be logged.
`log_checkpoints`	Dynamic	Logs each checkpoint.
`log_connections`	Dynamic	Logs each successful connection.
`log_disconnections`	Dynamic	Logs end of a session, including duration.
`log_duration`	Dynamic	Logs the duration of each completed SQL statement.
`log_error_verbosity`	Dynamic	Sets the verbosity of logged messages.
`log_executor_stats`	Dynamic	Writes executor performance statistics to the server log.
`log_filename`	Dynamic	Sets the file name pattern for log files.
`log_file_mode`	Dynamic	Sets file permissions for log files. Default value is 0644.
`log_hostname`	Dynamic	Logs the host name in the connection logs. As of PostgreSQL 12 and later versions, this parameter is 'off' by default. When turned on, the connection uses DNS reverse-lookup to get the hostname that gets captured to the connection logs. If you turn on this parameter, you should monitor the impact that it has on the time it takes to establish connections.
`log_line_prefix`	Dynamic	Controls information prefixed to each log line.
`log_lock_waits`	Dynamic	Logs long lock waits.
`log_min_duration_statement`	Dynamic	Sets the minimum running time above which statements will be logged.
`log_min_error_statement`	Dynamic	Causes all statements generating an error at or above this level to be logged.
`log_min_messages`	Dynamic	Sets the message levels that are logged.
`log_parser_stats`	Dynamic	Writes parser performance statistics to the server log.
`log_planner_stats`	Dynamic	Writes planner performance statistics to the server log.
`log_rotation_age`	Dynamic	Automatic log file rotation will occur after N minutes.
`log_rotation_size`	Dynamic	Automatic log file rotation will occur after N kilobytes.
`log_statement`	Dynamic	Sets the type of statements logged.
`log_statement_stats`	Dynamic	Writes cumulative performance statistics to the server log.
`log_temp_files`	Dynamic	Logs the use of temporary files larger than this number of kilobytes.
`log_timezone`	Dynamic	Sets the time zone to use in log messages.
`log_truncate_on_rotation`	Dynamic	Truncate existing log files of same name during log rotation.
`logging_collector`	Static	Start a subprocess to capture stderr output and/or csvlogs into log files.
`maintenance_work_mem`	Dynamic	Sets the maximum memory to be used for maintenance operations.
`max_connections`	Static	Sets the maximum number of concurrent connections.
`max_files_per_process`	Static	Sets the maximum number of simultaneously open files for each server process.
`max_locks_per_transaction`	Static	Sets the maximum number of locks per transaction.
`max_pred_locks_per_transaction`	Static	Sets the maximum number of predicate locks per transaction.
`max_prepared_transactions`	Static	Sets the maximum number of simultaneously prepared transactions.
`max_stack_depth`	Dynamic	Sets the maximum stack depth, in kilobytes.
`max_standby_archive_delay`	Dynamic	Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data.
`max_standby_streaming_delay`	Dynamic	Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data.
`max_wal_size`	Dynamic	Sets the WAL size (MB) that triggers a checkpoint. For all versions after RDS for PostgreSQL 10, the default is at least 1 GB (1024 MB). For example, `max_wal_size` setting for RDS for PostgreSQL 14 is 2 GB (2048 MB). Use the `SHOW max_wal_size;` command on your RDS for PostgreSQL DB instance to see its current value.
`min_wal_size`	Dynamic	Sets the minimum size to shrink the WAL to. For PostgreSQL version 9.6 and earlier, `min_wal_size` is in units of 16 MB. For PostgreSQL version 10 and later, `min_wal_size` is in units of 1 MB.
`quote_all_identifiers`	Dynamic	Adds quotes (") to all identifiers when generating SQL fragments.
`random_page_cost`	Dynamic	Sets the planner's estimate of the cost of a non-sequentially fetched disk page. This parameter has no value unless query plan management (QPM) is turned on. When QPM is on, the default value for this parameter `4`.
`rds.adaptive_autovacuum`	Dynamic	Automatically tunes the autovacuum parameters whenever the transaction ID thresholds are exceeded.
`rds.force_ssl`	Dynamic	Requires the use of SSL connections. The default value is set to 1 (on) for RDS for PostgreSQL version 15. All other RDS for PostgreSQL major version 14 and older have the default value set to 0 (off).
`rds.local_volume_spill_enabled`	Static	Enables writing logical spill files to the local volume.
`rds.log_retention_period`	Dynamic	Sets log retention such that Amazon RDS deletes PostgreSQL logs that are older than n minutes.
`rds.rds_superuser_reserved_connections`	Static	Sets the number of connection slots reserved for rds_superusers. This parameter is only available in versions 15 and earlier. For more information, see the PostgreSQL documentation reserved_connections.
`rds.restrict_password_commands`	Static	Restricts who can manage passwords to users with the `rds_password` role. Set this parameter to 1 to enable password restriction. The default is 0.
`search_path`	Dynamic	Sets the schema search order for names that are not schema-qualified.
`seq_page_cost`	Dynamic	Sets the planner's estimate of the cost of a sequentially fetched disk page.
`session_replication_role`	Dynamic	Sets the sessions behavior for triggers and rewrite rules.
`shared_buffers`	Static	Sets the number of shared memory buffers used by the server.
`shared_preload_libraries`	Static	Lists the shared libraries to preload into the RDS for PostgreSQL DB instance. Supported values include auto_explain, orafce, pgaudit, pglogical, pg_bigm, pg_cron, pg_hint_plan, pg_prewarm, pg_similarity, pg_stat_statements, pg_tle, pg_transport, plprofiler, and plrust.
`ssl`	Dynamic	Enables SSL connections.
`sql_inheritance`	Dynamic	Causes subtables to be included by default in various commands.
`ssl_renegotiation_limit`	Dynamic	Sets the amount of traffic to send and receive before renegotiating the encryption keys.
`standard_conforming_strings`	Dynamic	Causes ... strings to treat backslashes literally.
`statement_timeout`	Dynamic	Sets the maximum allowed duration of any statement.
`synchronize_seqscans`	Dynamic	Enables synchronized sequential scans.
`synchronous_commit`	Dynamic	Sets the current transactions synchronization level.
`tcp_keepalives_count`	Dynamic	Maximum number of TCP keepalive retransmits.
`tcp_keepalives_idle`	Dynamic	Time between issuing TCP keepalives.
`tcp_keepalives_interval`	Dynamic	Time between TCP keepalive retransmits.
`temp_buffers`	Dynamic	Sets the maximum number of temporary buffers used by each session.
`temp_file_limit`	Dynamic	Sets the maximum size in KB to which the temporary files can grow.
`temp_tablespaces`	Dynamic	Sets the tablespaces to use for temporary tables and sort files.
`timezone`	Dynamic	Sets the time zone for displaying and interpreting time stamps. The Internet Assigned Numbers Authority (IANA) publishes new time zones at https://www.iana.org/time-zones several times a year. Every time RDS releases a new minor maintenance release of PostgreSQL, it ships with the latest time zone data at the time of the release. When you use the latest RDS for PostgreSQL versions, you have recent time zone data from RDS. To ensure that your DB instance has recent time zone data, we recommend upgrading to a higher DB engine version. You can't modify the time zone tables in PostgreSQL DB instances manually. RDS doesn't modify or reset the time zone data of running DB instances. New time zone data is installed only when you perform a database engine version upgrade.
`track_activities`	Dynamic	Collects information about running commands.
`track_activity_query_size`	Static	Sets the size reserved for pg_stat_activity.current_query, in bytes.
`track_counts`	Dynamic	Collects statistics on database activity.
`track_functions`	Dynamic	Collects function-level statistics on database activity.
`track_io_timing`	Dynamic	Collects timing statistics on database I/O activity.
`transaction_deferrable`	Dynamic	Indicates whether to defer a read-only serializable transaction until it can be started with no possible serialization failures.
`transaction_isolation`	Dynamic	Sets the current transactions isolation level.
`transaction_read_only`	Dynamic	Sets the current transactions read-only status.
`transform_null_equals`	Dynamic	Treats expr=NULL as expr IS NULL.
`update_process_title`	Dynamic	Updates the process title to show the active SQL command.
`vacuum_cost_delay`	Dynamic	Vacuum cost delay in milliseconds.
`vacuum_cost_limit`	Dynamic	Vacuum cost amount available before napping.
`vacuum_cost_page_dirty`	Dynamic	Vacuum cost for a page dirtied by vacuum.
`vacuum_cost_page_hit`	Dynamic	Vacuum cost for a page found in the buffer cache.
`vacuum_cost_page_miss`	Dynamic	Vacuum cost for a page not found in the buffer cache.
`vacuum_defer_cleanup_age`	Dynamic	Number of transactions by which vacuum and hot cleanup should be deferred, if any.
`vacuum_freeze_min_age`	Dynamic	Minimum age at which vacuum should freeze a table row.
`vacuum_freeze_table_age`	Dynamic	Age at which vacuum should scan a whole table to freeze tuples.
`wal_buffers`	Static	Sets the number of disk-page buffers in shared memory for WAL.
`wal_writer_delay`	Dynamic	WAL writer sleep time between WAL flushes.
`work_mem`	Dynamic	Sets the maximum memory to be used for query workspaces.
`xmlbinary`	Dynamic	Sets how binary values are to be encoded in XML.
`xmloption`	Dynamic	Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments.

Amazon RDS uses the default PostgreSQL units for all parameters. The following table shows the PostgreSQL default unit for each parameter.

Parameter name	Unit
`archive_timeout`	s
`authentication_timeout`	s
`autovacuum_naptime`	s
`autovacuum_vacuum_cost_delay`	ms
`bgwriter_delay`	ms
`checkpoint_timeout`	s
`checkpoint_warning`	s
`deadlock_timeout`	ms
`effective_cache_size`	8 KB
`lock_timeout`	ms
`log_autovacuum_min_duration`	ms
`log_min_duration_statement`	ms
`log_rotation_age`	minutes
`log_rotation_size`	KB
`log_temp_files`	KB
`maintenance_work_mem`	KB
`max_stack_depth`	KB
`max_standby_archive_delay`	ms
`max_standby_streaming_delay`	ms
`post_auth_delay`	s
`pre_auth_delay`	s
`segment_size`	8 KB
`shared_buffers`	8 KB
`statement_timeout`	ms
`ssl_renegotiation_limit`	KB
`tcp_keepalives_idle`	s
`tcp_keepalives_interval`	s
`temp_file_limit`	KB
`work_mem`	KB
`temp_buffers`	8 KB
`vacuum_cost_delay`	ms
`wal_buffers`	8 KB
`wal_receiver_timeout`	ms
`wal_segment_size`	B
`wal_sender_timeout`	ms
`wal_writer_delay`	ms
`wal_receiver_status_interval`	s

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Viewing temporary file usage with Performance Insights

Tuning with wait events for RDS for PostgreSQL