Settings contexts, and planning a change from them

PostgreSQL 18 has 399 configuration parameters, and every one of them declares what it takes to change it. That declaration is the context column of pg_settings, and it is the single most useful piece of metadata in the whole configuration system, because it converts “can we change this?” from a discussion into a lookup.

The seven contexts

$ psql -U postgres -c 'SELECT context, count(*) FROM pg_settings GROUP BY context ORDER BY count(*) DESC'

      context      | count
-------------------+-------
user              |   151
sighup            |   104
postmaster        |    69
superuser         |    49
internal          |    20
superuser-backend |     4
backend           |     2
(7 rows)

The good news for change planning is in the distribution. 200 of the 399 parameters are user or superuser — settable live, in a session, with no coordination at all. Another 104 are sighup, needing only a reload. Only 69 parameters require a restart.

That ratio is worth carrying into conversations with change boards. The default assumption that “a database configuration change means an outage” is wrong for roughly five parameters out of six.

internal: the read-only ones

$ psql -U postgres -c "SELECT name, setting FROM pg_settings WHERE context = 'internal' ORDER BY name LIMIT 8"

         name          | setting
-----------------------+---------
block_size            | 8192
data_checksums        | on
data_directory_mode   | 0700
in_hot_standby        | off
integer_datetimes     | on
max_function_args     | 100
max_identifier_length | 63
segment_size          | 131072

These are reports, not controls. block_size is 8192 because the binary was compiled that way. data_checksums reflects what initdb decided, which is why the previous part’s lesson had to reach for pg_checksums to change it. in_hot_standby tells you whether this server is a standby.

Attempting to set one produces an error rather than a silent no-op, which is the correct behaviour and worth relying on.

The two backend contexts

These are the ones that surprise people, and they account for six parameters — including two that operators reach for constantly.

$ psql -U postgres -c "SELECT name, context FROM pg_settings WHERE context LIKE '%backend' ORDER BY context, name"

         name          |      context
-----------------------+-------------------
ignore_system_indexes | backend
post_auth_delay       | backend
jit_debugging_support | superuser-backend
jit_profiling_support | superuser-backend
log_connections       | superuser-backend
log_disconnections    | superuser-backend
(6 rows)

A backend-context parameter is read once, when a backend starts, and fixed for that backend’s lifetime. A reload updates the value the server will hand to future backends, and changes nothing about the ones already running.

Reading the context before proposing a change

# The change-planning query. Run it for the parameters you intend to touch.
psql -U postgres -c \
  "SELECT name, setting, unit, context, pending_restart
     FROM pg_settings
    WHERE name IN ('shared_buffers','work_mem','max_connections',
                   'log_min_duration_statement','max_wal_size',
                   'autovacuum_max_workers')
    ORDER BY context, name"

$ psql -U postgres -c 'SELECT name, setting, unit, context FROM pg_settings ORDER BY context, name'

            name            | setting | unit |  context
----------------------------+---------+------+------------
max_connections            | 100     |      | postmaster
shared_buffers             | 16384   | 8kB  | postmaster
autovacuum_max_workers     | 3       |      | sighup
max_wal_size               | 1024    | MB   | sighup
log_min_duration_statement | -1      | ms   | superuser
work_mem                   | 4096    | kB   | user

That single query turns six proposals into a plan: two of them can be done now, two at the next reload — which is also now, since a reload is not disruptive — and two need to be batched into whatever restart window comes next.

The version dimension

Contexts change between major versions, and a runbook that assumes one is version-specific whether or not it says so.

The clearest recent example is autovacuum_max_workers. On 18.6 it is sighup context — adjustable live, up to the autovacuum_worker_slots ceiling, which is itself postmaster and set at startup. That pairing of a live-adjustable setting with a restart-only ceiling is new in 18, and a runbook written against an older release will assume a restart is needed where it is not.

Check the context on the cluster in front of you rather than trusting a runbook’s memory of it:

SELECT name, setting, context FROM pg_settings
 WHERE name IN ('autovacuum_max_workers','autovacuum_worker_slots');

$ psql -U postgres -c "SELECT name, setting, context FROM pg_settings WHERE name LIKE 'autovacuum%worker%' ORDER BY name"

          name           | setting |  context
-------------------------+---------+------------
autovacuum_max_workers  | 3       | sighup
autovacuum_worker_slots | 16      | postmaster
(2 rows)

For an estate spanning versions, that difference decides whether an autovacuum emergency at 03:00 can be addressed without an outage. The runbook has to read the context rather than assert it, which is the same discipline as reading the file paths rather than hard-coding them.

Production discipline

1. Read context before writing the change request. Five parameters out of six do not need a restart, and the default assumption that they do costs real time.
2. Do reload-level changes separately and early. They are individually reversible in seconds; batching them into a restart window forfeits that.
3. Treat superuser-backend and backend as “new connections only”. Enabling connection logging during an incident says nothing about the sessions already open.
4. Do not assert a context in a runbook. It changes between major versions — autovacuum_max_workers moved from postmaster to sighup in 18 — so the runbook should read it.
5. Plan max_connections and max_locks_per_transaction together. They size the same shared structure and raising one without the other is how an out-of-shared-memory error appears weeks later.

Cross-course references

• Git, CI/CD & GitOps — Part CVI (Change management) covers stating the blast radius and rollback of a change, and the context column is the input to both for a database parameter.
• Ansible for Production Sysadmins — Part XVI (Handlers) covers triggering a reload rather than a restart when a template changes, which is the automated form of this lesson’s central distinction.
• Linux for Production Sysadmins — Part XXXVII (Resources) covers the kernel shared-memory limits that the postmaster parameters ultimately consume.

Quiz