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

5 Answers 5

up vote 4 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

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

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

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.

share|improve this answer

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

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.