Parameter descriptions-PolarDB(PolarDB)-阿里云帮助中心

PolarDB for PostgreSQL(Compatible with Oracle) ships with a default parameter template that covers most common workloads. When your database statistics or slow query analysis reveals a performance bottleneck, adjust the relevant parameters to tune cluster behavior.

Resource consumption parameters

Parameter	Unit	Description
shared_buffers	—	Size of the shared memory buffer pool used by all database server processes. A larger pool caches more data and reduces disk I/O. The default value scales with your cluster specification.
work_mem	—	Memory allocated to each internal sort operation or hash table before data spills to temporary disk files. A single complex query can run multiple sort or hash operations in parallel, each consuming up to this amount. Concurrent sessions multiply the total memory usage, so set this value with the full workload in mind. Sort operations include ORDER BY, DISTINCT, and merge joins. Hash tables are used for hash joins, hash-based aggregation, and hash-based IN subqueries.
maintenance_work_mem	—	Maximum memory for maintenance operations such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. Only one such operation runs per session at a time, but several may run concurrently across sessions. Set this value well above `work_mem` to avoid memory exhaustion. When autovacuum runs, the system may allocate up to `autovacuum_max_workers` × `maintenance_work_mem` in total. Set `autovacuum_work_mem` separately to cap autovacuum memory independently.
autovacuum_work_mem	—	Maximum memory per autovacuum worker process. Default: `-1` (inherits the value of `maintenance_work_mem`).
temp_file_limit	KB	Maximum disk space a process can use for temporary files, including sort and hash temporary files and stored cursor data. Any transaction that exceeds this limit is canceled. Set to `-1` for no limit.
max_worker_processes	—	Maximum number of background processes the system supports. Set this parameter before starting the server; changes require a restart.
max_parallel_workers	—	Maximum number of workers available for parallel queries across the entire system. Values above `max_worker_processes` have no effect. Each parallel worker is an independent process with roughly the same system impact as an additional user session.
max_parallel_workers_per_gather	—	Maximum number of workers a single Gather or Gather Merge node can start. Workers are drawn from the pool defined by `max_worker_processes` and capped by `max_parallel_workers`.
max_parallel_maintenance_workers	—	Maximum number of parallel workers a single CREATE INDEX operation can use.

Autovacuum parameters

Parameter	Unit	Description
autovacuum_max_workers	—	Maximum number of autovacuum worker processes that can run simultaneously, excluding the autovacuum launcher. Default: `3`. Set this parameter before starting the server; changes require a restart.
autovacuum_naptime	s	Minimum delay between autovacuum runs on any given database. The background process checks each database in turn and issues VACUUM and ANALYZE as needed. Default: `1min`.
autovacuum_vacuum_threshold	—	Minimum number of inserted, updated, or deleted tuples in a table before a VACUUM is triggered. Default: `50`. Override per table using table storage parameters.
autovacuum_analyze_threshold	—	Minimum number of inserted, updated, or deleted tuples in a table before an ANALYZE is triggered. Default: `50`. Override per table using table storage parameters.
autovacuum_vacuum_scale_factor	—	Fraction of the table size added to `autovacuum_vacuum_threshold` when deciding whether to trigger VACUUM. Default: `0.05` (5% of table size). Override per table using table storage parameters.
autovacuum_analyze_scale_factor	—	Fraction of the table size added to `autovacuum_analyze_threshold` when deciding whether to trigger ANALYZE. Default: `0.1` (10% of table size). Override per table using table storage parameters.
autovacuum_vacuum_cost_delay	ms	Cost delay applied during automatic VACUUM operations. Default: `-1` (inherits `vacuum_cost_delay`). Override per table using table storage parameters.
autovacuum_vacuum_cost_limit	—	Cost limit applied during automatic VACUUM operations. Default: `-1` (inherits `vacuum_cost_limit`). When multiple autovacuum workers run concurrently, this limit is distributed proportionally among them so the combined total never exceeds this value. Override per table using table storage parameters.

Query planning parameters

Important

Do not change these parameters globally. Modify them only within the current session when testing a specific query.

Parameter	Description
enable_bitmapscan	Enables or disables bitmap scan plan types in the query planner.
enable_hashagg	Enables or disables hashed aggregation plan types.
enable_hashjoin	Enables or disables hash join plan types.
enable_indexscan	Enables or disables index scan plan types.
enable_indexonlyscan	Enables or disables index-only scan plan types.
enable_seqscan	Enables or disables sequential scan plan types. Sequential scans cannot be fully disabled; disabling this parameter causes the planner to prefer other methods when available.
enable_sort	Enables or disables explicit sort steps. Explicit sorts cannot be fully disabled; disabling this parameter causes the planner to prefer other methods when available.
enable_mergejoin	Enables or disables merge join plan types.
enable_nestloop	Enables or disables nested-loop join plans. Nested-loop joins cannot be fully disabled; disabling this parameter causes the planner to prefer other methods when available.
enable_parallel_append	Enables or disables parallel-aware append plan types.
enable_parallel_hash	Enables or disables hash join plan types for parallel hash builds. Has no effect if `enable_hashjoin` is disabled.

Planner cost parameters

The query planner uses these cost estimates to choose between execution plans. All values are dimensionless and meaningful only relative to one another.

Parameter	Default	Description
seq_page_cost	1.0	Estimated cost of fetching a page during a sequential disk scan.
random_page_cost	4.0	Estimated cost of fetching a page during a non-sequential (random) disk access.
cpu_tuple_cost	0.01	Estimated cost of processing one row during a query.
cpu_index_tuple_cost	0.005	Estimated cost of processing one index entry during an index scan.
cpu_operator_cost	0.0025	Estimated cost of evaluating one operator or function during a query.
parallel_setup_cost	1000	Estimated cost of launching parallel worker processes.
parallel_tuple_cost	0.1	Estimated cost of transferring one tuple from a parallel worker to the coordinating process.
min_parallel_table_scan_size	8 MB	Minimum amount of table data that must be scanned before the planner considers using parallel execution. For a sequential scan, this equals the full table size. For index scans, only the pages the planner expects to read are counted.
min_parallel_index_scan_size	512 KB	Minimum amount of index data that must be scanned before the planner considers using parallel execution. Typically covers only the relevant index pages, not the entire index.

pg_settings view

Query the pg_settings system view to inspect current parameter values and metadata. Each row corresponds to one parameter.

SELECT name, setting, unit, context, vartype, min_val, max_val, pending_restart
FROM pg_settings
WHERE name = 'vacuum_cost_delay';

The table below describes the columns using vacuum_cost_delay as an example.

Column	Example value	Description
name	vacuum_cost_delay	Parameter name.
setting	10	Current value.
unit	ms	Unit of the value.
category	Resource Usage / Cost-Based Vacuum Delay	Parameter category.
short_desc	Vacuum cost delay in milliseconds.	Brief description.
extra_desc	None	Additional description, if any.
context	user	Context required to change the parameter (for example, `user`, `superuser`, or `postmaster`).
vartype	integer	Data type. Valid values: `bool`, `enum`, `integer`, `real`, `string`.
source	database	Source of the current value (for example, `database`, `user`, or `configuration file`).
min_val	0	Minimum allowed value.
max_val	100	Maximum allowed value.
enumvals	None	Allowed values for `enum` type parameters.
boot_val	0	Default value used at server startup when the parameter is not explicitly set.
reset_val	10	Value the parameter resets to in the current session when `RESET` is called.
sourcefile	None	Configuration file where the current value is set, if applicable.
sourceline	None	Line number in the configuration file, if applicable.
pending_restart	f	`t` if the parameter was modified and a server restart is required for the change to take effect; `f` otherwise.