PostgreSQL: Configuração para importação massiva - 160.000.000 de registros em minutos

O Governo brasileiro mantém uma publicação mensal dos dados do CNPJ - cadastro nacional de pessoa jurídica. Para novembro de 2024, são 35 arquivos compactados em format ".zip" (link para download no final do texto)

Um dos nossos clientes, nos trouxe a demanda para melhorar sua aplicaçao que mantém um banco de dados local atualziado com os dados do CNPJ.

O banco do nosso cliente atende outras aplicações internas e deveria ser mantido em um servidor Microsoft SQL Server. Fizemos a manutenção do código com algumas melhorias e houve algum ganho no tempo de atualização dos dados - mas ainda levava algumas horas. Não exatamente um problema para um dado atualizado a cada 4 ou 5 semanas.

A dificuldade da importação tanto pelo tamanho dos arquivos quanto peloo tempo de importação, foi pauta de algumas discussões dentro do time da Necto. Então decidimos fazer outros testes mesmo já tendo entergue uma solução para o nosso cliente.

A necto há anos usa o PostgreSQL como banco de dados, é nossa primeira escolha sempre que possível. É um banco de dados robusto, de código aberto, e que não perde em nada para outros SGDBs do mercado. E resolvemos fazer alguns testes com o PostgreSQL para importar os dados do CNPJ.

Os dados do CNPJ são disponibilizados em arquivos CSV compactados em formato ZIP. Hoje (2024) são 35 arquivos ZIP, que cotem algo em torno de 170.000.000 de registros - CNPJs únicos em torno de ~60.000.000.

Com os dados em mãos, usamos o comando `COPY` do PostgreSQL

COPY moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already). COPY TO can also copy the results of a SELECT query.

_{^{source: https://www.postgresql.org/docs/16/sql-copy.html}}

Algumas configurações foram feitas na tentativa de melhorar a performance da importação.

São muitas possibilidade de configuração do Postgresql, algumas devem ser feitas na inicialização do PostgreSQL outras podem ser feitas em tempo de execução.

Veja aqui uma lista completa dos parâmetros que são possíveis alterar para execução do PostgreSQL (https://www.postgresql.org/docs/16/runtime-config.html).

No caso de importações massivas de atenção a configuração do "WAL" (Write ahed log - https://www.postgresql.org/docs/16/wal-configuration.html)

Estes parâmetros podem ser definidos no arquivo de configuração do PostgreSQL. Como estamos usando uma imagem docker, passamos como parâmetro no `command` do docker-compose .

Outros parâmetros, são alterados em tempo de execução ...

e ainda, alguns outros logo antes da execução dos comandos SQL como o `INSERT INTO`.

Abaixo os resultados e o tempo de importação em 2 maquinas distintas.

As especificações estão abaixo.

Tivemos problemas durante a criação dos índices, e tivemos que altera a "shared memory" do docker - `shm_size: '1gb'` - veja no arquivo "docker-compose" no repositório.

O Python foi usado principalmente como um orquestrador, mas também faz algumas pequenas correções no dado e nome dos arquivos. O primeiro é devido ao um registro de CNPJ duplicado, o que impedia a criação da PK. O o outro é que alguns nomes de arquivos originais tem um caracter `$` no meio do nome.

Ainda há um problema de codificação em um dos arquivos CSV, que perdura entre as verões publicadas, então o Python executa um script Perl - `perl -i -pe 's/\\x00//g' {fle}` para corrigir o problema em tempo de execução.

Escrever em Python nos deu maior maleabilidade para controlar o processo todo. Mas sim, seria possível fazer tudo somente usando PSQL.

Repository: https://github.com/znc-sistemas/cnpj_import

For more details, check out: https://www.postgresql.org/docs/16/runtime-config-resource.html.