After you know the current status and problems of your database based on information such as database statistics and slow SQL queries, you can modify the parameter settings of your database to solve these problems. The default parameter template of PolarDB for PostgreSQL(Compatible with Oracle) is applicable to most common database scenarios but cannot meet the requirements of some special database scenarios. You can modify the database parameters to improve cluster performance.

Resource consumption parameters

ParameterDescription
shared_buffersThe size of memory used by database servers for shared memory buffers. A larger buffer can cache more data. The value of this parameter in PolarDB for PostgreSQL(Compatible with Oracle) varies based on PolarDB for PostgreSQL(Compatible with Oracle) cluster specifications.
work_memThe amount of memory to be used by internal sort operations and hash tables before data is written to temporary disk files. For a complex query, several sort or hash operations may run in parallel. Each operation is allowed to use the amount of memory that is specified by this parameter before the operation starts to write data into temporary files. Several running sessions may also perform such operations in parallel. Therefore, the total used memory may be several times the value of the work_mem parameter. Take this into consideration when you specify this parameter. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.
maintenance_work_mem

The maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. In a database session, only one of these operations can be executed at a time point. In most cases, a few such operations run in parallel when you use the database. Therefore, to prevent memory exhaustion, set this parameter to a value that is much greater than the value of the work_mem parameter. A large value improves the performance of vacuuming and restoring database dumps.

When the autovacuum worker process runs, the system may allocate memory up to autovacuum_max_workers times as much as the amount of memory that is specified by the maintenance_work_mem parameter. Therefore, do not set the maintenance_work_mem parameter to a large value. Independent setting of the autovacuum_work_mem parameter is helpful.

autovacuum_work_memThe maximum amount of memory that each autovacuum worker process can use. The default value is -1. This indicates that the value of the maintenance_work_mem parameter is used.
temp_file_limitThe maximum 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. A transaction that attempts to exceed this limit will be canceled. The unit is kilobyte. The value -1 indicates no limit.
max_worker_processesThe maximum number of background processes that the system can support. You can specify this parameter only before a server start.
max_parallel_workersThe maximum number of workers that the system can support for parallel queries. If the value of this parameter is greater than the value of the max_worker_processes parameter, the value of this parameter does not take effect. Parallel queries may consume more resources than non-parallel queries, because each worker process is an independent process. A worker process roughly has the same impact on the system as an additional user session.
max_parallel_workers_per_gatherThe maximum number of workers that can be started by a single Gather or Gather Merge node. Parallel workers are obtained from a process pool. The max_worker_processes parameter specifies the number of processes in the pool. The maximum number of parallel workers to be obtained cannot exceed the value of the max_parallel_workers parameter.
max_parallel_maintenance_workersThe maximum number of parallel workers that can be started by a single CREATE INDEX operation.

Parameters related to autovacuum

ParameterDescription
autovacuum_max_workersThe maximum number of autovacuum processes that can run at the same time, except the autovacuum launcher. Default value: 3. You can specify this parameter only before a server start.
autovacuum_naptimeThe minimum delay for autovacuum to run on a database. In each round, the background process checks the database, and issues the VACUUM and ANALYZE commands for tables in the database as needed. Unit: seconds. Default value: 1min.
autovacuum_vacuum_thresholdThe minimum number of inserted, updated, or deleted tuples that are needed to trigger a VACUUM operation in a table. Default value: 50. You can overwrite this parameter setting by modifying table storage parameters.
autovacuum_analyze_thresholdThe minimum number of inserted, updated, or deleted tuples that are needed to trigger an ANALYZE operation in a table. Default value: 50. You can overwrite this parameter setting by modifying table storage parameters.
autovacuum_vacuum_scale_factorThe fraction of the table size to be added to autovacuum_vacuum_threshold when a decision is made on whether to trigger a VACUUM operation. Default value: 0.05. This indicates 5% of the table size. You can overwrite this parameter setting by modifying table storage parameters.
autovacuum_analyze_scale_factorThe fraction of the table size to be added to autovacuum_analyze_threshold when a decision is made on whether to trigger an ANALYZE operation. Default value: 0.1. This indicates 10% of the table size. You can overwrite this parameter setting by modifying table storage parameters.
autovacuum_vacuum_cost_delayThe cost delay value to be used in automatic VACUUM operations. Default value: -1. If this parameter is set to -1, the value of the vacuum_code_delay parameter is used. You can overwrite this parameter setting by modifying table storage parameters.
autovacuum_vacuum_cost_limitThe cost limit value to be used in automatic VACUUM operations. Default value: -1. If this parameter is set to -1, the value of the vacuum_cost_limit parameter is used. If multiple autovacuum workers exist, this value is distributed in proportion among the running autovacuum workers. Therefore, the sum of the limit values for each worker does not exceed the value of the autovacuum_vacuum_cost_limit parameter. You can overwrite this parameter setting by modifying table storage parameters.

Query planning parameters

We recommend that you do not modify global settings of the following parameters. When you test a specific query, you can modify parameters in the current session.

ParameterDescription
enable_bitmapscanSpecifies whether to enable the query planner to use bitmap-scan plan types.
enable_hashaggSpecifies whether to enable the query planner to use hashed aggregation plan types.
enable_hashjoinSpecifies whether to enable the query planner to use hash-join plan types.
enable_indexscanSpecifies whether to enable the query planner to use index-scan plan types.
enable_indexonlyscanSpecifies whether to enable the query planner to use index-only-scan plan types.
enable_seqscanSpecifies whether to enable the query planner to use sequential scan plan types. Sequential scans cannot be completely prohibited. However, if you disable this parameter, the planner prioritizes other methods.
enable_sortSpecifies whether to enable the query planner to use explicit sort steps. Explicit sorts cannot be completely prohibited. However, if you disable this parameter, the planner prioritizes other methods.
enable_mergejoinSpecifies whether to enable the query planner to use merge-join plan types.
enable_nestloopSpecifies whether to enable the query planner to use nested-loop join plans. Nested-loop joins cannot be completely prohibited. However, if you disable this parameter, the planner prioritizes other methods.
enable_parallel_appendSpecifies whether to enable the query planner to use parallel-aware append plan types.
enable_parallel_hashSpecifies whether to enable the query planner to use hash-join plan types for parallel hashes. If hash-join plans are not enabled, this parameter does not take effect.

Planner cost parameters

ParameterDescription
seq_page_costThe planner-estimated cost of a sequential disk page fetch. Default value: 1.0.
random_page_costThe planner-estimated cost of a non-sequential disk page fetch. Default value: 4.0.
cpu_tuple_costThe planner-estimated cost of processing each row during a query. Default value: 0.01.
cpu_index_tuple_costThe planner-estimated cost of processing each index entry during an index scan. Default value: 0.005.
cpu_operator_costThe planner-estimated cost of processing each operator or function during a query. Default value: 0.0025.
parallel_setup_costThe planner-estimated cost of starting parallel worker processes. Default value: 1000.
parallel_tuple_costThe planner-estimated cost of transferring one tuple from a parallel worker process to another process. Default value: 0.1.
min_parallel_table_scan_sizeThe minimum amount of table data that must be scanned. If the amount of scanned table data exceeds this value, the use of parallel scans is considered. For a parallel sequential scan, the amount of scanned table data is always equal to the size of the table. However, if indexes are used, the amount of scanned table data is less than the size of the table in most cases. Default value: 8 MB.
min_parallel_index_scan_sizeThe minimum amount of index data that must be scanned. If the amount of scanned index data exceeds this value, the use of parallel scans is considered. In most cases, a parallel index scan does not involve the entire index, but involves the number of relevant pages that the planner believes to be scanned. Default value: 512 KB.

PolarDB for PostgreSQL(Compatible with Oracle) parameter settings

Table 1. Details of pg_setting
ColumnValueDescription
namevacuum_cost_delayThe name of the parameter.
setting10The current value of the parameter.
unitmsThe unit of the parameter.
categoryResource Usage / Cost-Based Vacuum DelayThe category of the parameter.
short_descVacuum cost delay in milliseconds.The description of the parameter.
extra_descNoneThe additional description of the parameter.
contextuserThe context that is required to specify the parameter value.
vartypeintegerThe type of the parameter. Valid values: bool, enum, integer, real, and string.
sourcedatabaseThe source of the parameter value.
min_val0The minimum allowed value of the parameter.
max_val100The maximum allowed value of the parameter.
enumvalsNoneThe allowed values of an enum parameter.
boot_val0The parameter value to be used during server startup if the parameter is left empty.
reset_val10The value to which the parameter is reset in the current session.
sourcefileNoneThe profile in which the current value is specified.
sourcelineNoneThe line number of the profile in which the current value is specified.
pending_restartfIf the parameter is modified and a restart is required, use the value true. Otherwise, use the value false.

Resource consumption parameters of PolarDB for PostgreSQL(Compatible with Oracle)

ParameterDescription
shared_buffersThe size of memory used by database servers for shared memory buffers. A larger buffer can cache more data. The value of this parameter in PolarDB for PostgreSQL(Compatible with Oracle) varies based on PolarDB for PostgreSQL(Compatible with Oracle) cluster specifications.
work_memThe amount of memory to be used by internal sort operations and hash tables before data is written to temporary disk files. For a complex query, several sort or hash operations may run in parallel. Each operation is allowed to use the amount of memory that is specified by this parameter before the operation starts to write data into temporary files. Several running sessions may also perform such operations in parallel. Therefore, the total used memory may be several times the value of the work_mem parameter. Take this into consideration when you specify this parameter. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.
maintenance_work_mem

The maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. In a database session, only one of these operations can be executed at a time point. In most cases, a few such operations run in parallel when you use the database. Therefore, to prevent memory exhaustion, set this parameter to a value that is much greater than the value of the work_mem parameter. A large value improves the performance of vacuuming and restoring database dumps.

When the autovacuum worker process runs, the system may allocate memory up to autovacuum_max_workers times as much as the amount of memory that is specified by the maintenance_work_mem parameter. Therefore, do not set the maintenance_work_mem parameter to a large value. Independent setting of the autovacuum_work_mem parameter is helpful.

autovacuum_work_memThe maximum amount of memory that each autovacuum worker process can use. The default value is -1. This indicates that the value of the maintenance_work_mem parameter is used.
temp_file_limitThe maximum 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. A transaction that attempts to exceed this limit will be canceled. The unit is kilobyte. The value -1 indicates no limit.