Revisar as configurações de memória (shared_buffers, work_mem, maintenance_work_mem)

Comando

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;

O que faz

Mostra os parâmetros relacionados à memória com valor, origem (padrão/arquivo de configuração) e contexto de alteração (postmaster = restart); tetos aproximados de memória para work_mem e autovacuum; e o volume de arquivos temporários por banco.

Quando usar

Ao montar um novo servidor, após adicionar RAM, após um evento do OOM killer ou quando se observa uso intenso de arquivos temporários.

Atenção

O work_mem não é alocado por conexão, mas por NÓ de sort/hash (vezes hash_mem_multiplier para hashes) e separadamente para cada parallel worker; uma consulta complexa pode usar vários múltiplos do work_mem, então o produto calculado é um aviso aproximado, não um limite superior. Pontos de partida típicos: shared_buffers ~25% da RAM (exige restart), effective_cache_size ~50-75% (apenas uma dica para o planner, não aloca nada), maintenance_work_mem 512MB-2GB. Com autovacuum_work_mem = -1, cada worker do autovacuum pode usar maintenance_work_mem. Se temp_bytes estiver alto, aumente o work_mem por role ou consulta, não globalmente. Com shared_buffers grande no Linux, considere huge_pages.