主页 / dba / 问题 / 55568
Accepted
Milovan Zogovic
Asked: 2013-12-25 10:48:51 +0800 CST

如何在 Postgresql 中有效地将数百万行从一个表复制到另一个表？

  • 772

我有两个数据库表。一个包含数亿条记录。让我们称之为那个history。另一个是每天计算的，我想将它的所有记录复制到history一个中。

我所做的是运行：

INSERT INTO history SELECT * FROM daily

它确实奏效了一段时间，但随着记录数量的不断增长，它开始变得越来越慢。现在我有大约 200 万条记录需要在一次操作中从复制到复制，daily并且history完成时间太长。

是否有另一种更有效的方式将数据从一个表复制到另一个表？

postgresql

5 个回答

  1. 以 csv 格式转储表格

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

    使用对大量数据更有效的 COPY 命令。

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

    在http://www.postgresql.org/docs/current/static/sql-copy.html查看 postgres 文档以获取更多信息

    • 18

  2. 问题出在索引上。该history表有 160M 索引行。通过运行或者运行COPY FROM，INSERT INTO .. SELECT不是插入行而是更新索引花费了大量时间。当我禁用索引时，它会在 10 秒内导入 3M 行。现在我需要找到更快的方法来重新索引大表。

    • 18

  3. 您可以使用psql工具，我可能会很高效，如下所示，

    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"

    你也可以写一个shell脚本。

    • 13
  4. Best Answer

    如果您打算长时间（数月）保留历史记录，我建议您查看分区选项 - 可能是每天或每周的一个分区，依此类推。它还取决于您的历史表的访问模式（您是否运行跨日期访问数据的查询？您是否进行了很多聚合等）。查看用于存储聚合/摘要的物化视图。 http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html

    • 12

  5. 这当然不是您问题的确切答案，但如果您不需要访问该history表，您也可以生成一个 SQL 转储：

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

    然后可以使用一种工具git来计算差异并有效地存储它。

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

    这很有用，因为数据库中的大多数部分不会每天都在变化。可以存储两天之间的差异，而不是每天存储整个副本。

    您可以使用crontab每天处理转储的作业。

    • 3

相关问题