PostgreSQL Configuration File

PostgreSQL Configuration File (postgresql.conf & auto.conf)

Table of Contents

1. What is postgresql.conf?

postgresql.conf is the main configuration file for PostgreSQL. It controls nearly every aspect of the server’s behavior — memory, connections, WAL, logging, query planner, autovacuum, and more.

But there is actually a second file most DBAs overlook:

  • postgresql.conf — the main file you edit by hand.
  • postgresql.auto.conf — created and managed by ALTER SYSTEM. It is read after postgresql.conf, so its values always win.

This post walks through both files, shows the difference between static (restart-required) and dynamic (reload-only) parameters, demonstrates the four levels of parameter scope, and shows what happens when things go wrong.

2. Locate the Configuration Files

The location varies by OS and installation method. Ask PostgreSQL directly:

postgres=# SHOW data_directory;
    data_directory
----------------------
 /pgData/pgsql17/data
(1 row)

postgres=# SHOW config_file;
             config_file
--------------------------------------
 /pgData/pgsql17/data/postgresql.conf
(1 row)

postgres=# SHOW hba_file;
             hba_file
----------------------------------
 /pgData/pgsql17/data/pg_hba.conf
(1 row)

postgres=# SHOW ident_file;
             ident_file
------------------------------------
 /pgData/pgsql17/data/pg_ident.conf
(1 row)

postgres=#

Key fact: postgresql.auto.conf always lives in the same directory as postgresql.conf (normally $PGDATA). It is created and updated only by ALTER SYSTEM.

3. Understand pg_settings — The Columns That Matter

The pg_settings view is your single source of truth for every configuration parameter. Here are the columns you need to know:

postgres=# SELECT
    name,
    setting,
    unit,
    context,
    source,
    sourcefile,
    sourceline,
    pending_restart
FROM pg_settings
WHERE name = 'max_connections';
      name       | setting | unit |  context   |       source       |              sourcefile              | sourceline | pending_restart
-----------------+---------+------+------------+--------------------+--------------------------------------+------------+-----------------
 max_connections | 100     |      | postmaster | configuration file | /pgData/pgsql17/data/postgresql.conf |          1 | f   <--------------
(1 row)

postgres=#

Column breakdown:

  • context — WHO/WHEN can change it. This is what defines static vs dynamic.
  • source — Where the current active value came from (default, configuration file, override, session, etc.).
  • sourcefile — The actual file that set it (.conf or .auto.conf).
  • sourceline — Line number within that file.
  • pending_restart — true = value was changed but needs a RESTART to apply.

4. Static vs Dynamic Parameters

Every parameter has a context that determines how and when it can be changed.

STATIC — context = postmaster (restart required)

postgres=# SELECT name, setting, context
FROM pg_settings
WHERE context = 'postmaster'
ORDER BY name
LIMIT 20;
                name                 |               setting                |  context
-------------------------------------+--------------------------------------+------------
 archive_mode                        | on                                   | postmaster
 autovacuum_freeze_max_age           | 200000000                            | postmaster
 autovacuum_max_workers              | 3                                    | postmaster
 autovacuum_multixact_freeze_max_age | 400000000                            | postmaster
 bonjour                             | off                                  | postmaster
 bonjour_name                        |                                      | postmaster
 cluster_name                        |                                      | postmaster
 commit_timestamp_buffers            | 32                                   | postmaster
 config_file                         | /pgData/pgsql17/data/postgresql.conf | postmaster
 data_directory                      | /pgData/pgsql17/data                 | postmaster
 data_sync_retry                     | off                                  | postmaster
 debug_io_direct                     |                                      | postmaster
 dynamic_shared_memory_type          | posix                                | postmaster
 event_source                        | PostgreSQL                           | postmaster
 external_pid_file                   |                                      | postmaster
 hba_file                            | /pgData/pgsql17/data/pg_hba.conf     | postmaster
 hot_standby                         | on                                   | postmaster
 huge_pages                          | try                                  | postmaster
 huge_page_size                      | 0                                    | postmaster
 ident_file                          | /pgData/pgsql17/data/pg_ident.conf   | postmaster
(20 rows)

postgres=#

DYNAMIC — everything else (no restart needed)

postgres=# SELECT name, setting, context
FROM pg_settings
WHERE context IN ('sighup','backend','superuser','user')
ORDER BY context, name
LIMIT 20;
                 name                  |            setting            | context
---------------------------------------+-------------------------------+---------
 ignore_system_indexes                 | off                           | backend
 post_auth_delay                       | 0                             | backend
 allow_alter_system                    | on                            | sighup
 archive_cleanup_command               |                               | sighup
 archive_command                       | cp %p /pgArch/pgsql17/arch/%f | sighup
 archive_library                       |                               | sighup
 archive_timeout                       | 0                             | sighup
 authentication_timeout                | 60                            | sighup
 autovacuum                            | on                            | sighup
 autovacuum_analyze_scale_factor       | 0.1                           | sighup
 autovacuum_analyze_threshold          | 50                            | sighup
 autovacuum_naptime                    | 60                            | sighup
 autovacuum_vacuum_cost_delay          | 2                             | sighup
 autovacuum_vacuum_cost_limit          | -1                            | sighup
 autovacuum_vacuum_insert_scale_factor | 0.2                           | sighup
 autovacuum_vacuum_insert_threshold    | 1000                          | sighup
 autovacuum_vacuum_scale_factor        | 0.2                           | sighup
 autovacuum_vacuum_threshold           | 50                            | sighup
 autovacuum_work_mem                   | -1                            | sighup
 bgwriter_delay                        | 200                           | sighup
(20 rows)

postgres=#

Count parameters per context

postgres=# SELECT context, count(*) AS num_params
FROM pg_settings
GROUP BY context
ORDER BY num_params DESC;
      context      | num_params
-------------------+------------
 user              |        145
 sighup            |         98
 postmaster        |         65
 superuser         |         46
 internal          |         19
 superuser-backend |          4
 backend           |          2
(7 rows)

postgres=#

Context cheat sheet:

  • postmaster → RESTART required. Cannot change on the fly.
  • sighup → RELOAD is enough (pg_reload_conf() or SIGHUP).
  • backend → Reload + only takes effect for new connections.
  • superuser / user → Can be changed live with SET, per session.

5. pg_file_settings — What’s Actually Written in the Files

pg_file_settings shows the raw contents of the conf files, independent of whether the value is currently applied.

postgres=# SELECT sourcefile, sourceline, name, setting, applied, error
FROM pg_file_settings
ORDER BY sourcefile, sourceline
LIMIT 15;
              sourcefile              | sourceline |            name            |      setting      | applied | error
--------------------------------------+------------+----------------------------+-------------------+---------+-------
 /pgData/pgsql17/data/postgresql.conf |          1 | max_connections            | 100               | t       |
 /pgData/pgsql17/data/postgresql.conf |          2 | shared_buffers             | 128MB             | t       |
 /pgData/pgsql17/data/postgresql.conf |          3 | dynamic_shared_memory_type | posix             | t       |
 /pgData/pgsql17/data/postgresql.conf |          4 | max_wal_size               | 1GB               | t       |
 /pgData/pgsql17/data/postgresql.conf |          5 | min_wal_size               | 80MB              | t       |
 /pgData/pgsql17/data/postgresql.conf |          6 | log_destination            | stderr            | t       |
 /pgData/pgsql17/data/postgresql.conf |          7 | logging_collector          | on                | t       |
 /pgData/pgsql17/data/postgresql.conf |          8 | log_directory              | log               | t       |
 /pgData/pgsql17/data/postgresql.conf |          9 | log_filename               | postgresql-%a.log | t       |
 /pgData/pgsql17/data/postgresql.conf |         10 | log_rotation_age           | 1d                | t       |
 /pgData/pgsql17/data/postgresql.conf |         11 | log_rotation_size          | 0                 | t       |
 /pgData/pgsql17/data/postgresql.conf |         12 | log_truncate_on_rotation   | on                | t       |
 /pgData/pgsql17/data/postgresql.conf |         13 | log_line_prefix            | %m [%p]           | t       |
 /pgData/pgsql17/data/postgresql.conf |         14 | log_timezone               | Asia/Singapore    | t       |
 /pgData/pgsql17/data/postgresql.conf |         15 | datestyle                  | iso, dmy          | t       |
(15 rows)

postgres=#

Key columns:

  • applied = false — the setting was overridden by a later file. For example, postgresql.auto.conf is read after postgresql.conf, so it wins.
  • error — non-null if that specific line failed to parse or apply.

6. Dynamic Parameter Change — Reload Only, No Restart

log_min_duration_statement has context = 'sighup', so a reload is enough.

-- Change the value using ALTER SYSTEM
postgres=# ALTER SYSTEM SET log_min_duration_statement = 500;
ALTER SYSTEM
postgres=#

-- Apply with reload (no restart needed)
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#

-- Confirm it applied immediately
postgres=# SELECT name, setting, context, source, sourcefile, pending_restart
FROM pg_settings
WHERE name = 'log_min_duration_statement';
            name            | setting |  context  |       source       |                sourcefile                 | pending_restart
----------------------------+---------+-----------+--------------------+-------------------------------------------+-----------------
 log_min_duration_statement | 500     | superuser | configuration file | /pgData/pgsql17/data/postgresql.auto.conf | f
(1 row)

postgres=#
postgres=# SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
 500ms   <-------------------
(1 row)

postgres=#

Change is LIVE right now. setting = 500, source = 'override', sourcefile = postgresql.auto.conf, pending_restart = false. No restart was needed.

7. Static Parameter Change — Reload is NOT Enough

max_connections has context = 'postmaster'. A reload picks up the file change, but the running server cannot apply it without a restart.

-- Change the value
postgres=# ALTER SYSTEM SET max_connections = 250;
ALTER SYSTEM
postgres=#

-- Reload
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#

-- Check the setting — the OLD value is still active!
postgres=# SELECT name, setting, context, source, sourcefile, pending_restart
FROM pg_settings
WHERE name = 'max_connections';
      name       | setting |  context   |       source       |              sourcefile              | pending_restart
-----------------+---------+------------+--------------------+--------------------------------------+-----------------
 max_connections | 100     | postmaster | configuration file | /pgData/pgsql17/data/postgresql.conf | t   <----------------
(1 row)

postgres=#

[postgres@pgdb01 ~]$ pg_ctl restart -D $PGDATA
waiting for server to shut down.... done
server stopped
waiting for server to start....2026-09-25 13:43:50.273 +08 [3390] LOG:  redirecting log output to logging collector process
2026-09-25 13:43:50.273 +08 [3390] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb01 ~]$

postgres=# SELECT name, setting, context, source, sourcefile, pending_restart
FROM pg_settings
WHERE name = 'max_connections';
      name       | setting |  context   |       source       |                sourcefile                 | pending_restart
-----------------+---------+------------+--------------------+-------------------------------------------+-----------------
 max_connections | 250     | postmaster | configuration file | /pgData/pgsql17/data/postgresql.auto.conf | f  <-------------
(1 row)

postgres=#

pending_restart = true. The file has the new value (250), but the running server still shows 100. A postmaster-context parameter requires a full restart to take effect. pg_reload_conf() alone is not enough.

8. How postgresql.auto.conf Overrides postgresql.conf

Every ALTER SYSTEM SET writes to postgresql.auto.conf, not to postgresql.conf. Since postgresql.auto.conf is parsed last, its values always win.

postgres=# SELECT name, setting, sourcefile, sourceline
FROM pg_settings
WHERE name IN ('log_min_duration_statement','max_connections')
ORDER BY name;
            name            | setting |                sourcefile                 | sourceline
----------------------------+---------+-------------------------------------------+------------
 log_min_duration_statement | 500     | /pgData/pgsql17/data/postgresql.auto.conf |          3
 max_connections            | 250     | /pgData/pgsql17/data/postgresql.auto.conf |          4
(2 rows)

postgres=#

Verify from the OS shell:

-- postgresql.conf
[postgres@pgdb01 ~]$ cat /pgData/pgsql17/data/postgresql.conf|grep max_connections
max_connections = 100                   # (change requires restart)
[postgres@pgdb01 ~]$

-- postgresql.auto.conf
[postgres@pgdb01 ~]$ cat /pgData/pgsql17/data/postgresql.auto.conf|grep max_connections
max_connections = '250'
[postgres@pgdb01 ~]$

Both files mention max_connections with different values. The auto.conf value (250) is what PostgreSQL uses. This can cause confusion if you edit postgresql.conf and forget that auto.conf is overriding it.

9. Reset a Parameter Back to postgresql.conf’s Value

ALTER SYSTEM RESET removes the line from postgresql.auto.conf entirely, so PostgreSQL falls back to postgresql.conf (or the built-in default).

postgres=# SELECT name, setting, source, sourcefile
FROM pg_settings
WHERE name = 'log_min_duration_statement';
            name            | setting |       source       |                sourcefile
----------------------------+---------+--------------------+-------------------------------------------
 log_min_duration_statement | 500     | configuration file | /pgData/pgsql17/data/postgresql.auto.conf
(1 row)

postgres=#

postgres=# ALTER SYSTEM RESET log_min_duration_statement;
ALTER SYSTEM
postgres=#
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#
postgres=# SELECT name, setting, source, sourcefile
FROM pg_settings
WHERE name = 'log_min_duration_statement';
            name            | setting | source  | sourcefile
----------------------------+---------+---------+------------
 log_min_duration_statement | -1      | default |
(1 row)

postgres=#

[postgres@pgdb01 ~]$ cat /pgData/pgsql17/data/postgresql.auto.conf|grep log_min_duration_statement
[postgres@pgdb01 ~]$

Back to postgresql.conf. The auto.conf override is gone. source now reads configuration file and sourcefile points back to postgresql.conf.

10. Set a WRONG Value and See the Error

Scenario A: ALTER SYSTEM catches it immediately

postgres=# ALTER SYSTEM SET shared_buffers = 'not_a_number';
ERROR:  invalid value for parameter "shared_buffers": "not_a_number"
postgres=#

ALTER SYSTEM validates most values up front. The bad value is rejected immediately — nothing is written to auto.conf, nothing to reload.

Scenario B: Hand-edit the file (the real-world “fat-finger” scenario)

# Someone accidentally edits postgresql.conf directly:

[postgres@pgdb01 ~]$ cp /pgData/pgsql17/data/postgresql.conf /pgData/pgsql17/data/postgresql.conf.bkp
[postgres@pgdb01 ~]$


[postgres@pgdb01 ~]$ echo "work_mem = 'banana'" >> /pgData/pgsql17/data/postgresql.conf
[postgres@pgdb01 ~]$


# Then reloads:
[postgres@pgdb01 ~]$ psql -c "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

[postgres@pgdb01 ~]$

Check the PostgreSQL log:

[postgres@pgdb01 ~]$ tail -5 /pgData/pgsql17/data/log/postgresql-Fri.log
2026-09-25 13:53:14.494 +08 [3428] ERROR:  invalid value for parameter "shared_buffers": "not_a_number"
2026-09-25 13:53:14.494 +08 [3428] STATEMENT:  ALTER SYSTEM SET shared_buffers = 'not_a_number';
2026-09-25 13:56:38.363 +08 [3390] LOG:  received SIGHUP, reloading configuration files
2026-09-25 13:56:38.364 +08 [3390] LOG:  invalid value for parameter "work_mem": "banana"
2026-09-25 13:56:38.365 +08 [3390] LOG:  configuration file "/pgData/pgsql17/data/postgresql.conf" contains errors; unaffected changes were applied
[postgres@pgdb01 ~]$

# Manually edit and update right Value
[postgres@pgdb01 ~]$ cat /pgData/pgsql17/data/postgresql.conf | grep work_mem
work_mem = '4MB'
[postgres@pgdb01 ~]$

[postgres@pgdb01 ~]$ psql -c "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

[postgres@pgdb01 ~]$
[postgres@pgdb01 ~]$ tail -5 /pgData/pgsql17/data/log/postgresql-Fri.log
2026-09-25 13:53:14.494 +08 [3428] STATEMENT:  ALTER SYSTEM SET shared_buffers = 'not_a_number';
2026-09-25 13:56:38.363 +08 [3390] LOG:  received SIGHUP, reloading configuration files
2026-09-25 13:56:38.364 +08 [3390] LOG:  invalid value for parameter "work_mem": "banana"
2026-09-25 13:56:38.365 +08 [3390] LOG:  configuration file "/pgData/pgsql17/data/postgresql.conf" contains errors; unaffected changes were applied

-- New output

2026-09-25 14:03:56.537 +08 [3390] LOG:  received SIGHUP, reloading configuration files
[postgres@pgdb01 ~]$

For a sighup-context bad value: PostgreSQL logs the error but keeps the previous good value running. The server does not crash. Fix the typo and reload again.

For a postmaster-context bad value: If you restart with a malformed listen_addresses or shared_buffers, the server will fail to start entirely. The log will show a fatal parse error.

11. Find Every Parameter Pending a Restart

This is the single most useful query for a DBA before scheduling a restart window. It tells you exactly which staged changes are waiting.

postgres=# SELECT
    name,
    setting AS current_running_value,
    context,
    sourcefile
FROM pg_settings
WHERE pending_restart = true
ORDER BY name;
 name | current_running_value | context | sourcefile
------+-----------------------+---------+------------
(0 rows)

postgres=# ALTER SYSTEM SET max_connections=300;  <---- static parameter
ALTER SYSTEM
postgres=# 

postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#

postgres=# SELECT
    name,
    setting AS current_running_value,
    context,
    sourcefile
FROM pg_settings
WHERE pending_restart = true
ORDER BY name;
      name       | current_running_value |  context   |                sourcefile
-----------------+-----------------------+------------+-------------------------------------------
 max_connections | 250                   | postmaster | /pgData/pgsql17/data/postgresql.auto.conf
(1 row)

postgres=#

postgres=# SELECT count(*) AS still_pending
FROM pg_settings
WHERE pending_restart = true;
 still_pending
---------------
             1  <----- means something require to restart 
(1 row)

postgres=#
postgres=# SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name = 'max_connections';
      name       | setting |  context   | pending_restart
-----------------+---------+------------+-----------------
 max_connections | 250     | postmaster | t  <----------------
(1 row)

postgres=#

DBA tip: Run this query before every planned restart. If it returns zero rows, there is nothing staged and the restart won’t change any configuration. This prevents unnecessary downtime.

12. Restart and Confirm pending_restart Clears

# Restart the server (OS shell)
[postgres@pgdb01 ~]$ pg_ctl restart -D $PGDATA
waiting for server to shut down.... done
server stopped
waiting for server to start....2026-09-25 14:16:29.475 +08 [3600] LOG:  redirecting log output to logging collector process
2026-09-25 14:16:29.475 +08 [3600] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb01 ~]$

After the restart, reconnect and verify:

postgres=# SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name = 'max_connections';

      name       | setting |  context   | pending_restart
-----------------+---------+------------+-----------------
 max_connections | 300     | postmaster | f   <-------------
(1 row)

postgres=#

-- Confirm nothing else is pending
postgres=# SELECT count(*) AS still_pending
FROM pg_settings
WHERE pending_restart = true;

 still_pending
---------------
             0  <--------- means nothing pending for restart
(1 row)

postgres=#

postgres=# SELECT
    name,
    setting AS current_running_value,
    context,
    sourcefile
FROM pg_settings
WHERE pending_restart = true
ORDER BY name;
 name | current_running_value | context | sourcefile
------+-----------------------+---------+------------
(0 rows)

postgres=#

max_connections is now 300 and pending_restart = false. The restart applied the staged change.

13. Parameter Scope — Cluster / Database / User / Session

PostgreSQL lets you set most dynamic parameters at four different scopes, each narrower than the last. The narrowest scope always wins.

Precedence (narrowest wins):

SESSION  >  ROLE+DATABASE  >  ROLE  >  DATABASE  >  CLUSTER  >  built-in default

13a. CLUSTER Level (ALTER SYSTEM)

Affects the whole server — every database, every user, every session.

postgres=# SHOW work_mem;
 work_mem
----------
 4MB <------
(1 row)

postgres=# ALTER SYSTEM SET work_mem = '8MB';
ALTER SYSTEM
postgres=#
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#
postgres=# SHOW work_mem;
 work_mem
----------
 8MB   <----------------------
(1 row)

postgres=#

13b. DATABASE Level (overrides cluster for one database)

Only sessions connecting to this specific database get this value.

postgres=# ALTER DATABASE postgres SET work_mem = '16MB';
ALTER DATABASE
postgres=#

-- Reconnect to "postgres" to see it take effect
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/run/postgresql" at port "5432".
postgres=#

postgres=#
postgres=# SHOW work_mem;
 work_mem
----------
 16MB  <-------------
(1 row)

postgres=# SELECT d.datname, r.rolname, s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_database d ON d.oid = s.setdatabase
LEFT JOIN pg_roles r ON r.oid = s.setrole
ORDER BY d.datname NULLS LAST, r.rolname NULLS LAST;
 datname  | rolname |    setconfig
----------+---------+-----------------
 postgres |         | {work_mem=16MB}
(1 row)

postgres=# \c raj
You are now connected to database "raj" as user "postgres".
raj=#
raj=# SHOW work_mem;
 work_mem
----------
 8MB  <---------------
(1 row)

raj=#

13c. USER / ROLE Level (overrides cluster and database for one role)

postgres=# ALTER ROLE postgres SET work_mem = '32MB';
ALTER ROLE
postgres=#

-- Combine ROLE + DATABASE for maximum specificity
postgres=# ALTER ROLE postgres IN DATABASE postgres SET work_mem = '64MB';
ALTER ROLE
postgres=#

-- Check the catalog
postgres=# SELECT d.datname, r.rolname, s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_database d ON d.oid = s.setdatabase
LEFT JOIN pg_roles r ON r.oid = s.setrole
ORDER BY d.datname NULLS LAST, r.rolname NULLS LAST;
 datname  | rolname  |    setconfig
----------+----------+-----------------
 postgres | postgres | {work_mem=64MB}    <--------- ROLE(USER)+DB level
 postgres |          | {work_mem=16MB}    <--------- Database level (overrides cluster for one database)
          | postgres | {work_mem=32MB}    <--------- ROLE(USER LEVEL)  overrides cluster and database for one role
(3 rows)

postgres=#

postgres=# \c raj
You are now connected to database "raj" as user "postgres".
raj=#
raj=# SHOW work_mem;
 work_mem
----------
 8MB  <----- CLUSTER LEVEL WE SET
(1 row)

raj=#

The postgres role connecting to the postgres database gets 64MB (the combined ROLE+DATABASE row wins over the role-only row, which wins over the database-only row, which wins over the cluster-wide 8MB).

PostgreSQL checks limits in this order (from most specific to least specific):

  1. ROLE + DATABASE together = 64MB 
  2. ROLE only = 32MB
  3. DATABASE only = 16MB
  4. Whole cluster = 8MB 

13d. SESSION Level (overrides everything, but only for this connection)

[postgres@lxceftsgvdb01 ~]$ psql -d postgres -U postgres
postgres=# SHOW work_mem;
 work_mem
----------
 64MB  <-----------------
(1 row)

postgres=#


-- SET lasts for this session only
postgres=# SET work_mem = '128MB';
SET
postgres=#

postgres=# SHOW work_mem;
 work_mem
----------
 128MB <----------------
(1 row)

postgres=#

-- SET LOCAL is even narrower — lasts only for the current transaction
postgres=# BEGIN;
BEGIN
postgres=*# SET LOCAL work_mem = '256MB';
SET
postgres=*# SHOW work_mem;
 work_mem
----------
 256MB   <---------------- Changed from 128MB to 256MB
(1 row)

postgres=*# COMMIT;
COMMIT
postgres=#
postgres=# SHOW work_mem;
 work_mem
----------
 128MB  <--------------- Came back to session setting 128MB
(1 row)

postgres=#

After COMMIT: work_mem reverted to 128MB (the session-level SET), not 256MB. SET LOCAL only lives inside the transaction. Disconnecting entirely drops the session-level SET and falls back to the role/database/cluster chain.

13e. See the Full Precedence Chain at a Glance

postgres=# SELECT name, setting AS effective_value, source, sourcefile
FROM pg_settings
WHERE name = 'work_mem';
   name   | effective_value | source  | sourcefile
----------+-----------------+---------+------------
 work_mem | 131072          | session |
(1 row)

postgres=#

The source column tells you which level actually won for the current session:

  • session → a SET / SET LOCAL you ran yourself
  • database → ALTER DATABASE ... SET
  • user → ALTER ROLE ... SET
  • override → ALTER SYSTEM (cluster level, via postgresql.auto.conf)
  • configuration file → plain postgresql.conf
  • default → built-in default, nothing overrode it

13f. Classify Every Parameter by Settable Scope

postgres=# SELECT name,
       context,
       CASE context
         WHEN 'internal'           THEN 'READ-ONLY (compile-time)'
         WHEN 'postmaster'         THEN 'CLUSTER only (restart required)'
         WHEN 'sighup'             THEN 'CLUSTER only (reload required)'
         WHEN 'backend'            THEN 'CLUSTER/DATABASE/ROLE (connect time only)'
         WHEN 'superuser-backend'  THEN 'CLUSTER/DATABASE/ROLE (connect time, superuser)'
         WHEN 'superuser'          THEN 'CLUSTER/DATABASE/ROLE/SESSION/TXN (superuser)'
         WHEN 'user'               THEN 'CLUSTER/DATABASE/ROLE/SESSION/TXN (any user)'
       END AS settable_scope
FROM pg_settings
ORDER BY context, name
LIMIT 15;
         name          | context  |              settable_scope
-----------------------+----------+-------------------------------------------
 ignore_system_indexes | backend  | CLUSTER/DATABASE/ROLE (connect time only)
 post_auth_delay       | backend  | CLUSTER/DATABASE/ROLE (connect time only)
 block_size            | internal | READ-ONLY (compile-time)
 data_checksums        | internal | READ-ONLY (compile-time)
 data_directory_mode   | internal | READ-ONLY (compile-time)
 debug_assertions      | internal | READ-ONLY (compile-time)
 huge_pages_status     | internal | READ-ONLY (compile-time)
 in_hot_standby        | internal | READ-ONLY (compile-time)
 integer_datetimes     | internal | READ-ONLY (compile-time)
 max_function_args     | internal | READ-ONLY (compile-time)
 max_identifier_length | internal | READ-ONLY (compile-time)
 max_index_keys        | internal | READ-ONLY (compile-time)
 segment_size          | internal | READ-ONLY (compile-time)
 server_encoding       | internal | READ-ONLY (compile-time)
 server_version        | internal | READ-ONLY (compile-time)
(15 rows)

postgres=#

Quick counts by scope:

-- Fully flexible (all 5 levels)
postgres=# SELECT count(*) AS fully_flexible
FROM pg_settings WHERE context IN ('user','superuser');
 fully_flexible
----------------
            191
(1 row)

postgres=#


-- Cluster-only (conf file or ALTER SYSTEM, never per-session)
postgres=# SELECT count(*) AS cluster_only
FROM pg_settings WHERE context IN ('postmaster','sighup');
 cluster_only
--------------
          163
(1 row)

postgres=#


-- Connect-time only (database/role, but not SET mid-session)
postgres=# SELECT count(*) AS connect_time_only
FROM pg_settings WHERE context IN ('backend','superuser-backend');
 connect_time_only
-------------------
                 6
(1 row)

postgres=#

14. Quick Cheatsheet

TaskCommand
Find config fileSHOW config_file;
View a settingSHOW work_mem; or SELECT * FROM pg_settings WHERE name = 'work_mem';
See non-default settingsSELECT * FROM pg_settings WHERE source != 'default';
See raw file contentsSELECT * FROM pg_file_settings;
Static vs DynamicSELECT name, context FROM pg_settings WHERE context = 'postmaster';
Change at cluster levelALTER SYSTEM SET work_mem = '64MB';
Change at database levelALTER DATABASE mydb SET work_mem = '64MB';
Change at role levelALTER ROLE myuser SET work_mem = '64MB';
Change at session levelSET work_mem = '128MB';
Change for one transactionSET LOCAL work_mem = '256MB';
Remove auto.conf overrideALTER SYSTEM RESET work_mem;
Apply without restartSELECT pg_reload_conf();
Find pending restartsSELECT * FROM pg_settings WHERE pending_restart = true;
Apply with restartpg_ctl restart -D $PGDATA
Check for config errorsSELECT * FROM pg_file_settings WHERE error IS NOT NULL;

15. Sample postgresql.conf

-- Server Config
# =============================================================================
# RAM   : 128 GB & CPU(s): 8  & OS: Redhat Linux 8
# =============================================================================

-- Sample parameters

# =============================================================================
# CONNECTION SETTINGS
# =============================================================================
listen_addresses = '*'                          
port = 6543                                     
max_connections = 1500
unix_socket_permissions = 0700

# =============================================================================
# SSL/TLS SECURITY SETTINGS
# =============================================================================
ssl = on
ssl_ca_file = '/pgData/pgsql17/certs/root.crt'
ssl_cert_file = '/pgData/pgsql17/certs/server.crt'
ssl_key_file = '/pgData/pgsql17/certs/server.key'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'       
ssl_prefer_server_ciphers = on
ssl_min_protocol_version = 'TLSv1.2'

# =============================================================================
# MEMORY SETTINGS
# =============================================================================
shared_buffers = 32GB                           # min 128kB
work_mem = 2796kB                               # min 64kB
maintenance_work_mem = 2GB                      # min 1MB
dynamic_shared_memory_type = posix              
effective_cache_size = 96GB
temp_tablespaces = 'pg_tstmp'

# =============================================================================
# PARALLEL EXECUTION & WORKER PROCESSES
# =============================================================================
effective_io_concurrency = 200                  # 1-1000; 0 disables prefetching
max_worker_processes = 8                        # (change requires restart)
max_parallel_workers_per_gather = 4             # taken from max_parallel_workers
max_parallel_maintenance_workers = 4            # taken from max_parallel_workers
max_parallel_workers = 8                        # maximum number of max_worker_processes that

# =============================================================================
# QUERY PLANNING & OPTIMIZATION
# =============================================================================
random_page_cost = 1.1                          # same scale as above
default_statistics_target = 1500                # range 1-10000

# =============================================================================
# WRITE-AHEAD LOGGING (WAL) SETTINGS
# =============================================================================
wal_level = replica                             
wal_buffers = 16MB                              # min 32kB, -1 sets based on shared_buffers
wal_log_hints = on                              
full_page_writes = on                           
max_wal_senders = 10                            # max number of walsender processes
wal_keep_size = 128000                          # in megabytes; 0 disables
hot_standby = on                                # 'off' disallows queries during recovery

# =============================================================================
# CHECKPOINT & WAL SIZE SETTINGS
# =============================================================================
checkpoint_completion_target = 0.9              
max_wal_size = 4GB
min_wal_size = 1GB

# =============================================================================
# ARCHIVING 
# =============================================================================
archive_mode = on                               
archive_command = 'rsync -a %p /pgArch/pgsql17/arch/%f'

#archive_command = '/pgBackup/pgsql17/backup/arch_copy.sh %p %f && pgbackup'
#archive_cleanup_command = 'pg_archivecleanup /pgArch/pgsql17/arch/ %r'

# =============================================================================
# LOGGING SETTINGS
# =============================================================================
logging_collector = on                          
log_destination = 'stderr'
log_directory = '/pgLog/pgsql17/logs'
log_filename = 'postgres-%Y-%m-%d_%HMS.log'
log_file_mode = 0600
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_hostname = on
log_line_prefix = '%m [%p] user=%u,database=%d,app_name=%a client_host=%h '
log_lock_waits = on
log_statement = 'ddl'
log_min_duration_statement = 2000   # log statements taking longer than 2000ms
log_timezone = 'Asia/Singapore'

# Optional
#log_truncate_on_rotation = on       # truncate rather than append on rotation
#log_rotation_age = 1d               # Automatic rotation by time
#log_rotation_size = 0               # Automatic rotation by size (0=disabled)
#log_duration = off

# ========================================================================
# AUTOVACUUM SETTINGS
# ========================================================================
autovacuum_naptime = 10min
autovacuum_vacuum_threshold = 10000
autovacuum_analyze_threshold = 10000
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 600

# ========================================================================
# QUERY TRACKING SETTINGS
# ========================================================================
track_activities = on
track_counts = on
track_io_timing = off
track_activity_query_size = 10240

# ========================================================================
# QUERY PLANNING SETTINGS
# ========================================================================
seq_page_cost = 1.0                 # measured on an arbitrary scale
random_page_cost = 2.0              # same scale as above
cpu_tuple_cost = 0.03               # same scale as above
from_collapse_limit = 20
join_collapse_limit = 20            # 1 disables collapsing of explicit JOIN clauses
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you
Rajasekhar Amudala
Email: br8dba@gmail.com
LinkedIn: linkedin.com/in/rajasekhar-amudala