Dead Tuples, bloat de tabelas e queries longas: o que o PostgreSQL não está te dizendo

Cada UPDATE e DELETE no PostgreSQL cria uma dead tuple — uma versão de linha que não é mais visível para nenhuma transação mas ainda ocupa espaço em disco até que um VACUUM o reclame. Isso não é um bug; é o núcleo do MVCC. O problema é que o autovacuum, o processo automático de limpeza, nem sempre acompanha. Quando ele fica para trás, suas tabelas incham, seus índices degradam, e suas queries ficam lentas silenciosamente. Nada dá erro. O banco só fica gordo e lento.

O que são dead tuples?

O PostgreSQL usa Multi-Version Concurrency Control (MVCC). Quando você roda UPDATE orders SET status = 'shipped' WHERE id = 42, o Postgres não sobrescreve a linha existente. Ele cria uma nova versão da linha e marca a antiga como morta — invisível para novas transações, mas ainda no disco. O DELETE faz o mesmo: a linha é marcada como morta, não removida. O espaço só é reclamado quando o VACUUM roda.

Você pode ver a acumulação diretamente:

SELECT
  schemaname || '.' || relname AS table_name,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup + n_dead_tup > 0
       THEN ROUND(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2)
       ELSE 0 END AS dead_ratio,
  last_autovacuum,
  last_vacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 20;

Um dead_ratio acima de 10–15% em uma tabela com muita escrita é um sinal de alerta. Acima de 25%, você provavelmente já está vendo planos de execução que escaneiam mais páginas do que o necessário — o planner vê uma tabela fisicamente maior do que a contagem de linhas vivas sugere.

Bloat: quando dead tuples se tornam permanentes

Dead tuples são temporárias se o vacuum roda. Bloat é o que acontece quando o vacuum não roda com frequência suficiente, ou quando transações longas o impedem de reclamar espaço. As dead tuples se acumulam, o arquivo físico da tabela cresce, e mesmo depois que o vacuum finalmente roda, o espaço é reclamado para reuso mas não devolvido ao SO. A tabela continua gorda. Um índice em uma tabela inchada também incha — mais páginas para escanear, mais I/O por lookup, menor cache hit ratio.

Você pode estimar bloat sem instalar extensões:

SELECT
  schemaname || '.' || relname AS table_name,
  pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(relname)) AS total_bytes,
  pg_size_pretty(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(relname))) AS total_size,
  n_live_tup,
  n_dead_tup,
  CASE WHEN n_live_tup > 0
       THEN pg_total_relation_size(relid) / n_live_tup
       ELSE 0 END AS avg_row_bytes
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Uma tabela com 100.000 linhas vivas de 200 bytes cada deveria ter uns 20 MB. Se total_bytes mostra 120 MB, são 100 MB de bloat — seis vezes o tamanho esperado. Cada sequential scan nessa tabela lê seis vezes mais páginas do que deveria.

Quais tabelas mais consomem disco?

Nem todas as tabelas são iguais. Um punhado de tabelas grandes tipicamente domina o uso de disco, e são nelas que bloat mais dói. A query acima já ordena por total_bytes DESC, mas para um quadro completo incluindo índices e TOAST:

SELECT
  n.nspname AS schema_name,
  c.relname AS table_name,
  pg_total_relation_size(c.oid) AS total_size_bytes,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size_pretty,
  COALESCE(s.n_live_tup, 0) AS row_count,
  CASE WHEN COALESCE(s.n_live_tup, 0) > 0
       THEN pg_total_relation_size(c.oid) / s.n_live_tup
       ELSE 0 END AS avg_row_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 50;

Essa é exatamente a query que o agente do PG Monitoring roda a cada ciclo de coleta. As top 50 tabelas por tamanho total (incluindo índices e TOAST) são enviadas para o SaaS, onde aparecem no dashboard ranqueadas por consumo de disco. Você vê quais tabelas estão crescendo, quais estão inchadas, e quais são ambas.

Quais tabelas são mais escritas?

Tabelas com muita escrita são as que acumulam dead tuples mais rápido. O PostgreSQL rastreia updates e deletes por tabela em pg_stat_user_tables:

SELECT
  schemaname || '.' || relname AS table_name,
  n_tup_upd AS updates,
  n_tup_hot_upd AS hot_updates,
  CASE WHEN n_tup_upd > 0
       THEN ROUND(100.0 * n_tup_hot_upd / n_tup_upd, 2)
       ELSE 100 END AS hot_ratio,
  n_tup_del AS deletes,
  (n_tup_upd + n_tup_del) AS total_activity
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC
LIMIT 15;

A coluna hot_ratio é crítica. Updates HOT (Heap-Only Tuple) não tocam entradas de índice — são baratos e não criam bloat de índice. Quando hot_ratio cai abaixo de 50%, significa que a maioria dos updates está tocando índices também, o que significa que bloat de índice acumula mais rápido e o autovacuum tem mais trabalho. Uma causa comum: uma coluna que é frequentemente atualizada e faz parte de um índice. Se você puder remover essa coluna do índice (ou usar um índice parcial), os updates HOT voltam a subir e a pressão de bloat cai.

Queries longas: thresholds configuráveis

Dead tuples e bloat são assassinos lentos. Queries longas são o sintoma agudo — a query que segura um snapshot, bloqueia o vacuum de reclamar dead tuples, e causa o bloat em primeiro lugar. O PostgreSQL expõe queries ativas em pg_stat_activity:

SELECT
  pid,
  usename,
  application_name,
  state,
  EXTRACT(EPOCH FROM (NOW() - query_start)) AS duration_sec,
  LEFT(query, 4000) AS query
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < NOW() - INTERVAL '3 seconds'
  AND pid <> pg_backend_pid()
ORDER BY query_start
LIMIT 10;

O INTERVAL '3 seconds' é arbitrário — você escolhe o que "longo" significa para sua carga. O agente do PG Monitoring coleta queries acima de 3 segundos por padrão, e o SaaS permite configurar thresholds de alerta por instância:

• Warning: dispara quando a contagem de queries longas excede 5 (detecção de acúmulo).
• Critical: dispara a 15+ queries longas concorrentes.
• Alerta de query única: dispara quando uma query roda por mais de 30 minutos (configurável via LONG_QUERY_ALERT_SECONDS). Abre um incidente dedicado com PID, usuário, aplicação e texto da query — e auto-resolve quando a query termina.

O threshold é por organização e por instância, então um banco de relatórios que rotineiramente roda queries analíticas de 60 segundos não dispara os mesmos alertas que um banco OLTP onde 3 segundos já é problema.

O ciclo de feedback

Esses três problemas estão conectados:

1. Uma query longa segura um snapshot.
2. O vacuum não consegue reclamar dead tuples anteriores àquele snapshot.
3. Dead tuples se acumulam → a tabela incha → sequential scans ficam mais lentos.
4. Scans mais lentos significam queries que rodam por mais tempo → mais snapshots segurados → mais bloat.

Quebrar o ciclo exige ver os três sinais juntos: quais tabelas têm mais dead tuples, quais são as maiores em disco, quais são as mais escritas, e quais queries estão rodando tempo suficiente para bloquear o vacuum. O PG Monitoring coleta os quatro a cada ciclo e os correlaciona em um único dashboard, para você não ter que rodar quatro queries separadas e juntar mentalmente os resultados.

O que fazer sobre isso

• Tuning de autovacuum: se last_autovacuum tem mais de um dia em uma tabela com muita escrita, baixe o autovacuum_vacuum_scale_factor para aquela tabela (ex.: ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05) em vez do padrão 0.2).
• Updates HOT: se hot_ratio está baixo, verifique se uma coluna indexada está sendo atualizada desnecessariamente. Removê-la do índice pode reduzir dramaticamente o bloat.
• Queries longas: se uma única query está bloqueando o vacuum, considere matá-la (pg_terminate_backend(pid)) ou adicionar um statement timeout na string de conexão da aplicação.
• Bloat severo: se uma tabela já está 5× o tamanho esperado, VACUUM FULL ou pg_repack é necessário para encolher fisicamente. VACUUM FULL tranca a tabela; pg_repack não.