PostgreSQL offers two distinct system views, pg_file_settings and pg_settings, to help administrators inspect and manage database configurations. While they overlap in subject matter, they serve different purposes regarding how configuration data is sourced and displayed.

pg_file_settings View

This view parses the contents of the configuration files (like postgresql.conf) currently active on the disk. It does not necessarily reflect the values currently running in memory, but rather what is defined in the text files.

Key Characteristics:

• Source: Reads directly from configuration files.
• Validation: Indicates weather the settings found in the files were successfully applied during the last reload or startup.
• Debugging: Highlights syntax errors or invalid values within the configuration files.

Relevant Columns:

• sourcefile: The absolute path of the configuration file.
• sourceline: The specific line number within that file.
• seqno: A sequence number to manage order, especially when include directives are used.
• name: The configuration variable name.
• setting: The value defined in the file.
• applied: A boolean flag; false indicates the setting was ignored or failed.
• error: Contains the error message if the setting was not applied.

Usage Examples:

To list all configuration directives found in files:

SELECT name, setting, sourcefile, sourceline 
FROM pg_file_settings 
ORDER BY seqno;

To find specific issues where a configuration setting was rejected:

SELECT name, setting, error 
FROM pg_file_settings 
WHERE applied = false;

pg_settings View

This view provides a real-time look at the parameters affecting the current database session. It aggregates values from multiple sources (defaults, config files, command-line arguments) and shows the runtime value.

Key Characteristics:

• Source: Reflects the current state of the running server instance.
• Metadata: Contains rich metadata, including descriptions, data types, and acceptable value ranges.
• Interactivity: Allows for the dynamic modification of certain parameters using UPDATE statements (depending on the context).

Relevant Columns:

• name: The parameter identifier.
• setting: The current runtime value.
• unit: The unitt of measurement (e.g., ms, kB).
• vartype: The data type (bool, integer, string, etc.).
• context: The level at which the parameter can be changed (e.g., user, superuser, postmaster).
• short_desc: A brief explanation of the parameter's function.
• source: How the value was determined (e.g., database, default, configuration file).
• pending_restart: true if changing this parameter requires a full server restart.

Usage Examples:

To retrieve the current runtime configuration for a specific variable:

SELECT name, setting, source, context 
FROM pg_settings 
WHERE name = 'work_mem';

To check which parameters require a restart to take effect:

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

Comparison Overveiw

1. Data Origin:

   • pg_file_settings: Static file analysis. It shows what is written in postgresql.conf.
   • pg_settings: Dynamic runtime analysis. It shows what the server is actually using right now.

2. Scope of Information:

   • pg_file_settings: Focuses on file location, line numbers, and parsing errors.
   • pg_settings: Focuses on current values, data types, limits (min/max), and descriptions.

3. Primary Use Case:

   • pg_file_settings: Use this to debug typos or invalid values in your configuration files before or after a reload.
   • pg_settings: Use this to monitor active performance parameters, understand variable constraints, or temporarily alter session behavior.