How to efficiently copy millions of rows from one table to another in Postgresql? I have two database tables. One contains hundreds of millions of records. Lets call that one history. The other one is calculated on daily basis and I want to copy all of its records into the history one.

What I did was to run:


INSERT INTO history SELECT * FROM daily


And it did the trick for a while, but it started to get slower and slower as the number of records kept growing. Now I have around 2 million records that need to be copied from daily to history in single operation and it takes too long to complete.

Is there another, more efficient way of copying data from one table to another?
Dump the table in csv format

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


use the COPY command which is far more efficient for large amounts of data.

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


Check postgres docs at http://www.postgresql.org/docs/current/static/sql-copy.html for more info