Skip site navigation (1) Skip section navigation (2)

Re: How import big amounts of data?

From: Ron <rjpeace(at)earthlink(dot)net>
To: arnaulist(at)andromeiberica(dot)com,pgsql-performance(at)postgresql(dot)org
Subject: Re: How import big amounts of data?
Date: 2005-12-29 14:20:28
Message-ID: 6.2.5.6.0.20051229085429.01db84d8@earthlink.net (view raw or flat)
Thread:
Lists: pgsql-performance
At 04:48 AM 12/29/2005, Arnau wrote:
>Hi all,
>
>   Which is the best way to import data to tables? I have to import 
> 90000 rows into a column and doing it as inserts takes ages. Would 
> be faster with copy? is there any other alternative to insert/copy?
Compared to some imports, 90K rows is not that large.

Assuming you want the table(s) to be in some sorted order when you 
are done, the fastest way to import a large enough amount of data is:
-put the new data into a temp table (works best if temp table fits into RAM)
-merge the rows from the original table and the temp table into a new table
-create the indexes you want on the new table
-DROP the old table and its indexes
-rename the new table and its indexes to replace the old ones.

If you _don't_ care about having the table in some sorted order,
-put the new data into a new table
-COPY the old data to the new table
-create the indexes you want on the new table
-DROP the old table and its indexes
-rename the new table and its indexes to replace the old ones

Either of these procedures will also minimize your downtime while you 
are importing.

If one doesn't want to go to all of the trouble of either of the 
above, at least DROP your indexes, do your INSERTs in batches, and 
rebuild your indexes.
Doing 90K individual INSERTs should usually be avoided.

cheers,
Ron



In response to

pgsql-performance by date

Next:From: Teemu TormaDate: 2005-12-29 14:41:05
Subject: Re: How import big amounts of data?
Previous:From: Dennis BjorklundDate: 2005-12-29 11:39:06
Subject: Re: How import big amounts of data?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group