This short article explains configuration portals and parameter priority in PostgreSQL.
PostgreSQL parameter configuration is comprehensive and flexible, which can be completed with configuration files, alter system, command lines, roles, databases, all roles, sessions, transactions, functions, and tables.
It's good with the flexibility, but there are too many configurable portals. Which one should be the priority? If different values of the same parameter are configured for multiple portals, which one prevails?
The bigger the value = the higher the priority
work_mem=3MB pg_ctl start -o "-c work_mem='3MB'"
work_mem=4MB alter role all set work_mem='4MB';
work_mem=5MB alter database postgres set work_mem='5MB';
work_mem=6MB alter role digoal set work_mem='6MB';
work_mem=7MB set work_mem ='7MB';
work_mem=8MB postgres=# begin; BEGIN postgres=# set local work_mem='8MB'; SET
The parameter is valid in the function. After the function is called, the other parameter value of the highest priority is used.
work_mem=9MB postgres=# create or replace function f_test() returns void as $$ declare res text; begin show work_mem into res; raise notice '%', res; end; $$ language plpgsql strict set work_mem='9MB'; CREATE FUNCTION postgres=# select f_test(); NOTICE: 9MB f_test -------- (1 row)
TABLE parameters (related to garbage collection): https://www.postgresql.org/docs/11/sql-createtable.html
autovacuum_enabled toast.autovacuum_enabled ... ... autovacuum_vacuum_threshold toast.autovacuum_vacuum_threshold ... ...
PostgreSQL supports the following configuration portals:
If a parameter has been configured on all portals, its priority is getting higher from top to bottom.
digoal - July 22, 2021
Alibaba Clouder - October 25, 2018
digoal - April 26, 2021
digoal - August 3, 2021
digoal - August 20, 2021
digoal - April 30, 2021
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilitiesLearn More
An online MPP warehousing service based on the Greenplum Database open source programLearn More
Fully managed and less trouble database servicesLearn More
An on-demand database hosting service for MySQL, SQL Server and PostgreSQL with automated monitoring, backup and disaster recovery capabilitiesLearn More
More Posts by digoal