Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Parameter Priority in PostgreSQL

Parameter Priority in PostgreSQL

This short article explains configurable portals and parameter priority in PostgreSQL.

By Digoal

This short article explains configuration portals and parameter priority in PostgreSQL.

Background

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?

Parameter Priority

The bigger the value = the higher the priority

1. Postgresql.conf

work_mem=1MB

2. Postgresql.auto.conf

work_mem=2MB

3. Command Line Options

work_mem=3MB  
  
pg_ctl start -o "-c work_mem='3MB'"

4. All Roles

work_mem=4MB  
  
alter role all set work_mem='4MB';

5. Database

work_mem=5MB  
  
alter database postgres set work_mem='5MB';

6. Role

work_mem=6MB  
  
alter role digoal set work_mem='6MB';

7. Session (Client Parameter)

work_mem=7MB  
  
set work_mem ='7MB';

8. Transaction

work_mem=8MB  
  
postgres=# begin;  
BEGIN  
postgres=# set local work_mem='8MB';  
SET

9. Function

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)

10. Table

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  
  
... ...

Summary

PostgreSQL supports the following configuration portals:

  • Configuration file (postgresql.conf)
  • Alter system (postgresql.auto.conf)
  • Command lines (postgres -o, pg_ctl -o)
  • All users (alter role all set)
  • Database (alter database xxx set)
  • User (alter role username set)
  • Session (set xxx)
  • Transaction (set local xxx;)
  • Function (create or replace function .... set par=val;)
  • Table (parameters related to table-level garbage collection)

If a parameter has been configured on all portals, its priority is getting higher from top to bottom.

Reference

PostgreSQL Database ApsaraDB RDS for PostgreSQL Parameter Priority Alter System Configurable portals
0 0 0
Share on

Read previous post:

Solution for the PostgreSQL Roaringbitmap UID Overflows Beyond Int4 (32 Bytes) – Offset

Read next post:

Modify Timezone through Alibaba Cloud RDS for PostgreSQL

digoal

282 posts | 24 followers

You may also like

Comments

digoal

282 posts | 24 followers

Related Products

More Posts by digoal

See All