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
#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 FROM
或INSERT 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
作业,以便每天处理转储。
评论
感谢你的回答。这似乎是唯一的方法。我需要按月对数据进行分区,从而使重新索引(因为这里的索引再生是个问题)要快得多。
– Milovan Zogovic
2013年12月26日上午11:21