Working with parameters on your RDS for PostgreSQL DB instance - Amazon Relational Database Service

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