Consulta de relatório na réplica que cancela sozinha: conflito de recuperação e hot_standby_feedback

O fechamento mensal agrega a tabela ledger_entries, com 310 milhões de lançamentos, e leva cerca de 11 minutos. Na primária, ele competia por cache e I/O com a API. Na réplica, deveria rodar em paz. O que o time viu foi um padrão estranho: o erro nunca aparecia nos primeiros 30 segundos, quase sempre aparecia antes de 1 minuto, e a taxa de falha acompanhava o volume de escrita da primária. Quanto mais lançamentos sendo conciliados durante o dia, mais o relatório morria.

O padrão tem explicação direta. Uma réplica em hot standby faz duas coisas ao mesmo tempo: atende consultas de leitura e aplica, em ordem, o WAL que a primária envia. Às vezes aplicar o próximo registro de WAL destruiria algo de que uma consulta em andamento ainda precisa. O Postgres chama isso de conflito de recuperação e resolve assim: o replay espera um prazo e, se a consulta não terminar, ela é cancelada.

PRIMARIA                                   REPLICA (hot standby)
t=0                                        relatorio comeca, snapshot enxerga a linha versao A
t=20 s  UPDATE troca A por B, commit
t=45 s  autovacuum remove A (ninguem
        na primaria precisa mais dela)
        WAL: "limpeza, horizonte = xid de A" --->  replay recebe o registro de limpeza
                                           o relatorio ainda pode precisar da versao A: CONFLITO
                                           replay espera ate max_standby_streaming_delay (30 s)
t=75 s                                     prazo esgotado: relatorio cancelado
                                           ERROR: canceling statement due to conflict with recovery
                                           replay aplica a limpeza e segue

Quem cede em cada configuracao:
padrao (30 s)                   a consulta e cancelada
hot_standby_feedback = on       a primaria adia a limpeza (acumula tuplas mortas)
max_standby_streaming_delay=-1  o replay espera (a replica fica atrasada)

Os 30 segundos do padrão são o valor padrão de max_standby_streaming_delay. A madrugada tranquila também se explica: sem UPDATE e DELETE na primária, não há versão de linha para limpar, então não há registro de limpeza no WAL e não há conflito. O relatório não tinha um problema de desempenho. Tinha um problema de convivência com o replay.

Na primária, o vacuum só remove uma versão de linha quando nenhuma transação ativa ainda pode enxergá-la. O detalhe é que "nenhuma transação ativa" quer dizer nenhuma transação da primária. Ela não sabe quais snapshots estão abertos na réplica. Para a primária, a versão antiga do lançamento atualizado há 25 segundos é lixo. Para o relatório que começou na réplica há 40 segundos, ela ainda é a versão correta da linha.

Cada registro de limpeza no WAL carrega um horizonte: o identificador da transação mais recente cujas versões foram removidas. Ao aplicar o registro, a réplica verifica se existe consulta com snapshot mais antigo que esse horizonte. Se existir, há conflito de snapshot. E a limpeza não vem só do autovacuum: a poda de página (HOT pruning), disparada por leituras e escritas comuns na primária, gera o mesmo tipo de registro. Por isso desligar ou atrasar o autovacuum não elimina o problema.

Conflito de snapshot é o tipo mais comum, mas não é o único, e a distinção importa porque o remédio mais conhecido só trata um deles.

Um detalhe do prazo costuma surpreender. max_standby_streaming_delay não é "quanto tempo a consulta pode rodar", e sim o atraso total que o replay aceita acumular desde que o WAL chegou. Se a réplica já está 30 segundos atrasada por causa de um conflito anterior, a próxima consulta que conflitar é cancelada praticamente na hora, sem carência. É por isso que, em dias de muita escrita, até consultas de poucos segundos começam a falhar em rajadas.

Antes de mudar qualquer parâmetro, três consultas na réplica dizem com o que você está lidando. A primeira lê pg_stat_database_conflicts, uma visão que só tem valores na réplica e conta cancelamentos por tipo. A segunda mostra os parâmetros em vigor. A terceira mede o atraso do replay em bytes e informa de quando é o dado que a réplica está servindo.

-- Na replica: quantas consultas foram canceladas e por qual tipo de conflito.
SELECT datname,
       confl_snapshot,
       confl_lock,
       confl_bufferpin,
       confl_deadlock,
       confl_tablespace
  FROM pg_stat_database_conflicts
 WHERE datname = current_database();

-- Na replica: os tres parametros que decidem quem perde, a consulta ou o replay.
SELECT name, setting, unit
  FROM pg_settings
 WHERE name IN ('hot_standby_feedback',
                'max_standby_streaming_delay',
                'max_standby_archive_delay');

-- Na replica: quanto WAL ja chegou e ainda nao foi aplicado, e de quando e o dado visivel.
SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS bytes_not_replayed,
       pg_last_xact_replay_timestamp()                                      AS data_as_of,
       now() - pg_last_xact_replay_timestamp()                              AS replay_delay;

No incidente, a leitura foi clara: confl_snapshot em 1.840 no mês, confl_lock em 12 e os demais em zero. Os 12 conflitos de lock coincidiam com o autovacuum truncando o fim de uma tabela de staging que é esvaziada todo dia. Sem essa medição, o time teria ligado hot_standby_feedback e continuado com uma dúzia de falhas mensais sem explicação.

• replay_delay só é confiável quando a primária está escrevendo. Com a primária ociosa, o último commit aplicado envelhece e o valor cresce sem que haja atraso real. Use bytes_not_replayed como sinal principal.
• Em Postgres 14 ou superior, log_recovery_conflict_waits = on registra no log quando o replay fica esperando uma consulta por mais de deadlock_timeout, com o tipo de conflito. É a forma mais barata de saber quem está segurando o replay.
• A mensagem de erro traz um DETAIL que distingue os casos: "User query might have needed to see row versions that must be removed" é snapshot; "User was holding a relation lock for too long" é lock.

Com hot_standby_feedback = on, a réplica informa periodicamente à primária o xmin mais antigo entre as suas consultas. A primária passa a tratar esse xmin como se fosse de uma transação local e deixa de remover as versões de linha que a réplica ainda pode precisar. O conflito de snapshot praticamente desaparece. Foi a primeira correção aplicada, e o relatório parou de falhar no mesmo dia.

Três semanas depois veio a conta. Uma ferramenta de BI conectada à mesma réplica deixou uma consulta ad hoc rodando por 3 horas e 10 minutos. Durante esse tempo, nenhum vacuum da primária conseguiu remover tuplas mortas mais novas que o snapshot daquela consulta, em nenhuma tabela. A ledger_entries foi de 42 GB para 61 GB em duas semanas de episódios parecidos, e as consultas da API ficaram mais lentas sem que nada no código tivesse mudado. O efeito é idêntico ao de uma transação longa aberta na própria primária, com a diferença de que ela não aparece em pg_stat_activity da primária.

-- Na primaria: o xmin que cada replica esta pedindo para preservar e ha quantas transacoes.
SELECT application_name,
       client_addr,
       state,
       backend_xmin,
       age(backend_xmin) AS xmin_age,
       replay_lag
  FROM pg_stat_replication;

-- Na primaria: com slot fisico, o xmin fica guardado no slot mesmo se a replica cair.
SELECT slot_name, active, xmin, age(xmin) AS xmin_age
  FROM pg_replication_slots
 WHERE slot_type = 'physical';

-- Na primaria: tabelas onde o vacuum roda e nao consegue remover as tuplas mortas.
SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum
  FROM pg_stat_user_tables
 ORDER BY n_dead_tup DESC
 LIMIT 10;

O sinal a procurar é xmin_age alto e crescente em pg_stat_replication ou no slot, junto com tabelas em que last_autovacuum é recente e n_dead_tup não cai. Isso significa que o vacuum roda, mas não tem permissão para limpar.

• O feedback não evita conflito de lock nem de buffer pin. Para o caso mais comum de lock, o truncamento do fim da tabela pelo vacuum, use ALTER TABLE ... SET (vacuum_truncate = false) nas tabelas que esvaziam e enchem.
• Sem slot de replicação, o xmin informado se perde quando a conexão de replicação cai, e a primária pode limpar o que a réplica precisava nesse intervalo. Com slot físico, o xmin persiste, e junto com ele o risco: uma réplica parada continua segurando o vacuum.
• Em réplicas em cascata, o feedback sobe a cadeia inteira. Uma consulta longa na réplica da réplica segura o vacuum na primária.
• O custo é proporcional à consulta mais longa da réplica, não à média. Feedback sem teto de duração na réplica é um cheque em branco.

A alternativa é fazer o replay ceder. Com max_standby_streaming_delay maior, ou -1 para esperar sem limite, a consulta nunca é cancelada: o replay fica parado até ela terminar. A primária não é afetada, o vacuum segue limpando normalmente e o custo fica todo na réplica, que passa a servir dado cada vez mais velho enquanto a consulta roda. Isso é inaceitável na réplica que atende leituras da aplicação ou que é candidata a failover, e é perfeitamente aceitável em uma réplica que só existe para relatório.

Por isso a solução definitiva foi separar os papéis. Cada estratégia transfere o custo para um lugar diferente, e a tabela ajuda a escolher onde ele dói menos.

# replica-app: leitura da aplicacao e candidata a failover. Prioridade: dado fresco.
# postgresql.conf (replica-app)
hot_standby_feedback = off
max_standby_streaming_delay = 30s
max_standby_archive_delay = 30s

# replica-reports: relatorios longos. Prioridade: a consulta termina, o replay espera.
# postgresql.conf (replica-reports)
hot_standby_feedback = off
max_standby_streaming_delay = -1
max_standby_archive_delay = -1
log_recovery_conflict_waits = on

-- Na primaria (papeis e parametros de papel sao replicados): teto para o que roda na replica.
ALTER ROLE reporting SET statement_timeout = '15min';
ALTER ROLE reporting SET idle_in_transaction_session_timeout = '60s';

O prazo -1 precisa de um teto vindo de outro lugar, e é esse o papel dos limites no papel reporting. Sem eles, uma consulta esquecida para o replay indefinidamente, o WAL recebido se acumula no disco da réplica e o atraso só cresce. Há também um efeito menos óbvio: enquanto o replay está parado, novas consultas abrem snapshots naquele mesmo ponto antigo e podem prolongar a espera. Com consultas encadeadas sem intervalo, a réplica pode nunca alcançar a primária. Agendar os relatórios com folga e alertar sobre atraso acima de 30 minutos resolve os dois casos.

Mesmo com a infraestrutura certa, o código que lê da réplica precisa tratar o conflito como um resultado esperado. O erro chega com SQLSTATE 40001, o mesmo código de falha de serialização, e a escolha não é acidental: significa que a operação pode ser repetida do zero. Em uma conexão somente leitura, repetir não tem efeito colateral. O conflito também pode encerrar a sessão inteira com um FATAL quando ela está parada dentro de uma transação, então a conexão deve ser descartada, não devolvida ao pool.

// replica-report.js
const RECOVERY_CONFLICT = '40001';

const sleep = (ms) => new Promise((resolve) => setTimeout(resolve, ms));

export async function withReplicaSnapshot(replicaPool, work, { maxAttempts = 4 } = {}) {
  for (let attempt = 1; attempt <= maxAttempts; attempt += 1) {
    let client;
    try {
      client = await replicaPool.connect();
      // Um snapshot unico para o relatorio inteiro: todas as consultas enxergam o mesmo instante.
      await client.query('BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY');
      // O relatorio informa de quando e o dado, em vez de fingir que e de agora.
      const { rows } = await client.query('SELECT pg_last_xact_replay_timestamp() AS data_as_of');
      const result = await work(client);
      await client.query('COMMIT');
      client.release();
      return { result, dataAsOf: rows[0].data_as_of, attempt };
    } catch (error) {
      // Conflito de recuperacao pode encerrar a conexao (FATAL): descarta em vez de devolver ao pool.
      if (client) client.release(error);
      // 40001 em replica somente leitura = conflito de recuperacao: repetir do zero e seguro.
      if (error.code !== RECOVERY_CONFLICT || attempt === maxAttempts) throw error;
      const wait = Math.min(1000 * 2 ** attempt, 20000) + Math.floor(Math.random() * 1000);
      console.warn('recovery conflict on replica (attempt ' + attempt + '), retrying in ' + wait + ' ms');
      await sleep(wait);
    }
  }
}

export async function monthlyClosing(replicaPool, tenantId, month) {
  return withReplicaSnapshot(replicaPool, async (client) => {
    const totals = await client.query(
      `SELECT account_id, sum(amount) AS total
         FROM ledger_entries
        WHERE tenant_id = $1 AND posted_at >= $2::date AND posted_at < $2::date + interval '1 month'
        GROUP BY account_id`,
      [tenantId, month],
    );
    const pending = await client.query(
      `SELECT count(*)::int AS open_items
         FROM ledger_entries
        WHERE tenant_id = $1 AND posted_at < $2::date + interval '1 month' AND reconciled_at IS NULL`,
      [tenantId, month],
    );
    return { totals: totals.rows, openItems: pending.rows[0].open_items };
  });
}

Dois detalhes do código valem mais que a retentativa. O primeiro é o REPEATABLE READ: sem ele, cada consulta do relatório abre um snapshot novo, e os totais e os itens em aberto podem ser de instantes diferentes, o que em relatório financeiro vira diferença de centavos impossível de explicar. O segundo é devolver data_as_of: na réplica de relatório o dado pode ter 15 minutos, e o relatório deve dizer "dados até 14:32" em vez de deixar o usuário descobrir sozinho.

1. Alerte sobre o crescimento de confl_snapshot e confl_lock por réplica. Um salto indica mudança no padrão de escrita da primária ou relatório novo sem teto.
2. Na réplica de relatório, alerte sobre bytes_not_replayed e atraso acima do maior relatório previsto. Na réplica da aplicação, o limite é de segundos.
3. Na primária, alerte sobre xmin_age das réplicas e dos slots. É o único sinal que mostra uma consulta remota segurando o vacuum.
4. Quebre exportações gigantes em páginas por chave. Cada consulta dura segundos e sobrevive ao prazo padrão, ao custo de perder o snapshot único, o que é aceitável para exportação e não para fechamento.