Sobre Os Fundamentos Arquiteturais Do Banco De Dados Postgresql Considere

Entendendo a arquitetura do PostgreSQL de verdade

sobre os fundamentos arquiteturais do banco de dados postgresql considere a forma como ele realmente trabalha por baixo do capô, e não apenas o que a documentação oficial descreve nos primeiros parágrafos. A maioria dos DBAs e desenvolvedores que conheço opera no PostgreSQL sem entender seu modelo de processos e memória compartilhada, e isso gera problemas chatos que poderia ser facilmente evitados com conhecimento básico. O PostgreSQL não é um banco com threads. Ele funciona com processos. Cada conexão de cliente gera um processo servidor separado. O processo principal é o postmaster, e ele orquestra tudo: o checkpointer, os processos de autovacuum, o walwriter, e os workers de estatísticas. Quando você vê algo como shared_buffers = 2GB no postgresql.conf, isso não significa que o PostgreSQL reservou 2GB. Ele reserva uma região de memória compartilhada que todos os processos podem acessar, e o tamanho ideal depende fortemente da carga de trabalho. Eu já vi gente colocar shared_buffers como 50% da RAM do servidor. Funciona bem até acontecer um pico de conexões ou um backup em quente. Aí o sistema começa a trocar memória no swap e você perde 400ms em cada consulta que antes levava 5ms. O recomendado pela Oracle na época do acquisto foi algo em torno de 25% da RAM. O resto fica para o sistema operacional gerenciar o cache de disco.

WAL: o que ele realmente faz

O Write-Ahead Logging é o que permite recuperação e consistência. Antes de qualquer dado ser escrito no disco, o registro de WAL precisa estar lá. Isso muda completamente a semântica de crash recovery do banco. Se o servidor cair durante uma transação, o WAL garante que o banco seja restaurado ao estado consistente mais recente antes de qualquer operação de COMMIT ser considerada concluída. Eu me lembro de um caso em que uma replicação streaming ficou com lag de 12 horas. Nada de erro no log, nada de connection reset. O problema era um checkpoint configuration mal ajustado com checkpoint_timeout muito alto e checkpoints sendo forçados toda vez que o wal_level estava em replica. Reduzi o checkpoint_timeout para 15 minutos e configurei checkpoint_completion_target para 0,9. O lag caiu para menos de 2 minutos em 30 segundos de observação.

O modelo MVCC do PostgreSQL

O MVCC no PostgreSQL é implementado de uma forma bem específica. Cada linha tem dois campos ocultos, xmin e xmax, que registram quais transações criaram ou removeram aquela versão. Quando você faz um UPDATE, a linha antiga não é sobrescrita. Uma nova versão é escrita e a antiga permanece visível para transações que já estavam rodando quando a atualização começou. Isso é poderoso, mas tem um custo. Cada UPDATE gera uma versão antiga morta que precisa ser limpa pelo autovacuum. Se você tem uma tabela com milhares de atualizações por segundo e o autovacuum não consegue acompanhar, a tabela começa a acumular versões mortas. A consulta pode começar a ler muito mais páginas do que o necessário, e o desempenho cai de forma gradual e silenciosa.

A armadilha dos índices com heap

Muita gente pensa que um índice B-tree cobre tudo. Mas se a coluna indexada for atualizada frequentemente, o planner pode decidir que vale mais a pena fazer index-only scan e depois acessar o heap para validar a visibilidade da linha. Isso acontece porque oVisibility Map não marca uma página como clean se houver linhas modificadas recentemente. Num banco que eu administro, tínhamos uma tabela de auditoria com 800 milhões de linhas e um índice B-tree em uma coluna que era atualizada a cada insert. As queries que pareciam dever ser puramente index scan eram muito mais lentas do que o esperado. A solução não foi mudar o índice, foi ajustar o autovacuum_multixact_freeze para um valor mais agressivo e rodar VACUUM VERBOSE manualmente nos horários de menor tráfego.

Buffer pool e como ele funciona na prática

O shared_buffers é o buffer pool do PostgreSQL. Mas diferente do Oracle ou MySQL InnoDB, o PostgreSQL não usa LRU com clock sweep sofisticado. O algoritmo é basicamente LRU com alguns truques. Páginas quentes ficam no início da lista, páginas acessadas raramente vão para o final. Se você tem uma consulta que varre 10 milhões de linhas de uma vez, essas páginas vão ocupar espaço no buffer e empurrar páginas que estavam sendo usadas por queries mais frequentes para fora do cache. O resultado é um ping-pong de I/O que não para até a próxima execução. Não tem auto-tuning. Você precisa entender o padrão de acesso e ajustar o work_mem e o shared_buffers manualmente.

Toast e colunas grandes

O TOAST é o mecanismo que o PostgreSQL usa para lidar com linhas que excedem 2KB. Colunas text, jsonb, ou arrays grandes são armazenados fora da linha principal em uma tabela TOAST separada. Quando você consulta uma linha, o PostgreSQL carrega primeiro a parte compacta e só sobe o dado TOAST se você realmente acessar a coluna grande. Eu já vi uma situação onde uma tabela tinha uma coluna jsonb com 50KB em média por linha, e todas as queries acessavam essa coluna. Isso significava que cada SELECT causava um acesso extra à tabela TOAST, dobrando praticamente o I/O. A solução foi normalizar a estrutura, separar o jsonb em uma tabela relacionada e usar JOIN apenas quando necessário.

Locking e concurrency

O PostgreSQL usa locks otimistas e pessimistas combinados. Cada transação adquire locks em nível de linha, não de tabela, e esses locks são liberados assim que a transação termina. O nível de lock mais comum é o RowExclusiveLock, que permite que múltiplas transações atualizem linhas diferentes da mesma tabela simultaneamente. O problema aparece quando duas transações tentam atualizar a mesma linha. Uma delas ganha o lock e a outra entra em wait. Se o deadlock_detector detectar um ciclo de dependência, ele mata uma das transações. Eu já diagnostiquei um deadlock que acontecia apenas em horário de pico, quando o volume de requisições era alto o suficiente para criar um padrão de lock em cruzamento que o detector levava 10 segundos para perceber. A solução foi reescrever a lógica de negócio para acessar as tabelas numa ordem consistente e evitar o cenário de circular wait.

Autovacuum: o serviço que ninguém vê mas tudo depende

O autovacuum é um daemon interno que roda automaticamente. Ele executa VACUUM, ANALYZE, e em alguns casos FREEZE nas tabelas. O threshold padrão para iniciar um VACUUM é quando 20% das linhas foram deletadas ou atualizadas, mais 50 linhas. Isso funciona bem para a maioria dos cenários, mas não para tabelas que recebem INSERT massivo em lotes. Num projeto meu, tínhamos uma tabela de logs com INSERT em batch de 100 mil linhas por minuto. O autovacuum padrão não conseguia acompanhar porque o threshold de 20% nunca era atingido — as linhas novas cresciam mais rápido que a porcentagem de mortas. A solução foi reduzir autovacuum_vacuum_threshold para 1000 e autovacuum_vacuum_scale_factor para 0,01. Após esse ajuste, o vacuum passava a rodar a cada poucas centenas de milissegundos, mantendo a tabela limpa sem impacto perceptível na performance.

Planejador de queries e estimativas

O query planner do PostgreSQL é um otimizador baseado em custo. Ele estima o custo de cada plano possível usando estatísticas coletadas pelo ANALYZE. Se as estatísticas estão desatualizadas, o planner escolhe planos ruins sem motivo aparente. Uma join que deveria usar hash join vira nested loop, e a query que levava 2 segundos passa a levar 40. Eu já lidado com um caso onde uma query simples de SELECT com JOIN em duas tabelas de 50 milhões de linhas começou a demorar de repente. O problema era que uma atualização massiva tinha sido feita na tabela de referência e o ANALYZE não tinha rodado. Atualizei manualmente as estatísticas com ANALYZE na tabela específica e a query voltou ao normal em segundos. Desde então, sempre rodo ANALYZE após grandes cargas de dados.

Configuração prática para produção

Não existe configuração universal, mas algumas diretrizes básicas ajudam. Shared_buffers em 25% da RAM. Effective_cache_size em 75%. Work_mem deve ser pequeno — 4MB é um bom ponto de partida — porque ele é por conexão e cresce linearmente com o número de threads concurrentes. Random_page_cost deve ser 1,1 para SSD e 4,0 para HDD, porque o planner usa esse valor para decidir entre index scan e seq scan. Max_connections é o parâmetro mais perigoso. Cada conexão consome memória e CPU. Se você tem 1000 conexões e work_mem de 10MB, você está falando de 10GB apenas de work_mem, além do shared_buffers e do custo do processo em si. O ideal é usar um connection pooler como PgBouncer, que permite centenas de conexões lógicas com uma dezena de conexões físicas ao banco.

Monitoramento essencial

O que mais mata PostgreSQL em produção não é falha de software, é má configuração. Os indicadores que você deve monitorar diariamente são: hit ratio do shared_buffers (deve ficar acima de 99%), filas de espera por lock, taxas de autovacuum, e tempo de resposta das consultas mais críticas. O pg_stat_statements extension é obrigatório para isso — ela registra estatísticas de todas as queries executadas, incluindo tempo médio, contagem, e I/O. Tudo que foi descrito aqui é o que eu vejo funcionando no dia a dia. A arquitetura do PostgreSQL é sólida, mas exige que você entenda como cada componente interage. Sem esse entendimento, cada problema parece um bug aleatório, quando na verdade é apenas um sintoma de configuração inadequada.