DBAtlas · Database operations atlas

Review memory settings (shared_buffers, work_mem, maintenance_work_mem)

PostgreSQLParameters

Command

SELECT name,
       current_setting(name) AS value,
       source,
       context
FROM pg_settings
WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem', 'hash_mem_multiplier',
               'maintenance_work_mem', 'autovacuum_work_mem', 'max_connections',
               'max_parallel_workers_per_gather', 'huge_pages', 'temp_buffers', 'wal_buffers')
ORDER BY name;

-- Rough ceilings: work_mem x max_connections and the autovacuum memory ceiling
SELECT pg_size_pretty(pg_size_bytes(current_setting('work_mem'))
                      * current_setting('max_connections')::int) AS work_mem_x_max_connections,
       pg_size_pretty(pg_size_bytes(current_setting('maintenance_work_mem'))
                      * current_setting('autovacuum_max_workers')::int) AS autovacuum_mem_ceiling;

-- Trace of operations that did not fit in work_mem: temp files per database
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_size, stats_reset
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY temp_bytes DESC;

What it does

Shows memory-related parameters with their value, source (default/configuration file) and change context (postmaster = restart); rough memory ceilings for work_mem and autovacuum; and temp file volume per database.

When to use it

When building a new server, after adding RAM, after an OOM killer event, or when heavy temp file usage is seen.

Caution

work_mem is allocated not per connection but per sort/hash NODE (times hash_mem_multiplier for hashes) and separately for each parallel worker; a complex query can use several multiples of work_mem, so the computed product is a rough warning, not an upper bound. Typical starting points: shared_buffers ~25% of RAM (restart required), effective_cache_size ~50-75% (a planner hint only, allocates nothing), maintenance_work_mem 512MB-2GB. With autovacuum_work_mem = -1, each autovacuum worker may use maintenance_work_mem. If temp_bytes is high, raise work_mem per role or query, not globally. With large shared_buffers on Linux, consider huge_pages.

Open in DBAtlas →

Related