Postgres pg_settings: Reading and Reasoning About Configuration at Runtime

A surprising fraction of Postgres incidents come down to the operator and the database disagreeing about what configuration is in effect. The operator edited postgresql.conf, restarted nothing, and is now confused about why the change is not visible. Or a parameter was set via ALTER SYSTEM in someone else's session months ago and quietly overrides the conf file. Or a per-database or per-role setting masks the cluster-wide value for some connections and not others. The view that resolves all of these questions is pg_settings, and it is one of the load-bearing tools for running Postgres in production.

What pg_settings actually contains

The view has one row per configuration parameter that Postgres knows about, which is several hundred rows in a modern version. The columns that matter most often are name, setting (the current effective value), unit, category, short_desc, context (when changes take effect), source (where the current value came from), reset_val (what RESET would set it to), boot_val (the compiled-in default), pending_restart, and sourcefile and sourceline for parameters loaded from files.

The context column is the one most worth memorizing. The values are internal (compile-time, cannot be changed), postmaster (requires full server restart), sighup (reload via SIGHUP suffices), backend (set at connection time, cannot change mid-session), superuser-backend (same but superuser-only), user (set per-session with SET), and superuser (per-session, superuser-only). The difference between postmaster and sighup is the difference between a planned restart and a routine reload, and getting it wrong is the difference between five seconds and a maintenance window.

The source column tells you where the current value came from. The values include default (compiled-in), configuration file (postgresql.conf), command line (passed to postgres at start), environment variable, database (ALTER DATABASE), user (ALTER USER), client (per-session SET), and session (SET LOCAL). The source matters when you are trying to understand why a setting has a particular value despite what postgresql.conf says.

The everyday diagnostic queries

The query for "what is currently overriding the default for any parameter" is:

SELECT name, setting, source, sourcefile, sourceline
FROM pg_settings
WHERE source NOT IN ('default', 'override')
ORDER BY source, name;

This produces a short list of everything that was deliberately changed from the compiled-in default. Reading this list during an incident is the fastest way to understand how the cluster differs from a stock Postgres install.

The query for "which parameters are waiting for a restart to take effect" is:

SELECT name, setting, pending_restart
FROM pg_settings
WHERE pending_restart = true;

If this query returns rows, someone changed a postmaster-context parameter via ALTER SYSTEM or postgresql.conf edit, and the change will apply on next restart. Forgetting about pending restarts is one of the subtle ways that incidents accumulate latent configuration that bites later.

The query for "what would this parameter be without the override" is:

SELECT name, setting, reset_val, boot_val
FROM pg_settings
WHERE name = 'work_mem';

The reset_val is what RESET parameter_name would set the value to (typically the configuration-file value), and boot_val is the compiled-in default. The three columns together explain the full inheritance chain for any parameter.

Per-database and per-role overrides

The pg_settings view shows the value in effect for the current session. Different sessions can have different values for the same parameter, depending on which database they connected to, which role they authenticated as, and whether the session has issued SET commands. The full picture requires joining pg_settings with pg_db_role_setting:

SELECT d.datname, r.rolname, unnest(s.setconfig) AS setting
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;

This query shows all per-database and per-role configuration overrides in the cluster. The rows where datname is null are role-level overrides applying to any database; rows where rolname is null are database-level overrides applying to any role; rows where both are populated are the most specific override and apply to that role connecting to that database.

The override resolution order is compiled-in default, then postgresql.conf, then ALTER SYSTEM, then database-level, then role-level, then database-and-role-level, then session-level via SET. Each later level overrides earlier ones for parameters in the appropriate context. Understanding this chain is the difference between "my parameter is not taking effect" and "my parameter is being overridden at a specific level I forgot about."

The SET LOCAL discipline

Per-session changes via SET persist until the connection ends. Per-transaction changes via SET LOCAL persist only until the current transaction commits or rolls back. The distinction matters because SET in a connection-pooled environment leaves state visible to the next session that gets that connection, while SET LOCAL cleans up automatically.

The pattern for tuning a specific query without affecting the rest of the application is:

BEGIN;
SET LOCAL work_mem = '256MB';
SET LOCAL statement_timeout = '30s';
SELECT ... -- the expensive query
COMMIT;

The transaction-scoped settings revert automatically on COMMIT or ROLLBACK. This is the right default for application code that needs to override defaults for specific operations.

The reload-without-restart trick

For parameters with sighup context, you can change postgresql.conf and signal the postmaster to reload:

SELECT pg_reload_conf();

This applies all sighup-context changes without disconnecting any sessions or restarting any backends. The postmaster-context changes are queued (visible as pending_restart = true) for the next restart, but the rest of the new configuration takes effect immediately.

The reload is also how you apply ALTER SYSTEM changes that are not postmaster-context. ALTER SYSTEM SET writes to postgresql.auto.conf but does not itself reload; the change becomes effective when something triggers a reload (SIGHUP, pg_reload_conf, or restart).

What pg_settings does not show

The view is for parameters Postgres knows about. Custom application-defined parameters (set via my_app.parameter_name convention) appear in pg_settings only after they have been set in the current session; they are not part of the configuration-parameter inventory in the usual sense.

Extension-defined parameters appear in pg_settings only after the extension's shared library is loaded. If you check pg_settings for auto_explain.log_min_duration without auto_explain being loaded, the row is not present. The library-load order during startup determines what appears in the view, which matters when diagnosing extension-related configuration questions.

Per-session SET commands change the current session's value but do not appear as overrides in any persistent sense; closing the session reverts the value. The source column shows session or client for these cases, which is the diagnostic that something was changed in this specific connection rather than via a cluster-wide mechanism.

Our SQLite baseline and Postgres migration plan

SQLite has no equivalent of pg_settings; configuration is largely compile-time or set via PRAGMA commands that take effect per-connection. Our four products (DocuMint, CronPing, FlagBit, WebhookVault) configure SQLite via PRAGMA on connection open, and the configuration is essentially uniform across all connections.

The Postgres migration plan includes a configuration audit endpoint that queries pg_settings for the parameters we care about and surfaces them via a health-check endpoint. The pattern is to make configuration drift visible operationally so that incidents do not start with "I had no idea this parameter was changed."

The deeper observation is that databases accumulate configuration over years of operation, and the divergence between what the team thinks is configured and what is actually configured is one of the reliable sources of incident surprises. The pg_settings view is the standard way to close that gap, and the discipline of reading it during incidents is one of the cheapest operational habits available.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.