GUC Parameters, ALTER SYSTEM, Session Settings, and Configuration Scope

Learning outcomes

PostgreSQL calls its server configuration variables Grand Unified Configuration (GUC) parameters in project terminology and code. The important operational question is not just “what is the value?” but where did that value come from, who is allowed to change it, when can it change, and how long does the change last?

1. Start with pg_settings as your configuration debugger

SHOW work_mem; is useful, but pg_settings tells the story behind the value. It exposes the unit, context, source, source file/line where applicable, reset value, allowed session behavior, and whether a restart is pending.

SELECT name, setting, unit, context, vartype,       source, sourcefile, sourceline,       reset_val, pending_restartFROM pg_catalog.pg_settingsWHERE name IN ('application_name','work_mem','log_min_duration_statement','shared_buffers')ORDER BY name;

The context tells you how difficult a parameter is to change. Important contexts include internal, postmaster, sighup, superuser-backend, backend, superuser, and user. Do not infer changeability from a parameter’s name; query the target server.

2. Scope and lifetime: the same parameter can have different defaults

Database/role defaults are applied when a new session starts; changing them does not retroactively rewrite existing sessions. This explains many “I changed it but SHOW still says…” incidents.

3. A low-risk demonstration with statement_timeout

statement_timeout is ideal for scope demonstrations because a short value can cancel intentionally slow statements without changing storage or durability. Use the disposable lab and choose values that will not disrupt unrelated work.

SHOW statement_timeout;SET statement_timeout = '2s';SHOW statement_timeout;-- This should be canceled after roughly two seconds in this session.SELECT pg_sleep(5);RESET statement_timeout;SHOW statement_timeout;

The timeout error is expected. It proves that this session’s effective GUC changed. It does not prove a cluster-wide setting changed.

4. SET LOCAL: make exceptional settings self-expire

BEGIN;SHOW statement_timeout;SET LOCAL statement_timeout = '1500ms';SHOW statement_timeout;SELECT pg_sleep(3);  -- expected cancellation in this transactionROLLBACK;SHOW statement_timeout;  -- back to the session/default value

Transaction-local configuration is powerful because cleanup is tied to transaction end. It is often safer for one report, migration step, or lock-sensitive operation than changing a global default. The application still needs disciplined transaction handling so “local” really stays bounded.

5. Role and database defaults: policy for new sessions

The Chapter 01 application role is a useful target for a defensive default. For example, you might set a statement timeout for ServiceHub application sessions. Do this as an administrator, then open a new application connection to verify.

ALTER ROLE servicehub_app IN DATABASE servicehub_labSET statement_timeout = '15s';-- Open a NEW servicehub_app connection, then:SHOW statement_timeout;SELECT current_user, current_database();-- Revert after the lab:ALTER ROLE servicehub_app IN DATABASE servicehub_labRESET statement_timeout;

This is a policy example, not a universal recommendation for 15 seconds. Real timeout values come from application SLOs, query classes, retry semantics, and migration/reporting requirements.

6. ALTER SYSTEM: persistent, cluster-wide, and not transactional

ALTER SYSTEM writes parameter settings to postgresql.auto.conf. Those values are read in addition to postgresql.conf and override matching values there. A reload applies settings that can reload; restart-only parameters wait for server restart.

Because ALTER SYSTEM modifies a file and cannot be rolled back as part of a SQL transaction, PostgreSQL does not allow it inside a transaction block. It also requires superuser or parameter-specific ALTER SYSTEM privilege.

-- Administrator-only disposable lab example.ALTER SYSTEM SET log_min_duration_statement = '750ms';SELECT pg_reload_conf();SELECT name, setting, source, sourcefile, sourceline, context, pending_restartFROM pg_catalog.pg_settingsWHERE name = 'log_min_duration_statement';-- Revert the ALTER SYSTEM entry and reload.ALTER SYSTEM RESET log_min_duration_statement;SELECT pg_reload_conf();

After reset, PostgreSQL falls back to the next-highest applicable source. That might be postgresql.conf, a command-line option, or another source—not necessarily a factory default.

7. Source precedence can surprise you

Suppose you edit postgresql.conf and set log_min_duration_statement = '2s', but SHOW still reports 750ms. If an ALTER SYSTEM entry exists, the file edit is not the effective source. Instead of repeatedly editing files, query:

SELECT name, setting, source, sourcefile, sourceline, context, pending_restartFROM pg_catalog.pg_settingsWHERE name = 'log_min_duration_statement';SELECT sourcefile, sourceline, name, setting, applied, errorFROM pg_catalog.pg_file_settingsWHERE name = 'log_min_duration_statement'ORDER BY sourcefile, sourceline;

This pair answers both questions: what the files contain and what the running session/server considers effective. Add database/role/session inspection when those scopes are relevant.

8. pending_restart: a safety signal, not an error by itself

If a postmaster-context parameter changes in a configuration file and the server reloads, the running value remains unchanged. pending_restart = true indicates that a restart is needed for the file change to take effect. That is expected behavior, not proof that the configuration is invalid.

SELECT name, setting, context, source, sourcefile, sourcelineFROM pg_catalog.pg_settingsWHERE pending_restartORDER BY name;

In production, treat that list as change-management state. A restart affects availability and in-flight work; it should be scheduled, verified, and observed.

9. Deliberately wrong approach: configure the same parameter everywhere

An undisciplined environment sets work_mem in postgresql.conf, then ALTER SYSTEM, then ALTER ROLE, then an application startup option, and finally a session SET. Each layer may be valid, but operators no longer know which one is intentional. An incident responder sees a value and cannot explain its provenance.

The repair is ownership of configuration:

• Choose one managed source for cluster-wide baseline settings.
• Use role/database defaults only when they represent intentional workload policy.
• Use session/transaction overrides for bounded exceptions.
• Record ALTER SYSTEM changes and avoid having multiple automation systems edit postgresql.auto.conf.
• Verify with pg_settings.source and file views after every change.

10. Hands-on lab: change, inherit, override, and revert

1. Record statement_timeout and its source as administrator and application role.
2. Set a role-in-database default for servicehub_app, reconnect, and verify the new default.
3. Inside that new session, use SET statement_timeout to override the role default and prove source becomes a session-level source.
4. Use RESET statement_timeout and confirm the role/database default returns.
5. Use SET LOCAL inside a transaction, then rollback and confirm automatic reversion.
6. Reset the role-in-database default and reconnect to prove cleanup.

SELECT name, setting, unit, source, context, reset_val, pending_restartFROM pg_catalog.pg_settingsWHERE name = 'statement_timeout';

11. Production judgment and next bridge

Configuration is part of the system’s data. Track who changes it, source control where appropriate, validate files, inspect effective provenance, and define a rollback/revert path before changes. In regulated or highly available environments, configuration drift is an incident precursor in its own right.

Lesson 5 turns this discipline into observability: logging destinations, structured formats, correlation fields, startup diagnostics, and a baseline profile that makes future incidents explainable.

Authoritative references

• PostgreSQL 18 — Setting Parameters
• PostgreSQL 18 — pg_settings
• PostgreSQL 18 — pg_file_settings
• PostgreSQL 18 — ALTER SYSTEM
• PostgreSQL 18 — SET
• PostgreSQL 18 — ALTER ROLE
• PostgreSQL 18 — ALTER DATABASE