A maior economia vem de duas regras simples. A primeira: se nada relevante mudou, não registre. A segunda: quando algo mudou, guarde só as colunas que mudaram, com o valor antigo e o novo. Em INSERT guarda-se o estado novo, em DELETE o estado antigo, e em UPDATE apenas a diferença. O gatilho abaixo implementa isso comparando os dois JSONB, aceita uma lista de colunas ruidosas como argumento e devolve sem gravar nada quando só elas mudaram.
-- Tabela nova, particionada por mes. A PK inclui a chave de particao.
CREATE TABLE auditoria_v2 (
id bigserial,
ocorrido_em timestamptz NOT NULL DEFAULT now(),
tabela text NOT NULL,
registro_id text NOT NULL,
operacao char(1) NOT NULL CHECK (operacao IN ('I', 'U', 'D')),
ator text,
antes jsonb,
depois jsonb,
PRIMARY KEY (id, ocorrido_em)
) PARTITION BY RANGE (ocorrido_em);
-- Grava so o que mudou. Colunas ruidosas passam como argumento do gatilho e sao ignoradas.
-- SECURITY DEFINER: a aplicacao nao precisa (nem deve ter) permissao de escrita na auditoria.
CREATE OR REPLACE FUNCTION auditar_alteracao() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, pg_temp AS $
DECLARE
ignoradas text[] := COALESCE(TG_ARGV, ARRAY[]::text[]);
chave text;
antes jsonb;
depois jsonb;
mudou jsonb;
BEGIN
IF TG_OP = 'INSERT' THEN
chave := NEW.id::text;
depois := to_jsonb(NEW);
ELSIF TG_OP = 'DELETE' THEN
chave := OLD.id::text;
antes := to_jsonb(OLD);
ELSE
chave := NEW.id::text;
antes := to_jsonb(OLD);
SELECT jsonb_object_agg(n.key, n.value) INTO mudou
FROM jsonb_each(to_jsonb(NEW)) AS n
WHERE n.key <> ALL (ignoradas)
AND (antes -> n.key) IS DISTINCT FROM n.value;
IF mudou IS NULL THEN
RETURN NULL; -- so colunas ruidosas mudaram: nada a registrar
END IF;
SELECT jsonb_object_agg(o.key, o.value) INTO antes
FROM jsonb_each(antes) AS o
WHERE mudou ? o.key;
depois := mudou;
END IF;
INSERT INTO auditoria_v2 (tabela, registro_id, operacao, ator, antes, depois)
VALUES (TG_TABLE_NAME, chave, left(TG_OP, 1),
NULLIF(current_setting('app.usuario_id', true), ''), antes, depois);
RETURN NULL;
END;
$;
-- Troca do gatilho antigo pelo novo em uma transacao curta, com lock_timeout para nao formar fila.
BEGIN;
SET LOCAL lock_timeout = '2s';
DROP TRIGGER IF EXISTS auditar ON pedidos;
CREATE TRIGGER auditar
AFTER INSERT OR UPDATE OR DELETE ON pedidos
FOR EACH ROW EXECUTE FUNCTION auditar_alteracao('atualizado_em', 'ultimo_acesso', 'visualizacoes');
COMMIT;
- O gatilho é AFTER ... FOR EACH ROW e devolve NULL, o que é válido nesse tipo, e roda na mesma transação do UPDATE. Se a transação for revertida, o registro some junto, o que é o comportamento certo para auditoria.
- SECURITY DEFINER com search_path fixo faz a função gravar com o dono, para que o papel da aplicação não precise de permissão de escrita na auditoria. Sem o search_path fixo, a função ficaria sujeita a sequestro por objetos criados em outro esquema.
- O argumento da lista de colunas ignoradas fica no CREATE TRIGGER, ao lado da tabela. Cada tabela pode ter a sua lista sem copiar a função.
- O ator vem de uma configuração local da transação, preenchida pela aplicação. Se ela não estiver definida, o campo fica nulo, e uma consulta por linhas sem ator é um bom alerta de alteração feita fora do caminho esperado, como um acesso direto ao banco.
- O código assume uma coluna id em cada tabela auditada. Para chave composta, concatene as colunas em chave antes do INSERT.
Para o ator, a aplicação precisa informar quem está alterando dentro da mesma transação. O trecho em Node.js usa set_config com o terceiro argumento verdadeiro, que limita o valor à transação. Isso importa com PgBouncer em modo de transação, em que a mesma conexão atende clientes diferentes em sequência e uma configuração de sessão vazaria de um usuário para outro.
// com-ator.js - informa ao gatilho quem esta alterando, sem mudar nenhuma consulta existente.
import pg from 'pg';
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
export async function comAtor(usuarioId, trabalho) {
const client = await pool.connect();
try {
await client.query('BEGIN');
// Terceiro argumento true: o valor vale so ate o fim da transacao. Por isso funciona
// com PgBouncer em modo transaction e nao vaza para a proxima requisicao da conexao.
await client.query("SELECT set_config('app.usuario_id', $1, true)", [String(usuarioId)]);
const resultado = await trabalho(client);
await client.query('COMMIT');
return resultado;
} catch (erro) {
await client.query('ROLLBACK');
throw erro;
} finally {
client.release();
}
}
// Uso: o UPDATE e o registro de auditoria nascem na mesma transacao, com o mesmo ator.
await comAtor(42, (client) =>
client.query('UPDATE pedidos SET status = $1 WHERE id = $2', ['pago', 981]),
);
No cenário medido, a linha média de auditoria caiu de 1,1 KB para 140 bytes, o volume diário de 14 milhões para 5,3 milhões de linhas, e o gatilho de 4,8 ms para 1,2 ms. Cada UPDATE ficou, no p95, em 10,4 ms. O ganho veio quase todo de gravar menos, não de um truque de otimização.