Tuning PostgreSQL: como ajustar shared_buffers, work_mem e effective_cache_size

Configure shared_buffers, work_mem, effective_cache_size e checkpoints do PostgreSQL para servidores com muita RAM e NVMe, com pgtune e huge pages.

Para que serve

A configuração padrão do PostgreSQL foi pensada para rodar em qualquer máquina, inclusive com pouca memória. Em um servidor dedicado com 128 GB ou mais de RAM e NVMe, ela deixa desempenho na mesa. Este guia de tuning PostgreSQL mostra quais parâmetros mudar primeiro, com valores de partida, e como conferir se a mudança ajudou. Vale para PostgreSQL 15 a 18 no Debian 12/13 e Ubuntu 24.04.

Pré-requisitos

• Saber quanta RAM o PostgreSQL terá de fato. Se ele roda em uma VM do Proxmox, conte a RAM da VM, não a do servidor físico.
• Saber se o banco divide a máquina com outros serviços (aplicação, PHP, Java) e quanto eles consomem.
• Acesso ao usuário postgres e uma janela para reiniciar o serviço (alguns parâmetros exigem restart).

1. Gere uma base com o pgtune

O pgtune calcula valores a partir da versão, RAM, CPUs, tipo de disco e tipo de carga (web, OLTP, data warehouse). Use o resultado como ponto de partida, não como verdade final. Em seguida, revise cada item com as explicações abaixo.

2. Parâmetros principais do tuning PostgreSQL

3. Aplique a configuração

No Debian/Ubuntu, o postgresql.conf já inclui o diretório conf.d. Crie um arquivo separado, o que facilita revisões e upgrades. Exemplo para uma VM com 64 GB de RAM dedicada ao PostgreSQL 17, 16 vCPUs e disco em NVMe:

cat > /etc/postgresql/17/main/conf.d/90-tuning.conf <<'EOF'
shared_buffers = 16GB
effective_cache_size = 44GB
work_mem = 32MB
maintenance_work_mem = 2GB
max_wal_size = 16GB
min_wal_size = 2GB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_io_concurrency = 200
max_connections = 200
max_worker_processes = 16
max_parallel_workers = 8
max_parallel_workers_per_gather = 4
huge_pages = try
log_temp_files = 0
EOF

Troque 17 pela sua versão (pg_lsclusters mostra). Também é possível usar ALTER SYSTEM SET parametro = valor;, que grava em postgresql.auto.conf; evite misturar os dois métodos para o mesmo parâmetro, porque o auto.conf tem prioridade.

No PostgreSQL 18, effective_io_concurrency já vem com 16 e existe o novo io_method (padrão worker) para I/O assíncrono. Mantenha o padrão até medir; io_uring é uma opção a testar no Linux.

4. Configure huge pages (recomendado acima de 8 GB de shared_buffers)

Com shared_buffers grande, huge pages reduzem o consumo de memória da tabela de páginas e melhoram a estabilidade. Descubra quantas páginas são necessárias (PostgreSQL 15 ou mais novo), com o serviço parado ou já configurado:

sudo -u postgres /usr/lib/postgresql/17/bin/postgres \
  -D /var/lib/postgresql/17/main \
  -c config_file=/etc/postgresql/17/main/postgresql.conf \
  -C shared_memory_size_in_huge_pages

Reserve um pouco mais que o valor retornado (ex.: retornou 8400, use 8500):

echo "vm.nr_hugepages = 8500" > /etc/sysctl.d/60-hugepages.conf
sysctl --system
grep HugePages_ /proc/meminfo

Com huge_pages = try, o PostgreSQL usa huge pages se houver e inicia normalmente se não houver. Depois de confirmar que funciona, você pode trocar para on para falhar alto caso a reserva suma.

5. Reinicie e confira

systemctl restart postgresql@17-main
sudo -u postgres psql -c "SELECT name, setting, unit, pending_restart
  FROM pg_settings WHERE name IN ('shared_buffers','work_mem',
  'effective_cache_size','max_wal_size','huge_pages');"

Se pending_restart aparecer como t, o parâmetro ainda não entrou em vigor. Parâmetros como work_mem e random_page_cost valem com um simples SELECT pg_reload_conf();; shared_buffers, max_connections e huge_pages exigem restart.

Como saber se funcionou

• Taxa de acerto de cache:

  SELECT datname, round(blks_hit*100.0/nullif(blks_hit+blks_read,0),2) AS hit_pct,
         temp_files, pg_size_pretty(temp_bytes) AS temp
  FROM pg_stat_database WHERE datname NOT LIKE 'template%';

  Em OLTP, hit_pct acima de 99% é o esperado. temp_bytes crescendo rápido indica work_mem curto para alguma consulta (o log_temp_files = 0 registra quais).
• Checkpoints: no PostgreSQL 17/18, SELECT num_timed, num_requested FROM pg_stat_checkpointer; (em versões anteriores, as colunas ficam em pg_stat_bgwriter). Se num_requested for muito maior que num_timed, aumente max_wal_size.
• Memória do sistema: free -h deve mostrar folga e nenhum uso de swap crescente.

Problemas comuns

O PostgreSQL não sobe depois de aumentar shared_buffers

Veja journalctl -u postgresql@17-main e /var/log/postgresql/. Com huge_pages = on e reserva insuficiente, o serviço falha. Volte para try ou aumente vm.nr_hugepages.

O servidor começou a usar swap ou o OOM killer matou o postgres

Soma de shared_buffers + (conexões ativas × work_mem × operações) + outros serviços passou da RAM. Reduza work_mem, use pool de conexões e, em VM, desative o ballooning de memória.

Mudei os parâmetros e nada melhorou

Configuração não compensa consulta ruim ou índice faltando. Identifique as piores consultas com pg_stat_statements (artigo sobre monitorar banco de dados) e veja o artigo sobre índices. Lentidão que piora com o tempo pode ser bloat: veja autovacuum e bloat no PostgreSQL.

Perguntas frequentes

Posso colocar shared_buffers em 75% da RAM?

Não é recomendado. O PostgreSQL depende também do cache do sistema operacional, e um shared_buffers enorme faz os mesmos dados ficarem em cache duas vezes. 25% é a recomendação da documentação como ponto de partida.

O pgtune é confiável?

É um bom começo e segue as regras da documentação. Ele não conhece suas consultas; ajuste depois de observar o banco em produção.

Preciso reiniciar para mudar work_mem?

Não. Basta SELECT pg_reload_conf(); ou definir por sessão, usuário ou banco.

Qual o valor certo de random_page_cost em NVMe?

Valores entre 1.0 e 1.1 são os mais usados, porque a leitura aleatória em NVMe custa quase o mesmo que a sequencial.

Leitura complementar

• PostgreSQL: Resource Consumption
• PostgreSQL Wiki: Tuning Your PostgreSQL Server
• PostgreSQL: Managing Kernel Resources (huge pages)

Precisa de ajuda?

Se houver suspeita de problema de memória ou disco no servidor físico, abra um ticket com a saída de free -h, dmesg | tail -50 e a descrição do sintoma.