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

我要做的是运行:

INSERT INTO history SELECT * FROM daily

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

还有另一种更有效的方法将数据从一个表复制到另一个表?

#1 楼

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

评论


感谢你的回答。这似乎是唯一的方法。我需要按月对数据进行分区,从而使重新索引(因为这里的索引再生是个问题)要快得多。

– Milovan Zogovic
2013年12月26日上午11:21

#2 楼

以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文档以获取更多信息

评论


它仍然运行得非常非常慢...也许它不得不做一些重建巨大索引的事情?历史记录表中有1.6亿行,我们还要追加3百万行。

– Milovan Zogovic
2013年12月24日20:46在

在您填充一个空表或添加比现有表多的行时,通常通常更有效的方法是删除非集群索引,并在传输完成后重新创建它们(除非当时正在积极使用该表) )

– David Spillett
13年12月24日在21:01

顺便说一句,这是一次性操作还是您必须定期执行的操作?如果我定期建议您创建一个触发器,那么您不必每次都经历此考验。

–法布里佐·马佐尼(Fabrizio Mazzoni)
2013年12月25日下午6:45

@FabrizioMazzoni-必须每天在特定时间执行一次(一定要及时拍摄快照)。

– Milovan Zogovic
2013年12月25日上午9:41

@DavidSpillett-的确如此!删除索引使导入非常快(请参见上面的答案),但是,重新创建索引要花费数小时(因为数据库中有1.6亿行)。

– Milovan Zogovic
2013年12月25日上午9:42

#3 楼

问题出在索引上。 history表具有1.6亿个索引行。通过运行COPY FROMINSERT INTO .. SELECT,不花大量时间不插入行,而是更新索引。当我禁用索引时,它在10秒内导入了3M行。现在,我需要找到重新索引大表的更快方法。

评论


您甚至需要在历史记录表上建立索引吗?

–夏洛克
15年6月11日在8:30

使用CONCURRENTLY关键字添加索引

– Akvel
16-2-16在10:51

我面临类似的问题,您是使用COPY命令还是使用insert命令将其复制了?

–大卫
20-4-5的2:32

#4 楼

您可以使用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脚本。

评论


没有中间文件的绝佳解决方案。我也非常快地在1h20(不包含索引)之间在常规磁盘和网络文件系统之间复制了9.5亿行表。

– Le Droid
17年1月23日在22:46

真遗憾,这不能直接从一张桌子到另一张桌子。

–查理·克拉克(Charlie Clark)
19年11月28日在15:12

#5 楼

这当然不是您问题的确切答案,但是如果您不需要访问history表,则还可以生成一个SQL转储:

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


然后可以使用诸如git之类的工具来计算差异并有效地存储差异。 。您可以存储两天之间的差额,而不必每天存储整个副本。

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