PostgreSQL - Inserir vários registros para testes
Durante o desenvolvimento, muitas vezes precisamos popular uma tabela no banco de dados para realizar testes ou simulações. Quando o volume de registros é grande, inserir dados manualmente se torna inviável e pouco eficiente.
Uma solução prática é utilizar funções de geração em série, como generate_series() no PostgreSQL, que permitem criar rapidamente centenas, milhares ou até milhões de registros de forma automatizada. Essa abordagem facilita testes de desempenho, validação de consultas e simulações realistas sem sobrecarregar o processo com inserções manuais.
Como exemplo, vamos criar a seguinte tabela para ilustrar a inserção de dados:
Em seguida, podemos inserir 1.000 registros de forma simples:
Também é possível gerar valores aleatórios utilizando a função RANDOM para tornar os dados mais variados.
Para exemplificar, vamos inserir mais 1.000 registros e, para variar o uso da função de geração em série, o id será criado dentro do intervalo de 1001 a 3000, alternando de 2 em 2. Isso demonstra como combinar séries e valores randômicos para popular uma tabela de forma mais dinâmica e realista:
Inserindo dados em grande volume com batches
Agora vamos criar uma versão alternativa para inserção massiva de 10 milhões de registros em uma tabela. Em vez de priorizar velocidade, essa abordagem foca em maior controle e estabilidade durante o processo, especialmente em servidores com menos memória ou quando é importante evitar transações muito grandes.
A ideia é gerar os dados em blocos (batches) usando um bloco anônimo (DO $$ ... $$) e CTEs recursivas, inserindo por lotes de 100.000 registros.
Para esse exemplo, vamos criar uma tabela representando as notas fiscais de um sistema.
O que essa abordagem traz:
No bloco anônimo, cada batch é processado de forma independente, evitando transações gigantes que podem travar o servidor. Além disso, o RAISE NOTICE oferece feedback contínuo, algo impossível em uma única instrução massiva.
Essa técnica é especialmente útil para testes de carga e simulações, garantindo um processo mais estável e previsível, mesmo que não necessariamente mais rápido.
Desempenho máximo com COPY
Para quem busca máxima performance, o comando COPY é o campeão. Ele permite importar dados diretamente de arquivos (como CSVs) de forma sequencial, eliminando o overhead de parsing e planejamento de query que ocorre em cada batch do INSERT.
Enquanto o bloco anônimo ainda precisa processar cada lote internamente (executando operações de log e verificação linha a linha), o COPY grava os dados diretamente nas páginas da tabela, tornando o processo muito mais rápido e eficiente.
Em cenários de milhões de registros, o COPY pode ser até 5 vezes mais rápido que a abordagem em batches, sendo a melhor escolha para cargas massivas e importações em larga escala.
Dicas importantes
Antes de concluir, quero deixar três dicas sobre cuidados importantes a se ter:
Conclusão
Particularmente, eu prefiro a abordagem com INSERT direto usando generate_series(), por ser simples, eficiente e fácil de executar. Essa forma costuma entregar excelente desempenho e é ideal para gerar grandes volumes de dados de forma prática em ambientes de desenvolvimento e testes.
Ainda assim, o uso do bloco anônimo (DO $$ ... $$) tem o seu valor, principalmente em servidores com menos memória, quando há necessidade de controle de batches ou em situações onde se deseja evitar transações muito grandes. Além disso, ele permite acompanhar o andamento da execução em tempo real por meio do comando RAISE NOTICE, o que é bastante útil em cargas longas. Nesses cenários, dividir a inserção em blocos pode tornar o processo mais estável, controlado e previsível, mesmo que não necessariamente mais rápido.
Por outro lado, para quem busca máximo desempenho, especialmente em cenários de milhões de registros, o COPY continua sendo a melhor escolha. Rápido, seguro e otimizado para inserções em massa.
No fim das contas, cada abordagem tem seu propósito. O importante é escolher aquela que melhor se adapta ao contexto e aos recursos disponíveis.
Leitura complementar
Uma solução prática é utilizar funções de geração em série, como generate_series() no PostgreSQL, que permitem criar rapidamente centenas, milhares ou até milhões de registros de forma automatizada. Essa abordagem facilita testes de desempenho, validação de consultas e simulações realistas sem sobrecarregar o processo com inserções manuais.
Como exemplo, vamos criar a seguinte tabela para ilustrar a inserção de dados:
CREATE TABLE funcionario ( id INTEGER, nome TEXT, nascimento DATE, funcao TEXT, salario NUMERIC(8, 2) );
Em seguida, podemos inserir 1.000 registros de forma simples:
INSERT INTO
funcionario
SELECT
serie.id,
'João',
'1983-03-17',
'Vendedor',
2400.00
FROM
generate_series(1, 1000) AS serie (id);
Também é possível gerar valores aleatórios utilizando a função RANDOM para tornar os dados mais variados.
Para exemplificar, vamos inserir mais 1.000 registros e, para variar o uso da função de geração em série, o id será criado dentro do intervalo de 1001 a 3000, alternando de 2 em 2. Isso demonstra como combinar séries e valores randômicos para popular uma tabela de forma mais dinâmica e realista:
INSERT INTO
funcionario
SELECT
serie.id,
(string_to_array('João,Maria,José,Antônio', ','))[floor((random() * 4 + 1))],
'01-01-1990'::DATE - (random() * ('01-01-1990'::DATE - '01-01-1970'))::INTEGER,
(string_to_array('Vendedor,Gerente', ','))[floor((random() * 2 + 1))],
floor(random() * 3000)
FROM
generate_series(1001, 3000, 2) AS serie (id);
Inserindo dados em grande volume com batches
Agora vamos criar uma versão alternativa para inserção massiva de 10 milhões de registros em uma tabela. Em vez de priorizar velocidade, essa abordagem foca em maior controle e estabilidade durante o processo, especialmente em servidores com menos memória ou quando é importante evitar transações muito grandes.
A ideia é gerar os dados em blocos (batches) usando um bloco anônimo (DO $$ ... $$) e CTEs recursivas, inserindo por lotes de 100.000 registros.
Para esse exemplo, vamos criar uma tabela representando as notas fiscais de um sistema.
-- 1. Criação da tabela de notas fiscais
CREATE TABLE nota_fiscal
(
id BIGINT PRIMARY KEY,
data_emissao DATE,
valor NUMERIC(12,2),
nome_cliente TEXT
);
-- 2. Inserção em blocos de 100.000 registros
DO $$
DECLARE
batch_size INT := 100000;
total_rows BIGINT := 10000000;
start_id BIGINT := 1;
end_id BIGINT;
BEGIN
WHILE start_id <= total_rows LOOP
end_id := least(start_id + batch_size - 1, total_rows);
INSERT INTO nota_fiscal (id, data_emissao, valor, nome_cliente)
SELECT
serie.id,
('2009-01-01'::DATE + (random() * 3652)::INT),
(50 + random() * (5000 - 50))::NUMERIC(12,2),
(string_to_array('João,Maria,José,Antônio,Carla,Paulo,Ana,Lucas', ','))[floor(random() * 8 + 1)]
FROM
generate_series(start_id, end_id) AS serie(id);
RAISE NOTICE 'Inseridos registros de % até %', start_id, end_id;
start_id := end_id + 1;
END LOOP;
END
$$;
O que essa abordagem traz:
- Batch de 100k: mais seguro para servidores com menos memória.
- Monitoramento do progresso: RAISE NOTICE permite acompanhar a execução em tempo real.
- Datas e valores aleatórios: gera intervalos e valores variados de forma automática.
No bloco anônimo, cada batch é processado de forma independente, evitando transações gigantes que podem travar o servidor. Além disso, o RAISE NOTICE oferece feedback contínuo, algo impossível em uma única instrução massiva.
Essa técnica é especialmente útil para testes de carga e simulações, garantindo um processo mais estável e previsível, mesmo que não necessariamente mais rápido.
Desempenho máximo com COPY
Para quem busca máxima performance, o comando COPY é o campeão. Ele permite importar dados diretamente de arquivos (como CSVs) de forma sequencial, eliminando o overhead de parsing e planejamento de query que ocorre em cada batch do INSERT.
Enquanto o bloco anônimo ainda precisa processar cada lote internamente (executando operações de log e verificação linha a linha), o COPY grava os dados diretamente nas páginas da tabela, tornando o processo muito mais rápido e eficiente.
Em cenários de milhões de registros, o COPY pode ser até 5 vezes mais rápido que a abordagem em batches, sendo a melhor escolha para cargas massivas e importações em larga escala.
Dicas importantes
Antes de concluir, quero deixar três dicas sobre cuidados importantes a se ter:
- Agrupe inserções em transações: Inserir registros em lotes dentro de uma única transação é muito mais eficiente do que inserir registro por registro com commits individuais, reduzindo overhead e melhorando a performance.
- Use batches controlados: Tentar inserir milhões de registros em uma única transação pode sobrecarregar a memória e gerar locks prolongados. Dividir a inserção em batches, como 100.000 ou 500.000 registros por vez, mantém o processo estável e permite acompanhar o progresso.
- Gerencie índices durante a inserção: Se a tabela possui índices, removê-los ou desabilitá-los temporariamente durante a inserção pode acelerar significativamente o processo. Após concluir a inserção, recrie os índices para restaurar a performance de consultas.
Conclusão
Particularmente, eu prefiro a abordagem com INSERT direto usando generate_series(), por ser simples, eficiente e fácil de executar. Essa forma costuma entregar excelente desempenho e é ideal para gerar grandes volumes de dados de forma prática em ambientes de desenvolvimento e testes.
Ainda assim, o uso do bloco anônimo (DO $$ ... $$) tem o seu valor, principalmente em servidores com menos memória, quando há necessidade de controle de batches ou em situações onde se deseja evitar transações muito grandes. Além disso, ele permite acompanhar o andamento da execução em tempo real por meio do comando RAISE NOTICE, o que é bastante útil em cargas longas. Nesses cenários, dividir a inserção em blocos pode tornar o processo mais estável, controlado e previsível, mesmo que não necessariamente mais rápido.
Por outro lado, para quem busca máximo desempenho, especialmente em cenários de milhões de registros, o COPY continua sendo a melhor escolha. Rápido, seguro e otimizado para inserções em massa.
No fim das contas, cada abordagem tem seu propósito. O importante é escolher aquela que melhor se adapta ao contexto e aos recursos disponíveis.
Leitura complementar
Comentários
Postar um comentário