Usuários de PostgreSQL, com certa frequência, notam que seus sistemas ficam gradativamente mais lentos: suas queries bem otimizadas passam a demorar um pouco mais para rodar, os backups ficam muito grandes – maiores que o crescimento real dos dados -, o uso do disco aumenta e você nem inseriu muitas informações novas. Nesse contexto, é identificado um problema corrosivo, apesar de comum, na performance do PostgreSQL: o famigerado bloat.
O bloat é um espaço desperdiçado nas suas tabelas e índices, ocupado por tuplas mortas e preenchimentos vazios. Contudo, fique tranquilo! Não está com um problema estrutural ou um bug destrutivo, o bloat é consequência do design do controle de concorrência do PostgreSQL, o MVCC (Multi-Version Concurrency Control). Assim, os elementos causadores do bloat devem ser gerenciados ativamente pois, caso negligenciados, podem acumular a ponto de degradar a performance de leitura, causar sobreuso do armazenamento e tornar operações rotineiras, como de manutenção, extremamente lentas.
Portanto, sabendo o que é o bloat e como ele prejudica sua infraestrutura de PostgreSQL, acompanhe este guia nas causas do bloat e anote os scripts prontos para diagnosticá-lo com precisão. Abordaremos também o pg_repack, uma ferramenta online que ajuda a remover o bloat em ambientes de produção de forma segura.
Por que o Bloat Acontece
Antes de eliminar por completo o bloat, é fundamental compreender de onde ele vem. Assim, suas causas estão no core arquitetônico do PostgreSQL: o Multi-Version Concurrency Control (MVCC). O MVCC é o que permite que o PostgreSQL ofereça consistência de leitura, com a qual 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 é exatamente 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 também não é exatamente 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” (tuplas mortas). Ao contrário do que deve estar pensando, não são totalmente inúteis ou uma falha de design, pelo contrário, servem para garantir que transações de longa duração, iniciadas antes da modificação – UPDATE ou DELETE -, ainda possam consultar a versão consistente dos dados que existia quando começaram.
Adicionalmente, há um processo responsável por “limpar” as tuplas mortas do seu banco, de forma a tornar o espaço que ocupavam reutilizável para futuros INSERTs: é o VACUUM, o qual regularmente exclui as tuplas mortas desnecessárias para manter a consistência das transações, garantindo a continuidade do seu sistema. Entretanto, se o volume de tuplas mortas geradas for maior do que a capacidade do VACUUM de limpá-las, é iniciado um processo de acumulação do bloat.
O Impacto do Bloat na Performance do Sistema
Além de desperdiçar espaço em disco, o bloat impacta a performance diretamente:
- 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. 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
Legal! Agora sabemos que o seu problema é o bloat, contudo, não é suficiente para começar a resolvê-lo, também é necessário medí-lo. Nesse cenário, a boa notícia é que a comunidade de PostgreSQL desenvolveu scripts robustos que auxiliam a estimar a quantidade de espaço desperdiçado. Ao comparar o tamanho real da tabela em disco com o tamanho esperado com base nas estatísticas do PostgreSQL, é possível estimar o espaço em disco que é desperdiçado com o bloat.
Atenção 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 ou sandbox primeiro. A mãe de vocês não os ensinou a não rodar scripts de desconhecidos indiscriminadamente por aí?
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 sofrer de bloat. 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 (finalmente).
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 bem ajustado.
- 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 – naquela lógica da capacidade de limpeza ser inferior ao volume de dead tuples gerado. 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 – tudo personalizado para a necessidade específica de cada tabela.
- Comando Exemplo:codeSQL
Higiene e Debloat Contínuos e Proativos
O bloat no PostgreSQL é comum e provavelmente voltará a acontecer, pois é uma consequência inevitável de sua arquitetura MVCC. Negligenciar seu 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.