55

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?

Milovan Zogovic
  • 1,463
  • 3
  • 15
  • 20

5 Answers5

21

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.

Milovan Zogovic
  • 1,463
  • 3
  • 15
  • 20
18

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

Fabrizio Mazzoni
  • 1,976
  • 5
  • 22
  • 32
14

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.

francs
  • 1,457
  • 1
  • 15
  • 25
13

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

Jayadevan
  • 1,051
  • 8
  • 12
3

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.

willeM_ Van Onsem
  • 134
  • 5