Ajustando Parâmetros do postgresql.conf para Desempenho Ótimo de Leitura e Escrita

O PostgreSQL geralmente funciona de forma aceitável com as configurações padrão, mas "aceitável" pode se transformar em leituras lentas, picos de escrita ou latência aleatória quando o tráfego real chega. O arquivo postgresql.conf é onde você define o orçamento básico de recursos: quanta memória o PostgreSQL pode usar para cache compartilhado, quanto cada operação de consulta pode usar antes de transbordar para o disco, com que agressividade os checkpoints escrevem páginas sujas e quais dicas o planejador recebe sobre a máquina subjacente.

O erro que vejo com mais frequência é tratar o ajuste do PostgreSQL como uma lista de números mágicos. Alguém copia shared_buffers = 25% da RAM, aumenta work_mem para um valor grande, dobra max_connections e espera que o banco de dados fique mais rápido. Às vezes funciona. Às vezes, começa a fazer swap durante um job de relatório ou atinge um limite durante os checkpoints.

A maneira mais segura é ajustar a partir dos sintomas. As leituras estão lentas porque o conjunto de trabalho não está em cache? Os relatórios estão transbordando ordenações para o disco? As escritas estão se acumulando durante os checkpoints? Muitas conexões de aplicativos estão competindo por memória? Este guia aborda os parâmetros que geralmente importam primeiro, com exemplos que você pode adaptar em vez de copiar cegamente.

Compreendendo os Parâmetros Principais de Memória

O gerenciamento eficiente de memória é fundamental para sistemas de banco de dados de alto desempenho. O PostgreSQL utiliza várias áreas de memória, sendo duas das mais críticas o shared_buffers para armazenar em cache dados acessados com frequência e o work_mem para operações internas de consulta.

shared_buffers

shared_buffers é, sem dúvida, um dos parâmetros de memória mais importantes para ajustar. Ele define a quantidade de memória dedicada que o PostgreSQL usa para armazenar em cache blocos de dados. Esses blocos incluem dados de tabelas, dados de índices e catálogos do sistema. Quando uma consulta solicita dados, o PostgreSQL primeiro verifica o shared_buffers. Se os dados forem encontrados lá (um acerto de cache), eles são recuperados muito mais rápido do que se tivessem que ser lidos do disco.

Impacto no Desempenho

• Desempenho de Leitura: Um valor maior de shared_buffers aumenta a probabilidade de acertos de cache, reduzindo significativamente a E/S de disco para cargas de trabalho com muitas leituras. Isso se traduz em respostas de consulta mais rápidas.
• Desempenho de Escrita: O shared_buffers também contém páginas "sujas" (blocos de dados que foram modificados, mas ainda não foram gravados no disco). Um buffer maior pode absorver mais escritas, permitindo que o sistema as agrupe em menos escritas, porém maiores, no disco, melhorando a taxa de transferência de escrita. No entanto, se for muito grande, pode levar a tempos de checkpoint mais longos e picos de E/S aumentados durante os checkpoints.

Diretrizes de Ajuste

• Ponto de Partida: Uma recomendação comum é definir shared_buffers como 25% da sua RAM física total. Por exemplo, em um servidor com 16 GB de RAM, shared_buffers seria 4 GB.
• Sistemas com Muita RAM: Em servidores com 64 GB+ de RAM, alocar 25% pode ser excessivo. O PostgreSQL também depende do cache do sistema de arquivos do sistema operacional. Além de um certo ponto, aumentar o shared_buffers pode oferecer retornos decrescentes, pois o cache do SO pode lidar efetivamente com grande parte do cache restante. Nesses casos, 15-20% pode ser suficiente, permitindo mais RAM para o cache do SO ou work_mem.
• Monitoramento: Fique de olho na taxa de acertos de cache em pg_stat_database, mas não trate uma porcentagem como prova de que tudo está saudável. Uma alta taxa de acertos pode esconder algumas consultas muito caras, e uma taxa mais baixa pode ser normal para jobs em lote que varrem tabelas grandes uma vez. Monitore também o comportamento do checkpoint e a latência do disco.

Exemplo de Configuração

Para definir shared_buffers como 4 GB no postgresql.conf:

shared_buffers = 4GB

Dica: Após alterar o shared_buffers, você deve reiniciar o serviço PostgreSQL para que as alterações entrem em vigor.

Uma verificação prática após alterá-lo:

SELECT
    datname,
    blks_hit,
    blks_read,
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');

Se você aumentou o shared_buffers e o aplicativo ainda espera por leituras de disco, o problema pode ser a forma da consulta, índices ausentes, inchaço da tabela ou um conjunto de trabalho maior que a memória. Mais cache não substitui um plano de execução melhor.

work_mem

work_mem especifica a quantidade máxima de memória a ser usada por uma operação de consulta (como uma ordenação ou tabela hash) antes de escrever dados temporários no disco. Essa memória é alocada por sessão, por operação. Se uma consulta complexa envolve várias operações de ordenação ou hash, ela pode consumir work_mem várias vezes dentro de uma única sessão.

Impacto no Desempenho

• Consultas Complexas: O work_mem impacta significativamente consultas que envolvem ORDER BY, GROUP BY, DISTINCT, junções hash e materialização. Quando uma operação de ordenação ou hash excede o limite de work_mem, o PostgreSQL transfere o excesso de dados para arquivos de disco temporários, levando a uma execução muito mais lenta.
• Concorrência: Como o work_mem é alocado por operação, por sessão, um valor global alto de work_mem combinado com muitas consultas complexas concorrentes pode esgotar rapidamente a RAM disponível, levando a swapping e degradação severa do desempenho.

Diretrizes de Ajuste

• Evite Valores Globais Excessivos: Não defina cegamente work_mem para um valor muito grande globalmente. Em vez disso, considere a concorrência típica de sua aplicação e o consumo de memória de suas consultas mais intensivas em recursos.
• Monitoramento de Transbordamentos para Disco: Use EXPLAIN ANALYZE em consultas problemáticas. Procure por linhas como Sort Method: external merge Disk: NkB ou HashAggregate batches: N (disk) que indicam que o work_mem era insuficiente e os dados foram transferidos para o disco.
• Ajuste Direcionado: Para relatórios específicos de longa duração ou jobs em lote, considere definir work_mem no nível da sessão antes de executar a consulta, em vez de globalmente. Isso permite maior uso de memória para essa consulta específica sem impactar outras sessões concorrentes.

Exemplo de Configuração

Para definir work_mem como 16 MB globalmente no postgresql.conf:

work_mem = 16MB

Para definir work_mem para uma sessão específica (por exemplo, no psql ou uma conexão de aplicativo):

SET work_mem = '256MB';
SELECT * FROM large_table ORDER BY some_column;

Aviso: Tenha cuidado ao aumentar o work_mem. Se 100 consultas concorrentes precisarem de 1 GB de work_mem cada, isso representa 100 GB de RAM! Sempre teste as alterações em um ambiente de homologação e monitore o uso de memória do seu sistema.

Uma maneira mais realista de usar work_mem é manter o valor global modesto e aumentá-lo apenas para sessões de relatórios conhecidas:

BEGIN;
SET LOCAL work_mem = '256MB';

SELECT customer_id, sum(total_amount)
FROM orders
WHERE created_at >= current_date - interval '90 days'
GROUP BY customer_id
ORDER BY sum(total_amount) DESC;

COMMIT;

Esse padrão é mais seguro do que aumentar o valor global para cada requisição web. Uma aplicação web com muitas consultas curtas precisa de uso de memória previsível. Um relatório noturno pode arcar com um orçamento maior por consulta.

Gerenciando Desempenho de Escrita e Durabilidade com Checkpoints

Checkpoints são um mecanismo crítico no PostgreSQL para garantir a durabilidade dos dados e gerenciar o log de transações (WAL - Write-Ahead Log). Eles sincronizam periodicamente os blocos de dados modificados do shared_buffers para o disco, marcando o ponto até o qual todas as alterações anteriores foram gravadas no armazenamento permanente.

checkpoint_timeout

checkpoint_timeout define o tempo máximo entre checkpoints automáticos de WAL. Checkpoints também ocorrem se a quantidade de segmentos WAL gerados desde o último checkpoint exceder max_wal_size.

Impacto no Desempenho

• Checkpoints Frequentes (checkpoint_timeout curto): Leva a picos de E/S mais frequentes à medida que as páginas sujas são liberadas para o disco. Embora isso reduza o tempo de recuperação após uma falha (menos WAL para reproduzir), pode impactar negativamente o desempenho da carga de trabalho ativa devido à atividade de escrita concentrada.
• Checkpoints Infrequentes (checkpoint_timeout longo): Reduz a frequência dos picos de E/S, levando a um desempenho mais suave durante a operação normal. No entanto, significa que mais dados podem precisar ser reproduzidos do WAL em caso de falha, resultando em tempos de recuperação de banco de dados mais longos. Também requer um max_wal_size maior para armazenar os segmentos WAL acumulados.

Diretrizes de Ajuste

• Equilíbrio: O objetivo é encontrar um equilíbrio entre desempenho contínuo suave e tempo de recuperação aceitável. Muitos sistemas de produção começam em torno de 5 a 15 minutos e depois ajustam com base no volume de WAL e nos objetivos de recuperação.
• Interação com max_wal_size: Esses dois parâmetros trabalham juntos. Se checkpoint_timeout for longo, mas max_wal_size for muito pequeno, os checkpoints serão acionados por max_wal_size com mais frequência do que por checkpoint_timeout. Ajuste max_wal_size para ser grande o suficiente para permitir que checkpoint_timeout seja o gatilho principal.
• Monitoramento: Use pg_stat_bgwriter para observar os contadores checkpoints_timed e checkpoints_req. checkpoints_timed deve ser significativamente maior que checkpoints_req (checkpoints solicitados devido a limites de tamanho do WAL) se o seu checkpoint_timeout for o gatilho principal.

Exemplo de Configuração

Para definir checkpoint_timeout como 10 minutos no postgresql.conf:

checkpoint_timeout = 10min
# Considere também ajustar max_wal_size de acordo
max_wal_size = 4GB # Exemplo, ajuste com base na carga de trabalho

Melhor Prática: Procure fazer com que os checkpoints sejam acionados principalmente por checkpoint_timeout em vez de max_wal_size. Isso fornece padrões de E/S mais previsíveis. Se max_wal_size estiver acionando checkpoints com frequência, aumente seu valor.

Verifique o padrão com:

SELECT
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint
FROM pg_stat_bgwriter;

Se checkpoints_req subir rapidamente, o PostgreSQL está fazendo checkpoint porque o WAL cresceu além de max_wal_size, não porque o temporizador expirou. Isso geralmente aparece como rajadas de E/S de escrita. Aumentar max_wal_size pode suavizar a carga de trabalho, mas também pode aumentar o tempo de recuperação de falhas porque mais WAL pode precisar ser reproduzido.

Configurações do Planejador e WAL que Vale a Pena Verificar

Três configurações geralmente ficam ao lado dos grandes parâmetros de memória e checkpoint.

effective_cache_size não é memória que o PostgreSQL aloca. É uma estimativa do planejador sobre quanto cache provavelmente está disponível entre os buffers compartilhados do PostgreSQL e o cache do sistema de arquivos do sistema operacional. Se estiver definido muito baixo, o planejador pode evitar varreduras de índice porque assume que as leituras serão caras. Em um servidor de banco de dados dedicado, um ponto de partida comum é uma grande fração da RAM, mas o valor certo depende do que mais é executado no host.

effective_cache_size = 12GB

maintenance_work_mem afeta operações de manutenção como CREATE INDEX, ALTER TABLE ADD FOREIGN KEY e VACUUM. Não é usado para ordenações de consultas normais da mesma forma que work_mem. Se as construções de índice forem dolorosamente lentas durante as janelas de manutenção, aumentar esse valor para a sessão pode ajudar:

SET maintenance_work_mem = '1GB';
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

wal_buffers controla a memória usada para registros WAL antes de serem gravados. O padrão geralmente é bom porque o PostgreSQL pode dimensioná-lo automaticamente, mas cargas de trabalho com muitas escritas e transações grandes podem se beneficiar da verificação se as gravações WAL são um gargalo antes de alterá-lo. Não o ajuste apenas porque aparece em uma lista de verificação.

Pontos de Partida Diferentes para Cargas de Trabalho Diferentes

Para uma aplicação web OLTP, a prioridade é latência estável sob concorrência. Mantenha work_mem conservador, use um pooler de conexões em vez de permitir milhares de conexões diretas e fique atento a esperas de bloqueio e planos ruins antes de culpar o shared_buffers. Um problema típico se parece com isso: um lançamento adiciona uma consulta de dashboard com ORDER BY created_at DESC em milhões de linhas, a consulta transborda para o disco e, de repente, toda requisição fica mais lenta porque o banco de dados está fazendo E/S de arquivo temporário. A correção pode ser um índice ou uma consulta mais restrita, não um work_mem global maior.

Para um banco de dados analítico ou de relatórios, grandes ordenações e agregações hash são normais. Você pode aumentar o work_mem para funções de relatório, aumentar maintenance_work_mem para trabalhos de índice em massa e aceitar consultas de execução mais longa. O risco é a concorrência. Dez analistas executando relatórios pesados em memória ao mesmo tempo podem consumir muito mais memória do que uma consulta de teste bem-sucedida sugeriu.

Para um sistema com muitas escritas, checkpoints e WAL importam mais. Se o aplicativo tiver paralisações periódicas de escrita, verifique se elas coincidem com os checkpoints. Observe também a latência do armazenamento, a saturação do disco WAL, a atividade do autovacuum e se transações longas estão impedindo a limpeza. Aumentar checkpoint_timeout sozinho não corrigirá um disco que não consegue acompanhar o volume médio de escrita.

Um Fluxo de Trabalho Simples de Ajuste

Comece registrando a configuração atual:

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem',
    'effective_cache_size',
    'checkpoint_timeout',
    'max_wal_size',
    'wal_buffers',
    'max_connections'
)
ORDER BY name;

Em seguida, capture os sintomas antes de alterar qualquer coisa. Salve um ou dois planos de consulta lentos com EXPLAIN (ANALYZE, BUFFERS). Verifique o log de arquivos temporários se suspeitar de transbordamentos:

log_temp_files = 0

Essa configuração registra cada arquivo temporário, portanto, use-a com cuidado em um sistema ocupado ou defina um limite, como 64MB. Se você vir muitos arquivos temporários grandes da mesma forma de consulta, ajuste a consulta, adicione um índice ou aumente o work_mem para essa carga de trabalho.

Altere uma coisa de cada vez. Algumas configurações exigem uma reinicialização, algumas precisam apenas de um recarregamento e algumas podem ser definidas por sessão. O PostgreSQL informa qual é qual:

SELECT name, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'checkpoint_timeout', 'max_wal_size');

O contexto postmaster significa reinicialização. sighup significa recarregamento. user significa que alterações no nível da sessão são possíveis.

Dicas Gerais de Ajuste e Melhores Práticas

• Ajuste Iterativo: Comece com pequenas alterações incrementais. Altere um parâmetro de cada vez, observe o impacto e ajuste ainda mais, se necessário. O ajuste não é uma tarefa única, mas um processo contínuo.
• Monitore Tudo: Utilize as visualizações de estatísticas internas do PostgreSQL (pg_stat_database, pg_stat_bgwriter, pg_stat_activity), ferramentas de monitoramento de nível de SO (por exemplo, iostat, vmstat, top) e soluções de monitoramento externas para coletar dados sobre CPU, memória, E/S de disco e desempenho de consultas.
• Entenda Sua Carga de Trabalho: Sua aplicação é intensiva em leitura ou escrita? Ela executa consultas analíticas complexas ou operações transacionais simples? Adapte sua configuração às características específicas de sua carga de trabalho.
• Considere Outros Parâmetros: Embora shared_buffers, work_mem e checkpoint_timeout sejam cruciais, muitos outros parâmetros podem impactar o desempenho. Por exemplo, effective_cache_size (dicas para o planejador de consultas sobre o cache do SO disponível) e wal_buffers (memória para registros WAL antes da liberação) são frequentemente ajustados juntamente com estes.
• Use EXPLAIN ANALYZE: Esta ferramenta inestimável ajuda você a entender como o PostgreSQL executa uma consulta, identifica gargalos e pode revelar se o work_mem é insuficiente.

O melhor trabalho de ajuste do PostgreSQL é chato de uma boa maneira: meça, altere uma configuração, meça novamente e mantenha um caminho de reversão. shared_buffers, work_mem e configurações de checkpoint podem fazer uma diferença real, mas eles trabalham com planos de consulta, índices, autovacuum, contagens de conexão e armazenamento. Se essas peças não estiverem saudáveis, apenas a configuração não salvará o banco de dados.