PostgreSQL Developer Settings Query: How to View Database Configuration Options

calendar Sep 5, 2025 / · 3 min read · postgresql database sql queries configuration developer tools system catalog database admin postgres pg_settings  ·
Share on: linkedin copy

PostgreSQL Developer Settings Query: How to View Database Configuration Options

This PostgreSQL query allows database administrators and developers to examine developer-specific configuration settings in their PostgreSQL database. The query targets the pg_settings system catalog to retrieve detailed information about parameters that affect development and debugging processes.

Query Purpose

The SQL code retrieves developer-related configuration parameters from PostgreSQL's system catalog. This information helps developers understand current database settings that impact development workflows, debugging capabilities, and performance tuning options specifically designed for development environments.

Code Breakdown

Query: PostgreSQL Database code to view developer configuration settings from pg_settings table

 1SELECT
 2    name,
 3    setting,
 4    -- category,
 5    vartype,
 6    short_desc,
 7    -- enumvals,
 8    source,
 9    min_val,
10    max_val,
11    boot_val,
12    reset_val,
13    -- not available on PostgreSQL < 9.5
14    pending_restart
15FROM
16    pg_settings
17WHERE
18    -- Developer Options
19    category ILIKE '%Developer%';

The query selects specific columns from the pg_settings view:

  • name - The configuration parameter name
  • setting - Current value of the parameter
  • vartype - Data type of the parameter (bool, integer, string, etc.)
  • short_desc - Brief description of what the parameter does
  • source - How the parameter was set (default, configuration file, command line, etc.)
  • min_val - Minimum allowed value for numeric parameters
  • max_val - Maximum allowed value for numeric parameters
  • boot_val - Default value when PostgreSQL starts
  • reset_val - Value that would be used if reset
  • pending_restart - Whether a server restart is needed for changes to take effect

Key Configuration Categories

The query filters results using category ILIKE '%Developer%' to focus on developer-specific settings. These typically include parameters for debugging, logging verbosity, query planning assistance, and development-oriented features that might not be suitable for production environments.

Practical Applications

Database administrators use this query to audit development environment configurations, ensure proper debugging settings are enabled, and verify that development-specific parameters are appropriately configured. The information helps optimize PostgreSQL instances for development workflows while maintaining awareness of settings that should differ between development and production environments.

Version Compatibility

The query includes a comment noting that pending_restart is not available in PostgreSQL versions before 9.5. This column indicates whether configuration changes require a database server restart to take effect, which is crucial information for planning maintenance windows.

Performance Insights

Understanding developer settings helps identify configuration parameters that might impact query performance during development. Parameters like log_statement, log_min_duration_statement, and various debugging options can significantly affect database performance and should be carefully managed.

References

Posts in this series