Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers 4

up vote 3 down vote accepted

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

share|improve this answer
    
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. –  Milovan Zogovic Dec 26 '13 at 11:21
add comment

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

share|improve this answer
    
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. –  Milovan Zogovic Dec 24 '13 at 20:46
1  
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) –  David Spillett 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. –  Fabrizio Mazzoni Dec 25 '13 at 6:45
    
@FabrizioMazzoni - It has to be performed on daily basis at specific time (kinda taking snapshots in time). –  Milovan Zogovic 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).. –  Milovan Zogovic Dec 25 '13 at 9:42
show 1 more comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.