PostgreSQL – How to Efficiently Copy Millions of Rows from One Table to Another

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?

Best Answer

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

Related Solutions

Postgresql – Copy PostgreSQL data from one PC to another

You could dump the database using pg_dump and then restore it on the new server using psql. Here's a couple of commands from the above link:

Create the backup:

pg_dump mydb > db.sql

Copy db.sql to the new server (specific command depends on OS)

Go to the new server

createdb mydb -E UTF8 (you don't have to specify UTF8 encoding, but I always do)

Then:

psql -d mydb -f db.sql

As I was answering, JohnP just answered with a fine answer but assumes that pg_hba.conf has been edited to allow remote connections and postgres.conf has been edited to listen on the network.

Postgresql – Copy few rows from one database to another

Off the top of my head, here are a few ways to do it without resorting to 3rd-party tools.

If you are using PostgreSQL 9.3, you can use the extension postgres_fdw.

If you are using an older version of PostgreSQL have a look at dblink

If you want a command-line only solution, and you are using Linux (or Cygwin etc), you could try something like the following:

psql -d yourdb -qtAXc "COPY (select col1, col2, col3 from your_table where id between 123 and 456) TO STDOUT CSV HEADER" -U youruser -h localhost -p 5432 \
| psql -d yourremotedb -U youruser -h your_remote_host -qtAXc "COPY yourtable FROM STDIN CSV HEADER"

Best Answer

Related Solutions

Postgresql – Copy PostgreSQL data from one PC to another

Postgresql – Copy few rows from one database to another

Related Question