Batch operations: COPY e multi-row INSERT para performance

1. Introdução à Inserção em Lote

Inserir dados linha por linha em um banco de dados relacional é uma das operações mais ineficientes que um desenvolvedor pode executar. Cada INSERT individual gera overhead significativo: uma transação separada, parsing completo da instrução SQL, planejamento de execução, e logging no Write-Ahead Log (WAL). Em um cenário de ingestão de 100.000 linhas, o custo acumulado dessas operações individuais pode tornar o processo centenas de vezes mais lento que uma abordagem em lote.

O conceito de batch operations é simples: agrupar múltiplas linhas em uma única operação de banco de dados. Em vez de enviar 100 comandos INSERT, enviamos um comando que insere 100 linhas de uma só vez. Isso reduz drasticamente o número de round trips entre aplicação e banco, elimina parsing redundante, e otimiza o logging transacional.

Cenários ideais para batch incluem processos de ETL (Extract, Transform, Load), migrações de dados entre sistemas, ingestão de logs de aplicação, e qualquer operação onde grandes volumes de dados precisam ser carregados em lote.

2. Multi-row INSERT: Sintaxe e Mecanismos

A sintaxe do multi-row INSERT é direta e suportada por praticamente todos os bancos relacionais modernos:

INSERT INTO usuarios (nome, email, data_cadastro)
VALUES 
    ('Ana Silva', 'ana@email.com', '2024-01-15'),
    ('Carlos Souza', 'carlos@email.com', '2024-01-15'),
    ('Maria Oliveira', 'maria@email.com', '2024-01-15');

O mecanismo por trás dessa otimização é a redução de overhead. Um único statement é parseado e planejado uma vez pelo otimizador, e o executor processa todas as linhas em sequência, compartilhando o mesmo plano de execução.

Existem limites práticos a considerar. Cada banco impõe um número máximo de linhas por statement (no PostgreSQL, por exemplo, não há limite explícito, mas o tamanho total do pacote SQL é limitado por configuração). Como regra geral, batches entre 100 e 1000 linhas oferecem bom equilíbrio entre performance e gerenciamento de memória.

3. COPY: O Comando de Alto Desempenho

O comando COPY do PostgreSQL representa o estado da arte em inserção em lote. Sua sintaxe básica:

COPY usuarios (nome, email, data_cadastro) 
FROM '/tmp/dados.csv' 
DELIMITER ',' 
CSV HEADER;

Para uso programático, o modo mais comum é via STDIN:

COPY usuarios (nome, email, data_cadastro) 
FROM STDIN 
DELIMITER ',' 
CSV;

O COPY opera em dois modos principais. O modo texto (CSV) é mais flexível e legível, mas requer parsing adicional. O modo binário (formato nativo) é significativamente mais rápido, pois transfere dados no formato interno do PostgreSQL, sem necessidade de conversão.

Comparando com multi-row INSERT, o COPY é superior em cenários de alto volume (acima de 10.000 linhas). Ele bypassa completamente o parser SQL, escreve diretamente nas páginas de dados, e otimiza o logging WAL. Para volumes menores, a diferença é menos perceptível.

4. Otimizações Internas no Processamento Batch

O Write-Ahead Log (WAL) é um dos principais gargalos em inserções em lote. Cada modificação no banco gera registros WAL para garantir durabilidade. Em batches grandes, o volume de WAL pode se tornar significativo.

Tabelas UNLOGGED oferecem uma alternativa para cenários onde a durabilidade não é crítica:

CREATE UNLOGGED TABLE staging_usuarios (
    id SERIAL PRIMARY KEY,
    nome TEXT,
    email TEXT
);

Inserções em tabelas UNLOGGED não geram WAL, resultando em velocidade 5-10x maior. O trade-off é a perda de dados em caso de crash — ideal para tabelas de staging temporário.

Índices e constraints são outro ponto crítico. Cada linha inserida em uma tabela indexada requer atualização de todos os índices relevantes. Para batches grandes, considere:

  • Remover índices não essenciais antes do batch e recriá-los depois
  • Usar ALTER TABLE ... SET UNLOGGED temporariamente
  • Desabilitar triggers e constraints de foreign key durante a carga

5. Estratégias de Tamanho de Lote

Encontrar o batch size ideal requer experimentação empírica. O tamanho depende de vários fatores:

  • Largura das linhas (número e tamanho das colunas)
  • Memória disponível no servidor
  • Latência de rede entre aplicação e banco
  • Capacidade de processamento do banco

Uma abordagem prática é começar com batches de 500 linhas e aumentar gradualmente até encontrar degradação de performance. Para conjuntos de dados gigantes (milhões de linhas), use chunking:

-- Processar em lotes de 10.000 usando CTIDs
WITH batch AS (
    SELECT ctid FROM usuarios 
    WHERE processado = false 
    LIMIT 10000
)
UPDATE usuarios 
SET processado = true 
WHERE ctid IN (SELECT ctid FROM batch);

6. Tratamento de Erros e Transações

Batches grandes introduzem complexidade no tratamento de erros. A atomicidade tradicional (tudo ou nada) pode ser contraproducente: se uma linha em 100.000 falha, todo o batch é revertido.

Estratégias recomendadas:

-- Usar savepoints para batches parciais
BEGIN;
SAVEPOINT batch_1;
INSERT INTO usuarios VALUES (...), (...), ...;
-- Se falhar:
ROLLBACK TO SAVEPOINT batch_1;
-- Registrar erro e continuar
SAVEPOINT batch_2;
...
COMMIT;

Para logs de falhas, mantenha um arquivo separado ou tabela de erros:

CREATE TABLE erros_carga (
    linha_original TEXT,
    erro TEXT,
    timestamp TIMESTAMP DEFAULT NOW()
);

7. Casos Práticos e Comparações de Performance

Benchmark com 1.000 linhas em tabela sem índices:

  • INSERT individual: ~850ms
  • Multi-row INSERT (100 linhas/batch): ~45ms
  • COPY: ~12ms

Exemplo com Python/psycopg2:

import psycopg2
from psycopg2.extras import execute_values

conn = psycopg2.connect("dbname=teste")
cur = conn.cursor()

# Multi-row INSERT
dados = [('Ana', 'ana@email.com'), ('Carlos', 'carlos@email.com')]
execute_values(cur, 
    "INSERT INTO usuarios (nome, email) VALUES %s",
    dados)

# COPY
from io import StringIO
buffer = StringIO()
buffer.write("Ana,ana@email.com\nCarlos,carlos@email.com\n")
buffer.seek(0)
cur.copy_from(buffer, 'usuarios', sep=',')
conn.commit()

Exemplo com Java/JDBC:

Connection conn = DriverManager.getConnection(url);
conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement(
    "INSERT INTO usuarios (nome, email) VALUES (?, ?)");

for (int i = 0; i < 1000; i++) {
    ps.setString(1, "Usuario " + i);
    ps.setString(2, "email" + i + "@test.com");
    ps.addBatch();

    if (i % 100 == 0) {
        ps.executeBatch();
        conn.commit();
    }
}
ps.executeBatch();
conn.commit();

8. Boas Práticas e Armadilhas Comuns

Armadilhas frequentes:

  • Muitas constraints: Foreign keys e triggers disparam para cada linha, anulando ganhos de batch
  • Índices pesados: Cada inserção atualiza índices B-tree e GiST
  • Transações gigantes: Podem causar wraparound de transaction ID e bloat no WAL

Monitoramento essencial:

-- Verificar queries lentas
SELECT query, calls, total_time 
FROM pg_stat_statements 
ORDER BY total_time DESC;

-- Analisar plano de execução
EXPLAIN ANALYZE 
INSERT INTO usuarios VALUES (...), (...), ...;

Quando NÃO usar batch: sistemas OLTP de alta concorrência, onde inserções individuais minimizam contenção de locks. Para aplicações web com milhares de usuários simultâneos, batches podem causar deadlocks e escalonamento.

Referências