Importar grandes volumes de dados no PostgreSQL parece simples — até você tentar importar 170 milhões de registros e descobrir que a abordagem padrão leva horas ou falha antes de terminar.
A Necto Systems documentou o processo completo de importação dos dados públicos do CNPJ (Cadastro Nacional de Pessoas Jurídicas) — 35 arquivos CSV compactados, aproximadamente 170 milhões de registros — com resultado de 21 minutos em hardware de médio porte. Este artigo detalha as decisões que tornaram isso possível.
O Problema com Abordagens Convencionais
INSERT statement por registro é o método mais intuitivo para popular um banco de dados. Também é o mais lento para grandes volumes: cada INSERT cria uma transação, valida constraints, atualiza índices e registra no WAL (Write Ahead Log). Em 170 milhões de registros, isso é inviável.
A solução: o comando COPY do PostgreSQL, que move dados diretamente entre arquivos e tabelas, ignorando grande parte do overhead transacional.
A Arquitetura da Solução
Componentes
- PostgreSQL rodando em Docker com parâmetros customizados para carga em lote
- Python como orquestrador — responsável por processar os arquivos, corrigir inconsistências e acionar o COPY
- Perl para um caso específico de encoding em um arquivo CSV corrompido
Por que Python como Orquestrador
SQL puro não tem flexibilidade para tratar exceções em tempo real: duplicatas, nomes de arquivo com caracteres especiais, encoding incorreto. Python permite detectar e corrigir esses problemas sem interromper o processo — continuando para o próximo arquivo quando um problema é identificado.
Configuração do PostgreSQL para Carga em Lote
As otimizações se dividem em dois grupos:
Parâmetros de inicialização (via Docker):
autovacuum = off
fsync = off
full_page_writes = off
shared_buffers = 768MB
checkpoint_timeout = 15minfsync = off e full_page_writes = off são arriscados em produção normal — se o servidor cair durante a carga, os dados podem ficar corrompidos. Para uma importação de dados públicos que pode ser refeita, o risco é aceitável pelo ganho de performance.
Configurações de runtime:
SET constraint_exclusion = off;
SET maintenance_work_mem = '512MB';
SET synchronous_commit = off;
SET work_mem = '4GB';work_mem = 4GB é crítico para criação de índices após a importação — índices criados com memória insuficiente são dramaticamente mais lentos.
Etapas da Importação
- Desativar triggers nas tabelas de destino antes de começar
- Aumentar memória compartilhada do Docker para 1GB (necessário para criação de índices)
- Processar cada arquivo com Python: descompactar, corrigir encoding, normalizar nomes de coluna
- Executar COPY para cada arquivo processado
- Reativar triggers e criar índices após a carga completa
A criação de índices depois da carga é mais rápida do que manter os índices atualizados durante — especialmente com work_mem elevado.
Resultados
| Hardware | Tempo total |
|---|---|
| Intel Core i7 Dual-Core 3,1 GHz, 16GB DDR3 | 39 minutos |
| Intel i5 12-core 2,60 GHz, 32GB DDR4 | 21 minutos |
Desafios Encontrados
Duplicatas no CNPJ: os dados públicos continham registros duplicados que impediam a criação de chave primária. Solução: identificar e remover duplicatas antes da criação da constraint.
Nomes de arquivo com caracteres especiais: alguns arquivos do pacote tinham caracteres não-ASCII no nome. Python (com pathlib) resolve isso de forma mais limpa do que shell script.
Encoding corrompido: um arquivo CSV tinha encoding inconsistente. Um script Perl de uma linha foi suficiente para converter para UTF-8 antes do processamento.
Quando Essa Abordagem Se Aplica
Importações únicas ou periódicas de grandes volumes — dados governamentais, migração de sistema legado, carga inicial de data warehouse — se beneficiam dessa configuração. Para cargas incrementais contínuas em produção, a abordagem de otimização é diferente (replicação, particionamento, COPY com upsert). Em qualquer caso, qualidade dos dados de origem determina o que chega ao destino — problemas de ETL frequentemente são problemas de qualidade mascarados.
A Necto Systems aplica engenharia de dados em projetos que envolvem grandes volumes, múltiplas fontes e integração com sistemas legados. O problema de performance de banco de dados é frequentemente o gargalo não identificado em projetos que parecem ser de desenvolvimento de software.
Fale com um especialista se sua empresa enfrenta gargalos de volume de dados.
Perguntas Frequentes
O que é o comando COPY do PostgreSQL e por que ele é mais rápido que INSERT? COPY é o comando nativo do PostgreSQL para mover dados em lote entre arquivos e tabelas. Ele é mais rápido que INSERT porque processa múltiplos registros em uma única operação, reduz o overhead de transação e pode ser combinado com configurações que desabilitam temporariamente mecanismos de segurança desnecessários para importações controladas (como fsync).
O que é WAL (Write Ahead Log) no PostgreSQL? WAL é o mecanismo que garante durabilidade no PostgreSQL: todas as mudanças são registradas no log antes de serem aplicadas ao banco. Isso garante recuperação em caso de falha, mas adiciona overhead em operações de escrita intensiva. Para importações em lote de dados que podem ser refeitos, desabilitar ou reduzir o WAL é uma otimização válida.
Como configurar o PostgreSQL para importações de grande volume?
As principais configurações são: fsync=off e full_page_writes=off (para reduzir operações de disco), shared_buffers elevado (para cache de dados em memória), maintenance_work_mem elevado (para criação de índices), e synchronous_commit=off (para reduzir latência de confirmação). Essas configurações devem ser revertidas após a importação para operação normal.
Por que criar índices depois da importação, não antes?
Manter índices atualizados durante a importação significa que cada registro inserido precisa atualizar todos os índices da tabela. Para 170 milhões de registros, isso multiplica o trabalho. Criar os índices uma vez, após a carga completa, é significativamente mais rápido — especialmente com work_mem elevado, que permite que a operação use memória em vez de disco.
Como tratar arquivos CSV com encoding incorreto no Python?
A abordagem mais robusta é detectar o encoding com a biblioteca chardet e converter para UTF-8 antes do processamento. Para casos simples, especificar o encoding diretamente no open() ou no Pandas read_csv() resolve. Para arquivos muito corrompidos, um script Perl ou iconv na linha de comando é frequentemente mais eficiente.
O que é ETL e como ele se relaciona com importação de grandes volumes de dados? ETL (Extração, Transformação e Carga) é o processo de mover dados de uma ou mais fontes para um destino, aplicando transformações no caminho. Importação de grandes volumes é a fase de Carga do ETL — e é onde problemas de performance aparecem com mais frequência. Um pipeline ETL bem desenhado separa cada etapa, torna cada uma testável e facilita a identificação de onde o gargalo está.
Como a Necto Systems trata projetos com grandes volumes de dados? A Necto projeta pipelines de dados que escalam com o volume da operação — desde importações únicas de dados legados até pipelines de ingestão contínua. O trabalho inclui diagnóstico de gargalos de performance, escolha da estratégia de carga adequada ao caso de uso e configuração do ambiente de banco de dados para o volume esperado. Atendemos empresas em agronegócio, setor público e ambiental com volumes de dados que exigem essa atenção.