Questoes Sobre Pg

O que costuma dar errado com PostgreSQL na prática

Muita gente pergunta questões sobre pg achando que o banco é complicado, mas a maior parte dos problemas viram coisa simples quando você para de tratar o PostgreSQL como se fosse outro motor qualquer. Eu passei anos configurando, migrando e depurando Postgres em ambientes que iam de servidores dedicados até containers na nuvem, e o padrão se repete com frequência cansativa. A primeira coisa que eu vejo todo mundo errar é a configuração de memória. O default do postgresql.conf não serve pra nada em produção. Se você deixar o shared_buffers no 128MB que vem como padrão, o banco vai trabalhar muito mais do que precisa. O certo é ajustar para algo perto de 25% da RAM total da máquina, mas sem exagero, senão o sistema operacional vai começar a swapping e você vai achar que o banco tá lento quando na verdade é a máquina toda sofrendo. Já vi servidor com 64GB de RAM tendo shared_buffers em 128MB rodando consulta que deveria ser instantânea levando quase trinta segundos.

Depois tem o random_page_cost. Esse parâmetro quase ninguém mexe, e é um dos que mais impactam performance no dia a dia. O valor padrão é 4.0, que assume disco rígido comum. Se vocêroda SSD, muda pra 1.1. Se mantiver no 4.0 num servidor com SSD, o query planner vai escolher planos ruins sem você perceber, porque ele acha que acessos aleatórios são caros quando na verdade são rápidos. Isso resolveu um problema meu há uns três anos onde uma query que deveria usar um índice estava fazendo full scan em tabela com 2 milhões de linhas. A tabela inteira tinha sido migrada pro SSD mas o parâmetro continuava no padrão de fábrica.

Questões sobre pg que mais aparecem em reuniões de tuning

Vacuum é o terceiro ponto que todo mundo desconhece ou ignora. O Autovacuum do PostgreSQL roda automaticamente, sim, mas ele não é mágico. Em tabelas com muito update e delete, o autovacuum pode não conseguir acompanhar e a tabela começa a acumular tuples mortas. O sintoma é consulta lenta sem motivo aparente, tabela com tamanho enorme mas pouca informação útil. A solução imediata é rodar um vacuum full manualmente na tabela problemática, mas isso trava a tabela durante a operação, então precisa agendar. O workaround que eu uso é criar uma rotina de maintenance com o cron do sistema, executando um vacuum normal em horários de baixo tráfego, e monitorar a taxa de mortes por meio da view pg_stat_user_tables. Se o ratio de dead tuples pra live tuples passar de 20%, aí sim entra vacuum full, e eu faço isso sempre em janela de manutenção. Outro ponto que gera confusão constante é a diferença entre LIKE e ILIKE com índices. Criar um índice B-tree normal e usar LIKE com curinga à esquerda (%) não aproveita esse índice de jeito nenhum. O PostgreSQL só usa índice B-tree para prefix matches, então LIKE 'texto%' funciona mas '%texto' não. A solução pra buscar com curinga em ambas as extremidades é usar pg_trgm, que é uma extensão nativa que cria índices GIN baseados em trigramas. Configurei isso num projeto onde precisávamos fazer busca em campos de endereço com dezenas de milhões de registros, e a query que antes levava quase oito segundos caiu pra menos de duzentos milissegundos após criar o índice com USING gin enderecodroop_pattern_ops.

Indexação também é um tema que rende muitas questões sobre pg. A regra básica é que todo filtro WHERE com igualdade, range ou JOIN precisa ter índice correspondente. Mas o problema real aparece quando você cria índice demais. Cada insert e update fica mais lento porque o banco precisa manter todos os índices sincronizados. Num sistema que eu administrei, tínhamos uma tabela de transações com quatorze índices que era atualizada a cada pocos segundos. Removeu três índices que ninguém usava e o throughput de escrita melhorou em cerca de quarenta por cento. Não adianta só olharpag_stat_user_indexes pra ver quais índices não são usados, porque uso esporádico passa despercebido. O jeito certo é analisar por um período razoável, tipo duas ou três semanas, e cruzar com a carga real do sistema.

Como configurar conexão pool quando o banco começa a falhar

PostgreSQL suporta no máximo algumas centenas de conexões simultâneas de forma saudável, e cada conexão gasta memória no lado do servidor. O número ideal de max_connections varia conforme a carga e a memória disponível, mas em aplicações web normais, 100 a 200 conexões já é bastante. O problema é que frameworks e ORMs tendem a abrir conexão pra cada requisição e fechar depois, o que consome tempo e recursos desnecessários. A solução é usar pool de conexões, e o pgBouncer é a ferramenta padrão do ecossistema. Instalei pgBouncer em frente a uma instância que estava caindo por falta de memória nos processos do postgres, e o gargalo simplesmente sumiu. O pool mantém um conjunto fixo de conexões reutilizadas e distribui entre os clientes, então o banco vê talvez cinquenta conexões reais em vez de duzentas. Uma questão importante com pgBouncer é escolher o modo correto. Existem basicamente três: transaction, session e statement. O modo session é o mais simples e seguro, funciona pra maioria dos casos, mas não permite que uma mesma conexão seja compartilhada entre transações diferentes simultaneamente. O modo transaction permite isso, mas se o seu código usa LISTEN/NOTIFY, prepare statements preparados ou transações que duram varias declarações SQL, ele quebra. Já vi aplicação quebrar sem erro óbvio porque o driver abria uma transação, fazia uma consulta, e na próxima requisição o mesmo processo pegava uma conexão diferente do pool, perdendo o estado da transação anterior. O modo statement é o mais agressivo e raramente recomendável fora de cenários muito específicos.

Backup e restauração que realmente funcionam

Backup em PostgreSQL parece simples até dar problema. O pg_dump é bom pra migração e restauração pontual, mas não serve pra recovery point objective baixo em produção. Pra backup contínuo, o WAL archiving combinado com base backup via pg_basebackup é o caminho. Você configura archive_command no postgresql.conf pra copiar cada WAL segment para um diretório seguro, e periodicamente roda um pg_basebackup pra ter uma snapshot completa do cluster. Quando precisa restaurar, você recupera o base backup, coloca os WALs archivrados na pasta correta e inicia o recovery mode. O PostgreSQL replay os WALs até o ponto desejado, e você tem exatamente o estado que queria. O problema prático que eu encontrei foi com retenção de WALs. Se o archive não estiver configurado corretamente, o PostgreSQL para de gerar WALs e essencialmente para de funcionar, porque ele não consegue reciclar arquivos de log que ainda podem ser necessários pra recuperação. Configurei um monitoramento simples com um script shell que verifica se há WALs envelhecidos demais no diretório de archive, e dispara alerta se um arquivo ficar lá por mais de vinte e quatro horas sem receber atualização. Isso me salvou de um downtime de quase seis horas que estava prestes a acontecer porque o job de backup noturno travou e os WALs acumularam sem serem arquivados.

Monitoramento que não custa nada e funciona

Existem ferramentas caras e complexas pra monitorar PostgreSQL, mas o básico que o próprio banco oferece já cobre a maioria das necessidades. As views do sistema são sua melhor amiga: pg_stat_activity mostra conexões ativas e o que cada uma tá fazendo em tempo real, pg_stat_user_tables dá métricas de uso por tabela, pg_stat_user_indexes revela quais índices estão sendo usados e quais não, e pg_stat_database oferece visão macro do comportamento geral do servidor. Se você rodar uma consulta SELECT * FROM pg_stat_activity todo minuto num loop e gravar num arquivo, já tem histórico suficiente pra identificar padrões e gargalos. Extensão como pg_stat_statements é obrigatória pra qualquer ambiente sério. Ela armazena estatísticas de execução de todas as queries rodadas no banco, incluindo tempo médio, tempo total, número de chamadas e linhas retornadas. Sem ela, você tá no escuro sobre quais queries estão consumindo mais recursos. Instalei pg_stat_statements num banco que tava com lentidão intermitente e descobrimos que uma query específica, responsável por apenas dois por cento do tráfego, consumia quarenta por cento do tempo de CPU porque não tinha índice na coluna de filtro. Depois de criar o índice, a latência geral da aplicação caiu praticamente pela metade.

Erros comuns ao fazer migration de outro banco pra pg

Quem vem de MySQL ou SQL Server e migra prositegreSQL costuma tropeçar nos mesmos lugares. O primeiro é a sensibilidade a maiúsculas e minúsculas em identificadores. No PostgreSQL, se você criar uma tabela com letras maiúsculas usando aspas duplas, precisa usar as aspas e a capitalização exatas sempre que referenciar essa tabela. O padrão é transformar tudo pra minúsculo, então faça isso desde o início pra não ter dor de cabeça depois. O segundo erro clássico é confiar que tipos de dados são equivalentes. VARCHAR no MySQL não é a mesma coisa que VARCHAR no PostgreSQL, porque o Postgres trata VARCHAR e TEXT de forma diferente internamente, e em alguns casos o planner se comporta de maneira distinta. O terceiro ponto é que o PostgreSQL é mais rigoroso com tipagem. Se sua aplicação MySQL deixava passar conversões implícitas que geravam dados corrompidos, no Postgres essas operações vão falhar com erro. Isso é bom a longo prazo, mas no início dói. A questão de constraints também pede atenção. O PostgreSQL suporta constraints check, unique, foreign key e not null de forma robusta, mas elas precisam ser criadas explicitamente. Em alguns bancos mais flexíveis, você pode inserir dados que violariam integridade referencial e o banco deixa passar. No Postgres, não. Se sua migration não levar em conta isso, vai encontrar chaves estrangeiras que precisam existir antes dos inserts, e a ordem das operações importa. Estruture seu script de migration com cuidado, criando primeiro as tabelas referenciadas, depois as que dependem delas.

Configuração de timezone também é um ponto que causa surpresas. O PostgreSQL armazena timestamps sem timezone (timestamp without time zone) e com timezone (timestamp with time zone) de formas diferentes. Se sua aplicação espera que todos os horários sejam convertidos automaticamente pro fuso do usuário, você precisa usar timestamptz e configurar o timezone corretamente tanto no banco quanto na aplicação. Configurei um sistema onde o timezone do servidor estava em UTC mas os usuários estavam no Brasil, e os relatórios mostravam horários errados porque o banco recebia o timestamp já convertido pela aplicação em vez de deixar o Postgres fazer a conversão. O ajuste foi padronizar o envio de timestamps como UTC e usar atimetamptz no banco, deixando a aplicação fazer a conversão final pra exibição. Questões sobre questões sobre pg geralmente se resolvem com entendimento sólido desses pontos básicos. A maioria dos problemas que vejo em produção não vem de complexidade extrema, mas de configuração padrão sendo usada como se fosse configuração de produção, e de falta de familiaridade com o comportamento específico do motor. PostgreSQL é confiável e performático quando tratado com respeito às suas particularidades, mas exige que você pare de tratar ele como um clone genérico dos outros bancos e entenda como ele funciona por dentro.