Como aproveitar ao máximo as configurações de memória do Postgres

• O banco de dados Postgres usa grandes quantidades de RAM. Ao montar um conjunto de resultados, ele passa por etapas como correspondência de índices, busca de linhas relevantes nas tabelas e mesclagem/filtragem/agregação/ordenação de tuplas, e todas essas etapas dependem de memória
• Para otimizar o uso de memória no Postgres, é preciso aproveitar ao máximo a RAM disponível, ajustar com eficiência os vários tipos de alocação de memória e evitar que o sistema operacional encerre processos por uso excessivo de memória

Sharing is Caring

• A maior parte da RAM relacionada ao Postgres é chamada de shared_buffers e representa as linhas de todas as tabelas e índices acessados com mais frequência. Isso é sustentado por uma heurística que atribui pontuações conforme a frequência de uso
  • shared_buffers é um valor fixo alocado na inicialização do Postgres e não contribui para problemas inesperados de memória
  • O valor padrão é 128MB
  • Mas o sistema operacional pode não tratá-lo como memória pré-alocada, então definir um valor muito alto, próximo à quantidade total de RAM da instância, pode ser arriscado
• Em sistemas de produção, a recomendação mais comum para shared_buffers é 25% da RAM disponível. Como isso é ajustado ao hardware, é um bom ponto de partida para a maioria dos sistemas
  • Resultados de benchmark mostram que a recomendação de 25% geralmente é suficiente, mas isso pode variar conforme a forma de uso do banco de dados
  • Por exemplo, sistemas de relatórios, por causa de consultas ad hoc complexas, têm baixa taxa de acerto de cache e às vezes mostram desempenho ligeiramente melhor com uma configuração menor
• Com a extensão pg_buffercache, é possível identificar exatamente quais tabelas e índices estão alocados nos buffers compartilhados. Ao verificar o número de páginas usadas no buffer, dá para ajustar o valor de shared_buffers
  • Se o cache de buffer não estiver sendo utilizado em 100%, a configuração pode estar alta demais, então pode ser possível reduzir o tamanho da instância ou o valor de shared_buffers
  • Se estiver em 100% e apenas partes de muitas tabelas estiverem em cache, pode valer a pena aumentar o valor gradualmente até o ponto de retorno decrescente
• A nova view pg_stat_io do Postgres 16 também pode ajudar no ajuste de shared_buffers. Ela permite verificar a taxa de acerto e leituras/escritas do backend do cliente
  • Se a proporção entre leitura e escrita estiver próxima de 1, isso pode indicar que o Postgres está reciclando continuamente as mesmas páginas dentro de shared_buffers. Para reduzir esse thrashing, vale aumentar shared_buffers
• Quando esse valor começa a passar de 50% da RAM do sistema, convém considerar aumentar o tamanho da instância, porque o Postgres ainda precisa de memória para sessões de usuário e suas consultas

Working Memory

• A outra metade da memória que o Postgres usa para realmente executar trabalho corresponde à memória de trabalho, controlada pelo parâmetro work_mem
  • O padrão é 4MB, e este é um dos primeiros valores que os usuários costumam alterar para acelerar a execução de consultas
  • Porém, se o sistema operacional estiver encerrando o Postgres com mensagens de “memória insuficiente”, aumentar work_mem pode parecer tentador, mas isso só piora o problema. Aumenta a quantidade de RAM usada pelo Postgres e torna essas interrupções ainda mais prováveis
• Muita gente interpreta “memória de trabalho” como uma única alocação destinada a todo o trabalho que o Postgres faz durante uma consulta, mas na prática pode ser bem mais que isso
  • Cada etapa (nó) recebe uma instância separada de work_mem. Por exemplo, se uma consulta exigir 4 nós e o work_mem estiver no padrão de 4MB, ela pode consumir até 16MB de RAM
  • Em um servidor movimentado, se 100 consultas desse tipo estiverem rodando ao mesmo tempo, só o cálculo dos resultados já pode usar até 1,6GB de RAM. Consultas mais complexas podem exigir ainda mais, dependendo da quantidade de nós no plano de execução
• Usando o comando EXPLAIN para verificar o plano de execução de uma consulta, é possível ver como o Postgres vai executá-la e todos os nós necessários para gerar a saída
  • Em conjunto com a extensão pg_stat_statements, isso permite isolar as consultas mais ativas e estimar o uso total de memória causado por work_mem
• Quando work_mem está configurado baixo demais, linhas ou resultados intermediários que não cabem na RAM acabam indo para disco, o que é muito mais lento
  • É possível consultar a view pg_stat_database para ver o tamanho acumulado e a quantidade de arquivos temporários gravados em disco; se o tamanho médio fizer sentido, pode-se aumentar work_mem nessa mesma ordem de grandeza
• Para ter uma noção aproximada da RAM disponível por sessão, pode-se usar a fórmula: (80% da RAM total - shared_buffers) / (max_connections)
  • Por exemplo, com 16GB de RAM, 4GB de buffers compartilhados e 100 conexões máximas, haveria cerca de 88MB por sessão
  • Dividindo esse valor pelo número médio de nós nos planos de consulta, obtém-se uma boa configuração para work_mem

Ongoing Maintenance

• A última parte ajustável do uso de RAM no Postgres é semelhante à memória de trabalho, mas específica para manutenção, usando o parâmetro maintenance_work_mem
  • O padrão é 64MB e ele define a quantidade de RAM dedicada a tarefas como VACUUM, CREATE INDEX e ALTER TABLE ADD FOREIGN KEY
• Como ele é limitado a uma tarefa por sessão e a chance de muitas tarefas simultâneas é menor, costuma ser considerado seguro usar valores mais altos
  • Essas tarefas de manutenção podem usar muita memória e terminar bem mais rápido se puderem operar inteiramente em RAM, por isso é muito comum configurar 1GB ou 2GB
• Um ponto importante é o processo automático de limpeza do Postgres, o autovacuum, que marca tuplas mortas para reutilização futura
  • O autovacuum inicia trabalhos em segundo plano até o limite de autovacuum_max_workers, e cada um deles pode usar uma instância completa de maintenance_work_mem
  • Em servidores com bastante RAM livre, 1GB para memória de manutenção costuma ser seguro, mas em ambientes com pouca RAM é preciso mais cautela
  • Para limitar especificamente os workers de autovacuum, existe o parâmetro separado autovacuum_work_mem
  • Os workers de autovacuum do Postgres não podem usar mais de 1GB, então configurar autovacuum_work_mem acima disso não traz efeito

Session Pooling

• A forma mais simples de reduzir o consumo de memória é impor um limite lógico às alocações potenciais
  • O Postgres hoje é um mecanismo baseado em processos, então cada sessão de usuário recebe um processo físico, e não uma thread
  • Por isso, toda conexão traz um certo overhead de RAM e contribui para trocas de contexto
  • Como resultado, a recomendação comum é definir max_connections para no máximo 4 vezes o número de threads de CPU disponíveis. Isso minimiza o tempo gasto alternando sessões entre CPUs e limita naturalmente a quantidade de RAM que as sessões podem consumir no total
• Se todas as sessões estiverem executando consultas e cada nó representar uma alocação de work_mem, o uso teórico máximo de memória de trabalho será connections * nodes * work_mem
  • Nem sempre é possível reduzir a complexidade das consultas, mas em geral dá para reduzir o número de conexões
  • Isso pode não ser tão simples quando a aplicação mantém sempre uma quantidade elevada de sessões abertas ou quando vários microsserviços dependem do Postgres
• A fórmula work_mem * max_connections * 5 é uma estimativa aproximada da quantidade máxima de RAM que a instância Postgres pode alocar para sessões de usuário ao processar consultas básicas, assumindo que todas as conexões estejam ativas
  • Se o servidor não tiver RAM suficiente para esse valor, convém reduzir um dos fatores ou aumentar a RAM
  • A estimativa de 5 nós por consulta média pode não refletir sua aplicação, então deve ser ajustada conforme você tiver uma visão melhor dos planos de execução
• O próximo passo é introduzir um pooler de conexões como o PgBouncer
  • Ele desacopla as conexões do cliente em relação ao banco de dados e reutiliza sessões custosas do Postgres entre vários clientes
  • Quando configurado corretamente, centenas de clientes podem compartilhar algumas dezenas de conexões Postgres sem impactar a aplicação
  • Já foi observado o PgBouncer multiplexando mais de 1000 conexões em 40-50 dessa forma, reduzindo bastante o consumo total de memória causado pelo overhead de processos

Reducing Bloat

• Provavelmente o aspecto mais difícil de acompanhar no uso de memória é o inchaço de tabelas (bloat)
  • O Postgres usa controle de concorrência multiversão (MVCC) para representar os dados no sistema de armazenamento
  • Isso significa que, sempre que uma linha é modificada, o Postgres cria outra cópia dessa linha em algum lugar da tabela e a marca com um novo número de versão
  • O processo VACUUM do Postgres marca versões antigas de linhas como espaço “não utilizado”, permitindo que novas versões sejam colocadas ali
• O Postgres tem um processo em segundo plano de autovacuum que procura continuamente essas alocações reutilizáveis e impede que as tabelas cresçam indefinidamente
  • Mas às vezes, especialmente em sistemas muito grandes, a configuração padrão pode não ser suficiente e essa manutenção acaba ficando para trás
  • Como resultado, a tabela pode acabar com mais linhas mortas do que vivas e se tornar “inchada” com dados antigos
• Quando uma tabela está extremamente inchada, é preciso considerar o impacto sobre os buffers compartilhados
  • Se cada página contiver apenas uma linha ativa e várias linhas mortas, uma consulta que precise de 10 linhas terá de trazer 10 páginas para os buffers compartilhados, desperdiçando muita memória que poderia ser usada para outra coisa
  • Se a demanda por essas linhas for especialmente alta, a frequência de uso fará com que elas permaneçam nos buffers compartilhados, reduzindo bastante a eficiência do cache
• Há muitas consultas circulando na internet para estimar o inchaço de tabelas, mas a única forma de ver concretamente como estão as páginas da tabela é usar a extensão pgstattuple
• Se free_percent for maior que 30%, talvez seja necessário tornar o autovacuum mais agressivo. Se for muito maior que 30%, vale a pena remover totalmente o inchaço
  • Hoje, a única forma suportada de fazer isso é com o comando VACUUM FULL, que essencialmente reconstrói a tabela. Ele realoca todas as linhas ativas para um novo local e descarta a cópia antiga inchada
  • Esse processo aplica um bloqueio de acesso exclusivo durante sua execução, então em quase todos os casos é necessário algum tipo de indisponibilidade
• Uma alternativa é a extensão pg_repack, suportada pela Tembo
  • Essa ferramenta de linha de comando pode reorganizar a tabela para remover o inchaço de forma totalmente online, sem bloqueio exclusivo
  • Como ela existe fora do core do Postgres e modifica o armazenamento de tabelas e índices, costuma ser tratada como um uso mais avançado
  • Recomenda-se testá-la bastante em ambiente não produtivo antes de usar
• Também é possível ir além e reorganizar a ordem das colunas para maximizar a quantidade de linhas por página, fazendo uma espécie de Tetris de colunas
  • Isso provavelmente já entra em um nível extremo de otimização, mas pode ser uma estratégia viável em ambientes onde há liberdade para reconstruir as tabelas dessa forma

The Balancing Act

• Configurar adequadamente todos esses parâmetros e recursos é ao mesmo tempo arte e ciência
  • Vimos como medir o uso real dos buffers compartilhados e como identificar quando a memória de trabalho está baixa demais
  • Mas e quando, como na maioria dos casos, há limitações de hardware disponível ou de orçamento? É aí que entra a “arte”
• Em cenários com pouca memória, pode ser necessário reduzir um pouco shared_buffers para abrir espaço para mais work_mem. Ou talvez seja preciso reduzir os dois
  • Se a aplicação precisa de muitas sessões, pode fazer mais sentido reduzir work_mem ou introduzir pooling de conexões para evitar que sessões simultâneas acumulem grandes alocações de RAM
  • Se maintenance_work_mem foi aumentado no passado partindo da premissa de que havia RAM suficiente para tudo, pode ser mais sensato reduzi-lo. Há muita coisa a considerar
• Em instâncias com pouca memória, até mesmo as recomendações acima podem não ser suficientes. Nesses casos, recomenda-se seguir a seguinte ordem de ações para maximizar o uso de memória e evitar esgotamento de recursos:
  1. Adicionar um pooler de conexões e reduzir max_connections. Essa é a forma mais rápida e fácil de reduzir o consumo máximo de recursos
  2. Usar EXPLAIN nas consultas mais frequentes relatadas por pg_stat_statements para encontrar o número máximo de nós por consulta, e não a média. Depois, definir work_mem como no máximo (80% da RAM total - shared_buffers) / (max_connections * número máximo de nós no plano)
  3. Voltar maintenance_work_mem e autovacuum_work_mem para o padrão de 64MB. Se as tarefas de manutenção estiverem lentas demais e houver RAM disponível, considerar aumentos em incrementos de 8MB
  4. Usar a extensão pg_buffercache para verificar a quantidade de tabelas armazenadas em shared_buffers. Examinar cuidadosamente cada tabela e índice e ver se há maneiras de reduzir isso, como arquivamento de dados e ajuste de consultas para usar menos informação. Isso pode incluir VACUUM FULL ou pg_repack para compactar páginas usadas por tabelas ativamente inchadas
  5. Se pg_buffercache mostrar que shared_buffers está cheio e não pode mais ser reduzido sem remover páginas ativas, usar a coluna usagecount para priorizar as páginas mais ativas. Como essa coluna vai de 1 a 5, focar nas páginas usadas de 3 a 5 vezes pode permitir reduzir shared_buffers sem grande impacto no desempenho
  6. Por fim, provisionar hardware mais potente. Se o banco de dados realmente precisar de mais RAM para a carga atual e reduzir os parâmetros acima prejudicar demais o desempenho, em geral faz mais sentido fazer um upgrade