PostgreSQL Connection Settings Query - Complete Guide to pg_settings

calendar Sep 4, 2025 / · 3 min read · postgresql database-administration system-views connection-settings configuration sql-queries database-tuning pg-settings  ·
Share on: linkedin copy

PostgreSQL Connection Settings Query - Complete Guide to pg_settings

This PostgreSQL query provides database administrators with essential connection configuration parameters by querying the pg_settings system view. Understanding these settings is crucial for optimizing database connectivity and troubleshooting connection-related issues.

Query Breakdown

The provided query retrieves connection-specific configuration parameters from PostgreSQL's system catalog:

 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    -- Connections and Authentication / Connection Settings
19    category ILIKE '% / Connection Settings%';

Purpose and Functionality

This query serves multiple administrative purposes by extracting connection-related configuration parameters that control how PostgreSQL handles client connections. The pg_settings view provides a comprehensive interface to PostgreSQL's configuration parameters, making it invaluable for database tuning and troubleshooting.

Column Explanations

name: The configuration parameter name (e.g., max_connections, port)

setting: The current value of the parameter as it appears in the configuration

vartype: The data type of the parameter (bool, integer, real, string, enum)

short_desc: A brief description explaining the parameter's purpose

source: Indicates where the setting originates (default, configuration file, command line, etc.)

min_val and max_val: The minimum and maximum allowed values for numeric parameters

boot_val: The default value compiled into the server

reset_val: The value that would be used if the parameter were reset

pending_restart: Shows whether a server restart is required for changes to take effect

Key Connection Parameters

The query typically returns critical connection settings including:

  • max_connections: Maximum number of concurrent connections
  • port: TCP port number for database connections
  • listen_addresses: IP addresses where the server listens for connections
  • unix_socket_directories: Directory locations for Unix domain sockets
  • tcp_keepalives_idle: TCP keepalive settings for connection maintenance

Practical Applications

Performance Monitoring: Identify connection limits and current utilization patterns

Security Auditing: Review connection authentication and access control settings

Capacity Planning: Determine optimal connection pool sizes and server capacity

Troubleshooting: Diagnose connection failures and timeout issues

Advanced Usage Tips

Filter results by specific parameter types using the vartype column to focus on particular configuration categories. The source column helps identify which settings come from configuration files versus defaults, essential for configuration management.

Monitor the pending_restart column to identify changes requiring server restarts before taking effect. This is particularly important in production environments where restart windows are limited.

Version Compatibility

The pending_restart column requires PostgreSQL 9.5 or later. For earlier versions, remove this column from the SELECT list or the query will fail.

References

Posts in this series