RDS for PostgreSQL Parameter Tuning Recommendations

search_path

• Description: specifies the schema search order for names that are not schema-qualified.

• Recommendation: You must set it to a comma-separated schema order and ensure that the schemas exist. Otherwise, the database will be affected.

log_duration

• Description: records the duration of each completed SQL statement.
• Default value: off
• Recommendation: Since SQL statements logged may contain sensitive information, you are advised to keep this parameter disabled.

log_min_duration_statement

• Description: specifies the minimum execution time threshold. Statements with execution time greater than or equal to this value are logged. The unit is milliseconds.
• Default value: 1000
• Recommendation: The value 0 means that all statements are logged. The value -1 means that no statements are logged. For details, see Viewing and Downloading Slow Query Logs.

temp_file_limit

• Description: specifies the total size limit (in KB) for all temporary files written to disk during a single session.
• Recommendation: The value ranges from -1 to 2,147,483,647. The value -1 means no limit on temporary file size.
  • Avoid setting -1 to prevent excessive disk usage that can affect workloads.
  • If you temporarily increase this parameter value, ensure that you reset it to its original value afterward. Failing to do so may lead to excessive consumption of temporary space, which can fill up the disk, interrupt your workloads, and cause the instance to enter an unhealthy state.

max_pred_locks_per_transaction

• Description: specifies the maximum number of predicate locks allowed per transaction.
• Recommendation: This parameter is related to max_connections and max_prepared_transactions. Configure it according to actual workload requirements. Setting the value too high may cause the instance to become abnormal.

max_locks_per_transaction

• Description: specifies the maximum number of locks allowed per transaction.
• Recommendation: This parameter is related to max_connections and max_prepared_transactions. Adjusting the value directly affects shared memory usage. Setting it too high may lead to insufficient memory and cause the instance to become abnormal. The estimated memory consumption (in KB) is calculated using the formula: 0.4 x max_locks_per_transaction x (max_connections + max_prepared_transactions)

log_statement

• Description: specifies the type of statements logged.
• Recommendation: Setting this parameter to ddl, mod, or all will cause operations such as creating or dropping database users to be logged. This may expose sensitive information, including database user passwords, and can degrade database performance. Fully evaluate these risks before setting this parameter.

shared_buffers

• Description: specifies the number of shared memory buffers used by the server. Each buffer is 8 KB in size.
• Recommendation: A reasonable value range is 25% to 40% of system memory. Setting the parameter too high may degrade performance. Evaluate the impact before setting this parameter.

max_worker_processes

• Description: specifies the maximum number of concurrent worker processes.
• Recommendation: This parameter is related to max_parallel_workers and max_parallel_workers_per_gather. Configure it according to workload requirements. Setting the value too high may affect database performance. Evaluate the impact before setting this parameter.