Help Center/ Relational Database Service/ User Guide/ Working with RDS for PostgreSQL/ Performance Tuning/ Suggestions on RDS for PostgreSQL Parameter Tuning
Updated on 2024-10-14 GMT+08:00
View PDF
Share

Suggestions on RDS for PostgreSQL Parameter Tuning

Parameters are key configuration items in a database system. Improper parameter settings may adversely affect database performance. This section describes some important parameters for your reference. For details, visit the PostgreSQL official website.

For details on how to modify RDS for PostgreSQL parameters on the console, see Modifying Parameters of an RDS for PostgreSQL Instance.

Sensitive Parameters

The following parameters can result in system security and stability issues if set improperly:

  • The search_path parameter must be set to a schema sequence where schemas are separated by commas (,). Ensure that the schemas exist. Otherwise, the database performance will be affected.
  • If you enable the parameter log_duration, SQL statements containing sensitive information may be recorded in logs. You are advised to disable this parameter.
  • log_min_duration_statement specifies how many milliseconds a query has to run before it has to be logged. The unit is millisecond. Setting this parameter to 0 means that all statements are recorded. Setting this parameter to -1 means that no statement is recorded. For details, see Viewing and Downloading Slow Query Logs.
  • The temp_file_limit parameter limits the total size (in KB) of all temporary files when writing temporary files to the disk is triggered in a session. The value ranges from -1 to 2,147,483,647. The value -1 indicates that the total size of the temporary files is not limited.
    • This parameter is only available to RDS for PostgreSQL 11, 12, 13, 14, and 15.
    • To prevent temporary files from occupying too much disk space and causing service exceptions, do not set this parameter to -1.
    • If the parameter value is changed to a larger value for temporary use but is not changed to the original value after the use, the disk space will be continuously used to store temporary files. If the disk space is used up, services will be interrupted and the DB instance will become unavailable.
  • The max_pred_locks_per_transaction and max_locks_per_transaction parameters need to be set based on the values of max_connections and max_prepared_transactions. Too large values may cause instance unavailability.

Performance Parameters

The following parameters can affect database performance:

  • If log_statement is set to ddl, mod, or all, the operations for creating and deleting database users (including passwords and other sensitive information) are recorded. This operation affects database performance. Exercise caution when setting this parameter.
  • Enabling the following parameters will affect the database performance: log_hostname, log_duration, log_connections, and log_disconnections. Exercise caution when enabling these parameters.
  • The shared_buffers parameter is recommended to be a value ranging from 25% to 40% of the system memory. The maximum value of this parameter cannot exceed 80% of the system memory to avoid affecting database performance.
  • The max_worker_processes parameter should be set based on the values of max_parallel_workers and max_parallel_workers_per_gather. If the max_worker_processes value is too large, the database performance will be affected.
Parent Topic: Performance Tuning