Início / dba / Perguntas / 232777
Accepted
Imanol Y.
Asked: 2019-03-22 10:30:20 +0800 CST

Maneira mais rápida de extrair tabela completa no Postgres

  • 772

Estou tentando despejar uma tabela com 50M de registros em um arquivo, e meu objetivo é reduzir o tempo em que esta ação é executada. Eu costumo usar o COPY metrics TO 'metrics.csv' DELIMITER ',' CSV;Isso pode levar uma hora nos melhores casos. Também estou interessado em exportar os dados em algum formato simples (evite usar pd_dumpdiretórios).

Uma das ideias é de alguma forma acessar essa tabela por uma condição ou cursor que divida a tabela inteira em pedaços de tamanhos iguais, para que você possa realizar por exemplo 2 consultas de cópia ao mesmo tempo reduzindo o tempo pela metade.

Exemplo:

COPY (SELECT * FROM metrics WHERE id < 25000000) TO 'metrics_1.csv' DELIMITER ',' CSV;
COPY (SELECT * FROM metrics WHERE id >= 25000000) TO 'metrics_2.csv' DELIMITER ',' CSV;

Os índices parciais criados nessas condições podem ajudar?

Alguma idéia de uma boa maneira de implementar esses despejos de cópia parcial de uma tabela? Existe alguma outra solução para despejar esta tabela mais rapidamente?

Postgresql 11 / 100 GB de RAM / 20 núcleos.

Após alguma paralelização com COPYlimites de E/S não parece ser o gargalo.

insira a descrição da imagem aqui

postgresql dump

1 respostas

  1. Best Answer

    Sua ideia atual de usar uma consulta de intervalo na chave primária é provavelmente a melhor aposta. Não vejo como índices parciais podem ajudar com isso. Você precisaria de uma série de índices parciais que, em conjunto, seriam um índice total e com índices BTREE usados ​​para consultas de intervalo que seriam inúteis. Mas, o sucesso desse método provavelmente dependerá das linhas de sua tabela serem ordenadas fisicamente aproximadamente pelo valor da chave primária. Você pode impor essa ordenação usando o comando CLUSTER, mas essa é uma operação muito cara de se fazer.

    Se você quiser que o PostgreSQL paralelize isso para você, você deve usar uma consulta fictícia que seleciona tudo:

    COPY (SELECT * FROM metrics) TO 'metrics.csv' DELIMITER ',' CSV;

    e você provavelmente também precisará diminuir drasticamente a "configuração de parallel_tuple_cost", talvez até zero. Mas isso provavelmente não lhe dará uma melhoria real, porque o gargalo (se não for IO de disco) estará na conversão de seus dados de seu formato binário interno para o formato de texto que será gerado pelo COPY. Essa conversão é sempre feita no processo líder, não nos trabalhadores paralelos.

    Você pode contornar isso escrevendo suas consultas para que o trabalho pesado seja feito explicitamente na própria consulta:

    COPY (SELECT col1::text, col2::text, col3::text, ... FROM metrics) TO 'metrics.csv' DELIMITER ',' CSV;

    Agora, o processo principal ainda precisa varrer todo esse texto para coisas que precisam ser escapadas/citadas, mas pelo menos não precisa converter JSONB em texto para todas as linhas por si só. Nas minhas mãos, usar 7 trabalhadores paralelos (8 processos incluindo o líder) reduz o tempo necessário COPY...TOpela metade. A sobrecarga torna-se então a comunicação dos trabalhadores paralelos para o processo principal. A maneira de remover isso é voltar suas sugestões originais de consultas de intervalo indexado em sessões separadas.

    • 4

relate perguntas