From: Ron <rjpeace@earthlink.net>
To: arnaulist@andromeiberica.com
To: pgsql-performance@postgresql.org
Subject: Re: How import big amounts of data?
Date: Thu, 29 Dec 2005 09:20:28 -0500
Message-ID: <6.2.5.6.0.20051229085429.01db84d8@earthlink.net> (raw)
In-Reply-To: <43B3B0EA.6070406@andromeiberica.com>
References: <43B3B0EA.6070406@andromeiberica.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rjpeace@earthlink.net, arnaulist@andromeiberica.com
Subject: Re: How import big amounts of data?
In-Reply-To: <6.2.5.6.0.20051229085429.01db84d8@earthlink.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox