How to Understand PostgreSQL Configuration Precedence: ALTER SYSTEM vs ALTER DATABASE

In Postgres, we can set configuration parameters at multiple levels. You can configure the same parameter globally for the entire server, for a specific database, for a specific role, or even only for the current session. Because of these multiple configuration layers, it is important to understand which setting is actually used by postgres when several values exist for the same parameter.

A common misconception is that executing ALTER DATABASE immediately overrides an ALTER SYSTEM setting for every existing connection. In reality, configuration precedence depends not only on the setting's priority but also on when the configuration is applied.

Let’s change the value of a postgres configuration parameter named work_mem.

Check the current configuration like this.

show work_mem ;

Result:

-[ RECORD 1 ]-
work_mem | 4MB

Now, use the alter system command to set another value for this parameter.

alter system set work_mem = '8MB';

Now, use the pg_reload_conf() command to reload its configuration files without restarting the server.

select pg_reload_conf();

Result:

-[ RECORD 1 ]--+--
pg_reload_conf | t

If it reloads successfully, we can see the boolean value true denoted as t.

Now, check the value for this changed configuration parameter.

show work_mem ;

Result:

-[ RECORD 1 ]-
work_mem | 8MB

Now, the value has changed.

Now, use the alter database command to set the value of work_mem for a specific database.

alter database postgres set work_mem = '1MB';

Now reload the configuration.

select pg_reload_conf();

Result:

-[ RECORD 1 ]--+--
pg_reload_conf | t

Now, check the value of the work_mem parameter.

show work_mem ;

Result:

-[ RECORD 1 ]-
work_mem | 8MB

Still the same value. This is because database-level configuration is applied only when a new connection is established.

The current session was already connected before ALTER DATABASE was executed. So reloading the configuration causes postgres to reread server configuration files, but it does not reinitialize existing sessions or reapply database-specific settings.

As a result, the existing connection continues using the value that was already loaded when the session started.

Now, connect to another database.

\c odoo

Check the value of the work_mem parameter.

show work_mem ;

Result:

-[ RECORD 1 ]-
work_mem | 8MB

Now, connect to the postgres database.

\c postgres 

Check the value again.

show work_mem ;

Result:

-[ RECORD 1 ]-
work_mem | 1MB

Now, the value of this parameter is 1 mb which we set earlier using the alter database command.

Now, Postgres has a catalogue named pg_db_role_setting. The main purpose of this catalogue is to store configuration settings that apply to a specific database, a specific role, or a combination of both.

select * from pg_db_role_setting ;

Result:

 setdatabase | setrole |   setconfig    
-------------+---------+----------------
       53522 |       0 | {work_mem=1MB}
(1 row)

• The setdatabase is the unique identifier of the database.
• The setrole is the unique identifier of the role related to this value setting. A value of 0 means the setting applies to all roles.
• The setconfig is the array containing one or more configuration parameters we set using the alter system commands.

Like the majority of the pg_catalogues, this is not a view. It is a table, and it is stored in a separate tablespace named pg_global.

Check the structure of this catalogue like this.

\d+ pg_db_role_setting

Result:

                                    Table "pg_catalog.pg_db_role_setting"
   Column    |  Type  | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
-------------+--------+-----------+----------+---------+----------+-------------+--------------+-------------
 setdatabase | oid    |           | not null |         | plain    |             |              | 
 setrole     | oid    |           | not null |         | plain    |             |              | 
 setconfig   | text[] | C         |          |         | extended |             |              | 
Indexes:
    "pg_db_role_setting_databaseid_rol_index" PRIMARY KEY, btree (setdatabase, setrole), tablespace "pg_global"
Not-null constraints:
    "pg_db_role_setting_setdatabase_not_null" NOT NULL "setdatabase"
    "pg_db_role_setting_setrole_not_null" NOT NULL "setrole"
Tablespace: "pg_global"
Access method: heap

A tablespace is a named storage location on the filesystem where we can store database objects like tables, indexes, materialized views, etc.

By default, postgres stores everything inside its data directory (PGDATA). A tablespace lets you store specific objects in a different directory, often on a different disk.

select * from pg_tablespace ;

Result:

 oid  |  spcname   | spcowner | spcacl | spcoptions 
------+------------+----------+--------+------------
 1663 | pg_default |       10 |        | 
 1664 | pg_global  |       10 |        | 
(2 rows)

The pg_default is the default storage location where all user tables and indexes are created unless another tablespace is specified.

The pg_global stores cluster-wide shared system catalogs that are accessible from every database.

Understanding postgres configuration hierarchy is essential for diagnosing situations where a parameter appears to ignore a newly applied setting. In the example above, ALTER DATABASE did not immediately replace the value established by ALTER SYSTEM because the existing session had already completed its initialization. Only after reconnecting did the postgres to reload the database-specific configuration from pg_db_role_setting and apply it to the new session.