PostgreSQL Memory Settings Query: Monitor Database Memory Configuration

calendar Sep 10, 2025 / · 3 min read · postgresql database administration memory management system monitoring performance tuning sql queries database configuration pg_settings  ·
Share on: linkedin copy

PostgreSQL Memory Settings Query: Monitor Database Memory Configuration

This PostgreSQL query helps database administrators monitor and analyze memory-related configuration settings in their database instance. By querying the pg_settings system catalog view, you can quickly identify current memory parameters, their values, and restart requirements.

Query Breakdown

The query used:

 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    -- Resource Usage / Memory
19    category ILIKE '%Memory%';

Purpose

This query serves multiple administrative purposes for PostgreSQL database management. It retrieves comprehensive information about memory-related configuration parameters, helping administrators understand current memory allocation, identify optimization opportunities, and plan configuration changes that may require database restarts.

Column Explanations

name: The configuration parameter name (e.g., shared_buffers, work_mem)

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

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

short_desc: A brief description explaining what the parameter controls

source: How the parameter was set (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 PostgreSQL

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

pending_restart: Indicates whether changes require a server restart to take effect

Key Memory Parameters

When you run this query, you'll typically see important memory settings like:

  • shared_buffers: Controls the amount of memory allocated for shared buffer cache
  • work_mem: Sets the amount of memory used for internal sort operations and hash tables
  • maintenance_work_mem: Specifies memory allocation for maintenance operations like VACUUM
  • effective_cache_size: Informs the planner about available kernel and PostgreSQL cache memory
  • huge_pages: Controls the use of huge memory pages on supported systems

Practical Applications

Database administrators can use this query results to audit current memory configurations, identify parameters that need server restarts when changed, compare current settings with default values, and document memory allocation for capacity planning.

Performance Insights

Understanding memory settings is crucial for PostgreSQL performance optimization. Parameters like shared_buffers should typically be set to 25% of total system RAM, while work_mem affects query performance for operations involving sorting and hashing. The pending_restart column helps administrators plan maintenance windows for configuration changes.

Usage Tips

Run this query regularly during performance tuning sessions. Compare the setting values with your system's total memory to ensure optimal allocation. Pay attention to the source column to understand configuration inheritance. Use the min_val and max_val information when planning parameter changes.

References

Posts in this series