Seu banco de dados PostgreSQL está ficando mais lento. As queries que antes eram rápidas agora demoram um pouco mais. O tamanho dos seus backups cresce a uma taxa que parece desproporcional ao crescimento real dos seus dados. A utilização do seu disco aumenta, mesmo que você não esteja inserindo um volume massivo de novas informações. Se este cenário lhe parece familiar, você pode estar enfrentando um dos inimigos mais silenciosos e corrosivos da performance do PostgreSQL: o bloat.
O bloat, ou inchaço, é o espaço desperdiçado em suas tabelas e índices, ocupado por “tuplas mortas” e preenchimento vazio. Não é um bug, mas uma consequência natural do design sofisticado de controle de concorrência do PostgreSQL, o MVCC. No entanto, se não for gerenciado ativamente, o bloat pode se acumular a ponto de degradar severamente a performance de leitura, inflar os custos de armazenamento e tornar as operações de manutenção dolorosamente lentas.
Este guia técnico oferece um mergulho profundo na causa raiz do bloat, fornece scripts acionáveis para diagnosticá-lo com precisão e detalha as estratégias, incluindo ferramentas online como o pg_repack, para removê-lo de forma segura em ambientes de produção.
A Causa Raiz: Por que o Bloat Acontece
Para combater o bloat, é essencial entender por que ele existe. A causa está no coração da arquitetura do PostgreSQL: o Multi-Version Concurrency Control (MVCC). O MVCC é o que permite que o PostgreSQL ofereça uma consistência de leitura fantástica, onde as leituras (SELECT) não bloqueiam as escritas (UPDATE/DELETE), e vice-versa.
O mecanismo funciona mantendo múltiplas “versões” de uma mesma linha. Quando você executa uma operação, o PostgreSQL não modifica os dados no lugar:
- UPDATE não é um UPDATE: Um UPDATE no PostgreSQL é, na verdade, uma operação combinada de INSERT + DELETE. O banco de dados insere uma nova versão da linha com os dados atualizados e marca a versão antiga como “morta” ou “inválida” para todas as transações futuras. A linha antiga não é fisicamente removida do arquivo de dados naquele momento.
- DELETE não é um DELETE: Da mesma forma, um DELETE não apaga fisicamente a linha. Ele simplesmente marca a versão atual da linha como “morta”.
Essas versões antigas e mortas das linhas são chamadas de “dead tuples”. Elas permanecem nos arquivos de dados para garantir que transações de longa duração, que começaram antes da modificação, ainda possam ver a versão consistente dos dados que existia quando elas começaram.
O processo responsável por, eventualmente, limpar essas tuplas mortas e tornar o espaço que elas ocupavam reutilizável para futuros INSERTs é o VACUUM. Se a taxa de criação de tuplas mortas (ou seja, a sua carga de escrita) for maior do que a capacidade do VACUUM de limpá-las, o bloat começa a se acumular.
O Impacto do Bloat na Performance do Sistema
O bloat não é apenas espaço desperdiçado; ele tem um impacto direto e negativo na performance:
- Seq Scans Mais Lentos: Quando o PostgreSQL realiza uma varredura sequencial em uma tabela inchada, ele precisa ler muito mais páginas do disco para obter a mesma quantidade de dados vivos. Se uma tabela tem 50% de bloat, uma varredura completa lerá o dobro de blocos, causando o dobro de I/O de disco.
- Performance de Índice Degradada: Os índices também sofrem de bloat. Um índice inchado é mais “profundo” e mais “largo”, exigindo mais I/O para ser lido e percorrido. Pior ainda, o bloat na tabela significa que mesmo um Index Scan eficiente precisa pular um grande número de tuplas mortas ao buscar os dados na tabela, um processo conhecido como “heap fetches”.
- Uso Ineficiente do Cache: O bloat consome seu recurso mais precioso: a memória RAM. Páginas de dados que estão 80% cheias de tuplas mortas podem estar ocupando espaço valioso no shared_buffers (o cache do PostgreSQL), expulsando dados “quentes” e úteis e diminuindo sua taxa de acerto no cache.
- Aumento do Custo de Armazenamento e Backups: O impacto mais óbvio é o aumento do tamanho físico do seu banco de dados, o que leva a custos de armazenamento mais altos e backups que demoram mais para serem concluídos e restaurados.
Scripts para Encontrar e Quantificar o Bloat
Adivinhar que você tem bloat não é suficiente. Você precisa medi-lo. A comunidade PostgreSQL desenvolveu scripts SQL robustos para estimar a quantidade de espaço desperdiçado. Eles funcionam comparando o tamanho real da tabela com o tamanho esperado com base nas estatísticas do PostgreSQL.
Atenção: Estes scripts podem ser intensivos em recursos. Execute-os em horários de baixa carga e teste-os em um ambiente de staging primeiro.
Script para Estimar o Bloat da Tabela
Este é um dos scripts mais conhecidos e confiáveis, adaptado de várias fontes da comunidade:
SELECT
current_database(),
schemaname,
tablename,
ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,
CASE WHEN relpages > otta THEN ROUND((relpages-otta)::FLOAT*bs/1024/1024, 2) ELSE 0 END AS tbsize_mb,
(CASE WHEN otta=0 OR sml.relpages=0 THEN 0.0 ELSE sml.relpages::FLOAT/sml.reltuples END)::NUMERIC AS tuple_bloat,
(CASE WHEN otta=0 OR sml.relpages=0 THEN 0.0 ELSE bs*(sml.relpages-otta)::FLOAT/sml.reltuples END)::NUMERIC AS wasted_bytes_per_tuple
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*sml.avg_width)/ (bs-20)) AS otta
FROM (
SELECT
ma, schemaname, tablename, (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr, (maxfracsum*(datawidth-hdr)) AS datafrac
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
) AS hdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::NUMERIC) AS bs,
CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
LEFT JOIN pg_stat_user_tables su ON cc.oid = su.relid
) AS sml
ORDER BY tbsize_mb DESC;
Como Ler a Saída:
- tbloat: Um fator de inchaço. 1.0 é o ideal. 1.5 significa 50% de bloat. 2.0 significa 100% de bloat (a tabela tem o dobro do tamanho que deveria).
- tbsize_mb: O tamanho estimado do espaço desperdiçado em megabytes. Ordene por esta coluna para encontrar seus maiores ofensores.
Script para Estimar o Bloat do Índice
Os índices também podem inchar. Este script ajuda a identificá-los:
SELECT
current_database(), nspname AS schemaname, tblname, idxname,
bs*idx_res.relpages/1024/1024 AS "size_mb",
idx_res.idx_scan,
idx_res.idx_tuples,
(
SELECT pg_size_pretty(
(bs*idx_res.relpages - bs*COALESCE(
CEIL(
idx_res.idx_tuples * (
24 + max(v.avg_width) * (1 - max(v.null_frac))
) / (
(100 - i.fillfactor) * bs / 100
)
), 0
))::bigint
)
) AS "wasted_space"
FROM (
SELECT
nspname,
tbl.relname AS tblname,
idx.relname AS idxname,
idx.oid,
idx.reltuples,
idx.relpages,
stat.idx_scan,
stat.idx_tuples
FROM pg_class tbl
JOIN pg_index i ON i.indrelid = tbl.oid
JOIN pg_class idx ON idx.oid = i.indexrelid
JOIN pg_namespace n ON n.oid = tbl.relnamespace
LEFT JOIN pg_stat_user_indexes stat ON stat.indexrelid = idx.oid
WHERE tbl.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
) AS idx_res
LEFT JOIN pg_stats v ON v.tablename = idx_res.tblname AND v.attname = ANY(string_to_array(idx_res.oid::regclass::text, '((, ))'::text)::text[])
CROSS JOIN (SELECT current_setting('block_size')::numeric AS bs) AS constants
GROUP BY 1, 2, 3, 4, 5, 6, 7
ORDER BY "wasted_space"::bigint DESC;
Como Ler a Saída:
- wasted_space: Uma estimativa do espaço desperdiçado no índice. Ordene por esta coluna para encontrar os índices mais inchados.
Estratégias para Remover o Bloat
Uma vez que você diagnosticou o bloat, é hora de removê-lo.
A Opção Nuclear: VACUUM FULL
- O que faz: VACUUM FULL reescreve a tabela inteira em um novo arquivo no disco, sem nenhum espaço morto, e depois troca o arquivo antigo pelo novo. Ele recupera o máximo de espaço possível.
- Por que você NÃO deve usá-lo em produção: Ele adquire um ACCESS EXCLUSIVE lock na tabela, bloqueando todas as operações, incluindo SELECTs, durante toda a sua execução. Em uma tabela grande, isso significa horas de downtime. É uma opção viável apenas em janelas de manutenção muito generosas ou para tabelas pequenas.
A Opção Online: A Extensão pg_repack
Esta é a ferramenta padrão-ouro para remover o bloat em ambientes de produção 24/7.
- Como Funciona: pg_repack é uma extensão que você precisa instalar no seu PostgreSQL. Ela funciona de forma inteligente para evitar locks de longa duração:
- Cria uma nova tabela, “sombra”, idêntica à original.
- Cria triggers na tabela original para capturar todas as alterações (INSERT, UPDATE, DELETE) que ocorrem durante o processo.
- Copia o conteúdo da tabela original para a tabela sombra.
- Aplica todas as alterações que foram capturadas pelos triggers na tabela sombra.
- Em uma transação muito curta no final, adquire um lock exclusivo, troca a tabela original pela sombra (uma operação de metadados quase instantânea) e descarta a tabela antiga.
- O Resultado: Você recupera todo o espaço desperdiçado com um bloqueio que dura apenas alguns segundos no final do processo, tornando-o seguro para a maioria dos ambientes de produção. Ele também pode ser usado para reconstruir índices online ou para mover tabelas para outro tablespace.
Ajustando o Autovacuum para Evitar o Bloat
Remover o bloat é bom, mas prevenir que ele se acumule em primeiro lugar é ainda melhor. A chave para isso é um autovacuum agressivo e bem ajustado.
- A Abordagem: O autovacuum padrão do PostgreSQL é configurado para ser conservador e não interferir na carga de trabalho. Para tabelas com alta taxa de escrita, essas configurações padrão são frequentemente insuficientes. A melhor prática é ajustar os parâmetros do autovacuum em uma base por tabela.
- Comando Exemplo:codeSQL
ALTER TABLE public.high_write_table SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 1000);Isso instrui o autovacuum a disparar uma limpeza nesta tabela específica quando 5% das suas linhas forem modificadas (em vez dos 20% padrão), garantindo que as tuplas mortas sejam limpas com muito mais frequência e impedindo o acúmulo de bloat.
Higiene e Debloat Contínuos e Proativos
O bloat no PostgreSQL não é uma questão de “se”, mas de “quando”. É uma consequência inevitável de sua arquitetura MVCC. Deixá-lo sem gerenciamento é uma forma de acumular débito técnico que se manifestará como lentidão, custos crescentes e incidentes de performance.
Ao entender suas causas, usar scripts para diagnosticá-lo e empregar ferramentas como o pg_repack para removê-lo de forma segura, as equipes de engenharia podem transformar a gestão do bloat de uma manutenção de crise reativa para uma prática de higiene proativa e contínua. Combinado com um autovacuum bem ajustado, isso garante que seus bancos de dados permaneçam enxutos, eficientes e performáticos a longo prazo.
Quer ajuda para diagnosticar e resolver problemas de bloat e outros gargalos silenciosos no seu PostgreSQL? Marque uma reunião com nosso especialista ou assista a uma demonstração na prática!
Saiba mais sobre o dbsnOOp!
Visite nosso canal no youtube e aprenda sobre a plataforma e veja tutoriais
Aprenda sobre monitoramento de banco de dados com ferramentas avançadas aqui.
Leitura Recomendada
- Como o dbsnOOp garante que o seu negócio nunca pare: Este artigo explora o conceito de continuidade de negócio sob a perspectiva da observabilidade proativa. Aprenda como a detecção preditiva de anomalias e a análise de causa raiz permitem que as equipes de engenharia previnam incidentes de performance antes que eles impactem a operação, garantindo a alta disponibilidade dos sistemas críticos.
- Indústria 4.0 e IA: O Desafio da Performance do Banco de Dados e a Importância da Observabilidade: Explore como as demandas da Indústria 4.0, IoT e Inteligência Artificial estão elevando a complexidade e o volume de dados a novos patamares. Este artigo discute por que as ferramentas de monitoramento legado são insuficientes neste novo cenário e como a observabilidade se torna crucial para garantir a performance e a escalabilidade necessárias para a inovação.
- Performance Tuning: como aumentar velocidade sem gastar mais hardware: Antes de aprovar o upgrade de uma instância, é fundamental esgotar as otimizações de software. Este guia foca em técnicas de performance tuning que permitem extrair o máximo de desempenho do seu ambiente atual, resolvendo a causa raiz da lentidão em queries e índices, em vez de apenas remediar os sintomas com hardware mais caro.