Postgres em VPS: os parâmetros que realmente importam

O Postgres sai da caixa configurado para subir em qualquer lugar — inclusive em um computador de 1 GB de memória. Isso é uma decisão sensata do projeto e uma péssima configuração para o seu servidor.

A boa notícia é que meia dúzia de parâmetros responde por quase todo o ganho possível. O resto da lista de ajuste que circula por aí produz diferenças irrelevantes, e alguns itens populares chegam a piorar.

Memória: os três parâmetros que decidem

# postgresql.conf, para uma máquina de 8 GB dedicada ao banco
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 32MB
maintenance_work_mem = 512MB

Vale entender o que cada um faz, porque a confusão entre eles é comum.

shared_buffers é a memória que o Postgres reserva para si. A regra de bolso é 25% da RAM. Aumentar muito além disso raramente ajuda, porque o sistema operacional também mantém cache dos mesmos arquivos — e você acaba com duas cópias das mesmas páginas.

effective_cache_size não reserva nada. É uma estimativa que o planejador usa para decidir entre varrer a tabela e usar índice. Informar um valor realista — algo como 75% da RAM — faz o planejador escolher índice com mais frequência. É o parâmetro de melhor retorno por esforço, e é gratuito.

work_mem é por operação de ordenação ou hash, não por conexão. Uma consulta complexa pode usar várias vezes esse valor, e vinte conexões simultâneas multiplicam de novo. É o parâmetro que mais causa problema quando exagerado: 256 MB parece razoável até que trinta consultas simultâneas tentem alocar 7 GB.

Comece conservador e suba com base em evidência, não em palpite:

-- Consultas que precisaram ordenar em disco
SELECT queryid, calls, temp_blks_written
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC LIMIT 10;

Escrita em arquivo temporário é o sinal de que work_mem está apertado para aquela consulta específica. Muitas vezes a resposta certa não é aumentar o parâmetro global, mas criar o índice que elimina a ordenação.

Conexões: menos é mais

O instinto é aumentar max_connections. Quase sempre é o caminho errado.

Cada conexão no Postgres é um processo do sistema operacional, com memória própria e custo de troca de contexto. Centenas de conexões simultâneas fazem o servidor gastar mais tempo gerenciando processos que executando consulta.

max_connections = 100

Se a aplicação precisa de mais, a resposta é um *pooler* — um intermediário que mantém poucas conexões reais com o banco e as compartilha entre muitos clientes:

# pgbouncer.ini
[databases]
app = host=127.0.0.1 port=5432 dbname=app

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

O pool_mode = transaction é o que entrega o ganho real: a conexão volta ao pool ao fim de cada transação, e não ao fim da sessão. A ressalva é que recursos ligados à sessão — instruções preparadas, tabelas temporárias, LISTEN — deixam de funcionar como o esperado, e a aplicação precisa estar de acordo com isso.

Disco e checkpoint

random_page_cost = 1.1
effective_io_concurrency = 200
checkpoint_completion_target = 0.9
max_wal_size = 4GB
wal_compression = on

O random_page_cost merece explicação. O padrão de 4.0 vem da época do disco mecânico, quando leitura aleatória custava muito mais que sequencial. Em SSD a diferença é pequena, e manter o valor antigo faz o planejador evitar índice sem motivo. Baixar para 1.1 é um dos ajustes mais eficazes em máquina moderna.

max_wal_size maior espaça os checkpoints, reduzindo picos de escrita. O custo é uma recuperação mais demorada após queda — troca que costuma valer em servidor de aplicação.

Medir antes de ajustar mais

Dois números dizem se a memória está bem dimensionada:

-- Taxa de acerto do cache: quer acima de 0.99
SELECT sum(blks_hit)::float / nullif(sum(blks_hit) + sum(blks_read), 0) AS cache_hit
FROM pg_stat_database;

-- Uso de índice por tabela
SELECT relname, seq_scan, idx_scan,
       n_live_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC LIMIT 10;

Taxa de acerto abaixo de 0,95 indica shared_buffers insuficiente para o conjunto de dados quente. Tabela grande com seq_scan alto e idx_scan baixo indica índice faltando — e índice faltando é, com folga, a causa mais comum de banco lento.

Nenhum ajuste de parâmetro compensa uma consulta que varre um milhão de linhas. Vale começar por aqui antes de mexer em memória.

Achar a consulta que dói

Ative a extensão de estatísticas, que é a ferramenta mais útil do conjunto:

shared_preload_libraries = 'pg_stat_statements'

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT round(total_exec_time::numeric, 0) AS ms_total,
       calls,
       round(mean_exec_time::numeric, 1) AS ms_media,
       left(query, 80) AS consulta
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

Ordene por tempo total, não por média. Uma consulta de 8 ms executada cem mil vezes pesa mais que uma de 3 segundos executada dez vezes — e é bem mais fácil de otimizar.

Complemente com o log de consultas lentas:

log_min_duration_statement = 1000
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0

O log_lock_waits é subestimado: ele registra quando uma consulta espera por trava, que é a explicação de boa parte das lentidões intermitentes que não aparecem em EXPLAIN.

Autovacuum não é opcional

O Postgres não sobrescreve linha ao atualizar — ele grava uma versão nova e marca a antiga como morta. O autovacuum limpa isso. Quando ele não dá conta, a tabela incha, as consultas ficam lentas e o disco cresce sem explicação.

SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup, 0), 1) AS pct_morto,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC LIMIT 10;

Percentual alto de linhas mortas ou last_autovacuum antigo indicam que ele está atrasado. Em tabela com muita escrita, vale ser mais agressivo pontualmente:

ALTER TABLE pedidos SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

Desativar o autovacuum "para melhorar o desempenho" é um erro que custa caro e aparece semanas depois, quando a tabela já está inchada demais para limpar sem janela de manutenção.

Ler um plano de execução sem se perder

Achar a consulta lenta é metade; entender por que ela é lenta é a outra. O EXPLAIN ANALYZE responde, e há três coisas a procurar antes de qualquer outra.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM pedidos WHERE cliente_id = 42 AND status = 'aberto';

Seq Scan em tabela grande é o achado mais comum. Significa varredura completa — o Postgres leu todas as linhas para devolver poucas. Em tabela pequena isso é correto e até mais rápido que índice; em tabela com milhões de linhas, é o problema.

Divergência entre estimativa e realidade. O plano mostra rows=12 seguido de actual rows=48000. Quando o planejador erra a estimativa por ordens de grandeza, ele escolhe a estratégia errada — e a causa costuma ser estatística desatualizada:

ANALYZE pedidos;

Ordenação em disco, que aparece como Sort Method: external merge Disk. É o sinal de work_mem insuficiente para aquela operação, discutido acima.

Para o exemplo, o índice que resolve cobre as duas colunas do filtro:

CREATE INDEX CONCURRENTLY idx_pedidos_cliente_status
  ON pedidos (cliente_id, status);

O CONCURRENTLY é essencial em produção: sem ele, a criação trava escrita na tabela durante todo o processo. Com ele, o índice é construído sem bloquear, ao custo de demorar mais e exigir uma segunda passada.

A ordem das colunas no índice composto importa: coloque primeiro a de maior seletividade, aquela que sozinha já reduz mais o conjunto.

O que não vale o esforço

• `fsync = off`. Melhora muito e corrompe o banco em qualquer queda de energia. Aceitável em máquina de teste descartável, nunca em produção.
• Copiar `postgresql.conf` da internet. Os valores dependem da sua RAM, do seu disco e da sua carga.
• Ajustar tudo de uma vez. Sem medir entre mudanças, você não sabe qual ajudou — nem qual atrapalhou.

Antes de qualquer alteração

Boa parte desses parâmetros exige reinício, e alguns podem impedir o banco de subir se o valor for incompatível com a memória disponível. Confira o que precisa reiniciar:

SELECT name, setting, context FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','max_connections');

context = postmaster significa reinício. Vale tirar um snapshot antes — um Postgres que não sobe por shared_buffers maior que a RAM é rápido de consertar, mas só se você souber que foi isso.

E lembre que ajuste de parâmetro não substitui backup: a rotina de dump e teste de restauração continua sendo o que separa um incidente de uma perda definitiva.

Vale fechar com a ordem de prioridade, porque ela contraria o instinto. O ganho de desempenho em Postgres vem, quase sempre nesta sequência: índice faltando primeiro, consulta mal escrita depois, e só então parâmetro de memória. Um servidor com shared_buffers perfeito e uma tabela de dois milhões de linhas sem índice continua lento — enquanto o inverso, configuração padrão com índices corretos, atende bem uma carga considerável.

Meça antes, ajuste um parâmetro por vez, e guarde o número anterior. Sem comparação, otimização de banco vira folclore rapidamente.