Início / dba / Perguntas / 55568
Accepted
Milovan Zogovic
Asked: 2013-12-25 10:48:51 +0800 CST

Como copiar com eficiência milhões de linhas de uma tabela para outra no Postgresql?

  • 772

Eu tenho duas tabelas de banco de dados. Um contém centenas de milhões de registros. Vamos chamar aquele history. O outro é calculado diariamente e quero copiar todos os seus registros para historyum.

O que eu fiz foi rodar:

INSERT INTO history SELECT * FROM daily

E funcionou por um tempo, mas começou a ficar cada vez mais lento à medida que o número de discos continuava crescendo. Agora eu tenho cerca de 2 milhões de registros que precisam ser copiados de dailypara historyem uma única operação e demora muito para ser concluído.

Existe outra maneira mais eficiente de copiar dados de uma tabela para outra?

postgresql

5 respostas

  1. Despeje a tabela no formato csv

    COPY table TO '/tmp/table.csv' DELIMITER ',';

    use o comando COPY que é muito mais eficiente para grandes quantidades de dados.

    COPY table FROM '/tmp/table.csv' DELIMITER ',';

    Verifique os documentos do postgres em http://www.postgresql.org/docs/current/static/sql-copy.html para obter mais informações

    • 18

  2. O problema era com os índices. A historytabela tinha 160 milhões de linhas indexadas. Ao executar qualquer um COPY FROMou INSERT INTO .. SELECTestava demorando muito para não inserir linhas, mas para atualizar índices. Quando desativei os índices, importou 3 milhões de linhas em 10 segundos. Agora eu preciso encontrar uma maneira mais rápida de reindexar a tabela grande.

    • 18

  3. Você pode usar a ferramenta psql , eu posso ser eficiente, como o seguinte,

    psql -h ${DAILY_HOST_IP} -p ${PG_PORT} ${DB_NAME} ${USER_NAME} -c "copy daily to stdout " | psql -h ${HISTORY_HOST_IP} -p ${PG_PORT} ${DB_NAME} ${USER_NAME}  -c "copy history from stdin"

    Além disso, você pode escrever um script de shell.

    • 13
  4. Best Answer

    Se você planeja manter o histórico por longos períodos (muitos meses), sugiro dar uma olhada nas opções de particionamento - pode ser uma partição para cada dia ou semana e assim por diante. Também depende dos padrões de acesso de sua tabela de histórico (você executa consultas que acessam dados entre datas? Você faz muitas agregações etc). Dê uma olhada nas visualizações materializadas para armazenar agregados/resumos. http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html

    • 12

  5. Obviamente, esta não é uma resposta exata para sua pergunta, mas se você não precisar acessar a historytabela, também poderá gerar um dump SQL:

    pg_dump -h host -p port -w -U user db > dump.sql

    Então, pode-se usar uma ferramenta como gitcalcular a diferença e armazená-la de forma eficiente.

    git add dump.sql
git commit -m "temp dump"
git gc --aggressive

    Isso é útil porque a maioria das partes em um banco de dados não muda todos os dias. Em vez de armazenar uma cópia inteira para cada dia, pode-se armazenar a diferença entre dois dias.

    Você pode usar um crontabtrabalho para que o despejo seja processado todos os dias.

    • 3

relate perguntas