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:


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?


If you plan to keep history for long periods (many months), I suggest having a look at partitioning options - may be one partition for each day or week and so on. It does depend on the access patterns of you history table also (do you run queries that access data across dates? Do you do a lot of aggregations etc). Have a look at materialized views for storing aggregates/summaries. http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html

  • Thanks for the answer. It seems like the only way to go. I'd need to partition the data by months and thus making reindexing (since index regeneration was a problem here) much faster. Dec 26 '13 at 11:21

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

  • 1
    Its still running very, very slow... Perhaps it has to do something with having to rebuild such a huge index? There is 160 million rows in history table, and we're appending 3 more million rows. Dec 24 '13 at 20:46
  • 3
    Of you are fulling an empty table, or adding more rows than already exist, it is usually more efficient to drop non-clustered indexes and recreate them once the transfer is complete (unless there is active use of the table(s) at the time) Dec 24 '13 at 21:01
  • BTW, is this a one off operation or is it something you have to do regularly? If its on a regular basis i sugest that you create a trigger so you don't have to go through this ordeal every time. Dec 25 '13 at 6:45
  • @FabrizioMazzoni - It has to be performed on daily basis at specific time (kinda taking snapshots in time). Dec 25 '13 at 9:41
  • @DavidSpillett - indeed! Dropping indexes makes import very fast (see my answer above), however, recreating indexes takes hours (since I have 160M rows in database).. Dec 25 '13 at 9:42

The problem was with indexes. The history table had 160M indexed rows. By running either COPY FROM or INSERT INTO .. SELECT it was taking a lot of time not to insert rows, but to update indexes. When i disabled indexes, it imported 3M rows in 10 seconds. Now i need to find faster way of reindexing the big table.

  • 3
    Do you even need indexes on a history table?
    – Sherlock
    Jun 11 '15 at 8:30
  • 2
    Add the index using the CONCURRENTLY keyword
    – Akvel
    Feb 16 '16 at 10:51
  • I'm facing a similar issue, did you copy it with the COPY command or with the insert command?
    – david
    Apr 5 '20 at 2:32

You can use psql tool, I might be efficient, as the following,

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"

Also you can write a shell script.

  • Great solution without intermediate file. Very fast too, I copied a 950 millions rows table in 1h20 (without indexes) between regular disk and network file system.
    – Le Droid
    Jan 23 '17 at 22:46
  • It's a real pity this can't be done directly from one table to another. Nov 28 '19 at 15:12

This is of course not an exact answer to your question, but if you don't need to access the history table, you can as well generate an SQL dump:

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

Then one could use a tool like git to calculate the difference and store this efficiently.

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

This is useful because most parts in a database, will not change every day. Instead of storing an entire copy for every day, one can store the difference between two days.

You can use a crontab job such that the dump is processed every day.

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .