PostgreSQL Resource Settings Query Guide

calendar Mar 18, 2026 / · 5 min read · postgresql database administration sql queries configuration performance tuning resource management pg_settings postgres dba  ·
Share on: linkedin copy

PostgreSQL Resource Settings Query Guide

This PostgreSQL query retrieves all resource usage configuration settings from the pg_settings system view. Resource settings control how PostgreSQL allocates memory, disk, and CPU resources across queries, background processes, and maintenance operations.

Purpose and Overview

Resource settings are among the most impactful configuration parameters for PostgreSQL performance. Allocating too little memory leads to excessive disk I/O; allocating too much can cause out-of-memory errors on the host. This query surfaces all resource-related parameters so administrators can audit and tune them in one place.

SQL Script

 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    -- Resource Usage / Asynchronous Behavior
20    -- Resource Usage / Cost-Based Vacuum Delay
21    -- Resource Usage / Background Writer
22    -- Resource Usage / Disk
23    -- Resource Usage / Kernel Resources
24    category ILIKE '%Resource%';

Code Breakdown

Filter Condition

1WHERE category ILIKE '%Resource%'

Matches all subcategories under the Resource Usage group:

  • Resource Usage / Memory — memory allocation parameters
  • Resource Usage / Asynchronous Behavior — parallel worker and async I/O limits
  • Resource Usage / Cost-Based Vacuum Delay — vacuum throttling settings
  • Resource Usage / Background Writer — bgwriter activity controls
  • Resource Usage / Disk — temporary file size limits
  • Resource Usage / Kernel Resources — OS-level resource limits

Selected Columns

name: The configuration parameter name such as shared_buffers or work_mem.

setting: The current active value.

vartype: The data type (bool, integer, real, string, enum).

short_desc: A brief description of what the parameter controls.

source: Where the value originates — default, configuration file, or command line.

min_val / max_val: Allowed range for numeric parameters.

boot_val: Compiled-in default value.

reset_val: Value used if the parameter is reset.

pending_restart: Whether a server restart is needed for a change to take effect.

Key Resource Parameters

Memory

shared_buffers — The primary memory allocation for caching data pages. Commonly set to 25% of total system RAM. Requires a server restart to change.

work_mem — Memory available to each sort or hash operation within a query. Note that a single complex query can use work_mem multiple times. Set too high with many concurrent sessions, it can exhaust system memory.

maintenance_work_mem — Memory available for maintenance operations such as VACUUM, CREATE INDEX, and ALTER TABLE. Can be set higher than work_mem since fewer maintenance operations run concurrently.

temp_buffers — Memory used for temporary tables within a session. Default is 8 MB per session.

effective_cache_size — Not a memory allocation but the planner's estimate of available OS and PostgreSQL cache. Affects index scan cost estimates.

huge_pages — Controls whether PostgreSQL requests huge memory pages from the OS (try, on, or off). Can improve performance on large shared_buffers allocations.

Asynchronous Behavior

max_worker_processes — Maximum number of background worker processes. Must be large enough to accommodate parallel workers and background jobs.

max_parallel_workers — Maximum number of workers that can participate in parallel query execution at any time.

max_parallel_workers_per_gather — Maximum parallel workers used for a single parallel query operation.

max_parallel_maintenance_workers — Maximum parallel workers for maintenance operations like CREATE INDEX.

effective_io_concurrency — Estimated number of concurrent I/O operations the disk subsystem can handle. Affects prefetch behaviour. Set higher for SSDs.

Cost-Based Vacuum Delay

vacuum_cost_delay — Time in milliseconds that vacuum sleeps between rounds when the cost limit is exceeded. Setting above 0 throttles vacuum to reduce its impact on query performance.

vacuum_cost_limit — Accumulated cost at which vacuum sleeps. Higher values reduce sleep frequency but increase vacuum impact.

vacuum_cost_page_hit — Cost of vacuuming a page found in the buffer cache.

vacuum_cost_page_miss — Cost of vacuuming a page not in the buffer cache (requires a disk read).

vacuum_cost_page_dirty — Cost of modifying a page that was clean, requiring an additional write.

Background Writer

bgwriter_delay — Sleep time between background writer rounds. Controls how often the bgwriter flushes dirty pages.

bgwriter_lru_maxpages — Maximum pages the background writer flushes per round.

bgwriter_lru_multiplier — Multiplier controlling how aggressively the bgwriter anticipates page demand.

bgwriter_flush_after — Forces the OS to flush dirty pages to disk after this many kilobytes have been written by the bgwriter.

Disk

temp_file_limit — Maximum total size of temporary files a session can create. Set to -1 for unlimited. Constrains runaway sort and hash operations.

Kernel Resources

max_files_per_process — Maximum number of files a backend process can have open simultaneously. Should be less than the OS ulimit -n value.

Practical Applications

Memory Tuning

Review shared_buffers, work_mem, and maintenance_work_mem together with total system memory to ensure allocations are balanced and will not cause OOM conditions under peak load.

Vacuum Throttling

If vacuum is impacting query response times, increase vacuum_cost_delay and reduce vacuum_cost_limit to throttle it. If tables are growing bloated faster than vacuum can keep up, reduce the delay.

Parallel Query Tuning

Check max_parallel_workers, max_parallel_workers_per_gather, and effective_io_concurrency to ensure parallel query is enabled and configured appropriately for the server's CPU and storage tier.

Pre-Maintenance Review

Before running large VACUUM, REINDEX, or CREATE INDEX operations, temporarily increase maintenance_work_mem at the session level to speed up the operation.

Version Compatibility

Requires PostgreSQL 9.5 or later due to the pending_restart column. Tested on PostgreSQL 9.5, 9.6, 10.x, 11.x, 12.x, and 13.0. Several parameters such as max_parallel_workers require PostgreSQL 10 or later.

Best Practices

Size shared_buffers conservatively: Setting it too large can cause the OS to swap, which is far worse than leaving more memory for the OS file cache.

Calculate total work_mem exposure: Total possible memory use is work_mem × max_connections × operations_per_query. Divide work_mem by this factor to avoid overcommitting.

Monitor temp file usage: Query pg_stat_database for temp_files and temp_bytes to detect queries that are spilling to disk and may benefit from a work_mem increase.

Use session-level overrides for maintenance: Rather than raising maintenance_work_mem globally, set it for a specific session with SET maintenance_work_mem = '1GB' before running a large index build.

References

Posts in this series